Revised GCIS SPARQL Example #2

[justgo129]

Given #95, the second SPARQL example on GCIS no longer generates results. I have thus modified the query:

PREFIX dcterms: http://purl.org/dc/terms/ PREFIX dbpedia: http://dbpedia.org/resource/ PREFIX xsd: http://www.w3.org/2001/XMLSchema# PREFIX gcis: http://data.globalchange.gov/gcis.owl#

SELECT DISTINCT str($chapterTitle) as $Chapter_Title str($findingNumber) as $Finding_Number str($findingStatement) as $Finding_Text $finding as $Finding_ID

WHERE { $report a gcis:Report . $report dcterms:title "Climate Change Impacts in the United States: The Third National Climate Assessment"^^xsd:string . $report gcis:hasChapter $chapter . $chapter dcterms:title $chapterTitle . $chapter gcis:chapterNumber $chapterNumber . $chapter gcis:hasFinding $finding . $finding gcis:findingNumber $findingNumber . $finding gcis:findingStatement $findingStatement . } ORDER BY $chapterNumber $findingNumber

Note that in the output, the Finding Numbers are ordered as 2.1, 2.10, 2.11, 2.12, etc. in-lieu of 2.1, 2.2, 2.3. 2.4, etc. Earlier this was addressed through having the problematic entry in #95 and sorting by that. Suggestions on how to order this properly are welcome, after which I could send a pull request. Thanks.

[justgo129]

Also, note that 11 of the NCA3 findings are "report findings" and are thus findings of the entire report, not of particular chapters. https://data.globalchange.gov/report/nca3/finding?page=8

As such, they do not appear here. We might need to add something akin to "if ! chapter, than report->finding";

[zednis]

What file have you modified? The above query is not legal SPARQL syntax so I assume you are showing a template that is generating a query.

[justgo129]

@zednis, correct. The original query contained a call-out to dbpedia:Natural_number which we deleted in #95. I no longer have the original query as I've pulled in the changes. The query as it appears on prod is:

PREFIX dcterms: http://purl.org/dc/terms/ PREFIX dbpedia: http://dbpedia.org/resource/ PREFIX xsd: http://www.w3.org/2001/XMLSchema#

SELECT str($findingNumber) as $findingNumber str($statement) as $statement $finding

FROM http://data.globalchange.gov WHERE { $report dcterms:title "Climate Change Impacts in the United States: The Third National Climate Assessment"^^xsd:string . $report a gcis:Report . $report gcis:hasChapter $chapter . $finding gcis:isFindingOf $chapter . $finding dcterms:description $statement . $finding dbpedia:Natural_number $ordinal . $finding gcis:findingNumber $findingNumber . $finding a gcis:Finding . $chapter gcis:chapterNumber $chapterNumber . } ORDER BY $chapterNumber $ordinal

[justgo129]

(I'll use markdown in the future to denote URLs. <> where necessary were input into the original query.)

[zednis]

@justgo129 are you confusing ? with $ in your query? What you have posted is not a valid SPARQL query.

Also just use 3 backticks at the beginning and end of a section of code or a query to have it treated as code and the < and > not be formatted.

like this

[justgo129]

I've seen them used (perhaps inaccurately) interchangeably. I'll close this ticket for now. @zednis let's chat offline about this at your convenience.

[justgo129]

@zednis I've replaced the dollar signs with question marks. My question about the ordering of finding numbers still stands, though as it doesn't change the output. The query is available at: https://gist.github.com/justgo129/0ad8ad99cf30e7370d8b . Assistance is welcome. Once we get this figured out let's add this to the test suite and subsequently close this ticket.

[zednis]

PREFIX dcterms: <http://purl.org/dc/terms/>
PREFIX dbpedia: <http://dbpedia.org/resource/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX gcis: <http://data.globalchange.gov/gcis.owl#>

SELECT DISTINCT
str(?chapterTitle) as ?Chapter_Title
?findingNumber
?chapterNumber
xsd:integer(SUBSTR(?findingNumber,3)) as ?figureOfChapter
str(?findingStatement) as ?Finding_Text
?finding as ?Finding_ID
WHERE {
<http://data.globalchange.gov/report/nca3> gcis:hasChapter ?chapter .
?chapter dcterms:title ?chapterTitle .
?chapter gcis:chapterNumber ?chapterNumber .
?chapter gcis:hasFinding ?finding .
?finding gcis:findingNumber ?findingNumber .
?finding gcis:findingStatement ?findingStatement . 
}
ORDER BY ?chapterNumber ?figureOfChapter

[zednis]

note - the query I just used does not work for chapters 10+. I will attempt to update the query.

[zednis]

Revised query that works for all chapter numbers. This could probably be refactored to be more succinct, but it works as-is and seems pretty fast when executed.

YASGUI link - http://yasgui.org/short/Nk1VZ02o

PREFIX dcterms: <http://purl.org/dc/terms/>
PREFIX dbpedia: <http://dbpedia.org/resource/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX gcis: <http://data.globalchange.gov/gcis.owl#>

SELECT * {
{ SELECT DISTINCT
str(?chapterTitle) as ?Chapter_Title
?findingNumber
?chapterNumber
xsd:integer(SUBSTR(?findingNumber,3)) as ?figureOfChapter
str(?findingStatement) as ?Finding_Text
?finding as ?Finding_ID
WHERE {
<http://data.globalchange.gov/report/nca3> gcis:hasChapter ?chapter .
?chapter dcterms:title ?chapterTitle .
?chapter gcis:chapterNumber ?chapterNumber .
?chapter gcis:hasFinding ?finding .
?finding gcis:findingNumber ?findingNumber .
?finding gcis:findingStatement ?findingStatement .
FILTER(?chapterNumber < 10)
}
ORDER BY ?chapterNumber ?figureOfChapter
} UNION {
SELECT DISTINCT
str(?chapterTitle) as ?Chapter_Title
?findingNumber
?chapterNumber
xsd:integer(SUBSTR(?findingNumber,4)) as ?figureOfChapter
str(?findingStatement) as ?Finding_Text
?finding as ?Finding_ID
WHERE {
<http://data.globalchange.gov/report/nca3> gcis:hasChapter ?chapter .
?chapter dcterms:title ?chapterTitle .
?chapter gcis:chapterNumber ?chapterNumber .
?chapter gcis:hasFinding ?finding .
?finding gcis:findingNumber ?findingNumber .
?finding gcis:findingStatement ?findingStatement .
FILTER(?chapterNumber >= 10)
}
ORDER BY ?chapterNumber ?figureOfChapter
}
}

[justgo129]

Excellent, and I just added one line to remove the "xsd:string" from one of the column headings. I tested it myself, to my satisfaction. @bduggan how would I replace the current 2nd example at: https://data.globalchange.gov/examples

with this updated query? I am looking at: https://github.com/USGCRP/gcis/blob/master/lib/Tuba/files/templates/doc/examples.html.ep but don't see SPARQL syntax there.

Thanks, @zednis!

[bduggan]

https://github.com/USGCRP/gcis/blob/master/lib/Tuba/Doc.pm#L29

[justgo129]

Thanks. The updated query is available at: http://yasgui.org/short/EyM1nvrn

I'm trying to remove the "xsd:integer" suffix from figureOfChapter. However, sandwiching the portion before "as" on line 11: xsd:integer(SUBSTR(?findingNumber,3)) as ?figureOfChapter with str( ) causes figure 2.10 to follow 2.1 in the output. Is there a way around this?

[zednis]

@justgo129 I would suggest leaving figureOfChapter as-is, if it is not an integer then the sort doesn't work correctly. If we want it to be sorted as a number it has to ~be~ a number, strings are sorted differently and not to our desired effect.

[zednis]

ok, this works. Add a ?figureOfChapter_Text to both the inner selects and then have the outer select return that rather than ?figureOfChapter.

I am actually not sure why the order by on ?chapterNumber is working correctly. as a string it should order 20 before 3. The only thing I can surmise is that it is doing the ordering based on when ?chapterNumber is an integer rather than a string (so before you overwrite the value with str(?chapterNumber) as ?chapterNumber).

That said, I don't think supporting overwriting the variable in this manner is standard. As I recall fuseki endpoints choke on it. It seems to work in this case, but I would advise against continuing to use this pattern in queries.

http://yasgui.org/short/N1aRy5B2

PREFIX dcterms: <http://purl.org/dc/terms/>
PREFIX dbpedia: <http://dbpedia.org/resource/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX gcis: <http://data.globalchange.gov/gcis.owl#>

SELECT 
    ?Chapter_Title
    ?findingNumber
    ?chapterNumber
    ?figureOfChapter_Text
    ?Finding_Text
    ?Finding_ID
{
  { 
    SELECT DISTINCT
        str(?chapterTitle) as ?Chapter_Title
        str(?findingNumber) as ?findingNumber
        str(?chapterNumber) as ?chapterNumber
        xsd:integer(SUBSTR(?findingNumber,3)) as ?figureOfChapter
        SUBSTR(?findingNumber,3) as ?figureOfChapter_Text
        str(?findingStatement) as ?Finding_Text
        ?finding as ?Finding_ID
    WHERE {
        <http://data.globalchange.gov/report/nca3> gcis:hasChapter ?chapter .
        ?chapter dcterms:title ?chapterTitle .
        ?chapter gcis:chapterNumber ?chapterNumber .
        ?chapter gcis:hasFinding ?finding .
        ?finding gcis:findingNumber ?findingNumber .
        ?finding gcis:findingStatement ?findingStatement .
        FILTER(?chapterNumber < 10)
    } ORDER BY ?chapterNumber ?figureOfChapter
  } UNION {
    SELECT DISTINCT
        str(?chapterTitle) as ?Chapter_Title
        str(?findingNumber) as ?findingNumber
        str(?chapterNumber) as ?chapterNumber
        xsd:integer(SUBSTR(?findingNumber,4)) as ?figureOfChapter
        SUBSTR(?findingNumber,4) as ?figureOfChapter_Text
        str(?findingStatement) as ?Finding_Text
        ?finding as ?Finding_ID
    WHERE {
        <http://data.globalchange.gov/report/nca3> gcis:hasChapter ?chapter .
        ?chapter dcterms:title ?chapterTitle .
        ?chapter gcis:chapterNumber ?chapterNumber .
        ?chapter gcis:hasFinding ?finding .
        ?finding gcis:findingNumber ?findingNumber .
        ?finding gcis:findingStatement ?findingStatement .
        FILTER(?chapterNumber >= 10)
    } ORDER BY ?chapterNumber ?figureOfChapter
  }
}

[justgo129]

I'm closing #121 since we're changing the query given that it is now much more complex. See #234 for some candidate replacements. (@bduggan please reopen that one - thanks).