Shortcut: WD:RAQ

Wikidata:Request a query

From Wikidata
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. 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 2018/07.

Project
chat

Lexicographical
data

Administrators'
noticeboard

Development
team

Translators'
noticeboard

Request
a query

Requests
for deletions

Requests
for comment

Bot
requests

Requests
for permissions

Property
proposal

Properties
for deletion

Partnerships
and imports

Interwiki
conflicts

Bureaucrats'
noticeboard

Slide show with images[edit]

I am trying to make a slideshow with automatic looping over the a set of images returned from a WDQS query. I have made some progress on a page that loops via CCS and Javascript. I was wondering if there is a simpler way. For instance, with SAMPLE and HTML meta refresh. This will not work:

#defaultView:ImageGrid
SELECT (SAMPLE(DISTINCT ?image) AS ?image) WHERE {
  ?image ^wdt:P18 / wdt:P50 / (wdt:P108| wdt:P463 | wdt:P1416/wdt:P361*) wd:Q24283660 .
}

Try it!

ISNI - human without ISNI, ordered by quantity of sitelinks and externalIDs[edit]

SELECT ?item  ?itemLabel ?id ?sl ((?id * ?sl) as ?rank)
{
  {
       SELECT *
       {  ?item wdt:P31 wd:Q5.
          ?item wikibase:identifiers 10 .
          #?item wikibase:sitelinks 10 .
          ?item wikibase:identifiers ?id.
          ?item wikibase:sitelinks ?sl.
          MINUS{?item wdt:P213 [] .}
       }
       LIMIT 1000
  }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?rank) DESC(?id) DESC(?sl) ?item

Try it!

I get a time out if "?item wikibase:identifiers 10" is commented out. Is there a way to extend the result set, e.g. write ">10" or VALUES 10,11,12,13,14,15? 2.245.11.89 15:25, 5 July 2018 (UTC)

You can do either of these, but it doesn't solve the timeout problem.
SELECT ?item  ?itemLabel ?id ?sl ((?id * ?sl) as ?rank)
{
  {
       SELECT *
       {  ?item wdt:P31 wd:Q5.
          ?item wdt:P106 wd:Q486748. # constrained to pianists so we see some results.
          values ?id {10 11 12 13 14 15}   # you can constrain ?id using values
          values ?sl {10 11 12 13 14 15 }
          #filter (?id >12)          # or filter ?ids based on value
          #filter (?sl >12)
          ?item wikibase:identifiers ?id.
          ?item wikibase:sitelinks ?sl.       
        MINUS{?item wdt:P213 [] .}
       }
       LIMIT 1000
  }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?rank) DESC(?id) DESC(?sl) ?item
Try it! --Tagishsimon (talk) 16:03, 5 July 2018 (UTC)

Tagishsimon - thanks a lot! Do you know how to require certain WP-Links without making individual statements for each WP? The following works but does not look very compact:

   ?article schema:about ?item .
   ?article schema:isPartOf <https://en.wikipedia.org/>.
   ?article_fr schema:about ?item .
   ?article_fr schema:isPartOf <https://fr.wikipedia.org/>.

2.245.11.89 20:49, 5 July 2018 (UTC)

To my knowledge, those are what is required to ascertain a language wiki article. But you can construct a set of languages for a report ... couple of techiques (h/t CalvinBall for the first)
wd:Q458 wdt:P37 ?langs . #European Union official langauges
?langs wdt:P424 ?langcode . #language code of language
BIND(IRI(CONCAT("https://",str(?langcode),".wikipedia.org/"))AS ?wikilangs)
?wikiarticle schema:about ?pioneer .
?wikiarticle schema:isPartOf ?wikilangs.
or from a list (in this exampe the list also contains the language name - both examples taken from List of specific people and whether they have WP articles in the EU official languages, above.
values (?language ?langcode) { ("French" "fr") ("German" "de") ("Italian" "it") ("Polish" "pl") ("Spanish" "es") ("Finnish" "fi") ("English" "en") ("Portuguese" "pt") ("Croatian" "hr") ("Dutch" "nl") ("Romanian" "ro") ("Bulgarian" "bg") ("Swedish" "sv") ("Danish" "da") ("Czech" "cs") ("Slovak" "sk") ("Slovene" "sl") ("Hungarian" "hu") ("Estonian" "et") ("Latvian" "lv") ("Lithuanian" "lt") ("Greek" "el") ("Irish" "ga") ("Maltese" "mt") } .
BIND(IRI(CONCAT("https://",str(?langcode),".wikipedia.org/"))AS ?wikilangs)
Depends exactly what you're looking for. For 'has a link to any language wikipedia - you can probably get the isPartof into a ?variable and then filter(contains(str(?variable)),"wikipedia.org") since commons, etc, sport wikimedia URLs (iirc). --Tagishsimon (talk) 21:19, 5 July 2018 (UTC)

User:Tagishsimon, thanks again! For me the following is good:

VALUES (?langcode) { ("en") ("fr") ("es") ("ru") } .
BIND(IRI(CONCAT("https://",str(?langcode),".wikipedia.org/"))AS ?wikilangs)

it seems that for to avoid duplicates one then has to add

GROUP BY ?item ?itemLabel ?id ?sl 

92.226.171.245 11:58, 6 July 2018 (UTC)

Yes, you'll get one row per language sitelink. If you group them, you might want to use (group_concat(distinct ?variable;separator=", ") in the select to capture all of the links into a single cell. If you're just after the four languages, you could go back to a set of four discrete clauses each dealing with a single language, so you can display their results in discrete columns. --Tagishsimon (talk) 15:57, 6 July 2018 (UTC)

Property Path Length[edit]

Is there a way to have the exact path length of each match of a property path query as part of the result? Example: descendants of queen Elizabeth II, how to extend this to have a column that has 1 for Charles (son), 2 for William (grandson), and so on:

SELECT ?descendant ?descendantLabel
WHERE
{
  ?descendant (wdt:P22|wdt:P25)+ wd:Q9682.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

--176.198.214.53 19:04, 6 July 2018 (UTC)

This. Sometime later I might understand how it works; from [1].
select ?descendentLabel (count(?mid) as ?distance)  where { 
  ?descendent (wdt:P22|wdt:P25)* ?mid .
  ?mid (wdt:P22|wdt:P25)+ wd:Q9682. 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
group by ?descendentLabel 
order by ?distance ?descendentLabel
Try it! --Tagishsimon (talk) 19:18, 6 July 2018 (UTC)
Thanks a lot, this is ingenious! Helpful to genealogists. With a very large set of descendants this might become slow with all the intermediate results, I guess?
--176.198.214.53 20:46, 6 July 2018 (UTC)
Yes. It'll do Philip II of Spain (Q34417) (d.1598, ~1400 descendents) but times out on Edward III of England (Q129247) (d.1377). I'm still not absolutely fathoming its workings; if we remove the group and constrain the descendent to Anne, why is she a (wdt:P22|wdt:P25)* of herself? Why 7-times over. smh.
select ?descendent ?descendentLabel ?midLabel  where { 
  values ?descendent {wd:Q151754} .
  ?descendent (wdt:P22|wdt:P25)* ?mid .
  ?mid (wdt:P22|wdt:P25)+ wd:Q9682. 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
Try it! --Tagishsimon (talk) 21:18, 6 July 2018 (UTC)

Date exists as /prop/ but not as /prop/direct/[edit]

In the query below, which returns values from Christian Bacmeister (Q108027), there is a p:P569 value but there is not a wdt:P569 value. The record displays a date of birth. I'm confused. Why is the DoB not being found using wdt:? --Tagishsimon (talk) 22:45, 7 July 2018 (UTC)

SELECT * where
{
  values ?item {wd:Q108027} . 
  optional {?item wdt:P569 ?wdt_value.}
  optional {?item p:P569 ?p_value.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

date of birth (P569) on Christian Bacmeister (Q108027) has deprecated rank. wdt: only returns values with normal or preferred rank. --Pasleim (talk) 22:50, 7 July 2018 (UTC)
Thanks. I should have known that :) --Tagishsimon (talk) 22:56, 7 July 2018 (UTC)

All librairies in Quebec[edit]

Hi,

I want a query who give to me all librairies in Quebec Province. I have this, but I don't know who to have the « tree » of Q176. Someone can help me ? Simon Villeneuve (talk) 10:43, 8 July 2018 (UTC)

SELECT ?item WHERE {?item wdt:P31/wdt:P279* wd:Q7075 . ?item wdt:P131 wd:Q176 . }

Try it!

@Simon Villeneuve: I think this might be half of the answer: but it times out.
SELECT ?item WHERE {?item wdt:P31/wdt:P279* wd:Q7075 . ?item wdt:P131/wdt:P131* wd:Q176 . }
Try it!
If we constrain it to country=Canada it seems to work a little better:
SELECT distinct ?item ?itemLabel ?locatedLabel WHERE {
  ?item wdt:P31/wdt:P279* wd:Q7075 . 
  ?item wdt:P17 wd:Q16.
  ?item wdt:P131/wdt:P131* wd:Q176 .
  ?item wdt:P131 ?located.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Tagishsimon (talk) 11:00, 8 July 2018 (UTC)
  • I was going to suggest something similar. BTW, there is a "P" missing before "279".
    --- Jura 11:10, 8 July 2018 (UTC)
Thank you very much ! Now, I know that I must use "wdt:PROPERTY/wdt:PROPERTY*" to get the "tree". Simon Villeneuve (talk) 11:37, 8 July 2018 (UTC)
I've taken the liberty of adding the missing P to all three queries. And thrown a distinct in for good measure. --Tagishsimon (talk) 12:17, 8 July 2018 (UTC)

Property only one value[edit]

Hi! Using this query

SELECT ?item WHERE { ?item wdt:P31 wd:Q24046192 . ?item wdt:P971 wd:Q4663261 }

Try it!

how can I specify that category combines topics (P971) should have only 1 value? Thank you very much! --Epìdosis 12:25, 8 July 2018 (UTC)

@Epìdosis: something like:
SELECT ?item ?itemLabel with { 
  select ?item (count(?P971) as ?P971count) 
  WHERE { 
    ?item wdt:P31 wd:Q24046192 . 
    ?item wdt:P971 wd:Q4663261 .
    ?item wdt:P971 ?P971. 
  } group by ?item } as %i where
{ include %i
  filter(?P971count=1)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Tagishsimon (talk) 12:45, 8 July 2018 (UTC)
Perfect, thank you! --Epìdosis 12:53, 8 July 2018 (UTC)

Former municipality of Norway[edit]

I am making a Query for listing all former municipality of Norway (Q18663579) and all municipality of Norway (Q755707) in Norway, when running this Query the item and itemLabel are shown only the first time when I am running the Query. If I am modyfying the qurety the columns for item and itemlabel becomes empty. I have
SELECT ?item ?itemLabel ?tidligere_norsk_kommune WHERE {

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } Can someone help? Breg Pmt (talk) 20:46, 10 July 2018 (UTC)

@Pmt: so here's a first query for discussion. It finds anything which is either (or both of) a former municipality of Norway (Q18663579) or a municipality of Norway (Q755707) and it then lists what type of thing the item is - so we get multiple rows for the same item if it has a number of P31s. Tell us where you want this query to go next. (Your query ... entirely lacking any triples :)
SELECT distinct ?item ?itemLabel ?typeLabel WHERE {
  {?item wdt:P31 wd:Q18663579.}
  UNION
  {?item wdt:P31 wd:Q755707.}
  ?item wdt:P31 ?type.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],no,en". }
} order by ?itemLabel
Try it! --Tagishsimon (talk) 22:24, 10 July 2018 (UTC)

@Tagishsimon: My initial Query goes like this

SELECT ?item ?itemLabel ?tidligere_norsk_kommune WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  { ?tidligere_norsk_kommune wdt:P31 wd:Q18663579. }
  UNION
  { ?tidligere_norsk_kommune wdt:P31 wd:Q755707. }
  UNION
  { ?tidligere_norsk_kommune wdt:P31 wd:Q19730508. }
  ?tidligere_norsk_kommune wdt:P17 wd:Q20.
}

Try it!


My initial problem is when running this Query it has three columns for item, itemLabel, tidligere_norsk_kommune. The column tidligere_norsk_kommune gives me an Q value only. I want also to have the label (name) and description for the column tidligere_norsk_kommune. The tabel name occurs the first time i am running the Query The but as soon as I am doing a change in the Query the description and the label (name) is disappering. Breg Pmt (talk) 16:54, 11 July 2018 (UTC)

SELECT ?tidligere_norsk_kommune ?tidligere_norsk_kommuneLabel ?tidligere_norsk_kommuneDescription WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  { ?tidligere_norsk_kommune wdt:P31 wd:Q18663579. }
  UNION
  { ?tidligere_norsk_kommune wdt:P31 wd:Q755707. }
  UNION
  { ?tidligere_norsk_kommune wdt:P31 wd:Q19730508. }
  ?tidligere_norsk_kommune wdt:P17 wd:Q20.
}
Try it!

You don't use ?item but ?tidligere_norsk_kommune so you can use the service on it. --ValterVB (talk) 17:49, 11 July 2018 (UTC)

@Pmt: Yes. In more detail, it does not much matter what the variable names are. It does matter that you tell the query what to fill them with. In your query, you select ?item but nowhere in the rest of the query do you define what is to go into the ?item variable. Your query is predicated entirely on a variable called ?tidligere_norsk_kommune which stands for a Qid where the item has certain values - Q18663579, etc. (And so I don't know why you would ever get any data in the ?item and ?itemLabel columns.) Per ValterVB, where you use SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } in a query, it is open to you to add suffixes to variables to get the Label and the Description of the item, so, ?tidligere_norsk_kommuneLabel and ?tidligere_norsk_kommuneDescription. I notice also that you get 4600+ results if you omit ?tidligere_norsk_kommune wdt:P17 wd:Q20 ... I do not know the data well, but perhaps most communes do not have a P17 value. Or else Norway used to be bigger than it now is. --Tagishsimon (talk) 18:23, 11 July 2018 (UTC)
@Thagishmon: Thank you very much. Problem solved. Very Nice. Pmt (talk) 16:30, 14 July 2018 (UTC)

ImageGrid with a default picture?[edit]

Hello, I am trying to build an imageGrid for a list of people but all the entries don't have a image (P18) :

SELECT distinct ?person ?personLabel (YEAR(?date) as ?year) (SAMPLE(?picture) as ?picture)
WHERE 
{
  ?person p:P166 ?distinction.
  ?distinction ps:P166 wd:Q55455135 .
  ?distinction pq:P585 ?date .
  optional {?person wdt:P18 ?picture }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
group by ?person ?personLabel ?date
order by ?year

Try it!

At the moment, and it is the default way imageGrid is working, only the entries with a P18 are displayed in this list, but I would like to display all entries, using this picture as a fallback when there is no P18. Any idea if it is doable? Symac (talk) 03:55, 12 July 2018 (UTC)

@Symac: I don't know if it is possible to define a specific commons picture as the value for a variable without having that picture as the image (P18) for at least one object. While waiting for someone who may know how to do this, I made the following query that has a workaround by using the image that is assigned to some object as the "default picture".
#defaultView:ImageGrid
SELECT distinct ?person ?personLabel (YEAR(?date) as ?year) (SAMPLE(?pic) as ?pic)
WHERE 
{
  VALUES ?any_object_with_the_default_picture { wd:Q4115189 } #using the sandbox object for this demo, wd:Q157002 is another object with an image that can be used as a "placeholder" 
  ?person p:P166 ?distinction.
  ?distinction ps:P166 wd:Q55455135 .
  ?distinction pq:P585 ?date .
  optional {?person wdt:P18 ?picture }
  ?any_object_with_the_default_picture wdt:P18 ?defaultpic .
  BIND(IF(BOUND(?picture),?picture,?defaultpic) AS ?pic)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
group by ?person ?personLabel ?date
order by ?year
Try it!
--Larske (talk) 07:27, 12 July 2018 (UTC)
  • Interesting idea!
    --- Jura 16:23, 13 July 2018 (UTC)

Compare years of awared titles[edit]

I am looking for a query that queries all items that have title of chess player (P2962): Grandmaster (Q105269) and International Master (Q752119), and both of these statements have start time (P580), and the filter shall be, that start time (P580) of Grandmaster (Q105269) shall be earlier or same year as the start time (P580) of International Master (Q752119). Steak (talk) 11:16, 12 July 2018 (UTC)

SELECT ?item ?start_GM ?start_IM WHERE {
  ?item p:P2962 [ ps:P2962 wd:Q105269; pq:P580 ?start_GM ], [ ps:P2962 wd:Q752119; pq:P580 ?start_IM ] .
  FILTER(YEAR(?start_GM) <= YEAR(?start_IM)) .
}
Try it! MisterSynergy (talk) 11:53, 12 July 2018 (UTC)
Thanks! Steak (talk) 17:18, 12 July 2018 (UTC)

Tour de France / Brest[edit]

Pour répondre à une question des no label (Q46010402) (https://twitter.com/Archives_Brest/status/1017346495853580288), pourriez-vous nous aider à rédiger les requêtes suivantes :

Merci d'avance

--2le2im-bdc (talk) 06:52, 13 July 2018 (UTC)

@2le2im-bdc: If I understand the question ... we have no information on races that pass through Brest (your question 1) but the report below lists the 12 races we know of which started or finished in Brest (at least, to the limit of my understanding of the data).
SELECT ?item ?itemLabel ?date ?terminal
WHERE 
{
  ?item wdt:P361* ?stage.      # part of
  ?stage wdt:P31 wd:Q33881.    # Tour de France
  {?item wdt:P1427 wd:Q12193.} # starts in Brest
  UNION                        # and or
  {?item wdt:P1444 wd:Q12193.} # finishes in Brest
  optional {?item wdt:P585 ?date}      # get the date if it exists   
  optional {?item wdt:P1427 ?starts}   # get the start location value
  optional {?item wdt:P1444 ?finishes} # get the end location value
  bind(if(?starts=wd:Q12193,"starts","finishes") as ?terminal) # work out if the race started or ended in Brest
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Tagishsimon (talk) 07:55, 13 July 2018 (UTC)
Excellent ! Thanks a lot @Tagishsimon:!

Completeness check[edit]

At # of people depicted, there is a suggestion to use "depicts" recursively to check its own completeness. Is this doable?
--- Jura 16:25, 13 July 2018 (UTC)

P910 & "Items that link to a Commons category" complex constraint[edit]

One of the complex constraints on topic's main category (P910) is "Items that link to a Commons category":

SELECT ?item ?categoryitem ?commonscategory WHERE { ?item wdt:P910 ?categoryitem . ?commonscategory schema:about ?item . ?commonscategory schema:isPartOf <https://commons.wikimedia.org/> . FILTER REGEX(STR(?commonscategory), "https://commons.wikimedia.org/wiki/Category:") . }

Try it!

I'm currently using that to clear through the backlog (per Wikidata:Requests for permissions/Bot/Pi bot 6), however after running through the first ~2,000 it's now giving query timeouts, even with "LIMIT 1000". Can anyone suggest something more efficient (and for bonus points, update the complex constraint definition for the property)? Thanks. Mike Peel (talk) 16:49, 14 July 2018 (UTC)

@Mike Peel: The following should work, stepping through the items with topic's main category (P910) about a third at a time. As with when we used this kind of method previously, you might need to re-run after the first set of passes, to pick up remaining stragglers.
SELECT ?item ?categoryitem ?commonscategory 

WITH { 
  SELECT ?item ?categoryitem WHERE {
    ?item wdt:P910 ?categoryitem . 
  } LIMIT 200000 OFFSET 0
} AS %items
  
WHERE {
  INCLUDE %items .
  ?commonscategory schema:about ?item . 
  ?commonscategory schema:isPartOf <https://commons.wikimedia.org/> . 
  FILTER (STRSTARTS(STR(?commonscategory), "https://commons.wikimedia.org/wiki/Category:")) . 
}
Try it!
No particular ideas come to mind as to how one might get it back into a one-shot query for a complex constraint check. Jheald (talk) 19:15, 14 July 2018 (UTC)
@Mike Peel: Note: some results of the above query may be occurring because someone has mistakenly used a topic's main category (P910) on a category item instead of a category's main topic (P301) --as per eg this diff, correcting one such case.
If you're bot-fixing the results of the query (an excellent idea), I hope that's a type of case you're looking out for. Jheald (talk) 09:56, 15 July 2018 (UTC)
@Jheald: Thanks, that seems to work. I'm bot-fixing the ones that are properly linked - so with topic's main category (P910) and category's main topic (P301) pairs that match, and no discrepancies between sitelinks and Commons category (P373) values. So it won't fix the more complex cases like you've linked to, those need to be fixed manually atm. Thanks. Mike Peel (talk) 06:53, 16 July 2018 (UTC)
@Mike Peel: Sure. No, I just wanted to flag up that this was a case to beware of -- that it may be worth checking that an item genuinely is a category item, before moving the Commons sitelink to it, even if it is the source of a category's main topic (P301) or target of a topic's main category (P910). Jheald (talk) 07:45, 16 July 2018 (UTC)
@Jheald: As in check for instance of (P31)=Wikimedia category (Q4167836)? I can add that in if needed, but it doesn't seem to be necessary so far, see Pi bot (talkcontribslogs)'s latest contribs. It's now over half-way through the backlog (a few thousand items). Thanks. Mike Peel (talk) 08:23, 16 July 2018 (UTC)
Actually, I've now added that check. I just realised that your query's only getting the first 200k entries, and there were >2k amongst those, so there are rather more pages to edit than I realised! Thanks. Mike Peel (talk) 08:40, 16 July 2018 (UTC)

IATA and ICAO codes (bis)[edit]

Hello Tagishsimon (talkcontribslogs) did a nice job with this query Wikidata:Request_a_query/Archive/2018/06#Look_after_an_IATA_or_ICAO_code and I'd like to adapt this way : add a column of French wikiarticle title + a column of English wikiarticle title. Can the query show both non existent french wikiarticles and non existent English wikiarticles : I wish to get (non existent Fr) + (existent Fr) + (non existent Eng) + (existent Eng) deduplicated per IATA/ICAO code. The columns needed : Q number, French wiki title, English wiki title, IATA, ICAO. Would it be possible? Many thanks! --Bouzinac (talk) 02:47, 16 July 2018 (UTC)

@Bouzinac: more columns than you need in this version, which selects airports in France that optionally have IATA, ICAO, and EN or FR articles. You can remove columns by removing variable names in the first (SELECT) line. I've given you a link to the article as well as the article name (one is clickable, other other is not). Let me know if you want modifications made - e.g. do you want France only? Do you want records that have neither an IATA not an ICAO.
SELECT distinct ?item ?itemLabel ?fr_article ?Wfr ?en_article ?Wen ?iata ?icao WHERE {
  ?item wdt:P31/wdt:P279* wd:Q1248784.                     # item must be an instance of an airport, or an instance that is a subclass of an airport
  ?item wdt:P17 wd:Q145 .                                  # item has country = UK
  optional {?item wdt:P238 ?iata .}                        # item may have a P238 (IATA) value
  optional {?item wdt:P239 ?icao .}                        # item may have a P239 (ICAO) value         
  optional {?Wen schema:about ?item;                       # item may have a sitelink
            schema:isPartOf <https://en.wikipedia.org/>;   # the sitelink points to en.wikipedia
            schema:name ?en_article.}                      # and has an article name
  optional {?Wfr schema:about ?item;                       # item may have a sitelink
            schema:isPartOf <https://fr.wikipedia.org/>;   # the sitelink points to fr.wikipedia 
            schema:name ?fr_article.}                      # and has an article name
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } . #this bit gets labels for Qid values
} order by ?itemLabel
Try it! --Tagishsimon (talk) 03:20, 16 July 2018 (UTC)
Thank you, it's perfect. And sadly, it shows a bunch of duplicates, notably redirections showed as single elements... Data to be cleaned :) --Bouzinac (talk) 04:38, 16 July 2018 (UTC)
Plus would you please add this exclusion : no military airports, "Q695850", no airport that has an end date , no airports that both don't have neither french wikipage nor englishwikipage ? Thanks a lot ! --Bouzinac (talk) 05:31, 16 July 2018 (UTC)
@Bouzinac: With luck, most of that is covered in the report below (which, this time, is for France, rather than the UK). Some comments: we're excluding Q695850 but in such a way that if the airfield is military and civilian, it will be excluded (and if that's a problem, I can do something more complicated such that we exclude only where there's a single military P31). And for end date, I found two items which had dissolved, abolished or demolished (P576) as main statements (and these are now excluded) ... I have not found any that have a Property Qualifier of P576 or end time (P582) for their P31 values ... so, if you come across example items that you think should be excluded and are not, point me at them. I have, for some reason, given you the ability to exclude items if they have neither an ICAO or IATA code - remove the # from # filter(bound(?code)) if you want to exclude these - there are only 3 of them. Again, let me know if you want any more changes doing.
SELECT distinct ?item ?itemLabel ?fr_article ?Wfr ?en_article ?Wen ?iata ?icao WHERE {
  ?item wdt:P31/wdt:P279* wd:Q1248784.                     # item must be an instance of an airport, or an instance that is a subclass of an airport
  ?item wdt:P17 wd:Q142 .                                  # ?item has country = France
  optional {?item wdt:P238 ?iata .}                        # item may have a P238 (IATA) value
  optional {?item wdt:P239 ?icao .}                        # item may have a P239 (ICAO) value         
  optional {?Wen schema:about ?item;                       # item may have a sitelink
            schema:isPartOf <https://en.wikipedia.org/>;   # the sitelink points to en.wikipedia
            schema:name ?en_article.}                      # and has an article name
  optional {?Wfr schema:about ?item;                       # item may have a sitelink
            schema:isPartOf <https://fr.wikipedia.org/>;   # the sitelink points to fr.wikipedia 
            schema:name ?fr_article.}                      # and has an article name
  minus {?item wdt:P31 wd:Q695850.}                        # exclude military airports
  bind(coalesce(?en_article,?fr_article) as ?article)      # create ?article if there is an EN or FR wikipedia article
  filter(bound(?article))                                  # select only if there is an ?article
  bind(coalesce(?iata,?icao) as ?code)                     # create ?code if there is an ICAO or IATA code 
#  filter(bound(?code))                                     # select only if there is an ?code
  minus {?item wdt:P576 ?date}                             # remove items with P576 (dissolved, abolished or demolished) as a main property
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr" } . #this bit gets labels for Qid values
} order by ?itemLabel
Try it! --Tagishsimon (talk) 08:33, 16 July 2018 (UTC)
Hi Tagishsimon, thank you. You may remove country:France as my search is global. You may notice some airports get a end date = https://www.wikidata.org/wiki/Q3887282 : P582 has a date.

You may also notice airports may have a valid IATA code. Exemple : http://www.wikidata.org/entity/Q1432638 no longer is ROV but RVI. Would you be kind to add these filters ? Thank you! --Bouzinac (talk) 14:24, 16 July 2018 (UTC)

@Bouzinac: Okay, here's the next version. ~14k airports. You may be busy. There are now so many columns (I added a country column) that the report service now gives results in its table-of-tables format, which may not be useful, though if you export to CSV all is fine. As before, chop out any column header variable names you do not want, such as ?country in the SELECT line, and the basic output will return to a more conventional tabular format.
Changes in this version: 1) all countries are included 2) it ignores IATA values where those values have an end-date property qualifier 3) it ignores airports which have a main property end date or a dissolved, abolished or demolished date 4) subclasses of military airports are excluded, lest there are any of these.
Should you want to look country by country, amend the the line ?item wdt:P17 ?country . to, for instance, ?item wdt:P17 wd:P142 . to get France. (i.e. the QId of the country, prefixed by wd:).
And to remind, you can filter the report to include only those airports with one or both of an ICAO or IATA code by removing the # from # filter(bound(?code)).
As normal, let me know if you want tweaks / spot issues.
SELECT distinct ?item ?itemLabel ?countryLabel ?fr_article ?Wfr ?en_article ?Wen ?iata ?icao WHERE {
  ?item wdt:P31/wdt:P279* wd:Q1248784.                     # item must be an instance of an airport, or an instance that is a subclass of an airport
  optional {?item wdt:P17 ?country .}                      # ?item may have a country 
  optional {?item p:P238 ?node .                           # item may have a P238 (IATA) node
           ?node ps:P238 ?iata.                            # and the node has a P238 (IATA) value
           filter not exists {?node pq:P582 [].}   }       # but the value is ignored if it has an end-date
  optional {?item wdt:P239 ?icao .}                        # item may have a P239 (ICAO) value         
  optional {?Wen schema:about ?item;                       # item may have a sitelink
            schema:isPartOf <https://en.wikipedia.org/>;   # the sitelink points to en.wikipedia
            schema:name ?en_article.}                      # and has an article name
  optional {?Wfr schema:about ?item;                       # item may have a sitelink
            schema:isPartOf <https://fr.wikipedia.org/>;   # the sitelink points to fr.wikipedia 
            schema:name ?fr_article.}                      # and has an article name
  minus {?item wdt:P31/wdt:P279* wd:Q695850.}              # exclude items having instances that are military airports & subclasses thereof
  bind(coalesce(?en_article,?fr_article) as ?article)      # create ?article if there is an EN or FR wikipedia article
  filter(bound(?article))                                  # select only if there is an ?article
  bind(coalesce(?iata,?icao) as ?code)                     # create ?code if there is an ICAO or IATA code 
#  filter(bound(?code))                                     # select only if there is an ?code
  minus {?item wdt:P576 [].}                               # remove items with P576 (dissolved, abolished or demolished) as a main property
  minus {?item wdt:P582 [].}                               # remove items with P582 (end date) as a main property
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr" } . #this bit gets labels for Qid values
} order by ?countryLabel ?itemLabel
Try it! --Tagishsimon (talk) 18:57, 16 July 2018 (UTC)
Hello, thanks but the query does not even look to start ? --Bouzinac (talk) 04:04, 17 July 2018 (UTC)
I'm getting 13941 results in 39315 ms ... what do you get, exactly? Timeout? --Tagishsimon (talk) 07:39, 17 July 2018 (UTC)
Oh strange! It worked out well in Chrome, but not in Microsoft Edge (my default browser)… I'll read the results carefully. Thanks again ! --Bouzinac (talk) 17:04, 17 July 2018 (UTC)
Odd, interesting & strange. I wonder what's going on there? Glad it's working, though ... enjoy your huge-looking work :) --Tagishsimon (talk) 18:00, 17 July 2018 (UTC)

Remove Duplicate[edit]

Hi,

Could you help me to remove duplicate from this query :

#écrivain né-e ou mort-e dans la Canton de Vaud 
SELECT ?ecrivain ?ecrivainLabel ?lieuNaissanceLabel ?lieuMortLabel
WHERE 
{
  ?ecrivain wdt:P31 wd:Q5.
  ?ecrivain wdt:P106 wd:Q36180.
   {?ecrivain wdt:P19 ?lieuNaissance.
  ?lieuNaissance wdt:P131 wd:Q12771.}
    UNION
  {?ecrivain wdt:P20 ?lieuMort.
  ?lieuMort wdt:P131 wd:Q12771.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr". }
}
ORDER BY ?ecrivainLabel

Try it!

For example Alphonse Mex (Q2839911) is duplicate.

Thanks

--2le2im-bdc (talk) 19:14, 16 July 2018 (UTC)

@2le2im-bdc: First select the distinct ?ecrivain that match your requirements; and then populate their ?lieu variables, in two linked but distinct queries. Right now you're getting all of the patterns that satisfy the unioned requirements. Something like these. The first version provides ?lieu values irrespective of whether they are in the Canton, so long as one of them is in the Canton. The second provides only those ?lieu values that are in the Canton. --Tagishsimon (talk) 21:22, 16 July 2018 (UTC)
#écrivain né-e ou mort-e dans la Canton de Vaud 
SELECT ?ecrivain ?ecrivainLabel ?lieuNaissanceLabel ?lieuMortLabel
WITH { SELECT DISTINCT ?ecrivain WHERE 
{
  ?ecrivain wdt:P106 wd:Q36180.
  {?ecrivain wdt:P19 ?lieuNaissance.
  ?lieuNaissance wdt:P131 wd:Q12771.}
  UNION
  {?ecrivain wdt:P20 ?lieuMort.
  ?lieuMort wdt:P131 wd:Q12771.}
  } } as %i
WHERE {include %i
  OPTIONAL {?ecrivain wdt:P19 ?lieuNaissance.}
  OPTIONAL {?ecrivain wdt:P20 ?lieuMort.}     
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr". }
} ORDER BY ?ecrivainLabel
Try it!
#écrivain né-e ou mort-e dans la Canton de Vaud 
SELECT ?ecrivain ?ecrivainLabel ?lieuNaissanceLabel ?lieuMortLabel
WITH { SELECT DISTINCT ?ecrivain WHERE 
{
  ?ecrivain wdt:P106 wd:Q36180.
  {?ecrivain wdt:P19 ?lieuNaissance.
  ?lieuNaissance wdt:P131 wd:Q12771.}
  UNION
  {?ecrivain wdt:P20 ?lieuMort.
  ?lieuMort wdt:P131 wd:Q12771.}
  } } as %i
WHERE {include %i
  OPTIONAL {?ecrivain wdt:P19 ?lieuNaissance. ?lieuNaissance wdt:P131 wd:Q12771.}
  OPTIONAL {?ecrivain wdt:P20 ?lieuMort. ?lieuMort wdt:P131 wd:Q12771.}     
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr". }
} ORDER BY ?ecrivainLabel
Try it!
@Tagishsimon: Thanks a lot. It's perfect.

Q206828[edit]

Hi, I come from eswiki. We don't understand why the infobox in this article shows that 128th was the team's worst position, when it was 189th, last year, as the Wikidata element states. It doesn't appear to happen in enwiki. Any ways to solve this? Thanks in advance, Gauri (talk) 00:49, 17 July 2018 (UTC)

@Gauri: Now fixed. The data is stored locally to es.wiki in w:es:Plantilla:Clasificación mundial de la FIFA/peores --Tagishsimon (talk) 01:23, 17 July 2018 (UTC)
Thanks, Tagishsimon! :) Gauri (talk) 01:35, 17 July 2018 (UTC)

Display P106[edit]

Hi, Could you help me to display the occupation of the results from this query?

#humain né-e ou mort-e dans la Canton de Vaud 
SELECT ?humain ?humainLabel ?lieuNaissanceLabel ?lieuMortLabel ?occup ?occupLabel
WITH { SELECT DISTINCT ?humain WHERE 
{
  ?humain wdt:P31 wd:Q5.
  ?humain wdt:P106* ?occup.
  {?humain wdt:P19 ?lieuNaissance.
  ?lieuNaissance wdt:P131 wd:Q12771.}
  UNION
  {?humain wdt:P20 ?lieuMort.
  ?lieuMort wdt:P131 wd:Q12771.}
  } } as %i
WHERE {include %i
  OPTIONAL {?humain wdt:P19 ?lieuNaissance.}
  OPTIONAL {?humain wdt:P20 ?lieuMort.}     
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr". }
} ORDER BY ?humainLabel

Try it!

Thanks --2le2im-bdc (talk) 14:42, 17 July 2018 (UTC)

@2le2im-bdc: Perhaps something like this, if we're keeping to one row per person. The occupation QId can be added back in if you need it.
#humain né-e ou mort-e dans la Canton de Vaud 
SELECT ?humain ?humainLabel ?lieuNaissanceLabel ?lieuMortLabel ?occ
WITH { SELECT ?humain (group_concat(?occupLabel;separator=", ") as ?occ) WHERE 
{
  ?humain wdt:P31 wd:Q5.
  ?humain wdt:P106 ?occup.
  ?occup rdfs:label ?occupLabel. filter(lang(?occupLabel)="fr")
  {?humain wdt:P19 ?lieuNaissance.
  ?lieuNaissance wdt:P131 wd:Q12771.}
  UNION
  {?humain wdt:P20 ?lieuMort.
  ?lieuMort wdt:P131 wd:Q12771.}
  } group by ?humain  } as %i
WHERE {include %i
  OPTIONAL {?humain wdt:P19 ?lieuNaissance.}
  OPTIONAL {?humain wdt:P20 ?lieuMort.}     
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr". }
} ORDER BY ?humainLabel
Try it!
And to get those without an occupation as well...
#humain né-e ou mort-e dans la Canton de Vaud 
SELECT ?humain ?humainLabel ?lieuNaissanceLabel ?lieuMortLabel ?occ
WITH { SELECT ?humain (group_concat(?occupLabel;separator=", ") as ?occ) WHERE 
{
  ?humain wdt:P31 wd:Q5.
  optional {?humain wdt:P106 ?occup.
  ?occup rdfs:label ?occupLabel. filter(lang(?occupLabel)="fr")}
  {?humain wdt:P19 ?lieuNaissance.
  ?lieuNaissance wdt:P131 wd:Q12771.}
  UNION
  {?humain wdt:P20 ?lieuMort.
  ?lieuMort wdt:P131 wd:Q12771.}
  } group by ?humain  } as %i
WHERE {include %i
  OPTIONAL {?humain wdt:P19 ?lieuNaissance.}
  OPTIONAL {?humain wdt:P20 ?lieuMort.}     
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr". }
} ORDER BY ?humainLabel
Try it! --Tagishsimon (talk) 15:18, 17 July 2018 (UTC)
@Tagishsimon: Wouahou! it's great! SPARQL is very powerful but also complex for a beginner as me... The last query is exactly what I look for.
Could I ask your help to display a last Proprierty in this query? I would like to display also archives at (P485) in addition.
Thanks in advance !
--2le2im-bdc (talk) 19:31, 17 July 2018 (UTC)
@2le2im-bdc: Only 6 have archives; Graham Greene has two archives, so once again we need to do the group_concat trick. It is complex: I'm still learning, but I seem to understand patterns such as the one used here, just about well enough. Everything I've learned has been through observing the traffic on this page. --Tagishsimon (talk) 20:34, 17 July 2018 (UTC)
#humain né-e ou mort-e dans la Canton de Vaud 
SELECT ?humain ?humainLabel ?lieuNaissanceLabel ?lieuMortLabel ?occ ?archive_at
WITH { SELECT ?humain (group_concat(distinct ?occupLabel;separator=", ") as ?occ) (group_concat(distinct ?archiveLabel;separator=", ") as ?archive_at) WHERE 
{
  ?humain wdt:P31 wd:Q5.
  OPTIONAL {?humain wdt:P106 ?occup.
            ?occup rdfs:label ?occupLabel. filter(lang(?occupLabel)="fr")}
  OPTIONAL {?humain wdt:P485 ?archive.
             ?archive rdfs:label ?archiveLabel. filter(lang(?archiveLabel)="fr")}
  {?humain wdt:P19 ?lieuNaissance.
  ?lieuNaissance wdt:P131 wd:Q12771.}
  UNION
  {?humain wdt:P20 ?lieuMort.
  ?lieuMort wdt:P131 wd:Q12771.}
  } group by ?humain  } as %i
WHERE {include %i
  OPTIONAL {?humain wdt:P19 ?lieuNaissance.}
  OPTIONAL {?humain wdt:P20 ?lieuMort.} 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr". }
} ORDER BY ?humainLabel
Try it! --Tagishsimon (talk) 20:34, 17 July 2018 (UTC)
Thanks a lot @Tagishsimon:! It's perfekt. I well done to ask for help. On your advice, I will follow this page to learn more aout SPARQL. --2le2im-bdc (talk) 05:05, 18 July 2018 (UTC)

Getting property label[edit]

I'm trying to figure out the current team ("member of sports team" without qualifier "end time") of the riders of this year's Tour de France:

SELECT ?person ?personLabel ?teamLabel (year(?st) as ?since)  WHERE {
  ?person wdt:P1344 wd:Q28859163.
  ?person p:P54 ?team.
  optional {?team pq:P580 ?st}
  optional {?team pq:P582 ?et}
  filter(bound(?st) && !bound(?et))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by desc(?since)

Try it!

However, since I've referenced the team with "p" instead of "wdt" I apparently cannot just add "Label" to get the proper name. What's the way to go here?

As a bonus question, there are 176 riders this year, all in Wikidata, but this query currently has 177 results. I guess there is a rider with multiple current teams (start but no end time). What is an elegant way to figure out that entity?

Thanks in advance.--95.222.168.248 20:47, 17 July 2018 (UTC)

SELECT ?person ?personLabel ?teamLabel (year(?st) as ?since)  WHERE {
  ?person wdt:P1344 wd:Q28859163.
  ?person p:P54 ?teamStatement .
  ?teamStatement ps:P54 ?team .
  optional {?teamStatement pq:P580 ?st}
  optional {?teamStatement pq:P582 ?et}
  filter(bound(?st) && !bound(?et))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by desc(?since)
Try it! Egan Bernal (Q21936667) has the wrong team item (Sky 2018 (Q39059584) instead of Sky (Q200009)), and Team Sunweb (Q247956) has nine cyclists of whom one probably is not a participant. —MisterSynergy (talk) 21:20, 17 July 2018 (UTC)
I've fixed Egan's team and found that Ramon Sinkeldam had two active team memberships. Now it all works out. Thanks!--95.222.168.248 15:51, 18 July 2018 (UTC)

Add a layer for missing images[edit]

In the query below, I'd like to display items with missing image in a different color. Could you help me please?

#defaultView:Map
SELECT DISTINCT ?item ?itemLabel ?coord ?image
WHERE {
  hint:Query hint:optimizer "None".
  ?comm wdt:P131 wd:Q3131.
  ?item wdt:P131 ?comm.
  ?item (wdt:P31/wdt:P279*) wd:Q16970.
  ?item wdt:P625 ?coord.
  OPTIONAL {?item wdt:P18 ?image}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

Thanks. — Ayack (talk) 15:02, 18 July 2018 (UTC)

@Ayack:
#defaultView:Map
SELECT DISTINCT ?item ?itemLabel ?coord ?image ?layer
WHERE {
  hint:Query hint:optimizer "None".
  ?comm wdt:P131 wd:Q3131.
  ?item wdt:P131 ?comm.
  ?item (wdt:P31/wdt:P279*) wd:Q16970.
  ?item wdt:P625 ?coord.
  OPTIONAL {?item wdt:P18 ?image}
  BIND(IF(BOUND(?image),'Image exists','No image exists') AS ?layer)
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Larske (talk) 16:07, 18 July 2018 (UTC)
Perfect! Thanks a lot Larske! — Ayack (talk) 16:16, 18 July 2018 (UTC)

GLAMs in Germany that are publicly funded[edit]

How can I find a list of all (rather: as many as possible) GLAM institutions in Germany that are publicly funded (no label (Q294095)).

My first attempt (see below) doesn't seem to be complete at all.

SELECT DISTINCT ?GLAM ?GLAMLabel ?Betreiber ?BetreiberLabel ?ist_ein_e_ ?ist_ein_e_Label WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?GLAM (wdt:P31/wdt:P279*) wd:Q1030034.
  ?GLAM wdt:P137 ?Betreiber.
  ?GLAM wdt:P17 wd:Q183. 
  MINUS { ?Betreiber wdt:P31 wd:Q4830453. }
  ?GLAM wdt:P31 ?ist_ein_e_. 
}

Try it!

More sophisticated queries yield timeouts.

Any help is much appreciated.

--MB-one (talk) 12:29, 19 July 2018 (UTC)