Sample SPARQL

boisvert edited this page Oct 4, 2015 · 39 revisions

Welcome to the ShefAirQualityAgent wiki!

Get observations for a specific sensor

select ?s ?observationTime ?observationValue where {
  graph ?g {
    ?s <uri://opensheffield.org/properties#sensor>     <uri://opensheffield.org/datagrid/sensors/Groundhog1/LD-Groundhog1_NO2.ic> .
    ?s a <http://purl.oclc.org/NET/ssnx/ssn#ObservationValue> .
    ?s <http://purl.oclc.org/NET/ssnx/ssn#endTime> ?observationTime.
    ?s <http://purl.oclc.org/NET/ssnx/ssn#hasValue> ?observationValue
  }
} LIMIT 1000

List all sensors

Run this query

select ?sensor where {
    ?sensor a <http://purl.oclc.org/NET/ssnx/ssn#SensingDevice>
}
ORDER BY DESC(?sensor)

All properties of all sensors

[Run This Query] (http://apps.opensheffield.org/sparql?default-graph-uri=&query=select+%3Fsensor+%3Fp+%3Fo+where+%7B%0D%0A++++%3Fsensor+a+%3Chttp%3A%2F%2Fpurl.oclc.org%2FNET%2Fssnx%2Fssn%23SensingDevice%3E+.%0D%0A++++%3Fsensor+%3Fp+%3Fo%0D%0A%7D%0D%0AORDER+BY+DESC%28%3Fsensor%29+DESC%28%3Fp%29&format=text%2Fhtml&timeout=0&debug=on)

select ?sensor ?p ?o where {
    ?sensor a <http://purl.oclc.org/NET/ssnx/ssn#SensingDevice> .
    ?sensor ?p ?o
}
ORDER BY DESC(?sensor) DESC(?p)

List all scc sensors, id, and highest timestamp

[Sensors and what they measure] (http://apps.opensheffield.org/sparql?default-graph-uri=&query=select+%3Fsensor+%3Fmeasures+where+%7B%0D%0A++%3Fsensor+%3Chttp%3A%2F%2Fpurl.oclc.org%2FNET%2Fssnx%2Fssn%23MeasurementProperty%3E+%3Fmeasures+.%0D%0A++%3Fsensor+a+%3Chttp%3A%2F%2Fpurl.oclc.org%2FNET%2Fssnx%2Fssn%23SensingDevice%3E+.%0D%0A%7D%0D%0Aorder+by+DESC%28%3Fsensor%29+DESC%28%3Fmeasures%29&format=text%2Fhtml&timeout=0&debug=on)

Sensors, what they measure and location

select ?sensor ?maxTimestamp ?sensorId ?lat ?long ?lastCheck where {
  ?sensor a <http://purl.oclc.org/NET/ssnx/ssn#SensingDevice> .
  ?sensor <uri://opensheffield.org/properties#sensorId> ?sensorId .
  ?sensor <http://purl.oclc.org/NET/ssnx/ssn#onPlatform> "scc_air_quality" .
  ?sensor <http://www.w3.org/2003/01/geo/wgs84_pos#long> ?long .
  ?sensor <http://www.w3.org/2003/01/geo/wgs84_pos#lat> ?lat .
  ?sensor <uri://opensheffield.org/properties#lastCheck> ?lastCheck .
  OPTIONAL { ?sensor <uri://opensheffield.org/properties#maxTimestamp> ?maxTimestamp } .
}
ORDER BY DESC(?sensor)

or

select ?sensor ?measures where {
  ?sensor <http://purl.oclc.org/NET/ssnx/ssn#MeasurementProperty> ?measures .
  ?sensor a <http://purl.oclc.org/NET/ssnx/ssn#SensingDevice>
}
order by DESC(?sensor) DESC(?measures)

How many observations for each sensor, and with what min/max timestamps

select ?sensor (count(?observation) as ?totalObservations) (min(?endTime) as ?minEndTime) ( max(?endTime) as ?maxEndTime ) where {
    ?sensor a <http://purl.oclc.org/NET/ssnx/ssn#SensingDevice>.
    ?observation <uri://opensheffield.org/properties#sensor> ?sensor.
    ?observation <http://purl.oclc.org/NET/ssnx/ssn#endTime> ?endTime 
}
GROUP BY (?sensor)

As above, but with min, max and avg values

select ?sensor (count(?observation) as ?totalObservations) (min(?endTime) as ?minEndTime) ( max(?endTime) as ?maxEndTime ) ( max(?observationValue) as ?maxObservedValue ) ( min(?observationValue) as ?minObservedValue ) ( avg(?observationValue) as ?avgObservedValue ) where {
    ?sensor a <http://purl.oclc.org/NET/ssnx/ssn#SensingDevice>.
    ?observation <uri://opensheffield.org/properties#sensor> ?sensor.
    ?observation <http://purl.oclc.org/NET/ssnx/ssn#endTime> ?endTime .
    ?observation <http://purl.oclc.org/NET/ssnx/ssn#hasValue> ?observationValue
}
GROUP BY (?sensor)

Measurements for a sensor grouped by date and hour, suitable for XYZ plot.

select max(?day), max(?hour), avg(?observationValue)
where {
  graph ?g {
    ?s <uri://opensheffield.org/properties#sensor>     <uri://opensheffield.org/datagrid/sensors/Groundhog1/LD-Groundhog1_NO2.ic> .
    ?s a <http://purl.oclc.org/NET/ssnx/ssn#ObservationValue> .
    ?s <http://purl.oclc.org/NET/ssnx/ssn#endTime> ?observationTime.
    ?s <http://purl.oclc.org/NET/ssnx/ssn#hasValue> ?observationValue .
    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 ?dayhour
ORDER BY ?dayhour
LIMIT 10000

All N02 readings for all sensors over a given time period

averaged to a measurement per hour (With min and max for error bars)

[Run this query] (http://apps.opensheffield.org/sparql?default-graph-uri=&query=select+%3Fsensor%2C+max%28%3Fday%29%2C+max%28%3Fhour%29%2C+avg%28%3FobservationValue%29%2C+max%28%3FobservationValue%29%2C+min%28%3FobservationValue%29%0D%0Awhere+%7B%0D%0A++graph+%3Fg+%7B%0D%0A++++%3Fs+%3Curi%3A%2F%2Fopensheffield.org%2Fproperties%23sensor%3E+%3Fsensor+.%0D%0A++++%3Fs+a+%3Chttp%3A%2F%2Fpurl.oclc.org%2FNET%2Fssnx%2Fssn%23ObservationValue%3E+.%0D%0A++++%3Fs+%3Chttp%3A%2F%2Fpurl.oclc.org%2FNET%2Fssnx%2Fssn%23endTime%3E+%3FobservationTime.%0D%0A++++%3Fs+%3Chttp%3A%2F%2Fpurl.oclc.org%2FNET%2Fssnx%2Fssn%23hasValue%3E+%3FobservationValue+.%0D%0A++++%3Fsensor+%3Chttp%3A%2F%2Fpurl.oclc.org%2FNET%2Fssnx%2Fssn%23MeasurementProperty%3E+%3Chttp%3A%2F%2Fdbpedia.org%2Fresource%2FNO2%3E+++++.%0D%0A++++BIND+%28bif%3Asubseq%28+str%28+%3FobservationTime+%29%2C0%2C11%29+AS+%3Fday%29+.%0D%0A++++BIND+%28bif%3Asubseq%28+str%28+%3FobservationTime+%29%2C11%2C13%29+AS+%3Fhour%29+.%0D%0A++++BIND+%28bif%3Asubseq%28+str%28+%3FobservationTime+%29%2C0%2C13%29+AS+%3Fdayhour%29+.%0D%0A++++FILTER+%28+xsd%3Adate%28%3FobservationTime%29+%3E+xsd%3Adate%28%222014-10-13%22%29+%26%26+xsd%3Adate%28%3FobservationTime%29+%3C%3D++++xsd%3Adate%28%222014-11-13%22%29+%29++%0D%0A++%7D%0D%0A%7D%0D%0AGROUP+BY+%3Fsensor+%3Fdayhour%0D%0AORDER+BY+%3Fdayhour+%3Fsensor&format=text%2Fhtml&timeout=0&debug=on)

select ?sensor, max(?day), max(?hour), avg(?observationValue), max(?observationValue), min(?observationValue)
where {
  graph ?g {
    ?s <uri://opensheffield.org/properties#sensor> ?sensor .
    ?s a <http://purl.oclc.org/NET/ssnx/ssn#ObservationValue> .
    ?s <http://purl.oclc.org/NET/ssnx/ssn#endTime> ?observationTime.
    ?s <http://purl.oclc.org/NET/ssnx/ssn#hasValue> ?observationValue .
    ?sensor <http://purl.oclc.org/NET/ssnx/ssn#MeasurementProperty> <http://dbpedia.org/resource/NO2>     .
    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

Daily Average NO2 for each sensor

select ?sensor, ?day, avg(?observationValue), max(?observationValue), min(?observationValue)
where {
  graph ?g {
    ?s <uri://opensheffield.org/properties#sensor> ?sensor .
    ?s a <http://purl.oclc.org/NET/ssnx/ssn#ObservationValue> .
    ?s <http://purl.oclc.org/NET/ssnx/ssn#endTime> ?observationTime.
    ?s <http://purl.oclc.org/NET/ssnx/ssn#hasValue> ?observationValue .
    ?sensor <http://purl.oclc.org/NET/ssnx/ssn#MeasurementProperty> <http://dbpedia.org/resource/NO2>     .
    BIND (bif:subseq( str( ?observationTime ),0,11) AS ?day) .
    FILTER ( xsd:date(?observationTime) > xsd:date("2014-01-01") && xsd:date(?observationTime) <=    xsd:date("2014-11-13") )  
  }
}
GROUP BY ?sensor ?day
ORDER BY ?day ?sensor

Annual Mean - All sensors measuring NO2

[Run this query] (https://apps.opensheffield.org/sparql?default-graph-uri=&query=select+%3Fsensor%2C+%3Fyear%2C+avg%28%3FobservationValue%29%2C+max%28%3FobservationValue%29%2C+min%28%3FobservationValue%29%0D%0Awhere+%7B%0D%0A++graph+%3Fg+%7B%0D%0A++++%3Fs+%3Curi%3A%2F%2Fopensheffield.org%2Fproperties%23sensor%3E+%3Fsensor+.%0D%0A++++%3Fs+a+%3Chttp%3A%2F%2Fpurl.oclc.org%2FNET%2Fssnx%2Fssn%23ObservationValue%3E+.%0D%0A++++%3Fs+%3Chttp%3A%2F%2Fpurl.oclc.org%2FNET%2Fssnx%2Fssn%23endTime%3E+%3FobservationTime.%0D%0A++++%3Fs+%3Chttp%3A%2F%2Fpurl.oclc.org%2FNET%2Fssnx%2Fssn%23hasValue%3E+%3FobservationValue+.%0D%0A++++%3Fsensor+%3Chttp%3A%2F%2Fpurl.oclc.org%2FNET%2Fssnx%2Fssn%23MeasurementProperty%3E+%3Chttp%3A%2F%2Fdbpedia.org%2Fresource%2FNO2%3E+.%0D%0A++++BIND+%28bif%3Asubseq%28+str%28+%3FobservationTime+%29%2C0%2C4%29+AS+%3Fyear%29+.%0D%0A++++FILTER+%28+%3FobservationValue+%3E+0+%29%0D%0A++%7D%0D%0A%7D%0D%0AGROUP+BY+%3Fsensor+%3Fyear%0D%0AORDER+BY+%3Fyear+%3Fsensor&format=text%2Fhtml&timeout=0&debug=on)

select ?sensor, ?year, avg(?observationValue), max(?observationValue), min(?observationValue)
where {
  graph ?g {
    ?s <uri://opensheffield.org/properties#sensor> ?sensor .
    ?s a <http://purl.oclc.org/NET/ssnx/ssn#ObservationValue> .
    ?s <http://purl.oclc.org/NET/ssnx/ssn#endTime> ?observationTime.
    ?s <http://purl.oclc.org/NET/ssnx/ssn#hasValue> ?observationValue .
    ?sensor <http://purl.oclc.org/NET/ssnx/ssn#MeasurementProperty> <http://dbpedia.org/resource/NO2> .
    BIND (bif:subseq( str( ?observationTime ),0,4) AS ?year) .
    FILTER ( ?observationValue > 0 )
  }
}
GROUP BY ?sensor ?year
ORDER BY ?year ?sensor

A really neat page at https://code.google.com/p/void-impl/wiki/SPARQLQueriesForStatistics lists some cool SPARQL statistics

total number of triples

SELECT (COUNT(*) AS ?no) { ?s ?p ?o  }

total number of entities

SELECT COUNT(distinct ?s) AS ?no { ?s a []  }

total number of distinct resource URIs (deprecated??)

SELECT (COUNT(DISTINCT ?s ) AS ?no) { { ?s ?p ?o  } UNION { ?o ?p ?s } FILTER(!isBlank(?s) && !isLiteral(?s)) }         

total number of distinct classes

SELECT COUNT(distinct ?o) AS ?no { ?s rdf:type ?o }

total number of distinct predicates

SELECT count(distinct ?p) { ?s ?p ?o }

total number of distinct subject nodes

SELECT (COUNT(DISTINCT ?s ) AS ?no) {  ?s ?p ?o   } 

total number of distinct object nodes

SELECT (COUNT(DISTINCT ?o ) AS ?no) {  ?s ?p ?o  filter(!isLiteral(?o)) }                               

exhaustive list of classes used in the dataset

SELECT DISTINCT ?type { ?s a ?type }

exhaustive list of properties used in the dataset

SELECT DISTINCT ?p { ?s ?p ?o }

table: class vs. total number of instances of the class

SELECT  ?class (COUNT(?s) AS ?count ) { ?s a ?class } GROUP BY ?class ORDER BY ?count

table: property vs. total number of triples using the property

SELECT  ?p (COUNT(?s) AS ?count ) { ?s ?p ?o } GROUP BY ?p ORDER BY ?count

table: property vs. total number of distinct subjects in triples using the property

SELECT  ?p (COUNT(DISTINCT ?s ) AS ?count ) { ?s ?p ?o } GROUP BY ?p ORDER BY ?count

table: property vs. total number of distinct objects in triples using the property

SELECT  ?p (COUNT(DISTINCT ?o ) AS ?count ) { ?s ?p ?o } GROUP BY ?p ORDER BY ?count

Grouped and with new header label

select ?sensor, (max(?day) as ?day), (max(?hour) as ?hour), (avg(?observationValue) as ?observation), (max(?observationValue) as ?maxReading), (min(?observationValue) as ?minReading)
where {
  graph ?g {
    ?s <uri://opensheffield.org/properties#sensor> ?sensor .
    ?s a <http://purl.oclc.org/NET/ssnx/ssn#ObservationValue> .
    ?s <http://purl.oclc.org/NET/ssnx/ssn#endTime> ?observationTime.
    ?s <http://purl.oclc.org/NET/ssnx/ssn#hasValue> ?observationValue .
    ?sensor <http://purl.oclc.org/NET/ssnx/ssn#MeasurementProperty> <http://dbpedia.org/resource/NO2>     .
    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

Sensors and the highest timestamp

select ?sensor, max(?timestamp)
where {
    ?s <uri://opensheffield.org/properties#sensor> ?sensor .
    ?s a <http://purl.oclc.org/NET/ssnx/ssn#ObservationValue> .
    ?s <http://purl.oclc.org/NET/ssnx/ssn#endTime> ?timestamp .
}
GROUP BY ?sensor

Report the latest readings for all sensors

N.B. II :I'm worried about the speed of this query - it probably needs rewriting to be more optimal.

select ?sensor, ?timestamp, ?observationValue
where {
    ?observation <http://purl.oclc.org/NET/ssnx/ssn#hasValue> ?observationValue .
    ?observation <http://purl.oclc.org/NET/ssnx/ssn#endTime> ?timestamp .
    ?observation <uri://opensheffield.org/properties#sensor> ?sensor .

    {
        select (max(?r) AS ?observation)
        where {
            ?r a <http://purl.oclc.org/NET/ssnx/ssn#ObservationValue> .
            ?r <uri://opensheffield.org/properties#sensor> ?sensor .
        }
        GROUP BY ?sensor
    }
}

Sensors latest reading with type and lat/lon

select ?sensor, ?measures, ?lat, ?long, ?timestamp, ?observationValue
where {
    ?observation <http://purl.oclc.org/NET/ssnx/ssn#hasValue> ?observationValue .
    ?observation <http://purl.oclc.org/NET/ssnx/ssn#endTime> ?timestamp .
    ?observation <uri://opensheffield.org/properties#sensor> ?sensor .
    ?sensor <http://purl.oclc.org/NET/ssnx/ssn#MeasurementProperty> ?measures .
    ?sensor <http://www.w3.org/2003/01/geo/wgs84_pos#long> ?long .
    ?sensor <http://www.w3.org/2003/01/geo/wgs84_pos#lat> ?lat .

    {
        select (max(?r) AS ?observation)
        where {
            ?r a <http://purl.oclc.org/NET/ssnx/ssn#ObservationValue> .
            ?r <uri://opensheffield.org/properties#sensor> ?sensor .
        }
        GROUP BY ?sensor
    }
}

Latest NO2 Readings over the city

select ?sensor, ?lat, ?long, ?timestamp, ?observationValue
where {
  ?observation <http://purl.oclc.org/NET/ssnx/ssn#hasValue> ?observationValue .
  ?observation <http://purl.oclc.org/NET/ssnx/ssn#endTime> ?timestamp .
  ?observation <uri://opensheffield.org/properties#sensor> ?sensor .
  ?sensor <http://purl.oclc.org/NET/ssnx/ssn#MeasurementProperty> <http://dbpedia.org/resource/NO2> .
  ?sensor <http://www.w3.org/2003/01/geo/wgs84_pos#long> ?long .
  ?sensor <http://www.w3.org/2003/01/geo/wgs84_pos#lat> ?lat .

  {
      select (max(?r) AS ?observation)
      where {
          ?r a <http://purl.oclc.org/NET/ssnx/ssn#ObservationValue> .
          ?r <uri://opensheffield.org/properties#sensor> ?sensor .
      }
      GROUP BY ?sensor
  }
}

As above but only use readings from 2015

select ?sensor, ?lat, ?long, ?timestamp, ?observationValue
where {
  ?observation <http://purl.oclc.org/NET/ssnx/ssn#hasValue> ?observationValue .
  ?observation <http://purl.oclc.org/NET/ssnx/ssn#endTime> ?timestamp .
  ?observation <uri://opensheffield.org/properties#sensor> ?sensor .
  ?sensor <http://purl.oclc.org/NET/ssnx/ssn#MeasurementProperty> <http://dbpedia.org/resource/NO2> .
  ?sensor <http://www.w3.org/2003/01/geo/wgs84_pos#long> ?long .
  ?sensor <http://www.w3.org/2003/01/geo/wgs84_pos#lat> ?lat .

  {
      select (max(?r) AS ?observation)
      where {
          ?r a <http://purl.oclc.org/NET/ssnx/ssn#ObservationValue> .
          ?r <uri://opensheffield.org/properties#sensor> ?sensor .
          ?r <http://purl.oclc.org/NET/ssnx/ssn#endTime> ?ts .  
          FILTER ( xsd:dateTime(?ts) > xsd:dateTime('2015-01-01 00:00:00') )
      }
      GROUP BY ?sensor
  }
}

Sensors where sensorId contains '.ic'

select ?sensor ?sensorId where {
    ?sensor a <http://purl.oclc.org/NET/ssnx/ssn#SensingDevice> .
    ?sensor <uri://opensheffield.org/properties#sensorId> ?sensorId .
    FILTER contains ( ?sensorId, '.ic' ) 
}

Real time sensors

select ?sensor where {
    ?sensor a <uri://opensheffield.org/types#realtimeMonitoringStation> .
}

All measurements from real time sensors

select ?observation, ?sensor, ?timestamp, ?observationValue
where {
    ?observation <http://purl.oclc.org/NET/ssnx/ssn#hasValue> ?observationValue .
    ?observation <http://purl.oclc.org/NET/ssnx/ssn#endTime> ?timestamp .
    ?observation <uri://opensheffield.org/properties#sensor> ?sensor .
    ?sensor a <uri://opensheffield.org/types#realtimeMonitoringStation> .
}

with date filter

select ?observation, ?sensor, ?timestamp, ?observationValue
where {
    ?observation <http://purl.oclc.org/NET/ssnx/ssn#hasValue> ?observationValue .
    ?observation <http://purl.oclc.org/NET/ssnx/ssn#endTime> ?timestamp .
    ?observation <uri://opensheffield.org/properties#sensor> ?sensor .
    ?sensor a <uri://opensheffield.org/types#realtimeMonitoringStation> .
    FILTER ( xsd:dateTime(?timestamp) > xsd:dateTime('2015-01-01 00:00:00') )
}