Aug 3, 2011

Spatial Error Related with SDO_NN

SELECT sdo_nn_distance (1) dist, a.cid,
sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 1) x,
sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 2) y
FROM code_boundaries a, golf_boundaries b
WHERE sdo_nn (a.sdo_geometry, b.sdo_geometry, 'sdo_num_res=1 UNIT=foot ', 1) = 'TRUE'
AND b.gid = 1341



When executing this sql  following error occurs .

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

We can resolve this error by removing  SDO_NN_DISTANCE ancillary operator from the query but what to do when we require distance what I have noted   is that it is due to non-spatial column which is in where clause is not indexed .When you create a index for that non-spatial column the query will execute

SQL > create index idx on golf_boundaries(gid) ;


SELECT sdo_nn_distance (1) dist, a.cid,
sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 1) x,
sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 2) y
FROM code_boundaries a, golf_boundaries b
WHERE sdo_nn (a.sdo_geometry, b.sdo_geometry, 'sdo_num_res=1 UNIT=foot ', 1) = 'TRUE'
AND b.gid = 1341 ;


cool
-------




No comments: