0

I have this table, there it goes a sample output to show that it is a point

select SDO_UTIL.TO_WKTGEOMETRY(geo) 
from places 
where id=1
_______________________________________________________

POINT (-88.706148 32.374127)

Given a point (i.e. latitude: 32.378097, longitude:-88.709356) I need to get the N neighbors that are around that point in a radio distance of 1 kilometer (0.6 miles)

So far I have this

SELECT id, description, latitude, longitude, SDO_NN_DISTANCE(1) dist  
FROM places P 
WHERE sdo_nn (geoloc,  MDSYS.SDO_GEOMETRY(1, 8307, MDSYS.SDO_POINT_TYPE( -88.709356 , 32.378097, NULL), NULL, NULL), 'distance=1 unit=KM', 1) = 'TRUE' 

But, that query does not return points from places that are near, like 400 meters

Is there something I'm missing?

  • That should work fine: the syntax is correct. What exact result do you get ? And can you share some sample data ? – Albert Godfrind Jun 28 '18 at 14:05
1

That works fine with my tests. It returns all the places that are less than 1 km from your origin point. Note that you should use the proper geometry type for 2D points (i.e. 2001 instead of 1):

SELECT id, description, latitude, longitude, SDO_NN_DISTANCE(1) dist  
FROM places P 
WHERE sdo_nn (geoloc, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-88.709356, 32.378097, NULL), NULL, NULL), 'distance=1 unit=KM', 1) = 'TRUE' 

For example:

select id, city, state_abrv, SDO_NN_DISTANCE(1) dist 
from us_cities 
where sdo_nn(location, SDO_GEOMETRY(1, 8307, SDO_POINT_TYPE(-88.709356, 32.378097, NULL), NULL, NULL), 'distance=300 unit=KM', 1) = 'TRUE';

        ID CITY                 STATE_       DIST
---------- -------------------- ------ ----------
        78 Jackson              MS      141.17698
        79 Mobile               AL     197.555367
        60 Birmingham           AL     219.435603
        86 Montgomery           AL     228.247059
        25 New Orleans          LA     281.561957

5 rows selected.

Just to be clear: the goal of the SDO_NN operator is to return the closest places to an origin, no matter the distance, and considering any other predicates. It is designed to answer questions like: "find me all the nearest restaurants of type 'chinese' that are open today, serve Szechuan cuisine and take reservations"

The distance limit is there to remove unrealistic answers - like those restaurants that meet all critieria but are 100km away.

Note that the distance returned by SDO_NN_DISTANCE is expressed in the unit you choose, i.e. kilometers. If you want a result in miles, then just do:

SELECT id, description, latitude, longitude, SDO_NN_DISTANCE(1) dist  
FROM places P 
WHERE sdo_nn (geoloc, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-88.709356, 32.378097, NULL), NULL, NULL), 'distance=0.6 unit=mile', 1) = 'TRUE'

For example:

select id, city, state_abrv, SDO_NN_DISTANCE(1) dist 
from us_cities 
where sdo_nn(location, SDO_GEOMETRY(1, 8307, SDO_POINT_TYPE( -88.709356 , 32.378097, NULL), NULL, NULL), 'distance=186.41 unit=mile', 1) = 'TRUE';

        ID CITY                 STATE_       DIST
---------- -------------------- ------ ----------
        78 Jackson              MS     87.7233084
        79 Mobile               AL     122.755214
        60 Birmingham           AL     136.350962
        86 Montgomery           AL     141.826147
        25 New Orleans          LA     174.954489

5 rows selected.

If all you want is to search for places within a radius, then use the SDO_WITHIN_DISTANCE operator instead.

0

You will need to to use degrees to radians and radians to degrees..

> SELECT * FROM ( SELECT zip, primary_city, state,
>        latitude, longitude, distance   FROM (  SELECT z.zip, z.primary_city, z.state,
>         z.latitude, z.longitude,
>         p.radius,
>         p.distance_unit
>             * rad2deg * (ACOS(COS(deg2rad * (p.latpoint))
>                             * COS(deg2rad * (z.latitude))
>                             * COS(deg2rad * (p.longpoint - z.longitude))
>                             + SIN(deg2rad * (p.latpoint))
>                             * SIN(deg2rad * (z.latitude)))) AS distance   FROM zip z   JOIN (
>         SELECT  42.81  AS latpoint,    -70.81 AS longpoint,
>                 50.0 AS radius,        111.045 AS distance_unit,
>                 57.2957795 AS rad2deg,
>                 0.0174532925 AS deg2rad
>           FROM  DUAL
>     ) p ON 1=1   WHERE z.latitude
>      BETWEEN p.latpoint  - (p.radius / p.distance_unit)
>          AND p.latpoint  + (p.radius / p.distance_unit)
>     AND z.longitude
>      BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(deg2rad * (p.latpoint))))
>          AND p.longpoint + (p.radius / (p.distance_unit * COS(deg2rad * (p.latpoint))))  )  WHERE distance <= radius  ORDER BY distance ) WHERE ROWNUM <= 20

source: https://www.plumislandmedia.net/oracle/fast-nearest-location-finder-oracle/

  • There is absolutely no need to manually compute the distances. The distance is automatically computed by the sdo_nn operator and returned by the sdo_nn_distance() function. – Albert Godfrind Jun 28 '18 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.