Find postcodes within certain radius of a given postcode

Hi Guys!

I have been asked to write a script that gets a list of postcodes within a certain radius (i.e 10 miles) from a given postcode. For example, I have a full database of UK postcodes along with their eastings and northings data. Now if I type SW1 and want to find a list of postcodes within a 10 mile radius of SW1 how would I do that?

Here is some example SQL data.

Eastings = column 11
Northings = column 12

``````
INSERT INTO `uk_postcodes` VALUES('B65 9EJ', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397103, 287734, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9EL', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397014, 287575, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9EN', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397146, 287233, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9EP', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397091, 287403, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9EQ', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397109, 287557, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9ER', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397223, 287103, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9ES', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397212, 287277, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9ET', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397251, 287295, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9EU', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397149, 287476, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9EW', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397198, 287232, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9EX', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397197, 287495, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9EY', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397262, 287445, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9EZ', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397372, 287385, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9FF', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 396984, 285664, '064', 'Y51', 'Q34', '00', 'CR', 'GK', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9HA', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397311, 287288, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9HB', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397353, 287292, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9HD', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397429, 287291, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9HH', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397283, 286955, '064', 'Y51', 'Q34', '00', 'CS', 'GC', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9HJ', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397135, 287309, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');
INSERT INTO `uk_postcodes` VALUES('B65 9HP', '10', 'X', '-99', '-99', '-99', '-99', '-99', '-99', '-99', 397316, 288171, '064', 'Y51', 'Q34', '00', 'CS', 'GS', 'X');

``````

There is a really complicated formula for calculating the distance - I have it on mywork computer which I’m not in front of right now, but try googling “mysql longitude distance formula” or something to that effect

http://www.sitepoint.com/forums/php-34/find-closest-location-near-your-address-482470.html I had to hack it around a bit, but it works