On Fri, July 28, 2006 12:06 pm, Dave Goodchild wrote: > Hi all. I am about to start writing an events listing application > (nationwide) and want users to be able for example to specify events > within > a 5, 10 and 15 mile radius of their postcode. Does anyone know of a > set of > classes/library that can provide this, would rather not fork out on a > bespoke piece of kit. Any suggestions appreciated! Having done this before, several times, several different ways... If it's US nationwide you can snarf the TIGER data for free to get: zip code <-> longitude/latitude You can then write a crude distance function just doing a Cartesian distance with a 0.6 factor to convert degrees of arc to miles: $distance = 0.6 * sqrt(pow($long2 - $long1, 2) + pow($lat2 - $lat1, 2)) This is of course utter [bleep] for an accurate calculation of distance due to curvature of the Earth, but for finding stuff within N miles of their postcode it's "okay" for US. Just don't try to fly an airplane or pilot a boat by it. You may even be able to create a function in your database of choice to make it less messy. Note that the number of zip codes is around 60K in the TIGER data, and there have been a whole mess of zip codes added since then. So if you want ALL the current zip codes, you have to shell out money. :-( What I do, however, is when I run across a zip code that isn't in the db, I just "guess" from the city/state and code number what other zip codes are "near" it and then average those long/lat and shove it in the db. Again, this is woefully inaccurate from a purist point of view, but for calculating what's within N miles, it's fine and dandy. And it don't cost me anything but a few minutes every month a new zip code pops up. Anyway, with such large tables, the query to calculate the distance in a JOIN from your events to your zips table can get quite expensive. So now it's time for a totally immoral non-denormalized freak-out-the-DBA hack: Add a longitude and latitude column to your events table, right after the zipcode column, and default them to NULL. Any time a zipcode changes, reset the longitude/latitude on events to NULL. Write a cron job that finds any events with NULL longitude and latitude and copies the long/lat from the zips to the events for all events with that same zip. You'll want to run this cron job pretty often, especially if you have a bunch of existing events that need to get initialized, but it's a pretty cheap operation. This cron job can notify you any time there is an unknown zip in the events table that's not in the zips table, and then you can fake up the long/lat as described above. There are all manner of far more complicated ways of doing this, but they are total overkill for what you need. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
