Choropleth Map

Creating topojson map files for Power BI Shape Maps with MapShaper.org

Shape maps or Choropleth maps are a great way to visualise spatial information where the data needs to represented in some predefined area such as a country, county, or smaller area.  Choropleth maps are especially useful for comparing differences between areas, and representing the data as a ratio of some other variable, such as the crime rate per 1000 people, or population density per square kilometre.  Power BI support Choropleth maps through several visuals including the Shape map, drill-down choropleth and Mapbox visual.  The first of these is currently in preview, and the latter two are custom visuals so don't appear in the visuals palette by default.

The default maps in Power BI - the map and filled map visuals - are easy to use, providing your data has location attributes, such as latitude and longitude and offer some customisations that may meet your needs.  As an example see this post where I look at new heat map feature in the map visual.  The filled map in particular does allow the display of choropleths but it is limited to displaying a default set of areas that it knows about.  So data aggregated at a country level is likley to display as expected, but at different geographies the results may not be as expected.  In the UK, the filled map can make a reasonable (but not perfect) attempt at mapping county level data, but at most other geographical levels, such as region, health authority, police force authority, or smaller units such as postcode, ward or cenus output area the filled map does not work.  The filled map relies on Bing for its "location intelligence" and where Bing Maps and Search cannot resolve a specific geography, the filled map fails.  For example. it is not possible to use the filled map to display data aggregated a Local Authority level in the UK and this is a requirement that I frequently see, especially in the public sector.

The solution to this problem is to use a visual that fully supports Choropleth maps and allows you to specify exactly what areas you wish to to see mapped.  However, to do this you must source appropriate base maps files, convert themt to a supported format for Power BI and prepare your data to take advantage of the custom maps that you have prepared.

I am going to go through an example using the Shape map visual which is currently in preview, so to use it, it must first be enabled in Power BI through Options and Settings - Options - Preview Features. We will need some data to map, so I'm using a dataset of street crime in Lancashire that I've already prepared. We will also need the map files to provide to the shape map visual, and that's what the rest of the post is about.

Sourcing map files

First we need to get hold of the map files. Where to get source maps files depends on which area you want to map, but in the UK we are fortunate to have an abundance of open mapping data provided by the Office of National Statistics and accessed through the Open Geography Portal

I've downloaded the following map files:

Police Force Areas

Local Authority Districts

Lower Layer Super Output Areas

I need the Police force areas map so that I can extract the Lancashire polygon.  Although I won't be using this for visualisation, it will be useful to use as a selection mask for the the Local Authoritiesa and Lower Layer Super Output Areas (LSOA) map. The Local Authorities Districts maps will allow data aggregated at the Local Authority level to be displayed.  The LSOA map will allow data aggregated at the LSOA level to be mapped.  I chose these because each crime observation in the data set contains location data on where the crime was committed.  One of the columns is LSOA.  There are also columns for latitude and longitude which is helpful should I wish to aggregrate by some other level of geography, but in this case LSOA is perfect.

It's worth noting that there is a choice of files to download. The area mapped is the same, but the file sizes are different, depending on whether the polygons have been generalised - which essentially means reducing the number of points describing the polygons in the map.  This saves space, but at higher magnfications the resolution of the generalised and ultra-generalised maps is too coarse.  The generalised files are fine, but in this case I've downloaded the full extent (full resolution) files, because I need to do some processing on the files to get the final maps I need, and I have had issues with this using the generalised files. It is possible to generalise or simplify the maps yourself anyway to reduce the file sizse as you will see.

Once downloaded, the files shoud be unzipped.  The files are ESRI shp format which is a widely accepted standard for map files, but not compatible with the Shape map visual in Power BI.  We will need first extract the polygons - for Lancashire - from the the files and convert them to topojson format.

There are various freely avialable tools to manipulate map files such as QGIS, ogr2ogr R and others, however all these require installation.  Mapshaper.org is a free online resource that can we can use for map preparation.  Here is a step by step guide.

Open mapshaper.org in a brower.

Select, or drag and drop the unzipped police force area files to the mapshaper window.  There are 6 files in the extract but only the shp, shx, prj and dbf files are required.  If you select all six mapshaper just loads the four it needs.

Click Import and you should see a map of English and Welsh police force areas.  We are only interested in Lancashire so click the Console link and in the console type the following:

filter 'pfa16nm == "Lancashire"'

This removes all polygons except Lancashire from the map. Click the filename in the mapshaper header bar and click on the name to select it.  Change the name to LancashirePFA.

We will use this as a template to filter just the Lancashire local authorities from the local authorities map.

Click "Add a file" and import the Local Authorities files following the same procedure as for the police force areas map.

In the console window type clip LancashirePFA.  This will remove all the Local Authorities from the map that are not masked by the LancashirePFA map or layer.

Click the filename in the mapshaper header to open the layers window.  Rename the layer to LancashireLocalAuthorities.  You can also delete the LancashirePFA layer since we have finished with it.

The map must be in the wgs84 projection so in the console type

proj wgs84

(not WGS84 as it is case sensitive)

Click Export, select topojson as the format, and click export.  Save the file with the name appropraitely, e.g. LocalAuthoritiesInLancashirePoliceForceArea.json

You now have a topojson file that can be used with the shape map visual in Power BI to map data aggregated at the local authority level.

To map data at the LSOA level, you should repeat the steps above with the LSOA map file that you downloaded.  Note that you can use the Local Authorities map as the clipping layer in mapshaper to filter only those LSOA that fall within those local authorities (and by default within the Lancashire police force area).

The rest of this post just considers the Local Authority map.

In Power BI add a Shape map to a page.  In the location field add Local Authority. In the value field add Total crime. Click the paint roller, then click shape. CLick Add map.  Add the json local authority map file that you created in Mapshaper.  You should see your map filled according to the data values for each Local Authority.  You can now modify the data colours to suit and add additional visuals to build out the report page.  The map fully support cross filtering so you can click on Local Authorities in the map to filter other visuals or highlight local authorities in the map through filtering other visuals.

The following report was built using the Local Authority map file created above.

The Power BI file for this report can be downloaded from here.

 

Posted in GIS, Maps, Power BI.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.