At the MySQL Users Conference, I mentioned that I had a stored function for computing distance in miles/kilometers/etc. from latitude and longitude. Some people have asked me for it, so here you go.

First, a general function for computing great circle distance from the radius of the great circle, and latitude/longitude for each point:

DROP FUNCTION IF EXISTS gc_dist;
CREATE FUNCTION gc_dist (
  radius DOUBLE,
  A_lat DOUBLE,
  A_lon DOUBLE,
  B_lat DOUBLE,
  B_lon DOUBLE
) RETURNS DOUBLE
RETURN (2 * radius * ATAN2(SQRT(@x := (POW(SIN((RADIANS(A_lat)-RADIANS(B_lat)) /
 2), 2) + COS(RADIANS(B_lat)) * COS(RADIANS(A_lat)) * POW(SIN((RADIANS(A_lon) - 
RADIANS(B_lon)) / 2), 2))), SQRT(1 - @x)));

Next, we can use gc_dist() to compute the distance in miles—on Earth—by passing in the radius of the Earth in meters:

DROP FUNCTION IF EXISTS earth_distance_miles;
CREATE FUNCTION earth_distance_miles (
  A_lat DOUBLE,
  A_lon DOUBLE,
  B_lat DOUBLE,
  B_lon DOUBLE
) RETURNS DOUBLE 
RETURN gc_dist(3963.1676, A_lat, A_lon, B_lat, B_lon);

You can also easily write another stored function to do the same using a POINT data type by using the X() and Y() functions within the stored function to extract the latitude and longitude. I’ll leave that as an exercise for the reader.