Step By Step Instructions – ShapeFile to Custom Polygons using Open Source GIS (QGIS)

Version 8

    Simon Runc Edit (04/04/2018)

    Since first writing this post, mapping has got much easier/better in Tableau; with Shape Files loading directly into Tableau (so no need to manually create Polygon files) and many more geographies, natively, supported. It's set to become even easier with Spatial Joins, and Spatial/Geometry Data Type (in Database) support coming soon. I also wanted to point everyone towards this amazing resource from Craig Bloodworth ...making things even easier!! (big thank you to Craig)

     

    https://www.tableaumapping.bi/map

     

     

    Simon Runc Edit (15/05/2015)

    Since posting this article I have learned a lot more about custom shapes in Tableau and a bit more (I stress the ‘bit’ here!) about QGIS. Mainly driven by the questions/issues posted below, when after following the original article your maps end up looking like….

     

    No Clean Up Image.JPG

    As such I have now created a further attachment (.doc), with the help of the below posts from Jon Foote and Shine Pulikathara , compiling their methods for cleaning up shape files (many thanks for your input). Using QGIS, manually amending the Polygon Data File, or via Tableau (the attached .twbx [Tableau 9.0] corresponds to the Tableau technique in the .doc file).

     

    Simon Runc Edit (15/11/2015)

    One of the major reasons the above occurs are where their are islands. The problem occurs as the Path Values we create, if a 'geography' encompasses several islands, so our path goes (1st Island)1,2,3...100 (second Island) 101,102... Tableau will plot 100 ->101 across the sea! To get around this we need to create 'Sub-Polygons' these are plotting levels so that the Path Values re-starts for each island. alexander.turner.0 has written some VBA script (and kindly shared) to do exactly this...and can be found here

     

    Re: Custom GIS Polygon Optimization

     

    In addition to these techniques, I have also found some other resources where (free) TDE polygon files are available (which is by far-and-away the easiest way!! assuming you can fin them for your required geography)....

     

    Miso Maps (for the UK have got beautify, clean, polygon TDEs, with all UK levels from Zones to Local Output Area)

    http://tableau.misoportal.com/tableau-maps/

     

    Tableau Mapping BI (lots of TDEs of Maps around the world)

    Tableau Mapping « Democratising The World's Mapping Data

     

    For UK Geographies...don't mess about with QGIS/Shapefile!!, just use this amazing piece of work from Craig Bloodworth Just (unzip and) copy the files onto your 'My Tableau Repository', and Tableau's 'built-in' geographic splits become all the UK levels you could ever want (all as point and area)

    UK Filled Map Geocoding Pack for Tableau - The Information Lab

     

    UK+Geogs.png

     

    However if you can't find what you are looking for (free and pre-built) and can only get Shapefiles of your required geography the below original post (along with the techniques in the .doc) should you what you want.

     

     

    Original Document

     

    I will point out that I’m no GIS expert, in fact I only use QGIS for this purpose. As such I've provided below a ‘do this, do this, do this…’ explanation that shouldn't matter if you understand what the GIS is doing or not!

     

    Intro

    Although Tableau has some in-build Geographic data for the UK [such as Post Codes, Cities, Counties…etc.] it doesn't contain administrative boundaries. There are also some countries which don’t have state boundaries defined.

    However Tableau allows us to create our own custom boundaries, known as Polygons. Polygons are created from a file that contains the points [in Longitude and Latitude] that make up the ‘point-extent’ of each boundary, and the order in which to draw them

    Once the file is created it is fairly straight forward to create custom polygons in Tableau

    …but first the complicated part! How do we create the file?

     

    Get the ShapeFile

    In my example I’m going to create Ward (Administrative) boundaries for a Council in the UK.

    Office of National Statistics [ONS] provides many ‘geographic’ layer files (free of charge) for use in many disciplines. Here is the Link to ONS download section of boundary ShapeFiles: https://geoportal.statistics.gov.uk/geoportal/catalog/content/filelist.page

    We need the point polygon co-ordinates from the Shape File, which means we need to process this file through a GIS application. I will use QGIS (which is Open Source…aka free!). Here is the download page https://www.qgis.org/en/site/forusers/download.html

    For this example, I've download the file called ‘Wards_(GB)_2013_Boundaries_(Full_Extent).zip’ from the ONS Geoportal [this is in the Boundaries section], and extract the files

     

    Open ShapeFile in QGIS

    Open QGIS, Click on ‘Add Vector Layer’ and browse to SHP file [it will be the biggest one], and Open. This will bring up all the Ward Boundaries for the UK

    First task: If the Co-ordinates for this file aren't in Longitude and Latitude, we need to create them. As I don’t need the whole UK, I can select the Wards I need using the selector [hold CTRL to select multiple Wards].

    Tip: Use the Label to add Ward names to map [Ward name field is called ‘WD13NM’], unless you are geeky enough to know the ONS ward codes!

     

    Convert Projection to Longitude/Latitude

    Right-click on the layer name in layer list and select "Save as ..." and the target CRS to be WGS 84 [EPSG:4326]. This is the Projection for Longitude and Latitude Coordinates, and set up the ‘Save As...’ as per the below screen shot.

    Key Settings are;

    File Type ‘ERSI Shapefile’, Selected CRS is WGS 84 [EPSG:4326]

    I've renamed the files with LL append, but obviously this is up to you

    I've also clicked ‘Save only selected features’ to keep the file size down [unless you need entire of UK]

     

    Image 1.jpg

     

    We can then add this new layer to map [using the ‘Add Vector Layer’ as before]

     

     

    Extract Nodes from Shape File

     

    Select the new file in the Layers pane

    Go to Vector > Geometry Tools > Extract nodes

    Save the Shape file to a new name. I've called it ‘Wards Polygons ShapeFile.shp’, selecting ‘Add Results to Canvas’

    Ensure the New ‘WD_DEC_2013_GB_BFE_LL’ file is selected. As per screen shot below

    Once it’s done, close the dialogue box

     

    Image 2.jpg

    The new Geometry file will now appear in the Layers pane

     

    Add Geometry Columns

    Select the new file in the Layers pane

    Go to Vector > Geometry Tools > Export/Add geometry columns (see screen shot below)

    Save the Shape file to a new name. I've called it ‘Wards Polygons ShapeFile_Geometry.shp’ selecting ‘Add Results to Canvas’

    Ensure the New ‘Wards Polygon ShapeFile’ file is selected

    Once it’s done, close the dialogue box

    Image 3.jpg

    Image 3.1.jpg

     

    The new Geometry with Columns file will now appear in the Layers pane

     

    Create CSV of nodes

    Right Click the new ‘Geometry with Columns’ layer [from Layers pane], and select ‘Save As…’, with the following settings

    Format = Comma Separated Value

    Choose Name for CSV

    CRS = WGS 84

    Layer options should default to the correct settings, but check against screen shot that you have Geometry AS_XY

    Screen Shots below

    Image 4.jpg

     

    Image 5.jpg

     

    This will give a CSV with Longitude/Latitude columns for the extent of each ward

    …Nearly There

    Getting the file into a Tableau Format

    Tableau needs a plot order for each polygon. This can be done in any ETL, SQL…I've given how to do it in Excel (as this seems the most used!)

    Open the file in Excel and add a 'Sequence' Column. The Polygon Nodes are in the correct order so it’s just a question of a formula. In the first sequence cell I've added the formula =IF(E2<>E1,0,I1+1). As per the screen shot below, and copy this down for Column I

     

    Image 6.jpg

     

    Rename X and Y fields to ‘Longitude’ and ‘Latitude’, and rename ‘WD13NM’ as ‘Ward’

     

    You will then end up with a data file looking like this. btw you don't need to create a numeric ID, and can just use Ward Name (I'm so used to using numeric Keys, and is more efficient when joining data...etc. [Star Schema, Fact Tables...etc.] on larger scales that I can't help myself!)

     

    Image 7.JPG

     

    Plotting Custom Polygons in Tableau

    Load in New File into Tableau

    Convert ‘Sequence’ to Dimension

    Add Longitude to Columns, Latitude to Rows

    Ward Name and Sequence into detail Pane, and change Sequence to ‘Path’ [use drop down to left of Blue Pill]

    I've then dragged Ward into the Colour Marks, for greater visibility

    We can then ‘Blend’ on the Ward name, to other Data, to use that data within our new custom areas [NB. The Polygon file needs to be the Primary Data source]

     

    Image 8.jpg

     

    And Voila Custom Polygons from ShapeFile in Tableau.

     

    I hope people find this useful