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!
I think you just made my life so much easier. I run into this issue all the time. I’ve analyzed NHL draft data by location and ton of the small towns in Canada (100s) where these players are drafted are not recognized. I’ve manually updated some of the big ones but figured I would have to do it every year. I knew there was a better way and it seems like I can reuse the work I’ve done and create some kind of master sheet. Your post makes this simple and straightforward. Thank you so much. (I also had to do a bunch of manual geocoding on this post: http://nhltoseattle.com/2015/02/09/seattlearenaoptions/ )
I’m glad it helped! I’ve used the technique a few times and it’s great to have something you don’t have to rebuild each time.
This is a great tip/ trick! Thank for sharing!
You’re welcome!
Josh, any way to bring those label back over top the original data on a filled map?
Can I do somewhat of the reverse? I have a table of geocoded points and I’d like to create another table of those points with the Zip Code data from the Tableau map added as a new column. Basically it would result in a data set shwoing the zip code for each of my geocoded points.
Thanks for your help.
Hi,
Thanks for very informative post! However I could not fine what I was looking for.
I have a list of about 150 locations in the US, but tableau could not recognize 65 of them(I have state as well, but it still could not). The locations are name of cities/localities some of them are very small and remote localities, Google map does find them (I tried for some of them), so is there a way to generate Lat and Long from google maps/bing maps for plotting on map?
Thanks,
Ashish
Hi Ashish!
Tableau does not include geocoding for small towns. I don’t remember the cutoff – but there is a population size at which Tableau will include locations in the internal database. You can use Google Maps or Bing, but it’s tedious (right-click in Google and select “What’s Here” — it will give you lat and lon). But you might also check out http://www.findlatitudeandlongitude.com/ to batch convert a few dozen to a few hundred locations.
Thanks Joshua,
It is helpful for the time.
If I can bring in Google Map inside tableau, It will work as well. I found a way here: “http://dataremixed.com/2013/06/how-to-embed-a-google-map-in-tableau/”
but the point I am stuck at is it shows one location at a time and need an action to go to that location. I still think that there is an easy way to achieve it since I saw someone did similar thing in qlikview using API and scripting.
Thanks!
Al
is there a free form US map available so that we can create customized shapes in the map for areas(like states)
Amar, did you ever find a solution for this? Also looking for something like this.
Thanks for the informative article, Joshua! I ran into a problem with the “View Data” window, however. When I open that window, I see a lot of Null values for both Lat/Long. For context, I’m mapping student enrollment data by city, and it seems Tableau is finding a lot (2k) of “ambiguous” cities, thus refusing to plot them. I know you can “edit locations”, but that’s a LOT of coordinate data to manually enter! Thoughts?
If you happen to have a way of providing your own latitude/longitude, even in a separate file — that might help. In versions before Tableau 10, you can use blending. In Tableau 10 you could even join in the file and then use the lat/lon for mapping.
Hi Joshua,
Thank you for posting this oh-so-long ago. It has helped me immensely today. Is there a way to change this to a filled map for the zip code areas? It would still be by region, and each of my regions has a percentage associated with it. Based on the steps above, what would need to be done differently? Is there an article you have written regarding this? Thanks for any and all information.