MySQL Distance Between Two Locations

Posted by Tully on Fri 07 January 2011

Problem:

I need to return table rows within a set amount of miles from given latitude/longitude.

Solution

1. Create a MySQL function that takes coordinates of location A and location B and returns the distance in miles from A to B.
2. Write a MySQL select statement that uses the newly created function to return rows where distance is less then X miles.

Reference

Calculate distance between two points on a globe.

MySQL Function

DELIMITER //
CREATE FUNCTION `GetDistance`(
lat1  numeric (9,6),
lon1  numeric (9,6),
lat2  numeric (9,6),
lon2  numeric (9,6)
) RETURNS decimal(10,5)

BEGIN
DECLARE  x  decimal (20,10);
DECLARE  pi  decimal (21,20);
SET  pi = 3.14159265358979323846;
SET  x = sin( lat1 * pi/180 ) * sin( lat2 * pi/180  ) + cos(lat1 *pi/180 ) * cos( lat2 * pi/180 ) * cos(  abs( (lon2 * pi/180) - (lon1 *pi/180) ) );
SET  x = acos( x );
RETURN  ( 1.852 * 60.0 * ((x/pi)*180) ) / 1.609344;
END //
DELIMITER ;

MySQL Query

SELECT
City,
GetDistance(Latitude,
Longitude,
(SELECT Latitude from hotels where City = 'Pasadena' and StateProvince = 'CA' limit 1),
(SELECT Longitude from hotels where City = 'Pasadena' and StateProvince = 'CA' limit 1)) as Distance
FROM
hotels
WHERE
StateProvince = 'CA'
GROUP by
City,
StateProvince
HAVING
Distance < 10
ORDER BY
Distance DESC;

MySQL Query Response

+----------------+----------+
| City           | Distance |
+----------------+----------+
| Duarte         |  9.83023 |
| Monrovia       |  9.03029 |
| El Monte       |  8.99051 |
| South El Monte |  8.80477 |