SDO_NN with Oracle Spatial 10g

From: Ina Schmitz <web_at_inalein.net>
Date: Thu, 3 Nov 2005 22:19:58 +0100
Message-ID: <dkduu3$4pt$03$2@news.t-online.com>

Hello,

although the description of my problem is a bit long, I hope you could help me.

I would like to compute the nearest neighbor of a point being located on the surface of the unit sphere.
For that query, I would like to take advantage of the features provided by Oracle Spatial (10g).

Table spatial_test contains the columns point_name, x, y, z, ra, dec where: point_name is the primary key
x, y, z are the coordinates of the points on the unit sphere (so x^2+y^2+z^2=1)
ra, dec are the the concerning spherical coordinates where the following conditions hold: x=cos(dec)*cos(ra) , y=cos(dec)*sin(ra), z=sin(dec).

For computing the nearest neighbor of a point with point_name='point1' the query without using Oracle Spatial is:

select * from(

        select
            acos(t1.x*t2.x+t1.y*t2.y+t1.z*t2.z) as distance, t1.*
            from spatial_test t1,
                    spatial_test t2
            where t2.point_name='point1'
                    and t1.name != t2.name
            order by dist
        )

where rownum<2;

For taking advantage of Oracle Spatial, I have to prepare my data doing the following five steps:

1. add a column to of type SDO_GEOMETRY to table spatial_test
2. insert values to that table
3. update table user_sdo_geom_metadata
4. create the spatial index
5. execute the following query on the amended table spatial_test:

SELECT t1.point_name name1, t2.point_name name2 FROM spatial_test t1, spatial_test t2
WHERE SDO_NN(t2.geom, t1.geom, 'sdo_num_res=2') = 'TRUE' and t1.point_name = 'point1'
and t1.point_NAME != t2.point_name

As mentioned in the User Guide for Oracle Spatial, only two dimensional objects are supported.
So, if I insert tuples in the following form to my table:

insert into spatial_test (point_name, x, y, z, geom) values (..., ..., ..., ...,

      SDO_GEOMETRY(3001,
                   NULL,  --SDO_SRID is null, so no coordinate system is
associated with the geometry
          SDO_POINT_TYPE(x_value, y_value, z_value),
          NULL,
          NULL));

I won't get the correct results. I assume that the z_value is just ignored. Am I right with that assumption?

For using Oracle Spatial, I have to use the equivalent just using two dimensions. Since ra, dec is another representation for x, y, z, I tried to do the same, just using ra and dec. But here, my results also differ from the ones computed with my own computation of the nearest neighbor.

Here an minimal example which shows my problem: CREATE TABLE spatial_test(
point_name varchar(20) PRIMARY KEY,

x float,
y float,
z float,

ra float,
dec float,
geom SDO_GEOMETRY);

INSERT POINTS --
insert into spatial_test(point_name, x, y, z, ra, dec, geom) values ('point1', -0.00472924, 0.110927216, 0.99381728, 92.44125, 83.62542, SDO_GEOMETRY(2001, -- 2 dimensions, last dimension is the measure, geometry type 01 = point NULL, --SDO_SRID is null, so no coordinate system is associated with the geometry SDO_POINT_TYPE(92.44125, 83.62542, null), NULL, NULL));

insert into spatial_test(point_name, x, y, z, ra, dec, geom) values ('point2', -0.00239923, 0.112814014, 0.993613226, 91.21833, 83.52097,

SDO_GEOMETRY(2001, -- 2 dimensions, last dimension is the measure, geometry type 01 = point

                   NULL,  --SDO_SRID is null, so no coordinate system is
associated with the geometry
          SDO_POINT_TYPE(91.21833, 83.52097, null),
          NULL,
          NULL));

insert into spatial_test(point_name, x, y, z, ra, dec, geom) values ('point3', -0.00701052, 0.122780703, 0.992409065, 93.26792, 82.93584,

SDO_GEOMETRY(2001, -- 2 dimensions, last dimension is the measure, geometry type 01 = point

                   NULL,  --SDO_SRID is null, so no coordinate system is
associated with the geometry
          SDO_POINT_TYPE(93.26792, 82.93584, null),
          NULL,
          NULL));


-------------------------------------------------------------------

-- UPDATA user_sdo_geom_metadata --

INSERT INTO user_sdo_geom_metadata
  (TABLE_NAME,
  COLUMN_NAME,
  DIMINFO,
  SRID)
  VALUES (
  'spatial_test',
  'geom',
  MDSYS.SDO_DIM_ARRAY(
    MDSYS.SDO_DIM_ELEMENT('ra', 0.0, 360.0, 0.0000000000001),     MDSYS.SDO_DIM_ELEMENT('dec', -90.0, 90.0, 0.0000000000001)      ),
  NULL -- no specific coordinate system should be associated with the geometries.
);

CREATE THE SPATIAL INDEX --
CREATE INDEX spatial_test_idx ON spatial_test(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Now I could execute the following queries which should both compute the nearest neighbor of 'point1'.

This is the statement computing the nearest neighbor without Oracle Spatial:

select * from(

        select
            acos(t1.x*t2.x+t1.y*t2.y+t1.z*t2.z) as distance, t2.point_name
p1, t1.point_name p2
            from spatial_test t1,
                    spatial_test t2
            where t2.point_name='point1'
                    and t1.point_name != t2.point_name
            order by distance
        )

where rownum<2;

RESULT:

  DISTANCE P1                   P2
---------- -------------------- --------------------
,003005107 point1               point2


------------------------------------------------------------------------------------

With the following statement, I compute the nearest neighbor of 'point1' using Oracle Spatial:
SELECT t1.point_name name1, t2.point_name name2 FROM spatial_test t1, spatial_test t2
WHERE SDO_NN(t2.geom, t1.geom, 'sdo_num_res=2') = 'TRUE' and t1.point_name = 'point1'
and t1.point_NAME != t2.point_name;

RESULT:

NAME1                NAME2
-------------------- --------------------
point1               point3


-------------------------------------------------------------------------------------

As you see, unfortunately, the two results differ.

Could you please tell me, what I understood wrong in using Oracle Spatial? In addition, what kind of coordinate system is assumed if it isn't specified in my SDO_GEOMETRY? Which kind of distance is computed using sdo_nn (euclidean distance, ...)?

Would be glad, if you could tell how to reach the same results for my nearest neighbors using Oracle Spatial.

Regards,
Ina Received on Thu Nov 03 2005 - 15:19:58 CST