Common Data Queries: The Cultural Commissions Finding measurements for specific emissions over time

We’re starting to get messages from people interested in participating in the cultural commissions – which is AWESOME!

With apologies to those who aren’t interested in the dirty details of the data, it seems fairest to share queries we write to support this activity here.

First off the line is this SPARQL which lists all the measurements for all NO2 sensors over a given time period. The measurements are averaged to provide one per hour (min and max are also given for the hour so you can draw error bars etc). The query could be used to draw a citywide map of NO2 measurements over the last month.

Judicious use of URL hacking will let you switch between different measurement properties (or emissions), different time periods and different output formats (like CSV).

In theory you could grab all the data in one query, but if you want to be kind to our servers, please grab more discrete chunks of data.  Be warned that the query engine [highlight]will[/highlight] truncate results (and let you know) if you try to run queries which will damage performance of the server.

On the SPARQL:

select ?sensor, max(?day), max(?hour), avg(?observationValue), max(?observationValue), min(?observationValue)
where {
  graph ?g {
    ?s <uri://> ?sensor .
    ?s a <> .
    ?s <> ?observationTime.
    ?s <> ?observationValue .
    ?sensor <> <>     .
    BIND (bif:subseq( str( ?observationTime ),0,11) AS ?day) .
    BIND (bif:subseq( str( ?observationTime ),11,13) AS ?hour) .
    BIND (bif:subseq( str( ?observationTime ),0,13) AS ?dayhour) .
    FILTER ( xsd:date(?observationTime) > xsd:date("2014-10-13") && xsd:date(?observationTime) <=    xsd:date("2014-11-13") )  
GROUP BY ?sensor ?dayhour
ORDER BY ?dayhour ?sensor

You can run this query here.

If there is sufficient interest, we can run a short SPARQL session to help people get to grips with this extremely powerful way of sharing data.

[highlight]Update[/highlight] This page seems to present a useful conversion of limits set in the EU Clean Air Directive from μg/m3 to ppb (which our measurements are in).

Ian Ibbotson

Open source developer and contributor working in libraries, local government, culture and learning.