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.
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.
Thank you so much for this! This saved me so much time, you are the man!
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?
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.
Thanks Sir for sharing :D it helps a lot