Some time back I used an example of plotting great circle routes on a map as a way to demonstrate data densification (https://blog.databender.net/2014/06/27/curved-lines-in-tableau-through-data-densification/).
To do data densification for the interpolated points we need two records for each route – one representing the start of the line and the other representing the end. In the original solution I used custom SQL to generate this scaffolding – running the query twice, each time returning a different number for the PointID, then unioning the two result sets together:
SELECT [Routes+Airports].[airline] AS [airline], [Routes+Airports].[from_code] AS [from_code], [Routes+Airports].[from_lat] AS [from_lat], [Routes+Airports].[from_long] AS [from_long], [Routes+Airports].[num_flights] AS [num_flights], [Routes+Airports].[to_code] AS [to_code], [Routes+Airports].[to_lat] AS [to_lat], [Routes+Airports].[to_long] AS [to_long], 1 as [PointID] FROM [Routes+Airports] UNION SELECT [Routes+Airports].[airline] AS [airline], [Routes+Airports].[from_code] AS [from_code], [Routes+Airports].[from_lat] AS [from_lat], [Routes+Airports].[from_long] AS [from_long], [Routes+Airports].[num_flights] AS [num_flights], [Routes+Airports].[to_code] AS [to_code], [Routes+Airports].[to_lat] AS [to_lat], [Routes+Airports].[to_long] AS [to_long], <Parameters.NumPoints> as [PointID] FROM [Routes+Airports]
Clearly this is not an optimal solution as it a) requires a custom query which is inflexible and requires the user to know how to write SQL, and b) has to query the data source twice which is inefficient. However, at the time it was the only way to generate 2 result-set records per source record. I was messing about with this workbook yesterday and it occurred to me that we now have a better way to solve this problem. In Tableau 9 we introduced a new feature for data preparation – the PIVOT feature. We can now use this to generate the required scaffolding without custom SQL and multiple passes of the source data.
Here’s our input data:
We can pivot this on the from/to airport codes:
And the result is two records per route:
We can now create a calculated field to assign starting and ending PointIDs:
From there, the densification technique is much the same as the original. However, this time around I wanted to avoid using R to calculate the interpolated values as it is not something all users have set up. Dusting off my Google-fu, I found http://williams.best.vwh.net/avform.htm#Int which had the formula I needed to do the calculation natively in Tableau:
The result is a much better performing workbook – partly because we are reading the input data more efficiently and partly because we are using native calculations.
You can download the TWBX for this workbook here.
Hi Alan! Firstly, really good post, especially for Mac users as we cannot use custom SQL. Secondly, I’m trying to achieve something similar, a flight route map. I pivoted the data, created the route id and point id but got stuck at the single latitude/longitude. How did you make it in your example because I have still the origin and destination data. I looked at your workbook but cannot work out as I’m quite new to Tableau.
Thanks for any info!
It’s hard to provide guidance without seeing your workbook. I’m happy to take a look at it if you like – you can email me at aeldridge at tableau dot com.
Pingback: Points and Polygons in Tableau 10.2 | The Last Data Bender