Pezholio

Adventures in SPARQL

| Comments

In case you’ve been living under a rock for the past month or so (or actually have a life), then you’ll have noticed that the cabinet office have started to release a big load of open data to a select few developers to have a play.

Much of this data is still in very ‘flat’ formats (such as Excel docs and the like), but some data is being released in RDFa format, the preferred format of the semantic web.

The most interesting of these datasets has been the education one, giving me (and other developers) the ability to get information about schools in an open format. I’ve been playing with this data for a bit, and now (I think) I might have something useful.

The key way of interacting with it is using a language called SPARQL, which is very similar to SQL, but with the web of data in mind. An example query for the education dataset is as follows:

prefix sch-ont: http://education.data.gov.uk/ontology/school# SELECT ?name WHERE { ?school a sch-ont:School; sch-ont:establishmentName ?name; sch-ont:districtAdministrative http://education.data.gov.uk/placeholder-id/administrativeDistrict/Lichfield ; } ORDER BY ?name

This gets the names of all the educational establishments (schools, nurseries etc) in Lichfield District. You can give it a try yourself on the public facing SPARQL endpoint.

What I wanted to do though, was to get all the primary or secondary schools within a 2 mile radius of a postcode. This was a little harder, but perfectly doable.

As well as general details (such as address, name of school etc) and more detailed information (such as capacity, inspection details etc), the dataset also has the easting and northing of the school. Therefore, to get all Primary Schools within a 2 mile radius of a point with an easting of 411021 and a northing of 307291, I needed to do the following query:

prefix sch-ont: http://education.data.gov.uk/ontology/school# SELECT ?name ?address1 ?address2 ?postcode ?town ?easting ?northing ?reference WHERE { ?school a sch-ont:School; sch-ont:establishmentName ?name; sch-ont:easting ?easting ; sch-ont:northing ?northing ; sch-ont:uniqueReferenceNumber ?reference ; sch-ont:phaseOfEducation http://education.data.gov.uk/ontology/school#PhaseOfEducation_Primary . FILTER (?easting 414239 ?easting 411021 ?northing 310509 ?northing 307291) }

This gets the name, easting, northing and reference number of every school within a 2 mile (3201-ish metre) box, with the original easting and northing as the midpoint.

I’ve given it a test here (a bit of naughtiness is required using the Google Maps API to get the latitude and longitude of the postcode, but it’s the only way at the moment!), so please feel free to try it out.

Once you submit the form you’ll get a list of all the primary and secondary schools in that area, as well as a link to more info on the Edubase site.

Postcode

School Type

Primary Secondary

I’d be interested to know your thoughts, so please feel free to comment! :)

There’s also a few more example queries on the Talis Blog, so if you’re that way inclined I heartily recommend having a play.

Comments