Free up-to-date UK postcode latitude longitude data

Unless your data comes pre geo-encoded, if you’re trying to do some UK-based geospatial analysis you’ll probably need some easy way of translating addresses into latitude/longitude pairs or some similar co-ordinate system.

Whilst full-address geocoders are available, if you don’t actually need that level of precision then looking up a full postcode is often good enough and far faster (note to US readers: UK postcodes are way more precise than US zipcode – BPH Postcodes quotes an average of about 15 properties per postcode)

A few years ago this data could be a little challenging to obtain –  for free anyway. But now there are various sources offering that up sans charge in various formats and levels of up-to-dateness; some more useful than others

My current favourite is probably Doogal. There, Chris Bell has provided all sorts of address-based tools there. Need to generate a random postcode or calculate the elevation of any route? He has it all.

Most interesting to me are the big CSVs of the UK’s postcodes, past and present, obtainable by pressing “download” at the top right of this page.

It’s full postcode to long/lat mapping and includes the below columns too, which allow for some very useful groupings or lookups.

  • Postcode
  • In Use?
  • Latitude
  • Longitude
  • Easting
  • Northing
  • GridRef
  • County
  • District
  • Ward
  • DistrictCode
  • WardCode
  • Country
  • CountyCode
  • Constituency
  • Introduced
  • Terminated
  • Parish
  • NationalPark
  • Population
  • Households
  • Built up area
  • Built up sub-division
  • Lower layer super output area
  • Rural/urban
  • Region
  • Altitude

From his blog and various appreciative comments, it sounds like it’s kept up to date nice and regularly, which is truly a service to mankind.

If you have any problems with his file, then some other alternatives would include the free version of Codepoint Open (although it seems to show Eastings/Northings rather than long/lat – although it is possible to convert between the two), or a UK-Postcode API.

All this is overkill if you just have  <100 such lookups to make – you can find many web-based batch converters that’ll do the job for you very quickly, and often to address-level accuracy, if you don’t have too many to do. Doogal’s is here.

Advertisements

#VisualizeNoMalaria: Let’s all help build an anti-Malaria dataset

As well as just being plain old fun, data can also be an enabler for “good” in the world. Several organisations are clearly aware of this; both Tableau and Alteryx now have wings specifically for doing good. There are whole organisations set up to promote beneficial uses of data, such as DataKind, and a bunch of people write reports on the topic – for example Nesta’s report “Data for good“.

And it’s not hard to get involved. Here’s a simple task you can do in a few minutes (or a few weeks if you have the time) from the comfort of your home, thanks to a collaboration between Tableau, PATH and the Zambian government: Help them map Zambian buildings.

Whyso? For the cause of eliminating of the scourge of malaria from Zambia. In order to effectively target resources at malaria hotspots (and in future to predict where the disease might flare up); they’re

developing maps that improve our understanding of the existing topology—both the natural and man-made structures that are hospitable to malaria. The team can use this information to respond quickly with medicine to follow up and treat individual malaria cases. The team can also deploy resources such as indoor spraying and bed nets to effectively protect families living in the immediate vicinity.

Zambia isn’t like Manhattan. There’s no nice straightforward grid of streets that even a crazy tourist could understand with minimal training. There’s no 3d-Google-Earth-building level type resource available. The task at hand is therefore establishing, from satellite photos, a detailed map of where buildings and hence people are. One day no doubt an AI will be employed for this job, but right now it remains one for us humans.

Full instructions are in the Tableau blog post, but honestly, it’s pretty easy:

  • If you don’t already have an OpenStreetMap user account, make a free one here.
  • Go to http://tasks.hotosm.org/project/1985 and log in with the OpenStreetMap account
  • Click a square of map, “edit in iD editor”, scan around the map looking for buildings and have fun drawing a box on top of them.

It may not be a particularly fascinating activity for you to do over the long term, but it’s more fun than a game of Threes – and you’ll be helping to build a dataset that may one day save a serious amount of lives, amongst other potential uses.

Well done to all concerned for making it so easy! And if you’ve never poked around the fantastic collaborative project that is OpenStreetMap itself, there’s a bunch of interesting stuff available there for the geographically-inclined data analyst.

 

Free interactive data and analysis tools from Public Health England

Public Health England is an agency sponsored by the UK Department of Health whose aim is to “protect and improve the nation’s health and wellbeing, and reduce health inequalities”.

They use, generate or distribute a bunch of interesting health-related data. They’ve a collection of many, many “data and analysis tools”, linked to from this page.

There’s something for everyone there, at least if you’re interested in various health stats based mainly on UK geography. Be careful, it’s easy to lose hours clicking around all the various tools, if you have even a vague interest in such things 🙂

There’s general indicators such as health inequality maps, tools to allow comparison between health practices and a lot of information on specific health-related concerns such as cancer,  maternal mortality, mental health, obesity and many many more.

Quite often the links are to interactive web tools, such as the one pictured below, or to PDF summaries. If you’re after data tables that you can use to integrate in your own analysis, it might be quicker to start at the Public Health England bit of the statistics subsection of the gov.uk website.

Capture

Showing continents on a Tableau map

Tableau maps can understand many geographic features, but, somewhat surprisingly, whole continents is not one of them. It’s a requested feature but hasn’t yet made the grade to be included.

However, it was pretty easy to find several shapefiles of continents freely available online. Here’s one.

Tableau can’t read shapefiles (yet). But, inspired by tableaumapping.bi, I then used this technique to convert it into a Tableau file with the relevant data to produce a polygon “chart” that appears as continents, like the below illustration shows. Click through to see it live on Tableau Online.

continents

If you’d like to incorporate this mapping data in your own analysis, feel free to download the TDE file from here.

Load it into your Tableau Desktop, and set up your marks shelf like this:

Capture

Then double-click the longitude and latitude fields so they end up on the Columns and Rows shelf respectively, and off you go. Far better (video) instructions on how to do that are available at tableaumapping.bi, if that was less than clear.

It should be noted that this “workaround” somewhat abuses the polygon chart feature so does not behave in the same way as the geographies Tableau natively knows (or can be added to with Mapbox et al.) – but it does nicely as a quick and dirty solution.

Using a Tableau Web Data Connector to access my Moves app location data

Never one to rush into things :-), here’s the second part of my journey towards creating a Tableau Web Data Connector.

Next on my plan was to try out an existing Web Data Connector so I could see what they looked like in practice. It’s always useful to review examples of other people’s work in analytical tools, if nothing else to get some insight into the potential scope and usefulness of their features.

Googling around made it clear that there are many generous people who have released free-to-use Tableau web data connectors for a bunch of services. I decided to go with the “Moves” web data connector, created and hosted by the Tableau experts at the Information Lab here.

For the uninitiated, Moves is an app for iOS and Android smartphones that sits in the background and records where you’ve been, and how active you’ve been whilst going there.

Moves is an automatic diary of your life. Your daily storyline and maps show where, when, and how much you move.

On one’s phone, it produces a daily timeline of activity like the below. Most days I work from home, and it really, really would not be a very interesting experience to visualise the locations I visit! So to make this more interesting, I’ve picked a day where I happened to be in Spain and needed to get back to my home in the UK. Here’s what the app shows me:

img_1049

It’s not perfect, but you can clearly see my journey from the hotel to Barcelona airport, then to London, and onto the train network to get me home. Considering I have not spent much time customising and correcting it, it’s not bad at all. It could certainly be useful if, for instance, I wanted to know how many times per year I went to Kings Cross.

But the app itself won’t tell me that very easily.

You can export your data from their website though. If you do that, you’ll get a zip file with actually a very comprehensive set of data files, including a representation of your data in JSON, CSV, KML, GPX and even a ICS calendar file of locations. Within those categories are hundreds of files, representing  different time granularities, dimensions, summaries and so on. It’s actually very comprehensive, full marks to ’em – but who wants the faff of downloading that every time you want a visualisation, working out which is the correct file, and creating something suitable for import into your favourite dataviz product? Not us, surely!

So instead I opened up a recent version of Tableau, and chose to connect to a web data connector. I then entered the web address of the web data connector that the Information Lab provided, which is http://data.theinformationlab.co.uk/moves.html.

Capture

This led me to a screen evidently designed by the Information Lab team, with a nice “Let’s Go” button. So I did.

CaptureThe next screen gave me a security code and told me to go into the Moves app on my phone to enter it, so that it knew I was happy for the data connector to see my data. I could tell from the address in the mini web-browser that this page was an authorisation page coming from the Moves app website.

Once I had done that, I was returned to Tableau where I waited for a couple of seconds and then was given the normal Tableau user interface screen, with a dataset called “Moves Storyline” already magically loaded up for me.

Capture

From that point on, I could use Tableau just like with any other Tableau dataset.

Fortunately the field names were self-explanatory enough to make sense of. They were also categorised into dimensions and measures, and by data type, in a useful way – although whether that’s a property of the web data connector or Tableau’s auto-recognition feature wasn’t immediately clear.

Anyway, time to visualise my trip home from Spain!

Here’s a few example records.

Capture

It looks like it’s recording datapoints every so often, recording where I was, how I was moving and for how far/long etc. This is also how it works in the manual data export from the Moves website.

Apparently I was walking around the hotel a lot that morning. Although I’m not so sure I could really have covered 2km in 337 steps! That said, I’ve never found iPhone step counters all that accurate, so was not too surprised to find a similar issue here.

The dataset also provides a latitude (“Lat”) and longitude (“Lon”) too, which means you can quickly construct geographic maps of your day in Tableau too. Here’s a few salient geo-points of my day, on a Tableau map.

Capture

 

So, what have I learnt?

  • Analysing ones’ movements when you largely work from home in a rural location is not all that thrilling on most days! But that aside:
  • Tableau web data connectors are hosted on, and “look like” web pages. The Tableau connection interface seems like it’s really a mini web-browser. This implies one needs to be able to host web pages somewhere to make your own. Luckily I have some past experience with regard to making websites, so this hopefully won’t be a problem.
  • The data returned by a web data connector comes in the form of a static Tableau extract. It doesn’t updated automatically. If I open the file I used above next week then it will still only have data in up until today. However, you can “Extract -> Refresh” in Tableau and then it will automatically suck in the new data, without having to go through the data connection process again, basically the same as how a “normal” database extract/refresh works. It therefore must be storing the connection details and credentials.
  • The connection process interacted with my app on the phone, over which neither Tableau or the Information Lab would have had control. It used the authentication features provided by the Moves website itself. This makes a lot of sense; personal location data can be extremely sensitive, so Tableau must have provided or allowed a facility to the auth features of external sites. However, I already decided to start my own creating process with a website that doesn’t require any authentication, so this probably isn’t something that is important to me right now – but it’s good to know there are ways to use confidential data in a secure way.

So now I know what a web data connector looks like to the end user and the format of data it can produce. Time to learn how to make one!

How to map geographies in Tableau that are not built in to the product (e.g. UK postcodes, sales areas)

Tableau has a nice ability to create point (“symbol”) or polygon (“filled”) maps to visualise spatial data on. However, in order to do this, it of course needs to understand where each point you wish to plot is in the real world.

Several of the most common geographic identifiers are built in, such as country, region and sometimes postcode part. A list and some instructions as to how to use can be seen near the bottom of this article in the section called “Built in geographic roles”. If you data fits into this format it is extremely easy to use.

However, especially outside of the US, the geospatial data coverage is not amazing. For instance, Tableau cannot immediately understand Sweden’s postcodes at all. In the UK, it knows the first half of the postcode (“AB1”) but not the full postcode (“AB1 2CD”). It also sometimes knows the centroids of, for example, postcodes which allows the construction of a point-based map, but not the polygons needed for a shaded map.

Of course it also does not know your own custom geographies, such as sales territories, drivetimes around stores and so on.

Although it’s usually not nearly as user friendly or fast as using the built-in mapping geographies, there are several workarounds or alternatives if you need to map something not built in. Here are some options I’ve successfully used in the past. Tributes need to be paid to the Information Lab, a consultancy which has some amazingly useful Tableau mapping information and insights on their blog for a lot of these

Some options described below:

Option 1: Geocode your source data

Tableau can always plot any latitude and longitude co-ordinate you give it, so if you can arrange for your datasource to include that on each record then it is very simple to select those and have them plotted as points. Here’s some very quick instructions.

If your datasource already has longitude at latitude in it then you’re sorted for point-based mapping, just use that verbatim. If not, you would need to add the co-ordinates into your dataset somehow.

There are plenty of free websites to help find the co-ordinates if you need them if you have the street address in text form (e.g. 1, 2 – these suggestions are not necessarily recommendations! Google will find you lots if those aren’t good), plus some “desktop GIS” packages can do that.

A challenge will be if you have a long list of co-ordinates you need to find such that these semi-manual efforts are not efficient. Desktop GIS tools or specialised analytics packages like Alteryx can often do that on a mass scale, albeit sometimes with a need for expensive data licenses.

If you don’t want the expense/bother/tedium of inhouse solutions, you can also find several companies that offer you the chance to send them a long file of addresses and it will return the co-ordinates. Be warned that usually they will charge a financial cost and require uploading potentially sensitive personal information. However, the cost may be more than worth facing if you have a requirement for large volumes of geocoding in terms of time saved.

Option 2: Join / blend / “Tableau custom geocode” geographic co-ordinates to your data

This is similar to option 1, but rather than geocode every record in your database, instead have a lookup table with the appropriate identifier  (e.g. postcode) and a latitude/longitude co-ordinate. There are then various options to combine this with your source data and and have Tableau plot your records on the map.

  • Tableau “custom geocoding” is probably the most flexible and reusable option after setting it up – instructions here. You are essentially creating a new geographic category within Tableau, so as well as knowing where the country of Spain is, it might also know where the airport of Heathrow is. Note though that this can only by default give you “point” mapping rather than “polygons” – so you could pop a dot in the middle of your sales area, but you can’t have it draw the boundaries like Tableau can do for countries. However, there is a workaround for this – see option 5.
  • Database join the your lat/long lookup file with your source file, e.g. a JOIN in a database, a VLOOKUP in Excel source, etc. such that every source record has an associated co-ordinate
  • Tableau blend the lat/long lookup file with your data, if it is in a suitable format. (See here for some notes regarding deciding between joining and blending data.)

The limitation here is often granularity. If you have a lookup table which has 1 co-ordinate per postcode, then all the records with a certain postcode will appear in the same place, rather than if you had geocoded a precise street address where the level of detail is much higher (although often unnecessary for analysis).

An example file format for this might be:

Postcode Latitude Longitude3
12525 63.42124 51.124123
12526 63.41211 50.123212
12527 62.51622 50.98271

If you don’t already have this sort of lookup data then there are various places to find it.

  • For official postal address information, often the country’s government or main postal service is the supplier of this data. This often has a cost associated with it (although it might not be much) but is usually the most accurate and timely.
  • Various agencies/consultancies can supply this type of information: usually at a more expensive cost.
  • Various websites / open data initiatives publish it for free, which Google searches will quickly find. One of the first that comes up today is Geonames for instance (this is not necessarily a recommendation). But as ever, with free public information, if it is not from an authoritative source there is some likelihood it is not entirely accurate or complete so it would be good practice to test it first. Be sure especially to check the granularity and recency of the geocoding, as a lot of this free data may lack a formal code-book.

Option 3: 3rd party mapping files designed for Tableau

Tableaumapping.bi is a great site for free downloads of mapping data, both points and polygons, that are designed to work with Tableau. These are provided by various kind people free of charge I believe (organised by the super folk at the Information Lab) so accuracy and updates are probably not guaranteed. However, whenever I have had cause to use them I have enjoyed the experience.

As they are designed specifically to be used in Tableau it should be a relatively easy job to get them working. Below is a video they have showing how to use them.

There are also commercial companies that create these type of things for a fee. Mapbox is a mapping company that works with Tableau files for instance.

Option 4 – convert a “shape file”

Most of the above options will result in a “point” datasource rather than a polygon one. One of the most common types of polygon mapping data formats is a shape file (“.shp”). Again you can often find these files freely downloadable from the web (above warnings re accuracy and updatedness may apply. Here is one site that offers a collection of links (I have not tested them) or purchasable / supported from professional GIS data shops/consultancies at a cost.

Shape files are normally used in GIS systems and general analytics products like Tableau or Excel often can’t read them. However it is possible to convert a shape file into a file Tableau can use. Google searches will, as usual, give you several methods of doing so, but perhaps the easiest and cheapest way I found so far if your shape file is not huge is to use the converter available in the free Alteryx Analytics Gallery.

You upload your shape file. It will then convert it and give you a file to download that works in Tableau. You can then use the same technique as shown in option 3 above to use it in Tableau. You do have to register with the gallery but it’s free of charge. Note any restrictions you have about uploading personal data to 3rd parties if appropriate.

The main limitation of that method will be if you have a large or complicated shapefile it will may not work over the web. The Alteryx desktop software itself can process almost any size file relatively quickly, and you can get a 14 day demo here. After the demo expires though you cannot use that feature any more without buying the full software package.

Large shape files may also result in large Tableau polygon data which can make your visualisations very slow with huge file sizes. If this is the case then, if you can sacrifice accuracy, opting to “generalise” the polygons in the conversion process can make a big difference. This reduces the number of polygon points, meaning it is less precise, but much faster, which is a good trade-off for many spatial tasks.

Option 5: modify Tableau’s geocoding database to put your own custom polygons in as an option.

This is really a special case of option 2’s first bullet point, but allows you to create easily reusable custom mapping polygons that behave in the same way as the default Tableau country, region etc. type datasets do. It’s another cunning mapping trick discovered by the geniuses at the Information Lab which uses Alteryx to rewrite the database Tableau uses for its custom geocoding.

As per option 4, you need a shapefile and Alteryx to get this one working, full instructions provided by Craig Bloodworth here. However if you don’t have either of those pre-requisites, or don’t fancy taking the time to follow the instructions, then they kindly provide some several useful basics like UK postcode areas and continents here and here.