Wikidata:Request a query/Archive/2023/10

This page is an archive. Please do not modify it. Use the current page, even to continue an old discussion.

"district of Turkey (Q1147395)" without "OpenStreetMap relation ID (P402)"

Hello, I'm searching "district of Turkey (Q1147395)" without "OpenStreetMap relation ID (P402)". This should be only in "Turkey (Q43)". Thanks Turankaya74 (talk) 16:17, 1 October 2023 (UTC)

@Turankaya74:

SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P31 wd:Q1147395 . 
  ?item wdt:P17 wd:Q43.
  filter not exists {?item wdt:P402 [] . }                    
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 16:59, 1 October 2023 (UTC)

Shorten URL

Is there any way to shorten the URLs to Wikipedia articles?
Example:

#defaultView:Timeline
SELECT DISTINCT ?president ?presidentLabel ?start ?end ?article
(SAMPLE(?image) as ?image)

WHERE {
  ?president wdt:P31 wd:Q5 .
  ?president p:P39 ?presidentStatement .
  ?presidentStatement ps:P39 wd:Q11696 .   
  OPTIONAL { ?presidentStatement pq:P580 ?start }
  OPTIONAL { ?presidentStatement pq:P582 ?end }
  OPTIONAL { ?president wdt:P18 ?image }
  OPTIONAL { 
    ?article schema:about ?president . 
    ?article schema:isPartOf <https://en.wikipedia.org/> .
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} GROUP BY ?president ?presidentLabel ?start ?end ?article
ORDER BY ?start

Try it!

Instead of <https://en.wikipedia.org/wiki/Harry_S._Truman> it would be better to display only Harry S. Truman (but still keeping the hyperlink to the actual article). --Cavernia (talk) 13:16, 2 October 2023 (UTC)

Can't be done in the WDQS UI, afaik. Can be done in e.g. Listeria - i.e. as a wikimedia table - but I don't think / know that a timeline is available on a wikimedia page from a SPARQL query. --Tagishsimon (talk) 17:13, 2 October 2023 (UTC)

OK, thank you. --Cavernia (talk) 17:59, 2 October 2023 (UTC)

Height in a single unit

height (P2048) is stored in different items in different units (meters, feet, centimeters and so). I would want to get all heights in meters and I suppose it could be done using qualifiers and probably BIND, IF and BOUND, but I suppose I'm not the first to face this problem and there is no need to invent the wheel. Is there an available example of how to convert a property to a single unit? Pere prlpz (talk) 20:53, 2 October 2023 (UTC)

@Pere prlpz: Normalised values are computed and stored as a triple within the statement, for height, and for various other properties; they can be accessed down the p: / psn: / wikibase:quantityAmount and wikibase:quantityUnit property paths. See the RDF_Dump_Format#Normalised_values and the property / conversion table for details & specifics.

SELECT ?item ?itemLabel ?value ?unitLabel ?N_value ?N_unitLabel where {
  ?item wdt:P31 wd:Q8502. # mountains
  ?item p:P2048 ?statement . # height statement

  ?statement psv:P2048 ?statement2 . # values as entered
  ?statement2 wikibase:quantityAmount ?value .
  ?statement2 wikibase:quantityUnit ?unit .
  
  ?statement psn:P2048 ?statement3 . # normalised values
  ?statement3 wikibase:quantityAmount ?N_value .
  ?statement3 wikibase:quantityUnit ?N_unit .
            
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 21:54, 2 October 2023 (UTC)

Stations on railway line with linear reference

Hi!

I'd like to have a query that asks for all stops and stations that are (via connecting line (P81)) on the Douro railway line (Q915792), and shows their linear reference (P6710) (the km reference) on it (added as a qualifier). I still don't know how to write/build a query with qualifiers. :( Thank you! Jcornelius (talk) 17:12, 2 October 2023 (UTC)

@Jcornelius: This? (See also https://wdqs-tutorial.toolforge.org/index.php/category/simple-queries/qualifiers/ which is a good tutorial for this sort of stuff)

SELECT ?item ?itemLabel ?km where {
  ?item p:P81 ?stat . 
  ?stat ps:P81 wd:Q915792.
  OPTIONAL {?stat pq:P6710 ?km .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],pt,en". }
}

Try it!

--Tagishsimon (talk) 17:18, 2 October 2023 (UTC)

Thanks so much! Jcornelius (talk) 16:18, 4 October 2023 (UTC)

Misstagged klingon text

I need to find all monolingual text fields with the language code und (undetermined language) or mis (unsupported language) with the qualifier language of work or name (P407) Klingon (Q10134) in order to add a correct language code. Thanks 🤓 – Shisma (talk) 17:22, 2 October 2023 (UTC)

@Shisma: No examples for und, afaics.

SELECT ?item ?itemLabel ?property ?propertyLabel ?value ?lang where {
  ?item ?predicate ?stat. 
  ?stat pq:P407 wd:Q10134.
  ?property wikibase:claim ?predicate.
  ?property wikibase:propertyType  wikibase:Monolingualtext.
  ?stat ?predicate2 ?value .
  ?property wikibase:statementProperty ?predicate2.
  bind(lang(?value) as ?lang)
  filter(?lang="und" || ?lang="mis" )
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],pt,en". }
}

Try it!

--Tagishsimon (talk) 17:50, 2 October 2023 (UTC)

thank you 🥹. I have seen examples for und – Shisma (talk) 18:35, 2 October 2023 (UTC)

@Shisma@Tagishsimon Looking at this edited query, I think the answer is that there are no monolingual-text properties at all, regardless of language, with a language of work or name (P407):Klingon (Q10134) qualifier. Six QIDs, seven externalIDs, and two URLs. There also don't seem to be any other qualifiers with value Q101034 which are attached to monolingual-text statements. Andrew Gray (talk) 17:06, 4 October 2023 (UTC)

Sure, but that's because Shisma has edited those which were found a few days ago via the first report (example). But, taking up Shisma's point: the report did not find any 'und' language strings, so... --Tagishsimon (talk) 17:20, 4 October 2023 (UTC)

Capital cities with GPS location

Hi, friends! Based on my highly limited skills I've been able to create the following query to list capital (P36) for a defined set of countries:

SELECT ?country ?countryLabel ?capitalLabel
WHERE {
  VALUES ?country { wd:Q965 wd:Q115 wd:Q117 wd:Q114 wd:Q1037 wd:Q924 wd:Q945 wd:Q1036 wd:Q902 wd:Q252 wd:Q928 wd:Q854 wd:Q869 wd:Q786 wd:Q792 wd:Q774 wd:Q790 wd:Q783 wd:Q96 wd:Q811 wd:Q750 wd:Q155 wd:Q739 wd:Q736 wd:Q419 wd:Q424 wd:Q1020 wd:Q836 wd:Q953 }
  OPTIONAL { ?country wdt:P36 ?capital. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ASC(?countryLabel)

Try it!

I'd like to enhance this query to include the coordinate location (P625) for each capital, but I'm unsure how to achieve that because it's an attribute of the capital, not the country. Is there a way I could do this? Thank you! Nine Tail Fox (talk) 18:49, 3 October 2023 (UTC)

@Nine Tail Fox: This, for instance. I'm sure all the capitals will have coords, but I wrapped the statement in an OPTIONAL{} just in case.

SELECT ?country ?countryLabel ?capitalLabel ?coord
WHERE {
  VALUES ?country { wd:Q965 wd:Q115 wd:Q117 wd:Q114 wd:Q1037 wd:Q924 wd:Q945 wd:Q1036 wd:Q902 wd:Q252 wd:Q928 wd:Q854 wd:Q869 wd:Q786 wd:Q792 wd:Q774 wd:Q790 wd:Q783 wd:Q96 wd:Q811 wd:Q750 wd:Q155 wd:Q739 wd:Q736 wd:Q419 wd:Q424 wd:Q1020 wd:Q836 wd:Q953 }
  OPTIONAL { ?country wdt:P36 ?capital. 
    OPTIONAL {?capital wdt:P625 ?coord . } }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ASC(?countryLabel)

Try it!

--Tagishsimon (talk) 19:25, 3 October 2023 (UTC)

Genius. Thank you, @Tagishsimon:! Nine Tail Fox (talk) 18:04, 4 October 2023 (UTC)

Get elements from ID list

I have a CSV file with 2 columns (ID, ISBN) and tens of lines.

How can I get wikidata elements given a list of ISBNs?

The goal is to get the title and publication date from Wikidata. I'd like to get a result like:

ID;ISBN;Title;PublicationDate

0;978-2-35087-015-1;Fleur de peau;2006

1;978-1-167-74271-2;Le Colonialisme;1905

2;978-2-909478-14-2;L'or de Tolosa;2001

...

Do you think you could show me what kind of query to write? Aandrem (talk) 01:21, 4 October 2023 (UTC)

@Aandrem: This sort of thing; ISBNs in the VALUES statement, in quotes... the query can probably cope with hundreds of them. Should you have ISBN13 values, then use ISBN-13 (P212) rather than ISBN-10 (P957). Probably ?itemLabel will provide a title more often than will title (P1476), but I've included it just in case. hth.

#title:ISBN10 version
SELECT ?item ?itemLabel ?isbn10 ?title ?published
WHERE 
{
  VALUES ?isbn10 {
    "0-521-22151-X"
    "0-521-29366-9"
  }
  ?item wdt:P957 ?isbn10.
  OPTIONAL {?item wdt:P577 ?published.}
  OPTIONAL {?item wdt:P1476 ?title.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr,en". }
}

ISBN10 version

#title:ISBN13 version
SELECT ?item ?itemLabel ?isbn13 ?title ?published
WHERE 
{
  VALUES ?isbn13 {
    "978-2-35087-015-1"
    "978-1-167-74271-2"
    "978-2-909478-14-2"
  }
  ?item wdt:P212 ?isbn13.
  OPTIONAL {?item wdt:P577 ?published.}
  OPTIONAL {?item wdt:P1476 ?title.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr,en". }
}

ISBN13 version

#title:ISBN10 and ISBN13 combined version
SELECT ?item ?itemLabel ?isbn ?title ?published
WHERE 
{
  VALUES ?isbn {
    "0-521-22151-X"
    "0-521-29366-9"
    "978-2-35087-015-1"
    "978-1-167-74271-2"
    "978-2-909478-14-2"
  }
  {?item wdt:P957 ?isbn.}
  UNION
  {?item wdt:P212 ?isbn.}
  OPTIONAL {?item wdt:P577 ?published.}
  OPTIONAL {?item wdt:P1476 ?title.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr,en". }
}

ISBN10 and ISBN13 combined version

--Tagishsimon (talk) 01:29, 4 October 2023 (UTC)

I only have ISBN13 codes, this works perfectly. @Tagishsimon Thank you! Aandrem (talk) 14:30, 5 October 2023 (UTC)

From an ID list, get those not matching any Wikidata element

Given a CSV file with 2 columns (ID, ISBN13) and around a thousand of lines, actually how can I get ISBN13 codes of this list that don't match any Wikidata element? Aandrem (talk) 15:31, 5 October 2023 (UTC)

@Aandrem:

SELECT ?isbn13
WHERE 
{
  VALUES ?isbn13 {
    "978-2-35087-015-1"
    "978-1-167-74271-2"
    "978-2-909478-14-2"
    "999-9-999999-99-9"
  }
  filter not exists {?item ?predicate ?isbn13.}
}

Try it!

--Tagishsimon (talk) 18:06, 5 October 2023 (UTC)

There's also this, for extra fu; VALUES can take multiple arguments, so the ID column can be included should this be advantageous:

SELECT ?id ?isbn13
WHERE 
{
  VALUES (?id ?isbn13) {
   ("id1" "978-2-35087-015-1")
   ("id2" "978-1-167-74271-2")
   ("id3" "978-2-909478-14-2")
   ("id4" "999-9-999999-99-9")
  }
  filter not exists {?item ?predicate ?isbn13.}
}

Try it!

--Tagishsimon (talk) 21:28, 5 October 2023 (UTC)

@Tagishsimon Thank You! Aandrem (talk) 13:41, 6 October 2023 (UTC)

Instances of humans with BCE birthdate

Why if execute this queryː

SELECT ?person ?dateOfBirth
WHERE {
  ?person wdt:P31 wd:Q5.
  ?person wdt:P569 ?dateOfBirth.
FILTER (?dateOfBirth = "-355-07-20"^^xsd:dateTime)
}
ORDER BY ASC(?dateOfBirth)
LIMIT 10

Try it!

I do not get Alexander the Great (Q8409) (born 20-07-356 BCE), while if I execute this queryː

SELECT ?person ?dateOfBirth
WHERE {
  ?person wdt:P31 wd:Q5.
  ?person wdt:P569 ?dateOfBirth.
FILTER (?dateOfBirth = "274-02-27"^^xsd:dateTime)
}
ORDER BY ASC(?dateOfBirth)
LIMIT 10

Try it!

I correctly get Constantine the Great (Q8413) (born 27-02-274)?

Thank you in advance Nungalpiriggal (talk) 14:40, 6 October 2023 (UTC)

@Nungalpiriggal: It's to do with wikidata storing dates against a proleptic Gregorian calendar, when they're entered and displayed as Julian calendar dates. In essence the gap between the Julian and Gregorian dates varies according to the date. If you revert the sandbox2 item to this revision https://www.wikidata.org/w/index.php?title=Q13406268&oldid=1987548191 and then run this report - https://w.wiki/7gZX - you'll be able to see the stored date for a bunch of 15 January dates, for a range of centuries denoted in the series ordinal column. Those in the report are:

• 15 Jan -300 Julian is stored as 11 Jan -300
• 15 Jan -200 Julian is stored as 12 Jan -200
• 15 Jan -100 Julian is stored as 13 Jan -100
• 15 Jan 1 Julian is stored as 13 Jan 1
• 15 Jan 100 Julian is stored as 13 Jan 100
• 15 Jan 200 Julian is stored as 14 Jan 200
• 15 Jan 300 Julian is stored as 15 Jan 300
• 15 Jan 400 Julian is stored as 16 Jan 400
• 15 Jan 500 Julian is stored as 16 Jan 500

And so it happened to be that Constantine's DoB is stored as the same date string as you filtered for, whilst Alexander's date string is stored as -355-07-15 --Tagishsimon (talk) 22:37, 6 October 2023 (UTC)

@Tagishsimon Very interesting, thank you for your detailed explanation! Nungalpiriggal (talk) 12:40, 7 October 2023 (UTC)

Show all lexemes of Vishnu

I want view all lexemes of Vishnu (Q11380), item for this sense (P5137) as the property in the query Sriveenkat (talk) 10:02, 7 October 2023 (UTC)

I found my answer Thanks--Sriveenkat (talk) 14:11, 7 October 2023 (UTC)

Norwegian citizens last names

Can I have a list of all norwegians citizens last names? And how many norwegian persons carrying the same name! I.e Hansen 450, Bøe 12, Haaland 7 and so on... Pmt (talk) 19:54, 23 September 2023 (UTC)

I tried this, but it reached the time limit:

SELECT (COUNT(?lastname) AS ?count) ?lastname ?lastnameLabel WHERE {
  ?person wdt:P27 wd:Q20.

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
  OPTIONAL { ?person wdt:P734 ?lastname. }.
}
GROUP BY ?lastname ?lastnameLabel
ORDER BY DESC(?count)

Try it!

--Cavernia (talk) 20:11, 23 September 2023 (UTC)

@Pmt, Cavernia: A named subquery, such that the label of the family name is selected only once per family name, rather than once per person, is a way to go:

SELECT ?lastnameLabel ?count with {
  SELECT (COUNT(*) AS ?count) ?lastname WHERE {
    ?person wdt:P27 wd:Q20 . hint:Prior hint:runFirst true.
    ?person wdt:P734 ?lastname . 
  } GROUP BY ?lastname } as %i
WHERE
{
  INCLUDE %i
  ?lastname rdfs:label ?lastnameLabel. filter(lang(?lastnameLabel)="nn") 
}
ORDER BY DESC(?count)

Try it!

--Tagishsimon (talk) 21:22, 23 September 2023 (UTC)

Thanks a lot for the query. This tells me that among 63953 norwegianns listed in Wikidata there are 11100 persons with country (P17) not having family name (P734) . Many of those familinames will do exist but family name (P734) have not been created. Doyou know n easy way to handle this more or less automaticly? Unfortunately use of tools like PetScan is not within my knowledge. Breg Pmt (talk) 22:23, 26 September 2023 (UTC)

@Pmt: There's an easy fix for about 6500 of them, currently being implemented. Then we need to create about 3500 new family names - being done now. Then apply those names to the remaining items, which I'll do when the family names quickstatements batch has run. That'll leave us about, say, 300 items with no family names, which we can look at slow-time. Mainly "van this" and "von that" where I couldn't be bothered to think about how to handle the van, von, de, &c. And then a few 'of xyz' style names, and other oddities - Eric Bloodaxe, for instance.

The techniques I'm using are a combination of fairly simple WDQS queries (1) to extract person items with no familyname and (2) to check for familyname items against a set of familyname-strings found in the person item data; and spreadsheet work to identify familynames from person itemLabels and/or match the familynames of a person item with a QId for a familyname item.

I'll ping you again when I'm done. --Tagishsimon (talk) 10:54, 27 September 2023 (UTC)

Tagishsimon Very nice and thank a lot! If the query above was not taken into consideration persons having occupation (P106) and monarch (Q116) then I can reduce the norwegians without last name with 37 re https://w.wiki/7i3R. Breg Pmt (talk) 11:39, 9 October 2023 (UTC)

@Pmt: About 440 left.

SELECT ?person ?personLabel WHERE {
  ?person wdt:P27 wd:Q20.
  FILTER NOT EXISTS { ?person wdt:P734 ?lastname . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],nn". }
}

Try it!

--Tagishsimon (talk) 14:09, 27 September 2023 (UTC)

Might as well show the query used to find the FamilyName item for a set of name strings ... strings are in the VALUES statement, in quotes and with a language specified (needed because the query uses an rdfs:label, which is a monolingual string)

SELECT ?name ?nameLabel
WHERE 
{
  VALUES ?nameLabel {
"Ødemarksbakken"@nn
"Ødeskaug"@nn 
"Odinsen"@nn 
"Oeding"@nn 
"Oequist"@nn 
"Oever"@nn 
"Oeyasaeter"@nn 
"Øfsti"@nn 
"Øfsti"@nn 
"Ofvid"@nn 
"Øgar"@nn 
"Ognjenović"@nn 
"Smith"@nn
}
  ?name wdt:P31 wd:Q101352 .
  ?name rdfs:label ?nameLabel.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

People with no country of citizenship having Norweigian names

Nice work! Another problem is that many people have no country of citizenship. However, people with a Norwegian surname have a high probability of Norwegian citizenship, and a query would be useful to locate them. Would it be possible to extend the current script to take the 100 most used Norwegian surnames and then filter out people without country of citizenship? Or would I have do extract the surnames first and then insert the elements into a new query? --Cavernia (talk) 14:09, 27 September 2023 (UTC)

@Cavernia: There are probably two games to play here, one for people with no P27 but having a P734, and the other, people with no P27 and no P734. Here's an example for the first of these, which looks at the Norweigian names where the count is greater than 99, and finds P27-less people having the same P734.

SELECT ?NC_PEOPLE ?NC_PEOPLELabel ?lastname ?lastnameLabel ?count WITH {
  SELECT (COUNT(*) AS ?count) ?lastname WHERE {
    ?person wdt:P27 wd:Q20 . hint:Prior hint:runFirst true.
    ?person wdt:P734 ?lastname . 
  } GROUP BY ?lastname HAVING (?count > 99) } as %i

WITH { SELECT ?NC_PEOPLE ?lastname ?count WHERE {
  INCLUDE %i
  ?NC_PEOPLE wdt:P734 ?lastname .
  FILTER NOT EXISTS {?NC_PEOPLE wdt:P27 [] .}
  } } as %j
          
WHERE
{
  INCLUDE %j
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],nn,en". }
}
ORDER BY DESC(?count)

Try it!

--Tagishsimon (talk) 15:37, 27 September 2023 (UTC)

The second case - no P27, no P734 - is harder, with ~10 million human items; not enough runtime to get the rdfs:label and filter for strings; an MWAPI search is indicated, but that returns a limited set of results. This sort of solution, but really we'd probably wanting to run the MWAPI query once each, for each name string.

SELECT DISTINCT ?item ?itemLabel ?lastname ?lastnameLabel with {
  SELECT (COUNT(*) AS ?count) ?lastname WHERE {
    ?person wdt:P27 wd:Q20 . hint:Prior hint:runFirst true.
    ?person wdt:P734 ?lastname . 
  } GROUP BY ?lastname HAVING (?count>99) } as %i

WITH { SELECT ?lastname ?lastnameLabel WHERE
{
  INCLUDE %i
  ?lastname rdfs:label ?lastnameLabel. filter(lang(?lastnameLabel)="nn") 
} } as %j

WHERE
{
  INCLUDE %j
          
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:srsearch ?lastnameLabel.
    ?item wikibase:apiOutputItem mwapi:title .
  }
  
  ?item wdt:P31 wd:Q5.
  FILTER NOT EXISTS { ?item wdt:P734 []. }
  FILTER NOT EXISTS { ?item wdt:P27 []. }
  ?item rdfs:label ?itemLabel .  filter(lang(?itemLabel)="en") filter(strends(str(?itemLabel),?lastnameLabel))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],nn,en". }
} ORDER BY ?lastnameLabel ?itemLabel

Try it!

--Tagishsimon (talk) 16:31, 27 September 2023 (UTC)

I suppose the best use of this query might be to set up quickstatements which add P734s to items. --Tagishsimon (talk) 16:52, 27 September 2023 (UTC)

Which I've now done, so we get very few results from that report, now. --Tagishsimon (talk) 18:29, 27 September 2023 (UTC)

Occurences in Norway

Meanwhile in Norway! I am running this query https://w.wiki/7h$9 giving me a lot of event (Q1656682) wich iI don't want to have in the query, ,but this query fails due to timeout? like https://w.wiki/7h$K How to avoid this? Breg @ Pmt (talk) 10:34, 9 October 2023 (UTC)

@Pmt: Two/three main changes 1) using wdt: rather than p:/ps: 2) gearing hints for the compound property path statements telling the query to work from the subject to the object, combined with 3) specifying which clause should be run first so as to limit the number of subject items presented to the rest of the query.

SELECT DISTINCT ?item ?itemLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],nn,en". }
  {
    SELECT DISTINCT ?item WHERE {

      ?item wdt:P17 wd:Q20. hint:Prior hint:runFirst true.
      ?item wdt:P31/wdt:P279* wd:Q1190554. hint:Prior hint:gearing "forward".
      MINUS {
        ?item wdt:P31/wdt:P279* wd:Q1656682. hint:Prior hint:gearing "forward".
      }
    }
   
  }
}

Try it!

--Tagishsimon (talk) 11:13, 9 October 2023 (UTC)

Adding qualifier P69: P528

Hi, I would like to add catalog code (P528) (= enrollment number of the university) to the list Wikidata:University of Tübingen/Listeria/UTübingen alumni (born before 1900). Example: Q122927432#P69.

---

SELECT DISTINCT ?item ?dobyear ?dodyear ?sl WHERE {

 ?item wdt:P69/wdt:P361? wd:Q153978; wdt:P31 wd:Q5; wikibase:sitelinks ?sl .
 OPTIONAL {
   ?item wdt:P569 ?dob .
   BIND(YEAR(?dob) AS ?dobyear) .
 }
 OPTIONAL {
   ?item wdt:P570 ?dod .
   BIND(YEAR(?dod) AS ?dodyear) .
 }
 FILTER(BOUND(?dobyear) && ?dobyear <= 1900) .

}

---

Can someone help? Thanks in advance. --Kolja21 (talk) 06:13, 5 October 2023 (UTC)

@Kolja21: Seems four records have a P528 statement, and then 14(?) items have P528 as a qualifier for educated at (P69); a couple have 2 P528s.

SELECT DISTINCT ?item ?itemLabel ?dobyear ?dodyear ?sl ?P528_statement ?propertyLabel ?P528_qualifier WHERE {

 ?item wdt:P69/wdt:P361? wd:Q153978; wdt:P31 wd:Q5; wikibase:sitelinks ?sl .
 OPTIONAL {
   ?item wdt:P569 ?dob .
   BIND(YEAR(?dob) AS ?dobyear) .
 }
 OPTIONAL {
   ?item wdt:P570 ?dod .
   BIND(YEAR(?dod) AS ?dodyear) .
 }
 FILTER(BOUND(?dobyear) && ?dobyear <= 1900) .
 {?item wdt:P528 ?P528_statement.}
  UNION
 {?statement pq:P528 ?P528_qualifier.
 ?item ?predicate ?statement.
 ?property wikibase:claim ?predicate .}
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de,en". }
} order by ?itemLabel

Try it!

--Tagishsimon (talk) 18:14, 5 October 2023 (UTC)

Hi Tagishsimon, thanks for the quick reply.

• I've changed the SPARQL code in the list Wikidata:University of Tübingen/Listeria/UTübingen alumni (born before 1900).
• The results (enrollment ID) are correct but the list now only shows items that have a catalog code (P528). It only lists 18 instead of ≈ 1300 persons.
• The command UNION seems to be the problem but I'm stuck.

--Kolja21 (talk) 23:07, 5 October 2023 (UTC)

@Kolja21: It's being a right swine, and dying with the "killed by OS for overloading memory" bug, which is a problem Listeria has had for the last 3 years or so. I've tried quite a few variations of SPARQL and Listeria, stripping it back as much as I can, but no joy yet. Too tired now; will have another look tomorrow :( ... feel free to revert to a previous version; your football. --Tagishsimon (talk) 23:59, 5 October 2023 (UTC)

I have seen your attempts and hard work. It comforts me that I'm not alone in having problems with Listeria. --Kolja21 (talk) 00:04, 6 October 2023 (UTC)

PS: I've created Wikidata:University of Tübingen/Listeria/UTübingen alumni (born before 1800) with less items. Might help avoiding the memory problem. --Kolja21 (talk) 00:20, 6 October 2023 (UTC)

 Done Memory problem solved. --Kolja21 (talk) 00:06, 10 October 2023 (UTC)

@Kolja21: Outstanding! Well done. --Tagishsimon (talk) 00:12, 10 October 2023 (UTC)

Thanks. It was your code. --Kolja21 (talk) 00:15, 10 October 2023 (UTC)

Sure, but it's a thankless task banging your head against Listeria, and I was conspicuous by my absence over the last couple of days. Your hard work & perseverance. --Tagishsimon (talk) 00:49, 10 October 2023 (UTC)

fabrication d'un point geographique - make a cordomate

Bonjour, Je cherche à trouver le barycentre de plusieurs ville et l'afficher sur la carte. J'arrive facilement à le calculer, mais je n'obtient pas une valeur de type coordonnées a afficher sur la carte. Je vous met ma solution, et si quelqu'un peut améliorer, c'est avec plaisir.

English (with translate)

Hello I'm trying to find the barycenter of several cities and display it on the map. I can easily calculate it, but I don't get a coordinate value to display on the map. I'll give you my solution, and if anyone can improve, I'll be happy to do it.

SELECT (concat ("Point(",(str(SUM(?lat)/2))," ", (str(SUM(?lon)/2)), ")") as ?point ) WHERE {  
  values ?lieu {wd:Q90  
                wd:Q240798}  
 ?lieu wdt:P625 ?coord .  
 ?lieu p:P625 ?coordinate .  
 ?coordinate psv:P625 ?coordinate_node .  
 ?coordinate_node wikibase:geoLatitude ?lat .  
 ?coordinate_node wikibase:geoLongitude ?lon .  }

Try it!

Silanoc (talk) 07:44, 10 October 2023 (UTC)

Bonjour. https://w.wiki/7irb should work ok for cities in France that is on the same side of the IERS reference meridian, but the math will be wrong if you try this on cities further apart, close to the poles or with coordinates of different signedness. Infrastruktur (talk) 11:45, 10 October 2023 (UTC)

Thank you very much Silanoc (talk) 12:14, 10 October 2023 (UTC)

Map of all places with Pinakes city ID

Hello, I want a map which displays the coordinates of all places with Pinakes city ID (P12062). But I don't know SPARQL yet and after trying for 40 minutes from various examples, I couldn't do it. The "airport cities in Belgium" query was similar but I don't want pages with a specific value to the property, I want XYZPinakes city ID (P12062)any value.

I suspect this is a very simple query for someone with an advanced knowledge of SPARQL. Would anyone be so kind as to help me out? Jonathan Groß (talk) 06:27, 11 October 2023 (UTC)

The easiest possibility is:

#defaultView:Map
#title:Places with Pinakes city ID
SELECT ?item ?itemLabel ?coord
WHERE {
  ?item wdt:P12062 ?id .
  OPTIONAL { ?item wdt:P625 ?coord } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Places with Pinakes city ID

Only 3 places presently don't have coordinate location (P625): Rein (Q10650634), Nivicë (Q16350395), Maria Laach (Q113186315). --Epìdosis 06:40, 11 October 2023 (UTC) P.S. @Jonathan Groß: I guess that somewhere in South America, Africa and Asia the could be a few more Greek manuscripts to discover ;-)

Thank you! I had also expected to find more places in Latin America. And it's hard to imagine that Belarus, Finland and North Africa shouldn't have any Greek manuscripts in their collections. BTW, I fixed one match after looking at this map. Jonathan Groß (talk) 11:18, 11 October 2023 (UTC)

help with wikimedia API

is there any API that can show articles on wiki with most number of interwiki links (descending) with no iw to Farsi wikipedia? I want to know the most important articles that aren't translated into Farsi. thanks for help WASP-Outis (talk) 20:01, 11 October 2023 (UTC)

@WASP-Outis: It's probably not an API query. And it is one I failed to solve in 2018, but which user:Nikki provided the code for. Here it is, for fa.wiki. It only works within limits; here it seems to take 46-52 seconds for all items with >50 sitelinks, so much below that threshold will probably timeout.

select ?item ?itemLabel ?number
with { # select all the numbers of sitelinks
  select ?number (count(*) as ?total) { # we don't use ?total but without it the query times out, so we'll just ignore it
    [] wikibase:sitelinks ?number .
  } group by ?number
} as %numbers
where {
  # include the numbers of sitelinks from above and filter out any numbers lower than n
  # the higher the number here, the fewer items there are to check and the faster this query will run
  include %numbers .
  filter (?number > 50) .

  # find items where the number of sitelinks is one of the remaining numbers, i.e. greater than 40
  ?item wikibase:sitelinks ?number .
  
  # filter out various uninteresting things
  minus { ?item wdt:P31/wdt:P279* wd:Q11266439 . hint:Prior hint:gearing "forward".} # No templates
  minus { ?item wdt:P31/wdt:P279* wd:Q4167836 . hint:Prior hint:gearing "forward".} # No categories
  minus { ?item wdt:P31 wd:Q14204246 } # No project pages
  minus { ?item wdt:P31/wdt:P279* wd:Q15184295 . hint:Prior hint:gearing "forward".} # No modules
  minus { ?item wdt:P31 wd:Q17442446 } # No other Wikipedia/Help pages
 
  # filter out items which have an fa.wiki sitelink
  minus { ?article schema:about ?item ; schema:isPartOf <https://fa.wikipedia.org/> }

  service wikibase:label { bd:serviceParam wikibase:language "en" }
} order by desc(?number)

Try it!

--Tagishsimon (talk) 21:53, 11 October 2023 (UTC)

Should also say, you can amend the filter (?number > 50) . line to say, for instance, filter (?number = 40) . to look at all items with a certain number of sitelinks ... and eventually that technique will probably timeout, too, per the increasing number of candidate articles the query would need to check:

select ?number_of_sitelinks (count(*) as ?number_of_articles) { 
    [] wikibase:sitelinks ?number_of_sitelinks .
  } group by ?number_of_sitelinks
 order by desc(?number_of_sitelinks)

Try it!

--Tagishsimon (talk) 22:03, 11 October 2023 (UTC)

Painting with article in the German WP without qualifier for collection

As a working list I would like to have a list of all paintings (instance of (P31) = painting (Q3305213)) with an article in the German Wikipedia version, that do not have a qualifier for any collection collection (P195). I think this shouldn't be to complicated but I don't have the skills to create it. Thanks, -- Achim Raschka (talk) 15:11, 3 October 2023 (UTC)

@Achim Raschka: You ask for "do not have a qualifier for any collection collection (P195)" but I think maybe you mean "do not have a statement for any collection collection (P195)" ... qualifier has a particlar meaning in the wikidata RDF model. In plain English, "that do not have a P195". If I'm wrong, please let me know.

The query took 55 seconds when I ran it, so dangerously near a timeout.

SELECT ?item ?itemLabel WHERE {
  ?item wdt:P31 wd:Q3305213.
  ?article schema:about ?item ; 
     schema:isPartOf <https://de.wikipedia.org/> .
  FILTER NOT EXISTS {?item wdt:P195 [] . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de,en". }
}

Try it!

Might be slightly faster as a named subquery:

SELECT ?item ?itemLabel WITH { SELECT ?item WHERE {
  ?item wdt:P31 wd:Q3305213.
  ?article schema:about ?item ; 
     schema:isPartOf <https://de.wikipedia.org/> .
  FILTER NOT EXISTS {?item wdt:P195 [] . }
  } } as %i
WHERE
{
  INCLUDE %i
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de,en". }
}

Try it!

--Tagishsimon (talk) 19:34, 3 October 2023 (UTC)

Thanks, this works for me; less than expected. -- Achim Raschka (talk) 05:47, 12 October 2023 (UTC)

socialist state (Q842112)

A administrator could add the french wikipedia page "état socialiste" (redirection vers "état communiste") on this element ? Thanks, Legonin (talk) 17:17, 14 October 2023 (UTC)

@Legonin: That seems a questionable ask. The FR https://fr.wikipedia.org/wiki/%C3%89tat_communiste artice definition is "a state in which a Party claiming to be communist exercises a monopoly on the management of public affairs"; a socialist state does not necessarily meet that definition, nor does the article offer a definition of nor a discussion of socialist states. So the redirect would be inappropriate / wrong.

And besides, this is the Request a Query page. --Tagishsimon (talk) 23:29, 14 October 2023 (UTC)

Thank you for your reply. In that case, what's the point of this page? Where can I ask an administrator to modify a protected page? Legonin (talk) 07:08, 15 October 2023 (UTC)

@Legonin: You seem to be overlooking the point that your request is inappropriate & wrong. This page is mainly about SPARQL queries for wikidata. There is Wikidata:Administrators' noticeboard and there is Wikidata:Project chat. I live in hope that neither will humour your request. --Tagishsimon (talk) 09:40, 15 October 2023 (UTC)

Extracting a range of QID

Is there a way to extract all of the items whose Wikidata item ID contains a certain string? For a (randompaged) example, all items whose ID start with Q766512? I'm aware of Special:AllPages, but I would like to include this in another query.

Thanks! Weasel (talk) 03:17, 15 October 2023 (UTC)

What are you trying to achieve? Chances are there is a better way. Infrastruktur (talk) 10:33, 15 October 2023 (UTC)

missing item for this sense in Tamil lexemes

I want view missing item for this sense in Tamil lexemes. Should a column shows the sense text in English and tamil. Sriveenkat (talk) 23:31, 13 October 2023 (UTC)

Like this? https://w.wiki/7o3d Infrastruktur (talk) 17:26, 15 October 2023 (UTC)

@Infrastruktur Looks Great Thanks! :) Sriveenkat (talk) 01:12, 16 October 2023 (UTC)

Count by US state

I want to list company headquarters by US state. Most cities are located in the administrative territorial entity (P131) of a county which are located in the administrative territorial entity (P131) of a state, then the query will be like this:

#defaultView:BarChart
SELECT ?entity2Label (COUNT(?entity2) AS ?count) WHERE {
  ?item wdt:P31 wd:Q46970.
  ?item wdt:P159 ?hq.
  ?hq wdt:P131 ?entity.
  ?entity wdt:P131 ?entity2 .
  ?entity2 wdt:P17 wd:Q30 .

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
}
GROUP BY ?entity2Label
ORDER BY ?count

Try it!

The problem is, some cities are placed directly under the state, then I get United States of America (Q30). Some headquarters are places within a city, which makes the query return the county. How could I get the query to return the state, no matter how meny levels deep it must search? --- Cavernia (talk) 14:26, 16 October 2023 (UTC)

@Cavernia: Use a property path - in this case wdt:P131* - which says 'look up the P131 class tree as many hops as is required', combined with ?entity wdt:P31 wd:Q35657. which says the ?entity must be a US State. I've also added a couple of hints to make the thing run faster; requiring a clause which produces a limited set of items to be run first; and then specifying that the wdt:P131* business should start from the subject and work towards the object, rather than the other way around. Have also removed the P17 clause, since US states are by definition within Q30.

#defaultView:BarChart
SELECT ?entityLabel (COUNT(?entity) AS ?count) WHERE {
  ?item wdt:P31 wd:Q46970. hint:Prior hint:runFirst true.
  ?item wdt:P159 ?hq.
  ?hq wdt:P131* ?entity. hint:Prior hint:gearing "forward".
  ?entity wdt:P31 wd:Q35657.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
}
GROUP BY ?entityLabel
ORDER BY ?count

Try it!

--Tagishsimon (talk) 16:46, 16 October 2023 (UTC)

@Tagishsimon: Excellent! Is there anywhere to learn how to use the hints? --Cavernia (talk) 18:38, 16 October 2023 (UTC)

@Cavernia: I don't think so. I've picked up a few over the years; my dim understanding:

• hint:Prior hint:rangeSafe true. - all of the data in the objects in the preceding triple are of the same sort. See query optimization#Fixed values and ranges
• hint:Query hint:optimizer "None". - make the query engine run the clauses in the order you list them, rather than deciding where to start
• hint:SubQuery hint:optimizer "None" - ditto, but for a named subquery
• hint:Prior hint:gearing "forward". - as above: when dealing with a compound property path, tell the optimiser to run from subject to object (or if "reverse", from object to subject)
• hint:Prior hint:runFirst true. - run this clause first. Need to wrap compound property path clauses within braces {} & the hint outside.
• hint:Prior hint:runLast true. - run this clause last

There is more here - https://github.com/blazegraph/database/wiki/QueryHints - and here - https://blazegraph.com/database/apidocs/com/bigdata/rdf/sparql/ast/QueryHints.html and possibly here - https://github.com/blazegraph/database/wiki/Explain but it's a bit inpenetrable. --Tagishsimon (talk) 18:50, 16 October 2023 (UTC)

Oh, also, if you've not met property paths properly, they're a way of life in SPARQL - https://www.w3.org/TR/sparql11-query/#propertypaths --Tagishsimon (talk) 18:54, 16 October 2023 (UTC)

People who are dead aggregated by decade/year

I wanted to create a counts by decade or year of death of a person.

I created this query but it unfortunately it times out

SELECT ?year (COUNT(?human) AS ?numHumans)
WHERE
{
  ?human wdt:P31 wd:Q5;
         wdt:P570 ?dateOfDeath . hint:Prior hint:rangeSafe true.
  BIND(YEAR(?dateOfDeath) AS ?year) 
}GROUP BY ?year 
ORDER BY DESC(?year)

Try it!

and by decade (also times out)

SELECT ?decade (COUNT(?human) AS ?numHumans)
WHERE
{
  ?human wdt:P31 wd:Q5 .
  ?human wdt:P569 ?dateOfDeath .
  BIND(FLOOR(YEAR(?dateOfDeath) / 10) * 10 AS ?decade) 
}GROUP BY ?decade 
ORDER BY DESC(?decade)

Try it!

I found that queries using filter can be run in time but it will take too long to make by hand for me

SELECT (COUNT(?human) AS ?numHumans)
WHERE
{
  ?human wdt:P31 wd:Q5;
         wdt:P570 ?dateOfDeath . hint:Prior hint:rangeSafe true.
  FILTER("2000-00-00"^^xsd:dateTime <= ?dateOfDeath &&
         ?dateOfDeath < "2009-00-00"^^xsd:dateTime)
}

Try it!

Is there a way to optimise the first query? Lame lexem (talk) 16:45, 17 October 2023 (UTC)

@Lame lexem: Probably not. ~10 million humans, many with DoD. Qlever has the ability to run a basic query (albeit the ORDER does not seem to work) - https://qlever.cs.uni-freiburg.de/wikidata/zGrX7S - but does not seem to be able to deal with time precision, so the result will include DoDs having century of millenium dates. --Tagishsimon (talk) 17:00, 17 October 2023 (UTC)

I think the query from qlever might be alright for my case except for P569 must be P570 as I want number of people who died by year(?). Thank you for you time! Lame lexem (talk) 17:42, 17 October 2023 (UTC)

@Lame lexem: Ah yes. That would be https://qlever.cs.uni-freiburg.de/wikidata/tgMJCA --Tagishsimon (talk) 18:50, 17 October 2023 (UTC)

Commons Query Service: Most common depicts without labels

I need a query that gives back the most common Wikidata items used in the Depicts statements on Commons, that don't have a label in a certain language (for me it would be Spanish, but would be great to set it up for any language). For example, I recently discovered that bamboo fence (Q11599055) didn't have a Spanish label, and thus a search on commons didn't return that value, 19:21, 17 October 2023 (UTC) Sadads (talk) 19:21, 17 October 2023 (UTC)

Another way to solve this would be to identify frequently used items with Depicts that don't have labels, Sadads (talk) 19:22, 17 October 2023 (UTC)

@Sadads: To avoid timeouts, I've looked at only the first 5 million depicts statements. So, somthing like this, in which there are quite a few parameters you can play with to fish for missing labels, including offset and limit in the slice service (which determines which set of depicts statements will be looked at) and then the having clause which dictates the size of qualifying ?count which is submitted to WD so that we can find out if labels exist. Interesting to note that where a WD item has been redirected - e.g. tram (Q5641) - its use has not been updated in Commons to point at the redirect destination tram (Q3407658) as would be the case on WD. But the short answer is, missing es labels are quite far down the list of most frequently used depicts statements (e.g. make 'filter not exists' into 'optional' if you want to see those ?depicts that do have labels) :

SELECT ?depicts ?count ?depictsLabel_en ?depictsLabel_es WITH {

SELECT ?depicts (count(?file) as ?count) WHERE
{
   SERVICE bd:slice {
?file wdt:P180 ?depicts.
    bd:serviceParam bd:slice.offset 0 . # Start at item number (not to be confused with QID)
    bd:serviceParam bd:slice.limit 5000000 . # List this many items
  }
} group by ?depicts having (?count>100 && ?count<4000000) } AS %I
WHERE
{
  INCLUDE %I
  SERVICE <https://query.wikidata.org/sparql> {
    filter not exists {?depicts rdfs:label ?depictsLabel_es FILTER (lang(?depictsLabel_es) = 'es') }  
    OPTIONAL {?depicts rdfs:label ?depictsLabel_en FILTER (lang(?depictsLabel_en) = 'en') }
  }
} order by desc(?count)

Try it!

--Tagishsimon (talk) 13:34, 18 October 2023 (UTC)

Very Cool @Tagishsimon, do you mind if I add this to the example queries for commons, since its the kind of maintenance that I expect other people will want to do, Sadads (talk) 14:12, 18 October 2023 (UTC)

@Sadads: Feel free; t/y --Tagishsimon (talk) 14:52, 18 October 2023 (UTC)

Global "ntsamr"-pattern spambot filter

I am tryping to publish my User Data page but it's saying This action has been automatically identified as harmful, and therefore disallowed. If you believe your action was constructive, please inform an administrator of what you were trying to do. A brief description of the abuse rule which your action matched is: Global "ntsamr"-pattern spambot filter Naman Bagdiya (talk) 12:20, 18 October 2023 (UTC)

@Naman Bagdiya: Maybe take this to Wikidata:Administrators' noticeboard. --Tagishsimon (talk) 13:04, 18 October 2023 (UTC)

Hierarchical list of subclasses

Dear all, is there a way to make SPARQL not only list subclasses of an entity, but rather depict how they are connected? While searching for subclass of (P279)manuscript (Q87167) (there are 187 at the moment) I am getting lots of results that are subclasses of subclasses of manuscript (Q87167), or perhaps triple or quadruple subclasses. I would like a more efficient way of finding out the current "taxonomy", however messy it is.

My original query:

SELECT DISTINCT ?item ?itemLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
  {
    SELECT DISTINCT ?item WHERE {
      ?item p:P279 ?statement0.
      ?statement0 (ps:P279/(wdt:P279*)) wd:Q87167.
    }
    LIMIT 200
  }
}

Try it!

As always, your help is very much appreciated. Cheers, Jonathan Groß (talk) 06:52, 21 October 2023 (UTC)

Never mind, I found this tool: https://wikidata-todo.toolforge.org/tree.html?q=87167&rp=279 Jonathan Groß (talk) 09:44, 21 October 2023 (UTC)

There is also this "Wikidata Graph Builder" tool which I find often useful: https://angryloki.github.io/wikidata-graph-builder/?item=Q87167&property=P279&mode=reverse ---MisterSynergy (talk) 11:18, 21 October 2023 (UTC)

Norwegian mayors elected in the 2023 local elections

May I have a list of person holdng the position mayor of a place in Norway (Q87008012) elected in the Municipal and county council elections 2023 in Norway. The persons will all have the qualifier start time (P580) for position held (P39) with mayor of a place in Norway (Q87008012) and subclasses. date of birth (P569), residence (P551) and member of political party (P102) should also be appriciated Pmt (talk) 21:23, 23 October 2023 (UTC)

@Pmt: This?

SELECT ?item ?itemLabel ?start ?position ?positionLabel ?dob ?residence ?residenceLabel ?party ?partyLabel
WHERE 
{
  ?item p:P39 ?stat . 
  ?stat ps:P39 ?position . 
  ?position wdt:P279 wd:Q87008012.
  ?stat pq:P580 ?start .
  filter(year(?start)=2023)
  optional {?item wdt:P569 ?dob .}
  optional {?item wdt:P551 ?residence .}
  optional {?item wdt:P102 ?party .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],nn,en". }
}

Try it!

--Tagishsimon (talk) 21:52, 23 October 2023 (UTC)

Thanks a lot. Works perfect Pmt (talk) 18:40, 24 October 2023 (UTC)

Ships with multiple operators that do not have multiple official names

Ships often change name when they change operator, and this is currently weakly modelled with label and aliases. I'm trying to formalise this using official name (P1448) with operator (P137) qualifiers. USS Chesapeake (Q1300790) is an example.

To help with this I'd like a list of items which are instances of frigate (Q161705), have 2 or more operator (P137), but don't have a matching official name (P1448) with an operator (P137) qualifier for each of them. Vicarage (talk) 05:50, 21 October 2023 (UTC)

@Vicarage: This seems to be the basic list.

SELECT distinct ?item ?itemLabel WHERE {
  ?item wdt:P31 wd:Q161705 .
  ?item wdt:P137 ?operator1.
  ?item wdt:P137 ?operator2.
  filter (str(?operator1) != str(?operator2) )
  filter not exists {?item p:P1448/pq:P137 ?operator1 . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }  
} order by ?itemLabel

Try it!

--Tagishsimon (talk) 11:43, 21 October 2023 (UTC)

excellent, though I see with 166 items that are just frigates, I've got lots of things to fix! Vicarage (talk) 11:59, 21 October 2023 (UTC)

Worse than that, we can track your progress. 111 just now, so that's not too shabby. Carry on. --Tagishsimon (talk) 13:47, 26 October 2023 (UTC)

Properties of Paintings

Painting (i.e., "visual artwork, surface artistically covered with paint") is Q3305213. I want to download Wikimedia's paintings and certain related data fields (probably artist, title, and date) within the Painting dataset. But this is not the nature of Wikidata's Properties concept. That concept seems to consist of hit-or-miss groupings of Paintings (e.g., Paintings by Gustav Klimt). How can I identify the data fields available for Paintings, or what kind of query should I be trying to construct for this purpose? Thanks in advance. Raywood1 (talk) 01:15, 26 October 2023 (UTC)

@Raywood1: If I understand you, something like this, which shows the properties used as statements in the (first 200,000) items which have P31=Q3305213.

SELECT ?property ?propertyLabel ?propertyType ?count WITH { 
  SELECT ?property (count(distinct ?item) as ?count)  WHERE {
    SERVICE bd:slice {
      ?item wdt:P31 wd:Q3305213.
      bd:serviceParam bd:slice.offset 0 . # Start at item number (not to be confused with QID)
      bd:serviceParam bd:slice.limit 200000 . # List this many items
    }
    hint:Prior hint:runFirst true .
    ?item ?predicate ?value .
    ?property wikibase:directClaim ?predicate .
  } group by ?property  } as %i
WHERE
{
  INCLUDE %i
  ?property wikibase:propertyType ?propertyType .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
} order by desc(?count)

Try it!

--Tagishsimon (talk) 01:32, 26 October 2023 (UTC)

Thank you for that.

But now I am puzzled. The results consist of 635 apparently unique Properties, none of whose labels seem to match the fields of interest in Wikimedia. A sample Wikimedia page names fields such as Artist, Title, and Date. Nothing exactly matching those three appears in the output of this code. There is a partial exception for Title, but the number of records stated in that case is insufficient for the number of paintings in the database.

I seem to be misunderstanding something basic about the Wikidata structure. 2603:8081:4B01:5CBA:9904:B7EA:9463:A316 06:37, 26 October 2023 (UTC)

The information in your example - A sample Wikimedia page - is held in a template on the text page associated with an image in wikimedia commons. The properties in the above report pertain to items about paintings on wikidata. So those are two completely different things. The wikidata query engine has little or no ability to report on information held in templates on text pages. Wikimedia commons has its own structured data, but it is less well developed than that of wikipedia; in your Munch example the structured data amounts to three triples with predicates 'depicts', 'digital representation of' and 'main subject' each taking the argument 'woman picking fruit'. (And to try to be clearer still, you are dealing with two entirely different and mostly separate things here: (1) wikidata, essentially an RDF triplestore, or in other words, a database; and (2) Commons, a repository of mainly image files. Wikidata has information on paintings, and may point to an instance of the painting on Commons. Commons, meanwhile, has textual descriptions of its images, which are most often structured by templates which provide a table structure with defined rows for various facets - title, date, creator, &c), and Commons has (some) structured data on images in RDF format, distinct from that held on wikidata, albeit it draws on wikidata for certain of its values.)

If it is your aim to download images from Commons, and it is your wish to download corresponding information from the text page associated with the image, then you're probably going to have to put together your own scraping code. Presumably many of the images will use the Template:Artwork, so study of that will give you some clues as to the 'fields' likely to be found in the text page. Good luck.

There are two and a half other routes available to you, which are to work from wikidata, or find the wikidata item corresponding to the commons image (if there is such an item), or to see what commons structured data has to offer. Again, working from your example, wikidata has the item record Women Picking Fruit (Q18891458) and that a) points to the commons file and b) has a handful of structured data. It would be well possible to create a report on wikidata items for paintings that point to commons images, such that you could abstract the wikidata information on the image, including the commons filename, which would allow you to download the corresponding commons image. --Tagishsimon (talk) 08:30, 26 October 2023 (UTC)

Mapping toponyms organized in their linguistic family

Amazing people, I am trying to classify and map municipality of Colombia (Q2555896) into to their language family (Q25295). I understand that every city and town in wikidata, that has the municipality of Colombia as instance, will be contained inside the list. These toponym (Q7884789) contains important information about the linguistic root that will help classify each toponym inside their corresponding linguistic family. There is a property called native label (P1705) that may work for this, but there is no consistency in how this property is used, or maybe, the thing that confuses me the most is that I can't find a connection between the toponym and some data that tells something about its language family.

I think that there is another issue with where to put or where to organize the linguistic information of toponyms. For example, Chía https://es.wikipedia.org/wiki/Ch%C3%ADa_(Colombia) in the Spanish version, when referring to its linguistic family, it writes about "Toponimia", while in the English version, when referring to its language family Chibchan (Q520478), that information is written inside the "Etymology" group. I don't understand how to handle these variations with SPARQL. Actually, I am new into SPARQL and I don't understand how to formulate a proper query with these topics and areas of knowledge.

I also found the Lexicographical data https://www.wikidata.org/wiki/Wikidata:Lexicographical_data/Documentation that can open another way to organize toponyms into linguistic properties, but I am not sure if that can work.

Also, a bonus question, I don't understand why some items like Chía (Q1093102) can be edited, and others like Bogotá (Q2841) that can't.

I thank you all for reading and, if you can help me, for answering. Duityors (talk) 00:55, 25 October 2023 (UTC)

@Duityors: I fear that Wikidata may have little or none of the etymological information on toponyms that you seek. In the Chía example you cite, the information of interest is in the ES Wikipedia article, and is not in Wikidata. There is no SPARQL query which will grab the information from the wikipedia article. Wikidata does have, as you note, native label (P1705), but for municipality of Colombia (Q2555896) items all of the data seems to be in Spanish and so WD does not point to, for instance, the Muisca language of Chía; see the query below which deals only in municipalities that have P1705 data.

You are right to point at wikidata's Lexicographical data; almost certainly this is where toponym to linguistic family information should be found. But a) I tend to doubt that there is anything at all there, right now, for the municipality toponyms and b) I'm afraid I have never interested myself in the Lexicographical data structure and so cannot be your guide there.

Finally, some WD items are protected such that only editors with a certain number of edits and who have had accounts for a certain amount of time can edit them. I don't think either figure is very great, so, stick around, do more edits, you will be able to edit both articles.

SELECT ?item ?itemLabel ?P1705 ?lang
WHERE 
{
  ?item wdt:P31 wd:Q2555896.
  ?item wdt:P1705 ?P1705. bind(lang(?P1705) as ?lang) 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 01:16, 25 October 2023 (UTC)

@Tagishsimon, thank you for the query and for your answers. In short words, you are telling me that the query and classification that I am looking for is possible, only if I manage a model to link each toponym to a etymological or toponymic value?

What I think could work is to edit the following info in WD:

1. Make shure each toponym is under the municipality of Colombia instance.

2. Add a new Statement of "native label" to each toponym that doesn't have.

3. Add, inside each "native label" property, the value of "language family" or directly put, in the case of Chía, the Chibchan (Q520478) value inside it. (Is this even possible?)

I think this method still has problems. I don't understand how can a word, in this case, a toponym, can hold linguistic information. Probably this concerns more to the Lexeme area. Is there a place where I can ask all these existential questions?

Thank you again Tagishsimon! Have a nice day! Duityors (talk) 13:27, 25 October 2023 (UTC)

@Duityors: Yes, that's a fairly good summary; you will need to find a way to build a model of the linguistic family of the tomonyms. However there is a constraint which you will have to work around, which is that properties such as official name (P1448), native label (P1705) and name (P2561) are of the wikidata type 'monolingual string', requiring that a language be associated with them; and the set of languages that can be associated with them is not as granular as you would wish - so Chibchan (Q520478) is not available, for instance. As a suggestion, you can choose 'unsupported language (mis)' and then qualify the statement with language of work or name (P407) taking the value Chibchan (Q520478).

Perhaps go to Wikidata:Project chat to ask questions about lexeme support; it is perhaps the most likely place for a question to be noticed. --Tagishsimon (talk) 00:28, 26 October 2023 (UTC)

@Tagishsimon, thank you again for your collaboration. I've been trying to find a way to link the linguistic info with each toponym but I haven't found a proper way that also respects the fields of knowledge and categories that science has created to organize this data . I am using the property you mention (language of work or name (P407)), applying it to each municipality and adding a specific value of a language family to each. I still think that the "Language of work or name" is not exactly a property linked with onomastics, toponymy nor language studies, but for now, it works for me. I think there should be a property that carries the "linguistic data" of a word or in this case, a place name. Probably, I will mention this in the Property Proposal discussion.

In the Lexemes chat nobody answered.

This is the SPARQL I did, based in the one you shared. I am only working with 11 item for now.

SELECT ?item ?itemLabel ?linguisticFamily
WHERE 
{
 ?item wdt:P31 wd:Q2555896.
 ?item wdt:P407 ?linguisticFamily.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
}

Try it!

Please, how can I add the coordinate location (P625) to each toponym that can be found on this list? After adding that, is it possible to visualize this information in a map? I have tried bu I can't figure it out. Thank you!

Thank you again, and have a nice day! Duityors (talk) 15:34, 30 October 2023 (UTC)

Populating the operators of ship classes from the operators of individual ships

I want list destroyers which have an operator which does not exist in the vessel class's list of operators, so I can propagate the information. The problem is my query below is it finds USS Converse (Q2469026) which was operated by the Spanish Navy (Q676404) and reporting it even though its class Fletcher-class destroyer (Q1428155) says the same thing. I think this is because the class has also a different operators which are confusing it, and me.

SELECT DISTINCT ?item ?itemLabel ?class ?classLabel ?operatorLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
  {
    SELECT DISTINCT ?item ?class ?operator WHERE {
      ?item wdt:P31 wd:Q174736.
      ?item wdt:P137 ?operator.
      ?item wdt:P289 ?class.
      MINUS {?class wdt:P137 ?operator}
    }
    LIMIT 100
  }

Try it!

Vicarage (talk) 09:09, 27 October 2023 (UTC)

@Vicarage: Although I'm not entirely sure what your report is supposed to be doing, the fail on USS Converse (Q2469026) is that its class shows the US Navy as an operator with preferred rank, so MINUS {?class wdt:P137 ?operator} sees only the US navy and not the Spanish navy. Changing that clause to p:P137/ps:P137 might fix it, if my supposition is right. (And thinking on, you might want to use p:P137/ps:P137 on the item clause - it returns all statements, not merely BestRank statements.)

SELECT DISTINCT ?item ?itemLabel ?class ?classLabel ?operatorLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
  {
    SELECT DISTINCT ?item ?class ?operator WHERE {
      ?item wdt:P31 wd:Q174736.
      ?item wdt:P137 ?operator.
      ?item wdt:P289 ?class.
      MINUS {?class p:P137/ps:P137 ?operator .}
    }
    LIMIT 100
  }
  }

Try it!

--Tagishsimon (talk) 10:03, 27 October 2023 (UTC)

I did wonder if it was the preferred rank upsetting things. Always tricky for ships classes, if you run 200 destroyers, but your enemy captures one and rebadges it for a couple of years, you might want a preference, but its a bit mean if some little country bought one as their pride and joy! I've been adding country of origin (P495) (often based on manufacturer (P176)) to give more selection control. Thanks for looking into this. Vicarage (talk) 10:14, 27 October 2023 (UTC)

I get the impression there's a lot of misuse of rank on WD. --Tagishsimon (talk) 11:33, 27 October 2023 (UTC)

List only the highest value

I would like to have this query returning only the highest number of followers for each person:

SELECT DISTINCT ?personLabel ?followers
WHERE
{
?person wdt:P106 wd:Q937857 .
?person wdt:P8687 ?followers .
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en" .
  }
} GROUP BY ?personLabel ?followers
ORDER BY DESC (?followers)

Try it!

And if possible an extra column showing which channel the number is derived from. Cavernia (talk) 22:33, 28 October 2023 (UTC)

@Cavernia: This, I think:

SELECT ?personLabel ?followers ?channelLabel WHERE {
  ?person p:P8687 ?stat . 
  ?stat ps:P8687 ?followers .
  ?stat ?predicate ?value . 
  ?property wikibase:qualifier ?predicate .
  ?property wdt:P1629 ?channel .
  filter not exists {wd:P585 wikibase:qualifier ?predicate.}
  {
    SELECT DISTINCT ?person (max(?followers_) as ?followers) WHERE
    {
    ?person wdt:P106 wd:Q937857 .
    ?person wdt:P8687 ?followers_ .
    } GROUP BY ?person
  } hint:Prior hint:runFirst true .
  SERVICE wikibase:label {bd:serviceParam wikibase:language "en" .}
} ORDER BY DESC (?followers)

Try it!

--Tagishsimon (talk) 23:26, 28 October 2023 (UTC)

@Tagishsimon:Beautiful, thanks a lot! --Cavernia (talk) 08:55, 31 October 2023 (UTC)

SPARQL queries to identify different categories of people contained within a set of interviews

Hello, I’m getting stuck with some SPARQL queries and wondering if someone could help me learn how to create them.

They all relate to this project I’m currently working on https://www.wikidata.org/wiki/Wikidata:WikiProject_LSESuffrageInterviewsProject

Firstly, I was looking to create a list of main subjects of the interviews, but only where the main subject is a person.   

I’ve created this query - https://w.wiki/7yks - but it contains all the subjects, and I was hoping to just list people who are included as a subject.  Is that possible?

Next I was hoping to create SPARQL queries to list people who fall into different categories within the project.

People who were notable in the suffrage movement and were interviewed about themselves will have an interview where their name is in the P710 participant, object has role interviewee and is also in the P921 main subject statement.  Is it possible to write a SPARQL query that can do that?  I made a start, but don’t know how to continue.  https://w.wiki/7ymF

People who were notable in the suffrage movement where a friend or family member was interviewed about them will have an interview where their name is in the P921 main subject statement, but that name is not the same as the name in the P710 participant, object has role interviewee.

And conversely I was hoping to create a list of people who were not notable in the suffrage movement, but were interviewed about a friend or family member who was.

Those people will have an interview where their name is in the P710 participant, object has role interviewee, but is not in the P921 main subject.

Is there a way to write queries that would pick the data up in this way?

Any advice will be gratefully received. Thanks!

HelsKRW (talk) 11:35, 31 October 2023 (UTC)

@HelsKRW: All well possible; these, probably:

1. List of main subjects of the interviews, but only where the main subject is a person. 

SELECT DISTINCT ?item ?itemLabel ?person ?personLabel ?familynameLabel WHERE {
  
  ?item wdt:P5008 wd:Q117322976;     # item on focus list of the LSESuffrageInterviewsProject
    wdt:P31 wd:Q178651 ;             # item is an interview
    wdt:P921 ?person .               # main subject...
  ?person wdt:P31 wd:Q5.           # ...is a person
  OPTIONAL { ?person wdt:P734 ?familyname }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} ORDER BY (?familynameLabel)

Try it!

People who were notable in the suffrage movement and were interviewed about themselves will have an interview where their name is in the P710 participant, object has role interviewee and is also in the P921 main subject statement.

SELECT DISTINCT ?item ?itemLabel ?person ?personLabel ?familynameLabel WHERE {
  
  ?item wdt:P5008 wd:Q117322976;     # item on focus list of the LSESuffrageInterviewsProject
    wdt:P31 wd:Q178651 .             # item is an interview
   ?item p:P710 ?statement .         # item has a participant statement
   ?statement ps:P710 ?person .      # participant is ?person
   ?statement pq:P3831 wd:Q55534929. # qualifier object has role is interviewee
   ?item wdt:P921 ?person  .         # main subject is same as participant
   OPTIONAL { ?person wdt:P734 ?familyname }
   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} ORDER BY (?familynameLabel)

Try it!

List of people who were not notable in the suffrage movement, but were interviewed about a friend or family member who was; those people will have an interview where their name is in the P710 participant, object has role interviewee, but is not in the P921 main subject.

I've thrown in the subject as well, but left the family name as the interviewee.

SELECT DISTINCT ?item ?itemLabel ?person ?personLabel ?familynameLabel ?subjectLabel WHERE {
  
  ?item wdt:P5008 wd:Q117322976;     # item on focus list of the LSESuffrageInterviewsProject
    wdt:P31 wd:Q178651 .             # item is an interview
   ?item p:P710 ?statement .         # item has a participant statement
   ?statement ps:P710 ?person .      # participant is ?person
   ?statement pq:P3831 wd:Q55534929. # qualifier object has role is interviewee
   filter not exists {?item wdt:P921 ?person .}        # main subject is not same as participant
   ?item wdt:P921 ?subject .         # main subject is ...
   ?subject wdt:P31 wd:Q5 .          # ...a person
   OPTIONAL { ?person wdt:P734 ?familyname }
   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} ORDER BY (?familynameLabel)

Try it!

--Tagishsimon (talk) 14:00, 31 October 2023 (UTC)

Thank you so much @Tagishsimon. HelsKRW (talk) 15:14, 31 October 2023 (UTC)

I was so close on the first one I feel that I should have been able to get that final step! Useful learning for next time, thank you. HelsKRW (talk) 15:14, 31 October 2023 (UTC)

Yes. It's not obvious that you can do that sort of thing until it is. The interview dataset looked excellent; exemplary work again. --Tagishsimon (talk) 15:18, 31 October 2023 (UTC)

Thank you! It's an experimental project and has taken a lot to get my head round the data structure. Just made a late change to my original plan last week in order to be able to pull distinct categories of people out of the dataset! We're still working on it, so a bit more data to be added and then I'll move onto next stages. It's been really interesting and I hope the data will be really useful once it's all pulled together. HelsKRW (talk) 15:24, 31 October 2023 (UTC)

There a set of UK diplomats interviewing UK diplomats data somewhere which I've used before. I may copy your model. Fascinating subject-matter for those like me interested in this aspect of government. --Tagishsimon (talk) 15:30, 31 October 2023 (UTC)

I could do with a like button! That sounds really interesting. I had a look at some other interview data before I started modelling ours, but I didn't come across that set. HelsKRW (talk) 15:34, 31 October 2023 (UTC)

The British Diplomatic Oral History Programme - https://bdohp.chu.cam.ac.uk/ --Tagishsimon (talk) 15:45, 31 October 2023 (UTC)

For people who were notable in the suffrage movement and were interviewed about themselves, where they are both the participant, and the main subject statement in the Qid metadata - I'm just trying to get my head round how the SPARQL is pulling out those double instances of names in both statements - is it by identifying the participant as a statement, then narrowing down that statement to a person - and is it then picking up that same person in the P921 ?person? HelsKRW (talk) 15:23, 31 October 2023 (UTC)

Yes and no. A row of data qualifies to be displayed in the output table if the value of ?person associated with P710 is the same as the value of ?person associated with P921. And so here we did not need to check if the main subject was a Q5 human b/c the participant had to be a Q5 human. --Tagishsimon (talk) 15:27, 31 October 2023 (UTC)

I'm with you...thank you for explaining. I'm trying to learn more about the SPARQL as I go so that I'll be able to get further by myself next time, so it's really helpful to get my head round it. HelsKRW (talk) 15:36, 31 October 2023 (UTC)

Like. --Tagishsimon (talk) 15:45, 31 October 2023 (UTC)