Re: Postcode proximity classes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux