Oracle spatial search within distance

Question

I have the following table Cities:

ID(int),City(char),latitude(float),longitude(float).

Now based on a user`s longitude(ex:44.8) and latitude(ex:46.3) I want to search for all the cities near him within 100 miles/KM.

I have found some examples but don`t know how to adapt them to my case

select *
from GEO.Cities a
where SDO_WITHIN_DISTANCE([I don`t know],
MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(44.8,46.3, NULL) ,NULL, NULL), 
'distance = 1000') = 'TRUE';

Any help would be appreciated.

P.S: If it is possible to have the distance and to be sorted

P.P.S: I want to do it in this way due to performance issues, I have done this in this way http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL but it takes too long...

Answer 1

You have a pretty good reference there for mySQL distance search.

Forget about the Oracle Spatial stuff. Too much code, too much complexity, not enough value-add.

Here's a query that will do the trick. This uses distances in statute miles. EDIT This fixes the bug mentioned by mdarwin, at the cost of divide-checking if you try to use it for a location at the north or south pole.

  SELECT id, city, LATITUDE, LONGITUDE, distance
    FROM
  (
    SELECT id, 
           city, 
           LATITUDE, LONGITUDE,
           (3959 * ACOS(COS(RADIANS(LATITUDE)) 
                 * COS(RADIANS(mylat)) 
                 * COS(RADIANS(LONGITUDE) - RADIANS(mylng)) 
                 + SIN(RADIANS(LATITUDE)) 
                 * SIN(RADIANS(mylat)) 
               ))
           AS distance,
           b.mydst
      FROM Cities
      JOIN (
        SELECT :LAT AS mylat,
               :LONG AS mylng,
               :RADIUS_LIMIT AS mydst
          FROM DUAL
      )b ON (1 = 1)
     WHERE LATITUDE >=  mylat -(mydst/69)
       AND LATITUDE <=  mylat +(mydst/69)
       AND LONGITUDE >= mylng -(mydst/(69 * COS(RADIANS(mylat))))
       AND LONGITUDE <= mylng +(mydst/(69 * COS(RADIANS(mylat))))
  )a
   WHERE distance <= mydst
   ORDER BY distance

If you're working in kilometers, change mydst/69 to mydst/111.045, and change 3959 to 6371.4. (1/69 converts miles to degrees; 3959 is a value for the radius of the planet.)

Now, you'll probably be tempted to use this large query as a "magic black box." Don't do it! It's not very hard to understand, and if you do understand it you'll be able to do a better job. Here's what's going on.

This clause is the heart of what makes the query fast. It searches your Cities table for nearby cities to the point you specified.

     WHERE LATITUDE >=  mylat -(mydst/69)
       AND LATITUDE <=  mylat +(mydst/69)
       AND LONGITUDE >= mylng -(mydst/(69 * COS(RADIANS(mylat))))
       AND LONGITUDE <= mylng +(mydst/(69 * COS(RADIANS(mylat))))

For it to work, you definitely need an index on your LATITUDE column. An index on your LONGITUDE column will also help a bit. It does an approximate search, looking for rows that are within a quasi-rectangular patch on the surface of the earth near your point. It selects too many cities, but not far too many.

This clause here lets you eliminate the extra cities from your result set:

   WHERE distance <= mydst

This clause is the haversine formula which calculates the great-circle distance between each city and your point.

           (3959 * ACOS(COS(RADIANS(LATITUDE)) 
                 * COS(RADIANS(mylat)) 
                 * COS(RADIANS(LONGITUDE) - RADIANS(mylng)) 
                 + SIN(RADIANS(LATITUDE)) 
                 * SIN(RADIANS(mylat)) 

This clause lets you enter your point, and your radius-limit, just once as bound variables to your query. It's helpful because the various formulas use those variables multiple times.

        SELECT :LAT AS mylat,
               :LONG AS mylng,
               :RADIUS_LIMIT AS mydst
          FROM DUAL

The rest of the query simply organizes things so you select and order by distance.

Here is a more complete explanation: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/