Hopefully the last word on UK postcode mapping in Tableau
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:
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
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.
Making use of this data – if you don’t want to get involved with SQL server
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).
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.