CODEWITHSUNDEEP
sqloracle-databasedistancenearest-neighbor
Stepan
Stepan

Reputation: 1431

Oracle spatial: SDO_NN cannot be evaluated without index

I have two tables called my_nodes and restaurants that contain a name and geoloc data. I need to get a table that for each my_node returns nearest restaurant and distance from node to restaurant.

    select n.node_id, r.r_name , sdo_nn_distance(1) as min_distance from 
        (select nodeid as node_id, geoloc  from my_nodes@my_server ) n,
        (select r_name, GEOLOC from restaurants) r 
        where sdo_nn   (n.geoloc, r.GEOLOC,   'Unit = MILE sdo_num_res = 1', 1) = 'TRUE'

The query above returns:

    ORA-13249: SDO_NN cannot be evaluated without using index
    ORA-06512: at "MDSYS.MD", line 1723
    ORA-06512: at "MDSYS.MDERR", line 17
    ORA-06512: at "MDSYS.PRVT_IDX", line 9

How to fix it?

Upvotes: 1

Views: 565

Answers (1)

Littlefoot
Littlefoot

Reputation: 142733

Did you create an index? If not, syntax would be like this:

CREATE INDEX whichever_name_you_want
  ON table_name_here (sdo_geometry_column_name_here)
  INDEXTYPE IS MDSYS.SPATIAL_INDEX;

As I'm not particularly good at spatial, have a look at this OTN Forums discussion: https://community.oracle.com/thread/3696687?start=0&tstart=0 ; you might find it interesting.

Although people on Stack Overflow prefer code being posted here, I think I'm not going to copy/paste ~200 lines of code. If it turns out that Paul Dziemiela (who wrote the answer there) helped, feel free to post your solution, once you reach it. Good luck!

Upvotes: 1

Related Questions