Skip navigation
2017

***UPDATED on 5/11/18 to include the awesomeness of the 2018.1 dual axis mapping improvements!***

** This simplifies the process of combining custom and generated latitude and longitude **

** Also added my demo datasets & 2018.1 TWBX as attached examples **

 

If you work with maps in Tableau, at some point in time you’ve probably wondered how you can add more than one layer of data.  The dual axis capabilities in Tableau can make (some of) your layering dreams come true, though sometimes it is tricky to bend it to your will.  In this post I’m going to explain and walk through some of the complexity of dual axis maps.

 

I’ve put all of the examples below up on Tableau Public as a workbook so that you can download an example set.  The workbook is in Tableau 10.2 to take advantage of the awesome spatial file support.

 

There are likely other pathways to making dual axis maps, other than the methods that I am describing here – feel free to let me know what has worked for you and I’ll add them to this list!

 

The basics:

To simplify, there are two types of geographic data that you can work with in Tableau mapping:

 

Latitude and Longitude

These are dimensions or measures with location coordinates from your data source or from a calculated field; they have a Geographic Role of Latitude or Longitude

1-lat and lon geo roles.png

 

Latitude (generated) and Longitude (generated)

These are measures generated by Tableau that tell Tableau how to draw your spatial data.  For instance, you see these when you use Tableau geocoding data (e.g., you have a country name and want to use Tableau geographic data to draw the countries), or when you work with spatial file types. When you add a dimension with a geographic role to your worksheet, Latitude(generated) and Longitude(generated) are added to Rows and Columns.

 

a.png

When creating a dual axis map, the most important rule to remember is that you cannot make a map with both generated and non-generated latitude and longitude values together on the Rows and Columns shelves.  This just doesn’t work.  Even though you can’t make a map with a mix of the two the types of latitude and longitude pills on the Rows and Columns shelves, there are still ways to work around this (you just have to get creative)!

 

3 - combining fields.png

 

Dual axis maps – many ways - examples:

I’m going to start with the easy examples – dual axis maps where we don’t have to worry about how to combine generated latitude and longitude with non-generated…

 

Tableau geocoding data only

If you are working with Tableau geocoding data, you just need to drop a second set of Latitude or Longitude on rows (or columns) and change the level of detail on the second marks card. For an example, say you’re working with the Superstore dataset and want a map with sales by state with points showing the cities all of the customers:

4 - two tableau geocoding data sources.png

Easy peasy since the datasource has both geocoding levels of detail (State and City).

 

Start by dragging State onto the viz – it will make a map showing centroids for each state.

 

Then drag Sales onto the color shelf and you’ll see a filled symbol map.

 

To make the dual axis map, drag a second copy of Latitude(generated) – this is the Tableau-created latitude for the geocoding polygons.  It may seem weird to use a second copy of the same field, but since this is the Tableau geocoding data the geography (point or polygon) drawn is defined by whatever you put on the detail on the Marks card…so you can re-use it for different geographies.  You should now have two maps that look the same and two Marks cards (technically three – All, and two that are named Latitude(generated) to match the name of the pills on Row).

4 - two marks cards.png

Now, we can change the second map so that it shows points instead of filled polygons.  To do this, open the lower marks card and drop the City dimension on detail.  You’ll want to have both State and City for your level of detail because the state disambiguates the city (e.g., there are many Lincolns, but only one Lincoln, Nebraska). Depending on your map type, you might see an error that filled map isn’t an option for point maps, if so…just hit the dropdown at the top of the Marks card and change it to ‘Automatic.’  You can also remove the Sum(Sales) pill from color and symbolize the point markers however you want.

 

Then, the only magic to making the dual axis is to right click on the second Latitude(generated) pill on Rows and select Dual Axis.

5 - two individual maps.png

If your points end up underneath your polygons and you want to change which symbols are drawn on top, just change the order of the Latitude pills on Rows.  The order of the pills specifies the order of the marks.

And now you’re done!

 

You can also do the same thing with two polygon geocoding datasets – perhaps you want county-level data with state outlines.  Just make your county map, drop another copy of Latitude(generated) on Rows and change the level of detail on the second Marks card to state.  Change to dual axis and you’re done!

 

6 - tableau geocoding - poly and poly.png

 

Tableau geocoding + spatial file

When you add a spatial data source to your workbook, Tableau creates values for Latitude(generated) and Longitude(generated).  Because Tableau geocoding also uses generated latitude and longitude values, we can combine them just like we did with the examples above.

 

Drop the Geometry measure onto the worksheet to draw the shapes from your spatial data source. You’ll see Latitude(generated) and Longitude(generated) in the Rows and Columns.  If needed, disaggregate the features on the map by putting a relevant dimension onto detail.

 

To add a dual axis with Tableau geocoding, start by dropping a second copy of Latitude(generated) onto Rows. In the second Marks card that appears, remove the detail used for your spatial data source (it’ll be something like COLLECT(Geometry) and whatever you put on detail to disaggregate the features), and replace it with the dimension that defines the Tableau geocoding role you want to use.    Then you just need to right click on the second Latitude(generated) and select Dual Axis.

 

7 - Tableau geocoding and spatial file.png

 

Spatial file + spatial file

It’s great that dual axis maps are possible working entirely with Tableau geocoding data, or combination of Tableau geocoding data and a spatial file, but what if it’s two shapefiles that you’re dealing with? That’s a little more complicated, but totally doable.  Currently, there isn’t a way to union two spatial files, but you can use an outer join (on non-matching fields) to combine two spatial datasets (or you could extract each file into a TDE and union the TDEs…but I’m not going into that here…).

 

Whaaatttt?  Make a join on fields that don’t match?  Yes – because that gives you unique rows for every geometry in your dataset, and since we can’t yet union spatial data files this allows us to get our data in a right-enough format to make the dual axis map.  The two ‘layers’ for your dual axis can be drawn independently because the Latitude(generated) and Longitude(generated) measures created by Tableau will only see the geometry for the level of detail that you select on the marks card (and nulls for the other dataset).  And then when you add the second Latitude(generated) you’ll select a level of detail for the other dataset and Tableau will see geometry for that second dataset and nulls for the first.  Huh? Let’s walk through it to explain…

 

In this example, we’ll use two shapefiles – USGS_earthquakes.shp (points for earthquake locations) and plate_polygons.shp (polygon boundaries of plates).   Individually they are two tables with Tableau-generated geometry columns:

 

8 - plate table.png9 - earthquake table.png

If you are using Tableau 10.2 or later – and if you are trying out this example with shapefiles, I assume that you are using 10.2, you can just create a full outer join between these two (using calculated fields of 0 and 1).

10-full outer join with calc fields.png

You should get a table with rows for all earthquakes and all plates, but where there is detail for plates there should be null for earthquakes and vice versa:

 

11 - full outer table.png

 

To make the dual axis viz, add the geometry for plates to a viz and color encode by the Plate dimension so that they polygons are all disaggregated and uniquely colored. Since there are a ton of Null geometries from the earthquake data, they show up in the map legend, but aren’t drawn on the map (because they’re null!).  It’s easy to ‘hide’ those in the legend.  Don’t ‘remove’ the rows, though, because then you won’t have those rows to draw on the dual axis!

 

Now you just add a second copy of Latitude(generated) to the Rows, change the level of detail on the lower Marks card to use the geometry from the Earthquake shapefile.  Remove the Plate pill on color (it doesn’t do anything for our earthquakes since Plate is null for the earthquakes), and drop Earthquake on detail to disaggregate the earthquake point geometries.  Symbolize them up to your liking and then right click on the second Latitude(generated) and select Dual Axis.

 

12 - plates and polygons dual axis.png

 

Spatial file + CSV

…and now, the more difficult dual axis map…combining a data set with generated latitude and longitude (shapefile) with a dataset with non-generated latitude and longitude (points from a CSV).

 

UPDATE: If you're working with Tableau 2018.1 or later, there is a great new feature to make it easier to combine custom and generated latitudes and longitudes in a dual axis map. 

 

The 2018.1 and beyond easy dual axis method:

 

First thing is to get the spatial file and CSV hooked together with a full outer join.  I find it easiest to just use a custom calculated field with 0 and 1 to make sure I get all rows of both tables:

 

On the worksheet, add the geometry from your spatial file - this will put generated latitude and longitude on the rows and columns.

 

Drop a second copy of Latitude(generated) on Rows to make the second axis.  In the second marks card that opens, remove all of the detail information...and then just replace it with the Latitude and Longitude values from your CSV.

 

 

Drop on the a dimension to disambiguate the points, right click on the second Latitude(generated) pill and select dual axis.  And then you're done!

 

 

 

Note that it also works the other way around...you can add the Latitude and Longitude from the CSV first, and then use a second copy of Latitude on Rows to create the dual axis.  Then on your second marks card, just drop the geometry from your spatial file on the viz and you're done!

 

 

 

But, if you're using Tableau 10.5 or earlier, here is the slightly more complex process:

 

First thing is to get our spatial file and CSV hooked together using a full outer join.  Since we can’t union spatial files (yet), we will use the join to give us a data source with a single row for every feature. To avoid problems with filtering (I don’t want to filter my polygons and have some points disappear as well), we’ll join using calculated fields of 0 and 1.

10-full outer join with calc fields - spatial and csv.png

Because the join doesn’t match (0 != 1), we end up with all of the rows from our shapefile input (North Carolina counties in this example) and then all of the rows from our csv file. This gives us a lot of null values. Where I have attributes for the shapefile I have nulls for the attributes in the csv, and vice versa.

dataSource_table.png

 

Because all of the features in the shapefile have null values for the Latitude and Longitude fields, we’ll need to create a calculated field so that we have a valid value for the Latitude and Longitude geographic roles for every row.   It’s easy to set this up using one of my favorite calculations in Tableau: ZN.  (Special thanks to Dan Cory for sharing the joys of ZN with me).    ZN checks an expression to see if the result is null. If it is, it returns 0; if it isn’t null, it returns the value of the expression.

 

So, we’ll make new calculated fields for latitude and longitude like these:

 

 

Right click on each and set the geographic roles to Latitude and Longitude, respectively.

 

Set these to geographic role of Latitude and Longitude.

 

Now we’re set up to make a map!

 

Drop Latitude (calc) and Longitude (calc) (the new calculated fields) onto the worksheet. Use a unique dimension (primary key) to disambiguate the points. In the example in my workbook, I used the ID field for the points.  I only used the ID for my points because the Latitude (calc) and Longitude (calc) values put in as a placeholder for the polygons are all the same (0).

 

To remove all of the un-necessary points at 0,0 – add ID to color and in the legend, right click on the ‘Null’ legend item and select ‘Hide.’  This doesn’t filter out the rows, it just hides them from the current view.  After you do this you can take ID off of color if you want.

 

14 - hide mark.png

 

Now for the dual axis – drop another copy of Latitude (calc) on Rows.   You should see two maps.

 

On the lower marks card, remove the all of the pills specifying the level of detail and drop the Geometry from the county shapefile on detail instead.  If you’re working with polygon data, you may be surprised that NOTHING happened.  Try changing the type of mark to ‘Filled Map’ – and then you should have a filled polygon map!

 

15 - shapefile and csv dual axis.png

 

Update the look of the symbols until you’re happy, then right click on the second Latitude (calc) pill on Rows and select dual axis.  If your points are under your polygons on the dual axis map, just swap the order of the Latitude (calc) pills and they’ll change drawing order.  And now you’re done!

 

 

I hope that this has been helpful for explaining the wonders of dual axis mapping using different data sources!  Let me know if I have missed combinations of data sources that you need to use in making your dual axis map.

 

Happy mapping,

Sarah (@mapsOverlord)

It turns out that someone is actually reading these blog entries and has sent me a question... what if I want base map tiles underneath my fancy projected dataset?   In the first few posts in this mini-series, I suggested that you just make the Tableau map tiles transparent so that you don't give away the secrets of the coordinates in your data.  If you want to put in a little more effort (and read a longer blog post), you can have custom base map tiles to match your newly projected data.  All it takes is a little ingenuity, more creative lying with spatial data, and a Mapbox account.  And then magical projected maps with custom basemap context can be yours...

1-polarMap_BaseMap.png instead of polar_no_basemap.jpg

 

 

Side note: my polar data exploration was encouraged by Alan Eldridge and I wrote a short form write-up on his blog – but here is the painfully detailed version

 

 

The basic details - if you want to skip the instructions:

  • Prepare your data for Tableau (see Parts II and III): project into your coordinate system of choice and then lie and say that the data is Web Mercator. All done.
  • Prepare your data for Mapbox tiles: project into your coordinate system of choice, lie and say the data is in Web Mercator, convert back to WGS84 (latitude and longitude), add to blank Mapbox tileset. 
  • Add your data into Tableau, make a map.  Add the Mapbox tiles.  Shake or stir as needed.  Garnish with good tool tips.  Sip and enjoy.

 

I’m going to use polar data for the example below, which is one of the harder geographies to work with since the poles don’t technically exist in Web Mercator (the projection cuts off at ~85°).  Even though I’m working with polar data, the general ideas should work for any region.  

 

Ingredients needed:

  • A few different spatial data files (these could be Shapefile, JSON, etc.)
    • The file for the data you'll interact with in Tableau (I'm using polar sea ice data from NSIDC - you can go straight to the FTP archive here)
    • Any vector datasets that you want for context in your base map (I’m using country outlines from ThematicMapping)
    • Any georeferenced imagery that you want for context in your base map (I’m using a polar geoTIFF from IBCSO)

 

  • QGIS– a FREE and open-source Geographic Information System (or you can use whatever GIS, database, programming language, or other tool you prefer for manipulating spatial data…there are plenty of options)

 

 

Some of the usual caveats with any of this projection alteration work:

  • With the projection trick that I've showed you in earlier posts, you will need to make the Tableau basemap fully transparent (it has to disappear) or make your own custom tiles in Mapbox.

 

  • Working with polar data is a pain in the ****, but I’m going to write fairly long and detailed instructions. If you’re not dealing with polar data, it is the same set of steps, but should be a little easier (less data cleaning needed)

 

Here we go:

 

Make the data for Tableau - re-hashing the details we went through in Part II of these posts

 

First, do a little homework. Read the earlier blog posts (Part I (projection basics) and/or Part II (data manipulation)) to understand the tricks of how to make Tableau think that a projected dataset is really Web Mercator.  Our process to get the polar data into Tableau will be a little easier than the earlier examples since the dataset from NSIDC already has an appropriate projection (NSIDC Sea Ice Polar Stereographic South), but the data for the map tiles will require a little more work.

 

Option 1 - The I love to do everything with GIS method: Use QGIS to change the layer properties so that the file thinks it is Web Mercator, and then 'save as...' to make a new copy of the dataset so that the projection information is permanently saved with the new shapefile.

save_as_wm.png

 

Option 2 - The lazy cartographers method: For data that is already in the projection you want, just replace the text defining the projection. How? A shapefile will often have a file that explicitly defines the coordinate system for the data; this file has the extension 'prj'. 

prj_info.png

This is just a text file, so you can edit it with any text editor (I like Sublime, but Notepad or whatever you prefer should be just fine).  For my sea ice dataset, the prj file contents look like this - "mumble mumble, sea ice polar stereographic, mumble mumble, whatever datum / earth model is being used, mumble mumble", or:

 

PROJCS["NSIDC_Sea_Ice_Polar_Stereographic_South",GEOGCS["GCS_Unspecified datum based upon the Hughes 1980 ellipsoid",DATUM["D_Hughes_1980",SPHEROID["Hughes_1980",6378273,298.279411123064]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Stereographic_South_Pole"],PARAMETER["standard_parallel_1",-70],PARAMETER["central_meridian",0],PARAMETER["false_easting",0],PARAMETER["false_northing",0],UNIT["Meter",1]]

 

This tells a GIS (or Tableau) how to project and draw this data appropriately.  Since we want Tableau to think that the data is Web Mercator, we can really just open the text, and swap out the original projection information and replace it with the details for Web Mercator (conveniently available from spatialreference.org, or you can just copy/paste from below).  No QGIS needed here, because we don't want to re-project, we just want to tell a little lie about the projection.

 

PROJCS["WGS 84 / Pseudo-Mercator",GEOGCS["Popular Visualisation CRS",DATUM["D_Popular_Visualisation_Datum",SPHEROID["Popular_Visualisation_Sphere",6378137,0]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Mercator"],PARAMETER["central_meridian",0],PARAMETER["scale_factor",1],PARAMETER["false_easting",0],PARAMETER["false_northing",0],UNIT["Meter",1]]

 

Now you should have a dataset that you can bring directly into Tableau.

 

That about covers it for the review of the earlier parts of this series.  Now we're going to get fancy!

 

Make a new base map!  (The new stuff!!)

 

First – figure out what data you want in your basemap

I’m going to make a really basic map for the polar data, but with Mapbox you can add as many layers of detail as you want (e.g., roads, buildings, counties, provinces, labeling, etc.) – you just have to have the data for whatever you want to show up on your fancy, projected basemap. 

 

Collect and process the base map data.  Since we are making our own custom map tiles that aren’t in Web Mercator, we can’t use the built in data in Mapbox (or Tableau).  This means that we have to dig up and process whatever data we want to show up on the base map tiles.  I’m going to keep it simple here to show the basics:

 

Polygons – we’ll use countries of the world

Imagery – we’ll use a geoTIFF (geographically registered TIFF)

 

Here are the basics: We need to get both datasets into a Polar Stereographic projection, and then ‘changed’ to Web Mercator (not reprojected, just using our little white lie skillz to make them think they are in Web Mercator) -  just like we did in Parts II and III of these posts.

 

The countries dataset from ThematicMapping.org is in WGS84 coordinates (World Geodetic Survey of 1984).  So in QGIS, we need to do a Save As… and project to Polar Stereographic:

countries_save_as_polarStereographic.png

 

Then we change the projection in the datasets Properties in QGIS so that it thinks that it’s in Web Mercator.

crs_properties_to_WM.png  To keep your data clean - you want to make sure that it is clear that this is a dataset with incorrect projection information (remember, you lied and said that this file is in Web Mercator, when it's really Polar Stereographic), so go ahead and right click and 'save as...' to make a new file with a name that makes it clear that this has bogus projection information.

 

Now if we open a new QGIS project and add the newly saved file in, it should look like this:

wheres_antarctica.png

  It isn’t common to use a polar stereographic projection for the entire world and the distortion around the periphery is crazy.  Fortunately, we just need the center bit for our map.

 

Since we only need the center bit, we’re going to delete all of the extra countries.  If you had great dreams of having all of the countries in your base map, I’m going to crush that dream right now, and save you the step of trying it out yourself. Here is what the new dataset looks like when you do the last step of converting back to latitude and longitude coordinates (so that you can put it into your Mapbox tiles)…I left an a copy of the dataset in the original Polar Stereographic for reference:

WTF_geography.png

 

Whaaa?  Let’s just say the math does not treat this data well when we try to use the inverse Web Mercator equations to translate polar stereographic coordinates to latitude and longitude.

 

Easy way to fix this problem – do some house cleaning and delete the countries on the periphery before converting back to latitude and longitude. 

 

To do this, click on the pencil icon in QGIS (Toggle editing) – the countries should now have a bunch of red x marks along the edges; these mark the vertices.  Using the select features by area tool (yellow box with an arrow on it), select everything except for bits of Africa, Australia, and South America and hit delete. I like to delete in phases so that I don’t accidentally remove part of the map that I want.  When you’re done, just click on the pencil icon again and save edits.

edit_delete_countries.png

 

Now, save a new copy of the dataset and change the coordinate system to WGS84 (World Geodetic Survey of 1984) so that we can drop it in Mapbox.  This will use the inverse equations to go from Web Mercator coordinates back to latitude and longitude...this keeps our polar data centered around (0,0), instead of putting it back at the poles.

prj_back_to_WGS84.png

 

If you have imagery data that you want in your basemap as well – like a geoTIFF – just follow the same steps:

  • Use QGIS to put into the projection of choice (most polar data is already in the right projection)
  • Change the projection definition to Web Mercator
  • Re-project to WGS84
  • Note that raster (imagery) reprojection is a harder beast than dealing with vector coordinates, so your mileage may vary on the results (e.g., your imagery may get a little blurry).  If you want to understand this problem well, I recommend some of the fine raster re-projection work by the Center of Excellence for Geospatial Information Science (CEGIS)

 

Push the data to Mapbox

Go to the Mapbox web site.  Log in or create a new account.

 

Go into Mapbox Studio.   Add new tilesets with whatever data you want in your basemap. My example uses a tileset for the simplified countries and the geoTIFF that I reprojected.

mapbox_newTileset.png

 

Add a new tileset with a ZIPPED copy of all of the files for your countries data (a shapefile is really 3 or more files – put all of files with the same name, but different file extensions (shp, shx, prj, dbf, etc. into your zip file).  The five files that I used and the resulting zip file that I used in Mapbox are shown below:

zippedFiles.png

 

Then add another new tileset using your GeoTIFF data. 

 

Add your tilesets to a new basemap

Switch over to ‘Styles’ and create a new style:

mapbox_newStyle.png

Use the Empty map style for your template:

mapbox_emptySTyle.png

 

Add new layers for the country outlines and imagery:

mapbox_newlayer.png

Adjust the styling until you are happy. There are lots of instructions available for this – so instead of making this post even longer, I’ll point you to the Mapbox Studio manual or Mapbox Studio Tutorials and Examples

 

If you add the image tileset in Mapbox Studio and it doesn’t show up, try zooming in and out on the view.  Sometimes the zoom level for imagery isn’t right for your needs and it has to be adjusted so that the image shows up when you want it.  If you have problems, here are some details from Mapbox on how to adjust the zoom level.   For my polar base map tiles, I used Mapbox Studio Classic and set the minimum zoom level to 1, so the image would always show up, regardless of zoom level in Tableau.

 

When you’ve adjusted the styling of your base map to your liking – click on Publish and when given the option, select Preview, develop, & use.

 

Copy the code for using it in Tableau. 

mapbox_codeForTableau.png

 

Finally, make your Tableau map

  • Add your polar shapefile (just adding the data source in Tableau) and draw the geometry
  • Change the base map (Map -> Background Maps -> Map Services)
    • Add -> Mapbox Maps
    • Paste in the special string of text that you copied from Mapbox in the URL textbox
    • When you paste the code in the URL textbox, the API Access Token, Username, and LayerID should be automatically populated.
    • Give the Style a name – and click okay

 

tableau_addMapbox.png

 

You should have a beautiful new map – complete with imagery and whatever reference geography details you added in in Mapbox.

 

tableau_finishedPolarMap.png

If you made it this far, pat yourself on the back! When you make some killer awesome fancy maps in Tableau let me know…or if these instructions just totally stump you, let me know and I’ll see what I can do to help out.  There are probably many examples where these cheats just won’t work, or don’t quite do what you need.  That is just going to happen when you’re working with the weirdness of map projections.