User:Trilotat/SPARQL

General

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 .
}

Try it!

Publications

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_

Try it!

Persons

Specific author in Geology journal

SELECT ?item ?itemLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  
  ?item  wdt:P2093 "C. Timmreck".
}

Try it!

Units & Stratigraphy

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")
}

Try it!

Changes to Main Subject

Work in variations of the subject term

Title includes "asteroseismology"] (P921 to be Q94529)

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

Try it!

Corrections, errata and corrigenda

All credit for these go to Tagishsimon

Find errata not having the original article set as topic

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". }
}

Try it!

Find items with label strings matching the erratum's title

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)

Try it!

Erratum where title starts - Erratum to:

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)
}

Try it!

Erratum where title starts - Corrigendum:

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)
}

Try it!

Erratum where title starts - Corrigendum to:

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)
}

Try it!

Erratum where title starts - Erratum: Corrigendum:

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)
}

Try it!

Erratum where title starts - Erratum: correction:

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)
}

Try it!

other formats of the item label

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)
}

Try it!

Physics Review errata

@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...

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)
}

Try it!

--Tagishsimon (talk) 14:06, 8 February 2019 (UTC)

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

@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)

Haha, I forgot about that! oops. Thank you for the compliment and I'll keep pinging you for help. You're a great member of this community. Trilotat (talk) 22:01, 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)

Finds all that start with Retraction

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)

@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)

User:Trilotat/SPARQL

Contents

General

Publications

Persons

Units & Stratigraphy

Changes to Main Subject

Corrections, errata and corrigenda

Physics Review errata

Next up, Retractions

Navigation menu