Example sparql queries

[Tony Hirst]

Hi

Are there any example queries on the finance or analytics datastores that serve as "this is the sort of thing the datastore usefully contains" examples, rather than "select ?a ?b c? WHERE { ?a ?b ?c} LIMIT 10 #what do you mean that's not useful?"

So for example, on finance, can i see what the budget was for a government department in a given year? OR is that not what;s in the datastore at the moment?

For analytics, can I get a report on site visits that maybe hint at the numbers used in the COI Reporting on Progress/gov websites report earlier this year [ http://coi.gov.uk/aboutcoi.php?page=357 ] or is that not what the analytics datastore is about?

thanks

tony

[Christopher Gutteridge]

Unit4 who make the software used by a number of council finance systems
are making their first steps into linked data. I'm going to try and
encourage them to get places to build a standard pattern then extend it,
rather than one data model per customer...

--
Christopher Gutteridge -- http://id.ecs.soton.ac.uk/person/1248

/ Lead Developer, EPrints Project, http://eprints.org/
/ Web Projects Manager, ECS, University of Southampton, http://www.ecs.soton.ac.uk/
/ Webmaster, Web Science Trust, http://www.webscience.org/

[Dave Reynolds]

Hi Chris,

Actually Unit4 aren't doing one data model per customer. They are moving
to use the payments ontology [1] that we developed with LeGSB (Paul
Davidson), LG Group et al. They are going up the linked data learning
curve pretty fast.

To address Tony's question: for payments data sets using this ontology
the stores will contain an instance (or instances) of
payment:PaymentDataset to represent the dataset itself which is where
the metadata can sit. This "should" include descriptive metadata
(DCterms), void metadata (vocabularies used, example resources) and data
cube structural information (the dimensions of the cube etc [2]).

However, by the "finance" store I assume you mean the finance linked
data endpoint currently on data.gov.uk. That pre-dates all the work on
data cubes, payments, reference time and all that. It is a Scovo dataset
and so does have a scovo:Dataset resource [3]. That resource doesn't
seem to have any useful metadata (unless you count the URI itself :))
but it points to observations it contains which you can then follow.
E.g.

DESCRIBE
<http://finance.data.gov.uk/statistics/public-expenditure-statistical-analyses/2009#public-expenditure-on-services-by-function-in-real-terms-1987-88-to-2008-09-V8>

>From that you can see the pattern for observations and start to slice by
e.g. time:

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX scovo: <http://purl.org/NET/scovo#>
PREFIX dim: <http://finance.data.gov.uk/statistics/dimension#>
DESCRIBE ?o WHERE { ?o scovo:dimension dim:financial-year-2007-08 . }

Dave

[1] http://reference.data.gov.uk/def/payment documentation for that will
be up somewhere public shortly.

[2]
http://publishing-statistical-data.googlecode.com/svn/trunk/specs/src/main/html/cube.html

[3]
http://finance.data.gov.uk/statistics/public-expenditure-statistical-analyses/2009#public-expenditure-on-services-by-function-in-real-terms-1987-88-to-2008-09

[Kingsley Idehen]

Extending these examples using the LOD Cloud Cache [1] instance we maintain:

1. http://bit.ly/bmCeN8 -- iSPARQL Query By Example Tool for SPARQL
2. http://bit.ly/bIqTgM -- Descriptor Page that also provides start point for Follow-Your-Nose exploration and Faceted Browsing .

Links:

1. http://lod.openlinksw.com -- LOD Cloud Cache Home Page (Text Search, Lookup by Label, and Lookup by URI options)
2. http://lod.openlinksw.com/sparql -- SPARQL endpoint
3. http://lod.openlinksw.com/isparql -- iSPARQL Query Builder endpoint .

-- 

Regards,

Kingsley Idehen	      
President & CEO 
OpenLink Software     
Web: http://www.openlinksw.com
Weblog: http://www.openlinksw.com/blog/~kidehen
Twitter/Identi.ca: kidehen 

[Tony Hirst]

For some reason, I really find it hard to communicate anything of what I want to on this list...;-)

So I'll try again...

I *know* (via http://data.gov.uk/sparql  )  that as well as the transport and education datastores, there are ones for analytics, finance and environment.

Telling me I can explore the datastore by doing SELECT ?a ?b ?c WHERE { ?a ?b ?c } LIMIT 10 (or whatever) tells me nothing.

I can start to get some sort of understanding about the education datastore if you tell me:

1) it contains data about schools, that i can look up using identifiers for schools, or identifiers for the regions schools belong to, for example;

2) If i was looking for data about schools, I could look up useful things like the name of a school, its address, the number of pupils in the school, the date the school opened, as well as other things;

3) one or two really simple examples of what i might do with the schools database, such as look up the names of schools in a particular local authority area using a query looking like:

PREFIX sch-ont: <http://education.data.gov.uk/def/school/>

SELECT ?name WHERE {

?school a sch-ont:School;

sch-ont:establishmentName ?name;

sch-ont:districtAdministrative <http://statistics.data.gov.uk/id/local-authority-district/00MG> .

}

So: can anyone give me a clue/a hint/a simple way in to what's in the analytics, environment and finance datastores using the 1, 2, 3 approach above, with one or more example queries that would generate a simple output dataset that someone who didn't care at all about Linked Data might think was useful, and would help them understand, if only partially, something of what the datastore contained?

Please...?:-)

thanks...

tony

[Christopher Gutteridge]

Hi, Tony.

I wrote a SPARQL explorer for this purpose -- I often have the same problem.

If you give it an endpoint and no URI, it'll start by listing all the
available classes -- DISTINCT ?b WHERE { ?a rdf:type ?b }

Once you start clicking around, for a URI it'll (try and) show all
triples which mention that URI as also attempt to get the rdf:type and a
label (foaf:name, rdfs:label, dc:title etc.) for all the URIs it's
showing you, to make them more readable.

I've found it very helpful to just wonder around clicking on stuff to
see inside endpoints.

http://graphite.ecs.soton.ac.uk/sparqlbrowser/?endpoint=http%3A%2F%2Fservices.data.gov.uk%2Ffinance%2Fsparql&uri=

(I'm open to feature suggestions)

>> data endpoint currently on data.gov.uk <http://data.gov.uk>. That pre-dates all the work on

> Weblog: http://www.openlinksw.com/blog/~kidehen <http://www.openlinksw.com/blog/%7Ekidehen>
> Twitter/Identi.ca: kidehen

[Bill Roberts]

Tony

You make an extremely important point, that with a fairly modest amount of extra information the linked data at data.gov.uk could be made a lot more useful. Although it doesn't help you right now, I can assure you that the folks behind data.gov.uk are certainly aware that this is an issue.

Together with others in the 'linked data kernel project' of data.gov.uk I'm trying to improve this situation by providing the kind of 1/2/3 information you describe, though I can't give you any kind of definite timeline as to when the data.gov.uk site will be enhanced in this way.

As for your specific question about the analytics, environment and finance datastores, I'll come back tomorrow with a few hopefully useful suggestions.

Regards

Bill

[Kingsley Idehen]

On 11/7/10 2:57 PM, Tony Hirst wrote:

For some reason, I really find it hard to communicate anything of what I want to on this list...;-)

So I'll try again...

I *know* (via http://data.gov.uk/sparql  )  that as well as the transport and education datastores, there are ones for analytics, finance and environment.

Telling me I can explore the datastore by doing SELECT ?a ?b ?c WHERE { ?a ?b ?c } LIMIT 10 (or whatever) tells me nothing.

Naturally.

I can start to get some sort of understanding about the education datastore if you tell me:

1) it contains data about schools, that i can look up using identifiers for schools, or identifiers for the regions schools belong to, for example;

2) If i was looking for data about schools, I could look up useful things like the name of a school, its address, the number of pupils in the school, the date the school opened, as well as other things;

You can start by text pattern queries of a school, region, or anything thing else that has a label. This you can experience via the LOD Cloud cache [1] instance that we maintain. Remember, we've loaded the recently published RDF dumps.

3) one or two really simple examples of what i might do with the schools database, such as look up the names of schools in a particular local authority area using a query looking like:

PREFIX sch-ont: <http://education.data.gov.uk/def/school/>

SELECT ?name WHERE {

?school a sch-ont:School;

sch-ont:establishmentName ?name;

sch-ont:districtAdministrative <http://statistics.data.gov.uk/id/local-authority-district/00MG> .

}

So: can anyone give me a clue/a hint/a simple way in to what's in the analytics, environment and finance datastores using the 1, 2, 3 approach above, with one or more example queries that would generate a simple output dataset that someone who didn't care at all about Linked Data might think was useful, and would help them understand, if only partially, something of what the datastore contained?

I think your particular killer demo is ultimately going to be entering the following: =importData("<SPARQL-Protocol-URL"). into the cell of a  Google Spreadsheet. That has always been an option for you to exploit :-)

Some links from old post re. data.gov.uk:

1. http://bit.ly/bmCeN8 -- iSPARQL Query By Example Tool for SPARQL
2. http://bit.ly/bIqTgM -- Descriptor Page that also provides start point for Follow-Your-Nose exploration and Faceted Browsing .

Links:

1. http://lod.openlinksw.com -- Start by entering a text pattern, entity label, or entity URI (the tabs have obvious names)
2. http://lod.openlinksw.com/ispaql -- Query By Example Tool
3. http://lod.openlinksw.com/sparql -- Basic SPARQL endpoint .


Kingsley

[Leigh Dodds]

Hi Tony,

On 7 November 2010 19:57, Tony Hirst <tony....@gmail.com> wrote:
> So: can anyone give me a clue/a hint/a simple way in to what's in the
> analytics, environment and finance datastores using the 1, 2, 3 approach
> above, with one or more example queries that would generate a simple output
> dataset that someone who didn't care at all about Linked Data might think
> was useful, and would help them understand, if only partially, something of
> what the datastore contained?

Hi, not quite the 1/2/3 step, but here's my understanding of what's in
those stores. As has been noted before, the data in there doesn't
follow all of the best practices and is, I believe, so early
experiments with generating Linked Data from some government
statistics.

To my knowledge those stores haven't been updated since the data was
first loaded in Sept/October 2009. I also wasn't involved in the
conversion, so this is just my understanding from doing some digging.

Environment:

This is a conversion of the June 2008 DEFRA survey on land use and
livestock, see:

http://www.defra.gov.uk/evidence/statistics/foodfarm/landuselivestock/junesurvey/index.htm

http://www.defra.gov.uk/evidence/statistics/foodfarm/landuselivestock/junesurvey/documents/RegCountUA_08.xls

Finance:

This is some of the PESA statistical data on public sector funding, see:

http://www.hm-treasury.gov.uk/pespub_pesa10_natstats.htm

Analytics

I'm least sure about this one, I just know its web site
usage/referral data from Directgov.

Clearly all this information ought to be on data.gov.uk itself, but
its worth noting that there's stub wiki pages for all of these where
people can help contribute:

http://data.gov.uk/wiki/Finance_(endpoint)
http://data.gov.uk/wiki/Analytics_(endpoint)
http://data.gov.uk/wiki/Environment_(endpoint)

Cheers,

L.

--
Leigh Dodds
Programme Manager, Talis Platform
Talis
leigh...@talis.com
http://www.talis.com

[Chris Wallace]

Hi Tony

I hesitate to mention a prototype I was working on a while back which
I think attempts to answer your question. In essence it automates the
generic exploratory queries on a dataset and builds a inducted model,
which is then browsable.

http://www.cems.uwe.ac.uk/xmlwiki/Stromness/induct6.xq

A number of datasets have been inducted into the application,
including the datasets you are looking at.

I have to admit the interface is pretty confusing even to me, but the
need for this kind of tool seems to crop up time and again so perhaps
I should spend a bit mor time working on this tool.. I would love to
get it to the stage where SPARQL queries could be generated
interactively from the ER Diagram.

Chris Wallace

On Nov 7, 10:03 pm, Kingsley Idehen <kide...@openlinksw.com> wrote:
> On 11/7/10 2:57 PM, Tony Hirst wrote:
>
> > For some reason, I really find it hard to communicate anything of what
> > I want to on this list...;-)
>
> > So I'll try again...
>

> > I *know* (viahttp://data.gov.uk/sparql )  that as well as the

> 1.http://bit.ly/bmCeN8-- iSPARQL Query By Example Tool for SPARQL
> 2.http://bit.ly/bIqTgM-- Descriptor Page that also provides start

> point for Follow-Your-Nose exploration and Faceted Browsing .
>
> Links:
>

> 1.http://lod.openlinksw.com-- Start by entering a text pattern, entity

> label, or entity URI (the tabs have obvious names)
> 2.http://lod.openlinksw.com/ispaql-- Query By Example Tool

> 3.http://lod.openlinksw.com/sparql-- Basic SPARQL endpoint .

>
> Kingsley
>
>
>
>
>
> > Please...?:-)
>
> > thanks...
> > tony
>
> > On Wed, Nov 3, 2010 at 4:15 PM, Kingsley Idehen
> > <kide...@openlinksw.com <mailto:kide...@openlinksw.com>> wrote:
>
> >     On 11/3/10 8:01 AM, Dave Reynolds wrote:
> >>     Hi Chris,
>
> >>     Actually Unit4 aren't doing one data model per customer. They are moving
> >>     to use the payments ontology [1] that we developed with LeGSB (Paul
> >>     Davidson), LG Group et al. They are going up the linked data learning
> >>     curve pretty fast.
>
> >>     To address Tony's question: for payments data sets using this ontology
> >>     the stores will contain an instance (or instances) of
> >>     payment:PaymentDataset to represent the dataset itself which is where
> >>     the metadata can sit. This "should" include descriptive metadata
> >>     (DCterms), void metadata (vocabularies used, example resources) and data
> >>     cube structural information (the dimensions of the cube etc [2]).
>
> >>     However, by the "finance" store I assume you mean the finance linked

> >>     data endpoint currently ondata.gov.uk  <http://data.gov.uk>. That pre-dates all the work on

> >>     data cubes, payments, reference time and all that. It is a Scovo dataset
> >>     and so does have a scovo:Dataset resource [3]. That resource doesn't
> >>     seem to have any useful metadata (unless you count the URI itself :))
> >>     but it points to observations it contains which you can then follow.
> >>     E.g.
>
> >>     DESCRIBE

> >>     <http://finance.data.gov.uk/statistics/public-expenditure-statistical-...>

>
> >>     > From that you can see the pattern for observations and start to slice by
> >>     e.g. time:
>
> >>     PREFIX rdf:<http://www.w3.org/1999/02/22-rdf-syntax-ns#>
> >>     PREFIX rdfs:<http://www.w3.org/2000/01/rdf-schema#>
> >>     PREFIX xsd:<http://www.w3.org/2001/XMLSchema#>
> >>     PREFIX owl:<http://www.w3.org/2002/07/owl#>
> >>     PREFIX skos:<http://www.w3.org/2004/02/skos/core#>
> >>     PREFIX scovo:<http://purl.org/NET/scovo#>
> >>     PREFIX dim:<http://finance.data.gov.uk/statistics/dimension#>
> >>     DESCRIBE ?o WHERE { ?o scovo:dimension dim:financial-year-2007-08 . }
>
> >>     Dave
>
> >>     [1]http://reference.data.gov.uk/def/payment documentation for that will
> >>     be up somewhere public shortly.
>
> >>     [2]

> >>    http://publishing-statistical-data.googlecode.com/svn/trunk/specs/src...
>
> >>     [3]
> >>    http://finance.data.gov.uk/statistics/public-expenditure-statistical-...

>
> >>     On Tue, 2010-11-02 at 23:56 +0000, Christopher Gutteridge wrote:
> >>>     Unit4 who make the software used by a number of council finance systems
> >>>     are making their first steps into linked data. I'm going to try and
> >>>     encourage them to get places to build a standard pattern then extend it,
> >>>     rather than one data model per customer...
>
> >>>     Tony Hirst wrote:
> >>>>     Hi
>
> >>>>     Are there any example queries on the finance or analytics datastores
> >>>>     that serve as "this is the sort of thing the datastore usefully
> >>>>     contains" examples, rather than "select ?a ?b c? WHERE { ?a ?b ?c}
> >>>>     LIMIT 10 #what do you mean that's not useful?"
>
> >>>>     So for example, on finance, can i see what the budget was for a
> >>>>     government department in a given year? OR is that not what;s in the
> >>>>     datastore at the moment?
> >>>>     For analytics, can I get a report on site visits that maybe hint at
> >>>>     the numbers used in the COI Reporting on Progress/gov websites report
> >>>>     earlier this year [http://coi.gov.uk/aboutcoi.php?page=357 ] or is
> >>>>     that not what the analytics datastore is about?
>
> >>>>     thanks
> >>>>     tony
>
> >     Extending these examples using the LOD Cloud Cache [1] instance we
> >     maintain:
>

> >     1.http://bit.ly/bmCeN8-- iSPARQL Query By Example Tool for SPARQL
> >     2.http://bit.ly/bIqTgM-- Descriptor Page that also provides

> >     start point for Follow-Your-Nose exploration and Faceted Browsing .
>
> >     Links:
>

> >     1.http://lod.openlinksw.com-- LOD Cloud Cache Home Page (Text

> >     Search, Lookup by Label, and Lookup by URI options)
> >     2.http://lod.openlinksw.com/sparql-- SPARQL endpoint

> >     3.http://lod.openlinksw.com/isparql-- iSPARQL Query Builder

> >     endpoint .
>
> >     --
>
> >     Regards,
>
> >     Kingsley Idehen        

> >     President&  CEO

> >     OpenLink Software
> >     Web:http://www.openlinksw.com

> >     Weblog:http://www.openlinksw.com/blog/~kidehen <http://www.openlinksw.com/blog/%7Ekidehen>

> >     Twitter/Identi.ca: kidehen
>
> --
>
> Regards,
>
> Kingsley Idehen

> President&  CEO

[Dave Reynolds]

On Mon, 2010-11-08 at 09:26 +0000, Leigh Dodds wrote:

> Clearly all this information ought to be on data.gov.uk itself, but
> its worth noting that there's stub wiki pages for all of these where
> people can help contribute:
>
> http://data.gov.uk/wiki/Finance_(endpoint)
> http://data.gov.uk/wiki/Analytics_(endpoint)
> http://data.gov.uk/wiki/Environment_(endpoint)

How you get from the endpoint page to the wiki page?

Several times I've heard mention of the wiki pages for the linked data
data sets but never been able to find them.

Dave

[Leigh Dodds]

Hi

On 8 November 2010 09:36, Dave Reynolds <dave.e....@gmail.com> wrote:
> On Mon, 2010-11-08 at 09:26 +0000, Leigh Dodds wrote:
>
>> Clearly all this information ought to be on data.gov.uk itself, but
>> its worth noting that there's stub wiki pages for all of these where
>> people can help contribute:
>>
>> http://data.gov.uk/wiki/Finance_(endpoint)
>> http://data.gov.uk/wiki/Analytics_(endpoint)
>> http://data.gov.uk/wiki/Environment_(endpoint)
>
> How you get from the endpoint page to the wiki page?

In this case pure chance, one of the them came up when I was googling
for a link! :)

> Several times I've heard mention of the wiki pages for the linked data
> data sets but never been able to find them.

They could definitely be better linked and promoted.

[BillRoberts]

Tony

Leigh's post (http://groups.google.com/group/uk-government-data-
developers/msg/8302f32eefc10245) gave some useful background as to
what's in the stores you were asking about. As I mentioned, these
datasets are crying out for some documentation and getting-started
info, but I thought it might be useful to show how you can explore the
data if you don't have any extra explanation. This shouldn't be a
substitute for documentation - it should be in addition to useful
documentation. But if the dataset is all you've got, you can still do
some 'detective work'.

There are lots of different ways to do this, but the following might
give you some useful ideas. Apologies: this post is quite long.

I'll take the environment data store as an example - this is
essentially an RDF version of this spreadsheet:
http://www.defra.gov.uk/evidence/statistics/foodfarm/landuselivestock/junesurvey/documents/RegCountUA_08.xls

(and for many purposes, simply using the spreadsheet might be the
easiest approach, but the Linked Data version does offer some
additional more powerful possibilities in terms of querying and
combining with other data).

I usually start with seeing what classes have been used to organise
the data. (All of the queries here can be entered in the text box at
http://services.data.gov.uk/environment/sparql)

select distinct ?t where {?s a ?t}

This gives a list of the kinds of things you can expect in the data -
the first thing in the list is http://purl.org/NET/scovo#Item. Copy
and paste this into your browser and you end up at a page that
describes the SCOVO vocabulary. A web search for SCOVO finds this
http://sw.joanneum.at/scovo/schema.html that explains the data model
in more depth.

Also we can see that there are lots of 'dimensions' related to
agriculture, so you start to get a clue what the data is all about.

It's worth knowing how much data we are dealing with:

select count(*) where {?s a <http://purl.org/NET/scovo#Item>}

shows there are 26131 data items in the dataset.

To get an idea of how the items are structured, let's have a look at
one of them: first get an identifier for one of the items

select * where {?s a <http://purl.org/NET/scovo#Item>} limit 1

then look at its properties:

select * where {<http://environment.data.gov.uk/statistics/agriculture-
horticulture-survey/june-2008#livestock-EH53> ?p ?o}

There are 3 SCOVO dimensions - 'year-2008', 'geese' and
'leicestershire-cc-and-rutland'. Also the value, '3320.012' (no, I
don't know how you can have 0.012 of a goose either, but that number
also appears in the original spreadsheet) and a pointer to the dataset
this item belongs to.

We can check which SCOVO datasets exist inside this data store:

select * where {?s a <http://purl.org/NET/scovo#Dataset>}

Turns out there are 6, and we are looking at the livestock dataset for
now. All the data appears to be from 2008, so the interesting
dimensions for us to investigate are the type of livestock and the
location. So we'll put together a query that pulls out some of that
data in a 'tabular' form.

As an aside, there has been some substantial work this year to develop
a better vocabulary than SCOVO for describing statistical data -
called the Cube ontology (after 'data cube'). It is not yet completely
final, but allows much clearer description of the data structures.

What can we find out about geese?

select * where {<http://environment.data.gov.uk/statistics/
dimension#geese> ?p ?o}

Turns out it's a type of poultry - so let's query the numbers for all
different poultry types.

and

select * where {<http://environment.data.gov.uk/statistics/
dimension#leicestershire-cc-and-rutland> ?p ?o}

shows that Leicestershire is a http://environment.data.gov.uk/statistics/dimension#local-authority.
Also we can see that the livestock type and local authority entries
have labels.

So we can query for the numbers of different types of poultry in each
local authority area in the 2008 livestock dataset:

PREFIX scovo: <http://purl.org/NET/scovo#>
PREFIX env: <http://environment.data.gov.uk/statistics/dimension#>

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

select ?region ?birdname ?value where {
?item scovo:dataset <http://environment.data.gov.uk/statistics/
agriculture-horticulture-survey/june-2008#livestock> .
?item scovo:dimension ?la .
?la a env:local-authority .
?la rdfs:label ?region .
?item scovo:dimension ?birdtype .
?birdtype a env:poultry .
?birdtype rdfs:label ?birdname .
?item rdf:value ?value} ORDER BY ?region

The PREFIXes are just abbreviations to make the rest of it more
readable. We restrict the items found to the 2008 livestock dataset,
then we get the local authority label for that item, then restrict the
query to items which relate to types of poultry and look up the label
of the livestock type. Then we put the results in (alphabetical)
order of local authority.


I'm not sure if that's the kind of thing you were looking for, but
hopefully combined with the pointers to background information that
Leigh provided, it may give you an idea on what's in that dataset and
how to go about finding out.

Regards

Bill