Shortcuts: WD:RAQ, w.wiki/LX

Wikidata:Request a query

From Wikidata
Revision as of 15:03, 17 August 2021 by Revibot I (talk | contribs) (Bot: Archiving 2 threads (older than 14 days) to Wikidata:Request a query/Archive/2021/08)
Jump to navigation Jump to search

Request a query
Fishing in the Wikidata river requires both an idea where to look for fish and a suitable fishing method. If you have the former, this page can help you find the latter.

This is a page where SPARQL 1.1 Query Language (Q32146616) queries can be requested. Please provide feedback if a query is written for you.

For sample queries, see Examples and Help:Dataset sizing. Property talk pages include also summary queries for these.

For help writing your own queries, or other questions about queries, see Wikidata talk:SPARQL query service/queries and Wikidata:SPARQL query service/query optimization.

Help resources about Wikidata Query Service (Q20950365) and SPARQL: Wikidata:SPARQL query service/Wikidata Query Help and Category:SPARQL.

To report an issue about the Query Service (interface, results views, export...) please see Wikidata:Contact the development team/Query Service and search.
On this page, old discussions are archived. An overview of all archives can be found at this page's archive index. The current archive is located at 2024/04.

Number of artists / cultural organisations from Québec

Hi! I am looking to find out how many artists (Q483501) or cultural institutions (Q3152824) / art organizations (Q7062022) from the province of Quebec (Q176) are listed actually on Wikidata (all disciplines combined). I have no clue as to where to start in creating a SPARQL query, but here are some properties I found that could be useful (I think), if someone (anyone) is willing to give me a hand. Thanks in advance for your help.

  • Wikidata property related to art (Q27918607)
  • Québec (Q176)
  • Artists (Q483501)
  • Cultural institution (Q3152824)
  • not-for-profit arts organization (Q7062022)
  • nonprofit organization (Q163740)
  • field of this occupation : Art (Q735)
  • sub-class of : Art (Q2018526)
  • part of / field of work : Culture (Q11042)
  • has quality of : art genre (Q1792379) , art style (Q1792644)
  • Catégorie:Arts (Q4104783)
  • Catégorie:Association ou organisme culturel au Québec (Q49656487)
  • Catégorie:Association ou organisme culturel (Q8809115)

Link for an external identifier

  • Hello, is it possible to have exactly the same querry than below but with an additional field giving a weblink for the value stored in Plazi ID (P1992)
The URL seems to be http://treatment.plazi.org/id/ + the value stored in Plazi ID (P1992)
Christian Ferrer (talk) 18:26, 6 August 2021 (UTC)[reply]
SELECT DISTINCT ?item ?itemLabel ?author ?authorLabel ?publication ?publicationLabel ?referenceHasRole ?referenceHasRoleLabel ?PlaziID
WHERE
{
  {?item p:P225 ?statement .
    ?statement prov:wasDerivedFrom ?reference .
    ?reference pr:P1992 ?PlaziID}
  ?item wdt:P105 ?rank .
  OPTIONAL { ?item p:P225 ?st . 
    ?st pq:P405 ?author . }
  OPTIONAL { ?item p:P225 ?statement .
    ?statement prov:wasDerivedFrom ?reference .
    ?reference pr:P248 ?publication.
              OPTIONAL { ?item p:P225 ?statement .
    ?statement prov:wasDerivedFrom ?reference .
    ?reference pr:P248 ?publication.
    ?reference pr:P6184 ?referenceHasRole}
}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
order by ?itemLabel
Try it!
@Christian Ferrer: It is:
SELECT DISTINCT ?item ?itemLabel ?author ?authorLabel ?publication ?publicationLabel ?referenceHasRole ?referenceHasRoleLabel ?PlaziID ?PlaziID_URL
WHERE
{
  {?item p:P225 ?statement .
    ?statement prov:wasDerivedFrom ?reference .
    ?reference pr:P1992 ?PlaziID
  BIND(IRI(CONCAT("http://treatment.plazi.org/id/",?PlaziID)) as ?PlaziID_URL)
  }
  ?item wdt:P105 ?rank .
  OPTIONAL { ?item p:P225 ?st . 
    ?st pq:P405 ?author . }
  OPTIONAL { ?item p:P225 ?statement .
    ?statement prov:wasDerivedFrom ?reference .
    ?reference pr:P248 ?publication.
              OPTIONAL { ?item p:P225 ?statement .
    ?statement prov:wasDerivedFrom ?reference .
    ?reference pr:P248 ?publication.
    ?reference pr:P6184 ?referenceHasRole}
}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
order by ?itemLabel
Try it!
--Tagishsimon (talk) 18:30, 6 August 2021 (UTC)[reply]

Help with querying multiple occupations

Hi—I’m brand new to this, thank you in advance for help! I have a query to pull all the women writers described in a particular women's biographical dictionary who don’t have an en-wiki entry yet (English Wikipedia Women in Red is having an editathon on this topic next month). Ideally I would like include other occupations associated with writing—so writers or calligraphers (Q3303330) or publishers (Q2516866), for example. Might someone kindly show me the syntax to ask SPARQL to include anyone who is either a writer, or a calligrapher, or a publisher?

Here’s what I’ve got:

SELECT ?item ?linkcount WHERE {
 ?item wdt:P1343 wd:Q105802405.            # described by source Dictionary of Women Worldwide 
 ?item wdt:P106 wd:Q36180.                 # occupation writer 
FILTER NOT EXISTS {                              # has no en.wikipedia sitelink
   ?wen schema:about ?item .
   ?wen schema:isPartOf <https://en.wikipedia.org/> .}
 
 ?item wdt:P21 wd:Q6581072 .                      # gender: female
 ?item wikibase:sitelinks ?linkcount .            # count of sitelinks
}

Thanks so much! Innisfree987 (talk) 17:20, 7 August 2021 (UTC)[reply]

@Innisfree987: I'd be inclined to something along these lines where...
  • We force the query to get the items which are described by source Dictionary of Women Worldwide first, 'cos that'll be a fairly short list and mean the reat of the query has to do less work.
  • Then a set of queries which look for the ?item having a specified occupation, or an occupation which is a subclass of the specified occuption
  • UNION these all together, which is to say, include the item if it meets any one of the occupation statements
  • Do the rest of the query - has no en.wiki article, is female.
I'll note Wikidata:SPARQL query service/query optimization, where, for instance, the hint I used is probably mentioned. I've used SELECT DISTINCT on the select clause; without this you get 8080 results (one for each women * each woman's occupations), with it, about 645. When used in Listeria, Listeria performs the DISTINCT itself, so DISTINCT could have been left out of the query with no harm done. Finally, I added 'author' as another occupation, b/c it seemed appropriate.
SELECT DISTINCT ?item ?linkcount WHERE {
  ?item wdt:P1343 wd:Q105802405.            # described by source Dictionary of Women Worldwide 
  hint:Prior hint:runFirst true.
  {?item wdt:P106/wdt:P279* wd:Q36180 . }                 # occupation writer 
  UNION
  {?item wdt:P106/wdt:P279* wd:Q3303330 . }                 # occupation calligrapher
  UNION
  {?item wdt:P106/wdt:P279* wd:Q2516866 . }                 # occupation publisher
  UNION
  {?item wdt:P106/wdt:P279* wd:Q482980 . }                 # occupation author
  FILTER NOT EXISTS {                              # has no en.wikipedia sitelink
    ?wen schema:about ?item .
    ?wen schema:isPartOf <https://en.wikipedia.org/> .}
 ?item wdt:P21 wd:Q6581072 .                      # gender: female
 ?item wikibase:sitelinks ?linkcount .            # count of sitelinks
}
Try it!
--Tagishsimon (talk) 17:36, 7 August 2021 (UTC)[reply]
@Innisfree987: Actually, I'd like to report a case of brain fade. Exactly the same query, but shorter, using VALUES to set out the list of high-level occupations we're interested in. Each gets applied in the ?item wdt:P106/wdt:P279* ?occupation . part of the query
SELECT DISTINCT ?item ?linkcount WHERE {
  ?item wdt:P1343 wd:Q105802405.            # described by source Dictionary of Women Worldwide 
  hint:Prior hint:runFirst true.
  VALUES ?occupation {wd:Q36180 wd:Q3303330 wd:Q2516866  wd:Q482980 }
  ?item wdt:P106/wdt:P279* ?occupation .
  FILTER NOT EXISTS {                              # has no en.wikipedia sitelink
    ?wen schema:about ?item .
    ?wen schema:isPartOf <https://en.wikipedia.org/> .}
 ?item wdt:P21 wd:Q6581072 .                      # gender: female
 ?item wikibase:sitelinks ?linkcount .            # count of sitelinks
}
Try it!
--Tagishsimon (talk) 17:47, 7 August 2021 (UTC)[reply]
Oh thank you so much Tagishsimon, that’s the language that had stumped me. And thanks also re: query optimization, I will check that out and see if I can get the main DoWW list loading again. For this list, if I may ask you one more question: what would I change if I wanted to leave off the subclasses? Including them seems to pull up a fair number of musicians (songwriters and composers) who I would like to save for another list. (It’ll be less elegant if I manually add more writing occupations but I don’t mind doing that part.) Thanks again, big leap forward for my understanding of SPARQL! Innisfree987 (talk) 18:29, 7 August 2021 (UTC)[reply]
@Innisfree987: It's the /wdt:P279* part of ?item wdt:P106/wdt:P279* ?occupation . ... the latter reads, ?item which has a P106 matching ?occupation, or a P106 which is a subclass (P279) to any number of levels (the *) of ?occupation. So instead, use ?item wdt:P106 ?occupation .. The whole forwardslash linking of the two predicates wdt:P106/wdt:P279* is, FYI, a property path, best defined here: https://www.w3.org/TR/sparql11-property-paths/#path-language
It's excellent you're getting your head around SPARQL. I learned SPARQL by hanging out on this page for a few months; pretty much everything of SPARQL/WDQS I know comes from studying the queries here & being helped by the denizens of this board. Please come back as often as you like for more input; we're completely at your disposal. --Tagishsimon (talk) 18:51, 7 August 2021 (UTC)[reply]
Oh I see about the pathways. Much to learn! I’ll keep reading and try to build on this knowledge—really appreciate the expert helping hand! Innisfree987 (talk) 19:10, 7 August 2021 (UTC)[reply]

I'm trying to write a query to find American politicians whose father or mother was also a politician

This is my query so far. Any help would be great.

SELECT ?item ?itemLabel ?fatherLabel ?motherLabel WHERE {

 ?item wdt:P31 wd:Q5;
       wdt:P27 wd:Q30;
       wdt:P106 wd:Q82955.
 {?item wdt:P22 ?father.
 ?father wdt:P106 wd:Q82955.}
 UNION
 {?item wdt:P25 ?mother.
 ?mother wdt:P106 wd:Q82955.}
 
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }

}

    • Problem: Repeats the same name if both mother and father are politicians

I want to display both names on the same line

This seems to work
SELECT ?item ?itemLabel (SAMPLE(?fatherLabel_) as ?fatherLabel) (SAMPLE(?motherLabel_) as ?motherLabel) WHERE {
 ?item wdt:P31 wd:Q5;
       wdt:P27 wd:Q30;
       wdt:P106 wd:Q82955.
 {?item wdt:P22 ?father.
 ?father wdt:P106 wd:Q82955.}
 UNION
 {?item wdt:P25 ?mother.
 ?mother wdt:P106 wd:Q82955.}
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
                        ?item rdfs:label ?itemLabel . 
                        ?father rdfs:label ?fatherLabel_ . 
                        ?mother rdfs:label ?motherLabel_ . }

} group by ?item ?itemLabel
Try it!
--Tagishsimon (talk) 21:25, 11 August 2021 (UTC)[reply]

Items with the most statements

I'd like a ranking of the items with the most statements on Wikidata. Is this possible here? AntisocialRyan (Talk) 02:28, 16 August 2021 (UTC)[reply]

@AntisocialRyan: Yes: like this. I think 8,345 is the current record, hence this query looks for #statements up to 10k. You can push that higher if you want, but I doubt you'll find anything.
SELECT ?item ?itemLabel ?number WITH {
  SELECT ?number WHERE {
  ?nn wdt:P31 wd:Q21199.
  ?nn wdt:P1181 ?number .
  FILTER (?number >= 1000 && ?number <= 10000)
} } AS %i
WHERE {
  INCLUDE %i
  BIND(xsd:integer(?number) as ?b)
  ?item wikibase:statements ?b.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by desc(?number)
Try it!
--Tagishsimon (talk) 03:14, 16 August 2021 (UTC)[reply]
@Tagishsimon: That is really surprising to me, I can't even load the page to see how it is possible. Thanks so much! How can I limit this to not include scholarly articles? Sorry, querying is very confusing to me. AntisocialRyan (Talk) 03:21, 16 August 2021 (UTC)[reply]
@AntisocialRyan: No probs, it's what this page is for.
SELECT ?item ?itemLabel ?number WITH {
  SELECT ?number WHERE {
  ?nn wdt:P31 wd:Q21199.
  ?nn wdt:P1181 ?number .
  FILTER (?number >= 1000 && ?number <= 10000)
} } AS %i
WHERE {
  INCLUDE %i
  BIND(xsd:integer(?number) as ?b)
  ?item wikibase:statements ?b.
  FILTER NOT EXISTS {?item wdt:P31 wd:Q13442814 .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by desc(?number)
Try it!
--Tagishsimon (talk) 03:35, 16 August 2021 (UTC)[reply]
This is very helpful, thanks so much again. AntisocialRyan (Talk) 16:19, 16 August 2021 (UTC)[reply]

Get the instance of an item

Given the item wikidata like wd:Q155164, Get the label of its instance property(P31) .Like for wd:Q155164 it would be human  – The preceding unsigned comment was added by 39.37.161.237 (talk • contribs).

SELECT ?item ?p31Label WHERE {
  VALUES ?item { wd:Q155164 }
  ?item wdt:P31 ?p31 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Larske (talk) 11:42, 16 August 2021 (UTC)[reply]

Because I suck at aggregation

The query below gets the count of uses of distinct predicates in each item of the set of (non scholarly article) items having from 1k to 10k statements. I'd like the report to list a single row for each item showing the property of its most frequently used predicate, and a count of uses of that predicate.

I can tweak the report along the lines of (MAX(?count) AS ?maxcount) to get a maximum count per item (e.g. if aggregated on ?item ?itemLabel ?number) but have failed to produce the associated property with the ?maxcount. (SAMPLE(?property) as ?sample), for instance, will return a ?property, but not necessarily the one associated with ?maxcount. #headDesk. Any help &c; thank you.

SELECT ?item ?itemLabel ?number ?property ?count WITH {
  SELECT ?number WHERE {                 # get numbers from 1k to 10k
  ?nn wdt:P31 wd:Q21199.
  ?nn wdt:P1181 ?number .
  FILTER (?number >= 1000 && ?number <= 10000)
} } AS %i
WITH { SELECT ?item ?number WHERE {     # get items having that many statements
  INCLUDE %i
  BIND(xsd:integer(?number) as ?b)
  ?item wikibase:statements ?b.
  FILTER NOT EXISTS {?item wdt:P31 wd:Q13442814 .}
 } } as %j
WITH { SELECT ?item ?number ?property (COUNT(?predicate) as ?count) WHERE {  # get & count claims for the items
  INCLUDE %j
  ?item ?predicate [].
  ?property wikibase:claim ?predicate . 
} GROUP BY ?item ?number ?property ORDER BY DESC(?count) } as %k
WHERE {                              # right now, get labels and an aggregation which does nothing
  INCLUDE %k
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?item ?itemLabel ?number ?property ?count order by desc(?number)
Try it!

--Tagishsimon (talk) 13:39, 16 August 2021 (UTC)[reply]

@Tagishsimon:Is this what you're after
SELECT ?item ?itemLabel ?number (MAX(?count) AS ?maxcount)  WITH {
  SELECT ?number WHERE {                 # get numbers from 1k to 10k
  ?nn wdt:P31 wd:Q21199.
  ?nn wdt:P1181 ?number .
  FILTER (?number >= 1000 && ?number <= 10000)
} } AS %i
WITH { SELECT ?item ?number WHERE {     # get items having that many statements
  INCLUDE %i
  BIND(xsd:integer(?number) as ?b)
  ?item wikibase:statements ?b.
  FILTER NOT EXISTS {?item wdt:P31 wd:Q13442814 .}
 } } as %j
WITH { SELECT ?item ?number ?property (COUNT(?predicate) as ?count) WHERE {  # get & count claims for the items
  INCLUDE %j
  ?item ?predicate [].
  ?property wikibase:claim ?predicate . 
} GROUP BY ?item ?number ?property ORDER BY DESC(?count) } as %k
WHERE {                              # right now, get labels and an aggregation which does nothing
  INCLUDE %k
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?item ?itemLabel ?number order by desc(?number)
Try it!

Piecesofuk (talk) 20:15, 16 August 2021 (UTC)[reply]

Oh I suspect you want the property...I'm stumped Piecesofuk (talk) 20:25, 16 August 2021 (UTC)[reply]
Thanks; and sadly, yes, wrangling the property is where it all fails. --Tagishsimon (talk) 06:39, 17 August 2021 (UTC)[reply]


@Tagishsimon: I slept on it and came up with this, is this right?
SELECT ?item ?itemLabel ?number ?property ?propertyLabel ?count ?maxcount  WITH {
  SELECT ?number WHERE {                 # get numbers from 1k to 10k
  ?nn wdt:P31 wd:Q21199.
  ?nn wdt:P1181 ?number .
  FILTER (?number >= 1000 && ?number <= 10000)
} } AS %i
WITH { SELECT ?item ?number WHERE {     # get items having that many statements
  INCLUDE %i
  BIND(xsd:integer(?number) as ?b)
  ?item wikibase:statements ?b.
  FILTER NOT EXISTS {?item wdt:P31 wd:Q13442814 .}
 } } as %j
WITH { SELECT ?item ?number ?property (COUNT(?predicate) as ?count) WHERE {  # get & count claims for the items
  INCLUDE %j
  ?item ?predicate [].
  ?property wikibase:claim ?predicate . 
} GROUP BY ?item ?number ?property ORDER BY DESC(?count) } as %k
WITH { SELECT  ?item (MAX(?count) AS ?maxcount) {
  INCLUDE %k
} GROUP BY ?item } AS %max

WHERE {                              # right now, get labels and an aggregation which does nothing
  INCLUDE %k
  INCLUDE %max
  FILTER (?count = ?maxcount) 
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}  order by desc(?number)
Try it!

Piecesofuk (talk) 09:49, 17 August 2021 (UTC)[reply]

@Piecesofuk: Thank you; ingenious. It's not the complete solution; should an item have a non-max count of a predicate matching that of a maxcount of a predicate for a different item, then afaics we'd report the non-max predicate of the first item. But it works well for this particular set, answering the question 'which predicate is contributing most to these items being so bloated'. (Well, almost; would need to look at qualifiers & refs for the full picture.) --Tagishsimon (talk) 10:06, 17 August 2021 (UTC)[reply]
I've added ?propertyLabel to your query so we can see which are the perps. --Tagishsimon (talk) 10:10, 17 August 2021 (UTC)[reply]

Swiss women politicians involved in foreign affairs and/or international policy

Hello,

I've been trying to query for Swiss women politicians involved in foreign affairs (Q610018), but I am realizing that however little I know of SPARQL might not be enough. I first aimed to look for Swiss women politicians as follows:

SELECT DISTINCT ?item ?itemLabel ?itemDescription ?sitelinks WHERE {

 ?item wdt:P31 wd:Q467;
   wdt:P27 wd:Q39;
   wikibase:sitelinks ?sitelinks.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en,nl". }
 ?item wdt:P106 wd:Q82955.

} ORDER BY DESC (?sitelinks)

But it appears I did not enter successful terms, and I must admit I wouldn't know how to add the extra contraint I am looking for ("involved in foreign affairs"). Thanks in advance for your help!--Flor WMCH (talk) 13:42, 17 August 2021 (UTC)[reply]

@Flor WMCH: You were close. The ?item wdt:P31 wd:Q467 was the undoing ... females are recognised by the coding ?item wdt:P21 wd:Q6581072.
SELECT ?item ?itemLabel ?sitelinks
WHERE 
{
 ?item wdt:P106 wd:Q82955.
 ?item wdt:P21 wd:Q6581072.
 ?item wdt:P27 wd:Q39.
 ?item wikibase:sitelinks ?sitelinks.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en,nl". }
}
Try it!
Involved in foreign affairs is more difficult; you could extend the scope of occupation to look for diplomats, for instance, and there may be more occupations that occur to you. Put them in the VALUES statement, and each will be applied by the ?item wdt:P106 ?occupation . clause.
SELECT DISTINCT ?item ?itemLabel ?sitelinks
WHERE 
{
  VALUES ?occupation {wd:Q82955 wd:Q193391}
 ?item wdt:P106 ?occupation .
 ?item wdt:P21 wd:Q6581072.
 ?item wdt:P27 wd:Q39.
 ?item wikibase:sitelinks ?sitelinks.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en,nl". }
}
Try it!
There might be other indicators - such as employed by the UN or one of its agencies.
SELECT DISTINCT ?item ?itemLabel ?sitelinks
WHERE 
{
 {VALUES ?occupation {wd:Q82955 wd:Q193391}
 ?item wdt:P106 ?occupation .}
 UNION
 {?item wdt:P108/wdt:P361* wd:Q1065 . }
 ?item wdt:P21 wd:Q6581072.
 ?item wdt:P27 wd:Q39.
 ?item wikibase:sitelinks ?sitelinks.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en,nl". }
}
Try it!
And so it goes on. They might work for an International Organisation.
SELECT DISTINCT ?item ?itemLabel ?sitelinks
WHERE 
{
 {VALUES ?occupation {wd:Q82955 wd:Q193391}
 ?item wdt:P106 ?occupation .}
 UNION
 {?item wdt:P108/wdt:P361* wd:Q1065 . }
 UNION
 {?item wdt:P108/wdt:P31 wd:Q484652 . }
 ?item wdt:P21 wd:Q6581072.
 ?item wdt:P27 wd:Q39.
 ?item wikibase:sitelinks ?sitelinks.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en,nl". }
}
Try it!

For completeness, all of the above involve UNIONs - the person is a politician or diplomat, AND/OR works for the UN, AND/OR for an international organisation. If we want the politician who is also any one of a diplomat, a UN employee or an international organisation employee, then:

SELECT DISTINCT ?item ?itemLabel ?sitelinks
WHERE 
{
 ?item wdt:P106 wd:Q82955 .     # is a politician
 {?item  wdt:P106 wd:Q193391 .} # is also a diplomat
 UNION
 {?item wdt:P108/wdt:P361* wd:Q1065 . } # AND/OR employed by the UN
 UNION
 {?item wdt:P108/wdt:P31 wd:Q484652 . } # AND/OR by an international organisation
 ?item wdt:P21 wd:Q6581072.
 ?item wdt:P27 wd:Q39.
 ?item wikibase:sitelinks ?sitelinks.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en,nl". }
}
Try it!
Hope some of these help. Happy to explain any of the concepts / code used & happy to field any & all questions on WD & SPARQL. --Tagishsimon (talk) 14:16, 17 August 2021 (UTC)[reply]