Calculating Postcodes within a given Radius

15:14.57 - Sunday 27th August 2006   (Link to This Entry)

So, I was mooching about on t'Internet with ZiKeS this morning, and I remembered that I had wanted to create some kind of Postocode Range Finder using the UK Postcode Database I'd snagged from jibble.org a long, long time ago.

The basic idea was to take a postcode, add a range, and return every postcode within that radius. Initially I was having nightmares about SIN() and COS() functions, but it turns out O-Level maths had some use after all, in the form of Pythagoras' Triangle Thigummybob, the one that goes 'The square of the side opposide the right-angle is equal to the sum of squares of the other two sides'. Something like that - we're reaching into parts of my brain that have gone unused for quite some time here.

I figured I'd let the server do all the work, and the final SQL I came up with was:

SELECT POSTCODE,GRIDREFX,GRIDREFY FROM ukpostcodes
WHERE SQRT(
  POW(ABS($xpos-GRIDREFX),2) +
  POW(ABS($ypos-GRIDREFY),2)
) < $dist

$dist being the range required in metres and $xpos and $xpos being the Grid Ref X and Y thingies respectively from my 'root' postcode. The script seems to work, and returns the other postcodes within my specified range, though in no particular order. I guess 'nearest first' would be cool.

My next problem is to actually apply this to something, since I don't actually have a project in mind for it. Initially I thought of something like Dating or maybe Car/House sales, or even an entire community thing which would let you see exactly what was available in your area. Stay tuned, folks - anything could happen in the next half hour few months.