[Phpwm] Cleaning up addresses

Phil Beynon phil at infolinkelectronics.co.uk
Tue Mar 20 12:18:23 GMT 2007

Hi Dave,

> I thought so Phil, my original assumption is correct this can be achieved
> very easily as I have implemented just this functionality in a number of
> sites, one of which is now maintained by Darren @ siftware. Basically they
> have a trader register which allows the user to enter their postcode and
> search for a builder for example.
> The system them runs a query on the database to generate a list of traders
> showing the closest first with an approximate distance, all of
> this was done
> by the trader simply entering their postcode when they register. i.e. the
> whole thing works based on postcode proximity.

Thats another way of doing it I agree, though there are a few situations
where mine is going to definitely work better.

> To achieve this you need a file which can be licensed for £500 at
> a reseller
> rates

Where from?

> then you just need a source postcode and postcode of the traders in
> the MySQL snip below table d is the search origin postcode and the table e
> is a company or your example traders postcode, results can be filtered to
> the most appropriate by ordering on distance and then using the limit
> statement.

Sure, I can see thats cheaper and less labour intensive than the way we did
it. :-)
We relied on multimap for distances / directions.

> <MySQL>
> (acos(sin(radians(d.latitude)) * sin(radians(e.latitude)) +
> cos(radians(d.latitude)) * cos(radians(e.latitude)) *
> cos(radians(d.longitude) - radians(e.longitude)))) * 3437.7387 *
> 1.150779 as
> mileage
> </MySQL>
> Took around a day to get the search working right and this is something I
> have used time and time again to provide proximity stuff for my clients.
> More importantly the results are generated in milliseconds using a single
> query

This was one query per postcode expansion, since you have to individually
run the math on all your table entries to get the distances I'm not sure in
a big database which would be faster, yours would certainly give the server
a bigger memory spike which on a busy site could be noticeable.

If I was doing it your way I think I would want to pre-generate a postcode
exclusion table based on exceeding a certain distance.


More information about the Phpwm mailing list