Poor Man’s Custom Geocoding in Tableau (Part 2)

• By Joshua Milligan
• Posted in All, Data Blending & Joining, Mapping / Geographic Visualization, Tableau Tip Tuesday, Tips and Tricks
• On February 10, 2015
• 14 Comments.

 

Last time we looked at a simple way to implement custom geocoding in Tableau using the ability to assign latitude and longitude to unknown locations.  That’s a fairly good option if you only have a few locaitons and only need to plot marks on a map or two in a single workbook.  But what happens if you have a lot of locations?  Or what if you want to reuse the solution in multiple workbooks?

There is a way!  We’ll use the tried and true Superstore sample data to illustrate.

This data set includes some fields with recognized geographic roles such as City, State, and Postal Code (Zip), but it also includes a Region field that does not have a geographic role.  Now, this is purely an example and this data set only has 4 regions — so it wouldn’t be too difficult to lookup latitudes and longitudes and plug them into Tableau like we saw last week.

(by the way, Shawn Wallwork got me to thinking and I realized you don’t need to leave Tableau and go to Google to find out the lat/lon for a coordinate on a map.  Just right-click the map and add a point annotation then Include latitude and longitude to see where that point is.)

But what if you had ten regions?  Or 15 territories?  And what if you want to reuse the results in more than one workbook?

Well, the good thing is, Tableau is ready to help!  We just have to coax it a little…

Get the Data Set with Latitudes and Longitudes

I’ll start by creating a geographic view that includes Region but also a few other geographic fields so Tableau will use the Latitude and Longitude fields it automatically generates:

Here I have a mark for each Postal Code (Zip code) color coded by Region.  You’ll notice that Regions contain multiple zip codes and a single zip code is contained in only one region.  This data is really clean — but this technique will work even if the data isn’t quite so nice.   Just have some kind of geographic relationship.

Here’s the trick — we’re going to capture Tableau’s values for latitude and longitude — along with the associated Region and then create another data source with that data.

I press Ctrl + A to select all the marks on the map (or Area select them) and then right-click a mark and select “View Data”.  And there it is, the data set I want!

Now I have every zip code within a region along with the associated Latitude and Longitude.  Now, simply press Ctrl + A to select all rows, then Copy, then close the dialog box, then Ctrl + V to paste back into Tableau as a new data source!

And now, the magic:

Use Data Blending to use the Latitudes and Longitudes

We’ll start a new sheet with our original data set and put Region on the Marks card to define the level of detail.  Then, we’ll switch to our new data source and bring out Latitude and Longitude to Rows and Columns.  We’ll use an aggregation of AVG for both.  Guess what? When we blend on Region, that’s the geographic center of all the zip codes within that Region!

And there you have it!  You now have an easy way to plot marks for any number of regions or territories or unknown geographic field based on the geographic center of a known geographic fields.

Like it?  Let me know in the comments and also check back next week for one more step that makes this even more exciting!