I would like to show you how to use QGIS to combine different skills to

  • Import data from Excel or other spreadsheets
  • Analyse the data
  • Present the results as a thematic map
  • Use a feature subset to hide superfluous data

There is a year to go to the next general election. Under the British electoral system, the country is divided into 660 constituencies. The MP for each constituency is elected using the First Past the Post system, where the candidate with the most votes is chosen as MP for that constituency. The party that has the most MPs elected wins the election, and the right to form the nest government.

Parties concentrate their resources on the constituencies that they are most likely to win or lose. These are usually the ones where majority in the previous election was closest.

This project will use QGIS to join fields between OS Boundary line data and the 2010 election results to identify and map these constituencies.

I downloaded the following data sets:-

OS Boundaryline:  https://www.ordnancesurvey.co.uk/opendatadownload/products.html

Election results: http://www.electoralcommission.org.uk/__data/assets/excel_doc/0020/105725/GE2010-constituency-results-website.xls

Step 1 – Examine and prepare the data

I need both data sets to use exactly the same name and formatting in order to In order to add the 2010 election results to the OS Boundary line polygons.

  1. Start QGIS and set the map projection to OSGB.
  2. Use the Add Vector Layer button to add the file westminster_const_region.shp from the OS Boundary line data
  3. Open the attribute table to check the data structure and contents:-
Image

Constituency attribute table

Now to check the GE2010-constituency-results-website.xls

 

Image

Election results Excel screenshot

Unfortunately the two datasets don’t use exactly the same constituency names! It is fairly easy, but time consuming to match the record from the Election Results dataset to the OS Boundary Line record using Excel or Libre Office.

To export the OS Boundary line polygon names to Excel:-

  1. Right click on the westminster_const_region.shp in the Layers Panel
  2. Select Save As

I prefer to use the .dbf format when exchanging data between GIS and Excel as it is quicker to import than using .csv format.

 

Image

Both name fields are needed. The first, constituency _name will be used to link to the constituency polygons once the table is imported into QGIS. The second, Results_Table_Name is used by the VLOOKUP query that adds the five columns from the results data.

Image

Excel screenshot showing the cleansed election results matched to constituency names

Save the data as a .csv file when this stage is complete.