Finding and Ordering Latitude and Longitude in SQL

Question

I have a SQL database where I store longitude and latitude from an iPhone application. I need to query all the records starting from a given location to the other far most location.

For example, I have longitude x and latitude y. I want all the records first whose longitude matches x the most closely and whose latitude matches y the most closely. I need to all the records one by one in the chain from nearest to farthest. The more distant the location, the greater the value of longitude and latitude will be than x and y.

I hope you got the point and I am waiting for the answer.

Answer 1

Assuming that you've queried the start location lat/lon and location ID... I am using $LAT, $LON, and $ID as placeholders:

select locID, locName, locDesc, lat, lon, locDiff
from (
    select locID, locName, locDesc, lat, lon, ABS(lat - $LAT) + ABS(lon - $LON) as locDiff
      from locationTable
    where locID <> $ID
) a
order by locDiff

Hopefully this helps... may not be the most optimized method, but it should be pretty close.

Answer 2

Similar to Fosco, but using Pythagoras' Theorem:

select locID, locName, locDesc, lat, lon, locDiff from
(select locID, locName, locDesc, lat, lon, 
 sqrt((lat - $LAT)*(lat - $LAT) + (lon - $LON)*(lon - $LON)) as locDiff
 from locationTable
 where locID <> $ID) a
order by locDiff

For really large distances (or locations far from the equator) you should ideally use a geodesic.

Answer 3

Distance with latitude and longitude is not a simple calculation, but one requiring spherical trigonometry.

acos(cos(lat1)*cos(lon1)*cos(lat2)*cos(lon2) + 
     cos(lat1)*sin(lon1)*cos(lat2)*sin(lon2) + 
     sin(lat1)*sin(lat2)) * R(adius of the earth)

So this query

select locID, locName, locDesc, lat, lon, locDiffMeters   
from (select locID, locName, locDesc, lat, lon, 
             acos(cos($lat)*cos($lon)*cos(lat)*cos(lon) + 
                  cos($lat)*sin($lon)*cos(lat)*sin(lon) + 
                  sin($lat)*sin(lat) ) * 6,371,000 -- earths radius in meters
               as locDiffMeters
      from locationTable    
    where locID <> $ID    
) a    
order by locDiffMeters    

Is probably the right answer, assuming you have that capable of a math library.

Answer 4

If you don't have to deal with large distances (see Adam's answer), you might consider using PostgreSQL's geometric types and associated functions.

Answer 5

If you're using Postgresql, add the PostGIS extension and check out the ST_Distance_Sphere

ST_Distance_Sphere — Returns minimum distance in meters between two lon/lat geometries. Uses a spherical earth and radius of 6370986 meters. Faster than ST_Distance_Spheroid, but less accurate. PostGIS versions prior to 1.5 only implemented for points.

SELECT round(CAST(ST_Distance_Sphere(ST_Centroid(the_geom), ST_GeomFromText('POINT(-118 38)',4326)) As numeric),2) As dist_meter ...