In this post I’ll walk through the process of pushing spatial data into PostgreSQL and SQL Server Express for use in Tableau.  The process should be similar for other spatial file formats, but, since shapefiles are probably the most common source I’m just going to focus on that process for now.

 

Tools that I use:

  • OSGeo4W – a set of open source geospatial tools. Includes QGIS, GDAL/OGR, GRASS, etc. I use QGIS and OGR quite a bit when manipulating data and pushing into databases
  • SQL Server Express – I use SQL Server Management Studio to interact with the database
  • PostgreSQL - I currently use OmniDB to interact with the database, but have used pgAdmin as well (I don’t like the new browser-based version – just a personal preference, nothing wrong with it otherwise)
  • PostGIS & PostGIS 2.0 Shapefile and DBF Loader Exporter – the shapefile importing tool comes with the OSGEO installation of PostGIS

 

Tips:

  • Tableau supports three coordinate systems for data in spatial databases.
    • NAD83 (EPSG: 4269)
    • ETRS89 (EPSG: 4258)
    • WGS84 (EPSG: 4326)

 

Transform your data into one of these before you put it into the database if you can. If you’re working with data in SQL Server you need to make sure that you’re starting with the data in a geographic coordinate system; with PostgreSQL you’ll have more flexibility to transform the data so you can always update later if needed.

 

More information on coordinate reference systems, identifying them, and updating them in a separate blog post here: Spatial SQL - Identify and Update Coordinate Reference Systems

 

  • Data in the database may be either GEOMETRY or GEOGRAPHY
    • For SQL Server – Tableau will visualize GEOGRAPHY
    • For PostgreSQL – Tableau will visualize GEOGRAPHY or GEOMETRY

Data that I use in this post:

  • Las Vegas Animal Shelters (a shapefile of point data).  I've attached two versions of this file to the post so that you can try it out.  Each uses a different coordinate reference system.
    • AnimalShelters_4326 - the shapefile in WGS84 coordinate reference system (EPSG 4326)
    • AnimalShelters_102707 - the shapefile in State Plane, Nevada East coordinate reference system (EPSG 102707)

 

There are two sections in this post - scroll to the one you want

 

  • PostgreSQL / PostGIS
  • SQL Serverl

 

 

Spatial data into PostgreSQL / PostGIS

Assumptions:

For reference, I am using:

PostgreSQL:  Latest version from EnterpriseDB

PostGIS: Install from OSGEO (postGIS, osmconvert, osm2pgrouting)

 

I’m also assuming that you already have a spatially enabled database to work with. If not, make one.

 

PostgreSQL (via pgAdmin) – the process will be similar with other database management tools, just make the database and then create the  PostGIS extension.

Create the database

 

 

Then create the postgis extension so that you can work with spatial data

 

 

 

Import data with either shapefile import/export GUI or OSGeo4W / OGR

PostgreSQL is a little more friendly when trying to deal with data in projected coordinate systems because it has nice tools for transforming the data.   In order to take advantage of those tools, you just need to make sure you have the right CRS defined to start.  It’s easiest to just define that when importing the data, but you can update it after the fact if you forget.

 

With Shapefile import/export manager – it’s easy and graphical!  Fun!

 

Open the PostGIS Shapefile Import/Export Manager, connect to the database, Add a File, set the SRID (spatial reference ID...this is the code for the coordinate reference system) and click Import.  If you don’t set the SRID, it defaults to 0 and you have to write a query to change it in the database).    If you don’t already know it, you can look up the SRID on the EPSG or Spatial Reference web sites.  More on identifying and updating coordinate reference systems on this supplemental post: Spatial SQL - Identify and Update Coordinate Reference Systems

With OSGeo4W and OGR

Importing data that is already in an appropriate coordinate system

All you need to do is type in a "simple" command on the command line.  Use ogr2ogr to connect to your database, enter the right credentials, reference your shapefile location and coordinate reference system...

 

 

ogr2ogr -f "PostgreSQL" PG:"dbname=<your database> host=localhost port=5432 user=<your user name> password=<your password>" "c:\temp\myShapefile.shp" -a_srs EPSG:4326

 

The geometry will be in a column named ‘wkb_geometry.’  This is different than the name for the column if you use the GUI PostGIS 2.0 Shapefile Import/Export Manager.  If that is important to you, you can rename it by adding:

-lco geometry_name=<name>

 

Importing data that needs to be re-projected first – OGR is great for this!

ogr2ogr -f "PostgreSQL" PG:"dbname=<your database> host=localhost port=5432 user=<your user name> password=<your password>" "c:\temp\myShapefile.shp" -s_srs EPSG:26911 -t_srs EPSG:4326

 

For example, if I were importing the Animal Shelters shapefile that was NOT in one of the coordinate systems that Tableau likes to read natively from PostgreSQL I would type in this to import the shapefile and change the coordinate reference system at the same time (the CRS updating is in bold below):

ogr2ogr -f "PostgreSQL" PG:"dbname=<your database> host=localhost port=5432 user=<your user name> password=<your password>" "c:\temp\AnimalShelters_.shp" -s_srs EPSG:102707 -t_srs EPSG:4326

 

 

Adding a GEOGRPAHY column

You don’t really need to create a geography column, since Tableau will work with both GEOMETRY and GEOGRAPHY from PostgreSQL, but if you want GEOGRAPHY it’s a simple query in PostgreSQL:

 

Alter table myTable

Add column geog geography;

 

Update myTable

Set geog = ogr_geometry::geography

-- I used the field name ‘ogr_geometry’ but use whatever your geometry field is actually named

 

Transforming data in PostgreSQL (if needed)

If you need to do a transformation once the data is in PostgreSQL, you can easily update the SRID to one of the supported CRS.  The important thing to note in the examples below is that we’re explicitly defining the geometry type (e.g., POINT) and the CRS for the new column.  This will help ensure that Tableau can properly recognize the geometry in the new column.

 

Alter table myTable

Alter column geom

Type Geometry(Point, 4326)

Using ST_Transform(geom, 4326);

 

If you don’t want to alter the original CRS, you can add a new column and use ST_Transform and just have multiple geometry columns!

 

Alter table myTable

Add column geom_4326 geometry(point, 4326);

 

Update myTable

Set geom_4326 = st_transform(geom, 4326);

 

You could even do this in Tableau with Initial SQL, Custom SQL, or RawSQL if you really need to (i.e., you can’t alter data in the database that you are accessing), but you’ll likely take a performance hit.  If you have to go this route, I recommend Initial SQL since that will just run once when you load the workbook.

Or:

------------

 

Spatial data into SQL Server

Assumptions:

I’m assuming that you already have a spatially enabled database to work with. If not, make one.

 

SQL Server (via Microsoft SQL Server Management Studio)

 

 

Import data with OSGeo4W and OGR

Tableau likes data from SQL Server to be of a GEOGRAPHY data type (latitude and longitude so that it is analyzed as if it’s on the surface of the earth, as opposed to data being in a GEOMETRY data type where the data is analyzed as if it’s projected onto a flat surface). 

 

You can only convert GEOMETRY to GEOGRAPHY if the coordinate system is already latitude and longitude.

 

SQL Server doesn’t have functionality for easily transforming the projection of the data, so you’ll either want to do that in advance, (See the section on Spatial SQL - Identify and Update Coordinate Reference Systems to learn more about tranforming data into an appropriate geographic coordinate system (e.g., NAD83, WGS84, ETRS89) or you can do the transformation at the same time as you import the data into SQL Server with OGR

 

For fun, try this with a dataset in a projected coordinate system – I’ve attached one to this post (see the AnimalShelters_102707 shapefile)

 

When you try to create the GEOGRAPHY field you’ll get an error like this:

 

Msg 6522, Level 16, State 1, Line 2

A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": System.FormatException: 24201: Latitude values must be between -90 and 90 degrees….

 

If you try to open the GEOMETRY field in Tableau, you’ll get an error like this:

 

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Operand type clash: geometry is incompatible with geography

 

 

Importing data that is already in an appropriate coordinate system

 

ogr2ogr -f "MSSQLSpatial" "MSSQL:server=localhost\SQLEXPRESS;Database=tc19;Trusted_Connection=yes" myShapefile.shp -a_srs EPSG:4326 -unsetFieldWidth

 

With SQL Server, I’ve had a reasonable number of datasets that give me arithmetic overflow errors related to converting numeric to data type numeric. This seems to be an issue with long numeric identifiers, so I often add the flag to -unsetFieldWidth  You don’t always need that flag to import

 

Importing data that needs to be re-projected first – OGR is great for this!

ogr2ogr -f "MSSQLSpatial" "MSSQL:server=localhost\SQLEXPRESS01;Database=tc19;Trusted_Connection=yes" myShapefile.shp -s_srs EPSG:102707 -t_srs EPSG:4326 -unsetFieldWidth

 

 

Make a field of GEOGRAPHY type so that we can visualize in Tableau.

 

The query below simply takes a data table (from an imported shapefile) and adds a new column called “ogr_geography” that is a GEOGRAPHY data type

 

alter table tableName

add ogr_geography geography;

 

If you’re working with POLYGON data and your original data source was a shapefile or something that has similar vertex ordering semantics, you’ll need to re-order the vertices (more information on this problem here and information about the process of re-ordering the vertices here)

 

update tableName

set ogr_geography = ogr_geometry.MakeValid().STUnion(ogr_geometry.MakeValid().STStartPoint()).MakeValid().STAsText()

 

If you’re working with POINT, LINE, or data that already has a semantic that matches the vertex ordering in SQL Server, you can just create your GEOGRAPHY column like this:

alter table tableName

add ogr_geography geography

 

GO

 

update tableName

set ogr_geography = ogr_geometry.MakeValid().STAsText()

 

How would you know if you have a vertex ordering problem? (because it’s not like most people enjoy thinking about the winding order of the vertices in their geographic polygons!) I check this by viewing the GEOGRAPHY in Tableau and checking if 1) it’s crazy slow, 2) a spatial intersection shows inverted results (e.g., the points identified as ‘inside’ the polygons are outside), or 3) the bounding box for the data is ALWAYS global scale (i.e., if you filter to just one polygon you still default to a global-scale zoom)

 

You should now have a GEOGRAPHY type column in SQL Server that is ready to work with in Tableau.