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:
- Pre-calculate and store the curve in a field of the table
- 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
Hello,
we are trying to create this get_curved function but got stuck at
select ID from [index]
can you please let us know how to get this [index] table?
thanks
Hi TB,
[INDEX] is just a table I created in my database with a field named ID that increments (1, 2, 3, 4, etc…). Just give it enough rows to cover as many points as you will ever want to generate.
Hi, thanks very much for these instructions, it’s extremely useful! I have one issue that I can’t resolve, I have multiple lines representing each curve where the line is sort of drawn back and forth but generally shows an arc. Do you have any thoughts on how to resolve this?