Great circle distance in MySQL

-- ---------------------------------------------------------------------------
-- 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.)