Tableau discovers the world – more detailed maps by using background images and a little trick in Microsoft Excel
Tableau Software offers a very easy way of overlaying data on a map, even without requiring the geographical references (latitude and longitude) in the data source. It automatically identifies field names in your data that are appropriate for visualizations on a map, like state or county and generates the according latitudes and longitudes.
If you are using data of the United States, Tableau provides a very high level of detail (state, county, zip code area) and displays the boundaries and names of the regions on the underlying map. You can even enhance the visualization with a choropleth map showing a variety of predefined US census data.
For other counties of the world, however, Tableau does not (yet?) provide this level of detail. In a data source of Germany, for one, Tableau automatically identifies the 16 states and shows the state boundaries on the map. But that’s it. Higher levels of detail like counties (Regierungsbezirke, Landkreise, etc.) or even zip code areas are not available.
Of course, the folks over at Tableau Software have been smart enough to implement a very easy workaround to overcome this: Tableau provides an option to overlay the data on any given image, e.g. on a picture of a map. Prerequisites for this workaround are a picture of the map and the latitudes and longitudes in the underlying data source. Usually both are easy to find and download somewhere on the Internet. Sometimes, however, the geographical references in your data do not exactly fit to the areas on your map image and Tableau’s visualizations will not be located exactly in the center of the according region.
Today’s post describes this problem using data based on German zip code areas and provides a how-to-tutorial, a Tableau packaged workbook and a little bonus track for my friends in Lithuania.
Tableau’s built-in maps
Let’s assume we have a database of German data on a level of zip code areas. For your understanding: German zip codes have 5 digits, but only the first 2 digits represent continuous regions. Using the first 2 digits of the zip code divides Germany into 95 regions. That’s right, 95 not 99. The remaining 4 are unused or do not represent a geographical area. But that’s another story.
With only a couple of mouse clicks and drag and drop actions, Tableau generates the visualization shown in the screenshot on the left. It is exactly what Tableau claims to be: rapid-fire Business Intelligence.
In most cases this visualization should meet all of your requirements. However, as already mentioned in the introduction, Tableau does not provide the boundaries of the zip code areas. If you like to see the boundaries as well, you have to use a background image map.
Using background images as maps with Tableau – step-by-step tutorial:
To replace Tableau’s built-in maps by any given image of a map, conduct the following steps:
- Organize an image of the background map you want to use
- Insert the latitudes and longitudes of the regions into your data source
- Open a new Tableau workbook and connect to the data
- Drag longitude to the columns shelf, latitude to the rows shelf and the measures to color and size on the marks card
- Dismiss Tableau’s built-in map by clicking on Data|Background Maps|None
- Insert your image as the background image (Data|Background Images|Add Image|Browse
- Set the minimum and maximum values of the longitudes by inserting the longitudes of the left and right border of your image to the left and right field of the X field
- Set the minimum and maximum values of the latitudes by inserting the latitudes of the bottom and top border of your image to the bottom and top field of the Y field
- Choose the desired washout using the slider
- Click ok
- Set the axes to fixed scales used in steps 7. and 8. (double click on axis) and hide the axes (right click on axis)
That’s it. Here is a screenshot of the result:
And here is our problem. The location of the circles based on latitude and longitude in our source data do not correspond to the underlying background map. I added some red circles to highlight a couple of problematic data points. The arrows indicate where the data point should be.
Root cause for this problem could be
- wrong latitude and longitude values in the data source
- wrong maximum and minimum values used for adding the background image and fixing the axes scales
- a small, invisible distortion of the dimensions of the map image
And here is the challenge: find an easy and convenient way to make the locations of the data points fit to the regions of the background image.
The trick – using an undesired feature of Microsoft Excel to solve the problem
In versions 2003 and earlier, Microsoft Excel provides the possibility of changing values of data markers in an XY scatter chart by clicking on a single data point and dragging the point to another position on the chart.
“It's a feature, not a bug.”
Honestly, if you call this one a feature, I would recommend to call it an undesired feature. In my humble opinion the visualization of data should never allow the user to change the underlying data. There is a high risk that users unintentionally change the original data without even noticing. The visualization parts of Business Intelligence software should allow to display, filter, group, brush data, etc. But it should definitely prevent users from changing the original data.
That said, we will take advantage of this undesired feature for solving our problem described above. Here is the how-to tutorial:
- Create a new Excel workbook and copy your data (zip code, latitude and longitude) to it
- Insert a XY scatter chart (X = longitude, Y= latitude)
- Clean up your chart by removing the usual suspects like the grey fill color of the plot area, the gridlines, the legend, etc.
- Fix the scales of the axes to the same values we used in our Tableau workbook (see above)
- Select the plot area, choose format, fill effects, click on the tab picture and browse for the file with the image of your map
- Click on a data point that is not in the right location and change the underlying latitude and longitude coordinates by dragging the data marker to the desired position. Please be advised that you can drag the data marker only either horizontally or vertically in one step
- Repeat step 6. for all data points you would like to relocate
- Copy the manipulated data set of longitudes and latitudes to the data source of your Tableau workbook
That’s it.
I know it is somehow hypocritical blaming a feature of Microsoft Excel and exploiting it at the same time. On the other hand: it is still there and if it can be helpful somehow, why not taking advantage of it?
By the way: I assume Microsoft would nowadays call this one an undesired feature as well, since it is no longer available in Microsoft Excel 2007.
The solution
Here is a screenshot of the Tableau map using the manipulated data:
And here is the Tableau Packaged Workbook with a map of Germany by zip code areas for free download:
Download Germany by ZIP code areas (Tableau Packaged Workbook, 259.0K)
To open this workbook you need Tableau 5.0 (14-days free trial) or the free Tableau Reader.
The Bonus Track
If you are a regular reader here on Clearly and Simply, you may have come across a previous post called Lithuania at a glance.
Thus, you might have reckoned that I spent some time in Lithuania and know Tableau aficionados there as well. If so, you are absolutely right.
So here is one for friends: a Tableau workbook including a map of Lithuania by municipalities:
Download Lithuania Map by Municipality (Tableau Packaged Workbook, 202.2K)
What’s next?
For the fast growing community of Tableau aficionados out there: I am planning to have a couple of more posts on visualization with Tableau during the upcoming months.
For our readers not yet using Tableau: I recommend to download the 14-days free trial: if you are in data analysis and visualization, I guarantee you will love Tableau.
Having said that, of course there will be further posts on Microsoft Excel, data visualization in general and project management here on Clearly and Simply as well.
Stay tuned.
Leave a Reply