Search code examples
spatial-queryoracle-spatial

Oracle spatial data operator - SDO_nn - Not getting any results for sdo_num_res = 1


I am using SDO_NN operator to find the nearest hydrant next to a building.

Building:

CREATE TABLE  "BUILDINGS" 
(   
"NAME" VARCHAR2(40), 
"SHAPE" "SDO_GEOMETRY") 

Hydrant:

CREATE TABLE  "HYDRANTS" 
   (    "NAME" VARCHAR2(10), 
"POINT" "SDO_POINT_TYPE"
  );

I have setup spatial indexes properly for buildings.shape and I run the query to get the nearest hydrant to the building 'Motel'

select b1.name as name, h.point.x as x, h.point.y as y  from buildings b1, hydrants h where  b1.name ='Motel'  and
                    SDO_nn( b1.shape, MDSYS.SDO_GEOMETRY(2003,NULL, NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),
                            SDO_ORDINATE_ARRAY( h.point.x,h.point.y)), 'sdo_num_res=1')= 'TRUE';

Here's the problem:

When I set the parameter sdo_num_res=1, I get zero tuples. And when I make sdo_num_res=2, I get one tuple.

What is the reason for the weird behavior ?

Note: I am getting zero rows only when building.name= 'Motel', for all other tuples I am getting 1 row when sdo_num_res = 1

Edit: Insert queries

Insert into buildings (NAME,SHAPE) values ('Motel',MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(564,425,585,436,573,458,552,447)));

Insert into hydrants  (name,POINT) values ('p57',MDSYS.SDO_POINT_TYPE(589,448,0));

Solution

  • To perform spatial comparisons between a point to a polygon, the SDO_GEOMETRY is defined with SDO_SRID=2001 and center set to a SDO_POINT_TYPE-> which we want to compare.

    MDSYS.SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(-79, 37, NULL), NULL, NULL)