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)
    READS SQL DATA

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 |
| Rosemead       |  8.09559 |
| Arcadia        |  6.91626 |
| Glendale       |  6.34357 |
| Monterey Park  |  5.67806 |
| San Gabriel    |  4.90842 |
| Alhambra       |  3.37174 |
| Pasadena       |  0.00000 |
+----------------+----------+
11 rows in set, 9240 warnings (0.36 sec)

Note: The above will return only unique cities that are within 10 miles of Pasadena, CA. To bring back all the matched rows just remove the group by City, StateProvince.