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
Thanks for you contribution.
ReplyDeleteHow should I do if I wanted the distance to be Kilometre? Could you please show me?
ReplyDeleteTo search by kilometers instead of miles, replace 3959 with 6371.
DeleteTo search by kilometers instead of miles, replace 3959 with 6371.
Delete