Warning: include_once(/home/vizpaint/public_html/wp-content/plugins/wp-super-cache/wp-cache-phase1.php): failed to open stream: No such file or directory in /home/vizpaint/public_html/wp-content/advanced-cache.php on line 22

Warning: include_once(): Failed opening '/home/vizpaint/public_html/wp-content/plugins/wp-super-cache/wp-cache-phase1.php' for inclusion (include_path='.:/usr/local/php71/pear') in /home/vizpaint/public_html/wp-content/advanced-cache.php on line 22
Spatial Joins in Tableau with Excel, Text, Anything | VizPainter

Spatial Joins in Tableau with Excel, Text, Anything

How can you join your non-spatial data to spatial data? Using the latest spatial functions, you can achieve spatial joins in Tableau with almost any data source.

A world map that serves as a placeholder for the concept of Spatial Joins in Tableau with Excel, Text, Non-Spatial data sources

I recently had a text file containing individual addresses along with latitude and longitude and requirements for a project that required matching those addresses to congressional districts. While there might be lots of approaches to solving the problem of joining non-spatial data to spatial data, the fact that it can be solved with a spatial join in Tableau never occurred to me! Until I saw this exchange on Twitter:

Robert Rouse
@bibleviz

Similar question for 
@tableau
 devs: what's a feature you've released in the past 2 years that you thought would get more interest than it did? There are likely some great new features out there that few have heard of.
Kent Marten
@datakmart

I think MakePoint (2019.2), which allows you to spatially join data with non-spatial sources (csv, xlsx) is flying under the radar. 🤓

Spatial functions and spatial joins in Tableau

Tableau has continued to add spatial capabilities and over the last few versions has enabled connections to spatial files, made it possible to use spatial joins, and then in the most recent version the functions MakePoint() and MakeLine() were added. I mentioned them here and knew they would provide a good alternative to what I described here, but using them for spatial joins in Tableau? Oh! That is a great idea!

And so I decided to prove out my particular use cases (looking up a congressional district based on latitudes/longitudes in a text file). But you might have all kinds of similar cases:

  • Lookup custom sales territories or trade areas for stores or customers
  • Lookup counties or provinces based on an address
  • Lookup historical areas based on latitude and longitude
  • Lookup any custom geographic or spatial area based on latitude or longitude in your Excel or text file

Finding Congressional District based on Latitude and Longitude

My particular use case was to find out which congressional district contained a given point based on latitude and longitude (well, actually, more specifically it was to find the congressional district based on an address – but there are lots of ways to get latitude and longitude from an address). To illustrate, let’s say you had a text file containing a list of national parks with the latitude and longitude and you wanted to know which congressional district they were in:

List of national parks with latitude and longitude which can be joined to other data using spatial joins in Tableau

There might be a dozen different ways to solve this problem from brute force manually looking up each park to finding some kind of API, but what if you could take your text file and use spatial joins in Tableau to lookup the congressional district?

Cross database spatial join in Tableau

The ability to join disparate data sources has been around in Tableau for quite a while. If I wanted to join my SQL Server database tables to Excel files and then to an Oracle database, I could do it. The ability to spatially join data came more recently and as long as I had two data sources with spatial fields, I could join using INTERSECTS and end up with related data.

But if one of my sources didn’t have any spatial fields, I was stuck with a traditional join on some kind of key field. But Tableau 2019.2 changes that with two new spatial functions: MakePoint() and MakeLine(). Specifically, MakePoint() allows me to take pairs of latitude and longitude and turn them into a spatial point. Now I can use that as a join calculation to join to other spatial data.

Here for example, I’ve created a cross database join that joins my National Parks text file to a Congressional Districts shape file (which is available for download):

The spatial join in Tableau that brings together all national parks and congressional districts

You’ll notice that the spatial join includes the join calculation MakePoint([Lat], [Lon]) for the text file where it Intersects with the geometry from the shape file. I’ve used a full outer join to make sure I have all parks and all congressional districts (even if they don’t have parks).

Voilà! I now have all the national parks associated with a congressional district:

National Parks in California along with Congressional District
Now I can write my representative to encourage them to take care of the park in their district!

2 Responses so far.

  1. Kent says:

    I love this blog post.

Leave a Reply

Your email address will not be published. Required fields are marked *