Open main menu

Wikidata:Request a query

(Redirected from Wikidata:RAQ)

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

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

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 2019/10.

Search for multiple (specific) types via wikibase:api "EntitySearch" running into 50 record limit?Edit

I'm trying to let people search for anything, as long as it's something in the popular culture like an actor, book, movie, sports team, etc. So if I search "Patriots" I should get the NFL Team and the movie The Patriot. And I think I've gotten a good start with this:

SELECT distinct ?ordinal ?item ?itemLabel ?itemDescription ?image WHERE {
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "EntitySearch";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:search "Patriots";
                    mwapi:limit 1000;
                    mwapi:language "en" .
    ?item wikibase:apiOutputItem mwapi:item .
    ?ordinal wikibase:apiOrdinal true .
  } 
  ?item wdt:P31/wdt:P279* ?type.
  OPTIONAL{?item wdt:P18 ?image .}
  FILTER( ?type in (wd:Q5, wd:Q17537576, wd:Q12973014))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} ORDER BY ASC (?ordinal) LIMIT 100
Try it!

I'm using the EntitySearch service so that I can get the :apiOrdinal value to help sort the results for relevance, and ?type contains the whole list of parent types so that I can just use "creative work" and "sports team" as catch-alls to filter on. (The FILTER list of Q types is incomplete, I was just starting to get the test going)

But, the problem happens when you replace "Patriots" with "Fellowship". In theory I should get the book and film Fellowship of the Ring. But the only result is for the "group" Fellowship of the Ring. If you take off the FILTER, you'll see it only returns 40 records from the EntitySearch for some reason? And if you change it to "Fellowship of the Ring" it will correctly find the book and film, so the EntitySearch can find those two. But for some reason it seems like EntitySearch is capping the results to a limit of 40?

Any ideas on what's going on? Or an alternative to do this search differently, preferably still with the :apiOrdinal so that the results are sorted by relevance?

Thanks!  – The preceding unsigned comment was added by Thomas.lumen (talk • contribs) at 17:16‎, 6. 7. 2019 (UTC).

Get all items that come under a particular categoryEdit

I'm trying to find all items that can be marked as scientific items. For instance, photosynthesis, ohm's law, etc. Like, photosynthesis belongs to Biological Processes category but when I use the category in the below query I also get "death" as an item which is not an intended item. I think I am leaning more towards the concepts that one studies during his school years. Please let me know if there's a specific category that I should aim for?

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

 – The preceding unsigned comment was added by 204.246.162.35 (talk • contribs) at 22:35‎, 16 July 2019 (UTC).

WikiProject every politicianEdit

Could you please help to create a query which returns all politicians and the linked persons to them (like sibling, siblings-in-law, family, unusual family relationships etc.)? Can this be done with one query?  – The preceding unsigned comment was added by Danail Stoyanov (talk • contribs) at 13 août 2019 à 09:15‎ (EST) (UTC).

WikiData SPARQL query for plants dataEdit

I am a BE CSE undergrad student currently working on collecting information about plants so I am trying to query about all the plants from the wikidata Query Engine but I couldn't find a way. Any help on this is appreciableλ  – The preceding unsigned comment was added by 14.139.161.242 (talk • contribs) at 14 août 2019 à 10:06‎ (EST) (UTC).

Does this help you get started?

SELECT ?plant ?plantLabel
WHERE
{
         # any instance or subclass of plant
  ?plant wdt:P31/wdt:P279* wd:Q756
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
}

Try it!

Query of Species of Trees in different languagesEdit

I am an artists looking at the representation of species of trees in Wikidata, looking for a list that contains f.ex. Quercus Robur, Acacia_podalyriifolia etc.

It is interesting to see that this query gets all individual trees, not species, as a result: SELECT ?item ?itemLabel WHERE {

 ?item wdt:P31 wd:Q10884. #is instance of tree
 ?item wdt:P17 wd:Q30 . # specific for US, uses other code than 'citizenship' of a place specified non-human

}

 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }

}

Rather, I'm looking for a list like this: https://en.wikipedia.org/wiki/Category:Trees_of_Africa (without the location property)

I would like to see what kind of species of trees are represented in EN, FR, SP, NL. I started something like this, but as 'taxon' is a property with a wide variety of values, it gives me a time out. SELECT ?item ?itemLabel WHERE {

 ?item wdt:P31 wd:Q16521;
   wdt:P105 wd:Q7432.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }

}

On the Wiki Data page of f.ex. Quercus Robur, everything refers to Quercus, which leads me to Genus/Family/Order etc. It would be wonderful to get some help on this. Many thanks in advance!

chemical substances used in plasticsEdit

I would like to retrieve the CAS number of all chemical substances that are used in plastics.

This is what I built with the Query Helper, however it returns nothing. Any suggestions?

#CAS number of all chemical substances that are used in plastics
   SELECT ?sitelink ?item ?CAS_Registry_Number WHERE {
   ?item wdt:P279 wd:Q79529.
   ?sitelink schema:about ?item.
   SERVICE wikibase:label { bd:serviceParam wikibase:language 
   "[AUTO_LANGUAGE],en". }
   ?item wdt:P366 wd:Q60082936.
   OPTIONAL { ?item wdt:P231 ?CAS_Registry_Number. }
}
ORDER BY (?itemLabel)|
Try it!

Trouble with nested town/state entitiesEdit

There is a lighthouse (Q7084940) in Scituate (Q2415936), Massachusetts. Scituate is located within Plymouth County (Q54086). How come asking for lighthouses in Plymouth County doesn't show Scituate light? (other lighthouses do show however) This is the query I'm using:

SELECT DISTINCT ?item ?itemLabel ?coords ?image WHERE { ?item wdt:P31 wd:Q39715 ;

             wdt:P131 wd:Q54086 ;
             wdt:P625 ?coords

OPTIONAL { ?item wdt:P18 ?image } SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],nb,nn,en,fi" } } ORDER BY ?itemLabel


Neverind. I figured it out. Just add an asterisk after P131.

Removing double entities/tags from dutch municipalitiesEdit

Is it possible to make a query that deletes the dutch municipality tag (Q2039348) from entitites that don't have a start (P580) or/and end time date set (P582)? And if yes, how would such a query look? if not, what's the best way to tackle this problem (clicking 2000 times is not really an option)? Antoni1626 (talk) 08:39, 7 October 2019 (UTC)

This is the query for the municipalities with a start date set:


SELECT ?municipalityLabel ?municipality ?begindatum ?einddatum ?CBS_gemeentecode WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?municipality p:P31 ?statement.
  ?statement ps:P31 wd:Q2039348;
    pq:P580 ?begindatum;
 optional {  ?statement pq:P582 ?einddatum }
  
 optional { ?municipality wdt:P382 ?CBS_gemeentecode. } 

}

Try it!

Query for municipalities without start and end date:

SELECT ?municipalityLabel ?municipality ?begindatum ?einddatum ?CBS_gemeentecode WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?municipality p:P31 ?statement.
  ?statement ps:P31 wd:Q2039348;
 minus {?statement pq:P580 ?begindatum;}
 minus {  ?statement pq:P582 ?einddatum }
  
 optional { ?municipality wdt:P382 ?CBS_gemeentecode. } }

Try it!

Here is an example: https://www.wikidata.org/wiki/Q2096012 (place), no start or end date, but the other entity https://www.wikidata.org/wiki/Q40671450 (municipality) has the correct dates. Antoni1626 (talk) 08:55, 7 October 2019 (UTC)

@Antoni1626: You can take the QIds from your second query, and use them in Quickstatements (help). In short you'll need to feed it a set of rows in the form -Q9769 [tab] P31 [tab] Q2039348. (Obvs, you need to be responsible for the goodness of your analysis...) --Tagishsimon (talk) 09:08, 7 October 2019 (UTC)
@Antoni1626, Tagishsimon: QS seems to remove the first statement that fulfils the criteria that instance of (P31) equals municipality of the Netherlands (Q2039348) without taking any qualifiers into consideration. See my experiments in Wikidata Sandbox (Q4115189), both using exactly the same QS syntax from above:
The above "QS syntax" will thus only work as long as the statement without qualifier(s), the one you want to remove,
  • is the first of the instance of (P31) statements and that
  • there are just one statement without qualifier(s).
In this particular case this may be fulfilled if all the faulty statements are "older" and the correct statements are "newer" and there is not more, or less, than one faulty statement per object, but in general you will have to check that these conditions before using QS to do such removal of statements. --Larske (talk) 09:38, 8 October 2019 (UTC)
Good point; hadn't occurred to me. --Tagishsimon (talk) 10:21, 8 October 2019 (UTC)

Reduce duplicates of Flanders Institute Arts Organization IDEdit

Hi guys

Currently, I am contributing for the following WikiProject Wikidata:WikiProject_Performing_arts/Data_structure/Data_modelling_issues. I identified some duplicates. There are multiple records with a Flanders Art Institute organization ID who refer to the same organization.

I created a query to identify the number duplicates, but unfortunately, I am not able to make a list with the duplicates. The goal is to have a query, which could help to identify and tackle the duplicates.

Former query: try it

List should have at least the following attributes (with example records, see example):

Flanders Art Institute organization ID ItemIdentifier_1 ItemLabel_1 ItemIdentifier_2 ItemLabel_2
361416 Q54804772 Q54804772 Q55829416 De Ontmoeting

Thank you for your help.

@Boxomi: Something similar; does this work for you?
SELECT ?org ?item ?itemLabel ?item2 ?item2Label WHERE {
  ?item wdt:P5164 ?org.
  ?item rdfs:label ?itemLabel . filter(lang(?itemLabel)="en") 
  ?item2 wdt:P5164 ?org.
  ?item2 rdfs:label ?item2Label . filter(lang(?item2Label)="en") 
  filter(str(?item) < str(?item2) )
}
Try it! --Tagishsimon (talk) 19:20, 7 October 2019 (UTC)

multiple optional conditionsEdit

Hi! we currently try to request a query for a list of q-numbers. The q-number is replaced from time to time. Depending on the requested q-number the given values of orcid, employer(s) and gender are given (or not) in the data set of the item. Therefore, those values should somehow be indicated as "optional".

SELECT  ?orcid ?employerLabel ?genderLabel ?parents ?parentsLabel
                        WHERE { 
                         OPTIONAL {?item wdt:P496 ?orcid }.  
                         OPTIONAL {?item wdt:P108 ?employer }. 
                         OPTIONAL {?item wdt:P21 ?gender }.
                               
                         Values ?item { wd:Q28360163 }. 
                         SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
                        }

Try it!

Can someone please correct my syntax? Thank you! Eva

@EvaSeidlmayer: It seem you have to include at least one non optional triple pattern to make the query work. As far as I can tell you’re interested into human items, so I added something related to the item being a human.
SELECT ?orcid ?employerLabel ?genderLabel ?parents ?parentsLabel
WHERE { 
  ?item wdt:P31 wd:Q5 .
  OPTIONAL {?item wdt:P496 ?orcid }.  
  OPTIONAL {?item wdt:P108 ?employer }. 
  OPTIONAL {?item wdt:P21 ?gender }.

  Values ?item { wd:Q28360163 }. 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
 }
Try it!
author  TomT0m / talk page 11:40, 8 October 2019 (UTC)
Oh, reading the sparql doc on optional it seems that « optional » must just have something on the left. After testing, the « values » clause can serve as this something and just inversing stuffs in your original query works :
SELECT ?orcid ?employerLabel ?genderLabel ?parents ?parentsLabel WHERE {
  VALUES ?item {
    wd:Q28360163
  }
  OPTIONAL { ?item wdt:P496 ?orcid. }
  OPTIONAL { ?item wdt:P108 ?employer. }
  OPTIONAL { ?item wdt:P21 ?gender. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! author  TomT0m / talk page 11:46, 8 October 2019 (UTC)

Mayors of Dutch municipalitiesEdit

Hello everyone. I am trying to construct a query for {{Wikidata list}} to request the mayors of Dutch municipalities. My current attempt is here User:Robin van der Vliet/Dutch mayors. It already shows a list of municipalities, their mayors and the start time and end time of a random mayor of the list.

I would like the query result to be sectioned by municipality, and below each section a table of all mayors of that municipalities with their start times, end times and birthdates.

Thank you for your help. Feel free to edit my subpage. Robin van der Vliet (talk) (contribs) 15:22, 8 October 2019 (UTC)

@Robin van der Vliet: I made a proposal on your subpage.
--Larske (talk) 07:29, 9 October 2019 (UTC)
Thank you, that is exactly what I want. Robin van der Vliet (talk) (contribs) 08:46, 9 October 2019 (UTC)

List of entries with given letterEdit

How to list all entries of a given letter of a given language? For example, all letter "h" entries from pt.wikipedia.org?  – The preceding unsigned comment was added by Rodvillar (talk • contribs).

SELECT ?item ?itemLabel WHERE {

 ?item wdt:P31 wd:Q146.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }

}

@Rodvillar: if I understand correctly, this. ?item rdfs:label ?itemLabel is used to get the label of the item within the query, rather than relying on the wikibase:label service; this facilitates filtering on the label value within the query. In the example below, the English label is fetched; change the lang filter to your preferred label language.
SELECT ?item ?itemLabel WHERE {
  ?item wdt:P31 wd:Q146.
  ?item rdfs:label ?itemLabel . filter(lang(?itemLabel)="en") 
  filter strstarts(?itemLabel,"H")
}
Try it! --Tagishsimon (talk) 15:27, 10 October 2019 (UTC)

Number of women who won any Nobel prizesEdit

Hello, could someone write a query to get all the women who won a Nobel prizes (physiologie, physics, chemistry, litterature, peace, economy)? Thanks in advance. Pamputt (talk) 08:31, 11 October 2019 (UTC)

select  ?womanwinner ?womanwinnerLabel {
  ?womanwinner wdt:P166/(wdt:P79|wdt:P361 )* wd:Q7191 ;  # using part of OR subclass of because of a discussion ongoing at the time of the query wrinting that did not totally reached a decision
          wdt:P21 wd:Q6581072 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
 }
Try it! @Pamputt:
 – The preceding unsigned comment was added by TomT0m (talk • contribs) at 11 oktober 2019 kl. 11.38‎ (UTC).


@Pamputt: Here is another one if you want to see their photos on a timeline (all but one have a image (P18) in Wikidata)
Click on the vertical double-arrow to get all the boxes visible.
#defaultView:Timeline{"hide": "?date"}
SELECT (SAMPLE(?im) AS ?image) ?womanwinner ?womanwinnerLabel ?date (YEAR(?date) AS ?year) ?priceLabel WHERE {
    ?womanwinner wdt:P21 wd:Q6581072; p:P166 [ ps:P166 ?price; pq:P585 ?date ].
    ?price (wdt:P79|wdt:P361)* wd:Q7191 .
    OPTIONAL { ?womanwinner wdt:P18 ?p18 }
    BIND(IF(BOUND(?p18),?p18,URI("http://commons.wikimedia.org/wiki/Special:FilePath/Head_silhouette.svg")) AS ?im)
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
 }
GROUP BY ?womanwinner ?womanwinnerLabel ?date ?priceLabel
Try it!
--Larske (talk) 19:34, 11 October 2019 (UTC)
@TomT0m, Larske: thank you :D Pamputt (talk) 20:04, 12 October 2019 (UTC)

Query for list of polygons and how many sides they haveEdit

Been trying to make a query that lists all polygons (Q37555) and lists their side count. Not sure how to approach it.. Comments in the query would be welcome :)

--MoonyTheDwarf (talk) 18:07, 11 October 2019 (UTC)

@MoonyTheDwarf: Here is a query proposal.
SELECT ?item ?itemLabel ?itemDescription ?corners {
  ?item wdt:P279 wd:Q37555 .
  ?item p:P2670 [ ps:P2670 wd:Q26401; pq:P1114 ?corners ] .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?corners

Try it!

--Larske (talk) 18:28, 11 October 2019 (UTC)
Works! Thanks! --MoonyTheDwarf (talk) 18:33, 11 October 2019 (UTC)
@MoonyTheDwarf: EC - slightly expanded report. We seem to record values in two different places.
SELECT ?item ?itemLabel ?line_segments ?sides_hasfacetpolytope ?vertexes ?sides_partsoftheclass
WHERE 
{
  ?item wdt:P279* wd:Q37555.
  OPTIONAL { ?item p:P1678 [ps:P1678 wd:Q166154; pq:P1114 ?line_segments] . }
  OPTIONAL { ?item p:P1312 [ps:P1312 wd:Q26382; pq:P1114 ?sides_hasfacetpolytope] . }
  OPTIONAL { ?item p:P2670 [ps:P2670 wd:Q26401; pq:P1114 ?vertexes] . }
  OPTIONAL { ?item p:P2670 [ps:P2670 wd:Q26382; pq:P1114 ?sides_partsoftheclass] . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by desc(?vertexes) desc(?sides_partsoftheclass)
Try it! --Tagishsimon (talk) 18:38, 11 October 2019 (UTC)
Some comments:
  • ?item wdt:P279 wd:Q37555. The item is a subclass of Q37555
  • ?item wdt:P279* wd:Q37555. The item is a subclass of Q37555, or a subclass of something that is a subclass, &c
  • ?item p:P1678 [ps:P1678 wd:Q166154; pq:P1114 ?line_segments] the item has a P1678 property. Its value is Q166154 and it has a qualifier P1114 (quantity)
  • OPTIONAL {}The item may or may not include this property. Don't reject the item if the property is missing
  • SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } get labels for items
--Tagishsimon (talk) 18:44, 11 October 2019 (UTC)

Pulling ADS bibcode and DOI for works citedEdit

I'd like to pull a list works cited by articles with main subject (P921) Gowganda Formation (Q69888216). I'd like to get the ADS bibcode (P819) and DOI (P356) of the works cited, but I'm getting the the ADS bibcode and DOI for the articles with main subject (P921) Gowganda Formation (Q69888216). I've generated the following, can someone edit it to get the right ADS bibcode and DOI?

SELECT ?Gowganda_Formation ?Gowganda_FormationLabel ?cites_work ?cites_workLabel ?ADS_bibcode ?DOI WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?Gowganda_Formation wdt:P921 wd:Q69888216.
  OPTIONAL { ?Gowganda_Formation wdt:P2860 ?cites_work. }
  OPTIONAL { ?Gowganda_Formation wdt:P819 ?ADS_bibcode. }
  OPTIONAL { ?Gowganda_Formation wdt:P356 ?DOI. }
} LIMIT 100
Try it!

Thanks for any help. Trilotat (talk) 16:08, 12 October 2019 (UTC)

@Trilotat: this, I think.
SELECT ?Gowganda_Formation ?Gowganda_FormationLabel ?cited_by ?cited_byLabel ?ADS_bibcode ?DOI WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?Gowganda_Formation wdt:P921 wd:Q69888216.
  ?cited_by wdt:P2860 ?Gowganda_Formation .
  OPTIONAL { ?cited_by wdt:P819 ?ADS_bibcode. }
  OPTIONAL { ?cited_by wdt:P356 ?DOI. }
} LIMIT 100
Try it! --Tagishsimon (talk) 16:46, 12 October 2019 (UTC)

Listeria issueEdit

I am trying make a list of spa/termal venues in Portugal and made a query in w:pt:Usuário:GualdimG/Testes/Listas/3 Termas. My difficulty is that the geographic coordinates are not appearing in the resulting Wikidata table. Would you help me change what is wrong? Thank you. GualdimG (talk) 10:53, 14 October 2019 (UTC)

@GualdimG: Sorted. Your wdt:P625 was slightly in the wrong place & not needed anyway ... Listeria will give you a P625 if it exists without it being in the query. --Tagishsimon (talk) 11:26, 14 October 2019 (UTC)
  Done! @Tagishsimon:, thank you. Greetings, GualdimG (talk) 11:31, 14 October 2019 (UTC)

x-axisEdit

Hi,
Someone know why the x-axis of this query didn't ajust automatically ?

#defaultView:LineChart
#Requête permettant de dénombrer le nombre d'article scientifiques publié par décennie dans une LANGUE donnée
select ?decade (count (distinct ?item) as ?count) where {?item wdt:P31 wd:Q13442814 ; wdt:P577 ?date ; wdt:P407 wd:Q188 . BIND(round(year(?date)/10)*10 AS ?decade) .} group by ?decade order by ?decade

Try it!

 – The preceding unsigned comment was added by Simon Villeneuve (talk • contribs) at 14 oktober 2019 kl. 19.00‎ (UTC).

@Simon Villeneuve: Don't know. But maybe this modified query gives the expected result:
#defaultView:LineChart
#Requête permettant de dénombrer le nombre d'article scientifiques publié par décennie dans une LANGUE donnée
SELECT ?decade (count (distinct ?item) as ?count) where {
  ?item wdt:P31 wd:Q13442814 ; wdt:P577 ?p577 ; wdt:P407 wd:Q188 .
  BIND(SUBSTR(CONCAT(STR(ROUND(YEAR(?p577)/10)*10),'/01/01'),1,4) AS ?decade)
} 
GROUP BY ?decade
Try it!
--Larske (talk) 23:06, 14 October 2019 (UTC)
Yes, it did. Thank you. Simon Villeneuve (talk) 23:36, 14 October 2019 (UTC)
By the way, can someone show me how to put additionnal P407 in this graph, like P150, P5287, P1321, etc. ? I've try to just put wdt:P407 ?lang , but the English ones timeout the query. Simon Villeneuve (talk) 13:10, 15 October 2019 (UTC)

Siege durationsEdit

Hello I have this query often populated with start and end date OR duration (P2047). However the duration is not expressed in days. For instance Siege of the British Residency in Kabul (Q7510538) was 8 hours but is shown 8 days. How to express duration in a number of days only ? Thanks, Bouzinac (talk) 19:12, 15 October 2019 (UTC)

@Bouzinac: You have to deal with the duration values as they are: they're denominated in hours, days, weeks, months ... probably other time units, too ... with no useful normalisation. So you'll have to get the unit and then do some maths in the query.
SELECT ?si_ge ?si_geLabel ?duration ?normalised_duration ?unitLabel WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
?si_ge wdt:P31 wd:Q188055.
OPTIONAL { ?si_ge wdt:P580 ?date_de_d_but. }
OPTIONAL { ?si_ge wdt:P582 ?date_de_fin. }
OPTIONAL { ?si_ge wdt:P276 ?lieu. } 
?si_ge p:P2047 [ps:P2047 ?duration ; psv:P2047  [ wikibase:quantityAmount ?normalised_duration; wikibase:quantityUnit ?unit ] ].
}
Try it! --Tagishsimon (talk) 20:42, 15 October 2019 (UTC)
Hello Tagishsimon (talkcontribslogs), another folk helped solve the pb :) thanks
result Bouzinac (talk) 08:12, 17 October 2019 (UTC)
Yeah. I suck at SPARQL. Anyway. Normalised time values are go.
SELECT ?si_ge ?si_geLabel ?duration ?normalised_duration ?unitLabel WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
?si_ge wdt:P31 wd:Q188055.
OPTIONAL { ?si_ge wdt:P580 ?date_de_d_but. }
OPTIONAL { ?si_ge wdt:P582 ?date_de_fin. }
OPTIONAL { ?si_ge wdt:P276 ?lieu. } 
?si_ge p:P2047 [ps:P2047 ?duration ; psn:P2047  [ wikibase:quantityAmount ?normalised_duration; wikibase:quantityUnit ?unit ] ].
}
Try it! --Tagishsimon (talk) 21:55, 17 October 2019 (UTC)

Multi-graph and colorsEdit

Inspired by User:Fuzheado/queries, I tried to do the following query (somewhat ridiculous but for the fun of it :-þ)

The following query uses these:

Features: Graph (Q24515287)    

 1 #defaultView:Graph
 2 SELECT ?item1 ?image1 ?item1Label ?item2 ?image2 ?item2Label ?edgeLabel ?rgb
 3 
 4 WITH {
 5   SELECT ?item1 ?rgb WHERE {
 6     {
 7       SELECT (?character as ?item1) WHERE {
 8         ?game wdt:P31 wd:Q7889.
 9         ?game wdt:P179 wd:Q219623.
10         ?game wdt:P674 ?character.
11       }
12     }
13     UNION
14     {
15       # Find sport -
16       SELECT (?sport as ?item1) WHERE {
17         ?game wdt:P31 wd:Q7889.
18         ?game wdt:P179 wd:Q219623.
19         ?game wdt:P674 ?character.
20         ?character wdt:P641 ?sport.
21       }
22     }
23     UNION
24     {
25       # Find games
26       SELECT (?game as ?item1) ?rgb WHERE {
27         ?game wdt:P31 wd:Q7889.
28         ?game wdt:P179 wd:Q219623.  
29         BIND("7FFF00" as ?rgb)
30       }
31     }
32 
33   }
34 } AS %item1
35 WITH {
36   SELECT (?item1 AS ?item2) WHERE {
37     INCLUDE %item1.
38   }
39 } AS %item2
40 WHERE {
41   INCLUDE %item1 .
42   INCLUDE %item2 .
43   ?item1 ?wdt ?item2 .
44   ?edge wikibase:directClaim ?wdt;
45         a wikibase:Property .
46   OPTIONAL { ?item1 wdt:P18 ?image1 . }
47   OPTIONAL { ?item2 wdt:P18 ?image2 . }
48   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
49 }

But why don’t the game-items get coloured? As far as I can see in Table view, it is set fine?

(also, there’s quite a bit of repetition between the three sub-queries − I initally wanted to do a first WITH query to get the games, and then derive the other 3 on it − but I was not sure how to return the same contents in one of the subquery − happy to take pointers :)

Jean-Fred (talk) 20:47, 15 October 2019 (UTC)

@Jean-Frédéric: Weirdly this is the second question on here about ?rgb not working at the same time :-)
For some reason, ?rgb can only be set for maps and for bubble charts. It can't be set for other kinds of charts - see Wikidata:SPARQL query service/Wikidata Query Help/Result Views for details, although why this is the case is unclear to me. Andrew Gray (talk) 21:49, 18 October 2019 (UTC)
@Andrew Gray: Thanks for the answer! But on eg this query, RGB is used and that colors the bubbles. Jean-Fred (talk) 22:02, 18 October 2019 (UTC)
@Jean-Frédéric: You're right and I'm an idiot - it also works for graph queries - I was confused because I saw "graph" and thought of charts. :-)
It looks like it would work if you moved "rgb" to be the third option - SELECT ?item1 ?image1 ?item1Label ?rgb ?item2 ?image2 ?item2Label ?edgeLabel - it seems to be position-sensitive? Andrew Gray (talk) 22:37, 18 October 2019 (UTC)

Generating a query with a DOI and ADS bibcodeEdit

I have the ADS bibcode (P819) for all these DOIs in a spreadsheet (which I can paste into a SPARQL query). I'd like to generate a query the provides the QID, the DOI, the bibcode and the data I provide for each of the DOIs below. For example, I'd like the first DOI to return 1982PreR...18..237L in the fourth column; the second to return 1990PreR...48..375N; the third 2019PreR..325....1D; the fourth 1997Sedim..44...67Z

SELECT ?item ?doi ?ADS_bibcode WHERE {
  VALUES ?doi {
    "10.1016/0301-9268(82)90012-2" TextStringA1
    "10.1016/0301-9268(90)90049-V" TextStringB2
    "10.1016/J.PRECAMRES.2019.02.010" TextStringC3
    "10.1111/J.1365-3091.1997.TB00424.X" TextStringD4
  }
  ?item wdt:P356 ?doi.
  OPTIONAL { ?item wdt:P1476 ?title. }
  OPTIONAL { ?item wdt:P819 ?ADS_bibcode. }
}
Try it!

With these results I can sort by the DOI in Wikidata and run a Quickstatement to add the others. Thanks. I'm totally confused, aren't I? Thanks for trying. Trilotat (talk) 07:12, 16 October 2019 (UTC)

@Trilotat: Not clear what the ask is. To get QIds from a DOI? To get QIds from a Bibcode? Your query does the first, so the second of these? Seems too simple.
SELECT ?item ?doi ?ADS_bibcode WHERE {
  VALUES ?ADS_bibcode {
"1982PreR...18..237L"
"1990PreR...48..375N"
"2019PreR..325....1D"
"1997Sedim..44...67Z"
  }
  ?item wdt:P819 ?ADS_bibcode. 
  OPTIONAL {?item wdt:P356 ?doi. }
  OPTIONAL { ?item wdt:P1476 ?title. }
}
Try it! --Tagishsimon (talk) 09:15, 16 October 2019 (UTC)
@Tagishsimon: Sorry; I made the request unnecessarily confusing. I simply need to add a text string to my query so that same text string returns with my results. I added some text after each DOI in my query example (which breaks the query) but you can see it if you edit my original request.
@Trilotat: Okay, I roughly get you. Here's how it works ... if I've added the textstring to the wrong thing, I think you can sort it out.
SELECT ?item ?doi ?ADS_bibcode ?this WHERE {
  VALUES (?doi ?text)
    {("10.1016/0301-9268(82)90012-2" "TextStringA1")
    ("10.1016/0301-9268(90)90049-V" "TextStringB2")
    ("10.1016/J.PRECAMRES.2019.02.010" "TextStringC3")
    ("10.1111/J.1365-3091.1997.TB00424.X" "TextStringD4")
  }
  ?item wdt:P356 ?doi.
  OPTIONAL { ?item wdt:P1476 ?title. }
  OPTIONAL { ?item wdt:P819 ?ADS_bibcode. }
  bind(concat(?ADS_bibcode,?text) as ?this )
}
Try it! --Tagishsimon (talk) 17:31, 16 October 2019 (UTC)
@Tagishsimon: Perfect! Thanks!! I've used it multiple times already. Thanks so much. Trilotat (talk) 00:16, 18 October 2019 (UTC)

Random lexemes (let's say, 30) and sensesEdit

Hello! would it be possible to get a random list of lexemes and senses in a given language? It can be any given number, but let's limit it to 20 or 30. -Theklan (talk) 18:14, 16 October 2019 (UTC)

@Theklan: something like this?
SELECT ?lexeme ?lemma ?sense ?gloss WHERE {
  SERVICE bd:sample {
    ?lexeme dct:language wd:Q8752.
    bd:serviceParam bd:sample.limit 20 .
  }
  ?lexeme wikibase:lemma ?lemma.
  OPTIONAL {
    ?lexeme ontolex:sense ?sense.
    ?sense skos:definition ?gloss.
  }
}
ORDER BY ?lexeme ?lemma ?sense ?gloss
Try it! --TweetsFactsAndQueries (talk) 11:07, 19 October 2019 (UTC)
@TweetsFactsAndQueries: Not sure, I can't see senses there! -Theklan (talk) 12:34, 19 October 2019 (UTC)
@Theklan: oh, sorry, it needs to be skos:definition instead of rdfs:label for the glosses. I edited the query above (didn’t seem worth adding a second {{SPARQL}} block for). --TweetsFactsAndQueries (talk) 12:59, 19 October 2019 (UTC)
@TweetsFactsAndQueries: Is it possible to limit also to one sense if the item has more than one? -Theklan (talk) 13:04, 19 October 2019 (UTC)
@Theklan: I think that would look like this:
SELECT ?lexeme (GROUP_CONCAT(?lemma; separator = "/") AS ?lemmata) ?sampleSense (SAMPLE(?gloss) AS ?anyGloss)
WITH {
  SELECT ?lexeme (SAMPLE(?sense) AS ?sampleSense) WHERE {
    SERVICE bd:sample {
      ?lexeme dct:language wd:Q8752.
      bd:serviceParam bd:sample.limit 20 .
    }
    OPTIONAL { ?lexeme ontolex:sense ?sense. }
  }
  GROUP BY ?lexeme
} AS %lexemesWithOneSense
WHERE {
  INCLUDE %lexemesWithOneSense.
  ?lexeme wikibase:lemma ?lemma.
  OPTIONAL { ?sampleSense skos:definition ?gloss. }
}
GROUP BY ?lexeme ?sampleSense
Try it! --TweetsFactsAndQueries (talk) 13:14, 19 October 2019 (UTC)
Thanks!
@Jura1: I don’t think so, just rather exotic – I think I learned about it at phabricator:T194884#4391965. --TweetsFactsAndQueries (talk) 15:47, 19 October 2019 (UTC)
Unfortunately this query times out for Russian (because it has 100000+ lexemes)... --Infovarius (talk) 23:19, 20 October 2019 (UTC)

specific colorsEdit

Hi,
In the following query, I want to associate similar colors from this publication to the qualifiers P4100 :

#defaultView:BarChart
select distinct ?législatureLabel (count (distinct ?item) as ?count) ?groupeLabel where {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
?item p:P39 ?fonction ; 
      wdt:P39 wd:Q15964890 .
?fonction ps:P39 wd:Q15964890 ;
          pq:P4100 ?groupe ; 
          pq:P2937 ?législature ; 
          pq:P768 ?circonscription . 
minus{?circonscription wdt:P31 wd:Q3248048 .}
}
group by ?législatureLabel ?groupeLabel ?count

Try it!
Someone can help me with this ? Simon Villeneuve (talk) 18:18, 18 October 2019 (UTC)

@Simon Villeneuve: strangely, you can define the colours for a bubble graph or for a map, but not a bar chart. See the documentation here; it's the ?rgb element in some of the examples. This query finds the right colour codes:
select distinct ?législatureLabel (count (distinct ?item) as ?count) ?groupeLabel ?rgb where {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
?item p:P39 ?fonction ; 
      wdt:P39 wd:Q15964890 .
?fonction ps:P39 wd:Q15964890 ;
          pq:P4100 ?groupe ; 
          pq:P2937 ?législature ; 
          pq:P768 ?circonscription . 
minus{?circonscription wdt:P31 wd:Q3248048 .}
  optional { ?groupe wdt:P465 ?rgb }
}
group by ?législatureLabel ?groupeLabel ?count ?rgb
Try it!
So to make a bubble chart for just the 42nd Parliament (it looks a bit confusing on a bubble chart if you have all the terms at the same time) you could do this:
#defaultView:BubbleChart
select distinct (count (distinct ?item) as ?count) ?groupeLabel ?rgb where {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
?item p:P39 ?fonction ; 
      wdt:P39 wd:Q15964890 .
?fonction ps:P39 wd:Q15964890 ;
          pq:P4100 ?groupe ; 
          pq:P2937 wd:Q21157957 ; # 42nd only
          pq:P768 ?circonscription . 
minus{?circonscription wdt:P31 wd:Q3248048 .}
  optional { ?groupe wdt:P465 ?rgb }
}
group by ?groupeLabel ?count ?rgb
Try it!
Note that some of the parties don't have colours yet (eg NDP) so they're shown with a default. Andrew Gray (talk) 21:42, 18 October 2019 (UTC)

Finding duplicate sets of normal ranked coordinates?Edit

Now and then I discover items like Q2462125 with more than 1 set of coordinate location (P625), both ranked normal. When I scan areas in JOSM (an OpenStreetMap editor), such items will not show up. This problem can be fixed either by removing all but one coordinate location (P625) or ranking one of them higher than the rest. So how can I find i.e. all instances of human settlement (Q486972) in Denmark (Q35) with more than 1 set of coordinate location (P625), all ranked normal? --Hjart (talk) 08:52, 19 October 2019 (UTC)

@Hjart: This will find any item with two "equally ranked" coordinates but no better one (so two preferred would also show up). Not quite the same thing but hopefully good enough for what you need! Unfortunately it times out if you include type, so I've commented that out for now. Andrew Gray (talk) 09:08, 19 October 2019 (UTC)
SELECT ?item ?itemLabel ?coord1 ?coord2 WHERE {
    ?item wdt:P17 wd:Q35 . # in Denmark
    # ?item wdt:P31 ?type . ?type wdt:P279* wd:Q486972 . # settlement
    ?item wdt:P625 ?coord1 .
    ?item wdt:P625 ?coord2 . 
  filter ( ?coord1 != ?coord2 ) . # not the same
  SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en,[AUTO_LANGUAGE]". }
}
Try it!
Update - it works (takes about 45s) if you don't include labels. Andrew Gray (talk) 09:10, 19 October 2019 (UTC)
SELECT ?item ?coord1 ?coord2 WHERE {
    ?item wdt:P17 wd:Q35 . # in Denmark
    ?item wdt:P31 ?type . ?type wdt:P279* wd:Q486972 . # settlement
    ?item wdt:P625 ?coord1 .
    ?item wdt:P625 ?coord2 . 
  filter ( ?coord1 != ?coord2 ) . # not the same
  # SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en,[AUTO_LANGUAGE]". }
}
Try it!
I get an EOF error (line 7) when trying to run it? --Hjart (talk) 09:19, 19 October 2019 (UTC)
@Hjart: try now - I missed a linebreak in the template and so it lost the last } character. Andrew Gray (talk) 09:23, 19 October 2019 (UTC)
Here is another query that avoids duplicates by using "<" instead if "!=" in the FILTER and by DISTINCT in the SELECT. It also wraps the inner query with an outer query to add the labels as well as the distance in kilometers between the two coordinates. For most object in the result the distance is very small, but for Stubberup (Q2361745) it is almost 20 kilometers.
SELECT ?item ?itemLabel ?coord1 ?coord2 ?distance WITH {
SELECT DISTINCT ?item ?coord1 ?coord2 WHERE {
    ?item wdt:P17 wd:Q35 . # in Denmark
    ?item wdt:P31/wdt:P279* wd:Q486972 . # settlement
    ?item wdt:P625 ?coord1 .
    ?item wdt:P625 ?coord2 . 
  filter ( ?coord1 < ?coord2 ) . # not the same
} 
} AS %i WHERE {
  include %i
  BIND(geof:distance(?coord1, ?coord2) as ?distance)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en,[AUTO_LANGUAGE]". }
}
ORDER BY ?itemLabel
Try it!
--Larske (talk) 11:12, 19 October 2019 (UTC)

@Hjart: Building on Larske's query, you'll also want to get Nordborg (Q1997687) where the two sets of coords are the same. As there are no deprecated rank P625s in this set, this query works. Filters would be more slightly more complex if we took deprecated into account, although I guess we'd just need to check that neither ?rank1 nor ?rank2 were deprecated.

SELECT ?item ?itemLabel ?coord1 ?coord2 ?distance ?rank1 ?rank2 WITH {
SELECT DISTINCT ?item ?coord1 ?coord2 ?rank1 ?rank2 WHERE {
    ?item wdt:P17 wd:Q35 . # in Denmark
    ?item wdt:P31/wdt:P279* wd:Q486972 . # settlement
    hint:Prior hint:gearing "forward".
    ?item p:P625 ?stat1 . ?stat1 ps:P625 ?coord1 . ?stat1 wikibase:rank ?rank1 . 
    ?item p:P625 ?stat2 . ?stat2 ps:P625 ?coord2 . ?stat2 wikibase:rank ?rank2 . 
    filter ( str(?stat1)  < str(?stat2) ) . # not the same
    filter ( str(?rank1)  = str(?rank2) ) . # are the same
} 
} AS %if WHERE {
  include %if
  BIND(geof:distance(?coord1, ?coord2) as ?distance)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en,[AUTO_LANGUAGE]". }
}
ORDER BY ?itemLabel
Try it! --Tagishsimon (talk) 11:44, 19 October 2019 (UTC)
Duplicated almost same coordinates are often an annoying problem. Shouldn't a bot be upranking one coordinate if two coordinates are very very geographically close ?Bouzinac (talk) 12:01, 19 October 2019 (UTC)
Absolutely not. They should be dealt with, but I think a humans need to get involved and work out which one (if either) is more accurate / precise / appropriate. It would be pointless to choose the worse of two coords merely to winnow down the wdt:P625 to a single value. If they're exactly the same, then yes, perhaps. (And yes, I suppose, if they are within inches, too.) --Tagishsimon (talk) 14:36, 19 October 2019 (UTC)
It's what I was saying : if two coordinates are very very close (let's say less than 3 meters?), then one of them should be randomly be upranked... Bouzinac (talk) 15:50, 20 October 2019 (UTC)
Depends on the precision. If their precisions are not the same, human interaction is required. Thank you very much, everyone, for the very helpful queries, BTW. I immediately put them to good use and fixed 35 settlements :-) --Hjart (talk) 21:44, 20 October 2019 (UTC)
You'll get knocked down with that sort of imprecision, Bouzinac: Mill Ends Park (Q753064) / https://www.wikidata.org/wiki/Q753064#P2046 --Tagishsimon (talk) 22:00, 20 October 2019 (UTC)
I don't know if it would be me to get knocked down but there are many imprecisions : for a same point, Google maps and Openstreetmap do not show exaclty the same map // there is 5 meter imprecision with the GPS of your smartphone/GPS device...
You tell about Mill end Park :
so who is right?Bouzinac (talk) 09:40, 21 October 2019 (UTC) Bouzinac (talk) 09:40, 21 October 2019 (UTC)
Not sure I agree. Those linked from the toolserver page seem to be in the same position. (Note I've tweaked the item coords such that they do point at the park. Manually.) --Tagishsimon (talk) 09:59, 21 October 2019 (UTC)

Chess players without specific Elo ratingEdit

I am trying to generate a list that gives all chess players that do not have an Elo rating for a particular month (January 2012). I constructed the following query, but at the moment it returns also players with this rating month. I guess the filtering is incorrect. Can somebody fix it?

SELECT DISTINCT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P106 wd:Q10873124.
  ?item wdt:P1440 ?fide .
  ?item p:P1087 [pq:P585 ?date ] .
  
  FILTER ( ?date != "+2012-01-01T00:00:00Z"^^xsd:dateTime ) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 100

Try it!

-- Steak (talk) 11:39, 20 October 2019 (UTC)

@Steak: something like:
SELECT DISTINCT ?item ?itemLabel with {
select ?item where
{
  ?item wdt:P106 wd:Q10873124.
  ?item wdt:P1440 ?fide .
  minus {
           ?item p:P1087 [pq:P585 ?date ] .
           FILTER ( month(?date)=1 && year(?date)=2012 ) .
        }
  } limit 100 } as %i 
where
{
  include %i
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Tagishsimon (talk) 12:00, 20 October 2019 (UTC)
Looks good, thanks! Steak (talk) 12:36, 20 October 2019 (UTC)

Flourished datesEdit

For a given author, can we query, please, for the years of publication of their earliest and last known papers? Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 15:59, 20 October 2019 (UTC)

@Pigsonthewing: Not a hard one, here is for Einstein :
select (min(year(?date)) as ?min_year) (max(year(?date)) as ?max_year ) {
  values ?author { wd:Q937 }
  ?author ^wdt:P50/wdt:P577 ?date .
}
Try it! (did not filtered by work type, anything with a publication date will match) author  TomT0m / talk page 16:15, 20 October 2019 (UTC)
(note that in this example we obviously have a posthumous work).