How can you leverage SQL Server geospatial tools in Tableau to draw cool curved flight paths (or any great arcs)?  I’m glad you asked!

When I first started learning Tableau, one of the most difficult things I encountered was the Path shelf.  I just didn’t get it.  Why would lines break when you placed certain fields in the view? Why wasn’t my data in the right structure?  What shape of data did I need?  I remember looking at things like Andy Kriebel’s flight map and trying to learn the secret.

I’ve since figured out how to shape the data, even leveraging Tableau Prep to get data ready for a flight path map. But I’m also excited to ignore the Path shelf for a change and leverage the new SQL Server geospatial integration in Tableau to do all kinds of cool things including great arcs andcurved flight paths!

And with this approach to great arcs and curved flight paths:

• No Reshaping your data (probably)
• No Data Densification
• No extra shape files

However, you must be using SQL Server 2008 or later…

Great Circles / Great Arcs in Tableau

I’m by no means the first to implement great circles and great arcs in Tableau (interestingly though, the very first post on this blog was calculating distance with great circles).  Chris DeMartini has a great post here.  He and several others at DataBlick even used it to implement a remake of War Games!  And various people have used different approaches.  Alan Eldridge used Data Densification and R.  And somewhere I saw someone use a shapefile, though I cannot find that source now.  Please let me know of what you have seen in the comments!

So I stand on the shoulders of giants.  Even the SQL code below is adapted from a python script that Chris DeMartini re-wrote as Tableau calculations.  But I’m excited to have a way that removes some of the complexity around data densification and just have a SQL script that you can use without even worrying about what it’s doing.

Great Arcs in Tableau using SQL Server Geospatial tools

Tableau 2018.1 allows you to leverage the geospatial capabilities of SQL Server.  This opens up all kinds of spatial functions and capabilities.  Here is an example:

The arcs were calculated using the script below and then stored in a geography field in a SQL Server table.  If desired you could use a similar method to the pass through SQL discussed here to dynamically calculate the arcs with a live connection (and in fact, the SQL script is written as a function to make that approach easy).  That would give you a lot of options for a dynamic user experience.  But if you already know the origin and destination points, materializing the arc as a geography field is an attractive option.  Either approach allows for storing the geography in a Tableau extract.

SQL Script for calculating Great Arcs / Curved Flight Paths

You might use the script in a couple of ways:

1. Pre-calculate and store the curve in a field of the table
2. Calculate the curve in real-time in Tableau.

In the first approach, you’d simply add a geography field (let’s call it Curve) and then write a statement like this:

UPDATE [tbl_Flights]
SET Curve = dbo.Get_Curve(Orig_Lat, Orig_Lon, Dest_Lat, Dest_Lon, 25)

In the second approach, you’d use a pass-through, Raw_SQL calculation in Tableau, something like this:

RAWSQL_SPATIAL("dbo.Get_Curve(%1,%2,%3,%4,%5)", [Orig_Lat], [Orig_Lon], [Dest_Lat], [Orig_Lon], [Number Points])

And here, finally, is the SQL script.  Feel free to take it and modify it for your needs!

-- =============================================
-- Author: Joshua N. Milligan
-- Create date: 04/28/2018
-- Description: Returns a curve as a geography given
-- origin points, destination points, 
-- and the number of points to plot along the curve
-- =============================================
CREATE FUNCTION [dbo].[Get_Curve] 
(
 @Origin_Lat float, 
 @Origin_Lon float,
 @Dest_Lat float,
 @Dest_Lon float,
 @NumberPoints int
)
RETURNS geography
AS
BEGIN


DECLARE 
 @Orig_Lat_Rad float,
 @Orig_Lon_Rad float,
 @Dest_Lat_Rad float,
 @Dest_Lon_Rad float,
 @Lat_Dif_Rad float,
 @Lon_Dif_Rad float

--convert to Radians
set @Orig_Lat_Rad = RADIANS(@Origin_Lat)
set @Orig_Lon_Rad = RADIANS(@Origin_Lon)
set @Dest_Lat_Rad = RADIANS(@Dest_Lat) 
set @Dest_Lon_Rad = RADIANS(@Dest_Lon)
set @Lat_Dif_Rad = @Orig_Lat_Rad - @Dest_Lat_Rad
set @Lon_Dif_Rad = @Orig_Lon_Rad - @Dest_Lon_Rad

--distance step 1
declare @dist_step1 as float
set @dist_step1 = SQRT(POWER(SIN((@Lat_Dif_Rad)/2), 2) + COS(@Orig_Lat_Rad)*COS(@Dest_Lat_Rad*POWER(SIN((@Lon_Dif_Rad) / 2), 2)))

--distance in km
declare
 @distance_rad as float, 
 @distance_km as float

set @distance_rad = 2*ASIN(case when @dist_step1 > 1 then 1 else @dist_step1 end) 
set @distance_km = 2*ASIN(case when @dist_step1 > 1 then 1 else @dist_step1 end) * 6371.009

declare @FlightPath geography

select @FlightPath = geography::STGeomFromText ('LINESTRING(' + CAST(@Origin_Lon as varchar(255)) + ' ' + CAST(@Origin_Lat as varchar(255)) + ',' + RIGHT(Linestring, LEN(Linestring) - 2) + ', ' + CAST(@Dest_Lon as varchar(255)) + ' ' + CAST(@Dest_Lat as varchar(255)) + ')', 4326).MakeValid() 
from 
(
SELECT 
 stuff( (SELECT ' , '+CAST(Lon as varchar(255)) + ' ' + CAST(Lat as varchar(255)) 
 FROM 
 (
 select 1 as GroupBy, ID,
 DEGREES(ATN2(z, SQRT(POWER(x,2)+POWER(y,2)))) as Lat,
 DEGREES(ATN2(y,x)) as Lon
 FROM 
 (
 select ID,
 A * COS(@orig_lat_rad)*cos(@orig_lon_rad) + B * COS(@dest_lat_rad)*cos(@dest_lon_rad) as x,
 A * COS(@orig_lat_rad)*sin(@orig_lon_rad) + B * COS(@dest_lat_rad)*sin(@dest_lon_rad) as y,
 A * SIN(@orig_lat_rad) + B * SIN(@Dest_Lat_Rad) as z
 from 
 (
 select ID,
 SIN((1-(cast(ID as float)/@NumberPoints))*@distance_rad)/SIN(@distance_rad) as A,
 SIN((cast(ID as float)/@NumberPoints)*@distance_rad)/SIN(@distance_rad) as B
 from (select ID from [Index] where ID <= @NumberPoints) p
 ) as AB

) as LatLon
 ) p2
 ORDER BY ID
 FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
 ,1,1,'')
 AS Linestring
 FROM (
 select 1 as GroupBy, ID,
 DEGREES(ATN2(z, SQRT(POWER(x,2)+POWER(y,2)))) as Lat,
 DEGREES(ATN2(y,x)) as Lon
 FROM 
 (
 select ID,
 A * COS(@orig_lat_rad)*cos(@orig_lon_rad) + B * COS(@dest_lat_rad)*cos(@dest_lon_rad) as x,
 A * COS(@orig_lat_rad)*sin(@orig_lon_rad) + B * COS(@dest_lat_rad)*sin(@dest_lon_rad) as y,
 A * SIN(@orig_lat_rad) + B * SIN(@Dest_Lat_Rad) as z
 from 
 (
 select ID,
 SIN((1-(cast(ID as float)/@NumberPoints))*@distance_rad)/SIN(@distance_rad) as A,
 SIN((cast(ID as float)/@NumberPoints)*@distance_rad)/SIN(@distance_rad) as B
 from (select ID from [Index] where ID <= @NumberPoints) p
 ) as AB

) as LatLon
 ) p1
 GROUP BY GroupBy 
) lines


return @FlightPath

END


GO