Wednesday, December 12, 2012

Find the nearest location by latitude and longitude in MySQL using the Haversine formula

If you need to find the nearest location of any point of interest by latitude and longitude in MySQL, then you can use the Haversine formula. Following here is and example that assumes you have a table named "destinations" which contains amongst other things, the latitude and longitude of those 'destinations'. An example of valid destinations in a real-world application would be a table listing hotels and their lat/long coordinates which would enable one to write a query to return the nearest hotels from that table based on your lat/long coordinates. (The distance provided is in miles. To get the distance in kilometers, exchange 3539 with 6371)


SET @my_lat = 35.229205 ; -- the source latitude
SET @my_lon = -114.26811 ;-- the source longitude

SELECT *, ( 3959 * acos( cos( radians(
-- latitude
@my_lat) ) * cos( radians( 
destination.latitude ) ) * cos( radians( 
destination.longitude ) - radians(
-- longitude
@my_lon) ) + sin( radians(
-- latitude
@my_lat) ) * sin( radians( 
destination.latitude ) ) ) ) AS distance 
-- table containing targets to compare distance
FROM destination
ORDER BY distance LIMIT 1 ;

Full example at SQL Fiddle ---> http://sqlfiddle.com/#!2/abba1/4/0 ( PLEASE DONATE )

I recently needed to use this to find the official timezone for a city closest to a person based on their latitude, longitude coordinates in MySQL, so as not to have to depend on look-ups to a web service. For anyone interested, city-timezone data can be downloaded from the geonames.org website and loaded as the 'destinations' table.

Reference:
http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula

6 comments:

  1. I keep getting error from this."You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Long ) - radians( -71.7559) ) + sin( radians( 42.1365) ) * sin( radians( Lat ) ' at line 4"

    Do you know how I can fix this?

    ReplyDelete
  2. Sure, check out a working sample here. It correctly shows that Las Vegas is close to Los Angeles than Reno: http://sqlfiddle.com/#!2/abba1/2/0
    Note, the distance given is in miles

    ReplyDelete
  3. How should I do if I wanted the distance to be Kilometre? Could you please show me?

    ReplyDelete
    Replies
    1. To search by kilometers instead of miles, replace 3959 with 6371.

      Delete
    2. To search by kilometers instead of miles, replace 3959 with 6371.

      Delete