Tableau Desktop and Web Authoring Help

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

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

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