Connect to Spatial Data in a Database

You can use spatial columns stored in your database to build map visualizations in Tableau.

Tableau supports direct connections to spatial data using these connectors:

  • Microsoft SQL Server
  • PostgreSQL + PostGIS
  • Pivotal Greenplum + PostGIS

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

Supported spatial data and SRIDs

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

PostgreSQL + PostGIS and Pivotal Greenplum + PostGIS support Geography- and Geometry- type fields. Geometry fields must specify the geo-type (e.g., POINT, LINESTRING) and SRID in order to be recognized as a spatial field. Geography fields must specify the geo-type in order to be recognized.

Note: In PostgreSQL + PostGIS, you can connect to tables with both mixed geometry types (points, lines, and polygons), but Tableau will not 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, or Pivotal Greenplum Database 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 will display the same name they have in your database.

    You are 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.[common nam] as SchoolName, P.[common nam] as ParkName, S.geom.STBuffer(<Parameters.Radius>) as school_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) = 1

This example query will produce a row for each case where a park 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

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, like 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

Note: the winding order of the polygon points (clockwise vs. counter-clockwise) 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 not 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 will 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: 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 could not 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 does not support yet.​
The spatial operation resulted in a MixedGeometry or MixedGeography, which Tableau does not 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 visualization

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 visualization: Select Analysis, and then clear Aggregate Measures.

PostgreSQL + PostGIS 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 visualization: Select Analysis, and then clear Aggregate Measures. This will eliminate 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 1GB

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:

  • seattleelementaryschools
  • allseattleparks

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.[common nam] as SchoolName, P.[common nam] as ParkName, S.geom.STBuffer(<Parameters.Radius>) as school_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) = 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, school_geom is used. When double-clicked, it is automatically added to Detail on the Marks card and a map view is created.

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

  4. On the Rows shelf, control-click (Command-click on a Mac) and drag Latitude(generated) to the right. This copies the field.

    A duplicate map appears below the first map and the Marks card updates with two Latitude(generated) tabs. The top tab is for the map on the top and the bottom tab is for the map on the bottom.

  5. On the Marks card, click the bottom Latitude(generated) tab, and remove the fields by dragging them back to the Data pane.

    The bottom map updates to a blank map.

  6. Select Analysis > Create Calculated field.

  7. 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.STBuffer(200))",[school_geom], [park_geom])

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

    • When finished, click OK.

  8. Select Analysis, and then clear Aggregate Measures.

  9. From the Data pane, drag the new calculated field (in this case, Intersection) to Detail on the Marks card.

    The bottom map updates with new marks. Notice that the marks are unique spatial shapes due to the RAWSQL formula.

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

    In this example, the dimension, ParkName, is used.

  11. From the Data pane, drag the same dimension to Color on the Marks card.

    In this example, each park is now assigned a color.

  12. On the Rows shelf, right-click the Latitude (generated) field on the right and select Dual Axis.

    The maps are now combined and the data is layered. For more information, see Create Dual-Axis (Layered) Maps in Tableau.

  13. Format the map. For more information, see Customize How Your Map Looks

    In this example, the following formatting is applied:

    • The School Name marks are colored gray.

    • The School Name mark labels are colored orange .

    • The map background style is set to Dark.

    • The Streets and Highways 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

Thanks for your feedback! There was an error submitting your feedback. Try again or send us a message.