[Phpwm] Cleaning up addresses

Mike Tipping mike at e-msg.co.uk
Tue Mar 20 12:56:26 GMT 2007

Google maps API now allows you to geo code UK addresses to Lat longs so
there is no need to use a licensed file - unless you need the resilience of
not using something that will be in beta for ever more.



-----Original Message-----
From: phpwm-bounces at mailman.lug.org.uk
[mailto:phpwm-bounces at mailman.lug.org.uk] On Behalf Of Dave Holmes
Sent: 20 March 2007 12:31
To: 'West Midlands PHP User Group'
Subject: RE: [Phpwm] Cleaning up addresses

You can get the file from a company call kings wood map mechanics and the
file is called the code point file. 

Server spike is negligible as MySQL eats the maths and in real terms there
are not that many recs. We are doing a lot more heavy duty processing on the
satellite tracking software we have created which handles loads of
concurrent connections from devices communicating via Data calls, SMS and
GPRS. No performance worries yet. Then again it is running across 6 servers
all of which are dual xeons with bags of ram. 

-----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 12:16
To: West Midlands PHP User Group
Subject: RE: [Phpwm] Cleaning up addresses

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.


Phpwm mailing list
Phpwm at mailman.lug.org.uk

Phpwm mailing list
Phpwm at mailman.lug.org.uk

More information about the Phpwm mailing list