Since I arrived at Snowflake, I’ve been using a dataset from the NYC Citibike program as my demo data. It’s about 61M bike trip records with lat/lon data for the starting and ending bike stations, and I’ve been showing it using the following dashboard where the starting and ending locations are in different maps…
What I really wanted to do though was to make a map that allowed me to select a starting station and show where passengers went. Something like this…
Well, with the latest 2019.2 beta, I did (as you can see). It’s running over my Snowflake demo database – naturally – and uses the following calculation to create the line:
TripLine = MAKELINE ( MAKEPOINT([Start Station Latitude], [Start Station Longitude]), MAKEPOINT([End Station Latitude], [End Station Longitude]) )
Oh, how long I have waited for thee, sweet MakeLine() and MakePoint()…
The reason this new feature helps so much is that the starting and ending location data is in the same record. In earlier versions of Tableau I would have had to pivot the data to split the record into two so I could use a line marker and the path shelf. This would have performance implications as I would be effectively doubling the amount of data we need to read and process.
So MakeLine() gives me the line between the start and end points, which I display in a sheet:
I also want the point map for the starting locations (so I can select them) – here’s that sheet:
As you can see, both of these maps are set up with no background. Thanks to this blog post (https://www.tableau.com/about/blog/2018/11/express-your-data-artistry-transparent-worksheets-tableau-98807) that showed me the trick for layering maps and getting the transparency to work. The secret is to set the sheet shading to be “none”:
Once I did this, I was able to layer them together using floating placement:
To get them to line up, I used the search/zoom feature of the map to set the viewport on each map to the same region:
Now to make the two maps work together… I set up a filter action from the point map to the line map to only show the lines for the selected starting station:
I also need a filter to control the Num Trips panel in the top right – this needs to be a separate filter as the logic is reversed to the lines. I.e. when I clear the selection, I want all the trips to be shown:
This gives me the interactive behaviour between the three elements (points, lines, trip count) but the problem is that the points layer is obfuscating the line layer (because it’s on top):
We need to hide the unselected points, and our good old “hide the mark using an invisible shape” trick comes into play. We define a set that will hold the selected marks, and we use the set to control the mark shape – using a filled circle for IN the set and an invisible 10x10px PNGcustom shape for OUT of the set:
We then use an Edit Set action to update the set values to the selection:
The result – the selected points are shown as circles while the unselected points disappear, allowing us to see the lines unobstructed. We get all the points back by clearing the selection:
The final point is that we need the base map for spatial context. I initially had this in the lines map but the problem was that it disappeared when there were no lines to show – i.e. the case when no points are selected.
To overcome this, I simply added a 3rd map sheet with a visible base map but with a single mark that was invisible. This meant that the map would be visible in all states of the upper layers. Again, transparency allows this to show through:
The final result is a map with both points and lines that dynamically updates based on the selection. The key elements were:
- MakeLine() to create the line mark between the starting and ending stations, even though there is no spatial data elements in the underlying database – just raw lat/lon FLOAT fields. Also, we don’t need to pivot the data to make starting and ending records;
- Sheet transparency to allow me to layer the maps together;
- That good old dynamic mark visibility trick using an invisible custom shape; and
- Edit Set actions to control the visibility of the point marks.
I’ve uploaded a version of the workbook with a reduced set of data extracted so you can pull it apart.