Spatial Joins in Tableau with Excel, Text, Anything

• By Joshua Milligan
• Posted in All, Data Blending & Joining, Data Shape & Structure, Mapping / Geographic Visualization, New Features
• On August 14, 2019
• 2 Comments.

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.

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:

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:

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):

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: