in dataviz

Open Glasgow – Mapping Health in the City


prescription rate – drugs for depression, anxiety & psychosis – by datazone


scottish index of multiple deprivation by datazone

On 25th April I joined team Dream Machine for Open Glasgow’s health themed hackathon. I spent most of the weekend mapping health indicators from the open Glasgow data-sets. This was all done with free software as well as open data from Ordnance Survey. I’ve set out a quick how-to below.

What you’ll need:

Putting together the right data

The first step is to create a data table that contains all the relevant variables linked together by their common variable, datazone. If we look at the prescriptions data in openRefine it look like this.

Screenshot from 2014-05-15 15:18:42

We have an identifier code for the datazones e.g. S01003025, a label for that datazone and then two numerical variables. As we want to compare datazones against each other we’re interested in prescr_rt, which give the prescription rate as a percentage of the population. We’ll remove the HS-prescr column to avoid confusion. So we have the geographic units we want to display (the datazones) and their attributes (the prescription rate). What we need now is to give the geographical units some Geographic Information System (GIS) data so that the mapping software knows what they look like and where to put them. If we open the datazone boundary data in QGIS (open the .shp file) then we get the outlines of all the Scottish datazones:

Screenshot from 2014-05-15 15:38:40

We only want the datazones that match our data for Glasgow. If we open the attribute table in QGIS we can see what variables make up this boundary data.we can select the whole table and then copy and paste this into a spreadsheet (I used LibreOffice’s Calc) it’s tab separated. From here we can save the data as a csv ready to input into OpenRefine so it can be joined to our prescriptions data. Once open in OpenRefine we have columns for the various attributes. The two we are interested in are wkt_geom which contains the polygon details of the datazone boundaries and DZ_CODE which has the datazone identifier code in the same format as the prescriptions data. In OpenRefine we can join these two datasets by adding the wkt_geom column to our prescription rate data. reopen the prescription data and rename the GeoCode column to match the DZ_CODE label in the boundary data. Then ‘add a column based on this column…’ and use the following GREL code “cell.cross(“project to merge”, “common variable”).cells[“attributes to merge”].value[0]” where “project to merge” = “datazones csv” (the boundary csv data we created earlier), “common variable” = “DZ_CODE”, and “attributes to merge” = “wkt_geom”. This will pull through the data for our Glasgow datazones. You should have something that looks like this:

Screenshot from 2014-05-15 16:09:39

Creating the map

We can now export this data from OpenRefine as a csv file. Open this file in QGIS by adding a text delimited layer. You should now have a QGIS layer that looks like this:

Screenshot from 2014-05-15 16:22:01

This gives us the boundaries for just the Glasgow datazones. We can now colour the different datazones relative to the prescription rate variable. In QGIS right click on the layer and select properties. select “categorized” from the drop-down menu and prescr_rt from the column menu. Choose the colour ramp you want (I chose reds) then hit classify to create the colour grading for each value. Then hit OK to accept these changes, your datazones should now be coloured by how high the prescription rate is.Now to give the ma more context we want to overlay our datazones on top of the OS map. unzip the OS Vectormap files into a directory and then in QGIS add a vector layer and select all the files containing the OS data (.shp files). Add this layer. Even if you drag your datazone layer to the top of the layer list your map now looks horrid. What we need to do now is apply styles to the OS data and put them in the right order.

The correct order is set out in Suggested-Layer-Order.tif a file that comes with the OS styles. drag and drop the layers into this order. Then one by one right click on the OS layers go to properties and ‘load styles’ finding the corresponding style .qml file in your styles directory (I chose to use the backdrop rather than full_color styles). Once you have all of the styles applied your map should look like this:

Screenshot from 2014-05-15 17:41:31

That’s pretty much it. Have a play with the properties for the datazone layer to achieve nicer looking results, for example try setting the layer transparency so that some of the OS map detail shows through.

This project contains Ordnance Survey data © Crown Copyright and database right 2014.
Both the OS and prescriptions data are released under the Open Government Licence v1.0.