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.
- 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:
- Extract the nodes from the 'geom' type in the table created by the imported shapefile (do I use
- Extract the path from the 'geom' type in the table created by the imported shapefile (do I use
- Extract the Easting and Northing data from the Nodes
- 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))
- 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...