In at the deep end: how to get started with COINS Linked Data

A tweet today from … someone?! … alerted me to the fact that COINS data has just been published as Linked Data over on the TSO Open Up Labs website. The documentation is still in the works, and as yet there aren’t any example queries available showing how to get started with querying the dataset, so I thought it might be worth trying to pull a few simple queries together to at least providing a jumping off point for exploring the data via the SPARQL interface.

I’m afraid this is all pretty much in at the deep end, but I’ve tried to construct queries that you can: - copy and paste straight into the form on the TSO OpenUpLabs website; - edit in hopefully obvious places to create your own queries; - combine together, in part, to create more complex queries.

At the moment, I’m leaving the money out of it, in favour of just getting a feeling for the structure of the data, and how to interrogate it.

So let’s get started…

The following query will generate a list of department codes and the associated department names – copy and paste it into the form at http://openuplabs.tso.co.uk/sparql/gov-coins, select the “Plain Text” output, and hit submit:

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT distinct ?deptCode ?deptName WHERE {
?dept <http://finance.data.gov.uk/dsd/coins/dimension/departmentCode> ?deptCode.
?deptCode rdfs:comment ?deptName.
} LIMIT 20

(For more, or fewer, results, change the LIMIT amount, or remove it altogether.)

After a short delay (?! – I’m not sure if I could optimise the query…? ) the results will come back looking something like this fragment:

$deptCode $deptName
<http://finance.data.gov.uk/def/coins/department-code/sfo019> “Serious Fraud Office”
<http://finance.data.gov.uk/def/coins/department-code/ilr041> “HM Revenue and Customs”
<http://finance.data.gov.uk/def/coins/department-code/isc034> “Office of the Immigration Services Commissioner”

To find the code for a particular department, we can try searching around a word that appears in the name of the department we are looking for – such as “Business”:

SELECT distinct ?deptCode ?deptName WHERE {
?dept <http://finance.data.gov.uk/dsd/coins/dimension/departmentCode> ?deptCode.
?deptCode rdfs:comment ?deptName.
FILTER (regex(str(?deptName), 'Business'))
}

The following query returns the names of programmes, and their codes, for the Serious Fraud Office (which has identifier <http://finance.data.gov.uk/def/coins/department-code/sfo019>):

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT distinct ?progCode ?progName WHERE {
?dept <http://finance.data.gov.uk/dsd/coins/dimension/departmentCode> <http://finance.data.gov.uk/def/coins/department-code/sfo019>.
?dept <http://finance.data.gov.uk/dsd/coins/attribute/programmeObjectGroupCode > ?progCode.
?progCode rdfs:comment ?progName.
}

We can also run more refined queries, for example search for projects to do with Nuclear topics in the Department of Energy and Climate Change?

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT distinct ?progCode ?progName WHERE {
?dept <http://finance.data.gov.uk/dsd/coins/dimension/departmentCode> <http://finance.data.gov.uk/def/coins/department-code/dec066>.
?dept <http://finance.data.gov.uk/dsd/coins/dimension/programmeObjectCode> ?progCode. ?progCode rdfs:comment ?progName.
FILTER (regex(str(?progName), 'Nuclear'))
} LIMIT 5

Many of the items in COINS involve one department as counterparty to another. So how do we see which other departments are counterparty to the Cabinet Office and on which programmes?

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT distinct ?v ?ccLabel ?progName ?progCode WHERE {
?dept <http://finance.data.gov.uk/dsd/coins/dimension/departmentCode> <http://finance.data.gov.uk/def/coins/department-code/cab010>.
?dept <http://finance.data.gov.uk/dsd/coins/dimension/programmeObjectCode> ?progCode.
?progCode rdfs:comment ?progName.
?dept <http://finance.data.gov.uk/dsd/coins/dimension/counterpartyCode> ?v.
?v rdfs:comment ?ccLabel.
FILTER (!(?v = <http://finance.data.gov.uk/def/coins/counterparty-code/cpid.na>))
} limit 20

So, now we can start to follow the money… Only, err, erm, I haven’t worked out how to find the money yet!