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

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"

ReplyDeleteDo you know how I can fix this?

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

ReplyDeleteNote, the distance given is in miles