Archive

Posts Tagged ‘SPARQL’

Ordnance Survey SPARQL Endpoint

August 7, 2013 Leave a comment

I just wanted to quick mention one feature of the Ordnance Survey linked data SPARQL endpoints that I think it pretty neat.  Go to the SPARQL endpoint and try one of the queries from my last four blogs posts. In this post I’ll got with the following simple query (recall this query gets the name, lat, long, gss code and unit_id for all districts in Great Britain):

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> .

}

You will notice that on hitting the query button that a box will appear that says “Request” and a rather long URL will appear:

SPARQL

You can now use this URL to issue a GET request in PHP, Javascript etc. and use these output within a web application just as you would with any API call. To see this working in a simple way copy the long URL you get from your SPARQL query and at the command line (if running something UNIXy) type:

curl LONG_URL

where LONG_URL is your long URL. You should now see the JSON response from that GET request.

Happy SPARQLing…

Ordnance Survey Linked Data – Combining postcode and spatial queries

August 5, 2013 Leave a comment

In my previous blog posts (here, here and here) I concentrated on a few simple SPARQL queries. However, these SPARQL queries were only performed on one dataset at a time. With linked data things get more interesting when you combine datasets (even if those datasets are both from the same publisher). The original opendata for Code-Point(R) Open was published as a CSV file, and Boundary-Line(TM) was published as a shape file. Both formats are useful, but combining the two datasets can be tricky if you are not sure how to use shape files. The two datasets are, however, implicitly linked via a key. In the linked data versions we have converted both datasets into a common data language (RDF) and made those implicit links explicit (see http://johngoodwin225.wordpress.com/2013/08/05/ordnance-survey-linked-data-a-simple-postcode-query/ ). We can now easily query both datasets together.

To query both datasets go to the SPARQL endpoint for the combined data:

http://data.ordnancesurvey.co.uk/datasets/os-linked-data/explorer/sparql

Recall that the query:

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> .
}

finds all of the postcodes (and their lat/long) in a ward called Bevois. Using the combined datasets we can run queries like: find me all postcodes in the ward Bevois and all the wards touching Bevois. Let’s break this down. To find all postcodes (and their lat/long) in regions that touch Bevois first find all the wards that touch Bevois, and then find all the postcodes within those wards. So the first line in the query below  “?y <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/touches&gt; <http://data.ordnancesurvey.co.uk/id/7000000000017707>&#8221; finds all wards touching Bevois, and the rest of the query matches postcodes in the wards “?y”:

select ?postcode ?lat ?long
where
{
  ?y <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/touches>
  <http://data.ordnancesurvey.co.uk/id/7000000000017707> .
  ?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> ?y .
}

To find all the postcodes in Bevois and the wards touching Bevois we simply union the two queries above together:

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> .
}

UNION

{
  ?y <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/touches>
  <http://data.ordnancesurvey.co.uk/id/7000000000017707> .
  ?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> ?y .
}}

As an exercise for the reader – find all postcodes in Southampton and districts touching Southampton. Happy SPARQLing…

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.

Mash-ups are so last year…

June 14, 2009 3 comments

Mash-ups are cool – ever since Ordnance Survey, Google, Yahoo! and Microsoft launched there various mapping APIs we’ve seen quite a few of them. This weekend I’ve been experimenting with creating a map mesh-up. I’m not sure if there is any strict definition of a mesh-up, but Kinglsey Idehen gave a pretty good account of mesh-up versus mash-up in this blog entry. I’ll leave it up to you the reader to decide if what I have done is truly a mesh-up, but I like to think I did the best I could given the current semantic web infrastructure.

Given my day job I thought it would be cool to do some kind of map mesh-up around regions in the UK (however being a typical researcher I’ve only done four locations so far just to prove the concept). The new version of Ordnance Survey’s mapping API (OS OpenSpace) provides easy API calls to let you display the boundaries of administrative regions in Great Britain (except for civil parishes and communities). This made OS OpenSpace a no brainer for this mesh-up (and of course the superior cartography is an added bonus :)). In order to process the RDF I used the ARC PHP library.

I’ll now explain how I did each of the various mesh-ups starting with the most straightforward one – the basic map with region information (e.g Southampton). This basic map mesh-up was made using the Ordnance Survey RDF for administrative units in Great Britain. This is hosted as linked data on the rkbexplorer site and has a SPARQL endpoint. This RDF data contains topological relations and name information for the administrative regions in Great Britain. For example, take a look at Southampton. For a given region the ARC library was used to issue a SPARQL query to find the bordering regions, contained regions and containing regions along with the area of the region. The result of these queries was then displayed in the map information pop-out. So to find the bordering regions for Southampton the query is very straightforward:

SELECT ?border
WHERE
{
<http://os.rkbexplorer.com/id/osr7000000000037256&gt; admingeo:borders ?border .
}

The family tree mesh-up was done in a similar way. I documented in a previous blog entry how I had started converting my family tree into RDF. In fact since my last blog entry I now have that data available as linked data (this was done using Paget, for example: http://www.johngoodwin.me.uk/family/I0002). The data was stored on the Talis Platform and again ARC was used to do a SPARQL query. You may notice for the Birmingham family tree map I list members of my family that were born in Birmingham and died in Birmingham. I also list relatives that were born in areas bordering Birmingham. I was able to do this because my family tree data was connected to the Ordnance Survey boundaries RDF. So from the OS data I could find all areas bordering Birmingham, and then return all family members born in these areas from my family tree data. Because the data was linked over the web is was easy to do this in a very simple SPARQL query:

SELECT ?s ?name
WHERE
{

?place admingeo:borders <http://os.rkbexplorer.com/id/osr7000000000000018&gt;.

?s dbpedia-owl:birthplace ?place .

?s foaf:name ?name
}

The BBC mesh-ups are arguably more interesting. The BBC recently announced a SPARQL endpoint for its RDF data. An example of the queries you can do are given here. The observant amongst you will notice that the BBC data does provide location information, but the URIs for the location are currently taken from DBpedia and not from the Ordnance Survey data. To get round this I used a new service called sameas.org. The sameas.org service offers a service that helps you to find co-references between different data sets. You can use this to look up other sources that represent your chosen URI. For example http://os.rkbexplorer.com/id/osr7000000000037256 has the equivalent URIs given here.

However, I didn’t want to hard code the equivalent URIs in my code. I’ll explain what I did using the Southampton example. First I issued a call to sameas.org to look up coreferences for the Ordnance Survey Southampton URI. I returned the URIs as an RDF file and used the ARC library to parse the RDF file for equivalent resources from dbpedia. I then issued a SPARQL query using the dbpedia URIs to return the artist/programme information from the BBC SPARQL endpoint.  So in a nutshell:

  1. take Ordnance Survey URI
  2. issue a look-up for that URI to sameas.org
  3. return URIs in an RDF file
  4. parse the RDF file using ARC for dbpedia URIs
  5. issue query to BBC endpoint using the dbpedia URIs.

The revyu mesh-up was done in a similar way.

I hope this all made sense. Comments and questions welcome – though please no comments on my HTML/web design being very 1995. It’s all about the RDF for me  :)

The mesh-up is here http://www.johngoodwin.me.uk/boundaries/meshup.html

Reblog this post [with Zemanta]
Follow

Get every new post delivered to your Inbox.

Join 2,189 other followers