8/6/2018
Importing and Converting Spatial Files for Tableau
Tableau has the ability to import and plot a number of spatial file types. This includes shapefiles (polygon, line and points), GeoJSON and KML files. This is very handy and makes it very easy to import and create a map. In fact, once imported into Tableau a simple double-click of the Geometry pill will plot the spatial file on a map. It's really that easy. However, due to the structure of some of these files, you may wish to convert your files to a different format. Let's walk through an example.
Creating a Custom Map Route
I have a short running/walking route through my neighborhood that I like (with a few variations). I don't have a Fitbit or anything that tracks my location, so the first thing I needed to do was map the route. There is a great tool from Google that makes this very easy, and it's free. Simply open Google.com/mymaps and give it a try.
Here are the steps to create a custom route using Google.
Select "Create New Map" and enter your starting location in the address bar.
Select "+ Add to map"
Select the "Add Marker" icon on the toolbar.
Click on the map for the location of the points that you wish to add. You can hold the CTRL key down to select multiple points quickly along a trail. The points will automatically sequence for you, which will be important when we import into Tableau.
Add markers for all of the location points you wish to add. When you are done, simply click the last point a second time.
You can click on "Untitled Map" to name your map. This will be saved in your Google account for later (assuming you are logged in).
Click on the 3 dots to the right of your map name for options and select "Export to KML/KMZ" (You can save as a KML or GeoJSON)
Select the Map Layer in the dropdown box that has your points. Note - Tableau will only work with KML files that have a single map layer (at least as of Tableau 2018.2).
Check the "Export to .KML file" checkbox and click the download button.
You now have a KML file that is ready for import into Tableau. Simply open a new data source in Tableau and select "Spatial file" and select the new file. The steps for plotting the map in Tableau are pretty simple.
Double click Geometry and your custom points will be plotted on a map.
On the Marks Card, change the dropdown box from Automatic to Line.
Move Name to Path.
You should now have a path mapping the points in the route. Here is my custom map route imported into Tableau as a KML file.
Animating the Route
Animating the route is also pretty easy. We will use the Page Shelf for this.
Move Name to the Page Shelf
On the Marks Card, change the dropdown box from Line to Circles.
On the Play Control, select the box "Show History"
On the Show History Option, click the arrow and set the "Mark to show history for" to All and select Both to plot trails and marks.
You now have an animated map using a KML file.
That was super easy, right? But take note of the limited fields that are available in Tableau from the KML file. You only have Geometry and the name of the point to work with. There is no easy way to link this file up (join or blend) with another file unless the point names match up. We also can't use generated Longitude and Latitude in calculations. If we had a list of the longitudes and latitudes along with a Point ID and Path Order in CSV we could append all sort of additional data or do custom calculations. To do this we can convert the KML file (or Shapefile or GeoJSON) to a CSV.
Converting Spatial Files
There are a number of great on-line conversion tool that I find very useful for converting spatial files. The tools below are free to use based on daily record limits.
Simply use one of the tools to convert the KML file (or whatever spatial file format you have). I used the KML to CSV converter which will output the latitude and longitude of the points in the KML file as a CSV.
Now that it's a CSV file you could also append or join other data, for example, elevation, state, county, zip code, etc. You could also reverse geocode the latitiude and longitude for an address. For example, using Geocod.io, I was able to quickly append Address, City, State, Zip Code, County, Time Zone, School District, FIPS and MSA just using the latitude and longitude in the CSV file.
When done, you now have a CSV file that is ready to import into Tableau.
Open a new data source in Tableau. Select Text File and chose the new CSV.
Move Longitude to Columns.
Move Latitude to Rows.
Move Name to details.
You should now see the points of your custom map plotted as a map in tableau.
You can add a Path Order to the file if needed by splitting the Name field that was created in the Google mapping tool.
Right-click on Name, select "Transform" and then "Split". This will create a field for the path order, for example "Point 12" becomes "12".
Rename this field to Path Order
If you want lines instead of dots, then change the dropdown on the Marks Card to lines (or Polygon if desired).
Select "Lines" from the dropdown box on the Marks Card.
Move Path Order to paths.
Remove Name from details by dragging off the canvas or right-clicking and selecting remove. (you could also use Name on paths in this case since it's unique and sequenced in order)
You should now have the same points plotted as a line. Here's my running route as a line.
Since there is a Name and a Path Order, it's easy to create an animated map of the route with the points.
On the Marks Card, move Path Order from "Path" up to "Page Filters".
If we want to add points to the mapping route then we can do that with a dual axis.
Duplicate Longitude on the Columns by holding he control key down and dragging Longitude next to itself. You now have two marks cards.
Right-click on the second Longitude pill and select dual axis (there is no need to synchronize the axis for maps).
Add Name to the "Details" on the second Marks Card labeled Longitude.
Change the dropdown box to Circles on the second Marks Card labeled Longitude.
You should now have an animated map plotting the custom map route, with both lines and circles. I added a Mapbox Map layer as well.
As a final step, we can link the longitude and latitude up with Google Streetview so that we can see the route at each point. To do this, I will leverage the URL information from this blog post on How to Embed Google Maps in Tableau. Below is an example of this technique in action.
Note - you can use this same Google Maps technique with the KML files by replacing the Longitude and Latitude in the URL with the generated Longitude and Latitude of the Geometry field.
I hope you find this information useful. If you have any questions feel free to email me at Jeff@DataPlusScience.com
