You Asked
Hello,
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
and Tom said...
I asked Dan Geringer of the spatial group to look at this and he said:
Hi Tom,
Attached is a way to compute the nearest neighbor of a point along the surface of a unit sphere (for you to post).
Hope this helps. Thanks.
Dan
-------------------------------------------------------------------
-- CREATE A USER DEFINED COORDINATE SYSTEM FOR A UNIT SPHERE...
-- SIMILAR TO SRID 2000001, WITH 1 AS THE SEMI MAJOR AXIS --
--
-- inverse flattening = semi_major/(semi_major - semi_minor)
--
-- In a perfect sphere, the inverse flattening equation
-- would divide by 0, so we set (semi_major-semi_minor) to a
-- very small positive number, resulting in a very large value
-- for inverse flattening (1000000000000).
-------------------------------------------------------------------
delete from cs_srs where srid = 10000001;
insert into cs_srs values ('UNIT SPHERE', 10000001, 10000001, 'YOUR_NAME',
'GEOGCS [ "UNIT SHPERE Longitude/Latitude", DATUM [ "UNIT SPHERE", SPHEROID ["UNIT SPHERE", 1, 1000000000000]],
PRIMEM ["SOME PRIME MERIDIAN", 0.0000000], UNIT ["Decimal Degree", 0.01745329251994330]]',
null);
-------------------------------------------------------------------
-- RUN SOME DISTANCE TESTS AGAINST THE UNIT SPHERE --
-------------------------------------------------------------------
select sdo_geom.sdo_distance (
sdo_geometry (2001, 10000001, sdo_point_type ( 0,-90,null), null, null),
sdo_geometry (2001, 10000001, sdo_point_type ( 0,0,null), null, null), .05)
from dual;
drop table sphere_test;
create table sphere_test (point_name varchar2 (20),
ra float,
dec float,
geom sdo_geometry);
-------------------------------------------------------------------
-- INSERT POINTS --
-------------------------------------------------------------------
insert into sphere_test(point_name, ra, dec, geom) values ('point1', 92.44125, 83.62542,
SDO_GEOMETRY(2001,
10000001,
SDO_POINT_TYPE(92.44125, 83.62542, null),
NULL,
NULL));
insert into sphere_test(point_name, ra, dec, geom) values ('point2', 91.21833, 83.52097,
SDO_GEOMETRY(2001,
10000001,
SDO_POINT_TYPE(91.21833, 83.52097, null),
NULL,
NULL));
insert into sphere_test(point_name, ra, dec, geom) values ('point3', 91.21833, 83.52097,
SDO_GEOMETRY(2001,
10000001,
SDO_POINT_TYPE(93.26792, 82.93584, null),
NULL,
NULL));
-------------------------------------------------------------------
-- UPDATA user_sdo_geom_metadata --
-- **NOTE** For, bounds MUST go from -180 to 180 for X and -90 to
-- 90 for Y.
-------------------------------------------------------------------
DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'SPHERE_TEST' AND COLUMN_NAME = 'GEOM';
INSERT INTO user_sdo_geom_metadata VALUES (
'SPHERE_TEST',
'GEOM',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('ra', -180, 180, 0.05),
MDSYS.SDO_DIM_ELEMENT('dec', -90.0, 90.0, 0.05)),
10000001);
-------------------------------------------------------------------
-- CREATE THE SPATIAL INDEX --
-------------------------------------------------------------------
create unique index sphere_idx on sphere_test (point_name);
-------------------------------------------------------------------
-- CREATE THE SPATIAL INDEX --
-------------------------------------------------------------------
CREATE INDEX sphere_test_idx
ON sphere_test(geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
--
-- Note, the smallest tolerance is 0.05 (5 centimeters) when
-- performing geodesic analysis. The distances returned
-- in the nearest neighbor query below are less than
-- 5 centimeters apart (so 0 is returned).
--
SELECT /*+ no_index (t2 sphere_idx) */
t1.point_name name1,
t2.point_name name2,
sdo_geom.sdo_distance (t1.geom, t2.geom, .05) distance
FROM sphere_test t1,
sphere_test t2
WHERE SDO_NN(t2.geom, t1.geom, 'sdo_batch_size=10') = 'TRUE'
and t1.point_name = 'point1'
and t1.point_NAME != t2.point_name
and rownum < 2;
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment