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

     

11 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!

  3. Gauthier says:

    Awesome stuff! I am looking at mapping events with a radius and show the points included/excluded, this is just what I need! But I am on 10.5 T-T

  4. Imesh Waasala says:

    Hi Joshua,

    Thank you very much for this awesome work. Was looking for exact same feature and this would be a great help for a Viz I am creating.

    I have a question. My data is in excel format. Do I have to direct them to SQL server? (sorry, I am a newbie and have no idea about working with SQL)

  5. Imesh Waasala says:

    Hi Joshua,

    Thank you very much for this awesome work. Was looking for exact same feature and this would be a great help for a Viz I am creating.

    I have a question. My data is in excel format. Do I have to direct them to SQL server? (sorry, I am a newbie and have no idea about working with SQL)

    Thank you very much!

    • Joshua Milligan says:

      Hi Imesh!
      Thank you!
      Yes, for this specific approach, it does have to be SQL Server. This leverages some specific geospatial tools that are built into SQL Server and not Excel.

  6. Thanks Josh,

    Watched the webinar too. Why can’t we just create Miles of Radius as a Measure, and put it into the size filter?

    Even if it won’t exactly correspond to real distances, at least you would get relative sizes. And you could set the size so that it was approximately accurate.

    • Joshua Milligan says:

      Laurence ,

      Thank you for attending the webinar! You definitely could simply add the radius to the Size shelf. As you noted it would be a relative indication and not absolute or anywhere close to precise. But if it visually conveys what’s important for the audience, it’s certainly a lot less involved solution!

  7. Which SQL Server do you recommend?

Leave a Reply

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