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.
We managed to get in a fine game of flag football in this morning. Thanks to Wes and Josh for rounding up enough able bodies to play as well as providing flags and cones for a respectable game.
Just got done playing one of the finer DS games I’ve played in a while, Mario & Luigi: Bowser’s Inside Story. This well crafted game provided much entertainment for a couple of weeks, such as while waiting for my car to be serviced. btw I will always bring a game to pass the time now when I’m getting the car serviced.