Dataset 10010

From Data-gov Wiki

Jump to: navigation, search
Metadata
RDF data
Links
  • Demos:
NOTES: This dataset covers data with following features: state,population,county,zipcode.
Infobox (User Generated Dataset) edit with form
  • name: us-agency


Contents

How to link data.gov datasets by agency

The first three queries work, but when the fourth query combines them, no results are returned. The first and third queries gather some data, while the second performs a join based on a pairing of strings. The filter( ?name_402 = "Department of Labor" ) filters are there to provide some example values and to limit the results while debugging. Q&A about SPARQL

Query 1. locate agency in Dataset 402 (Public Budget Database - Outlays and offsetting receipts 1962-Current, Executive Office of the President)

The following query works against http://data-gov.tw.rpi.edu/joseki/sparql-tdb-datagov.html:

execute query

prefix xsd:    <http://www.w3.org/2001/XMLSchema#>
prefix dc:     <http://purl.org/dc/terms/> 
prefix foaf:   <http://xmlns.com/foaf/0.1/>
prefix dgp:    <http://data-gov.tw.rpi.edu/vocab/p/10029/>
prefix dgp401: <http://data-gov.tw.rpi.edu/vocab/p/401/>

select *
where {
  graph <http://data-gov.tw.rpi.edu/vocab/Dataset_402> {
                                                  filter( ?name_402 = "Department of Labor" )
         ?entry dgp401:bureau_name ?name_402;     
                dgp401:num2008     ?fy_2008;
                dgp401:num2007     ?fy_2007;
                dgp401:num2006     ?fy_2006;
                dgp401:num2005     ?fy_2005;
                dgp401:num2004     ?fy_2004;
                dgp401:num2003     ?fy_2003;
                dgp401:num2002     ?fy_2002;
                dgp401:num2001     ?fy_2001;
                dgp401:num2000     ?fy_2000 .
  }
}


Query 2. locate agency in Dataset 10010 (us-agency, Rensselaer Polytechnic Institute)

The following query also works:

execute query

prefix xsd:    <http://www.w3.org/2001/XMLSchema#>
prefix dc:     <http://purl.org/dc/terms/> 
prefix foaf:   <http://xmlns.com/foaf/0.1/>
prefix dgp:    <http://data-gov.tw.rpi.edu/vocab/p/10029/>
prefix dgp401: <http://data-gov.tw.rpi.edu/vocab/p/401/>

select *
where {
  graph <http://data-gov.tw.rpi.edu/vocab/Dataset_10010> {
                                                  filter( ?name_10010       = "Department of Labor"^^xsd:string )
    ?join foaf:name     ?name_10010 ;             
          dc:identifier ?identifier_10010 .       filter( ?identifier_10010 = "Labor"^^xsd:string )
  }
}

Query 3. locate agency in Dataset 10029 (AFDC's report on governmental fuel consumption, Department of Energy)

And so does the following:

execute query

prefix xsd:    <http://www.w3.org/2001/XMLSchema#>
prefix dc:     <http://purl.org/dc/terms/> 
prefix foaf:   <http://xmlns.com/foaf/0.1/>
prefix dgp:    <http://data-gov.tw.rpi.edu/vocab/p/10029/>
prefix dgp401: <http://data-gov.tw.rpi.edu/vocab/p/401/>

select *
where {
  graph <http://data-gov.tw.rpi.edu/vocab/Dataset_10029> {
                                                  filter( ?identifier_10029 = "Labor" )
    ?o dgp:agency      ?identifier_10029;
       dgp:fuel        ?fuel;
       dgp:year        ?year;
       dgp:consumption ?consumption .
  }
}

Problem: composition of the three queries does not work

However, when the last three queries are combined, the query returns empty results. Is it a typed literal problem?

execute query

prefix xsd:    <http://www.w3.org/2001/XMLSchema#>
prefix dc:     <http://purl.org/dc/terms/> 
prefix foaf:   <http://xmlns.com/foaf/0.1/>
prefix dgp:    <http://data-gov.tw.rpi.edu/vocab/p/10029/>
prefix dgp401: <http://data-gov.tw.rpi.edu/vocab/p/401/>

select *
where {
  graph <http://data-gov.tw.rpi.edu/vocab/Dataset_402> {
                                                  filter( ?name_402 = "Department of Labor" )
         ?entry dgp401:bureau_name ?name_402;     
                dgp401:num2008     ?fy_2008;
                dgp401:num2007     ?fy_2007;
                dgp401:num2006     ?fy_2006;
                dgp401:num2005     ?fy_2005;
                dgp401:num2004     ?fy_2004;
                dgp401:num2003     ?fy_2003;
                dgp401:num2002     ?fy_2002;
                dgp401:num2001     ?fy_2001;
                dgp401:num2000     ?fy_2000 .
  }

  filter( str(?name_402) = str(?name_10010) )

  graph <http://data-gov.tw.rpi.edu/vocab/Dataset_10010> {
                                                  filter( ?name_10010       = "Department of Labor"^^xsd:string )
    ?join foaf:name     ?name_10010;             
          dc:identifier ?identifier_10010 .       filter( ?identifier_10010 = "Labor"^^xsd:string )
  }

  filter( str(?identifier_10010) = str(?identifier_10029) )

  graph <http://data-gov.tw.rpi.edu/vocab/Dataset_10029> {
                                                  filter( ?identifier_10029 = "Labor" )
    ?o dgp:agency      ?identifier_10029;
       dgp:fuel        ?fuel;
       dgp:year        ?year;
       dgp:consumption ?consumption .
  }
}

Solution

PREFIX xsd:     <http://www.w3.org/2001/XMLSchema#>
PREFIX dgp:     <http://data-gov.tw.rpi.edu/vocab/p/10029/>
PREFIX dgp401:  <http://data-gov.tw.rpi.edu/vocab/p/401/>
SELECT ?agency1   ?fuel  ?year sum (?fy_2008)
WHERE {
       {
              {
                      GRAPH <http://data-gov.tw.rpi.edu/vocab/Dataset_10029> {
                              ?o dgp:agency ?agency1 .
                              ?o dgp:fuel   ?fuel.
                              ?o dgp:year   ?year.
                              ?o dgp:consumption ?consumption.
                      }
                      GRAPH <http://data-gov.tw.rpi.edu/vocab/Dataset_10010> {
                              ?s <http://purl.org/dc/terms/identifier>  ?agency2.
                              ?s <http://xmlns.com/foaf/0.1/name>       ?agency.
                      }
                      FILTER (?agency1= ?agency2)
              }
              GRAPH <http://data-gov.tw.rpi.edu/vocab/Dataset_402> {
                      ?entry dgp401:bureau_name ?bu .
              }
              FILTER (str(?bu) = str(?agency))
       }
       GRAPH <http://data-gov.tw.rpi.edu/vocab/Dataset_402> {
              ?entry dgp401:num2008 ?fy_2008 .
              ?entry dgp401:num2007 ?fy_2007 .
              ?entry dgp401:num2006 ?fy_2006 .
              ?entry dgp401:num2005 ?fy_2005 .
              ?entry dgp401:num2004 ?fy_2004 .
              ?entry dgp401:num2003 ?fy_2003 .
              ?entry dgp401:num2002 ?fy_2002 .
              ?entry dgp401:num2001 ?fy_2001 .
              ?entry dgp401:num2000 ?fy_2000 .
      }
}
GROUP BY ?agency1   ?fuel  ?year

Solution' (variable naming)

prefix xsd:     <http://www.w3.org/2001/XMLSchema#>
prefix dgp:     <http://data-gov.tw.rpi.edu/vocab/p/10029/>
prefix dgp401:  <http://data-gov.tw.rpi.edu/vocab/p/401/>
prefix dcterms: <http://purl.org/dc/terms/>
prefix foaf:    <http://xmlns.com/foaf/0.1/>

select ?agency_10029 ?fuel ?year (sum(?fy_2008) as ?fy2008sum)
where {
       {
              {
                      graph <http://data-gov.tw.rpi.edu/vocab/Dataset_10029> {
                              ?e_29 dgp:agency      ?agency_10029;           # "Energy"
                                    dgp:fuel        ?fuel;
                                    dgp:year        ?year;
                                    dgp:consumption ?consumption .
                      }
                      graph <http://data-gov.tw.rpi.edu/vocab/Dataset_10010> {
                              ?e_10 dcterms:identifier  ?agency_id_10010;    # "Energy"^^xsd:string
                                    foaf:name           ?agency_name_10010 . # "Department of Energy"^^xsd:string
                      }
                      filter (?agency_10029 = ?agency_id_10010)
              }
              graph <http://data-gov.tw.rpi.edu/vocab/Dataset_402> {
                      ?e_402 dgp401:bureau_name ?name_402 .                  # "Department of Energy"
              }
              filter (?name_402 = ?agency_name_10010)
        }
        graph <http://data-gov.tw.rpi.edu/vocab/Dataset_402> {
                ?e_402 dgp401:num2008 ?fy_2008;
                       dgp401:num2007 ?fy_2007;
                       dgp401:num2006 ?fy_2006;
                       dgp401:num2005 ?fy_2005;
                       dgp401:num2004 ?fy_2004;
                       dgp401:num2003 ?fy_2003;
                       dgp401:num2002 ?fy_2002;
                       dgp401:num2001 ?fy_2001;
                       dgp401:num2000 ?fy_2000 .
        }
}
group by ?agency_10029 ?fuel ?year
Personal tools
internal pages