Page 1 of 1 (2 posts)

  • talks about »
  • formula

Tags

Last update:
Sun Nov 23 17:50:19 2014

A Django site.

QGIS Planet

Combining skills – Mapping Election Results

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.


Queries in QGIS pt 1 – Attribute Queries

Quick Select Tools

The Select Flyout button on the Main toolbar contains tools used to quickly select features:

QGIS Quick Select Tools

QGIS Quick Select Tools

Attribute Queries

One method to select features in a layer is to select features using an attribute query:-

To query a layer by its attribute data,

  1. Right click on the layer’s name in the Layer Panel
  2. Select Query from the short cut menu to open the Query Builder box
QGIS Select by attributes box

QGIS Select by attributes box

  1. It is possible to type the SQL query into the SQL where clause box. Alternatively:-
    1. Click the Field that contains the attributes you wish to base the query on
    2. Select the operator
    3. Press the All under the values box to list all the available values
    4. Double click the value that you wish to select
    5. You will see the query being built in the SQL where clauses box.

Boolean operations (And, Or, Not)

  • And: narrows the search by requiring all the criteria to be present. It is most commonly used when the criteria are in different columns. E.g. Select Business_type = “bank” And City = “London” will select banks with London addresses.
  • Or: broadens the search criteria by requiring just one of several criteria to be true. It is most commonly used to give different options to the same column e.g. select Business_type = “bank” Or Business_type = “shop” will select all the banks and shops within the data set.
  • Not: returns records that do NOT match the criteria e.g. select Business_type = “bank” AND City NOT “London” would return all the banks that do not have a London address. Note that QGIS uses != for not equal to

It is important to be clear about when to use And Or operators.

If the first example was worded Select Business_type = “bank” OR City = “London” this would return all the banks and any property with a London address

If the second example was worded select Business_type = “bank” AND Business_type = “shop” this would return only those business which had bank and shop entered as business type. This would probably be none at all as businesses tend to either be a shop or a bank but rarely both!

Mathematical Operations

It is possible to use mathematical formulae to select from columns that are formatted in a numeric format (e.g. integer):-

>Greater than e.g. Business_type = “shop” AND floor_space > 100

< Less than e.g. Business_type = “shop” AND floor_space < 100

>= greater than or equal to

<= Less than or equal to

!= Does not equal


  • Page 1 of 1 ( 2 posts )
  • formula

Back to Top

Sponsors