• Great Circles & Curved Flight Paths: Tableau & SQL Server Geospatial

    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!


    Curved Flight Paths in Tableau
    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

Leave a Reply

Your email address will not be published. Required fields are marked *