Database Software

I would like to store several points in the database given degrees and minutes as position. In this example I have point 1 that is E 150, 0/S 30, 0 and points 2 that is E 150, 0/S 30.1. For example if I enter 2 km as distance from position of point 1 I would like the search to return all points witin 2 km.(should return points 2 that is very near)

If I run the query
SELECT c.name
FROM cola_markets_cs c
WHERE
SDO_WITHIN_DISTANCE(c.shape,
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(150.0, 30.1, NULL), NULL, NULL),
'distance=10000 unit=KM')
= 'TRUE';
I get result both position 1 and 2, but if I decrease to distance=1000 no rows is returned.

Can you see what I am doing wrong here?

Can I also combine SDO_WITHIN_DISTANCE with SDO_NN_DISTANCE so I can ask for all points 10 km in distance from the reference point and I can also see the actual distance for each point?

Thank you

I have tested with the following code. Do I save the position wrong..?


CREATE TABLE cola_markets_cs (
mkt_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape MDSYS.SDO_GEOMETRY);

INSERT INTO cola_markets_cs VALUES (
1,
'Point 1',
MDSYS.SDO_GEOMETRY(
2001,
8307,
MDSYS.SDO_POINT_TYPE(150.0, 30.0, NULL),
NULL,
NULL
)
);

INSERT INTO cola_markets_cs VALUES (
2,
'Point 2',
MDSYS.SDO_GEOMETRY(
2001,
8307,
MDSYS.SDO_POINT_TYPE(150.0, 30.1, NULL),
NULL,
NULL
)
);

---------------------------------------------------------------------------
-- UPDATE METADATA VIEW --
---------------------------------------------------------------------------
-- Update the USER_SDO_GEOM_METADATA view. This is required
-- before the Spatial index can be created. Do this only once for each
-- layer (i.e., table-column combination; here: cola_markets_cs and shape).

INSERT INTO USER_SDO_GEOM_METADATA
VALUES (
'cola_markets_cs',
'shape',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, 10), -- 10 meters tolerance
MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, 10) -- 10 meters tolerance
),
8307 -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
);

-------------------------------------------------------------------
-- CREATE THE SPATIAL INDEX --
-------------------------------------------------------------------
-- Must be R-tree; quadtree not supported for geodetic data.
CREATE INDEX cola_spatial_idx_cs
ON cola_markets_cs(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

--This search only return if distance is 10 000 km...
SELECT c.name
FROM cola_markets_cs c
WHERE
SDO_WITHIN_DISTANCE(c.shape,
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(150.0, 30.1, NULL), NULL, NULL),
'distance=10000 unit=KM')
= 'TRUE';

--According to this search the distance to point 1 is 3331198,72256398,
--this should be zero..
SELECT
c.mkt_id, c.name, SDO_NN_DISTANCE(1) dist
FROM cola_markets_cs c
WHERE SDO_NN(c.shape,
sdo_geometry(2001, 8307,sdo_point_type(150.0, 30.1, NULL), NULL, NULL),
'sdo_num_res=2', 1) = 'TRUE' ORDER BY dist