Using SPARQL & the data.gov.uk school data

Simon — Wed, 10 Feb 2010 12:48:24 +0000

So i’ve been busy beavering away on the myDistrict project I’m developing for Stratford-on-Avon District Council, and part of this project is to retrieve various datasets from numerous API’s to enable the Stratford residents to get all their local information in one place.

The basics are there, and we’ll be gathering the residents geo information when they register (or upgrade their existing website account) so we are able to query all these fantastic datasets from www.data.gov.uk and more.

A bit stuck at first, I asked the Open Data guru from Lichfield District Council, Stuart Harrison, for some pointers. Stuart was very kind as to email through some sample code and links to blogs which reference the calls needed to access and unleash the data.

I thought I’d share some of this with you all, do with it what you will!

A few sample snippets are below, but you’ll need to replace some parts (especially the local authority district reference eg 44UE) with your own data to get accurate results.

You can find your ‘id’ number by having a look through the data.gov.uk lists here. This is the Warwickshire County starting point, you’ll need to go up a level to country and search that way. It’s a bit fiddly! Once you’re in the right county, you’ll need to look for local authority district. If I find a better way, I’ll update this post!

UPDATE: Stuart Harrison has kindly written a nice service to allow you to search for your council ID (or SNAC ID for the technical amongst you).

So, starting on the UK Government SPARQL form input page, you can start to play with the various options to tailor your results accordingly.

To call back all the schools in your local authority (in this case, Stratford):

prefix sch-ont:

SELECT ?name ?reference WHERE {
?school a sch-ont:School;
sch-ont:establishmentName ?name;
sch-ont:uniqueReferenceNumber ?reference ;
sch-ont:districtAdministrative

.

}

ORDER BY ?name

To filter by primary school you can add an extra couple of lines:

prefix sch-ont:

SELECT ?name ?reference WHERE {
?school a sch-ont:School;
sch-ont:establishmentName ?name;
sch-ont:uniqueReferenceNumber ?reference ;
sch-ont:districtAdministrative

;

sch-ont:uniqueReferenceNumber ?reference ; sch-ont:phaseOfEducation .

}

ORDER BY ?name

And to filter just secondary schools, you can make a small change also:

prefix sch-ont:

SELECT ?name ?reference WHERE {
?school a sch-ont:School;
sch-ont:establishmentName ?name;
sch-ont:uniqueReferenceNumber ?reference ;
sch-ont:districtAdministrative

;

sch-ont:uniqueReferenceNumber ?reference ; sch-ont:phaseOfEducation .

}

ORDER BY ?name

You could also pull back which schools have nurseries attached, very handy!

prefix sch-ont:
prefix xsd:

SELECT ?name WHERE {
?school a sch-ont:School;
sch-ont:establishmentName ?name;
sch-ont:districtAdministrative ;

sch-ont:nurseryProvision “true”^^xsd:boolean ;

}

ORDER BY ?name

Or you could pull back all the nursery schools available across the whole county. Just by tweaking a couple of search parameters:

prefix sch-ont:
prefix xsd:

SELECT ?name WHERE {
?school a sch-ont:School;
sch-ont:establishmentName ?name;
sch-ont:localAuthority ;

sch-ont:nurseryProvision “true”^^xsd:boolean ;

}

ORDER BY ?name

And then, if you want to get really flash, you can return the 5 closest primary schools to your location. In this example, using easting & northing data and adding address data to the mix as well:

prefix sch-ont:
prefix space:

SELECT ?name ?address1 ?address2 ?town ?postcode ?easting ?northing ?reference ?WebsiteAddress ?SCUpreferredemail

WHERE { ?school a sch-ont:School ; sch-ont:districtAdministrative

;
sch-ont:establishmentName ?name;
sch-ont:uniqueReferenceNumber ?reference ;
space:easting ?easting ;
space:northing ?northing ;

sch-ont:uniqueReferenceNumber ?reference ; sch-ont:phaseOfEducation

.

OPTIONAL {

?school sch-ont:address ?address .
?address sch-ont:address1 ?address1 ;
sch-ont:address2 ?address2 ;
sch-ont:town ?town ;
sch-ont:postcode ?postcode .

}

OPTIONAL {
?school sch-ont:WebsiteAddress ?WebsiteAddress ;

}

OPTIONAL {

?school sch-ont:SCUpreferredemail ?SCUpreferredemail ;

}

}

ORDER BY ASC(

((419997 – ?easting)* ( 419997 – ?easting))
+ ((254702 – ?northing)* ( 254702 – ?northing))

)
LIMIT 5
OFFSET 0

And this one, using longitude and latitude data:

prefix geo:

prefix sch-ont:

SELECT ?name ?address1 ?address2 ?town ?postcode ?lat ?long ?reference ?WebsiteAddress ?SCUpreferredemail

WHERE {

?school a sch-ont:School;

sch-ont:establishmentName ?name;

sch-ont:districtAdministrative

;

sch-ont:phaseOfEducation ;

sch-ont:uniqueReferenceNumber ?reference ;

geo:lat ?lat;

geo:long ?long.

OPTIONAL {

?school sch-ont:address ?address .

?address sch-ont:address1 ?address1 ;

sch-ont:address2 ?address2 ;

sch-ont:town ?town ;

sch-ont:postcode ?postcode .

}

OPTIONAL {

?school sch-ont:WebsiteAddress ?WebsiteAddress ;

}

OPTIONAL {

?school sch-ont:SCUpreferredemail ?SCUpreferredemail ;

}

}

ORDER BY ASC(

((52.190200973202 – ?lat)* ( 52.190200973202 – ?lat))

+ ((-1.7088976710252 – ?long)* ( -1.7088976710252 – ?long))

)

LIMIT 5

OFFSET 0

This is really just touching the surface, I’ve got oodles of examples, far too many to post here.

If you have any questions, feel free to email me and I can help out where possible!

Pluck U

Simon — Fri, 01 Jan 2010 19:52:31 +0000

Just having a quick flick through iPhoto at our shots from New York back in February 2009, and I stumbled across a capture I took of a fab looking take-away on the streets of Manhattan.

Obviously, the owners have a decent sense of humour.

Procrastination

Simon — Wed, 30 Dec 2009 00:10:22 +0000

WiFi problems when Upgrading iPhone

Simon — Tue, 29 Dec 2009 16:15:04 +0000

So, I took the plunge and upgraded my iPhone 3G to an iPhone 3GS in sparkly white with O2, and so far, so good.

I backed up and resynced the new device and everything worked as normal within an hour or so.

The only thing it appeared to lose was all my saved WiFi connections, no problem for the odd couple I use at home, work and at friends, but when it came to using either The Cloud or BT Openzone’s free WiFi, it kept asking me to register.

So, entering your phone number and wham… error message!

It turns out, that if your change or upgrade your iPhone, you’ll also need to call O2 to ask them to reset your MAC address associated to your account.

I called and within 60 minutes my WiFi should be re-enabled. Though they gave me a handy tip….

Save yourself waiting in a queue, if you are with O2, and experience the same issue I did, then simply send the word WIFI as an SMS to 2020 and your MAC address will be reset.

As a Russian meerkat would say… “Simples”

New iPhone App underway

Simon — Tue, 29 Dec 2009 15:13:26 +0000

So I’ve been busy for the last month or so on a fantastic new project for the iPhone and iPod Touch.

This will me my maiden voyage into the dark depths of the App Store, and I’m sure I have lots to look forward to when it finally gets submitted to iTunes Connect in the coming weeks. Let’s just hope I don’t fall foul of some ludicrous rejection for the wrong sized icon or something equally daft.

The base coding and layouts are complete, just waiting on a few bits and pieces before I can submit the App.

Installed Pinch Media’s stats, to get a good idea of App usage. Seems to be working well so far.

I’ll post the full details once I’m allowed to, I hope you’ll buy it!

Starting from scratch

Simon — Tue, 29 Dec 2009 14:45:11 +0000

So, I decided it was about time that I starting putting together my blog properly.

I set this up over a year ago, and so far it’s consisted of my tweets. But as most of those are useless bits of information on my day-to-life, I decided to bin them all off, stick with a tiny Twitter feed and just post up useful blog stuff.