VISUALISE ALL DUTCH CITIES AND NEIGHBOURHOODS IN TABLEAU
For visualization of data nothing is more powerful then a map. Tableau is a fantastic tool in creating the most stunning visualizations and it also enables you to show data in a map. By combining different datasets you can for instance relate profit margins to geographic regions to help you understand your business better (why are our campaigns outperforming in Friesland? Why is our market share higher in Breda?). These insights could lead to changes in your marketing initiatives to improve market share or profitability in the other regions.
Unfortunately, one of the things lacking in Tableau Software is the geographic details of states, provinces, cities, districts or neighbourhoods outside the US. Luckily more and more countries are being offered via the Tableau Mapping initiative. The Netherlands was missing though, until now.
<div class="tableauPlaceholder" style="width: 600px; height: 810px;"><noscript><a href='#'><img alt=' ' src='https://public.tableausoftware.com/static/images/Du/DutchCitiesinTableau/Neighbourhoods/1_rss.png' style='border: none' /></a></noscript><object class="tableauViz" style="display: none;" width="600" height="800"><param name="host_url" value="https%3A%2F%2Fpublic.tableausoftware.com%2F" /><param name="site_root" value="" /><param name="name" value="DutchCitiesinTableau/Neighbourhoods" /><param name="tabs" value="yes" /><param name="toolbar" value="yes" /><param name="static_image" value="https://public.tableausoftware.com/static/images/Du/DutchCitiesinTableau/Neighbourhoods/1.png" /><param name="animate_transition" value="yes" /><param name="display_static_image" value="yes" /><param name="display_spinner" value="yes" /><param name="display_overlay" value="yes" /><param name="display_count" value="yes" /></object>
<div style="width: 600px; height: 22px; padding: 0px 10px 0px 0px; color: black; font: normal 8pt verdana,helvetica,arial,sans-serif;">
<div style="float: right; padding-right: 8px;"><a href="http://www.tableausoftware.com/public/about-tableau-products?ref=https://public.tableausoftware.com/views/DutchCitiesinTableau/Neighbourhoods" target="_blank">Learn About Tableau</a></div>
You can find the links to download the separate files below.
The source of the geographic borders (the polygons) are offered via the “Centraal Bureau voor de Statistiek” (CBS or translated into English something like the “National Office for Statistics”). You can find the files here: Dutch geographic borders for Municipalities, Districs and Neighbourhoods. They provide the polygons for Municipalities (technically, we are talking about Municipalities and not Cities), Districts and Neighbourhoods (or in plain old Dutch: “Gemeentes, Wijken & Buurten”).
There are some unfortunate matters here though.
First of all they are in Shapefile format (SHP). A SHP file is a popular geospatial vector data format for geographic information system software but also a file format not being used by Tableau.
Secondly, they are outdated. In 2013 and 2014 the Netherlands merged several Municipalities into bigger ones (the ROI achieved with these mergers is definitely outside the scope this post). These pages give you the details of these changes Municipalities as per 1st of Januari 2014 and as it was as per 1st of January 2013.
Third, they also include the polygons for the larger Dutch waterways (Zeeuwse Wateren, IJsselmeer & Waddenzee). Although they are important from a Dutch perspective and I do love to sail there, I would like to exclude them from Tableau as they are not relevant for most visualizations. As a Bonus I have included them as a separate Tableau export in the Alteryx file. This should help you analyzing your sailing trips in Dutch waters using Tableau. A mental note has been taken to assign the proper names to the different waterways, please come back to this Blog the coming years to follow any progress in that.
Fourth, the SHP files do not include the Dutch Provinces or States (“Provincies”), which is just sometimes more then nice to have. As the Provinces are included in the XLS overview of all municipalities, I have included it and exported it as a separate Tableau file.
And fitfth, the CBS polygons are defined for every centimeter of the border. Without proper actions you would end up with polygons to large to be used easily. A refresh of the visual map would take minutes and not the milliseconds you like to work with.
I thought it would be a good idea to tackle this matter for once and for all.
NOTHING GOODS HAPPENS WITHOUT ALTERYX
With Alteryx you have an extremely powerful tool to tackle easily all the issues mentioned above.
Shapefiles are just one of the many formats Alteryx can read out of the box. The renaming of municipalities is done via a simple Excel spreadsheet (old name and new name) and used as input in Alteryx. With some simple but powerful joins, blends and Excel-like formula’s, I have updated all municipalities with the 2014 names and merged their polygons. As every District and Neighbourhood has an unique ID which for the first part consists of the Municipality ID, I had also to recode all IDs for Districts and Neighbourhoods. Just another pleasure to do with Alteryx. As last part of the transformation I generalised the polygons to optimise the filesize and usability. With Alteryx you can easily smooth out the polygons. This increases the speed of using geospatial visualisations significantly as the size of the datafiles decreases up to 85% (the neighbourhoods TDE decreased from 39 Mb to 6 Mb for instance) and Tableau can now visualise the data instantly.
Below is the screenshot for the Workflow for the Dutch districts. If you would like to receive a copy of those workflows, please send me a mail.
Below the images of Provinces, Municipalities, Districts and Neighbourhoods in Tableau (and sorry for the bad joke of putting the maps in the Dutch national colours). The intensity of the colours have been applied alphabetical and not by something like amount of inhabitants. That would be just too predictive
You can download the TDE files here:
The TDE files also include some basic statistics of the region. These are: amount of inhabitants, amount of households, total amount of cars and area in square km’s area for the total but also for the total amount of land and water. My best guess is that these are 2013 numbers but I am not sure about it. Full datasets for socio-demographic data can be found on the site of the CBS, these datasets are fully customisable at CBS Open Data Statline.
To translate Shapefile polygon-objects into a set containing polygonIDs, subpolygon IDs and Point IDs – which is needed by Tableau – an Alteryx macro has been used provided by the Alteryx crew. You can find this macro included in the Alteryx Visual analytics Kit for Tableau.
There is a small twist in using polygons in Tableau. You have to add the polygon ID and subpolygonID into the details. Download the Tableau Public dataset and have a good look into it as I can imagine that sometime in the near future you will crack your head on this, at least I did the first time.