Yesterday I received a request asking how to extract some simple data from the Ordnance Survey linked data using a SPARQL query. This post is not intended as a SPARQL tutorial – you can find plenty of those here.

A user wanted to know how to retrieve the name, unit-id, GSS Code, lat and long of all the unitary authorities, districts and metropolitan districts in England, Scotland and Wales as a CSV file.

To extract this information for all of the districts go to the Ordnance Survey’s Boundary-Line(TM) linked data SPARQL endpoint explorer and in the response format drop down menu select CSV. Now in the query window enter the following query:

select ?name ?lat ?long ?gss ?unit_id

where

{

?x <http://www.w3.org/2000/01/rdf-schema#label> ?name .

?x <http://www.w3.org/2003/01/geo/wgs84_pos#lat> ?lat .

?x <http://www.w3.org/2003/01/geo/wgs84_pos#long> ?long .

?x <http://data.ordnancesurvey.co.uk/ontology/admingeo/gssCode> ?gss .

?x <http://data.ordnancesurvey.co.uk/ontology/admingeo/hasUnitID> ?unit_id .

?x a <http://data.ordnancesurvey.co.uk/ontology/admingeo/District> .

}

This query selects the various attributes from the data, and the final line of the query makes sure that all of the entities selected from the data are of type District.

Scroll down the page and you should see the query response. To get the values for the district, unitary authorities and metropolitan districts we need to use a SPARQL union to gather together all of the results as follows:

select ?name ?lat ?long ?gss ?unit_id

where

{

{

?x <http://www.w3.org/2000/01/rdf-schema#label> ?name .

?x <http://www.w3.org/2003/01/geo/wgs84_pos#lat> ?lat .

?x <http://www.w3.org/2003/01/geo/wgs84_pos#long> ?long .

?x <http://data.ordnancesurvey.co.uk/ontology/admingeo/gssCode> ?gss .

?x <http://data.ordnancesurvey.co.uk/ontology/admingeo/hasUnitID> ?unit_id .

?x a <http://data.ordnancesurvey.co.uk/ontology/admingeo/District> .

}

UNION

{

?x <http://www.w3.org/2000/01/rdf-schema#label> ?name .

?x <http://www.w3.org/2003/01/geo/wgs84_pos#lat> ?lat .

?x <http://www.w3.org/2003/01/geo/wgs84_pos#long> ?long .

?x <http://data.ordnancesurvey.co.uk/ontology/admingeo/gssCode> ?gss .

?x <http://data.ordnancesurvey.co.uk/ontology/admingeo/hasUnitID> ?unit_id .

?x a <http://data.ordnancesurvey.co.uk/ontology/admingeo/MetropolitanDistrict> .

}

UNION

{

?x <http://www.w3.org/2000/01/rdf-schema#label> ?name .

?x <http://www.w3.org/2003/01/geo/wgs84_pos#lat> ?lat .

?x <http://www.w3.org/2003/01/geo/wgs84_pos#long> ?long .

?x <http://data.ordnancesurvey.co.uk/ontology/admingeo/gssCode> ?gss .

?x <http://data.ordnancesurvey.co.uk/ontology/admingeo/hasUnitID> ?unit_id .

?x a <http://data.ordnancesurvey.co.uk/ontology/admingeo/UnitaryAuthority> .

}

}

order by ?name

The ‘order by’ at the end of the query orders the results in alphabetical order.

To save the query results as a CSV file again make sure that response format in set to CSV and this time, before hitting the query button, make sure the ‘show raw response’ option is selected. Now hit the query button and you should be given the option to save your query result as a CSV file.