Visualize geographic spatial data from SQL Server directly in Tableau 2018.1
This is a partner post authored with the Tableau team.
As part of Microsoft’s open and flexible platform for data and analytics, we’re always excited when partners add features that will expand customer options and extend business functionality. Business intelligence partner Tableau has recently introduced enhancements that will make it easier for their users to process spatial data stored in SQL Server.
The SQL Server database engine has two spatial types – geometry and geography. The geography spatial type helps organize geospatial mapping data into SQL Server tables and works with several SQL-native graphing functions to answer questions like how far apart two geographic locations are, or what locations fall within a certain radius.
Before, customers had to process geography spatial data stored in SQL Server into Shapefiles before they could access it from Tableau. Now with Tableau 2018.1, customers can connect to and visualize data stored in SQL Server directly. This means Tableau will recognize spatial data in your SQL Server tables without any intermediate steps, and customers can leverage spatial operations for geography supported by SQL Server or custom queries to work with geographic data stored in SQL Server. Alongside support for native spatial data, Tableau 2018.1 also includes new RAWSQL functions that can leverage the spatial operations from the database and allow you to create calculations that return generated spatial objects.
Here are some steps to try it out:
Get started with spatial data
Tableau will recognize if your table has a spatial field and allow you to utilize it, with no intermediary steps. Just drag and drop the geographic field to get started.
Using spatial operations
Connections to SQL Server support Custom SQL queries. You can use a Custom SQL query to leverage operations supported by the database. Let’s say you wanted to understand how many parks are located near schools. You could perform a proximity analysis using the query below to produce the following visualization. This query uses a spatial join based on a buffer of the schools’ point geometry.
SELECT S.[common nam] as SchoolName, P.[common nam] as ParkName, S.geom.STBuffer(750) as circle_geom, P.[Geom] as park_geom FROM TestSpatial.dbo.seattleelementaryschools S LEFT JOIN TestSpatial.dbo.allseattleparks P ON S.geom.STBuffer(750).STIntersects(P.geom) = 1
Make the data dynamic by adding parameters to the query. You can also apply a buffer to the park geometries to make them more prominent and to create a polygon geometry that can be used in further analysis.
SELECT S.[common nam] as SchoolName, P.[common nam] as ParkName, S.geom.STBuffer(<Parameters.Radius>) as circle_geom, P.[Geom] as park_geom FROM TestSpatial.dbo.seattleelementaryschools S LEFT JOIN TestSpatial.dbo.allseattleparks P ON S.geom.STBuffer(<Parameters.Radius>).STIntersects(P.geom.STBuffer(200)) = 1
Expand your analysis with RAWSQL
Support for SQL Server Spatial includes a new set of RAWSQL functions that are usable in calculations. RAWSQL functions allow you to pass values into a SQL statement where the query is executed when the calculated field is utilized within the worksheet. This means you can leverage the spatial operations supported by the database to generate a new spatial object. The calculation below returns the INTERSECTION of two spatial objects—in this case, the school and park buffers. STIntersection is like a cookie-cutter operation. The resulting geometry will represent only the area that was shared by the two inputs.
RAWSQL_SPATIAL("Select %1.STIntersection(%2.STBuffer(200))",[circle_geom], [park_geom])
You can put buffered parks and the intersection results on a dual axis to produce a visualization that only shows the areas that overlap between the two fields.
Join the pre-release community to test new geospatial features as they’re released in beta. We’re excited to see how you leverage this feature to create even more beautiful (and useful) maps!
For more information on this feature, read up on SQL Server documentation for geography types or visit the Tableau Help page. Learn about other features in the 2018.1 release on our feature highlights page.
