Recently a client wanted a search to be performed to return records that were within x amount of miles from a zip code.
Luckily there are a couple of pretty nifty ways of getting this to work with a MySQL db. The first obstacle was that the data didn’t have latitude and longitude coordinates in the db. Luckily there’s a couple of API’s available where we can feed them an address and it will return coordinates. I do recommend the Yahoo! Geo Technologies for this, they’re extremely fast and well documented.
Now if the records do have their coordinates stored in the db, we can make a SQL statement to find the records that are a certain distance from a point.
I found this SQL example here.
SELECT ( (ACOS(SIN(39.339806 * PI() / 180) * SIN(lat * PI() / 180) + COS(39.339806 * PI() / 180) * COS(lat * PI() / 180) * COS((-74.575122 - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance, last_name, first_name, city FROM doctors HAVING distance<=10 ORDER BY distance ASC |
The above example has a starting point hard-coded into it, which can be altered to whatever your starting point is. The HAVING then filters out in miles the records whose value is less than or equal to 10 miles. Again this value can be altered.
Again, thanks to My Random Blog for sharing.