Getting Started with data.gov.uk, Triplr SPARYQL and Yahoo Pipes

RDF, SPARQL and the semantic web are too scarey for mortals, right? So here’s a hopefully easier way in for those of us who are put off by the syntactic nightmare that defines the world of formal Linked Data: Yahoo Pipes :-)

A few weeks ago, I was fortunate enough to get one of the developer preview keys for data.gov.uk, the UK answer to Tim Berners Lee’s call to open up public data.

The UK data.gov.uk solution is currently a hybrid – a growing set of separate Linked Data stores (hosted on the Talis Platform, I think?) covering areas such as education, finance and transport; and a set of links to CSV and Excel spreadsheets available for download on a wide variety of Government department websites. (Some of them have nice URLs, some don’t; if you think they should, how does this sound? Designing URI Sets for the UK Public Sector: Machine- and human-readable formats. Most of the data is public too – it’s just the meta-sit – data.gov.uk – that I think is under wraps at the moment?)

I’ve played with online CSV and Excel spreadsheets before (e.g. in the context of the Guardian Datastore), but I’ve always found SPARQL endpoints and RDF a little bit, err, terrifying, so last week I felt it was time to bite the bullet and spend an hour or two trying to do something – anything – with some hardcore Linked Data. Or at least, try to just do something – anything – with some data out of a single data store.

So where to start? Regular readers will know that I try to use free online apps and client side Javascript code wherever possible (I don’t want to have to assume the availability of access to my own web server), so it made sense to look to at Yahoo Pipes :-)

I’ve done the odd demo of how to use SPARQL in a Yahoo Pipe before (Last Week’s Football Reports from the Guardian Content Store API (with a little dash of SPARQL), which is not about the football, right?) but a tweet last week tipped me off to a potentially more abstracted way of writing SPARQL queries in a Pipes environment: Triplr’s SPARQL + YQL = SPARYQL.

Ooh…. the idea is that you can wrap a SPARQL query in a YQL query, which in turn suggests two other things…

Firstly, I now have a way I’m already familiar with of generating and debugging bookmarkable RESTful queries to SPARQL endpoints:

Secondly, the Yahoo Pipes YQL block provides a handy container for making the SPARYQL queries and pulling the results back into a pipes environment.

Here’s the pipework… (based on an original pipe by @hapdaniel)

SPARQL + YQL= SPARYQL pipe http://pipes.yahoo.com/ouseful/sparyql

So what can we do with it? Not being particularly fluent in SPARQL, I had a poke around for some examples I could cut, paste, hack and tinker with and found a few nice examples on the [n]^2 blog: SPARQLing data.gov.uk: Edubase Data

So here’s a quick demo – a pipe runs a query that looks for the 10 schools with the latest opening dates on data.gov.uk’s education datastore:

SELECT ?school ?name ?date ?easting ?northing WHERE {?school a sch-ont:School; sch-ont:establishmentName ?name; sch-ont:openDate ?date ; sch-ont:easting ?easting; sch-ont:northing ?northing . } ORDER BY DESC(?date) LIMIT 10

In order to plot the schools on a map, it’s necessary to convert northings and easting to latitude and longitude. A cry for help on twitter was quickly responded to by @kitwallace who gave me a link to a service that did just the job… almost – for some reason, Pipes didn’t like the output, so I had to run the query through a YQL proxy:

(Note that Kit soon came up with a fix, so I could actually just call the service directly via a Data Source block using a call of the form http://www.cems.uwe.ac.uk/xmlwiki/geo/OS2latlong2.xq?easting=527085&northing=185400.)

Here’s the result:

Note that the KML output from the pipe can be plotted directly in a Google map (simply paste the KML URL into Google Map search box and hit return.)

By writing two or three different queries, and pulling the data separately into a web page via the JSON feed, we can easily create a map that displays the schools that have opened and closed between 1/1/08 and 1/10/09:

If we take the CSV output of the pipe, we can also see how it’s possible to transport the content into a Google spreadsheet (once again thanks to @hapdaniel for pointing out that changing the output switch of a pipe’s RSS feed from rss to csv does the format conversion job nicely):

which gives:

(Note that the CSV import seems to require quite a flat data structure (though it is trying really hard with the more hierarchical data – it’s just not quite managing to catch the data values?), so some renaming within the pipe might be required to make sure that the child attributes of each feed item do not have any children of their own. Empty attributes also need pruning.)

PS I did try importing the XML output from a RESTful YQL query into a Google spreadsheet with an =importXML formula but it didn’t seem to work. Firstly, the RESTful URI was too long (easily solved by rewriting it as a shortenedURI). Secondly, the Google spreadsheet didn’t seem to like output XML :-(

So near, yet so far… but still, it poses the question: could we write containerised queries/topic specific APIs over data.gov.uk SPARQL endpoints that expose the results in a spreadsheet capable of importing XML?

Author: Tony Hirst

I'm a Senior Lecturer at The Open University, with an interest in #opendata policy and practice, as well as general web tinkering...

8 thoughts on “Getting Started with data.gov.uk, Triplr SPARYQL and Yahoo Pipes”

  1. Note corrections (at http://www.cems.uwe.ac.uk/xmlwiki/Stromness/edqueries.txt via @kitwallace from @iand)

    #6. Select the name, easting and northing for the 100 newest schools in the UK.
    # Can be used to plot them on a map

    PREFIX osr:
    prefix sch-ont:
    SELECT ?school ?name ?date ?easting ?northing WHERE {
    ?school a sch-ont:School;
    sch-ont:establishmentName ?name;
    sch-ont:openDate ?date ;
    osr:easting ?easting ;
    osr:northing ?northing .
    }
    ORDER BY DESC(?date)
    LIMIT 100

    #6A Select the name, latitude and longitude for the 100 newest schools in the UK.
    # Can be used to plot them on a map

    PREFIX geo:
    prefix sch-ont:
    SELECT ?school ?name ?date ?lat ?long WHERE {
    ?school a sch-ont:School;
    sch-ont:establishmentName ?name;
    sch-ont:openDate ?date ;
    geo:lat ?lat ;
    geo:long ?long .
    }
    ORDER BY DESC(?date)
    LIMIT 100

    #7. Select the uri, name, easting and northing for all schools opened in 2008

    PREFIX osr:
    prefix sch-ont:
    prefix xsd:
    SELECT ?school ?name ?date ?easting ?northing WHERE {
    ?school a sch-ont:School;
    sch-ont:establishmentName ?name;
    sch-ont:openDate ?date ;
    osr:easting ?easting ;
    osr:northing ?northing .
    FILTER (?date > “2008-01-01″^^xsd:date && ?date < "2009-01-01"^^xsd:date)
    }

  2. Here is another meshup based on UK Edubase Data. Note that this meshup exposes distinct URLs for Query Results [1] and Query Definition [2].

    Links:

    1. http://bit.ly/bHJMkg — Query Results Page (Note Query Tab for SPARQL behind the page)
    2. http://bit.ly/djah4E — Query Definition Page link that put you in a SPARQL Query By Example UI
    3. http://bit.ly/cuQ8Lp — Things of Interest within 2km radius of Trafalgar Square
    4. http://delicious.com/kidehen/meshup — many other SPARQL driven Linked Data meshups.

    Kingsley

Comments are closed.