So you want to use your KML files in Tableau?

by Philip Mannering

So you want to use KML Files in Tableau? Tableau comes with a list of recognisable geospatial areas and regions straight out of the box. But this list is limited and oftentimes you will want to create your own custom geocoding to create a filled map. If, like me, you’ve found your custom KML files then here’s how you can prep the data for use in Tableau. In my case I have a KML file for each of the Police Force Areas in England and Wales.

Important: Tableau 10.2 now comes with a spatial file connector making life a lot simpler by allowing you to connect directly to your KML files. If you’re using versions 10.1 or earlier then the process is much more convoluted. I’ll will cover both in this blog post.

Tableau 10.1

In Tableau version 10.1 or earlier, the KML files have to be converted into polygon data. This can be almost accomplished using the following Alteryx workflow,

 

The steps involved are:

  1. Import all the KML files. This is done by using the filepath with a *.kml wildcard and extension to catch all KML files. For example, I would use “C:\Users\Philip\Documents\Geolocation Police Force Area\*.kml” to import all files. In the ‘Output File Name as Field’ box, select ‘File Name Only’.
  2. Next, we split the spatial objects into points. We now have a row of data for each vertex of the polygon, increasing the number of rows significantly.
  3. We then find the X and Y coordinates of those points. Using a WGS84 projection, the X and Y coordinates refer to longitude and latitude respectively.
  4. Finally, we export this as a TDE.

Alas, if only it were so simple. Unfortunately, some of the areas that I’m trying to map are made up of multiple polygons. For example, the Police Force Area Devon & Cornwall also includes the Scilly Isles which would many separated polygons. This introduces artefacts in our mapped areas when Tableau joins up all these points in as one connected polygon as shown below in the SW of England and Wales,

To overcome this problem we need to introduce two more modules to our Alteryx workflow,

The additional steps 2 and 3 first split up the spatial objects into their corresponding polygons and the number them (to tell them apart). This means that Devon and Cornwall (and the associated Scilly Isles) are made up of 22 polygons, but can now be mapped as one police force area in Tableau,

Once you have saved your TDE file you still need to do some jiggery-pokery in Tableau,

  1. First move your RecordID and Split_SequenceNum from measures to dimensions (if they are not already)
  2. Your CentroidX Coordinate should be converted to the geographical role of Longitude and dragged to Columns
  3. Similarly, CentroidY should be converted to Latitude and dragged to Rows
  4. Add RecordID to Detail on the Marks card
  5. Change the Mark from Automatic to Polygon
  6. Finally, drag Split_SequenceNum to Path

And hey presto! You have created your filled map

Tableau 10.2

The problem with producing maps by joining your data with ordered polygon coordinates is that you end up dramatically increasing the number of rows of data. Not only will this slow performance, but aggregating your data can be messy as each piece is replicated for each of coordinate of the region that it’s associated with. For instance, my Police Force Areas consists of just 44 spatial objects, but split up into coordinates they make up over 900,000 rows of data!

If you have access to Tableau 10.2 then you’re in for a treat. The spatial file connector allows you to connect directly to various spatial filetypes including KML files. All you need to do is combine all the KML files,

The steps are:

  1. Import all the KML files, as in Tableau 10.1. Select ‘File Name Only’ in the ‘Output File Name as Field’ box.
  2. Save as a KML file. Be sure to select the right field to ‘FileName’ in the ‘Name Field’ box to retain name information.

In Tableau 10.2, once connected to the spatial file, drag Geometry to Detail and you’re done. Simple as that! Tableau 10.2 spatial file connector is there for all your geospatial analysis needs.

 

 

 

Avatar

Philip Mannering

Reflections on the Data School

4 mins read

Tue 23 May 2017

Andy Kriebel’s Marathon Training - Mapping a Run

4 mins read

Sun 23 Apr 2017

How to Batch your Macro

2 mins read

Fri 21 Apr 2017

How to Create a Macro in Alteryx

3 mins read

Wed 19 Apr 2017