Connect to Spatial Data in a Database

You can use spatial columns stored in your database to build map visualisations in Tableau. You can connect to supported spatial data sources and build visualisations with them to analyse spatial data, or you can connect to custom SQL or RAWSQL queries to perform advanced spatial analysis.

Tableau supports direct connections to spatial data using these connectors:

  • Amazon Redshift
  • Microsoft SQL Server
  • PostgreSQL + PostGIS
  • Pivotal Greenplum + PostGIS
  • Oracle
  • Snowflake

For information on using these connectors, see the Microsoft SQL Server, PostgreSQL, Pivotal Greenplum Database, Oracle orSnowflake connector examples.

Supported spatial data and SRIDs

For Microsoft SQL Server, only Geography-type spatial data is supported.

PostgreSQL + PostGIS, Oracle and Pivotal Greenplum + PostGIS support Geography- and Geometry-type fields. Geometry fields must specify the geo-type (for example, POINT, LINESTRING) and SRID to be recognised as a spatial field. Geography fields must specify the geo-type to be recognised.

Note: In PostgreSQL + PostGIS, you can connect to tables with both mixed geometry types (points, lines and polygons), but Tableau won’t render these simultaneously.

The following SRIDs are supported by these connectors:

  • NAD83 (EPSG:4269)

  • ETRS89 (EPSG:4258)

  • WGS84 (EPSG:4326)

In PostgreSQL + PostGIS, SRID always defaults to WGS84 (EPSG: 4326).

Tableau supports both live and extract connections using these connectors.

Connect to spatial columns

  1. Open Tableau and connect to your data.
    For more information on how to connect to data using supported connectors, see the Microsoft SQL Server, PostgreSQL, Oracle, Pivotal Greenplum Database or Snowflake connector examples.
  2. On the Data Source page, in the left pane under Table, drag a table that contains spatial data onto the canvas.

    Spatial columns display the same name they have in your database.

    You're now ready to build a map in Tableau using spatial data. For more information on how to create maps from spatial files, see Build a map view from spatial data.

Use Custom SQL and RAWSQL to perform advanced spatial analysis

Connect to a Custom SQL query

  1. Open Tableau and connect to your database.
  2. On the Data Source page, in the left pane, drag New Custom SQL onto the canvas.

  3. Type or paste your query into the Edit Custom SQL dialog box that appears. For examples of Custom SQL you can use with spatial data, see Custom SQL query examples.

  4. When finished, click OK.

For more information, see Connect to a Custom SQL Query

Custom SQL query examples

The following queries are examples. Note that Custom SQL queries will vary from data source to data source.

If your data has two sets of points, such as schools and parks, and you want to show where they intersect:

SELECT S.[name] as SchoolName, P.[name] as PubSpaceName, S.geog_schools.STBuffer(<Parameters.Radius>) as Geog_School, P.geog_pub_space as Geog_Pub_Space FROM TestSpatial.dbo.seattle_public_schools S LEFT JOIN TestSpatial.dbo.seattle_pub_space P ON S.geog_schools.STBuffer(<Parameters.Radius>). STIntersects(P.geog_pub_space) = 1

This example query produces a row for each case where a public space polygon is within Radius distance (meters) from a school. The result of the query looks like this in the data grid:

Note: This example references a parameter named "Radius". It measures distance (meters) and has the following settings:

  • Data type: Float
  • Current Value: 600
  • Allowable values: Range
  • Minimum: 100
  • Maximum 2,000
  • Step size: 50

Using the parameter, you can adjust the relationship between schools and public spaces dynamically.

For more information, see Use parameters in a custom SQL query.

If you only want data around a certain point

SELECT * FROM [Test_spatial_<username>].[dbo].[SDOT_Collisions] C WHERE C.geom.STIntersects(geography::STGeomFromText('POINT(-122.344706 47.650388)', 4326).STBuffer(1000))=1

This example query limits data to 1000 meters around the POINT. The radius can be a parameter, such as the parameter used in the first example.

Or, rather than a point and radius, a rectangle can be used. For example:

SELECT * FROM [Test_spatial_<username>].[dbo].[SDOT_Collisions] C
WHERE C.geom.STIntersects(geography::STGeomFromText('POLYGON ((-122.3625 47.6642,-122.3625 47.6493,-122.3427 47.6493,-122.3422 47.6642,-122.3625 47.6642))', 4326))=1

The winding order of the polygon points (clockwise vs anticlockwise) determines which set of points you get. You can reverse the winding order with the SQL Server function ReorientObject().

If you get an error about mixed data types

SELECT *, F.geom.STGeometryType() as geomtype FROM [Test_spatial_<username>].[dbo].[us_historic_fire_perimeters_dd83] F
WHERE F.geom.STGeometryType() = 'MultiPolygon' OR F.geom.STGeometryType() = 'Polygon'

This example query filters out the geometry-type spatial data, since Tableau can’t render geometry-type spatial data for Microsoft SQL Server connections. You can select other spatial types as well.

If your data renders too slowly in Tableau

SELECT [Id], [OBJECTID], [fire_num], [year_], [acres], [fire_name], [unit_id], [Shape_Leng], [Shape_Area], [geom].Reduce(500) as SimpleGeom
FROM [Test_spatial_<username>].[dbo].[us_historic_fire_perimeters_dd83]
WHERE [geom].Reduce(500).STGeometryType() = 'MultiPolygon' OR [geom].Reduce(500).STGeometryType() = 'Polygon'

This example query may greatly reduce data size. (Some objects are reduced to Points, and these are discarded.)

Use RAWSQL

To use RAWSQL with spatial data, you can create a calculated field using either the RAWSQL_SPATIAL or RAWSQLAGG_SPATIAL functions. For example: 

RAWSQL_SPATIAL("Select %1.STIntersection(%2.STBuffer(200))",[school_geom], [park_geom])

This formula returns unique spatial data based on the intersection of two values.

RAWSQLAGG_SPATIAL("ST_ConvexHull(ST_Collect(%1))", [Geog])

RAWSQLAGG_SPATIAL("ST_ConcaveHull(ST_Collect(%1), %2, false)", [Geog], [ParameterValue])

Each of these return a bounding area around the supplied data points. ConcaveHull is a minimum bounding area based on the precision, which in this function is the parameter value.

For more information about using RAWSQL with spatial data, see Pass-Through Functions (RAWSQL). For more information about how to create calculated fields, see Create a calculated field.

Note: RAWSQL spatial functions aren’t available when connected to Oracle.

Note: It's possible to cause errors when using RAWSQL specific to PostGIS functions when using PostgreSQL + PostGIS.

Troubleshooting spatial connections

Performance issues

When working with joined spatial columns with a large number of records, duplicate marks can occur. This can result in slow performance.

To improve performance, extract the joined data source. For more information, see Extract Your Data.

Microsoft SQL Server error messages and solutions

When the spatial table is using an unsupported spatial reference

Error message:

An error occurred while communicating with the Microsoft SQL Server data source '<data source name>'.​
Bad Connection: Tableau couldn’t connect to the data source.​
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Operand type clash: geometry is incompatible with geography​
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared.​

Solution:

  • Export the spatial data from Microsoft SQL Server to a Shapefile and connect to it in Tableau. Spatial file connections go through a data transformation process that supports thousands of projections.​

  • Export the data from Microsoft SQL Server and transform the data using a GIS tool like QGIS or ArcGIS. Then reload the table back into Microsoft SQL Server. ​​

When the spatial table includes multiple types of spatial objects

Error message:​

Unable to complete action​
The spatial operation resulted in a MixedGeometry or MixedGeography, which Tableau doesn’t support yet.​
The spatial operation resulted in a MixedGeometry or MixedGeography, which Tableau doesn’t support yet.​

Solution:

Use Custom SQL to filter the data. For more information, see If you get an error about mixed data types Custom SQL example.

When measures are being aggregated in the visualisation

Error message:

An error occurred while communicating with the Microsoft SQL Server data source '<data source name>'
Bad Connection: Tableau could not connect to the data source.
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Solution:

Disaggregate measures in the visualisation: Select Analysis, and then clear Aggregate Measures.

PostgreSQL + PostGIS, Oracle and Pivotal Greenplum + PostGIS error messages and solutions

When using mixed SRIDs and one of the SRIDs is unsupported

Error message:

An error occurred while communicating with the PostgreSQL data source '<data source name>'.
Bad Connection: Tableau could not connect to the data source.
Error: Operation on mixed SRID geometries;
Error while executing the query
<query> from <datasource>
ERROR: Operation on mixed SRID geometries;
Error while executing the query
SELECT ST_ForceCollection(ST_Collect("mixed_SRID_geography"."location"::geometry)) AS "clct:location:nk"
FROM "public"."mixed_SRID_geography" "mixed_SRID_geography"HAVING (COUNT(1) > 0)

Solution:

Disaggregate measures in the visualisation: Select Analysis, and then clear Aggregate Measures. This eliminates the error message and the data where the SRID is unsupported.

When the spatial table is using an unsupported spatial reference

In PostgreSQL + PostGIS, you can connect to a table with unsupported SRIDs, but the Geometry column will display as "Unknown".

Solution:

  • Export the spatial data from PostgreSQL + PostGIS to a Shapefile and connect to it in Tableau. Spatial file connections go through a data transformation process that supports thousands of projections.​

  • Export the data from PostgreSQL + PostGIS and transform the data using a GIS tool like QGIS or ArcGIS. Then reload the table back into PostgreSQL + PostGIS. ​​

When a query result exceeds the limit of 1 GB

Error message:

ERROR: array size exceeds the maximum allowed (1073741823); Error while executing the query"
SELECT ST_Collect(geom) FROM <data source>

Solution:

Rewrite the calculation to be more efficient. See Best Practices for Creating Calculations in Tableau or Create Efficient Calculations.

 

Example - Build a map from Microsoft SQL Server spatial data

The following example demonstrates how to create the map below, which shows schools within 600 meters of parks in Seattle, Washington.

This example uses a Microsoft SQL Server connection to a database called, TestSpatial. The following tables from this database are used:

  • seattle_schools
  • seattle_public_space

Step 1: Connect

  1. Open Tableau Desktop and connect to Microsoft SQL Server.

  2. On the Data Source page, in the left pane, drag New Custom SQL onto the canvas.

  3. Type or paste a query into the Edit Custom SQL dialog box that appears.

    For this example, the following query is used:

    SELECT S.[name] as SchoolName, P.[name] as PubSpaceName, S.geog_schools.STBuffer(<Parameters.Radius>) as Geog_School, P.geog_pub_space as Geog_Pub_Space FROM TestSpatial.dbo.seattle_public_schools S FULL JOIN TestSpatial.dbo.seattle_public_space P ON S.geog_schools.STBuffer(<Parameters.Radius>). STIntersects(P.geog_pub_space) = 1

    Note: This example references a parameter named "Radius". For more information, see Use parameters in a custom SQL query.

    The result of this query looks like the following:

Step 2: Build the map

  1. Go to a new worksheet.

  2. In the Data pane, double-click a spatial field.

    In this example, Geog_School is used. When double-clicked, it’s automatically added to Detail on the Marks card and a map view is created.

  3. From the Data pane, drag a dimension field to Labelon the Marks card. In this example, School Name is used.

  4. Select Analysis > Create Calculated field.

  5. In the Calculation editor that opens, do the following:

    • Name the calculated field. In this example, the calculated field is named Intersection.

    • Enter a RAWSQL formula. This example uses the following formula:

      RAWSQL_SPATIAL ("Select %1.STIntersection(%2)", [Geog_School], [Geog_Pub_Space])

      This formula returns unique spatial data based on the intersection of two geometries.

    • When finished, click OK.

  6. Select Analysis, and then clear Aggregate Measures.

  7. From the Data pane, drag the new calculated field (in this case, Intersection) to a new layer on the worksheet.


    The map updates with a new layer of polygons from the Intersection field. The marks are unique spatial shapes because of the RAWSQL formula.

  8. From the Data pane, drag a dimension field to Detail on the Marks card. This disaggregates the marks.

    In this example, the dimension PubSpaceName is used.

  9. From the Data pane, drag the same dimension field to Colour on the Marks card.

    In this example, each public space is now assigned a colour.

  1. Format the map. For more information, see Customise How Your Map Looks

    In this example, the following formatting is applied:

    • The School buffer marks (Geog_School) are coloured grey.

    • The School Name mark labels are coloured white.

    • The map background style is set to Dark.

    • The Streets and Highways/Motorways layer is shown on the background map.

    The map is now finished.

See Also

Create Tableau Maps from Spatial Files

Connect to a Custom SQL Query

Pass-Through Functions (RAWSQL)

Get Started with Calculations in Tableau

Spatial Join Slow and Returns Inverted Results Using KML or SQL Spatial Data Imported from Shapefile or GeoJSON

 

Thanks for your feedback!Your feedback has been successfully submitted. Thank you!