Code The City is a civic hacking initiative focused on using tech and (open) data for civic good. We use hack weekends, open data, workshops, and idea generation tools. We run Data Meetups, the Aberdeen Python User Group and the annual Scottish Open Data Unconference
At Code the City we believe that the right people, with the right skills and tools, can do great things. We believe that we can use technology and data to solve many civic challenges. Those beliefs are as applicable now as was when we started seven years ago. And our volunteers who come to our events time and again agree. They know that sharing their skills and knowledge with others in small teams, over a weekend, working on a focussed and achievable project, is a satisfying experience which leaves them with a sense of achievement. It also introduces them to working in teams and in an agile way: short sprints of work and pauses for review.
“The power of one, if fearless and focussed, is formidable, but the power of many working together is better” –
Gloria Macapagal Arroyo
In the last seven years we’ve tackled many topics – and worked with multiple partner organisations in the public and private sector to solve their challenges – and to identify opportunities to use data and technology to improve how they deliver their services.
Throughout that period we’ve had some central principles that we’ve adopted which still hold true:
Data, where appropriate, should be open and licensed for reuse
Software should be developed as open source – where the code can be inspected, and improved on by anyone, and reusable openly by others
Information, images and other content should be as openly licensed as possible to encourage re-use and creativity
Where appropriate stable platforms exist (such as WIkidata, Open Streetmap, Github,or Wiki Commons) we should use those
People working in small teams and in short sprints of activity can achieve an enormous amount over a weekend
Last week at Open UK’s COP26 event “Open Technology for Sustainability”, which our co-founder and trustee Ian Watt attended, those same principles that inspired our creation, and inform our continuing work, were echoed time and again by speakers. And at the evening awards dinner we were runners-up to the the wonderful Open Knowledge Foundation, in the Data category. This further validates our belief in our approach.
CTC’s Runner-up trophy for data in Open UK’s 2021 awards
More recently we’ve been concentrating even harder on improving open data in Scotland and the UK – but not to the exclusion of other projects. In addition to several history and heritage projects which have seen large amounts of open data created and published, we’ve had projects such as Open Wastemap which was built almost entirely over two CTC weekend and uses community-sourced data in Wikidata and OpenStreetmap to power this really useful tool to find local recycling facilities.
Our next event CTC24 – Open In Practice is taking place in just over a week. It is the perfect introduction to what we do and to becoming involved. We already have a list of potential projects that attendees, new and experienced, can get involved in. Some of these are local in scale and some national. All need a blend of skills from attendees. You don’t need to be either a coder or data expert to participate. You can sign up directly here or from the event link above.
No excuses: be part of the group that does the good things – or stand by and watch while we do!
Mesolithic Deeside is a group of archaeologists, students and local volunteers investigating the river Dee area 10,000 years ago. They’ve been gathering flints on seasonal field-walking trips and recording the data from the outputs of those allowing them to map Mesolithic Deeside.
Close up of hand holding a lithic
The following is a summary of what the the group with some additional helpers achieved over the two days of CTC20.
Day 1
Team: Andy, Ali, Sheila and Irvine
Notes:
Discussed the goals of the project with the Mesolithic Deeside Team
Displaying data visually for public consumption
Updating / refreshing the website
Looking at ways to identify future sites for test pitting
Decided to focus on developing a way of visualising the data that has been collected
Data is currently stored in a QGIS project and a number of csv files
Initial work looked into the possibility of using QGIS and Tableau for visualisation
Tableau was later dropped in favour of QGIS
Issues with Andy loading QGIS data from the project – no reason why it shouldn’t work
Decided that Irvine would focus on working with QGIS and Andy would focus on finding a solution with Google Maps
Andy has selected a subset of the data and is currently working to put that data on a Google Map
Data needs to be cleaned and tidied up before being displayed, i.e typos, consistent name formatting
Currently working with Google Sites & Awesome Table
Awesome table works with Google Sheets and picks up certain types from a header row – can be tricky to get working
Unable to disable clustering when zoomed in
Example of AwesomeTable as a Map with clustering
Example of data being filtered by flint type
Example of colour coding for the different finds
Day 2
Team 1: Andy, Robert & Irvine
Team 2: Ali, Sheila & Dave
Objectives:
Collate the finds data into a single spreadsheet
Investigate simple HTML / JS implementation of a google map with filter
Notes:
Two extra members joined our group today: Robert and Dave
Provided an update and explanation of what we have done so far to Robert and Dave
Andy had done some extra investigating into display finds data on a google map and found that AwesomeTable was limited to a 100 views total before having to pay and suggested that looking into a free option using javascript and HTML would be a better option
It was decided that the we split into two groups:
Ali, Sheila and Dave would explore options for the Mesolithic Deeside website
Andy, Irvine and Robert would continue working with the flint data
The following codepen was found showing what we were looking for, however, the code and script was not runnable, which meant devising our own code
Andy focused on gathering together individual spreadsheets into a single google sheet that would later be converted to a json file for loading into the google map
Contains over 8,000 flint samples
Files needed to be manually joined as columns differed between files
Irvine tidied up the dropbox to ensure that only processed spreadsheet files were ready for loading, and helped with any issues that came up with the files
A number of entries under type needed tidying up to catch variations in spelling and change in case
Before loading into Google Maps, the X & Y co-ordinates needed converting from OSGB36 to Lat & Long
Robert investigated a Python library for the conversion, but when the results were plotted they were offset so another option was needed
Example of an info box and colour-coded points by find type.
Multiple points displayed at once on a zoomed out version of the map.
Summary of Technologies Used
Technology
Description
Comments
QGIS
Original software used by Mesolithic Deeside for collating the flint finds
Looked at options to use QGIS cloud, but features were limited.
Andy had issues loading the shape files and project files – likely to be a problem with Andy’s setup, as version was up to date
Python
Conversion of co-ordinates from OSGB36 to Lat & Long
Conversion of main spreadsheet to JSON file
Robert put together a short script for carrying out the conversion of co-ordinates, however, points were offset. Method dropped in favour of Batch Convert Tool.
AwesomeTable
Seems like a simple way to display and visualise data on a website. Has multiple options for tables and maps.
Allowed for quick displaying and filtering of data. No need to worry about coding.
Limited to 100 views before you had to start paying.
Ditched in favour of a manual solution.
Batch Convert Tool
Quickly converts osgb36 to wgs84 or wgs84 to osgb36 and vice versa. (link)
Very quick when converting 6,000 points at once.
Google Maps (My Maps)
Initially tested for displaying the points on a Google Map
Limited functionality, but points could be easily colour coded for a simple visualisation
Google Maps API, Javascript & HTML
A manual way of displaying a Google Map on a webpage. Allows for full control over what is displayed.
Google Maps API was very tricky to work with. Took a bit of working out how to get points and info boxes to display correctly.
The selected solution for going forward
Google Sheets
Used for compiling flint finds into one file from multiple csv files.
Easily allowed multiple users to work on the same spreadsheet at the same time
This application is designed to be used on a mobile phone. It allows you to call up a map of where you are at the moment and find missing images of listed building (as red dots). You can then authorise the app, using your Wikipedia / Wikidata credentials, and click on a red dot to upload a photo that you either take there and then or from your phone’s media. The image goes straight to Wiki Commons with a CC-BY-SA licence. And, once uploaded, the photos are automatically linked to the wikidata entry for that item! Should that be automagically?
I had a bunch of projects where I thought it would be useful to generate a custom map with missing images (for example of plaques, or boundary stones), then encourage people to photograph them and add them. Thankfully, Wikishootme allows you to do that.
It turns out it’s not too hard to do. Here is a walk through.
1. Create your wikidata query
I’m going to use the March Stones of Aberdeen as an example. I suggest that you copy exactly what I do, creating this query in full through all three steps. Then when you understand how it works, substitute your own query.
In Wikidata’s Query Service, create the query to retrieve the data you want. Wikishootme is quite particular about column names in the final output, so we need to make sure that our query has columns called ‘q‘ (for the wikidata identifiers) and ‘location‘ for the coordinate locations.
(For the purposes of this tutorial it is not necessary to understand the syntax of a SPARQL query. If you are curious, in the above query P31 means an instance of; Q921099 is the identifier for a boundary marker;P131 means located in the administrative entity; and Q62274582 is Aberdeen City)
Test that your query runs ok and returns what you expect. The query above will generate a table with two columns – one labelled q with a list of Wikidata QID codes, and another, location with coordinate pairs for each item.
2. Grab the SPARQL
Next copy all of the code between the {} pair (i.e. all of the second and third lines of the query above, but without the curly braces.
This will create a stream of characters that can be passed as part of a URL to another service. Copy all of that text. When I encode the query above I get the following string:
Click on the link above. Did it work? It does for me. When I open it it defaults to a whole world map.
Default view of Wikishootme
Scroll and zoom to where your red dots are.
Wikishootme, scrolled and zoomed
Tip: when you get the map centred and at the scale you like, recopy the URL. This will capture the location and zoom level in your map for sharing.
Also, click on the layers symbol at the top right of the map. Choose to display where the data has images (green) as well as the red:
Wikishootme Layers control
That will change your view to showing red (missing) and green (captured) images for your wikidata items. This will give the URL such as this which loads the map correctly centred. at the right scale, and showing the layers you want.
Wikishootme showing red and green dots
Now you can share your map. I suggest copying your URL (see the Tip above) into a link shortener such as bit.ly so as to make sharing easier.
Now, when someone clicks on your URL they can click on a red dot, and upload a missing photo to Wiki Commons, and automatically link it to Wikidata – and turn those red dots green!
In part one I described what we did at CTC18 to capture data and images of Commemorative Plaques in Aberdeen, and what I then did in the following three weeks.
A few people asked my why we would bother to put plaques into Wikidata and WikiCommons in this way. Why not have a council website – or why not use Open Plaques?
In this second instalment I am going to demonstrate how we can use the data which we have created to make some interesting visualisations and even do some calculations and analysis.
It can also power other new apps and services – allowing developers to create tailored routes around the city, on themes such as the arts or medicine – which is beyond the scope of this post.
Getting Started
At the time of writing we now have 132 Aberdeen Commemorative Plaques recorded in Wiki Data.
I can check that with this simple query on the Wiki Data Query Service:
Plaques – Query One
All that this does is ask for every instance (P31) of a commemorative plaque (Q721747) whcich is located in (P131) the Aberdeen City (Q62274582) area.
Click on the white-on-blue arrow at the left. See what it produces. Note the bottom half of the screen turns into a table of results, and on the centre bar there is a message ‘xxx results in xxxx milliseconds‘.
How many pictures of plaques?
I can retrieve the photograph for plaque using the following query.
Plaques – Query Two
Here I am saying give us plaques which have image (P18). In effect this is saying ONLY those that have an image. If not all entries have an image, yet, then we will get a smaller number.
As I run it I get 126 – which is six fewer than I got plaques.
Get all plaques with images or not
Let’s modify the query to this.
Plaques – Query Three
Here I am the OPTIONAL command which has the effect of saying IF there is an image give me it, but don’t restrict the results to only those with images. When we run that we can spot the missing ones by scrolling down through the list. I get six plaques with no images. This is a useful technique to spot missing things when totals (in this case plaques and images) don’t tally.
As it stands the query is still not very user-friendly as all we have for the plaques is their Plaque ID. Of course we can click on those, but it would be more helpful to have the names of their subjects.
We’ll do that in two steps.
Firstly, let’s work out what the subjects are.
We can add the following line to the query and remember to add ?subject to the SELECT on the first line.
If we run that we get a new column called subject and it is filled with links to subject IDs, which are the Wikidata entries for either people or things that the plaques commemorates. I note that when I run it my list has grown from 132 to 134.
Any guesses why that should be?
Some of the plaques commemorate more than one person.
Let’s make it a bit more friendly.
Add the following line just before the end of your query
SERVICE wikibase:label {bd:serviceParam wikibase:language "en". }
And change ?subject to ?subjectLabel in the first line.
This instructs the WikiData Query service to use another service to retrieve labels from the items.
Plaques – Query Four
The label is in effect the title of the Wikidata item. Look at this one https://www.wikidata.org/wiki/Q80818579 Immediately below the title, and to the left, there is an edit link. Click that. See how the ‘label‘ and the ‘description‘ immediately below it become editable. Cancel that for now.
At this point you might want to change the view for your query just to have a look at the images we have.
Above the table of results, on the extreme left there is an eye symbol and a drop down. Choose “Image Grid” to see the images only.
Plaques – change view
You might also have noticed that there are other options, several of which are greyed out as we don’t yet have that data in our query. These views include ‘Map‘ and “Timeline‘. We’ll come back to those.
Our Image Grid looks something like this:
Plaques – Image Grid
Remember to swap back to ‘Table’ view once you’ve finished.
Adding more data fields
We can now add more data fields to our query.
Firstly, let’s add the geographic coordinates of the plaques’ locations.
Add the following line to your code:
OPTIONAL {?plaque wdt:P625 ?coordinates .}
and, again add the new value, ?coordinates to the first line of the query too.
You will now have an extra field in the returned data table.
Now change the view from Table to Map. The Wikidata query service automatically uses the coordinates to plot the results on a map which is scaled to show the results. You may need to scroll down to see all of the map. Click on one of the plotted points. You should get a pop up with the name of the person or building commemorated, plus a photo of the plaque itself, as shown below.
Plaques – map view
Note – if you add the following as the first line of your query, it will default to a map view rather than table when first run.
#defaultView:Map
Now let’s see if we can get more data for the people for whom there are plaques.
Dates of birth and death
We can change our query to find out if there are dates of birth and death for our human subjects (rather than buildings).
We can use P569 (date of birth) and P570 (date of death) and ascribe those to
?DOB and ?DOD respectively – again, adding those fields to our SELECT statement on line one. Your query should look like this?
Looking at our table of results we can see that we have a mix of types of results – people, bridges, buildings etc. but only the people have dates.
Table showing dates of birth
Interestingly the one subject with the DOB and DOD in the screenshot above is Elizabeth Crombie Duthie who gifted Duthie Park to the city of Aberdeen.
Remember, if you change the DOB and DOB from being OPTIONAL to just being regular requests, you can filter records to show ONLY those with dates associated with them which will screen out not only non-human subjects but will exclude any people with incomplete or missing dates.
Notable people
It could be argued that the fact there is a plaque to a person would indicate that they are notable, but not every person or object for which there is a plaque has a Wikipedia article. Let’s add some code to see which of our plaques has an associated article.
Changing the above so that we remove the OPTIONAL {} around the section beginning ?article we get ONLY those with Wikipedia articles which is, as I run it, 79 plaque subjects.
You can if you want we add the following
?subject wdt:P31 wd:Q5 .
where P31 (instance of ) is Q5 (human) we can screen out all of the non-people plaques.
At this point, try flipping the view to TimeLine – you may have to scroll down quite a way to see all of the plaques. Many of them are concentrated at the right, spanning much of the 20th century. You should see John Barbour (1316-1395 at the extreme left).
Plaques – timeline
Finally, before we start doing some statistical analysis let’s try something more sophisticated.
Can we create a map showing only female subjects whose work was in the medical sciences?
To do that we need to select only subjects who have a P21 (gender or sex) of Q6581072 (female). Then we need to select an occupation (P31) which is an instance or subclass of Q66811410 (the medical profession). This requires a structure that we haven’t see before:
?occupation wdt:P31/wdt:P279* wd:Q66811410
While we are at it, let’s get an image of the subject if there is one, and find out of there is a wikipedia article about the subject. And, since we want a map, we add that as our default view at the top.
With 81% of plaques to people being for males it is hardly surprising that our league table of names begins with James, William, George, John, Alexander ….
We can do more sophisticated analysis too.
Analysing Occupations
We can add the following line to our query to get back the occupation of the subject of the plaque:
?subject wdt:P106 ?occupation
Bear in mind that many of our plaque subjects are true polymaths. Have a look at Robert Brown. He has 10 listed occupations!
So what are the most common occupations of those people for whom there are plaques? Any guesses?
Let’s use the following query:
Plaques – Using Count()
This uses the COUNT () function as well as a GROUP BY clause. The query looks at all of the different occupation labels, counts how many of each there are.
This returns, by default, a table of values. We can flip to a Bar Chart to make better sense of the data:
Plaques – Bar Chart of occupations
So, we can see that for those commemorated by a plaque the most common occupations are Physician, Painter, University Lecturer, Writer and so on.
We can add a couple of refinements if we wish. If we want our query to default to a BarChart when we run it we can add the following line at the start of the query:
#defaultView:BarChart
and if we want the table to be sorted by value we can add a line such as
Over the last month I’ve been busy gathering data, taking photographs and publishing all of those on WikiData and wiki Commons. That phase is not quite complete, if it ever could be considered complete. You can monitor live progress here.
There are a couple of photographs which I can’t easily take which I know Aberdeen City Council’s Museum and Galleries team have. It would be great to see those made available by them on Wiki Commons, as I have shared the 148 plaque photos I have taken.
I know of at least 24 more plaques which I have photographed which are not listed yet in Wikidata.
When I published part one of this series I got some great feedback on Twitter. One suggestion is that we add structured data to the Wiki Commons pages for each photograph. Another was to add further data to the record for each plaque using statement P276 (location) where the plaque is on a known listed building. So far I have done that for 5 plaques – check it for yourself. There are loads more to do.
Many of the people records that I have created in Wikidata are skeletal. They need more detail, photographs, biographical links etc. Similarly, given that people or places are noteworthy enough to merit a plaque, they should pass the notability test for Wikipedia, yet at least 68 plaque subjects have no Wikipedia entry.
And plaques are just a start – an easy introduction to what is possible given, in this case, about 100 hours of work. While that was almost all done by one person, if we ran a Code The City weekend on a similar theme and similar sized challenge, six people could achieve the same over a weekend with a little coordination.
At Code The City, we’re about to start discussions with the local cultural institutions about setting up a more formal alliance for the city (shire?) to help shape how they use digital and data more effectively and grow volunteers with skills and tools to make that happen, which is an exciting note on which to finish this post! Watch this space, as they say.
Code The City 