• Draw circles with exact distance radius on maps in Tableau

    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

    Parameter to specify the exact distance of the radius

    • 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.

    Calculation of Miles

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

    Pass-through SQL to get the spatial object

     

    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:

    Customers and Stores with exact distance radius

     

    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!

     

4 Responsesso far.

  1. Peter says:

    About time we got support for SQL geospatial data types. I needed that back in 5.1 ????

    • Joshua Milligan says:

      I’m guessing the Hyper engine has opened up all kinds of possibilities for efficiently handling things like this and that we’ll start to see all kinds of useful things come in the near future.

  2. John Hoover says:

    Looking forward to testing this out. Thank you for showing how to make it happen. This will be very useful in our supply chain analytics.

    Is there a means of using the circle to capture the customers and filter an associated dashboard table similar to using the radius tool?

    • Joshua Milligan says:

      You’re welcome! I haven’t gotten that far, but I think it would be possible to use the SQL Server geospatial tools to determine intersections of geographies. It’s definitely something to pursue!

Leave a Reply

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