Ordnance Survey Linked Data – A Simple Postcode Query

August 5, 2013 1 comment

In the previous two blog posts (here and here) I gave some simple examples of queries you could run on the Ordnance Survey Boundary-Line(TM) linked data. Here I want to give some simple examples of what you can do with the Code-Point(R) Open linked data. The Code-Point(R) Open linked data has a URI for every Postcode Unit, Postcode Sector, Postcode District and Postcode Area in England, Scotland and Wales.  Each Postcode Unit is nested within a Postcode Sector, each Postcode Sector is nested within Postcode District and each Postcode District is nested within a Postcode Area. The reciprocal contains relationships are also included.

A Postcode Unit URI takes the form:

http://data.ordnancesurvey.co.uk/id/postcodeunit/SO160AS

A Postcode SectorURI takes the form:

http://data.ordnancesurvey.co.uk/id/postcodesector/SO160

A Postcode District URI takes the form:

http://data.ordnancesurvey.co.uk/id/postcodedistrict/SO16

A Postcode Area URI takes the form:

http://data.ordnancesurvey.co.uk/id/postcodearea/SO

Let us now try some SPARQL. Go to the CodePoint-Open SPARQL endpoint, and for simplicity select the response format to CSV. Supposed we wanted to select all of the postcodes and their lat/long coordinate for postcodes within the SO postcode area. This can be done simply as follows:

select ?postcode ?lat ?long
where
{
  ?x <http://www.w3.org/2000/01/rdf-schema#label> ?postcode .
  ?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/spatialrelations/within>
     <http://data.ordnancesurvey.co.uk/id/postcodearea/SO> .
}

Notice that I do not need to specify that ‘x’ is a postcode unit in this case as only postcode units have a lat/long value.

Let us try a slightly more complicated query – say I wanted all the postcodes, and their lat/long, within the postcode sectors SO16 and SO17. I would do a query similar to the above, but with a UNION to collect together results for each postcode sector:

select ?postcode ?lat ?long
where
{
{
  ?x <http://www.w3.org/2000/01/rdf-schema#label> ?postcode .
  ?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/spatialrelations/within>
     <http://data.ordnancesurvey.co.uk/id/postcodedistrict/SO16> .
}
UNION
{
  ?x <http://www.w3.org/2000/01/rdf-schema#label> ?postcode .
  ?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/spatialrelations/within>
     <http://data.ordnancesurvey.co.uk/id/postcodedistrict/SO17> .
}
}

So far so easy…

The Code-Point Open linked data also contains links to the administrative areas that ‘contain’ the postcode. A word of caution is needed here. Postcodes do not respective administrative boundaries so containment in this case actually means the administrative region that the lat/long for that postcode lies within. There are three predicates for relating postcode units to administrative areas. These are:

  • ward – this relates postcode units to wards and unitary electoral divisions
  • district – this relates postcode units to districts, metropolitan districts, London boroughs and unitary authorities
  • county – this relates postcode units to counties (where applicable)

So say I want a list of all the postcode units (and their lat/longs) that lie within the ward of Bevois. This is a straightforward query. First you can find the URI for Bevois here. You’ll find the URI for Bevois is:

http://data.ordnancesurvey.co.uk/id/7000000000017707

Now enter the following SPARQL query to find all the postcodes in Bevois:

select ?postcode ?lat ?long
where
{
  ?x <http://www.w3.org/2000/01/rdf-schema#label> ?postcode .
  ?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/postcode/ward>
     <http://data.ordnancesurvey.co.uk/id/7000000000017707> .
}

I’ll leave it as an exercise for the reader to find all of the postcode units (and their lat/long) in Southampton.

What if I want to find out the postcode districts that are covered by the ward Bevois? First I find all the postcode units in Bevois, and then I do a query to look up all the postcode districts those postcode units are within as follows:

select distinct ?postcodedistrict
where
{
  ?x <http://data.ordnancesurvey.co.uk/ontology/postcode/ward>
     <http://data.ordnancesurvey.co.uk/id/7000000000017707> .
  ?x <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/within> ?y .
  ?y a <http://data.ordnancesurvey.co.uk/ontology/postcode/PostcodeDistrict> .
  ?y <http://www.w3.org/2000/01/rdf-schema#label> ?postcodedistrict .
}

You will now see all the postcode districts the ward Bevois covers. As a final exercise to the reader perform the same query, but find postcode sectors that cover Southampton. Happy SPARQLing.

Ordnance Survey Linked Data – A Simple Spatial Query

August 2, 2013 3 comments

In this blog I thought I would  give an example of some very simple spatial queries using the Ordnance Survey Linked Data. When we first created the Ordnance Survey linked data not many RDF triplestores had spatial indexes, or in other words there was no easy way to say ‘find me all the Parishes in Hampshire‘ using a query based on the geometries of these regions. This functionality is fairly standard in GIS systems and a number of spatially enabled relational databases, and is now being increasingly implemented in RDF triplestores and other NoSQL technologies. To get round this issue it was decided that it would be very useful to precompute various topological relationships between the administrative areas described in the Boundary-Line(TM) linked data. What you will see in the data are explicit spatial relationships like touches, within and contains that relate the different administrative regions. Now the administrative geography of this country is complicated, and I’m no geographer so a complete description of it will be left for a later blog post. For now I will say that Boundary-Line contains different geographies based on national voting and some on local authorities. The spatial relationships are only includedwhere relevant – for example you won’t find explicit spatial relationships between Westminster Constituencies and Counties, but you will find them between Counties and Districts.

In the Ordnance Survey linked data you will find three types of spatial relationship: touches, within and contains:

  • touches means that two regions share a point on their boundary, but share no common points on their interior. They are adjacent/bordering. Touches relationships are typically only recorded between regions of the same type, i.e. which parish touches which parish. You won’t find a list of parishes touching counties. However, at some levels it gets a bit more complicated due to single tier local authorities (unitary authorities) and those based on a double tier (county/district). Counties and unitary authorities tessellate the country at some level, as do districts and unitary authorities.
  • contains and within are fairly self explanatory I hope.  Contains and within relationships are only stated between regions in the same geography and only explicitly stated between entities that directly contain/are within each other. What does this last part mean? In the local authorities geography counties contain districts and districts, in turn, contain parishes. You will only find explicit ‘contains’ statements between counties and districts, and between districts and parishes – you won’t find them between counties and parishes.

So now for some examples. Supposed I want to find the name of all the regions contained immediately in Hampshire. First you need to find which URI identifies Hampshire. Go to the Boundary-Line search API and search for Hampshire. You should then see that the county of Hampshire has the following URI:

http://data.ordnancesurvey.co.uk/id/7000000000017765

You can now use this in your query. Go to the SPARQL endpoint and enter the following:

select ?x ?name
where
{
  ?x <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/within> <http://data.ordnancesurvey.co.uk/id/7000000000017765> .
  ?x <http://www.w3.org/2000/01/rdf-schema#label> ?name .
}

You will see a list of everything immediately within Hampshire, and these will all be of type district. Supposed you now want to get everything within Hampshire. This can be done easily by adding a ‘+’ at the end of the within predicate as follows:

select ?x ?name
where
{
  ?x <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/within>+ <http://data.ordnancesurvey.co.uk/id/7000000000017765> .
  ?x <http://www.w3.org/2000/01/rdf-schema#label> ?name .
}

You now have a list of everything within Hampshire – this includes districts, wards and parishes. Now suppose you just want the parishes- you can do this by adding an extra line to the query to only match x to things of type civil parish:

select ?x ?name
where
{
  ?x <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/within>+ <http://data.ordnancesurvey.co.uk/id/7000000000017765> .
  ?x <http://www.w3.org/2000/01/rdf-schema#label> ?name .
  ?x a <http://data.ordnancesurvey.co.uk/ontology/admingeo/CivilParish> .
}

Touches works in a similar way. Supposed you want the names of unitary authorities that touch Hampshire issue the following:

select ?x ?name
where
{
  ?x <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/touches> <http://data.ordnancesurvey.co.uk/id/7000000000017765> .
  ?x <http://www.w3.org/2000/01/rdf-schema#label> ?name .
  ?x a <http://data.ordnancesurvey.co.uk/ontology/admingeo/UnitaryAuthority> .
}

Say you want to find parishes that touch Hampshire. This is where it gets complicated and the following is maybe for advanced SPARQL-wizards only. First find all of things that touch Hampshire (this will include other counties, unitary authorities and districts), then find all parishes within those regions and find which of those parishes touch parishes within Hampshire:

select distinct ?y ?name
where
{
  ?x <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/touches> <http://data.ordnancesurvey.co.uk/id/7000000000017765> .
  ?y <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/within> ?x .
  ?y a <http://data.ordnancesurvey.co.uk/ontology/admingeo/CivilParish> .
  ?z <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/within>+ <http://data.ordnancesurvey.co.uk/id/7000000000017765> .
  ?z a <http://data.ordnancesurvey.co.uk/ontology/admingeo/CivilParish> .
  ?z <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/touches> ?y .
  ?y <http://www.w3.org/2000/01/rdf-schema#label> ?name .
}

Congratulations – you now have a list of all the parishes that touch Hampshire.

Hopefully some of these queries are useful – happy SPARQLing.

Ordnance survey Linked Data – Simple SPARQL example

August 1, 2013 Leave a comment

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.

How are you using Ordnance Survey Linked Data?

June 5, 2013 1 comment

I might have mentioned (a few times) that the new look Ordnance Survey linked data site is now live. A question I ask from time to time is:

1) Are you using the data, and if so what for (if you don’t mind saying)?

2) Even if you aren’t actively using the data are you linking to it?

Please comment below if you have anything you’d like to share. Thank you in advance!

New Ordnance Survey Linked Data Site not just for Data Geeks

June 3, 2013 1 comment

Ordnance Survey’s new linked data site went live today. You can read the official press release here. One of the major improvements to the site is the look and feel of the site, and as a result of this the site should be useful to people who don’t care about ‘scary things’ like APIs, linked data or RDF.

One key additional feature of the new site is map views (!) of entities in the data. This means the site could be useful if you want to share your postcode with friends or colleagues as a means of locating your house or place of work. Every postcode in Great Britain has a webpage in the OS linked data of the form:

http://data.ordnancesurvey.co.uk/id/postcodeunit/POSTCODE

Examples of this would be the OS HQ postcode:

http://data.ordnancesurvey.co.uk/id/postcodeunit/SO160AS

or the postcode for the University of Southampton:

http://data.ordnancesurvey.co.uk/id/postcodeunit/SO171BJ

Click on either of these links you’ll see a map of the postcode – which you can view at various levels of zoom. You’ll also see useful information about the postcode such as its lat/long coordinate. More interestingly you’ll notice that it provides information about the ward, district/unitary authority, county (where applicable) and country your postcode is located in. So for the University of Southampton postcode we can see it’s located in the ward Portswood, the district Southampton and the country England.

Another interesting addition to the site is links to a few useful external sites such as: They Work For You, Fix My Street, NHS Choice and Police UK. This hopefully makes the linked data site a useful location based hub to information about what’s going on in your particular postcode area.

Why not give it a try with your postcode…:)

GeoSPARQL and Ordnance Survey Linked Data

April 26, 2013 3 comments

The Ordnance Survey Linked Data contains lots of qualitative spatial information – that is topological relationships between different regions. We have information about what each region contains, is within and touches (e.g. Cambridgeshire touches Norfolk). These relationships were encoded using an Ordnance Survey vocabulary as there was nothing suitable at the time. Since then a new standard has emerged from the OGC called GeoSPARQL. In the long term we would probably like to migrate the OS data over to the GeoSPARQL standard, but to stop third party applications using the data from breaking we decided not to on this release. However, mappings from the OS vocabulary have been made to the GeoSPARQL vocabulary via ‘owl:equivalentProperty’. So each of the spatial relationships now have a link to their equivalent in GeoSPARQL. Please see: contains, within, touches, equals, disjoint and partially overlaps for more details on which properties they are related to in GeoSPARQL.

 

Ordnance Survey Linked Data and the Reconciliation API

April 25, 2013 5 comments

The new Ordnance Survey Linked Data has a reconciliation API that allows users to turn text into URIs by matching against the Ordnance Survey linked data using a tool called open refine.

I’m not an expert on open refine but had a quick try of the tool today using some open data about libraries (available here). Instructions on installing Open Refine can be found here.

To use the Open Refine load your data into the tool and create your new project. On loading the library data into Open Refine you should see something like this:

Image

We can use Open Refine to turn the labels in both the ‘county’ column and postcode column into URIs. For the county column click the down arrow next the column name and select reconcile -> start reconciling. Now click ‘Add Standard Service’ and add the following URL http://data.ordnancesurvey.co.uk/datasets/boundary-line/apis/reconciliation. 

As the ‘county’ column will contain a mixture of types select the ‘reconcile against no particular type’ option and click ‘start reconciling’. You should now see that most of the text labels have turned to hyperlinks (note OS linked data does not included Northern Ireland data…this accounts for the missing values).

You can do the same for the postcode column, but this time use the API at: http://data.ordnancesurvey.co.uk/datasets/code-point-open/apis/reconciliation

Your data should now look something like:

Image

You have now successfully replaced the text in these columns with links to the OS linked data.

Another useful thing to try is a simple bit of geocoding based on postcodes. Again go to the postcode column and select “Edit Column -> Add Column by fetching URLs’. Where asked type in a column name (e.g. PC JSON) and in the Expression box type:

http://data.ordnancesurvey.co.uk/datasets/code-point-open/apis/search?output=json&query=’ + escape(value,’url’)

You should now see a column appear full of JSON results:

Screen Shot 2013-04-25 at 15.23.11

On the PC JSON column select “Edit Column -> Add Column Based on this column”. Again add a column name of your choice. I wanted to extract the value of the easting and northing and add it as a column so I called my new column ‘easting,northing’. In the expression box enter the following to get the value of the easting and northing:

with(value.parseJson(), pair, pair.results[0].easting + ‘,’ + pair.results[0].northing)

and you should now see something like:

Screen Shot 2013-04-25 at 15.27.27

Congratulations…you have now geo-coded your libary spreadsheet via a postcode and the OS linked data.

For more info on how to use Open Refine for reconciliation watch this youtube video.