Search using Geolocation data in MySQL
One of the many things brought to the forefront by HTML5 is Geolocation. It’s easy enough to get someones geolocation data using HTML5 but what do you do with it once you have it? Ideally you’d like to search through some sort of database be it store locations, real estate listings or what have your. This is where most people start to get tripped up.
There’s lots of different techniques for achieving this unfortunately they don’t seem to ever be explained very well as they relate to MySQL. Until now… dun dun dun dahhh.
The two most common technique are the Haversine formula and the Spherical Law of Cosines. The method I’ve chosen to use in my example is the Spherical Law of Cosines. The formula is a bit short and uses fewer mathematical functions.
The example is comprised of two parts. The first is the Spherical Law of Cosines. The second part is defining a search radius. Defining this search radius will significantly narrow the scope of our search. This is a good thing.
Alright enough chitchat. Here’s the sample MySQL query. It’s built in PHP below but can easily be converted to your language of preference.
Note: I’m Canadian so the example below is done using kilometers. If you want miles you’ll need to multiple $earths_radius and $surface_distance_coeffient by 0.6214 to convert them to miles.
<?php | |
// Search parameters | |
$lat = 45.411572; | |
$lng = -75.698194; | |
$radius = 25; | |
// Constants related to the surface of the Earth | |
$earths_radius = 6371; | |
$surface_distance_coeffient = 111.320; | |
// Spherical Law of Cosines | |
$distance_formula = "$earths_radius * ACOS( SIN(RADIANS(latitude)) * SIN(RADIANS($lat)) + COS(RADIANS(longitude - $lng)) * COS(RADIANS(latitude)) * COS(RADIANS($lat)) )"; | |
// Create a bounding box to reduce the scope of our search | |
$lng_b1 = $lng - $radius / abs(cos(deg2rad($lat)) * $surface_distance_coeffient); | |
$lng_b2 = $lng + $radius / abs(cos(deg2rad($lat)) * $surface_distance_coeffient); | |
$lat_b1 = $lat - $radius / $surface_distance_coeffient; | |
$lat_b2 = $lat + $radius / $surface_distance_coeffient; | |
// Construct our sql statement | |
$sql = <<<SQL | |
SELECT *, ($distance_formula) AS distance | |
FROM listings | |
WHERE (latitude BETWEEN $lat_b1 AND $lat_b2) AND (longitude BETWEEN $lng_b1 AND $lng_b2) | |
HAVING distance < $radius | |
ORDER BY distance ASC | |
SQL; |
If your interested in digging deeper into the theory and math behind the Spherical Law of Cosines or the Haversine formula checkout the Movable Type Scrips site they have tons of background information.