Mapping the 2014 Local Elections Campaign Asks

The map has been useful in reviewing the LCC’s Space for Cycling Survey results in our borough and arriving at our set of Asks for each ward. It shows:

• the individual local election wards as coloured areas with labels
• the Survey results as a set of markers, coloured according to the campaign theme into which they fall.

This enables us the see the survey responses in their locations and read the text of each with a single click.

In addition to the survey responses the map includes several additional layers showing relevant data. So far we have included:

• a layer showing available data about the schools in the borough including the pupils’ travel modes
• a layer showing the reported collision data (STATS19 1994-2012, KSI).
• a layer (called ‘Ask summaries’) that will be populated to show the results of our selection process.

When you are viewing the map, you can turn each layer on or off by clicking in the relevant tick box to the right of the layer’s name in the panel on the right. You will need to turn most of them off to make much sense of the map!

We are using the ‘Ask summaries’ layer to choose and record a set of specific Asks, one or more for each ward. Once the selected Asks have been recorded in the map, we intend to download them in the form of a table suitable for use by others.

How we made the map

That map was inspired by the excellent set of data files and hints put together by Alex Ingram of HFCyclists (the LCC Hammersmith and Fulham Group). Using Google Maps Engine Pro and Alex Ingram’s notes (some of which are quoted below as indented text), we proceeded as follows:

1. Start by creating a new map using the ‘Create Map’ button on your Google Maps > My Places page

You can get started using Google Maps Engine Lite (free) but to add all the layers suggested below you will need to subscribe to Google Maps Engine Pro (£4 per month). You only need the subscription to create and edit the map. It will stay in your list of maps and anyone with the link can view it until you choose to delete it. You can give others permission to edit it, but they must be subscribers too.

2. Add a layer showing the Ward Boundaries for all of the wards in the borough

Use the Add Layer button to import the relevant KML shape file conveniently provided by Alex:

Added 4 Feb 2014:

Go back to your Google My Places page and create a new ‘classic’ map using the ‘Or create with classic My Maps’ link. Import your borough’s ward boundaries to your ‘classic’ map using the KML file obtained from the set that Alex has conveniently provided:

“We’ve sourced our shape files from MySociety’s MapIt service.

If you’re in London, we’ve saved you the hard work and made shape files for every ward within each council in London. It took only fractionally longer than just making ours. These are on dropbox here.”

Note the URL of the ‘classic’ map you created. Now go to your Google Maps Engine Lite or Pro page (use the ‘Create Map’ button on your Google My Places page). In this second newly created map,  use the Add Layer button create a new layer and import the borough boundaries data by pasting the URL of the classic map you just created into the box that appears. (This workaround is needed because Google haven’t yet added the capability to import KML data to their ‘new’ maps).

In the newly-created layer use the Style control to show the wards in different colours and the Labels control to display the name of each.

2. Add a layer showing the LCC’s Space for Cycling Survey results

Go to the LCC’s Space for Cycling Survey Results fusion table:

• In the panel on the left tick your borough and remove all other ticks.
• Use the File>Download function to download a CSV file with the survey results for your borough.

Now create a new layer in your map for the survey results and use the import facility to load the CSV data. One you have loaded it you will need to customise its appearance. We chose to colour the icons according to the Site Ask field. Also we customised the Info Panel that pops up when you click on an icon. To do that you click on any icon, then enter editing mode (click on the pencil) and choose which fields you want to appear (the ticked ones).

3. Add a layer showing the Schools Travel Data (from a 2011 ‘hands-up’ survey)

From Alex Ingram’s data:

“List of London Schools with cycling rates from 2011, listed by borough and ward (as an excel file)

(thanks to Jack Thurston for inspiration on this)
The transport data on these maps is based on hands up surveys. We’ve only linked data where existing open school identifier matches that from the 2011 data. There may be some schools therefore with a low cycling rate where at least the building and possibly a previous incarnation of the school had a good cycling rate. We’re looking at ways of adding their data. Thanks to Robert Heyward for pointing out some examples of this.”

Proceed as follows:

• Download Alex’s LondonSchoolsSpaceForCyclingList.xls file, select the rows referring to schools in your borough and save them in as a CSV file. The table includes a lot of columns, some of which are irrelevant to our purpose, but we can leave them in at this stage and hide them after importing to the map.
• Create a new map layer and import the CSV file into it.
• Choose suitable styles for the school markers. We chose to discriminate by type of school, using icon colour and shape.
• Select a subset of available fields that you want to show in the Info window for each school. Click on any school marker, click the pencil symbol in the info window and tick those fields that you wish to be shown, unticking all the others. You can see which we chose in the image above or on our live map. After hitting Save the selection of visible fields will be applied to all of the schools.

4. Add a layer showing the STATS19 reported collision data for cyclists (1994-2012).

The procedure for adding more layers based spreadsheet data is similar, provided that each row contains a latitude and a longitude field. Thus for the 1994-2012 cyclists collision data we used the online Google Fusion table STATS19 London cycle collision data 2012 – 1994 Google Fusion Table Map (Alex has also produced a STATS19 London cycle collision data guide and the spreadsheet STATS19 London cycle collision data 2012 – 1979 (as an Excel xlsx file containing collision data for a longer period).

• The collision data table contains data for every borough in London and for all collisions causing fatal, serious and slight injuries to cyclists. So we use the Google fusion table filtering to filter first on ‘borough name’ and then on ‘severity’, omiting ‘slight’ casualties since if they are included the map would be completely covered by them.
• Download the resulting filtered set of rows as a CSV file.
• Create another layer in the map and upload the CSV data into it.
• Choose styles for the collision data. We used different coloured star-shaped icons for fatal and serious crashes. Then open then info window any of the crash icons. You will see many fields, most of them empty or inscrutable select those fields that you want included in the displayed info, then hit Save.

5. Add an ‘Asks Summaries’ layer into which we intend to enter our selected set of Asks

To create a layer from scratch with all the fields we want (as opposed to importing it from a CSV or KML file) we need to know a couple of extra tricks.

• First use the Add Layer button as before to create a new layer called ‘Ask summaries’, but this time we won’t import anything. Instead we proceed as follows.
• Add a single new marker to the layer anywhere using the Add Marker tool in the row of tools near the top of the map. You will now see a marker with its info window open and ready to edit it. There are 3 fields initially: Name, Descriptionand one with an incomplete URL. Ignore the latter. Now you probably want to add some more fields. You can do that but only in the Data View of the layer. Below the name of the layer in the list of layers you should see Data button alongside the Style button. The Data button will bring up a table view of the layer with a column for each field (this works in all the layers in your map). Alongside the name of each column in the table there is a small pulldown tool with options including insert column before and insert column after. Adding a new column adds a new data field to each marker in the layer.

6. Adding markers, inserting and editing data in existing layers

The map can be viewed and its different layers turned on and off by anyone with a link to it. The owner can also assign editing permission to anybody, but only users with a current Google Maps Engine Pro subscription can actually modify it, e.g. to add new markers or modify existing data. This is unfortunate because the editing task is liable to fall on one person or a small number who have subscriptions.

7. Getting data you have added out of the map and into a spreadsheet

Ironically, this is tricky, even though the data clearly has a tabular form. The only facility available to download the data in a layer is accessed via the icon just to the left of the Add Layer button. Use the option Export to KML and then select the layer from which you want to get a spreadsheet. (KML is an XML notation for expressing geographic annotation and visualisation). The downloaded KML contains all the data in the exported layer in a fully-labelled form. But that’s not a very convenient form for other to view and use it.

Fortunately there is an open source program called KMLCSV (http://choonchernlim.com/kmlcsv/) that does most of the work of converting a KML file to a CSV file. The only thing is, the resulting CSV merges all the non-standard KML fields (i.e. the ones we have added to the Ask Summaries layer) into the Description field, separated by <BR>. Our solution for that was to modify the resulting CSV file with a text editor (one that works for .txt files), replacing ‘<BR>’ with commas throughout. The resulting file should then open in Excel without problems.

We’re not very satisfied with this last step – it is definitely a bit ‘hacky’ in programmer parlance – but we haven’t yet found a better procedure for exporting a table or converting the exported KML, although there probably is one based on various arcane topics such as XSLT.

Ultimately, it’s incumbent on the Google Maps Engine people to make this simple requirement easier. Maps Engine does still seem to under active development and other users have expressed similar frustrations, so perhaps they will.

That completes our description. We’ll update this page from time to time. We hope to extend the mapping techniques for use in other LCC activities, e.g. for infrastructure planning, where traffic counts (using data from http://www.dft.gov.uk/traffic-counts/ or elsewhere) would be useful, but that will involve a significant chunk of work.