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.

Advertisements

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s