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:
- grab the Prescription Rate data from Open Glasgow.
- download OpenRefine (I use this for manipulating the data you could use your own favourite package here).
- Download and install QGIS.
- Download the OS VectorMap™ District data (Glasgow is square NS).
- Download the OS VectorMap™ styles.
- Download the Scottish Datazone boundary shape files, these are taken from the Scottish Neighbourhood Statistics website.
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.
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” 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:
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:
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:
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.