Hopefully the last word on UK postcode mapping in Tableau

Posted by on Mar 14, 2011

I’ve written a lot on mapping in Tableau, and have answered the question of how to map UK postcodes too many times.  This post is intended to be my last on the subject!  So I’m planning to cover the subject from Land’s End to John ‘’o’ Groats. 

If you’re not from the UK, these places are the very top and the very bottom of our little island.

Firstly, this is not for the feint hearted.  It’s not particularly difficult, but does require some patience and some data management skills.  I would encourage you to contact us if you need mapping at this level but are not comfortable with the content here.

This post assumes you have already read my previous posts – UK Postcode Mapping in 5 minutes.

At the bottom of this post, you’ll find the files which support this work.  I have included them all – FIVE csv files formatted for import into Tableau, which is the postcode data formatted at 5 different levels.  I will include a backup of the SQL server database used to format this data, and some twbx files showing the data in use.

 

So what was the problem?

 

I was challenged on a previous post to map ALL the postcode data, not just the first part of the postcode (the outcode).  If you have not read my previous post, you should probably read it now.

This requires sourcing a complete list of UK postcodes with their latitude and longitudes and then making this data available in the format Tableau likes to consume.

I also wanted to create files which describe the various level of hierarchy implied in the UK postcode – in order to take advantage of the ability to add hierarchies to Tableau – I have created 5 levels of hierarchy in this data set.

 

What needed to be done?

 

This data set is hard to get for a number of reasons.  Firstly, I found it hard to find – the OS site being the source I finally settled on, since it is presumably very reliable, and it is free.  If you want to replicate this work, you can find the data here.

This provided a couple of problems though – firstly, the data does not include Northern Ireland (a problem I have not tried to solve), and secondly the data DID NOT include latitude and Longitude.  In the UK we use the ‘Easting/Northing’ grid co-ordinates and thus the Ordnance Survey had used this method to encode these files.

This needed to be converted, and after some playing around, Grid InQuest solved this problem.

I then loaded the output into SQL server, and built views to format this data in a number of different ways.  From these views I created csv files which are formatted to be used as custom geocoding files in Tableau.

 

And what is the output?

 

Firstly, some great looking views in Tableau – here are a few examples:

 

GET THE WORKBOOK HERE

 

image

 

image

 

image

 

image

 

 

Secondly, we have the 5 custom geocoding files.  Each of these uses the average Lat/Long within the group as the position to map.

1-Region.csv This uses the first two characters of the postcode
2-Outcode.csv This is the lefthand side of the postcode, which is either 2,3 or 4 characters
3-OutcodePlusOne.csv This is the outcode, plus one character from the right side of the postcode
4-OutcodePlusOne.csv This is the outcode, plus two characters from the right side of the postcode
5-CompletePostcode.csv This is the complete postcode, formatted in the most common way (SE1 1AB for example)

 

 

Making use of this data – for SQL server people

 

THE SQL BACKUP IS HERE

 

If you have the skills and the infrastructure to make use of the SQL backup, you will find all this data is available.

To make the most of this data, I would add some WHERE clauses on to the view which produces the postcode CSV file to limit this to the postcodes you are interested in.  Then use this as the basis of your custom geocoding – this is recommended as I have found Tableau to be a little slow with large custom geocoding – if you need something with a little bit more resolution than outcode, but less than the full set, try outcode plus one, or outcode plus two.  Both these sets perform well.

 

image

 

 

Making use of this data – if you don’t want to get involved with SQL server

 

THE TDE FILE IS HERE

 

If you want to create a custom geocoding file using postcode at the lowest level, you’re going to need to accept slow performance, or add some filtering to the postcode set, so that only the regions you need are in your custom geocoding.

To do this, I have create a TDE file with all the postcode data.  You can attach tableau to this file, and select the postcode regions you want to include in your custom geocoding file using filters.  Then produce a file with three columns, postcode, latitude and longitude.  You then need to output this file to csv, and import as a custom geocoding (as detailed in our earlier post).

 

And now…

 

Everything is now possible with postcode data in the UK, we can plot data at any level of the post code hierarchy, filter by elements of the postcode to focus on specific areas.

I realise this is not a straightforward subject however, and that I could have done a much better job in documenting this work.  I would like to help though, so if this seems like it might be what you are looking for, but you just can’t make it work – please contact us.

Tom Brown

Founder & Managing Director

Latest posts by Tom Brown (see all)

5 Comments

  1. Hello
    Wonderful to find this post, but all the file links seem to have stopped working. Any other way to get access to these very useful files?

    Many thanks
    Mike

  2. Tom,
    This post is the most comprehensive post i've seen on the subject, spent a lot of time looking for these mappings, even the post on the Tableau Knowledge base doesn't compare.
    Really useful instructions, Also brilliant that you've provided the files for download, especially the SQL backup, I imagine this will come in handy.
    All the best,
    Looking forward to future posts

  3. This is great – managed to set up Tableau to use unit postcodes or postcode sectors in about 10 minutes with these files and the explanation given. Thanks for all the effort this must have taken!

  4. I want to try to map some UK census data which is available by local authority and government office region. I have no postcode data. How can I map this?

  5. The grid inquest link seems to be dead. A little googling returned this link. http://www.ordnancesurvey.co.uk/oswebsite/support/os-net/grid-inquest.html

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>