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.

Enjoy!