Statistics Beta launched – fun with SPARQL
Confused by SPARQL
I’m trying to learn more about linked open data so I thought I’d attempt to write a SPARQL query. Our platform lets you query all of the data held on it using SPARQL.
At first I struggled, but then I remembered some wise words that someone once said about thinking about the structure of the data. So I thought about it, and by considering how all the data is stored in RDF triples it started to make sense. Triples are based on a subject, a predicate (property) and an object. You can express statements like “roses are red” as a triple. The subject would be roses, the predicate would be colour and the object would be red. I just needed to think of which triples I wanted.
I decided to write a query to extract a simple dataset: annual electricity usage in Edinburgh local authority area. I built up the query slowly. First I selected all triples that were in the Energy consumption dataset. Then I selected the rest of the information I’d need: year, energy type (gas or electricity), customer type (all, domestic or commercial) and the amount consumed. Finally I filtered the data to only include electricity used in Edinburgh. The query I used is at the bottom of this page. I’m sure that it could be written in a much simpler and more efficient way, but it does the trick.
Well you can start to do cool things like automatically import the results into a Google spreadsheet. This spreadsheet will always show the latest data and you can use it to power a chart like the one below. Pretty cool!
And here is the query:
PREFIX dcterms: <http://purl.org/dc/terms/>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX qb: <http://purl.org/linked-data/cube#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
SELECT ?year ?consumption
?observation <http://purl.org/linked-data/cube#dataSet> <http://statistics.gov.scot/data/business-enterprise-and-energy/energy-consumption>.
?observation <http://statistics.gov.scot/def/statistical-dimensions/refPeriod> ?yearURI.
?observation <http://statistics.gov.scot/def/measure-properties/count> ?consumption.
?observation <http://statistics.gov.scot/def/statistical-dimensions/refArea> ?refArea.
?observation <http://statistics.gov.scot/def/dimension/energyType> ?EnergyType.
?observation <http://statistics.gov.scot/def/dimension/customerType> ?CustomerType.
?yearURI rdfs:label ?year .
FILTER (?refArea = <http://statistics.gov.scot/id/statistical-geography/S12000036>)
FILTER (?EnergyType = <http://statistics.gov.scot/def/concept/energy-type/electricity>)
FILTER (?CustomerType = <http://statistics.gov.scot/def/concept/customer-type/all>)