Return results within X distance of X postcode

homer09001

ok i need a script that will get results from a database based on a user selection.

e.g. the user enters their postcode the script searches the database for results that are within a user specified distance of a given postcode, i have access to an almost complete UK postcode database, i am just unsure how to use this to my advantage.

layout of postcode info i have:

im guessing its set up:
3/4 Area Code | Lat or Long | Lat or long | Town | County

NE35	54.952	-1.46	Boldon Colliery	Co Durham
NE36	54.945	-1.44	East Boldon	Co Durham
NE37	54.913	-1.526	Washington	Co Durham
NE38	54.894	-1.524	Washington	Co Durham
NE39	54.924	-1.759	Rowlands Gill	Co Durham
NE4	54.974	-1.643	Newcastle Upon Tyne	Northumberland
NE40	54.966	-1.772	Ryton	Co Durham
NE41	54.976	-1.819	Wylam	Northumberland
NE42	54.962	-1.854	Prudhoe	Northumberland
NE43	54.948	-1.905	Stocksfield	Northumberland
NE44	54.943	-1.978	Riding Mill	Northumberland
NE45	54.976	-2.016	Corbridge	Northumberland
NE46	54.979	-2.106	Hexham	Northumberland

shu

Try something like this:

SELECT * FROM myTable WHERE 
(POW((69.1*(long-$long) * cos($lat/57.3)), 2) + 
POW((69.1*(lat-$lat)), 2)) < ($radius*$radius)

/*
  lat & long : fields in your db denoting latitude and longitude respectively
  $lat & $long : Lat & Long of your reference location
  $radius : Radius of your search
*/

There's a nice article at PHP Architect that goes into details on implementing this. I don't quite remember the exactly link, you shouldn't have too much trouble finding it with just a little searching on the site.

MarkR

You should bear in mind the current horrendous scheme that Brown et al have regarding postcodes; we pay money (through taxation) to generate postcode data and other data, yet we still (i.e. the general public) are expected to pay top dollar (Thousands of quid per year) to use these data.

Postcode data are only available to subscribers for several thousand per year. What you seem to have are outcodes only (the first part of a postcode) - a lat/long or national grid ref of an outcode is very inaccurate - some cover areas many miles across.

I recommend, if you can, using OS national grid references instead of lat/long, as the calculations are easier and more accurate (in the scope of the UK certainly).

You can pay per query for postcode geocode lookups from postcodeanywhere.com - but remember, you are only paying the government indirectly. Petition Brown to scrap the unfair postcode tax!

Mark

homer09001

the problem is i want a user to search for specific companies within a defined radius of their postcode

MarkR

With lat/long it's terribly difficult. The maths for a great circle route on a sphere isn't that tricky, but the earth isn't a sphere.

If you can get OS national grid coords (eastings/northings) that's much more usable. Having said that, with only outcodes to go on, you're not going to be accurate to more than a few km anyway.

I live in OX2, which covers several different parts of Oxford, some several miles apart.

Mark

homer09001

them postcodes i have i've taken from OSDate, a dating site script that used them to find profile of people who live near you.