Wikidata:Request a query
Request a query 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/05. |
Entities with articles in many languages but not English. edit
How can I find the Wikidata entities with the most wiki links which doesn't have an English page? (Or, more generally, the Wikidata entities with the most interwiki links which does not have a link in such-and-such a language.) Grendelkhan (talk) 03:57, 23 April 2024 (UTC)
- I don't have a direct solution, but I did identify some parts:
- You can select the count of some query, as in the example "Number of humans in wikidata"
- The current top topic is "largest lakes no enwiki", the solution at https://w.wiki/9kqs may help you with figuring out how to detect whether there is no English wikipedia. It looks like that is this codeblock:
- Try it!
optional { : ?article schema:about ?item ; : schema:isPartOf <https://en.wikipedia.org/> . : } : filter(!bound(?article))
- I think for items linked to a wikipedia page, for each language you get one rdfs:label in the article, for the title of the article in that language. For example here for the United Nations item: https://w.wiki/9seF
- I guess the total query would need to select the item ID and number of those labels, while excluding the ones which are not allowed using the filter. I tried combining it all, but my attempt seems to run forever even with just a few items: https://w.wiki/9seW M.alten.tue (talk) 13:18, 25 April 2024 (UTC)
- I think you've run into phabricator:T120198. There's a different way of doing it; see mw:Wikibase/Indexing/RDF Dump Format#Sitelinks. I unfortunately don't understand enough SPARQL to implement this, but this looks promising, maybe? Grendelkhan (talk) 22:54, 5 May 2024 (UTC)
All riders of the Tour de France 2023 with sport number and ranking edit
I think that this discussion is resolved and can be archived. If you disagree, don't hesitate to replace this template with your comment. Strahtw (talk) |
To get all riders of the Tour the France 2023 is easy:
SELECT ?item ?itemLabel WHERE {
?item wdt:P1344 wd:Q106203524.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Now I want also if exits the sport number (P1618) and the ranking (P1352) of the Tour de France 2023. Usually this two are qualifiers of the Tour de France 2023.
Can someone help me please? Strahtw (talk) 13:28, 25 April 2024 (UTC)
- I got help from another side. If someone is intereseted in the solution here is what I got:
- https://w.wiki/9uZb
- Strahtw (talk) 11:24, 30 April 2024 (UTC)
Feminine demonyms edit
Hi, I wanted to run but it does not work, any guess?
SELECT ?gare_ferroviaire ?gare_ferroviaireLabel ?gare_ferroviaireDescription ?female_statement ?paysLabel WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
?gare_ferroviaire wdt:P31 wd:Q55488.
MINUS { ?gare_ferroviaire wdt:P5817 ?état_d_usage. }
OPTIONAL { ?gare_ferroviaire wdt:P17 ?pays.}
OPTIONAL {
?pays p:P1549 ?female_statement .
?female_statement (pq:P366|pq:P518) ?female_val; ps:P1549 ?female .
FILTER( LANG( ?female ) = 'fr' ) .
MINUS { ?female_statement (pq:P366|pq:P518) wd:Q146786 } .
} .
}
Hospitals in Oxfordshire, displayed on map edit
Hi, I created this using the query builder: get a list of hospitals, but am not able to get the results to display on a map.
#Map of hospitals
#added 2017-08
#defaultView:Map
SELECT DISTINCT ?item ?itemLabel WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
{
SELECT DISTINCT ?item WHERE {
?item p:P31 ?statement0.
?statement0 (ps:P31/(wdt:P279*)) wd:Q16917.
?item p:P131 ?statement1.
?statement1 (ps:P131/(wdt:P131*)) wd:Q23169.
}
LIMIT 5
}
}
Error/warning message: Unable to display result: Map Arided (talk) 12:07, 29 April 2024 (UTC)
Hi, this query looks a bit overcomplicated, which is why it was timing out. The title at the start needs to be prefixed with "title:" which is why you were getting that error. Maps need co-ordinate locations, which you get using the property coordinate location (P625). Here's a version that works and gets an image from Wikimedia Commons if one is available:
#title:Map of hospitals
#defaultView:Map
SELECT DISTINCT ?item ?itemLabel ?coords ?image WHERE {
?item wdt:P31/wdt:P279? wd:Q16917. # Hospital or type of hospital
?item wdt:P131/wdt:P131? wd:Q23169. # In Oxfordshire or a (part of)* Oxfordshire
?item wdt:P625 ?coords. # get longitude and latitude
OPTIONAL{?item wdt:P18 ?image}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]".}
}
Cheers, MartinPoulter (talk) 14:35, 29 April 2024 (UTC)
- Hi Martin, Thanks. Here's what I eventually came up with using Kartographer to display queries. https://www.wikidata.org/wiki/User:Arided I broke down my query for rail stations across districts to get it to complete! Arided (talk) 17:30, 29 April 2024 (UTC)
Get all participants with ranking and number of the Giro d'Italia 2023 edit
All Participants of the Giro d'Italia 2023 (Q114614803) are a qualifier of the property P710 with their ranking and their start number. Now I want a list of the riders with their ranking and number
With this query I get all riders waht I want:
SELECT ?rider ?riderLabel ?number ?rank WHERE {
?Giro wdt:P31 wd:Q33861;
wdt:P361 wd:Q106203942;
wdt:P710 ?rider;
#p:P710 ?GiroStatement.
#OPTIONAL { ?GiroStatement pq:P1352 ?rank. }
#OPTIONAL { ?GiroStatement pq:P1618 ?number. }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de". }
}
And with this query I get all the rankings:
SELECT ?rider ?riderLabel ?number ?rank WHERE {
?Giro wdt:P31 wd:Q33861;
wdt:P361 wd:Q106203942;
#wdt:P710 ?rider;
p:P710 ?GiroStatement.
OPTIONAL { ?GiroStatement pq:P1352 ?rank. }
OPTIONAL { ?GiroStatement pq:P1618 ?number. }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de". }
}
I tried different things but I can not get what I want. Strahtw (talk) 11:34, 30 April 2024 (UTC)
Daxia (Q846492) edit
No conozco el idioma inglés. Cómo se realiza una página de desambiguación?. Saludos. Gracias. Mdelt (talk) 15:06, 30 April 2024 (UTC)
- The following finds anything that is said to be the same as (P460) or different from (P1889) Daxia (Q846492) including reverse relations. Is this what you mean? If not, try to be more verbose with your question.
- Infrastruktur (talk) 15:31, 30 April 2024 (UTC)Try it!
select distinct ?item ?itemLabel where { ?item wdt:P460|^wdt:P460|wdt:P1889|^wdt:P1889 wd:Q846492 . service wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . } }
- Gracias. Saludos Mdelt (talk) 17:24, 30 April 2024 (UTC)
Why my filter not exists(contains) doesn't work ? edit
Hello everyone,
Someone can explain to me why this query isn't working ?
#articles scientifiques possédant "GRB" dans leur libellé et dont l'identifiant NASA ADS ne contient pas les lettres "GCN".
SELECT ?item ?label
WHERE {
?item wdt:P31 wd:Q13442814 ;
rdfs:label ?label ;
wdt:P819 ?ads.
FILTER(LANG(?label) = "en")
FILTER(STRSTARTS(?label, "GRB")).
bind(STR(?ads) as ?string) .
FILTER not exists{CONTAINS(?string, "GCN")}.
}
Daxia (Q109312877) edit
How can I delete a repeated and empty entry in Wikidata: Daxia (Q109312877). Mdelt (talk) 17:50, 2 May 2024 (UTC)
How can I return football players who have played for two teams? edit
I currently have this query:
SELECT DISTINCT ?player ?playerLabel WHERE {
?player wdt:P31 wd:Q5; wdt:P106 wd:Q937857. ?player p:P54 ?membership1. ?membership1 ps:P54 wd:Q1543. ?player p:P54 ?membership2. ?membership2 ps:P54 wd:Q18656.
SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
This returns 9 results showing players who have played for both AC Milan and Manchester United but misses one player, Diogo Dalot. I'm assuming this is because he was only on loan at AC Milan and so they don't appear on his Wikidata page. How can I change my query to include players who only played on loan for one of the teams?
Many thanks PerneusGalway (talk) 22:39, 2 May 2024 (UTC)
Album tracks edit
Could someome make a query that shows all the tracks for Cultist Simulator: Original Soundtrack (Q125506146) + all compositions that the tracks are based on. --Trade (talk) 10:06, 4 May 2024 (UTC)
url queries of wikipedia urls take forever. edit
not a query request, but a question:
Why does this query take ~212ms:
SELECT ?url ?item WHERE {
VALUES ?url {
<https://www.podchaser.com/podcasts/this-american-life-399612>
<https://www.podchaser.com/podcasts/this-american-life-399612/>
<http://www.podchaser.com/podcasts/this-american-life-399612>
<http://www.podchaser.com/podcasts/this-american-life-399612/>
<http://podchaser.com/podcasts/this-american-life-399612>
<http://podchaser.com/podcasts/this-american-life-399612/>
<https://www.podchaser.com/>
<http://www.podchaser.com/>
<http://podchaser.com/>
<https://www.podchaser.com>
<http://www.podchaser.com>
<http://podchaser.com>
}
?item ?predicate ?url.
?property wikibase:directClaim ?predicate.
}
while this shorter query takes more then 35s?
SELECT ?url ?item WHERE {
VALUES ?url {
<https://en.wikipedia.org/wiki/Double_bond>
<https://en.wikipedia.org/wiki/Double_bond/>
<http://en.wikipedia.org/wiki/Double_bond>
<http://en.wikipedia.org/wiki/Double_bond/>
<https://en.wikipedia.org/>
<http://en.wikipedia.org/>
<https://en.wikipedia.org>
<http://en.wikipedia.org>
}
?item ?predicate ?url.
?property wikibase:directClaim ?predicate.
}
In both cases I'm only looking url statements that point to these urls. can I make both queries fast while yielding the same results? –Shisma (talk) 14:03, 4 May 2024 (UTC)
- Some times the optimizer makes bad guesses about which order to do things. It's been said that starting with small sets gives good performance, but really what you want is to keep the size of the intermediate joins small throughout the query and especially at the start. Here it will run things in order of appearance. For your first query it will start with a set of 2 items and combine with a set of 11K items, where on the second query it will start with a set of 9.8M items, then it will combine this with a second set of 11K items. To fix the second query you can replace the last line with "?property wikibase:directClaim ?predicate. hint:Prior hint:runFirst true .". Infrastruktur (talk) 14:58, 4 May 2024 (UTC)
- interesting. the hint, makes the second query faster, but the first query becomes slower. since I'm generating these queries programmaticaly I would need a heuristic to decide whether the hint should be present or not. why does the second query it start with a set of 9.8M items in the first place? I assume there aren't 9.8M statements with these urls… are there? – Shisma (talk) 15:13, 4 May 2024 (UTC)
- I think it matches the basic graph pattern ?s schema:isPartOf <https://en.wikipedia.org/> that the sitelinks use. Infrastruktur (talk) 15:21, 4 May 2024 (UTC)
- sounds reasonable since this query is much faster
- I think it matches the basic graph pattern ?s schema:isPartOf <https://en.wikipedia.org/> that the sitelinks use. Infrastruktur (talk) 15:21, 4 May 2024 (UTC)
- interesting. the hint, makes the second query faster, but the first query becomes slower. since I'm generating these queries programmaticaly I would need a heuristic to decide whether the hint should be present or not. why does the second query it start with a set of 9.8M items in the first place? I assume there aren't 9.8M statements with these urls… are there? – Shisma (talk) 15:13, 4 May 2024 (UTC)
SELECT ?url ?item WHERE {
VALUES ?url {
<https://en.wikipedia.org/wiki/Double_bond>
<https://en.wikipedia.org/wiki/Double_bond/>
<http://en.wikipedia.org/wiki/Double_bond>
<http://en.wikipedia.org/wiki/Double_bond/>
#<https://en.wikipedia.org/>
<http://en.wikipedia.org/>
<https://en.wikipedia.org>
<http://en.wikipedia.org>
}
?item ?predicate ?url.
?property wikibase:directClaim ?predicate.
}
- –Shisma (talk) 15:26, 4 May 2024 (UTC)
- can I explicitly exclude sitelinks? – Shisma (talk) 15:23, 4 May 2024 (UTC)
- Sure.
select ?url
where {
{
select * where {
hint:SubQuery hint:optimizer "None".
VALUES ?url {
<https://www.podchaser.com/podcasts/this-american-life-399612>
<https://www.podchaser.com/podcasts/this-american-life-399612/>
<http://www.podchaser.com/podcasts/this-american-life-399612>
<http://www.podchaser.com/podcasts/this-american-life-399612/>
<http://podchaser.com/podcasts/this-american-life-399612>
<http://podchaser.com/podcasts/this-american-life-399612/>
<https://www.podchaser.com/>
<http://www.podchaser.com/>
<http://podchaser.com/>
<https://www.podchaser.com>
<http://www.podchaser.com>
<http://podchaser.com>
<https://en.wikipedia.org/wiki/Double_bond>
<https://en.wikipedia.org/wiki/Double_bond/>
<http://en.wikipedia.org/wiki/Double_bond>
<http://en.wikipedia.org/wiki/Double_bond/>
<https://en.wikipedia.org/>
<http://en.wikipedia.org/>
<https://en.wikipedia.org>
<http://en.wikipedia.org>
}
MINUS {
SELECT distinct ?url WHERE {
[] schema:isPartOf ?url .
}
}
?item ?predicate ?url.
?property wikibase:directClaim ?predicate.
}
}
}
Infrastruktur (talk) 16:14, 4 May 2024 (UTC)
- I should note that the above won't list direct claims that match the base URL used for sitelinks either. If this is unwanted, it seems better to ensure that the directClaim BGP is ran first, limiting the worst-case performance of the query. Infrastruktur (talk) 09:05, 5 May 2024 (UTC)
find persons that are male and female edit
(yes I know there are exceptions, but in general this is a glitch.) Hi, I was stumbling across some persons with wrong sex or gender (P21) (incorrectly assigned). As a first step I tried to find people having both sex or gender (P21) using
#title: humans with double sex/gender (non trans)
SELECT ?item where { # (count(distinct ?item) as ?count) WHERE { #
?item wdt:P21 wd:Q6581072 .
?item wdt:P21 wd:Q6581097 .
#filter not exists {?item wdt:P21/wdt:P279* wd:Q189125 }
#?item wdt:P31 wd:Q5 .
#bind (replace(xsd:string(?item),"http://www.wikidata.org/entity/","") as ?itemQid)
#SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en" } .
} order by ?item offset 0 limit 50
but this leads to timeout in many combinations of parameters tried.
counting also leads to timeout
#title: count humans with double sex/gender (non trans)
SELECT (count(distinct ?item) as ?count) WHERE {
?item wdt:P21 wd:Q6581072 .
?item wdt:P21 wd:Q6581097 .
filter not exists {?item wdt:P21/wdt:P279* wd:Q189125 }
}
only when I count the cases without the transgender clause, I get some 934 cases. But not the items themselves.
#title: count humans with double sex/gender
SELECT (count(distinct ?item) as ?count) WHERE {
?item wdt:P21 wd:Q6581072 .
?item wdt:P21 wd:Q6581097 .
}
Can somebody help me to re-write the first query to overcome the time constraint?
There are some more questions arising:
- How can I, in general, perform such overall quality / consistency checks on large sets (like people)? In most cases I have to tweak queries and only sometimes I will succeed. Is there something with higher privileges and higher timeout thresholds, that can run such consistency checks for all of us?
- Is data quality an issue?
- And does wikidata scale enough to allow queries on large sets, assuming that the number of items modelling people in wikidata will increase and increase. If not, is quality of data an issue that cannot be pursued any longer and we all will depend on finding incorrect data only by incident?
best --Herzi Pinki (talk) 22:25, 5 May 2024 (UTC)
- Try it!
SELECT ?item WHERE { ?item wdt:P21 wd:Q6581072, wd:Q6581097 }
- This works for me in ~25 seconds, resulting in the 934 results. If timeouts are an issue, the slicing service may help you breaking down any amount of results, but it is not a single query any more and you may want to use a script. Here is an example for demonstration purposes; you probably want to work with much larger slices such as
WDQS_SLICE_LIMIT=500_000
if necessary. —MisterSynergy (talk) 23:25, 5 May 2024 (UTC)
- works for me. thanks --Herzi Pinki (talk) 19:18, 8 May 2024 (UTC)
Hi everyone. I have a set of nearly 200 Wikidata items and I want to know if there's a chance to make a query to know:
- in how many Wikipedias the article exists?
- and if they are available on es.wiki, pt.wiki, en.wiki and fr.wiki separately?
Is all this possible? I look forward to your comments. Thank you very much. Paula (WDU) (talk) 16:44, 6 May 2024 (UTC)
- You may try this:
- --Flipping Switches (talk) 04:26, 8 May 2024 (UTC)Try it!
SELECT ?item (COUNT(?wiki) AS ?wikis) ?en ?es ?fr ?pt WHERE { hint:Query hint:optimizer "None". VALUES ?item { wd:Q20 wd:Q21 wd:Q22 } # <- Replace with your set of items OPTIONAL { ?wiki schema:about ?item; schema:isPartOf / wikibase:wikiGroup "wikipedia". } OPTIONAL { ?en_ schema:about ?item; schema:isPartOf <https://en.wikipedia.org/>; schema:name ?en. } OPTIONAL { ?es_ schema:about ?item; schema:isPartOf <https://es.wikipedia.org/>; schema:name ?es. } OPTIONAL { ?fr_ schema:about ?item; schema:isPartOf <https://fr.wikipedia.org/>; schema:name ?fr. } OPTIONAL { ?pt_ schema:about ?item; schema:isPartOf <https://pt.wikipedia.org/>; schema:name ?pt. } } GROUP BY ?item ?en ?es ?fr ?pt ORDER BY DESC(?wikis)
IPAA Members with location information edit
Hello everyone! I've managed to create a query that will display all members of the Indigenous Performing Arts Alliance who have any of their work location, residence, or place of birth listed as within Canada. However, every time I attempt to reformat the query to get a results column that shows which place within Canada a given member is associated with, I get syntax errors. I am assuming it has to do with the way I am grouping those three properties, but I'm having a hard time figuring out how to resolve it and would appreciate help.
What I have so far:
SELECT DISTINCT ?member ?memberLabel ?memberDescription ?sitelinks WHERE {
?member wdt:P463 wd:Q110938829;
(wdt:P19|wdt:P551|wdt:P937|wdt:P27)/wdt:P131* wd:Q16; #pob/residence/work location
wikibase:sitelinks ?sitelinks.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC (?sitelinks)
MichifDorian (talk) 14:42, 8 May 2024 (UTC)
- Meaning "which place", what columns whith what values do you want to add? Flipping Switches (talk) 18:59, 8 May 2024 (UTC)
- I was initially imagining one "place" column which displayed information from any of the identified properties, but it would probably read more clearly if they were separate columns. So I guess the goal is a query which produces the person's name, and then "place of birth", "work location", and "residence" columns -- any one of which could be blank or could have one or more values. It should only return results that have a value for at least one of those properties, though. MichifDorian (talk) 19:54, 8 May 2024 (UTC)
- Here your initial query (citizenship removed) filters "has at least one of values". Several members are duplicated by different residence which you could
GROUP_CONCAT
.Flipping Switches (talk) 01:26, 9 May 2024 (UTC)Try it!SELECT ?member ?memberLabel ?memberDescription ?sitelinks ?birthLabel ?workLabel ?residenceLabel WITH { SELECT DISTINCT * WHERE { ?member wdt:P463 wd:Q110938829; (wdt:P19|wdt:P551|wdt:P937)/wdt:P131* wd:Q16. } } AS %i WHERE { INCLUDE %i. OPTIONAL { ?member wdt:P19 ?birth } OPTIONAL { ?member wdt:P551 ?residence } OPTIONAL { ?member wdt:P937 ?work } ?member wikibase:sitelinks ?sitelinks. SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } } ORDER BY DESC (?sitelinks) ?memberLabel
- Here your initial query (citizenship removed) filters "has at least one of values". Several members are duplicated by different residence which you could
- I was initially imagining one "place" column which displayed information from any of the identified properties, but it would probably read more clearly if they were separate columns. So I guess the goal is a query which produces the person's name, and then "place of birth", "work location", and "residence" columns -- any one of which could be blank or could have one or more values. It should only return results that have a value for at least one of those properties, though. MichifDorian (talk) 19:54, 8 May 2024 (UTC)