Pezholio

Adventures in SPARQL Part 2 - Now With Added KML!

| Comments

It’s been a few weeks now since I posted my first foray into the Edubase dataset, and since then, there’s been a few changes to the dataset, so I thought I’d give it another crack - the end result meaning I might have something exciting for everyone to benefit from!

Since the dataset was published, the most significant thing has been the addition of latitude and longitude, as well as eastings and northings. This means that we can now get location-based data much easier, without a lot of mathematical mucking about with conversions.

To get a list of primary schools, addresses, locations and a latitude and longitude point, I can use the following query on the SPARQL endpoint:

` prefix geo: http://www.w3.org/2003/01/geo/wgs84_pos# prefix sch-ont: http://education.data.gov.uk/def/school/ prefix space: http://data.ordnancesurvey.co.uk/ontology/spatialrelations/ SELECT ?name ?lat ?long ?reference ?address1 ?address2 ?town ?postcode WHERE { ?school a sch-ont:School ; “.$open.” sch-ont:districtAdministrative http://statistics.data.gov.uk/id/local-authority-district/41UD ; sch-ont:phaseOfEducation http://education.data.gov.uk/def/school/PhaseOfEducation_Primary ; sch-ont:establishmentName ?name; 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 .

} } ORDER BY ?name `

This gives me this result, which I can easily (with a bit of code wizardry), turn into a KML file, which can then be used with Google Maps, Google Earth and Bing.

I’ve generated two KML files for Lichfield District, Primary and Secondary, which I’ve used on the (still in development) ‘ubermap’ on the Lichfield District Council website.

This was quite easy to replicate for everyone, so, because I’m a nice fella, I’ve put together a script that generates KML files for primary and secondary schools in any council area in the country. All that I ask is that you save the files to your server if you use them, so you don’t cane my bandwidth!

Just in case you’re interested…

To get the list of councils for the above script, I did another bit of SPARQLing, this time using the Office of National Statistics SPARQL endpoint. The query I used was as follows:

` PREFIX rdfs: http://www.w3.org/2000/01/rdf-schema# SELECT ?authority ?label WHERE {

 ?authority
   a http://statistics.data.gov.uk/def/administrative-geography/LocalAuthority .

?authority rdfs:label ?label ; } ORDER BY ?label `

This returns the authority label, as well as a unique URI.

Comments