Determining Distance With Latitude and Longitude in MySQL

When I was programming an application for a marketing company trying to determine where the greatest concentration of registrars where, it made sense to compare the distance from our home base, and with, at the time, release of the Google Maps API, we where able to plot those locations on the map and at the same time sort them by distance (from closest to farthest).

System Requirements

To get things started, here is what we need:

1. A server with PHP (5 or newer, but not necessary) and MySQL (again newer the better).
2. A zip code database with both latitude and longitude attached to the zip codes
3. Optional: A Google Maps key.

We have the server. The zip code database we purchased from http://www.zipcodedownload.com/ with the premium or better having the latitude and longitude we needed.

So we have to upload the database we purchased into the MySQL server, this in and of itself has been an issue.

For example, for our client who uses GoDaddy, we actually had to have the database uploaded and level 2 tech support load it for us, as the 100 MB database table was too large to upload without a script/mysql timeout.

Load the latitude and longitude

Once you have that, move on to the Query.

First find the latitude and longitude of the zip code of the starting location.

SELECT * FROM postalcodes WHERE PostalCode = %s AND  CityType='D'

CityType – is set because it seems many zip codes have many sub cities with different name, while the D types are the primary “official” name.

$StartLat = $row_lupCenter['Latitude'];
$StartLon = $row_lupCenter['Longitude'];

Get the list sorted by distance

So now we have the latitude and longitude of the start. We can even plot this on a map if we want.

Now we are going to go back to the database and compare the stored zips to the one in the center.

SELECT loc.LocationName,(3963.0 *  acos(sin(".$StartLat."/57.2958) *   sin(fz.latitude/57.2958) + cos(".$StartLat."/57.2958) *  cos(fz.latitude/57.2958) * cos(fz.longitude/57.2958 - ".$StartLon."/57.2958)))   as miles FROM locations loc INNER JOIN postalcodes fz ON  LEFT(loc.ZipCode,5) =  fz.PostalCode, postalcodes dz  WHERE   (dz.PostalCode = '" . $DistanceLOC . "') ORDER BY miles ASC

This will return our list of locations, with miles ascending.

And there you go!

Here it is in action: http://www.askpatty.com/dealer_search.php?b=&m=150&q=85014

If you find this helpful, or have suggestion to improve the above, post a comment below!