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

Question

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));

Could you please post a few example shapes (i.e. one successful combination of building and hydrant and the unsuccessful combination that is causing your problem)? This will be difficult to answer without being able to replicate the problem. Also, would be useful to know which Oracle release you're using (Spatial bug fixes appear in most releases). — Ben, Mar 08 '13 at 08:57
I am unable to reproduce this problem (see SQLFiddle: http://www.sqlfiddle.com/#!4/9ce47/14). It's possible that your spatial index has issues, I suggest dropping and rebuilding it. — Ben, Mar 10 '13 at 22:23
Also, your syntax is a bit dubious - you seem to be converting your single hydrant coordinate into an invalid single-point polygon for comparison - are you sure this is what you wanted to do? Note - fixing this doesn't change the result for me. — Ben, Mar 10 '13 at 22:24
@ben: i am trying to find the nearest hydrant(point) to a building(polygon) — Kalyanaraman Santhanam, Mar 11 '13 at 00:32
Check your syntax, you're converting the point to an invalid polygon geometry - 2003 instead of a 2001. It doesn't change the result, but I imagine it's not your intent. This question is unanswerable as-is, because it works fine for me. Maybe more data will trigger the problem? — Ben, Mar 11 '13 at 11:54

Kalyanaraman Santhanam · Accepted Answer · 2013-04-15T02:00:04.230

0

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)

edited Apr 15 '13 at 02:00

answered Apr 13 '13 at 15:43

Kalyanaraman Santhanam

1,371
1
18
30

score 0 · Answer 2 · answered May 16 '14 at 18:37

First of all, your query does not do what you say it does: it actually returns the nearest building called "Motel" from any of your hydrants. To do what you want (i.e. the opposite) you need to reverse the order of the arguments to SDO_NN: all spatial operators search the first argument, using the value of the second argument.

Then the insert into your HYDRANTS table is wrong:

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

The SDO_POINT_TYPE object is not designed to be used that way: it is only used inside the SDO_GEOMETRY type. The proper way is this:

insert into hydrants (name,POINT) values ('p57',sdo_geometry(2001, null, SDO_POINT_TYPE(589,448,null), null, null));

And of course you need to change your table definition accordingly.

Then your building is also incorrectly created: a polygon must always close, i.e. the last point must be the same as the first point. So the proper shape should be like this:

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

Here is the full example:

Create the tables:

create table buildings (
  name varchar2(40) primary key,
  shape sdo_geometry
);

create table hydrants(
  name varchar2(10) primary key, 
  point sdo_geometry
);

Populate the tables:

insert into buildings (NAME,SHAPE) values ('Motel', SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(564,425,585,436,573,458,552,447,564,425)));
insert into hydrants (name,POINT) values ('p57',sdo_geometry(2001, null, SDO_POINT_TYPE(589,448,null), null, null));
commit;

Confirm that the geometries are all correct:

select name, sdo_geom.validate_geometry_with_context (point, 0.05) from hydrants;
select name, sdo_geom.validate_geometry_with_context (shape, 0.05) from buildings;

Setup spatial metadata and create spatial indexes:

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
values (
  'BUILDINGS',
  'SHAPE',
  sdo_dim_array (
    sdo_dim_element ('X', 0,1000,0.05),
    sdo_dim_element ('Y', 0,1000,0.05)
  ),
  null
);
commit;

create index buildings_sx on buildings (shape)
  indextype is mdsys.spatial_index;

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
values (
  'HYDRANTS',
  'POINT',
  sdo_dim_array (
    sdo_dim_element ('X', 0,1000,0.05),
    sdo_dim_element ('Y', 0,1000,0.05)
  ),
  null
);
commit;

create index hydrants_sx on hydrants (point)
  indextype is mdsys.spatial_index;

Now Try the properly written query:

select h.name, h.point.sdo_point.x as x, h.point.sdo_point.y as y  
from buildings b, hydrants h 
where b.name ='Motel' 
and sdo_nn(h.point, b.shape, 'sdo_num_res=1')= 'TRUE';

which returns:

NAME                      X          Y
---------------- ---------- ----------
p57                     589        448

1 row selected.

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

2 Answers2