Computing distance in miles

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.

4 thoughts on “Computing distance in miles

  1. Amazing work! I’m not sure that I’ll be able to use this in the PHP app I’m working on (with the multiple statements), nor am I entirely sure that it would be possible with one statement. In any case, just wanted to comment on your excellent work here with this.

  2. I need to query by the great circle distance like how many people whose distance to me are within 3 kilo-meters. It’s is possible to create fucntion index like Oracle to fast the query?

  3. I think you have mixed up miles and meters. Your text says you’re passing the radius in meters but your code actually passes the miles. The radius of the earth in meters is actually about 6371000 meters.

What do you think?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s