MySQL UK post code table with corresponding longitude and latitude

(Edit March 2016) Please note the freemaptools site now maintains a MySQL version so it would probably be best to use theirs instead.

One of the projects I have been working on recently involved the calculation of the distance between two postcodes. Google provides a decent API and retrieving the distance between two post codes with map routing is very simple. For example to calculate the distance between Fountains Abbey and Northallerton high street using their postcodes would only require:

https://maps.googleapis.com/maps/api/distancematrix/json?origins=HG43DY&destinations=DL78LW&sensor=false&units=imperial

The downside to this is that there are request limits imposed on this API and you can only make a certain amount before you need to hand over some cash to Google. Due to the nature of the project, I realized these limits would quickly be met and as a result found the Google API to be unsuitable.
An alternative would be to find a database with all the UK post codes and their longitude and latitude coordinates. You could then easily lookup the coordinates of a post code and pass them to a routing engine (e.g. Open Source Routing Machine) to calculate distances by road or potentially just a simple ‘as the crow flies’ distance calculation using trigonometry.Open Source Routing Machine Logo

It didn’t take long to find the fantastic freemaptools.com which has a csv containing all UK postcodes and their coordinates. Unfortunately they only had an MSSQL version so I whipped up a MySQL version which worked nicely and is just below for your perusal and use. The same goes for the output the script generates:

Output of the script: mysql_postcodes.zip (35M) Last updated 01/07/2014

The PHP script on github

Readme

PHP code for taking the csv found at http://www.freemaptools.com/download-uk-postcode-lat-lng.htm and importing it into a mysql database. You can use the script in two ways:

  1. Fill in the database details at the start of the file and run the queries as they are built.

    php import.php postcodes.csv

  2. Specify ‘echo’ as the 2nd argument and output the sql to a file.

    php import.php postcodes.csv echo > mysql_postcodes.sql