Tuesday, October 5, 2010

Google Maps distanceFrom in MySQL

So, step two of the website I was working on here was to actually search my MySQL database for points within a given distance of a second point. I simply converted the Haversine formula to a MySQL query:

SELECT *
FROM Point
WHERE
6378137
*
(
2
*
ATAN2(
SQRT(
SIN((Point.Lat - 59.332526) * PI() / 180)
*
SIN((Point.Lat - 59.332526) * PI() / 180)
+
COS((59.332526 * PI() / 180))
*
COS((Point.Lat * PI() / 180))
*
SIN(((Point.Lng - 18.064091) * PI() / 180) / 2)
*
SIN(((Point.Lng - 18.064091) * PI() / 180) / 2)
),
SQRT(
1
-
(
SIN((Point.Lat - 59.332526) * PI() / 180)
*
SIN((Point.Lat - 59.332526) * PI() / 180)
+
COS((59.332526 * PI() / 180))
*
COS((Point.Lat * PI() / 180))
*
SIN(((Point.Lng - 18.064091) * PI() / 180) / 2)
*
SIN(((Point.Lng - 18.064091) * PI() / 180) / 2)
)
)
)
) <= 300 -- 300 meters

In the above example I use the LatLng position of central Stockholm (59.332526, 18.064091) as the base point and select all points within a 300-meter radius from it. Of course this can be simplified, but you get the Point.