[Phpwm] Cleaning up addresses

[Dave Holmes]

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. 

To achieve this you need a file which can be licensed for £500 at a reseller
rates 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.  

<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


-----Original Message-----
From: phpwm-bounces at mailman.lug.org.uk
[mailto:phpwm-bounces at mailman.lug.org.uk] On Behalf Of Phil Beynon
Sent: 20 March 2007 11:23
To: West Midlands PHP User Group
Subject: RE: [Phpwm] Cleaning up addresses

> I really must be missing something here Phil when you say
> adjacent postcodes
> are you using this / were using this to work out the nearest location of a
> relevant resource or service... i.e. you nearest shop is in postcode XYZ
>
> I just be interested to know what the problem was you put the solution in
> for as it still seems overly complicated
>

I can't see why anyone wouldnt want to use this as a solution, since it is
the most elegant.

OK - you are on a building site in Leicester LE5, you need a bag of cement.
You enter the postcode you are in, it searches against the builders
merchants table, it doesn't find one. So it then expands out one ring of
adjacent postcodes and finds 3 builders merchants. Apart from in the
Highlands most of the postcode areas are relatively small, so this is an
efficient way of searching for things.
We had about 370 different types of datasets, and plans for many more -
these were coming form a wide variety of sources and all in slightly
different formats. Using this method all we had to do was explode the first
and second half of the postcodes into seperate fields at which point the
data had an indexable commonality.
Essentially it allowed it to take data and add it in very quickly, or update
existing data without any field preprocessing tailored to that dataset.

So yes, internally it was complicated, which is what computers are good at,
but it could be driven by anyone with about 5 mins of training.

Phil


_______________________________________________
Phpwm mailing list
Phpwm at mailman.lug.org.uk
https://mailman.lug.org.uk/mailman/listinfo/phpwm