-- --------------------------------------------------------------------------- -- Great circle distance -- -- from the Artful Common Queries page -- http://www.artfulsoftware.com/infotree/qrytip.php?id=109 -- --------------------------------------------------------------------------- -- Find the distance in kilometres between two points on the surface of the -- earth. This is just the sort of problem stored functions were made for. -- For a first order approximation, ignore deviations of the earth's surface from -- the perfectly spherical. Then the distance in radians is given by a number -- of trigonometric formulas. ACOS and COS behave reasonably: -- COS(lat1-lat2)*(1+COS(lon1-lon2)) - COS(lat1+lat2)*(1-COS(lon1-lon2)) -- rads = ACOS( --------------------------------------------------------------------- ) -- 2 -- We need to convert degrees latitude and longitude to radians, and we need to -- know the length in km of one radian on the earth's surface, which is 6378.388. -- The function: set log_bin_trust_function_creators=TRUE; DROP FUNCTION IF EXISTS GeoDistKM; DELIMITER | CREATE FUNCTION GeoDistKM( lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS float BEGIN DECLARE pi, q1, q2, q3 FLOAT; DECLARE rads FLOAT DEFAULT 0; SET pi = PI(); SET lat1 = lat1 * pi / 180; SET lon1 = lon1 * pi / 180; SET lat2 = lat2 * pi / 180; SET lon2 = lon2 * pi / 180; SET q1 = COS(lon1-lon2); SET q2 = COS(lat1-lat2); SET q3 = COS(lat1+lat2); SET rads = ACOS( 0.5*((1.0+q1)*q2 - (1.0-q1)*q3) ); RETURN 6378.388 * rads; END; | DELIMITER ; -- toronto to montreal (505km): select geodistkm(43.6667,-79.4167,45.5000,-73.5833); -- +----------------------------------------------+ -- | geodistkm(43.6667,-79.4167,45.5000,-73.5833) | -- +----------------------------------------------+ -- | 505.38836669921875 | -- +----------------------------------------------+ -- (Setting log_bin_trust_function_creators is the most convenient way to step -- round determinacy conventions implemented since 5.0.6.)