User:Trilotat/SPARQL
General
[edit]Thanks to Simon Villeneuve
Checking for duplicate citations.
SELECT DISTINCT ?item
WHERE
{
VALUES ?work { wd:Q59238938 wd:Q96484383 wd:Q63994232 }
?item p:P2860 ?cites_statement1 .
?item p:P2860 ?cites_statement2 .
FILTER (?cites_statement1 != ?cites_statement2)
?cites_statement1 ps:P2860 ?work .
?cites_statement2 ps:P2860 ?work .
}
Publications
[edit]- Geology journal with specified volume
SELECT ?Geology ?volume ?issue ?page_s_ ?GeologyLabel WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
?Geology wdt:P1433 wd:Q5535339.
OPTIONAL { ?Geology wdt:P433 ?issue. }
OPTIONAL { ?Geology wdt:P304 ?page_s_. }
?Geology wdt:P478 "36".
} order by ?issue ?page_s_
Persons
[edit]- Specific author in Geology journal
SELECT ?item ?itemLabel WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
?item wdt:P2093 "C. Timmreck".
}
Units & Stratigraphy
[edit]Term search within a specified publication
SELECT ?article ?title
WHERE
{
?article wdt:P31 wd:Q13442814.
?article wdt:P1433 wd:Q465355.
?article wdt:P1476 ?title.
FILTER REGEX(?title, "\\bformation\\b")
# FILTER REGEX(?title, "\\bU\\.S\\.A\\.\\b")
}
Changes to Main Subject
[edit]Work in variations of the subject term
- Title includes "asteroseismology"] (P921 to be Q94529)
- Try it!
SELECT DISTINCT ?item ?title WHERE { hint:Query hint:optimizer "None". { SERVICE wikibase:mwapi { bd:serviceParam wikibase:api "Search"; wikibase:endpoint "www.wikidata.org"; mwapi:srsearch "\"asteroseismology\" haswbstatement:P31=Q13442814". ?page_title wikibase:apiOutput mwapi:title. } } BIND(IRI(CONCAT(STR(wd:), ?page_title)) AS ?item) FILTER NOT EXISTS { ?item wdt:P921 wd:Q94529. } ?item wdt:P31 wd:Q13442814; wdt:P1476 ?title. FILTER CONTAINS(LCASE(?title), "asteroseismology"). } # LIMIT 10000
Corrections, errata and corrigenda
[edit]All credit for these go to Tagishsimon
Find errata not having the original article set as topic
- Try it!
SELECT ?item ?itemLabel ?errata ?errataLabel WHERE { ?item wdt:P2507 ?errata. #item has a P2507 filter not exists {?errata wdt:P921 ?item .} #oh woe; the errata does not point back to the item SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } }
Find items with label strings matching the erratum's title
- Try it!
SELECT distinct ?errata ?errataLabel ?itemLabel ?item (count(?item) as ?count) WHERE { hint:Query hint:optimizer "None" . ?errata wdt:P31 wd:Q1348305 . # ?errata is an errata filter not exists {?itemZ wdt:P2507 ?errata . } # there's no ?item pointing to the errata. ?errata rdfs:label ?errataLabel . filter(lang(?errataLabel)="en") filter(strstarts(?errataLabel,"Erratum:")) bind(replace(?errataLabel,"Erratum: ","") as ?itemLabel) . ?item rdfs:label ?itemLabel. filter(strlen(?itemLabel)>20) ?errata wdt:P1433 ?PI . ?item wdt:P1433 ?PI . # ?errata wdt:P50 ?nn . # ?item wdt:P50 ?nn . # ?errata wdt:P2093 ?nn2 . # ?item wdt:P2093 ?nn2 . } group by ?errata ?errataLabel ?itemLabel ?item having (?count=1)
Erratum where title starts - Erratum to:
- Try it!
SELECT ?errata ?errataLabel ?itemLabel ?item WHERE { hint:Query hint:optimizer "None" . ?errata wdt:P31 wd:Q1348305 . # ?errata is an errata filter not exists {?itemZ wdt:P2507 ?errata . } # there's no ?item pointing to the errata. ?errata rdfs:label ?errataLabel . filter(lang(?errataLabel)="en") filter(strstarts(?errataLabel,"Erratum to:")) bind(replace(?errataLabel,"Erratum to: ","") as ?itemLabel) . ?item rdfs:label ?itemLabel. filter(strlen(?itemLabel)>20) filter(?item != ?errata) }
Erratum where title starts - Corrigendum:
- Try it!
SELECT ?errata ?errataLabel ?itemLabel ?item WHERE { hint:Query hint:optimizer "None" . ?errata wdt:P31 wd:Q1348305 . # ?errata is an errata filter not exists {?itemZ wdt:P2507 ?errata . } # there's no ?item pointing to the errata. ?errata rdfs:label ?errataLabel . filter(lang(?errataLabel)="en") filter(strstarts(?errataLabel,"Corrigendum:")) bind(replace(?errataLabel,"Corrigendum: ","") as ?itemLabel) . ?item rdfs:label ?itemLabel. filter(strlen(?itemLabel)>20) filter(?item != ?errata) }
Erratum where title starts - Corrigendum to:
- Try it!
SELECT ?errata ?errataLabel ?itemLabel ?item WHERE { hint:Query hint:optimizer "None" . ?errata wdt:P31 wd:Q1348305 . # ?errata is an errata filter not exists {?itemZ wdt:P2507 ?errata . } # there's no ?item pointing to the errata. ?errata rdfs:label ?errataLabel . filter(lang(?errataLabel)="en") filter(strstarts(?errataLabel,"Corrigendum to:")) bind(replace(?errataLabel,"Corrigendum to: ","") as ?itemLabel) . ?item rdfs:label ?itemLabel. filter(strlen(?itemLabel)>20) filter(?item != ?errata) }
Erratum where title starts - Erratum: Corrigendum:
- Try it!
SELECT ?errata ?errataLabel ?itemLabel ?item WHERE { hint:Query hint:optimizer "None" . ?errata wdt:P31 wd:Q1348305 . # ?errata is an errata filter not exists {?itemZ wdt:P2507 ?errata . } # there's no ?item pointing to the errata. ?errata rdfs:label ?errataLabel . filter(lang(?errataLabel)="en") filter(strstarts(?errataLabel,"Erratum: Corrigendum:")) bind(replace(?errataLabel,"Erratum: Corrigendum: ","") as ?itemLabel) . ?item rdfs:label ?itemLabel. filter(strlen(?itemLabel)>20) filter(?item != ?errata) }
Erratum where title starts - Erratum: correction:
- Try it!
SELECT ?errata ?errataLabel ?itemLabel ?item WHERE { hint:Query hint:optimizer "None" . ?errata wdt:P31 wd:Q1348305 . # ?errata is an errata filter not exists {?itemZ wdt:P2507 ?errata . } # there's no ?item pointing to the errata. ?errata rdfs:label ?errataLabel . filter(lang(?errataLabel)="en") filter(strstarts(?errataLabel,"Erratum: correction:")) bind(replace(?errataLabel,"Erratum: correction: ","") as ?itemLabel) . ?item rdfs:label ?itemLabel. filter(strlen(?itemLabel)>20) filter(?item != ?errata) }
other formats of the item label
- Try it!
SELECT ?errata ?errataLabel ?itemLabel ?item WHERE { hint:Query hint:optimizer "None" . ?errata wdt:P31 wd:Q1348305 . # ?errata is an errata filter not exists {?itemZ wdt:P2507 ?errata . } # there's no ?item pointing to the errata. ?errata rdfs:label ?errataLabel . filter(lang(?errataLabel)="en") filter(strstarts(?errataLabel,"Corrigendum to:")) bind(replace(?errataLabel,"Corrigendum to: “","") as ?111) . bind(replace(?111,"”","") as ?itemLabel) . ?item rdfs:label ?itemLabel. filter(strlen(?itemLabel)>20) filter(?item != ?errata) }
- Try it!
SELECT ?errata ?errataLabel ?itemLabel ?item WHERE { hint:Query hint:optimizer "None" . ?errata wdt:P31 wd:Q1348305 . # ?errata is an errata filter not exists {?itemZ wdt:P2507 ?errata . } # there's no ?item pointing to the errata. ?errata rdfs:label ?errataLabel . filter(lang(?errataLabel)="en") filter(strstarts(?errataLabel,"Corrigendum to:")) bind(replace(?errataLabel,"Corrigendum to: “","") as ?111) . bind(strbefore(?111,"”") as ?222) . bind(replace(?222,"”","") as ?itemLabel) . ?item rdfs:label ?itemLabel. filter(strlen(?itemLabel)>20) filter(?item != ?errata) }
- Try it!
SELECT ?errata ?errataLabel ?itemLabel ?item WHERE { hint:Query hint:optimizer "None" . ?errata wdt:P31 wd:Q1348305 . # ?errata is an errata filter not exists {?itemZ wdt:P2507 ?errata . } # there's no ?item pointing to the errata. ?errata rdfs:label ?errataLabel . filter(lang(?errataLabel)="en") filter(strstarts(?errataLabel,"Corrigendum:")) bind(replace(?errataLabel,"Corrigendum: “","") as ?111) . bind(replace(?111,"”","") as ?itemLabel) . ?item rdfs:label ?itemLabel. filter(strlen(?itemLabel)>20) filter(?item != ?errata) }
- Try it!
SELECT ?errata ?errataLabel ?itemLabel ?item WHERE { hint:Query hint:optimizer "None" . ?errata wdt:P31 wd:Q1348305 . # ?errata is an errata filter not exists {?itemZ wdt:P2507 ?errata . } # there's no ?item pointing to the errata. ?errata rdfs:label ?errataLabel . filter(lang(?errataLabel)="en") filter(strstarts(?errataLabel,"Corrigendum:")) bind(replace(?errataLabel,"Corrigendum: “","") as ?111) . bind(strbefore(?111,"”") as ?222) . bind(replace(?222,"”","") as ?itemLabel) . ?item rdfs:label ?itemLabel. filter(strlen(?itemLabel)>20) filter(?item != ?errata) }
- Try it!
SELECT ?errata ?errataLabel ?itemLabel ?item WHERE { hint:Query hint:optimizer "None" . ?errata wdt:P31 wd:Q1348305 . # ?errata is an errata filter not exists {?itemZ wdt:P2507 ?errata . } # there's no ?item pointing to the errata. ?errata rdfs:label ?errataLabel . filter(lang(?errataLabel)="en") filter(strstarts(?errataLabel,"Erratum to:")) bind(replace(?errataLabel,"Erratum to: “","") as ?111) . bind(replace(?111,"”","") as ?itemLabel) . ?item rdfs:label ?itemLabel. filter(strlen(?itemLabel)>20) filter(?item != ?errata) }
- Try it!
SELECT ?errata ?errataLabel ?itemLabel ?item WHERE { hint:Query hint:optimizer "None" . ?errata wdt:P31 wd:Q1348305 . # ?errata is an errata filter not exists {?itemZ wdt:P2507 ?errata . } # there's no ?item pointing to the errata. ?errata rdfs:label ?errataLabel . filter(lang(?errataLabel)="en") filter(strstarts(?errataLabel,"Erratum to:")) bind(replace(?errataLabel,"Erratum to: “","") as ?111) . bind(strbefore(?111,"”") as ?222) . bind(replace(?222,"”","") as ?itemLabel) . ?item rdfs:label ?itemLabel. filter(strlen(?itemLabel)>20) filter(?item != ?errata) }
- Try it!
SELECT ?errata ?errataLabel ?itemLabel ?item WHERE { hint:Query hint:optimizer "None" . ?errata wdt:P31 wd:Q1348305 . # ?errata is an errata filter not exists {?itemZ wdt:P2507 ?errata . } # there's no ?item pointing to the errata. ?errata rdfs:label ?errataLabel . filter(lang(?errataLabel)="en") filter(strstarts(?errataLabel,"Erratum:")) bind(replace(?errataLabel,"Erratum: “","") as ?111) . bind(replace(?111,"”","") as ?itemLabel) . ?item rdfs:label ?itemLabel. filter(strlen(?itemLabel)>20) filter(?item != ?errata) }
- Try it!
SELECT ?errata ?errataLabel ?itemLabel ?item WHERE { hint:Query hint:optimizer "None" . ?errata wdt:P31 wd:Q1348305 . # ?errata is an errata filter not exists {?itemZ wdt:P2507 ?errata . } # there's no ?item pointing to the errata. ?errata rdfs:label ?errataLabel . filter(lang(?errataLabel)="en") filter(strstarts(?errataLabel,"Erratum:")) bind(replace(?errataLabel,"Erratum: “","") as ?111) . bind(strbefore(?111,"”") as ?222) . bind(replace(?222,"”","") as ?itemLabel) . ?item rdfs:label ?itemLabel. filter(strlen(?itemLabel)>20) filter(?item != ?errata) }
Physics Review errata
[edit]@Tagishsimon: Hope you're well. Is there a way to edit of the above so it includes the results similar to: Erratum: Nonlinear dynamics of a solid-state laser with injection [Phys. Rev. E58, 4421 (1998)] (Q56913208)? I think the queries exclude this result because of that annoying part of the erratum title between the brackets. I did a search for (Erratum "[Phys. Rev.") less the parenthesis and got about 430 results. I added corrigendum / erratum (P2507) to about 30 and ran out of steam. Thanks for any help. Trilotat (talk) 13:50, 8 February 2019 (UTC)
- @Trilotat: Afternoon, Trilotat; very well thanks. I trust you are. And still mining errata. That's good.
- I'm not sure if this is what you're looking for (if it is, two flavour given - swap the position of the # on the
bind(strbefore(?111 ...
string to get an extra 20 hits; discuss... - --Tagishsimon (talk) 14:06, 8 February 2019 (UTC)Try it!
SELECT ?errata ?errataLabel ?itemLabel ?item WHERE { hint:Query hint:optimizer "None" . ?errata wdt:P31 wd:Q1348305 . # ?errata is an errata filter not exists {?itemZ wdt:P2507 ?errata . } # there's no ?item pointing to the errata. ?errata rdfs:label ?errataLabel . filter(lang(?errataLabel)="en") filter(strstarts(?errataLabel,"Erratum:")) bind(replace(?errataLabel,"Erratum: ","") as ?111) . bind(strbefore(?111," [") as ?itemLabel) . # bind(strbefore(?111,"[") as ?itemLabel) . ?item rdfs:label ?itemLabel. filter(strlen(?itemLabel)>20) filter(?item != ?errata) }
- wow! You speak Wikidata Query Service as fluently as an additional spoken language! It's perfect. I am still mining errata. I hope someday when Wikidata achieves its potential as a citation resource, that the errata are useful. Thanks for the immediate reply. It's phenomenal. Trilotat (talk) 14:10, 8 February 2019 (UTC)
Next up, Retractions
[edit]@Tagishsimon: When I get caught up with errata, I'll try my hand at editing one of the queries above to mine retraction notice (Q7316896). Trilotat (talk) 21:11, 8 February 2019 (UTC)
- Yup, you touched on this problem on Request a query some time back ... there's a query there for you when you have time. You're doing good work tying these things up; hats off to you. Ping me whenever you want a query tweaked; I'm still a SPARQL learner & it's handy to be challenged. --Tagishsimon (talk) 21:29, 8 February 2019 (UTC)
@Tagishsimon: I edited one of your errata queries to reflect retractions rather than errata. I didn't edit the ?errata because I was afraid I'd break the query. They work, so I'll keep at it. Note, there are no results in the first example below since I tagged the retracted articles as retracted.
Woohoo! I am enjoying this! I welcome your editorial comments. Trilotat (talk) 04:27, 11 February 2019 (UTC)
- My editorial comment is - and you can quote me on this - "Whoop, good stuff, Trilotat". Make these queries work for you :). Meanwhile I /might/ be able to get that page you asked for - I'll have a go later today when my other half, who has a university login, emerges from her slumber. --Tagishsimon (talk) 07:30, 11 February 2019 (UTC)
@Tagishsimon: Can you help me with the following query? You provided it as a way to find the items which have title that begin with Retraction. I am unable to further exclude items that have an item that points back via P5824. I think that might be the only way to see the number of results reduce as I tag retracted articles. Thanks. Trilotat (talk) 16:06, 11 February 2019 (UTC)
- This, if I understand correctly. I've added a line towards the bottom,
minus {?item wdt:P5824 [] .}
which says "remove items if they have a P5824 (and we don't care what the value is)". Let me know if that's not the answer. --Tagishsimon (talk) 16:18, 11 February 2019 (UTC)
- This, if I understand correctly. I've added a line towards the bottom,
- @Tagishsimon: It doesn't seem to. One, that query includes all the articles with the word "retraction", not those that start with "retraction". Your query, Finds all that start with Retraction, is the query I was hoping that you'd edit. Also, I hoped that it would filter results that are pointed at by an "is retracted by" (P5824) statement in the original but retracted article. That query fitlers results that include the P5824, but the retraction notices wouldn't include the "is retracted by" statement. Sorry. I am afraid I might really be becoming a nuisance. Trilotat (talk) 16:32, 11 February 2019 (UTC)
- Assuredly you are not being a nuisance when you ask for queries, or when you tell me that I've barked up the wrong tree. I'm really am very happy to try to come up with solutions to your needs. Equally, I sometimes only have half a brain on the job, which is suboptimal. I need to sort out some Ghanaian MPs right now, but once that's out of the way I'll come back here with undivided attention. tbh, I'll be much sadder if you hold off asking stuff & pointing out my wrongness from some misapprehension that you're being a pest. --Tagishsimon (talk) 17:18, 11 February 2019 (UTC)
- Thanks! Are those Ghanaian Ministers of Parliament or Military Police? Either way, I've a fondness for Ghana, what with Kofi Annan (Q1254) and Ofeibea Quist-Arcton (Q7078850). I think Ghana is my favorite West African country. Am I allowed to have a favorite? haha. Trilotat (talk) 17:28, 11 February 2019 (UTC)
- @Tagishsimon: Revisiting... Hope you're well. Trilotat (talk) 01:42, 20 February 2019 (UTC)
- Yup. Sorry about the radio silence. I'll come back to you later today, I hope. Don't give up hope :) --Tagishsimon (talk) 12:16, 23 February 2019 (UTC)