Calculating Distances Between Addresses
Here’s a basic rundown of one way (there are many) to build a store locator.
Essentially it boils down to three steps:
- Geocode a table of addresses to latitude and longitude coordinates using the Google Maps API (only need to do this once).
- Geocode a query string to get its latitude and longitude coordinates.
- Query addresses from DB by comparing latitude and longitude using the Haversine formula.
- Bask in the warm glow of trigonometry.
Let’s start with the DB table. Since we’re going to be querying based on latitude and longitude, those two fields should be set as keys. Your MySQL server will probably cry if you don’t do this. And your queries may be noticeably slow.
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE TABLE `stores` ( `id` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT, `store_name` VARCHAR(64) DEFAULT NULL, `store_address` VARCHAR(128) DEFAULT NULL, `store_city` VARCHAR(64) DEFAULT NULL, `store_state` VARCHAR(64) DEFAULT NULL, `latitude` DECIMAL(10,7) DEFAULT NULL, `longitude` DECIMAL(10,7) DEFAULT NULL, PRIMARY KEY (`id`), KEY `lat_key` (`latitude`), KEY `lng_key` (`longitude`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; |
Populate your table of stores, leaving the latitude and longitude fields blank. Then, you can fill those fields by looping through the table, pinging them against the Google Maps API, and popping off AJAX calls to update your DB table:
(You’ll need to include the Maps API Javascript, and jQuery will help you with the AJAX calls.)
1 2 | <script type="text/javascript" src="http://maps.googleapis.com/maps/api/js?sensor=false"></script> <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.js"></script> |
You’ll want to query your table of stores, assembling an array of address strings, and encoding them to a JSON array or something using the row IDs as the keys. Then, loop through it:
1 2 3 4 5 6 7 8 9 10 11 12 | var geocoder = new google.maps.Geocoder(); for (var index in addresses) { geocoder.geocode({ 'address':addresses[index] }, function(results, status) { if (status == google.maps.GeocoderStatus.OK) { latitude = results[0].geometry.location.lat(); longitude = results[0].geometry.location.lng(); $.post("insert.php", { id:index, lat:latitude, lng:longitude }, function(data) { console.log(data); }); } }); } |
You can update a DB, right? Your insert.php would look something like this:
1 2 3 4 5 6 | $db = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASSWORD); $q=$db->prepare('INSERT INTO stores (latitude,longitude) VALUES (?,?) WHERE id = ?'); $q->bindParam(1,$_POST['lat']); $q->bindParam(2,$_POST['lng']); $q->bindParam(3,$_POST['id']); $q->execute(); |
If you hate Javascript (or just suck at it), you could go with this super simple PHP route to get latitude and longitude from Google (thanks Weston):
1 2 3 4 5 | $geocode = file_get_contents('http://maps.google.com/maps/api/geocode/json?address=/'.$address.'&sensor=false'); $output = json_decode($geocode); $lat = $output->results[0]->geometry->location->lat; $lon = $output->results[0]->geometry->location->lng; // Insert to DB... |
That’s step one, the hardest part, the part that only needs to be done once. Once you’ve got that groundwork done, your DB is ready for real user queries.
First, take the user’s input query and Geocode it, using either method above (Javascript or PHP), to get the latitude and longitude. Once you have those, you can do one clean MySQL query to get locations ordered by distance (replace QUERY_LAT and QUERY_LON with the latitude and longitude returned from Google, respectively):
1 | SELECT *, (3959 * acos(cos(radians(QUERY_LAT)) * cos(radians(latitude)) * cos(radians(longitude) - radians(QUERY_LON)) + sin(radians(QUERY_LAT)) * sin(radians(latitude)))) AS distance FROM stores ORDER BY distance; |
Fuck yeah, math.
