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.