Tableau Tip: Curved Flight Paths on a Tableau Map

November 21, 2016 | David Taylor

David Taylor | Consulting Senior Manager

David has been in consulting since 1994 and serves public and privately held organizations in a variety of industries. His expertise includes data science, reporting, data analysis, data warehousing, data architecture...

Go to David's bio page »
Tableau offers developers a vast array of customizable visualizations that allow consumers to highly tailor dashboards to their specific business needs. With the staggering amount of options, some of the visualizations and customizations can be a bit complex to implement. This article will demonstrate how to achieve a more complex customization: how to display flight paths as curved lines between points rather than straight lines. 

We’ve all seen flight paths in curved lines, like those in the map below, on on the last few pages in those in-flight magazines we read at 30,000 feet.
EKS&H Tech Tip - Tableau - Curved Flight Path
 

Visualizing this on a Tableau map is relatively easy, but Tableau uses straight lines. See this article for a good essay on how to do it. Below, I take it a step further and used arced lines to draw those same connections. My use case is to mimic this map showing all the direct routes serviced by my wonderful home airport in Boise, Idaho. All this got started from this Tableau community post. Many thanks to Łukasz Majewski!

As mentioned, those straight lines in Tableau are perfectly functional, as shown here:
EKS&H Tableau Tech Tip - Straight Line Flight Path

However, in my opinion, curved lines appear much nicer, especially when representing air travel on Tableau’s Mercator projection map, because they represent the shortest path between two locations. Note: London is added to illustrate the curvature of a long flight path. We Boiseans, sadly, do not have a direct flight to Heathrow just yet!
EKS&H Tableau Tech Tip - Curved Flight Path

These curved lines are segments of Great Circles, as Ed Williams wrote about in this excellent article, which introduced me to the concepts. Ed’s article contains a set of spherical trigonometry formulas used to navigate the shortest distance on the surface of a sphere. Ed’s “intermediate points” section has all the formulas we will use in this exercise, and they are all nicely portable into Tableau. 

Resources

This workshop uses the 20-row Excel data set attached here. You can also download a completed Tableau workbook that shows both straight lines and the finished curved lines. 

Data Set

The sample data shows a map with all direct routes serviced by Boise, Idaho. Its layout does not immediately lend itself to Tableau’s “Path” functionality because it has both origin and destination cities on the same record, which is a common layout for this type of data. We will resolve this within the workbook.
EKS&H Tableau Tech Tip - Sample Workbook

Data Padding

In order to draw the paths we want, we need many more than 20 marks on our Tableau map. This is commonly called “padding” your data by adding extra rows needed by Tableau. Some Tableau authors would use a technique called domain padding to create those rows, but we will use a different technique. Domain padding requires a lot of window and LOD calculations, which can be tough to understand in my opinion. 

Therefore, cross join data padding is the technique we’ll use. We will still have some calculations, but all of them are straightforward and their results can be viewed two dimensionally in the workbook’s data source tab. 

Boise’s airport serves 20 cities directly. I will continue to include London (instead of Lewiston) again for dramatic and aspirational effect. Tableau draws lines by simply connecting points, so in order to use Tableau’s Paths appropriately, I need at least 40 marks (20*2) and an origin and destination for each route. In order to draw smooth arcs, I actually need many more: 20*10 looks decent, 20*50 looks really smooth, and 20*150 looks fantastic, even for unrealistically long flights. The “Layovers” tab in Excel accomplishes this; it has a set of intermediate points, numbered 0 to 157, for every flight. 
Here’s Auckland to Seville, one of the world’s theoretically longest city-to-city trips, with 159 points mid-route:
EKS&H Tableau Tech Blog - City to City Trip

In order to create these points, I will use a cross join so that each row gets replicated many additional times — once for each point needed. 

Prepare the Data 

1. “Connect to Data” in Tableau 10. Use Excel, and select “Curved flight paths using CJ.xlsx.”
2. Drag the Routes worksheet and drop it to the canvas. Then double click Layovers. An inner join should automatically appear, where “Always One” = “Always One (Layovers).” 
3. True cross joins actually have no join clauses, but Tableau requires at least one. We are effectively doing the same thing here because those columns always contain the integer value 1, which results in a traditional cross join, returning 20*159 rows — 159 for each city pair. I have filtered below to the BOI-DEN route. Observe that every row is exactly the same except in the Layover column. 

Assemble the Visualization

1. On Sheet1, drag “Layover” from Measures to Dimensions. 
2. Create a float parameter, p_radians_between_points, with an initial value of .02 and an allowable minimum value of .02.
3. Add the nine calculations below. I have added some brief descriptions, but most are still tricky to understand:
 

Calculation Name

Calculation

d 2*asin(sqrt((sin((radians([Origin Latitude])-radians([Destination Latitude]))/2))^2 +
cos(radians([Origin Latitude]))*
cos(radians([Destination Latitude]))*
(sin((radians([Origin Longitude])-radians([Destination Longitude]))/2))^2))
f min ( [Layover] * [p_radians_between_points] / [d], 1)
A sin((1-[f])*[d])/sin([d])
B sin([f]*[d])/sin([d])
x [A]*cos(radians([Origin Latitude])) *cos(radians([Origin Longitude])) +
[B]*cos(radians([Destination Latitude]))*cos(radians([Destination Longitude]))
y [A]*cos(radians([Origin Latitude]))*SIN(radians([Origin Longitude])) +
[B]*cos(radians([Destination Latitude]))*SIN(radians([Destination Longitude]))
z [A]*SIN(radians([Origin Latitude])) +
[B]*SIN(radians([Destination Latitude]))
lat degrees(atan2([z],sqrt([x]^2+[y]^2)))
long degrees(atan2([y],[x]))
Draw? ( [Layover]*[p_radians_between_points] ) <=
( [d] + [p_radians_between_points] )

Calculation Name

Description

d Distance between the two origin and destination points on the earth’s sphere, measured in radians so the value of d will always be between 0 and pi. This calculation is pulled directly from Williams. Unless you’re a professional geometer, you’ll struggle to understand this calculation.
f Percentage of the distance between the origin and the destination for which to calculate the intermediate point. This number is expressed as a float between zero and one and incorporates the p_points parameter as a way to control the number of points between cities. Because we used a cross join and thereby queried more rows than necessary, the “min” function here ensures that Tableau draws no points beyond our destination city. And because our “Layovers” sheet in Excel has only 150 rows, the “max” function ensures we have enough padding even if the value of p_points is entered below .02.
x, y, z, A and B These terms don’t have specific functional descriptions and are pulled directly from Williams. Unless you’re a professional geometer, you’ll struggle to understand these.
lat, long Final latitude and longitude.
Draw? This Boolean is TRUE for points that should be drawn, FALSE for all the extraneous points that can be ignored.

4. Switch Lat and Long to have their proper geographic roles. Both should bear globe icons as shown here:
EKS&H Tableau Tech Tip - Setting Geographical Roles

5. Move lat to the row shelf and long to the column shelf. 
6. In the row and column shelves, change both lat and long to be dimensions versus measures:
EKS&H Tableau Tech Tip - Setting Dimensions
   
7. You should now be looking at a map. Switch the Marks from Automatic to Line.
8. Drop Layover to the Path shelf.
9. Drop Destination City to the Color shelf.

You should now have map using arcs versus lines.

Now that we have added circle arcs to our map, we can further enhance the visualizations by adding parameter control, adding additional data to our data source, or removing unnecessary intermediate data points.

10. Extra credit: We have one unseen performance issue on this viz: for short routes, we are asking Tableau to draw many more points than necessary. Specifically, Tableau will plot scores of invisible extra points on the destination lat/long; enough to accommodate a 3.14–radian trip across the globe. You can see this overage if you lasso all the points at any destination city and view the underlying data. Let’s reduce this burden by filtering out redundant points. Add the following Data Source Filter to do so:  Draw?=TRUE.

11. Extra credit: Experiment with the lines by changing the number of points.

a. Right click your parameter p_radians_between_points and edit it to change its current value to 0.1 or 0.2. This will give you a good visual understanding of how the lines are being drawn.

b. Right click on p_ radians_between_points and “Add parameter control.” You’ll have a nice box to help you do so easily.

12. Extra credit: Add some city pairs to the Excel sheet. I just used Google Maps to find latitudes and longitudes. You may need to introduce a Route ID field, and add it to the Detail shelf, if you start adding cities outside of Boise. (Try ORD-LHR, for example.)
RESOURCE TYPE

RECENTLY ADDED