August 9, 2018
Bridget Cogley
Comments Off on How the EXCEL do I do Mapping in Tableau? The Toddler Guide to getting on Point

How the EXCEL do I do Mapping in Tableau? The Toddler Guide to getting on Point

At some point in school, we learn that ‘latitude has some attitude and takes a wide path’ while longitude is long. I think I was sick that day, so I’ll share my sticky note with you:

LAT = row

LONG = column

Maybe by the year 2025, I’ll sort this out…It’s not lookin’ good kids.

Within the last couple of years, Tableau has invested heavily in making maps almost otherworldly. While most of us have come to rely on GPS to get anywhere, there’s a fine art to maps that someday, I’ll get half-right. Until then, I’m putting down these breadcrumbs – not for you, but for me…

Data vs Drawing

Shapefiles come in 2 primary varieties. There’s the kind that (with newer versions of Tableau) you can import as a spatial file. These have cute file extension like:

  • SHP
  • KML
  • GeoJSON (support for this is coming!)

These types of shapefiles are magic. Ideally, you have a 1 to 1 match of a shapefile to some level of data, such as exhibits at a zoo. Here’s what that might look like:

The data here is animals in exhibits. The STR(TRIM(LEFT…. is a join calculation pulling out the number from my data set. You’ll also notice the extension (KML), so this is data. Each row in this KML file is a shape, regardless of whether it is a point, polygon, or line. When Tableau pulls this file in, it’s given a measure (Geography – but you can change the name if you want) and an aggregation (Collect – no, you can’t change this). Using these will also populate Tableau’s generated lat/long. If you double-click the geo-field, you don’t have to guess what goes where (some of us live and die by this).

Shapefiles like this (the ones that are data) work best when you join them to your data. The key is that they match 1:1 to some field. With this, you can make like Carmen Sandiago and explore your whole world of data through mapping. If you have it as a dimension, you can explore to your heart’s content.

The other variety is what I call drawing. We’ve been able to do some version of this for as long as I can remember. They are often a translated version of ‘shape file’ to a tabular format with coordinates as points. If you’ve ever read translated literature, you know where I’m going with this…

To draw polygons or lines, you’ll want a PathID. This tells Tableau how to draw it. Polygons will have a 0 point that matches the last point. If you want to create chaos in your organization and watch everyone lose their wits, join this file to your data. If you want to keep your sanity (and Tableau license), blend, blend, blend! Drawing in Tableau requires many points, so this will create data gremlins AKA ‘Cartesian demons’ if joined with other data.

Drawing is great when you have limited options. Blending means you can only drill down to the ‘linking level of detail’ so if you think you’re going to slice and dice a number ways, think again. You’ll likely hit the limits of that linking field pretty quickly. This is why those spatial files above should be your new best friend. Points are (typically) the exception.

So, data > drawing – check!

Points, Lines, and Polygons – Where in the World is Carmen Sandiego?

Here comes the part that reads like a 7th grade word problem – yes, kids, confusion traveling at 78 miles per hour from the east. If you’re coming from the west at 45 MPH, when will you meet if you’re currently 1279 miles apart and headed in the exact same direction? (My answer was to step on the gas and crash ASAP.)

Lines are not polygons, and polygons are not lines. Points can be used to make polygons, but you better have them in good order and double up first and last. Polygons within polygons create fractal polygons that look nothing like their intended format. SVG can be used to make polygons, but you need Python or Alteryx to make them legible to Tableau. If you can’t sort out ArcGIS or qGIS, there’s at least Google Earth.

Confused? Trust me, I was too. Here’s the less cryptic version:

  • Points are just that – literal dots (X/Y) somewhere on the map. Sure, you can use other icons in Tableau, but that’s on you and your shapes directory in Tableau. For now, we’ll just call these dots on a map.
  • Lines have a start and stop that are not in the same place. They go one way and don’t link up. SVG files seem to favor these, just as a heads up, so if you’re thinking you can make those cute lines into a polygon…you better make a good friend or get quite acquainted with your SVG program. These work best for roads, rivers, and anything else that we like to see as a single line. You can also make outlines, but be aware, the middle is hollow – yes, SVG brain, I’m lookin’ at you.
  • Polygons start and stop at a given point and are solid. If you’re making in them in a GIS program or translating them in something like Alteryx, you can get these as data (see above rant), but make sure the first point (0) and the last point (pick a number, any number) are the exact same coordinates or you’ll get a fractal looking monster that makes great abstract art. Smarter people than me (nudge, nudge, cough, cough) can write about good digitizing practices (you know who you are…). Bad ones can create angles that overlap in all the wrong places and create odd shapes – great for fashion, bad for maps. If you’re using Tableau to draw these, that path ID and blending (not joining) is key.

What else?

  • SVG files, like so many other files in the world (including Tableau and Alteryx files), can be read as XML. If you, like me, cried when learning XML, there’s XML Grid. I can’t recommend it enough. XML is a handy nested database and you can export your shapes with the right tools (Python, Alteryx, etc).
  • GIS programs make all kinds of useful shapefiles (the data kind) – qGIS is free. Google Earth can act as an entry level GIS without all the bells and whistles. You know which camp I favor.
  • There are 2 main ways things get mapped – either to coordinates within the image (these have lots of numbers and fewer decimal points) or to the real world geographic coordinates. Be aware, you can get punked when things are mapped to images because they may not all line up if items don’t share the same scale, direction, and so forth. (See above about ‘good digitizing practices.’)

Generated vs Data-Created Coordinates

Tableau can generate coordinates based on known or encoded geographic fields. Spatial files with a geometry field include that encoding so you do not need to bring in lat/long fields.

Here’s where things get tricky. If you have a bunch of addresses and you want these mapped, pick 1:

  • Create geometry for these.
  • Export your own lat/long.

If you bring in your own lat/long, make sure it’s tagged in Tableau as a geo-field or you will be angry. Remember: globes make for happy mapping.

Happy mapping!