Tableau: Using UK Shapefiles in PostGIS Database

Question

I'm trying to use Tableau to display data. Some of this data is suitable for mapping, and therefore I'd like to create maps in Tableau to display this data.

Unfortunately, neither the basic view nor the excellent work done for UK Mapping in tableau that is available freely available quite meets my needs. The right combination of Districts, Counties, Unitaries and metropolitans doesnt - as far as I can tell - exist in tableau format. However I believe I've got all the data and bits I need to DIY... but am struggling putting it all together.

I have Tableau Professional 9.3; Postgres 9.4 (PostGIS 2.1) and QGIS 2.12.2.

I have all of my data held in a PostGres database that I've connected to Tableau (and previously to QGIS).

I have all of my shapefiles downloaded from the ONS and imported into a second PostGres database. I'm particularly interested in this one: (https://geoportal.statistics.gov.uk/Docs/Boundaries/Local_authority_district_(GB)_2014_Boundaries_(Generalised_Clipped).zip). These are EPSG:27700 (1936/British National Grid)

I can query either database from tableau or QGIS. I have tried the method here (https://community.tableau.com/docs/DOC-5831) but end up with a blank attribute table in QGIS after performing the first step to convert to WPGS 84 (i.e. it loses all data when it converts). This therefore doesn't work for me; and I'm wary of the method as it strikes me there should be a more elegant solution at the database level.

Tableau wants:

• a path;
• a poloygon id;
• a name;
• a latitude;
• a longitude;

Therefore, in order to get this information in a suitable form, in the PostGIS database I believe I need to:

1. Extract the nodes from the 'geom' type in the table created by the imported shapefile (do I use (ST_DumpPoints(GEOM)).Geom)?)
2. Extract the path from the 'geom' type in the table created by the imported shapefile (do I use (ST_DumpPoints(GEOM)).Path)?)
3. Extract the Easting and Northing data from the Nodes
4. Convert the Easting and Northing data from EPSG:27700 to Lat\Long (i.e. WPS:84) (I know I need to use something like the following set long=st_x(st_transform(geom,4326)), lat=st_y(st_transform(geom,4326))
5. Put these into a new table\view and link to this in Tableau

Here's where I've got to:

--Drop Table IF Exists ctyua_dec_2014_ew_bgc_tblu;

With CTE_PointsAndNodes as 
(
SELECT gid
    , ctyua14cd
    , ctyua14nm
    , ctyua14nmw
--  , geom
    , (st_dumppoints(geom)).geom as points
    , (st_dumppoints(geom)).path as path
  FROM (Select * from ctyua_dec_2014_ew_bgc Where ctyua14nmw is Null) as tbltest
  )

Select gid
    , ctyua14cd
    , ctyua14nm
    , ctyua14nmw
--  , geom
    , points
    , path
    , st_x(st_transform(points,4326))
    , st_y(st_transform(points,4326))

INTO ctyua_dec_2014_ew_bgc_tblu

From CTE_PointsAndNodes

I do this; link to it in Tableau... but I get a blank sheet!

So - am I right... and what am I doing wrong...

Answer 1

Figured this out. This process should work for any shapefile held in a Postgis database, and will put it in a format that can then be plotted as a polygon in Tableau directly from the database; no mucking about importing or exporting stuff. It will create a new table containing the data in the shapefile in a format thats usable by Tableau. The more adventurous amongst you can use this as the basis for a stored procedure\table loop to do multiple tables.

The steps are as follows:

1. Import your shapefile into your Postgis database (obvious, I know, but helps to start somewhere!)

2. Make sure you have explicitly stated the correct SRID. In reference to my OP, the files provided by the ONS geoportal do not have this explicitly stated (for these it is 27700), and therefore I cannot assume that any others do.

   Select UpdateGeometrySRID(<tablename>, 'geom', <correct_SRID>); --Ensures that you have the correct SRID in the chosen shapefile table. See http://postgis.org/docs/UpdateGeometrySRID.html for guidance. 
   

3. This process creates a specific table for the tableau-formatted data (you could use it to create a materialised view but at present Tableau does not like these very much). We need to drop this if it's been created before.

   Drop Table IF Exists <tablename>_tblu; --Removes Tableau table if previously generated. See http://www.postgresql.org/docs/current/static/sql-droptable.html for examples.
   

4. Use the following code to extract the data and build the table

   --Initial temporary table to unpack geometary data
   With CTE_PointsAndNodes as 
   (
   SELECT gid                                              --Primary key; ID created by shapefile import
   , <Start of Shapefile Descriptors>                      --Codes and ID's included with shapefile
   ...                                                     --to help distinguish different geographies
   , <End of Shapefile Descriptors>                        --they differ per shapefile; add yours here
   --  , geom
   , (st_dumppoints(geom)).geom as points                  --Unpack points geometry using PostGIS st_dumppoints. See http://postgis.net/docs/ST_DumpPoints.html
   , (st_dumppoints(geom)).path as path                    --Unpack path geometry using PostGIS st_dumppoints. See http://postgis.net/docs/ST_DumpPoints.html
   FROM (Select * from <tablename>) as tblShapefile
     )
   
   -- Now to unpack the path array into integer hierarchies and place into new table. 
   -- Array_to_string function translates the path array into string for manipulation; Split_part function extracts the individual parts of the array. 
   -- See http://www.postgresql.org/docs/current/static/functions-string.html for examples of both.
   -- PostGIS function st_transform converts SRID into WGS 84 required for Tableau. See http://postgis.org/docs/ST_Transform.html for guidance
   -- PostGIS functions st_x and st_y unpack the WGS 84 points into the lat and long for plotting
   
    Select gid                                             --Tableau Needs this for drawing. Final ID in sequence.
   , <Start of Shapefile Descriptors>                      --Take these from the Original CTE for linking to other datasets/colouring
   ...
   , <End of Shapefile Descriptors>
   --  , geom
   --  , points
   --  , Array_to_string(path,',') as Path_Str
   , Split_Part(Array_to_string(path,','),',',1)::integer as tblu_id_1 --Tableau Needs this for drawing. First ID in sequence.
   , Split_Part(Array_to_string(path,','),',',2)::integer as tblu_id_2 --Tableau Needs this for drawing. Second ID in sequence.
   , Split_Part(Array_to_string(path,','),',',3)::integer as tblu_path --Used as the 'Path' dimension in Tableau
   , st_x(st_transform(points,4326)) as x_axis             --Used for plotting on X-Axis
   , st_y(st_transform(points,4326)) as y_axis             --Used for plotting on Y-axis
   INTO <tablename>_tblu                                   --Creates new table with the output, named as per original with '_tblu' appended to distinguish. See http://www.postgresql.org/docs/current/static/sql-selectinto.html for examples.
   From CTE_PointsAndNodes
   

5. That finishes the work in Postgres\postGIS; now open Tableau and create a new data source to your PostGIS database. Once this is done open up your '_tblu' table and bring in the newly accessible shapefile data.

6. Now to get the data in the table into a coherent format before plotting it

   • Right-click the [GID] field and select 'Convert to Dimension'
   • Right-click the [tblu_id_1] field and select 'Convert to Dimension'
   • Right-click the [tblu_id_2] field and select 'Convert to Dimension'
   • Right-click the [tblu_path] field and select 'Convert to Dimension'
   • Right-click the [x_axis] field
     • Navigate to Default Properties
     • Navigate to Aggregation
     • Select 'Average'
   • Right-click the [y_axis] field
     • Navigate to Default Properties
     • Navigate to Aggregation
     • Select 'Average'
   • Right-click on the green '#' to the left of the [x_axis] field
     • Navigate to Geographic Role
     • Select 'Longitude'
   • Right-click on the green '#' to the left of the [y_axis] field
     • Navigate to Geographic Role
     • Select 'Latitude'

7. Now to plot this in Tableau.

   • On a blank worksheet, in the 'Marks' card, select 'Polygon'. The [path] option should appear
   • Drag the [tblu_path] field to the [path] option. This should appear at the bottom of the marks card with a line'n'points icon next to it
   • Drag the [tblu_id_2] field to the 'Marks' card so that it sits just below the [tblu_path] pill.
   • Drag the [tblu_id_1] field to the 'Marks' card so that it sits just below the [tblu_id_2] pill.
   • Drag the [GID] field to the 'Marks' card so that it sits just below the [tblu_id_1] pill.
   • Drag the [x_axis] field to the 'Columns' shelf
   • Drag the [y_axis] field to the 'Rows' shelf
   • Add an ID field to the 'Colour' part of the Marks card to verify all geographies have been added properly.

8. This should get you a usable - if slightly odd looking (due to the reprojection) - map. However the first time I did it, I found it extremely squashed. I needed to make sure the axis points were set up properly.

   • Right-click on the X Axis of the plot
     • Select 'Edit Axis'
     • Ensure [Range] is set to 'Automatic'
     • Ensure [Include 0] is unchecked
   • Right-click on the Y Axis of the plot
     • Select 'Edit Axis'
     • Ensure [Range] is set to 'Automatic'
     • Ensure [Include 0] is unchecked

This then gets use a usable shapefile into polygons for use in Tableau. Because you have set the Lat and Long up earlier, Tableau should then know enough to add in the standard background map and to make it a map view.

Hope it helps you. Enjoy!