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

 

 

8 comments / Add your comment below

  1. Hi Vixre,

    I am looking to use your postcode to postcode distance calculator on my start-up WordPress courier website.
    I have now downloaded the mysql_postcodes.zip files and the PHP script on github from this site.

    Can you please tell me how to set this up on my WordPress website so that I can have a postcode to postcode distance calculator insert on a Page from where a visitor would input their pick-up and delivery postcodes and get a milage calculation on the page.

    Thanking you in anticipation,
    Glen

    1. Hi Glenn, thanks for commenting.

      One way to implement a post code to postcode distance search would be to:

      1. Create an ajax call which posts the two postcodes to a php file.
      2. In the php file take the two postcodes, find their longitude and latitude points by doing a lookup in the table.
      3. Use the the haversine formula to calculate the distance between the two points.
      4. You could also do any cost calculations etc once the distance has been determined.
      4. Return the distance and anything else (e.g. costs incurred) back to the ajax function and write to a div with the results.

      Please note that this method does not take into account roads, it’s a simple ‘as the crow flies’ distance calculation. I have some code which does this but I need to go through it and write an article on how to use it.

      I hope this has shed some light on a possible way to approach the distance calculation.

  2. Thank you very much for your reply.
    This does indeed shed some light on a possible way to approach the distance calculation.

    When do you think your article on how to do this using your code will be available?

    Thank you once again.
    Glen

  3. Actually that site you mention DOES have mysql tables as well as that they have a CSV including the MsSql you used.

    Note that the database gets updated and theirs an issue of duplicate records that need addressing!

  4. I wanted to implement complete list of United Kingdom potcodes so can any one help me how to find area, postcode including lattitude and langitude.

Leave a Reply

Your email address will not be published. Required fields are marked *