Since Ordnance Survey released their data to the public, anyone with some basic programming skills can make use of it to make a simple UK postcode to latlong lookup table. My partner and I couldn't resist this opportunity, so here's how we did it.

You may be wondering why we didn't simply use the Google Maps API to do all this for us. Partly because the Ordnance Survey data is more accurate, but mostly because we can import the data into our local system, so that we can use it as often as we like. Google has a limit of 2,500 geocode requests per 24 hours, and my boss for one has a lot more customers than that in her database.

So, on with the importing. First, you'll need a copy of the catchily titled codepo_gb_wgs84.zip. I'm not quite sure why you can't download it via a direct link to http://www.ordnancesurvey.co.uk/, given its Creative Commons license. Not to worry, it's mirrored on various other websites.

Once you've got the raw data, unzip it and take a look. We're interested in the codepo_gb_wgs84/data/CSV directory, which unsurprisingly contains a bunch of CSV files. Move them all into an empty directory that Apache can see. It's time to make a PHP script! Here's my humble import.php script for you to use as you wish:

<?php

set_time_limit(120);
mysql_connect('localhost', 'root', 'root');
mysql_select_db('foo');

$files = scandir('.');

foreach ($files as $file) {
if (strlen($file) < 5 || substr($file, -4) != '.csv') {
continue;
}

echo "Reading {$file}<br>";
$handle = fopen($file, 'r');

while ($row = fgetcsv($handle)) {
$postcode = $row0;
$latitude = round($row2, 7);
$longitude = round($row3, 7);
mysql_query("INSERT INTO ordnance_survey_locations SET postcode = '{$postcode}', latitude = {$latitude}, longitude = {$longitude}");
}

fclose($handle);
}

?>

I've trimmed the latlong coordinates to a mere seven significant digits, for 1.11cm accuracy.

If you don't see all the filenames listed when you run the script, ending in ze.csv, increase the timeout at the beginning and try again.

Normally you'd want to comment your code, protect yourself from SQL injection attacks, and indulge in other niceties that I've foregone in my import script. I've only let myself be so lazy because we're just going to use it once then throw it away, without it ever being available on a publicly accessible server.

You'll also need to make a database table to store the data:

CREATE TABLE ordnance_survey_locations (
id mediumint(8) unsigned NOT NULL auto_increment,
postcode char(7),
latitude decimal (10,7),
longitude decimal (10,7),
primary key (id),
KEY postcode (postcode)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

If you're not using MySQL or don't like MyISAM tables, feel free to adjust this to taste. Note that I've indexed the column storing the postcodes, so that the time spent looking one up gets significantly reduced. In a simple test, the time was reduced from about 0.5 seconds to about 0.05 seconds.

Once you've created the table, just run the script and let it populate the database with all the data. You should now have a table full of all 1,696,088 UK postcodes and their equivalent latlong coordinates, perfect for interfacing with the Google maps API and probably several other cool things too.

We're almost ready for the fun part, but first let's familiarise ourselves a bit more with the UK postcode format.

All UK postcodes are made up of two different parts, the postcode district on the left and the delivery point on the right. The former consists of two to four alphanumeric characters, and is space padded by two, one or zero spaces, so that it always takes up exactly four characters in total. The latter is always exactly three alphanumeric characters.

This is fine for Royal Mail's highly trained staff, but the general public are likely to put the space in the wrong position, so I'd recommend using something like this to correct any potential mistakes on the user's part:

// Tidy up the postcode
$postcode = str_replace(' ', '', strtoupper($postcode));
$postcode = str_pad(substr($postcode, 0, -3), 4, ' ', STR_PAD_RIGHT).substr($postcode, -3);

This will ensure that what you're trying to match, assuming the user hasn't gotten anything other than whitespace wrong, will be a valid postcode.

That should be everything you need to convert your users' postcodes into latlong coordinates, perfect for plotting all your customers on a map to find out where you should open your next shop, for instance. Have fun thinking up creative ways to use this information!

Log in or register to write something here or to contact authors.