Wikidata:Request a query/Archive/2023/06

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

Numbers and sourcing circumstances (P1480) = circa (Q5727902)

How to find numbers statements having sourcing circumstances (P1480) = circa (Q5727902) such as in https://www.wikidata.org/wiki/Q4110412#P1590 Bouzinac ^{💬●✒️●💛} 08:23, 5 June 2023 (UTC)

SELECT ?item ?itemLabel 
WHERE 
{
  ?item p:P1590 ?stat . 
  ?stat pq:P1480 wd:Q5727902 . 
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 09:06, 5 June 2023 (UTC)

@Bouzinac: This seems to be the query for the general case of a quantity property having a qualifier of sourcing circumstances (P1480) = circa (Q5727902).

SELECT ?item ?itemLabel ?property ?propertyLabel ?value
WHERE 
{
  ?item ?predicate ?stat . 
  ?stat pq:P1480 wd:Q5727902 . hint:Prior hint:runFirst true.
  ?property wikibase:claim ?predicate.
  ?property wikibase:statementProperty ?statementProperty .
  ?stat ?statementProperty ?value .
  ?property wikibase:propertyType wikibase:Quantity .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 19:04, 6 June 2023 (UTC)

Wow ~5500 results... I alone honestly cannot correct circa (Q5727902) to being approximately (Q60070514). Thanks anyway ! Bouzinac ^{💬●✒️●💛} 19:24, 6 June 2023 (UTC)

Qualifier hashes

Hello, why this works:

SELECT ?item ?hq ?q
    WHERE {
     VALUES ?item {wd:Q39183}
      ?item p:P159 ?st . 
      ?st ps:P159 ?hq.
      ?st pqv:P580 ?q . 
    }

Try it!

and this does not:

SELECT ?item ?hq ?q
    WHERE {
     VALUES ?item {wd:Q39183}
      ?item p:P159 ?st . 
      ?st ps:P159 ?hq.
      ?st pqv:P281 ?q . 
    }

Try it!

I need to retrieve the qualifier hashes from the 2nd query... Thanks all for help. Vojtěch Dostál (talk) 11:27, 8 June 2023 (UTC)

According to the documentation "not all aspects of the data model are represented". Some simple datatypes such as strings apparently does not have a value node representation of the data, because this would just take up more space for no particular reason. So switching out "pqv" with "pq" will fix the second query. Infrastruktur (talk) 20:20, 11 June 2023 (UTC)

Huh! :( Vojtěch Dostál (talk) 19:55, 13 June 2023 (UTC)

Find/highlight possible duplicates

Is there a way to highlight possibles duplicates for instance things that share a same date inside a long list ? Eg inside https://w.wiki/6pRB I can find them in Excel but a more direct way of finding would be appreciable Bouzinac ^{💬●✒️●💛} 18:26, 11 June 2023 (UTC)

@Bouzinac: You can group items with the same date like this, if it's any help:

SELECT ?date (group_concat(?accident_ferroviaire; separator=";") as ?accidents_ferroviaires)  WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
?accident_ferroviaire wdt:P31/wdt:P279* wd:Q1078765; wdt:P17 ?pays;wdt:P585 ?date.
OPTIONAL { ?accident_ferroviaire wdt:P1120 ?nombre_de_morts. }
}                   
group by ?date order by ?date

Try it!

Vojtěch Dostál (talk) 18:56, 11 June 2023 (UTC)

Nice idea! https://w.wiki/6pRn Any idea to filter those that have been grouped out ? (ie no longer see individual dates) ? Bouzinac ^{💬●✒️●💛} 19:01, 11 June 2023 (UTC)

@Bouzinac Yes, with COUNT and HAVING ;-) https://w.wiki/6pRp Vojtěch Dostál (talk) 19:09, 11 June 2023 (UTC)

All is said :) Thanks Bouzinac ^{💬●✒️●💛} 19:14, 11 June 2023 (UTC)

About the list of Bangladesh National Parliament Members

This list is currently sorted by the constituency number. Sometimes there are more than one person having the same constituency with different start time, then is it possible to sort the two/three item by the value of their "start time"? — Haseeb (talk) 17:31, 11 June 2023 (UTC)

I'm surprised no one has answered this yet. Simply change the last line of the query to "ORDER BY xsd:integer(?seat_number) ?start". Infrastruktur (talk) 13:15, 14 June 2023 (UTC)

Find volleyball players

Find all volleyball players born in Germany who do not have information about a sports club, fill in the basic details Angelikas uw (talk) 19:35, 14 June 2023 (UTC)

Largest paintings

I am trying to list largest paintings:

SELECT ?painting ?widthAmount ?heightAmount (?widthAmount * ?heightAmount AS ?area)
WHERE {
  ?painting wdt:P31 wd:Q3305213 .  # Instance of painting
  ?painting p:P2048 ?widthStatement .
  ?widthStatement ps:P2048 ?widthValue .
  ?widthStatement wikibase:rank wikibase:PreferredRank .
  ?widthValue wikibase:quantityAmount ?widthAmount .
  ?painting p:P2049 ?heightStatement .
  ?heightStatement ps:P2049 ?heightValue .
  ?heightStatement wikibase:rank wikibase:PreferredRank .
  ?heightValue wikibase:quantityAmount ?heightAmount .
}
ORDER BY DESC(?area)
LIMIT 10

Try it!

but I'm getting no results. Jklamo (talk) 15:19, 15 June 2023 (UTC)

You should try psv instead of ps probably:

SELECT ?painting ?widthAmount ?heightAmount (?widthAmount * ?heightAmount AS ?area) {
  ?painting wdt:P31 wd:Q3305213 .  # Instance of painting
  ?painting p:P2049 ?widthStatement .
  ?widthStatement psv:P2049 ?widthValue .
  ?widthStatement wikibase:rank wikibase:PreferredRank .
  ?widthValue wikibase:quantityAmount ?widthAmount .
  ?painting p:P2048 ?heightStatement .
  ?heightStatement psv:P2048 ?heightValue .
  ?heightStatement wikibase:rank wikibase:PreferredRank .
  ?heightValue wikibase:quantityAmount ?heightAmount .
}
ORDER BY DESC(?area)
LIMIT 10

Try it!

, regards, Piastu (talk) 11:51, 16 June 2023 (UTC)

@Jklamo@Piastu - using psv: gets you the values, but doesn't get you the units, and so if one painting is measured in inches and another in centimetres, you'll get very confusing results. Using psn: instead normalises everything (into metres) so the results will be comparable.

One other thing to add - your wikibase:PreferredRank statement means it will only get answers where one rank in each is explicitly marked as preferred. This would rule out any where there is only one value but it's marked as "normal". This may be what you want, but worth noting it. Andrew Gray (talk) 12:06, 17 June 2023 (UTC)

Thanks, ended up with:

SELECT ?painting ?widthAmount ?heightAmount (?widthAmount * ?heightAmount AS ?area) 
{ 
?painting wdt:P31 wd:Q3305213 .  # Instance of painting
?painting p:P2049 ?widthStatement .
?widthStatement psn:P2049 ?widthValue .
?widthStatement rdf:type wikibase:BestRank .
?widthValue wikibase:quantityAmount ?widthAmount .
?painting p:P2048 ?heightStatement .
?heightStatement psn:P2048 ?heightValue .
?heightStatement rdf:type wikibase:BestRank .
?heightValue wikibase:quantityAmount ?heightAmount .
}
ORDER BY DESC(?area)
LIMIT 10

Try it!

Jklamo (talk) 10:46, 18 June 2023 (UTC)

The biggest is probably Siege of Sevastopol (Q1232436) and it's not in the list (use P31/P279*, but it times out). Infovarius (talk) 13:09, 19 June 2023 (UTC)

Finding humans with a page in English Wikipedia and an ORCID

I presume this would be easy for someone who is more clever at using this tool. Thank you. Trilotat (talk) 13:14, 16 June 2023 (UTC)

This is a short list of 100 humans with an English Wikipedia article and an ORCID. If they have multiple ORCID each of them may be listed multiple times. You can change the "100" to another number if you want more.

SELECT 
  ?human ?humanLabel
  ?orcid
  ?article
WITH {
  SELECT 
    ?human ?orcid ?article  
  WHERE { 
    ?human wdt:P31 wd:Q5 ;
           wdt:P496 ?orcid ;
           ^schema:about ?article .
    ?article schema:inLanguage "en" .
  }
  LIMIT 100  # Change if more humans should be returned
} AS %result
WHERE {
  INCLUDE %result
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }        
}

Try it!

If you have a specific ORCID, then you can copy and paste it into a URL in Scholia (Q45340488), for instance, https://scholia.toolforge.org/orcid/0000-0002-6340-9247 Scholia should show a short abstract of the English Wikipedia abstract with a link. — Finn Årup Nielsen (fnielsen) (talk) 08:41, 17 June 2023 (UTC)

List of countries and their respective capitals, currencies, populations, and flags

This query does not return the intended information: only the current capital and flag. More than one capital and more than one flag are returned, incorrectly. Could anyone help me correct this SPARQL? Thank you so much!

SELECT DISTINCT ?country_label ?capital_label ?currency_label ?population ?flag_label ?flag_image ?determination_method_label #?flag_start_time 
WHERE {
  ?country wdt:P31 wd:Q3624078 . 
  FILTER NOT EXISTS {?country wdt:P31 wd:Q3024240} 
  FILTER NOT EXISTS {?country wdt:P31 wd:Q28171280} 
  OPTIONAL { ?country wdt:P36 ?capital } . 
  OPTIONAL {
    ?country p:P36 [
      ps:P36 ?capital;
      pq:P459 ?determination_method]. 
    ?determination_method rdfs:label ?determination_method_label . 
    #FILTER(STR(?determination_method_label) != "de jure")
    FILTER (LANG(?determination_method_label) = "ru") 
  } . 
  OPTIONAL { ?country wdt:P38 ?currency } . 
  OPTIONAL { ?country wdt:P1082 ?population } . 
  OPTIONAL { 
    ?country p:P41 ?flagStatement . 
    ?flagStatement wikibase:rank wikibase:PreferredRank .
    ?flagStatement ps:P41 ?flag_image . 
    #?flagStatement pq:P580 ?flag_start_time . 
  } . 
  SERVICE wikibase:label { 
    ?country rdfs:label ?country_label . 
    ?capital rdfs:label ?capital_label . 
    ?currency rdfs:label ?currency_label . 
    ?country rdfs:label ?flag_label .
    bd:serviceParam wikibase:language "en" .
  }
}
ORDER BY ?country_label

Try it!

Wgfreitas (talk) 17:25, 17 June 2023 (UTC)

What to do with Bolivia having 2 capitals? + I've fix a piece of code about determination method. --Infovarius (talk) 13:44, 19 June 2023 (UTC)

Co-author graph within years limit

Hello!!, I'm using this query in Scholia about co-author graph, but I want to make this kind of graphic within certain limits, for example, the co-authored graph between 1900 and 1950; or 1970 and 2000. Thanks in advance! Mikelzubi (talk) 12:44, 16 June 2023 (UTC)

We unfortunately do not have that kind of filter in Scholia, but you can filter on publication time "manually" by modifying the SPARQL code:

   ?work wdt:P577 ?publication_datetime .
   FILTER (?publication_datetime < "1960"^^xsd:dateTime)
   FILTER (?publication_datetime < "1970"^^xsd:dateTime)

Example — Finn Årup Nielsen (fnielsen) (talk) 08:15, 17 June 2023 (UTC)

Thanks!! this is really usefull for us. I will use this type of filter. Mikelzubi (talk) 15:24, 22 June 2023 (UTC)

Query to find wikicommons images with...

I am hoping for a query which finds wikicommons images which have a Korean description, but no English description. Additional fields might include file usage on other wikis, or a count of file usage on other wikis.. MargaretRDonald (talk) 23:16, 22 June 2023 (UTC)

Still hoping for a response to this request. MargaretRDonald (talk) 00:31, 25 June 2023 (UTC)

Paintings in need of disambiguation

I'm looking for paintings that are in need of disambiguation. For example The Annunciation (Q3202079): painting by Sandro Botticelli should be disambiguated because The Annunciation (Q1114806): painting by Sandro Botticelli in the Kelvingrove Art Gallery and Museum and The Annunciation (Q3202069): painting by Sandro Botticelli in the Metropolitan Museum of Art exist. I already started with a query, but it times out if I add the last filter

SELECT ?item ?item2 ?label ?description ?description2 ?description2start WHERE {
  ?item wdt:P31 wd:Q3305213;
        wdt:P170 ?creator;
        rdfs:label ?label;
        schema:description ?description.
  FILTER(LANG(?label)="en" && LANG(?description)="en" && REGEX(STR(?description), "^painting by [^\\(]+$")) .
  ?item2 wdt:P170 ?creator;
         rdfs:label ?label;
         wdt:P31 wd:Q3305213;
         schema:description ?description2.
  FILTER(?item!=?item2 && LANG(?description2)="en" && ?description!=?description2 && STRLEN(?description) < STRLEN(?description2)) # && SUBSTR(STR(?description2), STRLEN(?description))=STR(?description)  ) 
  BIND(SUBSTR(STR(?description2), 0, STRLEN(?description)+1) AS ?description2start)
  #FILTER(STR(?description)=?description2start)
  } LIMIT 10

Try it!

Any suggestions to get a query without false positives that doesn't time out? Multichill (talk) 13:02, 24 June 2023 (UTC)

Looks like the timeout was caused by an off-by-one issue in the substring comparison.

SELECT ?item ?item2 ?label ?description ?description2 ?description2start WHERE {
  ?item wdt:P31 wd:Q3305213;
        wdt:P170 ?creator;
        rdfs:label ?label;
        schema:description ?description.
  FILTER(LANG(?label)="en" && LANG(?description)="en" && REGEX(STR(?description), "^painting by [^\\(]+$"))
  ?item2 wdt:P170 ?creator;
         rdfs:label ?label;
         wdt:P31 wd:Q3305213;
         schema:description ?description2.
  FILTER(?item!=?item2 && LANG(?description2)="en" && ?description!=?description2 && STRLEN(?description) < STRLEN(?description2)) 
  BIND(SUBSTR(STR(?description2), 1, STRLEN(?description)) AS ?description2start)
  FILTER(STR(?description)=?description2start)
  #filter( SUBSTR(STR(?description2), 1, STRLEN(?description))=STR(?description) )
  } LIMIT 10

Try it!

Infrastruktur (talk) 16:56, 24 June 2023 (UTC)

Thanks. That's weird, in computer science I always start counting at 0, not at 1! Multichill (talk) 17:16, 24 June 2023 (UTC)

Made a list at Wikidata:WikiProject sum of all paintings/Needs disambiguation for now. Suggestions to improve the performance a welcome. Maybe split it up in two parts? Multichill (talk) 20:31, 24 June 2023 (UTC)

Request

Hi, I am trying to retrieve publicly listed companies that are part of some indexes (DAX, MDAX, etc). I would like to get the ISIN, the name of the company, the wikipedia url in english if available (else german). The following works but is really very verbose and also returns Null values. What does need to change to replace the Null values by an arbitrary placeholder so that the values in the json response have the same length? Also: How can I make my query less verbose? Also: This query produces a lot of duplicates. How to get rid of those?Thanks.

# Query to retrieve publicly listed companies that are members of an index with additional information such as ISIN, etc.

 SELECT ?company ?companyLabel ?isinLabel ?url 
 WHERE 
 {
  {?company wdt:P31 wd:Q167037 .}         # Corporation
  UNION
  {?company wdt:P31 wd:Q6881511 .}         # Enterprise
  UNION
  {?company wdt:P31 wd:Q783794 .}         # Company
  UNION
  {?company wdt:P31 wd:Q4830453 .}       # Business
  UNION
  {?company wdt:P31 wd:Q891723 .}         # Publicly listed company
  
  {?company wdt:P361 wd:Q155718 .}         # DAX
  UNION
  {?company wdt:P361 wd:Q595622 .}         # MDAX
  UNION
  {?company wdt:P361 wd:Q448445 .}         # SDAX
  UNION
  {?company wdt:P361 wd:Q874430 .}         # CDAX
  UNION
  {?company wdt:P361 wd:Q880692 .}        # Segment: General Standard
  UNION
  {?company wdt:P361 wd:Q1425632 .}       # Segment: Prime Standard
  UNION
  {?company wdt:P361 wd:Q31837632 .}      # Segment: Basic Board
  
  OPTIONAL {?company wdt:P946 ?isin .}    # Show ISIN if given, else a placeholder ("") (not NULL) 

 OPTIONAL { ?url schema:about ?company .  # I'd like to get the en-version as a default, else de-   
  version if en is not available, else "" (not NULL)
           { ?url schema:isPartOf <https://en.wikipedia.org/> }
           UNION
           { ?url schema:isPartOf <https://de.wikipedia.org/> }
          }   
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }  # Helps get the label in your language, if not, then en language
 }

Sportgogel (talk) 07:47, 27 June 2023 (UTC)

List of Famous French-speaking singers and their popularity, romantic relationships and family members

Hello all, I'm trying to retrieve information about singers who speak French, born between 1900-2015, with at least 10 sitelinks (i.e., to select the most famous ones to rely on most reliable information). I want to extract information in 4 separate datasets:

- One dataset inclusing demographics information: one row by singer with gender, birth date, birth place(grouped in one cell), death date, country of citizenship (grouped in one cell), languages spoken (grouped in one cell).

- One dataset including popularity information: The popularity index includes two indicators:

(1) the total number of sitelinks of singers’ Wikidata page, as we suppose that the more Wikimedia sites (including mainly interlanguage Wikipedia pages) that have been linked to the singer’s Wikidata page, the more well known the singer is.

(2) the total number of triples or statements, which I think is a good indicator of the lenght of the Wikidata page. The rationale here is that the more notable the individual, the more documented his/her biographies will be.

If possible, I would add the average number of Wikipedia pageviews (hits) for each individual between 2010 and 2023 in the French language edition, using an API available in https://wikitech.wikimedia.org/wiki/Analytics/AQS/Pageviews ; This paper (https://www.nature.com/articles/s41597-022-01369-4#MOESM1) used a similar approach to measure notability.

- One dataset including romantic relationships names and occupations, if available. I would like to have for each singer, the names and occupations of their past and present spouses and unmarried partners (with their wikipedia URL link). Each row is a unique partner. Occupations are grouped in one cell. I would also collect age difference between partners and the relationship duration.

- One dataset including family members and occupations, if available. I would like to have for each singer, the names and occupations of their parents (mother, father) and their children, if any (with their wikipedia URL link). Each row is a unique family member. Occupations are grouped in one cell. I would also collect age difference between partners and the relationship duration.

I don't know if this is possible, but I would like to also collect popularity (as mentionned before) for all romantic partners and family members.

I've tried multiple queries but also most of the time, I reached the timeout limit. Can you help me to provide a well-optimized queries ?

Thanks a lot. 2A02:AA15:4101:5E00:5150:1208:A3FB:5D7A 11:16, 28 June 2023 (UTC)

fyi: I've already run two different queries but I don't find the sames numbers of singers between the two queries. For the demographics, I've found around 740 results after checking for duplicates, whereas, I've found only 638 results for the popularity search. Could you help me to harmonize my queries?

DEMOGRAPHIC INFORMATION query:

SELECT ?item ?itemLabel ?sex_or_genderLabel (GROUP_CONCAT(DISTINCT ?occupationLabel; SEPARATOR = ", ") AS ?occupations) (GROUP_CONCAT(DISTINCT ?languageLabel; SEPARATOR = ", ") AS ?languages_spoken) ?birth ?death ?Wikipedia_articles ?birth_placeLabel (GROUP_CONCAT(DISTINCT ?citizenLabel; SEPARATOR = ", ") AS ?citizenship) ?death_causeLabel WHERE {

VALUES ?target_language {

wd:Q150

}

VALUES ?target_occ {

wd:Q177220

}

?item wdt:P31 wd:Q5;

wdt:P1412 ?target_language, ?language;

(wdt:P106/(wdt:P279*)) ?target_occ;

wdt:P106 ?occupation;

wikibase:sitelinks ?Wikipedia_articles;

wdt:P569 ?birth.

hint:Prior hint:rangeSafe "true"^^xsd:boolean.

FILTER((?birth >= "1900-01-01T00:00:00"^^xsd:dateTime) && (?birth < "2005-01-01T00:00:00"^^xsd:dateTime))

FILTER((?Wikipedia_articles >=10))

OPTIONAL { ?item wdt:P21 ?sex_or_gender. }

OPTIONAL { ?item wdt:P570 ?death. }

OPTIONAL { ?item wdt:P19 ?birth_place. }

OPTIONAL { ?item wdt:P27 ?citizen. }

OPTIONAL { ?item wdt:P509 ?death_cause. }

SERVICE wikibase:label {

bd:serviceParam wikibase:language "en,fr".

?item rdfs:label ?itemLabel.

?sex_or_gender rdfs:label ?sex_or_genderLabel.

?occupation rdfs:label ?occupationLabel.

?language rdfs:label ?languageLabel.

?birth_place rdfs:label ?birth_placeLabel.

?citizen rdfs:label ?citizenLabel.

?death_cause rdfs:label ?death_causeLabel.

}

GROUP BY ?item ?itemLabel ?sex_or_genderLabel ?birth ?death ?Wikipedia_articles ?birth_placeLabel ?death_causeLabel

ORDER BY DESC (?Wikipedia_articles)

POPULARITY INFORMATION query:

SELECT ?item ?label ?Wikipedia_articles (COUNT(?label) AS ?triples) WHERE {

?item wdt:P106 wd:Q177220 .

?item wdt:P1412 wd:Q150 .

?item wikibase:sitelinks ?Wikipedia_articles .

?item rdfs:label ?label .

?item wdt:P569 ?birth.

?item ?p ?o .

FILTER (LANG(?label) = "fr")

FILTER((?birth >= "1900-01-01T00:00:00"^^xsd:dateTime) && (?birth < "2005-01-01T00:00:00"^^xsd:dateTime))

FILTER((?Wikipedia_articles >=10))

}

GROUP BY ?item ?label ?Wikipedia_articles

ORDER BY DESC(?triples) 2A02:AA15:4101:5E00:5150:1208:A3FB:5D7A 11:37, 28 June 2023 (UTC)

Wikidata:Request a query/Archive/2023/06

Contents

Numbers and sourcing circumstances (P1480) = circa (Q5727902)

Qualifier hashes

Find/highlight possible duplicates

About the list of Bangladesh National Parliament Members

Find volleyball players

Largest paintings

Finding humans with a page in English Wikipedia and an ORCID

List of countries and their respective capitals, currencies, populations, and flags

Co-author graph within years limit

Query to find wikicommons images with...

Paintings in need of disambiguation

Request

List of Famous French-speaking singers and their popularity, romantic relationships and family members

Navigation menu

Wikidata:Request a query/Archive/2023/06

Numbers and sourcing circumstances (P1480) = circa (Q5727902)

Qualifier hashes

Find/highlight possible duplicates

About the list of Bangladesh National Parliament Members

Find volleyball players

Largest paintings

Finding humans with a page in English Wikipedia and an ORCID

List of countries and their respective capitals, currencies, populations, and flags

Co-author graph within years limit

Query to find wikicommons images with...

Paintings in need of disambiguation

Request

List of Famous French-speaking singers and their popularity, romantic relationships and family members

Navigation menu

Search