A typical map is a two-dimensional representation of a three-dimensional sphere. When we draw paths, we know that the shortest distance between two points is a straight line. Nevertheless, the shortest path on the surface of a sphere does not necessarily look like a straight line on a two-dimensional map.
In this post, we explain how to draw paths that connect two locations on a map based on the shortest distance between them using Tableau. We use a concept called great-circle distance, which is the shortest distance between two locations on the surface of a sphere.
We have included the formulas of all the calculated fields at the end of this post so they can be copy-pasted as needed!
A. DATA
The data needs to have the following form:
We have a column with the Name of the locations. We have a column for the Latitude and Longitude of each location (*, **). We have a column that defines the origin and destination for each pair of locations. Finally, we have an ID column that indicates the origin-destination pairs. For example, for ID == 1, there is a path between the origin, Sydney (Australia), and the destination, Buenos Aires (Argentina).
B. DRAWING PATHS WITH TWO-DIMENSIONAL STRAIGHT LINES
As a first step, we can quickly create the paths using two-dimensional straight lines. To do this we must make sure we are not aggregating the measure. Then we:
- Drag Lon to Column
- Drag Lat to Row
- Change the Mark to Line
- Drag ID to Detail
- Drag PathOrder (bin) to Path.
The result is the chart shown above which presents the connections between the cities. However, the connections are simple straight lines in two dimensions, but NOT the shortest paths on a sphere.
For some applications, this chart will suffice. However, if the goal is the shortest path distance, clear your sheet, and continue with the next steps.
C. GREAT CIRCLE EQUATIONS
A great circle is the shortest distance between two locations across the surface of a sphere. I will not go into the details of how to derive the equations. Instead, we present the equations required.
Where Lat_Start and Lon_Start are the latitude and longitude at the origin, and Lat_End and Lon_End are the latitude and longitude at the destination. f is a value that will range between 0 and 1.
D. CALCULATED FIELDS
Here, we present all the fields that require calculations.
D.1. PATH ORDER
We have a field that indicates the path order, in other words, it indicates which is the origin and destination. We will make Origin equal to 0, and Destination equal to 1.
D.2. PATH ORDER DENSIFICATION – PathOrder (bin)
We need a path with a set of points between the start and end locations. Therefore, we generate a data-set that contains the missing values. This process is referred to as “densification”. To do this, we create “Bins” from the PathOrder.
The PathOrder has values of 0 and 1. To define the “Size of the bins”, we first determine how many points we want to use to draw the line. For example, if we want N = 101 points, the "Size of the bins" is given by 1/(N-1) = 1/100 = 0.01.
D.3. LATITUDE AND LONGITUDE START AND END VALUES
We now create the values for the latitude and longitude for the start and end locations.
Why do we need these calculations if we already know the values from Lat and Lon?
In the table below we observe that the values of Lat only exist in PathOrder == 0 for the origin, and PathOrder == 1 for the destination. The new calculated fields separate these two values into two different columns (LatStart and LatEnd), and fills in the densified points obtained by the PathOrder (bin). For these fields, “Compute Using” the PathOrder (bin). The same logic applies to Lon.
D.4. GREAT CIRCLE CALCULATIONS
Below we present the calculated fields for d, f, A, B, X, Y, Z, NewLat and NewLong. We will first need an Index field.
d, f, A and B are defined as:
Note that the Lat and Lon values are in degrees, and we require them to be in RADIANS!
X, Y and Z are defined as:
NewLat and NewLon are defined as:
Note that the NewLat and NewLon values are in radians, and we require them to be in DEGREES!
E. VISUALIZING THE MAP PATHS
To visualize the map paths, we must make sure we are not aggregating the measure. Then we need to:
- Convert NewLat and NewLon to Geographic Role -> Latitude and Longitude, respectively.
- Drag ID to Detail.
- Drag Path Order (bin) to Rows. Make sure the “Show Missing Values” is checked!
- Drag the Path Order (bin) from Rows to Details.
- Drag NewLon and NewLat to Columns and Rows, respectively; and “Compute Using” PathOrder (bin).
- Change the Mark to Line.
- Drag PathOrder (bin) to Path.
- Format as you wish!
Feel free to play with the map below!
Formulas:
Here, you can copy-paste the formulas into your calculated fields.
d =
ACOS(SIN(RADIANS([LatStart]))*SIN(RADIANS([LatEnd]))+
COS(RADIANS([LatStart]))*COS(RADIANS([LatEnd]))*
COS(RADIANS([LonStart]-[LonEnd])))
f =
([Index]-1)/(WINDOW_MAX([Index])-1)
A =
SIN((1-[f])*[d])/SIN([d])
B =
SIN([f]*[d])/SIN([d])
X =
[A]*COS(RADIANS([LatStart]))*COS(RADIANS([LonStart]))+
[B]*COS(RADIANS([LatEnd]))*COS(RADIANS([LonEnd]))
Y =
[A]*COS(RADIANS([LatStart]))*SIN(RADIANS([LonStart]))+
[B]*COS(RADIANS([LatEnd]))*SIN(RADIANS([LonEnd]))
Z =
[A]*SIN(RADIANS([LatStart]))+
[B]*SIN(RADIANS([LatEnd]))
NewLat =
DEGREES(ATAN2([Z],SQRT([X]^2+[Y]^2)))
NewLon =
DEGREES(ATAN2([Y],[X]))
* Tableau does not allow to drag the generated Latitude and Longitude fields into Calculated Fields; therefore, we need to have these defined in the data.
**West Longitudes need to be written in negative form. For example, Caracas is 10.4806˚N and 66.9036˚W. We would use the values 10.4806 and -66.9036. This does not apply to East Longitudes, nor any of the Latitudes.