This discussion supports the blog post series on working with data in Tableau via a database that supports spatial analysis (e.g., PostgreSQL / PostGIS or SQL Server). It provides background for identifying and updating the coordinate system in your spatial data.
These instructions are only going to be useful if you already have a CRS defined in your dataset. If you don’t have a CRS defined and you don’t know what it is, that is an entirely different (and sometimes super challenging) problem.
Tableau currently supports three coordinate systems for data in spatial databases:
- NAD83 (EPSG 4269)
- ETRS89 (EPSG 4258)
- WGS84 (EPSG 4326)
To work with your data directly in Tableau, you will need to transform your spatial data into one of these three coordinate systems. You can do this in advance (before your data is loaded into the database), during the process of loading the data into the database (using ogr2ogr), or afterwards by altering the coordinate system of the data in the database (in PostgreSQL only).
When you add spatial data to a database, you should always define the coordinate reference system (CRS) on import. You can do it afterwards, but there is high likelihood of forgetting and then losing a bunch of time trying to figure out why none of your queries are working or why Tableau won’t show your data. So – just make it a practice to define the CRS when you initially drop the data into the database.
You might already know the CRS because all of the data from your source is exactly the same. In that case, just move forward with importing your data. But…if you don’t already know the CRS, or you want to check and be sure, here is what I do:
- Check if there is a .prj file with your shapefile. This defines the projection, or CRS, for the file. Even though the extension is .prj, the file is just a plain text file. Open it with your favorite text editor (Notepad, Sublime, etc.)
- Take a look at the first part of the text in the .prj – that should be your coordinate system. For instance, this text indicates the dataset uses WGS84 (in bold below):
- Figure out the spatial reference ID (SRID) for the CRS. I generally use the search tool in spatialreference.org or epsg.io for this. You’ll want to use the EPSG code for defining your SRID. Here are some common SRIDs so you don’t have to look them up:
- WGS84 (World geodetic survey of 1984): SRID 4326
- NAD83 (North American Datum of 1983): SRID 4269
- ETRS89 (European Terrestrial Reference System of 1989): SRID 4258
How do you change the coordinate reference system for your data?
As mentioned above, Tableau supports three coordinate systems for data in spatial databases (yes, I'm saying this same thing over and over again...write it down on a sticky note and put it somewhere visible...if you work with spatial databases and Tableau you'll want to burn these numbers into your brain):
NAD83 (North American Datum 1983)
ETRS89 (European Terrestrial Reference System 1989)
WGS84 (World Geodetic Survey 1984)
If you want your data to just work without having to do any serious data transformations in the database, transform your data into one of these coordinate systems before importing the data.
If you need to change your CRS, there are two ways that I generally do this:
QGIS (slower, but easy and uses a nice GUI)
Open your shapefile in QGIS.
Right click on the shapefile name in the Layers list, and select Export -> Save Features As…
Save the file with a new name, and select the CRS that you want to use for the output file. You can pick a new CRS using the little globe icon next to the CRS drop down in the Save Vector Layer as… dialog:
OGR in OSGeo4W
This is super fast and is done in OSGeo4W so you can quickly import the data into your database in one step (update CRS and import at the same time), or you can just do the transformation using the commands below and then import later.
Update CRS only:
ogr2ogr -f "ESRI Shapefile" destination_name.shp source_name.shp
-s_srs EPSG:<input crs> -t_srs EPSG:<output crs>
ogr2ogr -f "ESRI Shapefile" myShapefile_4326.shp myShapefile.shp
-s_srs EPSG:26913 -t_srs EPSG:4326
Update CRS and put into PostgreSQL at the same time:
ogr2ogr -f "PostgreSQL" PG:"dbname=yourDBName host=localhost port=5432 user=userName password=*******" source_name.shp -s_srs EPSG: <input crs> -t_srs EPSG: <output crs>
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