Draw circles with exact distance radius on maps in Tableau

• By Joshua Milligan
• Posted in All, Calculations, DataViz, Mapping / Geographic Visualization, New Features, Tips and Tricks
• On April 24, 2018
• 21 Comments.

Tableau 2018.1 introduces a lot of new features, especially geospatial features that open a whole new world of data exploration and analysis.  With support for SQL Server geospatial data types, there are near-infinite possibilities.  I’m sure I’ll have more than a few posts on various options soon!  Here’s one thing you can do: render circles with a radius of an exact distance on maps, like this:

By the way, want to see this in action? You missed the webinar on April 25th, but don’t worry – There’s a recording here!

And, it turns out, this is fairly easy to accomplish in Tableau 2018.1.

Creating a geographic function in SQL Server to draw the exact distance

I started by created a function in SQL Server that returns a point that is buffered out to an exact distance:

-- =============================================
-- Author: Joshua Milligan
-- Description: Creates a circle geography based on center point (lat/lon) and radius in meters
-- =============================================
CREATE FUNCTION [dbo].[Get_Circle] 
(
 @Lat float, 
 @Lon float,
 @Radius_Meters float
)
RETURNS geography
AS
BEGIN

DECLARE @g geography; 
SET @g = geography::STGeomFromText('POINT(' + CAST(@Lon as varchar(255)) + ' ' + CAST(@Lat as varchar(255)) + ')', 4326).BufferWithTolerance(@Radius_Meters, .5, 0);

return @g

END

As you can see, the function takes 3 arguments: latitude, longitude, and a radius.  The point is created from a linestring defining a point at the latitude and longitude.  The radius is passed to the BufferWithTolerance() function to grow the point to a circle of the exact distance specified.  With the spatial reference id of 4326 used in the geometry definition, the exact distance is defined as meters.  With that, I have something I can use in Tableau.

Using the function in Tableau

I’ve been using Tableau for over half a decade and as long as I remember, Tableau has had pass-through SQL functions.  Basically these functions allow you to pass raw SQL directly through VizQL to the generated query.  I don’t think I’ve ever really used them…

…until now!

But Tableau 2018.1 introduces a new pass-through function RAWSQL_SPATIAL() which allows you to write SQL that returns a spatial object.

And that was the key, but first, a bit of setup:

• A live connection to a data set in SQL Server containing latitude and longitude (you can extract it after the fact and thus capture the Spatial object in a .hyper file which you could even join or blend to other sources, but the pass-through functions only work with a live connection)
• A parameter to specify distance in miles

• A calculation to to apply the parameter value to records of “stores” while setting records of “customers” to a distance of a quarter of a mile.

• Another calculation to pass through the SQL function call by passing the latitude, longitude and radius converted from miles to meters

 

And that final calculation gives me a spatial field that renders with a double-click!  With a bit of Tableau visualization magic, the final view not only looks great, but gives me a real-time dynamic tool to evaluate my customer locations in relation to the distance from my stores:

 

And that, is just the tip of the iceberg when it comes to what can be done with the new SQL Server geospatial support in Tableau 2018.1.  What will you do? I’d love to hear your ideas!