Wikidata:Request a query/Archive/2018/11

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

List of language labels

Hi, a friend of mine need for a work-related project a list of labels of items in the following languages es,pt,ro,cs,pl,ru,ar,fa,hi,zh,ja,ms of all items under the subclasses of scalar quantity (Q181175). I have no idea how to do this, but could be useful in the future in any case for me to learn it. Can you please show to me how to encode this request?--Alexmar983 (talk) 17:57, 2 November 2018 (UTC)

@Alexmar983: One approach - presuming you want a table with one column per language - is below. We find ?items in the Q181175 class tree with wdt:P31/wdt:P279* and then use a series of optional requirements that ask for the rdfs:labal each filtered to just a single language. Optional is used since some items may not have labels in a particular language and would not otherwise appear. We could take a different approach to produce a 2 or 3 column output of itemnumber, label, language ... but that's very slightly more complicated so I'll duck that for now. You can add Englist as ?en / "en" should you need it.

select ?item ?es ?pt ?ro ?cs ?pl ?ru ?ar ?fa ?hi ?zh ?ja ?ms where
{
  ?item wdt:P31/wdt:P279* wd:Q181175 . # The item is an instance or subclass of a scalar physical quantity 
  optional {?item rdfs:label ?es . filter(lang(?es)="es") } # get the label in whatever language - Spanish, in this case
  optional {?item rdfs:label ?pt . filter(lang(?pt)="pt") }
  optional {?item rdfs:label ?ro . filter(lang(?ro)="ro") }
  optional {?item rdfs:label ?cs . filter(lang(?cs)="cs") }
  optional {?item rdfs:label ?pl . filter(lang(?pl)="pl") }
  optional {?item rdfs:label ?ru . filter(lang(?ru)="ru") }
  optional {?item rdfs:label ?ar . filter(lang(?ar)="ar") }
  optional {?item rdfs:label ?fa . filter(lang(?fa)="fa") }
  optional {?item rdfs:label ?hi . filter(lang(?hi)="hi") }
  optional {?item rdfs:label ?zh . filter(lang(?zh)="zh") }
  optional {?item rdfs:label ?ja . filter(lang(?ja)="ja") }
  optional {?item rdfs:label ?ms . filter(lang(?ms)="ms") }              
}

Try it!

--Tagishsimon (talk) 18:32, 2 November 2018 (UTC)

Tagishsimon that's good, he has an account he seldomly uses, I have asked him to insert some of the missing ones when he find them.--Alexmar983 (talk) 19:05, 2 November 2018 (UTC)

Thanks Alexmar983. I suspect he'll find that difficult, unless you give him advice on the Babel Extension, which it looks like you have implemented on your account; without it, the user interface will not give him the facility to add in more than a few languages. --Tagishsimon (talk) 19:14, 2 November 2018 (UTC)

You are right Tagishsimon about the extension, I'll tell him. he wants to learn about wikidata in any case.--Alexmar983 (talk) 19:55, 2 November 2018 (UTC)

@Alexmar983: You can use the query select ?item where { ?item wdt:P31/wdt:P279* wd:Q181175 . } in the Tabernacle tool (with a 'Label' column with languages "es,pt,ro,cs,pl,ru,ar,fa,hi,zh,ja,ms") so that you can also edit the labels for those items in one place. Mahir256 (talk) 00:22, 3 November 2018 (UTC)

Disambiguation with multiple value of P31

It's possible to have a query for items with multiple values of P31 and with one value of P31 = Q4167410 ? --ValterVB (talk) 14:42, 3 November 2018 (UTC)

@ValterVB:

• Items used: Wikimedia disambiguation page (Q4167410)   

• Properties used: instance of (P31)   

SELECT ?item ?itemLabel ?notdab ?notdabLabel
WHERE
{
  ?item wdt:P31 wd:Q4167410 .
  ?item wdt:P31 ?notdab . FILTER( ?notdab != wd:Q4167410 )
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],it,en". }
}

Try it!

Like the above? --- Jura 15:12, 3 November 2018 (UTC)

@Jura1: Probably perfect, thanks a lot. --ValterVB (talk) 15:17, 3 November 2018 (UTC)

Replicate a list from Wikipedia

Dear all. In Wikipedia there are different list especially for economical data like inflation, gross domestic product etc. Can we for example replicate lists like this?

Maybe we could use the query here from @MisterSynergy: as a starting point.

• Can we generate an integer showing the rank using SPARQL in one separate column for each country (attention - the european union are not ranked)?
• Can we get (small) flaggs like in wikipedia?

Cheers! --Datawiki30 (talk) 18:50, 4 November 2018 (UTC)

@Datawiki30: We can do both those things, up to a point, and probably only in Listeria. I've constrained the report below to members of the UN security council; if we open the list to all UN members, we get a timeout. WDQS will not display flags, but were we to put this in a listeria query such that we generate a table on a wikidata / wikipedia page, we'd get the flags. So. Timeout is the main problem ... not sure what the cure there might be.

SELECT ?country ?countryLabel ?gdp ?currencyLabel ?flag (count(*) as ?rank) WHERE {
#  values ?country {wd:Q30 wd:Q184}
  ?country wdt:P463 wd:Q37470. 
  ?country2 wdt:P463 wd:Q37470.          
  ?country p:P2131 [ psv:P2131 [ wikibase:quantityAmount ?gdp; wikibase:quantityUnit ?currency ]; pq:P585 ?time; prov:wasDerivedFrom/pr:P248 wd:Q21540096 ] .
  FILTER(YEAR(?time) = 2017) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
  ?country2 p:P2131 [ psv:P2131 [ wikibase:quantityAmount ?gdp2; wikibase:quantityUnit ?currency ]; pq:P585 ?time; prov:wasDerivedFrom/pr:P248 wd:Q21540096 ] .
  filter (?gdp2 >= ?gdp)
  ?country wdt:P41 ?flag.
} group by ?country ?countryLabel ?gdp ?currencyLabel ?flag ORDER BY ?rank

Try it!

--Tagishsimon (talk) 19:56, 4 November 2018 (UTC)

@Tagishsimon: Tjank you! Can we add also entities like European Union (Q458) to the list but exclude them from ranking? Do you think that we can raise the performance and avoid the timeout if we use values ?country {wd:Q30 ... } for all the coutnries? --Datawiki30 (talk) 20:30, 4 November 2018 (UTC)

@Datawiki30: Actually, thinking about it, Listeria will provide a # column, so if we put it into listeria, we can dispense with the part of the approach we're currently taking which is causing the timeout, and give you exactly what you want. Using values will not help; the problem is we, before we group things, we get one row for the #1 country, 2 rows for the #2 country, and so on ... and indeed, for 189 countries, need to fetch and do filter comparisons for ~189^2 rows ... that's hideously inefficient, though it is a shame that we don't have enough time to complete the operation. I'll try to knock up a listeria version now & ping you when done. (But I thought it would be interesting to see ranking from SPARQL in action, since it's not a game I've played before.) Not sure how we deal with the EU requirement ... might come back to that.--Tagishsimon (talk) 20:53, 4 November 2018 (UTC)

@Datawiki30: Here's the work in progress at User:Tagishsimon/junk2. If we're going to do this properly, plenty still to do, not least in making it less ugly, such as controlling the table width; making flags smaller; checking that all the GDP values are in the same currency, &c &c &c. But as a demonstrator of the principle, we're pretty much there. Rows in the table are not constrained other than that they have a GDP figure which fits the select - so the EU pops up, for instance. We could constrain by requiring the ?items to be members of the UN to get just countries; and could add in the EU by, for instance, doing a UNION with a values statement containing its QId. From here it becomes a process of working through lots of tedious checks (e.g. on currency) & trials (e.g. ensmallening flags) ... you'll have to let me know whether there's any virtue in doing that. --Tagishsimon (talk) 21:15, 4 November 2018 (UTC)

@Tagishsimon: Thank you for the fast implementation! It looks very promising. I think this is an important task and it is worth working on that to improve the list, because:

• The data for these lists is well accessible and can stored in Wikidata
• After the discussion here I would like to make a request to import more values to already available economic properties with the WDBot
• This sort of lists can offer automatization for the wikipedia projects, so that editors can concentrate on other things

I know that it needs a hard work to convince the wikipedia communities. I would suggest that we make lists on some testpage in wikipedia for some properties beginning with nominal GDP (P2131). I would propose to make a test, to observe and to track the result for several months. This should help us to see if the data in Wikidata and thus the automatic gerated list are stable. I'm sure this could serve us to derive some advantages of the automatic generated lists but also to optimize the process of the list generation.

On the other side we need the actual values in the infobox of each country. For this we have (thanks to RexxS) the appropriate Lua functions - see again here.

Ready to go the next step? Cheers! --Datawiki30 (talk) 20:18, 5 November 2018 (UTC)

@Datawiki30: Maybe in 10 days or so ... feel free to ping me to remind. --Tagishsimon (talk) 00:54, 7 November 2018 (UTC)

Unexplained deprecation of ORCID iDs

Please could someone make a query for people with a ORCID iD (P496) that is deprecated, but with no reason for deprecated rank (P2241) qualifier? Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 15:15, 5 November 2018 (UTC)

@Pigsonthewing: Not a huge list:

SELECT ?item ?itemLabel where 
{
  ?item p:P496 ?statement.
  ?statement wikibase:rank wikibase:DeprecatedRank.
  filter not exists {?statement pq:P2241 [].}  
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}

Try it!

--Tagishsimon (talk) 00:52, 6 November 2018 (UTC)

@Tagishsimon: Glad to see it's not huge, but I'm aiming to clear it. Thank you. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 20:23, 6 November 2018 (UTC)

Good work. You must be exhausted. --Tagishsimon (talk) 21:32, 6 November 2018 (UTC)

I think that this discussion is resolved and can be archived. If you disagree, don't hesitate to replace this template with your comment. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 20:23, 6 November 2018 (UTC)

Overview of places with names ending with same letters

Hi, I would like to see (on a map) all places in Germany with names ending with the same group of letters like "...büttel". The result would display places like Tremsbüttel, Hoisbüttel, Poppenbüttel etc. The challenge is, that the places are qualified very differently (and partly inconsistently) as "Gemeinde (Q15284)", "Gemeinde in Deutschland (Q262166)", "Ortsteil (Q253019)", Stadtteil (Q2983893), "Stadtteil von Hamburg (Q15830667)", "Stadt (Q515)" (and maybe more). Regards --Wikipeter-HH (talk) 10:40, 7 November 2018 (UTC)

@Wikipeter-HH: this sort of approach, Wikipeter; trying to union in the query below times-out, but you can run the P131* route or the P17 route on their own and get results (or, sometimes, a timeout). Or, I guess, look at a union of a bunch of P31=Q15284, Q262166, things, per the second example.

SELECT ?item ?itemLabel where 
{
#  {?item wdt:P131* wd:Q183.}
#  union
  {?item wdt:P17 wd:Q183.}
  ?item rdfs:label ?itemLabel. filter(lang(?itemLabel)="de")
  filter(strends(?itemLabel,"büttel"))
}

Try it!

SELECT ?item ?itemLabel where 
{
  values ?stuff {wd:Q15284 wd:Q262166 wd:Q253019 wd:Q2983893 wd:Q15830667 wd:Q515}
  ?item wdt:P31 ?stuff.
  ?item rdfs:label ?itemLabel. filter(lang(?itemLabel)="de")
  filter(strends(?itemLabel,"büttel"))
}

Try it!

--Tagishsimon (talk) 13:40, 7 November 2018 (UTC)

Thanks for the quick reply --Wikipeter-HH (talk) 17:26, 7 November 2018 (UTC)

Challenge! An up to date map of Wikimedia movement affiliates

This map is out of date. Can we make a Wikidata-driven, up-to-date equivalent?

The static map of Wikimedia affiliate (Q5926733) on https://meta.wikimedia.org/wiki/Wikimedia_movement_affiliates is out of date. But we have data about all affiliates on Wikidata, don't we? Could anyone create a working query that produces a nice, up to date map? (And who knows, a display that can be shown on wiki with Kartographer?) Also, if anyone has any ideas to combine visualizations of geography-based and more 'thematic' user groups...? Spinster 💬 12:00, 7 November 2018 (UTC)

EHRI authority control (Q51278630)

I'm trying to find all the data (which should be WWII camps and ghettos) in the EHRI authority files. Which query should I use for this?

Thanks in advance!

@Jjorna: This:

SELECT ?item ?itemLabel 
WHERE 
{
  ?item p:P528 ?statement.
  ?statement pq:P972 wd:Q51278630.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 15:01, 7 November 2018 (UTC)

Thanks for the quick reply!

List of subsidiary

i need a query which will list all the subsidiary of a given company.I just need the name of the subsidiary company. — Preceding unsigned comment added by 42.109.151.32 (talk) 8 november 2018 kl. 04.26 (UTC)‎

The following query lists also has subsidiary (P355) in several steps to the company stated in the VALUES clause. Remove the '*' if you only want "direct" has subsidiary (P355).

SELECT ?item ?itemLabel WHERE {
  VALUES ?parent_company { wd:Q95 }    #example, Q95=Google
  ?parent_company wdt:P355* ?item .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY ?itemLabel

Try it!

--Larske (talk) 06:57, 8 November 2018 (UTC)

Sparql query per estrazione dataset per lavorare sulla mia tesi - Extract dataset regarding an argument given by input

Hi everybody. I need a sparql query that extracts all wikidata items that are correlated to an specific argument given by input. In the end, I have to save the result in a local dump. For example, the argument could be "cardiology" or "computer security". Thank you all for the attention.

Salve. Avrei bisogno di una query che fosse in grado di estrarre tutti gli oggetti di wikidata di un determinato argomento dato come input e salvarli su un dump locale. Per esempio vorrei estrarre tutti gli oggetti che intercorrono nell'argomento "sicurezza informatica" o "cardiologia". Mi rendo conto che la computazione può essere onerosa ma verrà eseguita su macchine dedicate. Questo mi serve per poter applicare determinati algoritmi su tali dati e poterne analizzare manualmente il risultato: questo è un compito che si può pensare di eseguire solo su dataset ristretti rispetto che sull'intera wikidata. Se queste query sono già disponibili o non è la sezione adatta, per eventuali errori grammaticali, chiedo scusa. Buona giornata a tutti.

@Isabeauoliveri: Here is a starter query ... not sure how much it will help, but it probably covers the most obvious correlations of search term to wikidata item.

SELECT DISTINCT ?item ?itemLabel WHERE {
  values ?term {wd:Q10379}         #this is the input term
  {?item wdt:P31/wdt:P279* ?term .} #this gets instances of the term, or subclasses of the term
  UNION
  {?item wdt:P921 ?term .}           #this gets items which have the term as their main subject
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en". }
}

Try it!

--Tagishsimon (talk) 16:58, 8 November 2018 (UTC)

All female vs male writters born in a territorial entity including all the sub-territorial entities

I would like to compare by sex (P21) the number of female vs male writters born (P19) in a Spanish Autonomous communitie (Castilla y León). My main problem is P19 usually refers to a village, that belongs to a province, that belongs to an Autonomous communitie. And I would like to know the whole Castilla y León.

Thanks for considering my request

--Pablohn6 (talk) 13:51, 8 November 2018 (UTC)

@Pablohn6: Like this. wdt:P19/wdt:P131* wd:Q5739 says "born in, or born in a place that is in, Castilla y León".

SELECT DISTINCT ?genderLabel (count(?gender) as ?count) WHERE {

  ?item wdt:P106 wd:Q36180.
  ?item wdt:P19/wdt:P131* wd:Q5739.
  ?item wdt:P21 ?gender.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} group by ?genderLabel

Try it!

--Tagishsimon (talk) 14:46, 8 November 2018 (UTC)

Items starting or ending with a specific word

Greetings, I want a query for the items starting with a specfic words "James" which gives the result "James Bond, James Cameron" etc. --Titodutta (talk) 15:32, 8 November 2018 (UTC)

@Titodutta: You will not get that sort of a search across all items - far too many given that filtering for "James" is very inefficient. Best you can do is to cut down the search space by constraining the items you're going to test for Jamesness ... here, for instance, as a consolation, are ten spies called James.

SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P106 wd:Q9352089.
  ?item rdfs:label ?itemLabel. filter(lang(?itemLabel)="en")
  filter(strstarts(?itemLabel, "James"))
}

Try it!

--Tagishsimon (talk) 16:25, 8 November 2018 (UTC)

A second approach is to look for items that have a given name of James, where the series ordinal property qualifier is 1, or where there is only one given name which is James. THat technique works, of course, only with values in wikidata that are codified ... does not work so well when looking for substrings of labels.

SELECT ?item ?itemLabel 
WHERE 
{
  {?item p:P735 ?statement.
  ?statement pq:P1545 "1" .
  ?statement ps:P735 wd:Q677191.}
  union
  {?item wdt:P735 wd:Q677191.
   minus {?item wdt:P735 ?thing. filter(?thing!=wd:Q677191)} 
  } 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }  
}

Try it!

--Tagishsimon (talk) 16:36, 8 November 2018 (UTC)

• Thank you for your kind support. --Titodutta (talk) 18:53, 9 November 2018 (UTC)

Wikipedia's by country

I'd like to know the "home" country of each Wikipedia. So, where would the different language Wikipedias (292) appear on a map as an indigenous or native language? I've tried to figure out the SPARQL query, but I'm very new to Wikidata.

It's an interesting question. You'd think a simple bit of SPARQL like the first query would answer it, but instead it times out.

SELECT ?item ?itemLabel ?lang ?langLabel ?country ?countryLabel
where 
{ ?item wdt:P31 wd:Q10876391. #find language wikipedias
  ?item wdt:P407 ?lang.       #get the wiki's 'language of work'
  ?country wdt:P463 wd:Q1065. #id countries by their membership of the UN
  ?country wdt:P37 ?lang.     #get the official languages of the country
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }  
}

Try it!

So instead we need to reach for a couple of named subqueries, one of which finds language wikipedias and their language, the other of which finds countries and their language ... these combine to give us a lookup from wikipedia to country via language.

SELECT ?item ?itemLabel ?lang ?langLabel ?country ?countryLabel
with 
{ select ?item ?lang where
{ ?item wdt:P31 wd:Q10876391. #find language wikipedias
  ?item wdt:P407 ?lang.       #get the wiki's 'language of work'
} } as %w
with
{ select ?country ?lang where
{ ?country wdt:P463 wd:Q1065. #id countries by their membership of the UN
  ?country wdt:P37 ?lang.     #get the official languages of the country
} } as %c
where
{ include %w
  include %c
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }  
}

Try it!

Note I have used the official language (P37) where you asked for "indigenous or native language" ... I'm not sure we have a better choice than P37, but if you find one, you can slot it in. I've also used membership of the United Nations as a proxy for defining an item as a country. The results suggest that not all language wikipedias have countries. As normal (for me) this is a rough & ready answer; doubtless you could spend some time refining and polishing it. --Tagishsimon (talk) 23:26, 8 November 2018 (UTC)

@Tagishsimon: Any insight on why the first query timeouts ? It seems not like it’ that a complex query … a join over a language, with both wikipedias having mostly one language and the same for languages … is it that the query engine does not really realize that and that you have to make him realize by precomputing the (almost) functional dependancies wp -> lang and coutry -> lang beforehand so that he can take advantage of this ? author  TomT0m / talk page 12:46, 9 November 2018 (UTC)

@TomT0m: No, I'm puzzled by it all. Fetching items on their own takes <3 seconds to return 314 rows; fetching countries on their own takes < 3 seconds to return 339 rows. Query 2 does the whole job in <3 seconds. Blazegraph documentation on Join Order Optimization suggests the use of hint:Query hint:optimizer "None" . or, later the use of a runtime query optimizer - hint:Query hint:optimizer "Runtime".. Adding either of these incantations makes query 1 work:

SELECT ?item ?itemLabel ?lang ?langLabel ?country ?countryLabel
where 
{ 
  ?item wdt:P31 wd:Q10876391. #find language wikipedias
  ?item wdt:P407 ?lang.       #get the wiki's 'language of work'
  ?country wdt:P463 wd:Q1065. #id countries by their membership of the UN
  ?country wdt:P37 ?lang.     #get the official languages of the country
 # hint:Query hint:optimizer "None" .
  hint:Group hint:optimizer "Runtime".
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }  
}

Try it!

but I don't understand how the query optimization could have gone so wrong in the first place. BG offers an explain service, but I've yet to get that to work through our interface. Right now, I'll take a wild guess that BG is trying to evaluate the ?item wdt:P407 ?lang. and ?country wdt:P37 ?lang. join before looking at ?item wdt:P31 wd:Q10876391. and ?country wdt:P463 wd:Q1065.. --Tagishsimon (talk) 14:00, 9 November 2018 (UTC)

Query showing the sum of statements per used property on selected items

Does anyone has an idea how to query the sum of the statements of each property of one item? The result should look something like this (for example for Q30):

property	propertyLabel	sum_statements
P31	instance of	5
P2131	nominal gdp	58
...	...	...

--Datawiki30 (talk) 21:45, 9 November 2018 (UTC)

@Datawiki30: Like this:

SELECT ?property ?propertyLabel (count(?property) as ?count)
where 
{ 
  values ?item {wd:Q12823}
  ?item ?property [].
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }  
} group by ?property ?propertyLabel

Try it!

--Tagishsimon (talk) 21:50, 9 November 2018 (UTC)

If one only looks for Wikidata properties (no labels, sitelinks, meta stuff and so on), this one is showing real property labels:

SELECT ?property ?propertyLabel (COUNT(?property) AS ?count) WHERE { 
  wd:Q12823 ?p [] .
  ?property wikibase:claim ?p .
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }  
} GROUP BY ?property ?propertyLabel

Try it!

—MisterSynergy (talk) 22:15, 9 November 2018 (UTC)

Thank you both! Datawiki30 (talk) 13:27, 10 November 2018 (UTC)

List of (international) airports having only one runway

using the quantity (P1114) of runway (Q184590) for every international airport (Q644371) ? or via a count of runway (P529) ?Bouzinac (talk) 19:24, 11 November 2018 (UTC)

@Bouzinac: The second of those; I've provided P1114 values in the query below, but they're all null.

SELECT ?item  ?itemLabel (count(?runway) as ?runways) (sample(?quantity) as ?quantity) WHERE { 
  ?item wdt:P31 wd:Q644371.
  optional {?item wdt:P529 ?runway.}
  optional {?item wdt:P1114 ?quantity.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }  
} group by ?item ?itemLabel having (?runways=1) order by ?itemLabel

Try it!

--Tagishsimon (talk) 20:05, 11 November 2018 (UTC)

Thanks!

All items with an identifier assigned in a given administrative territorial entity including all the sub-territorial entities

In this case:

• to get all items with an asigned identifier P3318 (an ID in a public monuments catalog)
• inside a given P131 (in this case Q81802, Province of Almería), including all P131 members of Q81802.

Thanks in advance.

--Olea (talk) 12:38, 7 November 2018 (UTC)

@Olea: This

SELECT ?item ?itemLabel ?P3318 where 
{
  ?item wdt:P3318 ?P3318.
  ?item wdt:P131* wd:Q81802.
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}

Try it!

--Tagishsimon (talk) 13:20, 7 November 2018 (UTC)

♥♥♥

--Olea (talk) 12:01, 12 November 2018 (UTC)

Female librarians with no en.wp article

This is a simple enough query:

SELECT ?item WHERE { ?item wdt:P31 wd:Q5 . ?item wdt:P21 wd:Q6581072 . ?item wdt:P106 wd:Q182436 .

FILTER NOT EXISTS { ?wen schema:about ?item; schema:isPartOf <https://en.wikipedia.org/> . } }

However, when I add wdt:P279* for the subclasses of the occupation, the query crashes every time. Is there something that can be done to improve this query so it will not crash and include subclasses? Thank you. Gamaliel (talk) 17:50, 12 November 2018 (UTC)

@Gamaliel: It's always odd when WDQS fails on simple queries like this. I tried hint:Query hint:optimizer "None" . but that had no effect ... my supposition was that the query optimizer was erring. So that leaves the get-out-of-jail-free named subquery as the probable solution:

SELECT ?item with
{ select ?item where 
  {
  ?item wdt:P106/wdt:P279* wd:Q182436 .
  }
} as %i
where
{
  include %i
  ?item wdt:P31 wd:Q5 . 
  ?item wdt:P21 wd:Q6581072 . 
  FILTER NOT EXISTS { ?wen schema:about ?item; schema:isPartOf <https://en.wikipedia.org/> . } 
}

Try it!

--Tagishsimon (talk) 17:57, 12 November 2018 (UTC)

Tagishsimon It worked! Thank you! Gamaliel (talk) 18:14, 12 November 2018 (UTC)

Merged people items with a Flanders Arts Institute identifier

27736 people on Wikidata have a Flanders Arts Institue person ID. Some of these items have since been merged with previously existing items on Wikidata. I am looking for a query that shows not only the current Q-number, but (if applicable) also the Q-number that has been merged with it. e.g.: Wim Vandekeybus Wim Vandekeybus (Q52189692) is merged with (and thus redirects to) (Wim Vandekeybus (Q3569237). Start of the query:

SELECT ?item ?itemLabel ?Flanders_Arts_Institute_person_ID 
WHERE { ?item wdt:P5068 ?Flanders_Arts_Institute_person_ID. 
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],nl". }
      }

Try it!

Beireke1 (talk) 09:30, 14 November 2018 (UTC)

@Beireke1: AFAIK, not happening. WDQS has no access to info on the fact that an item is a redirect. Quarry might be able to provide what you want (painfully, requiring crosstabulation between the WDQS result set of your query and the Quarry query) but that's still above my pay-grade. Any items returned by your query are substantive & not redirects, since by their nature a redirect has no statements & hence is not returned by the query. --Tagishsimon (talk) 09:43, 14 November 2018 (UTC)

Proof of that final assertion:

SELECT ?item ?itemLabel ?Flanders_Arts_Institute_person_ID WHERE 
{ 
  values ?item {wd:Q52189692}
  ?item wdt:P5068 ?Flanders_Arts_Institute_person_ID. 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],nl". }
}

Try it!

--Tagishsimon (talk) 09:47, 14 November 2018 (UTC)

WDQS servers not all up to date?

Hi all, this query does not seem to return all items:

SELECT ?item ?catcode WHERE { 
  ?item p:P528 [ pq:P972 wd:Q53207781 ; ps:P528 ?catcode]. 
}

Try it!

You can see what items should be in there by clicking "what links here" on Adam Pynacker catalog raisonné, 1926 (Q53207781). So e.g. this item is missing: Landscape with Hunters (Q31158443) is missing. Any idea why? Magnus suggested opening a phab task but I wanted to check in here first. This affects a lot of listeria lists that I manage. Thx in advance, Jane023 (talk) 12:07, 14 November 2018 (UTC)

@Jane023: Maybe check out https://phabricator.wikimedia.org/T208928 on your route to https://phabricator.wikimedia.org/T203646 ... this seems to be where the action on this point probably is - the various WDQS replicas are out of synch, to the tune of 40 million triples - see triples graph on https://grafana.wikimedia.org/dashboard/db/wikidata-query-service?orgId=1

Your issue may be different, of course ... so like this - https://phabricator.wikimedia.org/T208925 - where WDQS seemed oblivious of an update done in July 2018.

I guess another data point will not hurt; so don't hesitate to phab the problem. Your ticket can always be closed, as T208925 has been. --Tagishsimon (talk) 13:17, 14 November 2018 (UTC)

Thanks! This looks to be T207675

Tracked in Phabricator
Task T207675

(I only read about 4 or 5 of that thread and this one explicitly mentions the same problem with a qualifier. I will follow that one and add a link back here. Jane023 (talk) 14:09, 14 November 2018 (UTC)

Merge multiple columns into one

Hello, what's wrong with this query ? I wished to concatenate ?FAA_USA ?Code_Transport_Canada ?DGAC ?russ ?FAArg into one column ?Local_Code.

SELECT DISTINCT ?airport ?airportLabel (SAMPLE(?coord) AS ?coord) ?ICAO ?IATA 
(sample(?FAA_USA ?Code_Transport_Canada ?DGAC ?russ ?FAArg) AS ?Local_Code) WHERE {
?airport (wdt:P31/wdt:P279*) wd:Q62447.
?airport wdt:P17 wd:Q236.
MINUS { ?airport wdt:P576 _:b2. }                                # remove items with P576 (dissolved, abolished or demolished) as a main property
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q695850. }               # exclude military airports
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q1311670. }              # exclude railways stations
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q7373622. }              # exclude Royal Air Force
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q502074. }               # exclude héliports
MINUS { ?airport wdt:P31/wdt:P279* wd:Q2265915.}                 # exclude vol à voile/gliders
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q44665966. }             # exclude  airports being build
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q782667. }               # exclude motorways where plane can land
MINUS { ?airport wdt:P582 _:b3. }                                # remove items with P582 (end date) as a main property
MINUS { ?airport a wdno:P17.}                                    # exclude airports that aren't physically based in a country
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
OPTIONAL { ?airport wdt:P625 ?coord. }
OPTIONAL { ?airport wdt:P239 ?ICAO. }
OPTIONAL { ?airport wdt:P238 ?IATA. }
OPTIONAL { ?airport wdt:P240 ?FAA_USA. }
OPTIONAL { ?airport wdt:P5699 ?Code_Transport_Canada. }
OPTIONAL { ?airport wdt:P5746 ?DGAC. }
OPTIONAL { ?airport wdt:P5851 ?russ. }
OPTIONAL { ?airport wdt:P6120 ?FAArg. }
}
GROUP BY ?airport ?airportLabel ?ICAO ?IATA ?Local_Code
ORDER BY ?coord

Try it!

Bouzinac (talk) 10:25, 14 November 2018 (UTC)

Several things. You'd need to use GROUP_CONCAT as an aggregate function if you wanted to aggregate the values ... but you have ?Local_Code as a GROUP, so maybe you;d be better using BIND(concat(?FAA_USA, ?Code_Transport_Canada, ?DGAC, ?russ, ?FAArg) AS ?Local_Code) in the body of the query, and a simple Local_code with no aggregation (i.e. no SAMPLE) in the select. That aside, there is no data in any of ?FAA_USA, ?Code_Transport_Canada, ?DGAC, ?russ, ?FAArg for the data returned by your query, so far as my experiments have shown. --Tagishsimon (talk) 10:51, 14 November 2018 (UTC)

Does this looks like what you are looking for ?

SELECT DISTINCT ?airport ?airportLabel (SAMPLE(?coord) AS ?coord) ?ICAO ?IATA (sample(?Local_Code) as ?lcode) {
  ?airport (wdt:P31/wdt:P279*) wd:Q62447;
           wdt:P17 wd:Q236.
  MINUS { ?airport wdt:P576 _:b2. }                                # remove items with P576 (dissolved, abolished or demolished) as a main property
  MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q695850. }               # exclude military airports
  MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q1311670. }              # exclude railways stations
  MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q7373622. }              # exclude Royal Air Force
  MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q502074. }               # exclude héliports
  MINUS { ?airport wdt:P31/wdt:P279* wd:Q2265915.}                 # exclude vol à voile/gliders
  MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q44665966. }             # exclude  airports being build
  MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q782667. }               # exclude motorways where plane can land
  MINUS { ?airport wdt:P582 _:b3. }                                # remove items with P582 (end date) as a main property
  MINUS { ?airport a wdno:P17.}                                    # exclude airports that aren't physically based in a country
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
   
  OPTIONAL { ?airport wdt:P625 ?coord. }
  OPTIONAL { ?airport wdt:P239 ?ICAO. }
  OPTIONAL { ?airport wdt:P238 ?IATA. }
   
  OPTIONAL { ?airport wdt:P240 ?Local_Code. }
  OPTIONAL { ?airport wdt:P5699 ?Local_Code. }
  OPTIONAL { ?airport wdt:P5746 ?Local_Code. }
  OPTIONAL { ?airport wdt:P5851 ?Local_Code. }
  OPTIONAL { ?airport wdt:P6120 ?Local_Code. }
}
GROUP BY ?airport ?airportLabel ?ICAO ?IATA
ORDER BY ?coord

Try it!

Colour me unconvinced, T0mt0m. Surely that just overwrites values into ?Local_Code meaning you get whatever the last value is. Equally, what do I know about SPARQL - my concat suggestion seems on testing to be too simple ... I find if there's no value for one of the variables, the concat fails. So right now I'm advocating the following rather ponderous route, where you test each variable before finally concatenating them all. By now, I've somewhat bowdelerised the query, since as I said, there's no data to be concatenated for the original query:

SELECT DISTINCT ?airport ?airportLabel ?ICAO ?IATA ?FAA_USA ?6 {
  ?airport (wdt:P31/wdt:P279*) wd:Q62447.
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }   
  OPTIONAL { ?airport wdt:P239 ?ICAO. }
  OPTIONAL { ?airport wdt:P238 ?IATA. }
  ?airport wdt:P240 ?FAA_USA. 
  OPTIONAL { ?airport wdt:P5699 ?Code_Transport_Canada. }
  ?airport wdt:P5746 ?DGAC. 
  OPTIONAL { ?airport wdt:P5851 ?russ. }

  bind(if(bound(?FAA_USA),?FAA_USA,"") as ?1)
  bind(if(bound(?Code_Transport_Canada),?Code_Transport_Canada,"") as ?2) 
  bind(if(bound(?DGAC),?DGAC,"") as ?3) 
  bind(if(bound(?russ),?russ,"") as ?4)
  bind(if(bound(?FAArg),?FAArg,"") as ?5)
  bind(concat(?1,"; ",?2,"; ",?3,"; ",?4,"; ",?5) as ?6)
}
limit 10

Try it!

and here it is for the original query...

SELECT DISTINCT ?airport ?airportLabel (SAMPLE(?coord) AS ?coord) ?ICAO ?IATA ?Local_Code WHERE {
?airport (wdt:P31/wdt:P279*) wd:Q62447.
?airport wdt:P17 wd:Q236.
MINUS { ?airport wdt:P576 _:b2. }                                # remove items with P576 (dissolved, abolished or demolished) as a main property
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q695850. }               # exclude military airports
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q1311670. }              # exclude railways stations
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q7373622. }              # exclude Royal Air Force
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q502074. }               # exclude héliports
MINUS { ?airport wdt:P31/wdt:P279* wd:Q2265915.}                 # exclude vol à voile/gliders
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q44665966. }             # exclude  airports being build
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q782667. }               # exclude motorways where plane can land
MINUS { ?airport wdt:P582 _:b3. }                                # remove items with P582 (end date) as a main property
MINUS { ?airport a wdno:P17.}                                    # exclude airports that aren't physically based in a country
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
OPTIONAL { ?airport wdt:P625 ?coord. }
OPTIONAL { ?airport wdt:P239 ?ICAO. }
OPTIONAL { ?airport wdt:P238 ?IATA. }
OPTIONAL { ?airport wdt:P240 ?FAA_USA. }
OPTIONAL { ?airport wdt:P5699 ?Code_Transport_Canada. }
OPTIONAL { ?airport wdt:P5746 ?DGAC. }
OPTIONAL { ?airport wdt:P5851 ?russ. }
OPTIONAL { ?airport wdt:P6120 ?FAArg. }
  bind(if(bound(?FAA_USA),?FAA_USA,"") as ?1)
  bind(if(bound(?Code_Transport_Canada),?Code_Transport_Canada,"") as ?2) 
  bind(if(bound(?DGAC),?DGAC,"") as ?3) 
  bind(if(bound(?russ),?russ,"") as ?4)
  bind(if(bound(?FAArg),?FAArg,"") as ?5)
  bind(concat(?1,"; ",?2,"; ",?3,"; ",?4,"; ",?5) as ?Local_Code)  
}
GROUP BY ?airport ?airportLabel ?ICAO ?IATA ?Local_Code
ORDER BY ?coord

Try it!

--Tagishsimon (talk) 11:25, 14 November 2018 (UTC)

@Tagishsimon: The original author used « sample » which in my opinion implies that he wants any of the value. It’s perfectly possible considering the usecase that there is only one of the code possible local code for any of the airport which would mean that sample will always choose the one possible value. But there is probably a way to write the query in a totally different spirit to avoid this, for example « optional {?airport ?code_prop ?code_val values ?code_prop { wdt:P240 wdt:P5699 wdt:P5746 wdt:P5851 dt:P6120 } would probably work, and by not selecting all the airports and excluding some subkinds but selecting the right kinds in the first place. This would mean a « explain to me what you want, I’ll tell you how to avoid this » that is unfortunately usually not always welcome by the teller :) author  TomT0m / talk page 11:34, 14 November 2018 (UTC)

I see where you're coming from :). --Tagishsimon (talk) 11:43, 14 November 2018 (UTC)

Hello, well, you pointed out the fact an airport can happen to have only one local code. (albeit the fact there two or three canadiano-USA airports that have both Canada and USA code). So I thought the sample function would pick the first non-NULL code. I'll test out your results by filtering some countries. And return to you :) Bouzinac (talk) 15:24, 14 November 2018 (UTC)

Taking the "  OPTIONAL { ?airport wdt:P240 ?Local_Code. }

  OPTIONAL { ?airport wdt:P5699 ?Local_Code. }

  OPTIONAL { ?airport wdt:P5746 ?Local_Code. }

  OPTIONAL { ?airport wdt:P5851 ?Local_Code. }

  OPTIONAL { ?airport wdt:P6120 ?Local_Code. }" solution, works really well and looks to be slightly quicker. Thanks all! Bouzinac (talk) 20:54, 14 November 2018 (UTC)

Uploads with timestamps

With this API call I get the recent uploads of my bot. So I would like this in SPARQL to make an overview of the number of uploads per day for the last couple of days.

SELECT * {
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
    bd:serviceParam wikibase:api "Generator" .
    bd:serviceParam mwapi:generator "allimages" .
    bd:serviceParam mwapi:gaiuser "BotMultichillT" .
    bd:serviceParam mwapi:gaisort "timestamp" .
    bd:serviceParam mwapi:gaidir "older" .
    ?title wikibase:apiOutput mwapi:title .
    ?timestamp wikibase:apiOutput mwapi:timestamp .
  }

} LIMIT 100

Try it!

But this query just gives the same timestamp (now) for all files. Any idea how to get the actual upload time? Than I can extract the year/month/day, group by it to get a count and maybe even generate a nice graph. Multichill (talk) 11:10, 15 November 2018 (UTC)

Fuse

Cañón El Dorado (Q5351174) y Cañón El Dorado (Q49800146). Gracias --Ortisa (talk) 17:53, 15 November 2018 (UTC)

done --Tagishsimon (talk) 18:02, 15 November 2018 (UTC)

All newspapers and magazines in Basque language

Hello! I'm trying to make something rather simple, but I get a timeout message every time I try. There must be an error somehwere...

SELECT ?aldizkako_argitalpen ?aldizkako_argitalpenLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?aldizkako_argitalpen wdt:P31* wd:Q1002697.
  ?aldizkako_argitalpen wdt:P407 wd:Q143.
}

Try it!

Thanks! -Theklan (talk) 15:33, 15 November 2018 (UTC)

here ya go! CalvinBall (talk) 15:49, 15 November 2018 (UTC)

SELECT ?a ?aLabel WHERE {
  ?a wdt:P31/wdt:P279* wd:Q1002697.
  ?a wdt:P407 wd:Q143.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

@CalvinBall: Thanks, but this query doesn't cover newspapers, that also a kind of periodic publication. -Theklan (talk) 20:44, 15 November 2018 (UTC)

@Theklan: There was a typo in Calvin's query, now corrected. Here, FYI, is a count of the P31 types of items that have a language of work or name (P407) of Esperanto (Q143), in case there are other things that might be of interest to you.

SELECT ?P31 ?P31Label (count(?P31) as ?count)  WHERE {
  ?a wdt:P407 wd:Q143 .
  ?a wdt:P31 ?P31.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?P31 ?P31Label order by desc(?count)

Try it!

--Tagishsimon (talk) 21:02, 15 November 2018 (UTC)

@Tagishsimon: eeerrr... no... this counts all the kind of things in a given language, but doesn't show which are the publications in a given language, that was the query giving timeout. -Theklan (talk) 21:11, 15 November 2018 (UTC)

@Theklan: Indeed. I have corrected Calvin's query (i.e. edited the query that he wrote), and provided a different query which counts things. Run Calvin's query again. All will be good. If in doubt, look at the diff for my first edit and you'll see I changed a wd: to a wdt: - --Tagishsimon (talk) 21:19, 15 November 2018 (UTC)

@Tagishsimon: Right! Thanks, just misunderstood your message. This works! -Theklan (talk) 21:48, 15 November 2018 (UTC)

norwegian citizens

Is it possibele to have a list of Norwegian Peoples born between 1840 to 1945. But not listening person who died before 1939. Breg Pmt (talk) 02:41, 16 November 2018 (UTC)

@Pmt: Here's a quick & dirty version ... you have some very old people in Norway. Main points:

• It ignores date precision - watch out for people born on the turn of the century
• It is based on country of citizenship, not, for instance place of birth
• Adjust the dates if you do not want 1840-1945 inclusive, etc
• date of death is optional - if null, and the dob condition is met, then the item is selected.
• you'll get multiple rows for people with multiple dobs or dods
• Let us know if you want any of the above addressing, or any other tweaks
• Oh - I've given you a calculated age, which might come in handy. Obviously your 174-year olds are problematic :)

SELECT ?item ?itemLabel ?dob ?dod ?age WHERE 
{ 
  ?item wdt:P27 wd:Q20 .
  ?item wdt:P569 ?dob .
  optional {?item wdt:P570 ?dod .}
  filter(year(?dod) > 1938|| !bound(?dod))
  filter(year(?dob) > 1839 )
  filter(year(?dob) < 1946 )
  bind(coalesce(year(?dod),2018) as ?dod2)      
  bind(?dod2-year(?dob) as ?age)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],no". }
}

Try it!

--Tagishsimon (talk) 02:56, 16 November 2018 (UTC)

@Tagishsimon: Exactly what I needed. All this people lived during WW2 in Norway, and should be included among the 50000 who fled to Sweden or among 15000 people served in the Norwegian mercant fleet or navy or the 10000 ki lled in Norway? The identificators and sources are available on paper.

Old Norwegians? they are dipped in Norwegian North sea oil, and then just slip away. Breg Pmt (talk) 03:55, 16 November 2018 (UTC)

I suspect the Rakfisk --Tagishsimon (talk) 10:53, 16 November 2018 (UTC)

@Pmt: More seriously, I've amended the query - it was finding people with dods outside the range you were interested in. Now fixed. --Tagishsimon (talk) 11:17, 16 November 2018 (UTC)

@Tagishsimon: Nice, I now have 15600 instead of 18500. Breg Pmt (talk) 11:27, 16 November 2018 (UTC)

There's no rush, Pmt. Based on your countrymen & women, you'll be living to about age 150. Plenty of time. --Tagishsimon (talk) 11:28, 16 November 2018 (UTC)

Leaning on this Government Pension Fund Global (Q1196231) it should not be a problem Pmt (talk) 11:59, 16 November 2018 (UTC)

Show media possible reach

Hello! I have built a query with items operating area, and want to show it in a bubble chart... but I want to add the results of different operating areas in the same item. For example, if somethingis operationg in Germany and Sweden, I don't want to show it twice, but with both populations aggregated. I'm doing something wrong:

SELECT ?KomLabel ?q WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?Kom wdt:P31 wd:Q340169.
  ?Kom wdt:P407 wd:Q8752.
  ?Kom wdt:P2541 ?op.
  ?op wdt:P1082 ?q.
}
GROUP by ?KomLabel

Try it!

-Theklan (talk) 12:16, 16 November 2018 (UTC)

@Theklan: Probably just:

SELECT ?KomLabel (sum(?q) as ?q) WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?Kom wdt:P31 wd:Q340169.
  ?Kom wdt:P407 wd:Q8752.
  ?Kom wdt:P2541 ?op.
  ?op wdt:P1082 ?q.
}
GROUP by ?KomLabel

Try it!

--Tagishsimon (talk) 12:21, 16 November 2018 (UTC)

Query times out. Why?

Hello! I have been trying to get the most frequent occupations for females on the Arabic Wikipedia. I found an example query and tried to modify it but the modified version always gets timed out upon running. Here is the example query:

#Most frequent occupations without a label in a given language
#added before 2016-10
#Most frequent occupations without a label in Latvian (lv)
#by Edgars2007/Jura1, 2016-05-09
SELECT ?occup ?occupLabel ?count
WHERE
{
	{
		SELECT ?occup (COUNT(?person) as ?count)
		WHERE
		{
			?person wdt:P106 ?occup
		}
		GROUP BY ?occup
		ORDER BY DESC(?count)
		LIMIT 1000
	}
  	OPTIONAL {?occup rdfs:label ?label1 	filter(lang(?label1) = "lv")}
	FILTER(!BOUND(?label1))
 
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en,es,pl,ja,zh,ru" }
}
ORDER BY DESC(?count)
LIMIT 50

And here is the one I tried to modify:

#Most frequent occupations without a label in a given language
#added before 2016-10
#Most frequent occupations without a label in Latvian (lv)
#by Edgars2007/Jura1, 2016-05-09
SELECT ?occup ?occupLabel ?count ?female ?femaleLabel WHERE {
  {
    SELECT ?occup (COUNT(?person) AS ?count) WHERE { ?person wdt:P106 ?occup. }
    GROUP BY ?occup
    ORDER BY DESC(?count)
    LIMIT 1000
  }
  OPTIONAL {
    ?occup rdfs:label ?label1.
    FILTER((LANG(?label1)) = "ar")
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,es,pl,ja,zh,ru". }
  FILTER(!BOUND(?label1))
  ?female wdt:P21 wd:Q6581072.
}
ORDER BY DESC(?count)
LIMIT 50

Did I do something wrong?--Reem Al-Kashif (talk) 14:42, 7 October 2018 (UTC)

SELECT ?occup ?occupLabel ?count ?female ?femaleLabel WHERE {
  {
    SELECT ?occup (COUNT(?person) AS ?count) WHERE { ?person wdt:P106 ?occup. }
    GROUP BY ?occup
    ORDER BY DESC(?count)
    LIMIT 1000
  }
  OPTIONAL {    ?occup rdfs:label ?label1.    FILTER((LANG(?label1)) = "ar")
  }
  FILTER(!BOUND(?label1))
  ?female wdt:P106 ?occup .
  ?female wdt:P21 wd:Q6581072.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,es,pl,ja,zh,ru". }
}
LIMIT 50

Try it!

Above a slightly different query. There might have been too many results to sort and there was no link between ?occup and ?female. You could also limit ?person directly to females. --- Jura 14:53, 7 October 2018 (UTC)

If you only want people who have an Arabic Wikipedia page, then maybe this does what you want:

select ?occup ?occuplabel ?total
with {
  select ?occup (count(*) as ?total) {
    ?sitelink schema:isPartOf <https://ar.wikipedia.org/> ; schema:about ?item .
    hint:Query hint:optimizer "None" .
    ?item wdt:P31 wd:Q5 ;
          wdt:P21 wd:Q6581072 ;
          wdt:P106 ?occup .
  } group by ?occup
} as %occupations
where {
  include %occupations .
  optional { ?occup rdfs:label ?occuplabel filter (lang(?occuplabel) = "en") }
} order by desc(?total) ?occuplabel

Try it!

- Nikki (talk) 15:13, 7 October 2018 (UTC)

Thank you, Nikki! I really appreciate it :)--Reem Al-Kashif (talk) 15:49, 7 October 2018 (UTC)

Hi, @Nikki:! I have been trying the last query you suggested. It worked great with many languages (like Arabic, Spanish, German) except for English. When I change the language code in the sitelink, the query times out. Do you have an idea why does this happens?--Reem Al-Kashif (talk) 13:03, 19 November 2018 (UTC)

@Reem Al-Kashif: The English Wikipedia has a lot more sitelinks than any other project. I don't think there's a way to make the query fast enough for that. :( - Nikki (talk) 13:58, 19 November 2018 (UTC)

@Nikki: Oh I see :( Thank you so much for your reply, though :)--Reem Al-Kashif (talk) 15:42, 19 November 2018 (UTC)

Query with Subform Data

Hello, I am new to WikiData and SPARQL. I am great with MySQL but struggling to get my head around this. I want to create a list of association football goalkeepers with the teams they have played for, plus number of appearances and goals scored. So far, I have tried this sort of thing:

SELECT ?item ?itemLabel ?date_of_birth ?sex_or_gender ?sex_or_genderLabel ?country_of_citizenship ?country_of_citizenshipLabel ?given_name ?given_nameLabel ?place_of_birth ?place_of_birthLabel ?position_played_on_team___speciality ?position_played_on_team___specialityLabel ?FIFA_player_ID ?member_of_sports_team ?member_of_sports_teamLabel ?start_time ?end_time ?number_of_matches_played ?number_of_points_goals_set_scored ?height ?sport ?sportLabel ?acquisition_transaction ?acquisition_transactionLabel WHERE {

 ?item wdt:P413 wd:Q201330.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
 OPTIONAL { ?item wdt:P569 ?date_of_birth. }
 OPTIONAL { ?item wdt:P735 ?given_name. }
 OPTIONAL { ?item wdt:P1469 ?FIFA_player_ID. }
 OPTIONAL { ?item wdt:P54 ?member_of_sports_team. }
 OPTIONAL { ?item wdt:P580 ?start_time. }
 OPTIONAL { ?item wdt:P582 ?end_time. }
 OPTIONAL { ?item wdt:P1350 ?number_of_matches_played. }
 OPTIONAL { ?item wdt:P1351 ?number_of_points_goals_set_scored. }
 OPTIONAL { ?item wdt:P1642 ?acquisition_transaction. }

} LIMIT 10

It will not return the 'number_of_matches_played' etc. as this is "subform data of ?member_of_sports_team".

Please help me on my way, by pointing me in the right direction! I have read lost of tutorials, but can not find the answer.

I've not checked whether ?item wdt:P1642 ?acquisition_transaction. is pointing to a qualifier (in your terminology, subform data) so pretty much ignored it. Below is a revised query which works for the other values. Can I point you to User talk:Tagishsimon#Wittylama where I explain at length & with examples how to get at qualifier and reference data - your subform data. That might help.

SELECT ?item ?itemLabel ?date_of_birth ?sex_or_gender ?sex_or_genderLabel ?country_of_citizenship ?country_of_citizenshipLabel ?given_name ?given_nameLabel ?place_of_birth ?place_of_birthLabel ?position_played_on_team___speciality ?position_played_on_team___specialityLabel ?FIFA_player_ID ?member_of_sports_team ?member_of_sports_teamLabel ?start_time ?end_time ?number_of_matches_played ?number_of_points_goals_set_scored ?height ?sport ?sportLabel ?acquisition_transaction ?acquisition_transactionLabel WHERE {

 ?item wdt:P413 wd:Q201330.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
 OPTIONAL { ?item wdt:P569 ?date_of_birth. }
 OPTIONAL { ?item wdt:P735 ?given_name. }
 OPTIONAL { ?item wdt:P1469 ?FIFA_player_ID. }
 OPTIONAL { ?item p:P54 ?statementP54. 
 OPTIONAL { ?statementP54 ps:P54 ?member_of_sports_team. }
 OPTIONAL { ?statementP54 pq:P580 ?start_time. }
 OPTIONAL { ?statementP54 pq:P582 ?end_time. }
 OPTIONAL { ?statementP54 pq:P1350 ?number_of_matches_played. }
 OPTIONAL { ?statementP54 pq:P1351 ?number_of_points_goals_set_scored. }
           }
 OPTIONAL { ?item wdt:P1642 ?acquisition_transaction. }

} LIMIT 10

Try it!

--Tagishsimon (talk) 22:10, 19 November 2018 (UTC)

-- Superb! Has really set me on my way and I will read your article to learn more. Thank you.

UK MPs

Two separate, but related, queries, please:

• Members of the United Kingdom parliament with no English Wikipedia article
• Members of the United Kingdom parliament with no image.

Cheers. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 12:37, 16 November 2018 (UTC)

@Pigsonthewing: Presuming for all parliaments? Change Q16707842 to Q30524710 if you just want the current parliament (spoiler - all have articles, 24 are missing images).

# No en.wiki article
SELECT distinct ?item ?itemLabel with 
{
  select distinct ?item where
  {  ?item wdt:P39/wdt:P279* wd:Q16707842 . }
} as %i
where
{include %i
  filter not exists {?article schema:about ?item ;
          schema:isPartOf <https://en.wikipedia.org/> .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

# No image
SELECT distinct ?item ?itemLabel with 
{
  select distinct ?item where
  {  ?item wdt:P39/wdt:P279* wd:Q16707842 . }
} as %i
where
{include %i
  filter not exists {?item wdt:P18 [] .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 12:52, 16 November 2018 (UTC)

@Tagishsimon: Thank you. Yes, all parliaments. That's odd, AIUI there are many red-linked (or unlinked) MPs on en.Wikipedia. Or were your referring only to the current parliament? Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 15:23, 19 November 2018 (UTC)

Was referring to the current. --Tagishsimon (talk) 16:26, 19 November 2018 (UTC)

For MPs who have served since 1832, I maintain a list at w:User:Andrew Gray/MPs with contextual detail (eg last seat held, last date in Parliament, etc; we're complete from 1935-45 onwards). For all MPs that we index (back to the fourteenth century, with gaps during the Commonwealth and most of the fifteenth century) use the query below. Andrew Gray (talk) 20:50, 19 November 2018 (UTC)

#All UK and pre-Union MPs, including Ireland and Scotland
#NB this omits the Commonwealth period which we don't yet model

SELECT distinct ?person1 ?person1Label
WHERE {
 { ?person1 wdt:P31 wd:Q5 ; wdt:P39 [ wdt:P279* wd:Q16707842 ] } # UK MP
 UNION { ?person1 wdt:P31 wd:Q5 ; wdt:P39 [ wdt:P279* wd:Q18015642 ] } # British MP
 UNION { ?person1 wdt:P31 wd:Q5 ; wdt:P39 [ wdt:P279* wd:Q18018860 ] } # English MP
 UNION { ?person1 wdt:P31 wd:Q5 ; wdt:P1614 [] } # or in HoP but without an item
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }  
 # look for articles (sitelinks) in English ("en")
  OPTIONAL {
    ?article schema:about ?person1 .
    ?article schema:inLanguage "en" .
    ?article schema:isPartOf <https://en.wikipedia.org/>
  }
  # but select items with no such article
  FILTER (!BOUND(?article))
}

Try it!

The distinction between this and Tagishsimon's one above is that this captures pre-1801 MPs; the wdt:P279* wd:Q16707842 form will only get those who were strictly MPs of the United Kingdom, not a predecessor. Andrew Gray (talk) 20:59, 19 November 2018 (UTC)

Yup. There's an element of 'be careful what you ask for' here, but equally it's always useful to specify the limits of scope of answers given, which I neglected to do. --Tagishsimon (talk) 21:22, 19 November 2018 (UTC)

@Pigsonthewing: - just realised I forgot to ping you with this reply yesterday! Andrew Gray (talk) 12:43, 20 November 2018 (UTC)

Hi!

Hi, I'm Luca Could someone helpme to estract a value for codificazione (P3294).

P3294 is a qualifier for the P4152 property

I wrote this:

SELECT ?item ?itemLabel ?itemDescription ?pattern_identificazione_del_formato_file ?codificazione ?codificazioneLabel WHERE {
  ?item wdt:P31 wd:Q235557.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en, de, fr, da, pl". }
  OPTIONAL { ?item wdt:P4152 ?pattern_identificazione_del_formato_file. }
  OPTIONAL { ?item wdt:P3294 ?codificazione. }
}
ORDER BY ?itemLabel
LIMIT 5800

Try it!

W Wikidata!!! LucaBiondi (talk) 22:22, 21 November 2018 (UTC)

done....

SELECT ?item ?itemLabel ?itemDescription ?pattern_identificazione_del_formato_file ?codificazione ?codificazioneLabel 
WHERE {

  ?item wdt:P31 wd:Q235557.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en, de, fr, da, pl". }
  OPTIONAL { ?item wdt:P4152 ?pattern_identificazione_del_formato_file. }
  OPTIONAL { ?item p:P4152 ?statementP4152. 
  OPTIONAL { ?statementP4152 pq:P3294 ?codificazione. }
}
} LIMIT 10

Try it!

I've added some format above; you seem to have solved the problem yourself, yes? --Tagishsimon (talk) 22:59, 21 November 2018 (UTC)

Extract hyperlinks from wikipedia page

Hi,

I am looking to extract a list of URL from a portuguese language (pt-BR) wikipedia page called https://pt.wikipedia.org/wiki/Rompimento_de_barragem_em_Mariana

Can you help?

Thanks,

Thomas.

Possibly. Exactly which URLs are you wanting to extract? It's not something that can be done with the wikidata query service, but it should not be too hard to parse the HTML or the wiki markup. --Tagishsimon (talk) 23:25, 18 November 2018 (UTC)

MediaWiki supports this out of the box, via the extlinks property in the query API. See for example https://pt.wikipedia.org/w/api.php?action=query&prop=extlinks&titles=Rompimento%20de%20barragem%20em%20Mariana. --TweetsFactsAndQueries (talk) 10:04, 25 November 2018 (UTC)

List of the most used items by a peculiar property

Hi,
I want to list the most used items by a peculiar property. By example, I know that there is 10 308 items having Museu Nacional d'Art de Catalunya (Q861252) for owned by (P127), but I don't know if this item is the one who is the most used by this property. Simon Villeneuve (talk) 15:08, 23 November 2018 (UTC)

SELECT ?something ?somethingLabel (count (DISTINCT ?item) as ?count) WHERE 
{
  ?item wdt:P127 ?something . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} 
group by ?something ?somethingLabel order by desc(?count)

Try it!

Thank you ! I thinked that I wasn't too far with this one, but it don't work (and I still don't know why)

SELECT DISTINCT ?item (COUNT(?creat) AS ?count) WHERE {
  ?item wdt:P127 ?creat .
}
GROUP BY ?item
HAVING (?count>5000)

Try it!

Simon Villeneuve (talk) 20:25, 23 November 2018 (UTC)

You're grouping by ?item ... any ?item will have 1 or a very small number of P127 ownerships. Than you require the ?count to be greater than 5000, which is not going to happen ... you'd need a single item to be owned by >5000 things. Remove the having and you can see the issue. So, group by the thing that owns the item is the way to go:

SELECT DISTINCT ?item (COUNT(?creat) AS ?count) WHERE {
  ?item wdt:P127 ?creat .
}
GROUP BY ?item order by desc(?count)

Try it!

--Tagishsimon (talk) 20:39, 23 November 2018 (UTC)

I was trying to do a similar query for the most used concepts for "Instance Of", but the above with P31 in place of P127 gets a "Query timeout limit reached" every time. How else should I modify it to make it work? --Reason&Squalor (talk) 01:58, 24 November 2018 (UTC)

Not sure it'll be possible - too many items have a P31. You might get queries to work if you cut down the number of items, e.g. by requiring the item to have other attributes - e.g. country, or a language wikilink. --Tagishsimon (talk) 05:10, 24 November 2018 (UTC)

Thank you for the suggestion!--Reason&Squalor (talk) 20:57, 24 November 2018 (UTC)

List of persons that have more than one citizenship and one of them is a specific one

Hi guys, could you help me with a query that lists all the persons that have more than one citizenship and one of them is a specific one, like "German" for example? --StanProg (talk) 15:45, 25 November 2018 (UTC)

@StanProg: This:

SELECT DISTINCT ?item ?itemLabel WHERE 
{
  ?item wdt:P27 wd:Q183.          # has country of citizenship of Germany
  ?item wdt:P27 ?somewhere.       # has a country of citizenship which we bind to ?somewhere
  filter (?somewhere != wd:Q183)  # ?somewhere does not equal Germany
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }    
}

Try it!

--Tagishsimon (talk) 15:51, 25 November 2018 (UTC)

Thank you a lot. Exactly what I needed. --StanProg (talk) 16:31, 25 November 2018 (UTC)

List of municipalities in a province

Hi, I am preparing a list of municipalities of the province of Girona. I need a query of all municipality of Spain (Q2074737) located in the administrative territorial entity (P131) Province of Girona (Q7194). The "problem" is that the majority of the items of the municipalities of the province of Girona are located in the administrative territorial entity (P131) in a Q, which respectively is located in the administrative territorial entity (P131) in Province of Girona (Q7194). See for exemple Palafrugell (Q13490) --Davidpar (talk) 20:57, 25 November 2018 (UTC)

@Davidpar: this, perhaps.

SELECT DISTINCT ?item ?itemLabel WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q2074737.
  ?item wdt:P131/wdt:P131* wd:Q7194.  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "es" . }    
}

Try it!

--Tagishsimon (talk) 21:50, 25 November 2018 (UTC)

@Tagishsimon: Thank you SO MUCH --Davidpar (talk) 21:55, 25 November 2018 (UTC)

Possible to build query on pages in categories and subcategories?

I've never posed this type of query before, so might need redirection to another channel (bot? zero experience with those too...). What I want to know:

• Which pages in all subcategories of en:Category:English-language newspapers by country also have a page in the Simple English WP?

Hope I've phrased this well enough; am willing to clarify. -- Deborahjay (talk) 11:45, 26 November 2018 (UTC)

Try this PetScan query. It matches all objects that are instance of (P31) newspaper (Q11032) or subclass of (P279) and have a "simple.wikipedia.org" sitelink to the articles in the en:Category:English-language newspapers by country category tree.

--Larske (talk) 12:06, 26 November 2018 (UTC)

Addition: The set of newspaper objects with articles in simple-wp has 139 members. The set of articles in the en-wp category tree has 308 members. The intersection of the two sets has 12 members and is the result from this query. --Larske (talk) 12:25, 26 November 2018 (UTC)

Worked quite well, thanks. Now how might I save the query structure so I {?!) could modify it for a series of related queries, e.g. English-language newspapers in Europe/South America/Africa/Middle East with pages in the Simple English WP

PetScan queries are automatically saved and assigned an id, PSID. The PSID for the question above is 6558105 and when you do any changes to the query and run it, it will get another PSID. The category part of the query is found in the "Categories" tab and the SPARQL (i.e. Wikidata) part is found in the "Other sources" tab. You may want to try out your SPARQL changes in the Wikidata Query interface before including the SPARQL stuff in PetScan.

For the changes to continent scope there is no simple short-cut. The en-wp category tree is not divided by continent so you would have to add all categories like "English-language newspapers published in <country name>" for all country category you want to include, and change "Combination" from "Subset" to "Union", if you go for changes in the category part. (In some cases it may be easier to exclude categories for countries outside the continent by stating "Negative categories"). If you try the SPARQL part, you could add some rows to the query to state that country (P17) should be a country that is continent (P30) for example Europe (Q46). This would work if all newspaper (Q11032) objects had a country (P17) statement, but unfortunately they have not. --Larske (talk) 18:35, 26 November 2018 (UTC)

Articles within a subject which exist on one language Wikipedia but not another

Surely this has been done before, but I'm unable to locate an example to follow. Specifically, I'm interested in finding items that are an instance of Q11173 (chemical compounds) for which there is a German Wikipedia article, but no English Wikipedia article. I should then be able to adapt this to other related searches that I'm also interested in. Any help would be greatly appreciated. Edgar181 (talk) 16:26, 26 November 2018 (UTC)

@Edgar181: Nope, it's never been done before ;)

SELECT DISTINCT ?item ?itemLabel WHERE 
{
  ?item wdt:P31 wd:Q11173. 
  ?article_de schema:about ?item ;
          schema:isPartOf <https://de.wikipedia.org/> .
filter not exists {  
  ?article_en schema:about ?item ;
          schema:isPartOf <https://en.wikipedia.org/> . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,de" . }    
}

Try it!

--Tagishsimon (talk) 16:32, 26 November 2018 (UTC)

@Tagishsimon: Fantastic, so quick! That's exactly what I was looking for. Thank you very much. Regards, Edgar181 (talk) 16:35, 26 November 2018 (UTC)

String in item description

Hi! I am looking for a query of all items, where the string '%Carambolage%' is a part of German item description or German item description is empty, but I didn't find such a similar example on your pages. Thank you, Doc Taxon (talk) 14:20, 27 November 2018 (UTC)

@Doc Taxon: That's challenging, without cutting down the search space ... there's no good way to search the ~28 million de desciptions for a substring; and we can say for sure that there are ~22+ million items with no German item description; again, we lack the time to list all of these. (Figures from User:Pasleim/Language statistics for items).

There is an MWAPI approach which returns 77 items; but it will be returning items with "Carambolage" in the label or in other-than-de descriptions ... if you remove the # from the final filter then the query returns 10 items which presumably do have "Carambolage" in the de descriptions. Come back if you have more ideas on how we can help with the "no description" element - e.g. if you can specify a set of instances to look through. (As normal - there's possibly a Quarry method to do this query, but I've not had much luck trying to get that SQL approach to do a substring search across all item labels.)

SELECT DISTINCT ?item ?itemLabel ?itemDescription
WHERE {
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:srsearch "Carambolage".
    ?title wikibase:apiOutput mwapi:title.
  }
  BIND(IRI(CONCAT(STR(wd:), ?title)) AS ?item)
  
            ?item rdfs:label ?itemLabel. 
            filter(lang(?itemLabel)="de")
            ?item schema:description ?itemDescription. 
            filter(lang(?itemDescription)="de")
 #           filter CONTAINS( ?itemDescription, "Carambolage")
}

Try it!

--Tagishsimon (talk) 14:45, 27 November 2018 (UTC)

@Tagishsimon: "German item description is empty" has been a mistake by myself. The query with and without # is exactly this what I wanted. But I forgot the aliases, can you include them please? Doc Taxon (talk) 16:22, 27 November 2018 (UTC)

@Doc Taxon: Here's a version 2 with aliases; and I've made possession of the de label, description & alias optional - probably should have done that in V1.

SELECT DISTINCT ?item ?itemLabel ?itemDescription (group_concat(distinct ?itemAlias) as ?itemAlias)
WHERE {
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:srsearch "Carambolage".
    ?title wikibase:apiOutput mwapi:title.
  }
  BIND(IRI(CONCAT(STR(wd:), ?title)) AS ?item)
  
 optional { ?item rdfs:label ?itemLabel. 
            filter(lang(?itemLabel)="de") }
 optional { ?item schema:description ?itemDescription. 
            filter(lang(?itemDescription)="de") }
 optional { ?item skos:altLabel ?itemAlias.
            filter(lang(?itemAlias)="de") }
 #           filter CONTAINS( ?itemDescription, "Carambolage")
} group by ?item ?itemLabel ?itemDescription

Try it!

--Tagishsimon (talk) 16:31, 27 November 2018 (UTC)

Sampling nature label : only one to be shown

Hello, I'd like this to show only one nature label per wikielement (in other terms, one row == one wikielement). For instance, Ponta Grossa Airport (Q7228040) has 2 natures linked to aerodrome (Q62447). How to show juste one nature related to aerodrome (Q62447) ? Thanks ! (note this query does not work in Edge) Bouzinac (talk) 15:47, 27 November 2018 (UTC)

PREFIX schema: <http://schema.org/>
SELECT  ?item ?itemLabel (SAMPLE(?naturelabel) AS ?naturelabel) ?countryLabel ?IATA ?ICAO (SAMPLE(?Local_Code) AS ?Local_Code)
WHERE {
  OPTIONAL { ?item wdt:P238 ?IATA. }
  OPTIONAL { ?item wdt:P239 ?ICAO. }
  OPTIONAL { ?item wdt:P17 ?country. }
  OPTIONAL { ?item wdt:P240  ?Local_Code. }
  OPTIONAL { ?item wdt:P5699 ?Local_Code. }
  OPTIONAL { ?item wdt:P5746 ?Local_Code. }
  OPTIONAL { ?item wdt:P5851 ?Local_Code. }
  OPTIONAL { ?item wdt:P6120 ?Local_Code. }
  OPTIONAL { ?item wdt:P31 ?nature. }
  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
  MINUS    { ?item (wdt:P31/wdt:P279*) wd:Q782667. } # exclude motorways where plane can land
  MINUS    { ?item wdt:P31/wdt:P279* wd:Q44665966.} # exclude  airports being build
  MINUS    { ?item wdt:P31/wdt:P279* wd:Q2265915.}  # exclude vol à voile/gliders
  MINUS    { ?item (wdt:P31/wdt:P279*) wd:Q502074. }               # exclude héliports
  MINUS    { ?item a wdno:P17.}                    #exclude airports that aren't physically based in a country
  FILTER( ?item not in 
  (wd:Q60972, wd:Q2886531,wd:Q176146, wd:Q323983, wd:Q388394, wd:Q472049, wd:Q473055, wd:Q576617, wd:Q577946, wd:Q619976, wd:Q649929, wd:Q761001, wd:Q768542, wd:Q939039, wd:Q955268, wd:Q1031829, wd:Q1032113, wd:Q1095676, wd:Q1148639, wd:Q1298115, wd:Q1338960, wd:Q1350451, wd:Q1403609, wd:Q1416857, wd:Q1433509, wd:Q1444594, wd:Q1493010, wd:Q1529149, wd:Q1636887, wd:Q1654635, wd:Q1656800, wd:Q1657595, wd:Q1658031, wd:Q1658500, wd:Q1751264, wd:Q1800787, wd:Q1836818, wd:Q1836849, wd:Q1893607, wd:Q2026702, wd:Q2360285, wd:Q2361539, wd:Q2371958, wd:Q2602457, wd:Q2663480, wd:Q2872473, wd:Q2886451, wd:Q2886460, wd:Q2886467, wd:Q2886488, wd:Q2886489, wd:Q2886504, wd:Q2886563, wd:Q2886566, wd:Q2886568, wd:Q2902833, wd:Q3196747, wd:Q3218170, wd:Q3499338, wd:Q3612693, wd:Q3635552, wd:Q3887690, wd:Q4115006, wd:Q4115518, wd:Q4257441, wd:Q4381026, wd:Q4402324, wd:Q5135044, wd:Q5365417, wd:Q5453767, wd:Q5607481, wd:Q5669602, wd:Q5721555, wd:Q5721576, wd:Q5733903, wd:Q5753943, wd:Q5754926, wd:Q6358138, wd:Q6413305, wd:Q6747097, wd:Q6906023, wd:Q7158431, wd:Q7188237, wd:Q7242494, wd:Q7274959, wd:Q7274976, wd:Q7277289, wd:Q7331905, wd:Q7368132, wd:Q7419397, wd:Q7419671, wd:Q7427836, wd:Q7580932, wd:Q7930596, wd:Q7942651, wd:Q7966282, wd:Q7969333, wd:Q7992372, wd:Q8243338, wd:Q8243340, wd:Q10822643, wd:Q10853535, wd:Q10860387, wd:Q10860397, wd:Q10860400, wd:Q10860405, wd:Q14679983, wd:Q14709491, wd:Q15221694, wd:Q17082712, wd:Q17512364, wd:Q18126343, wd:Q19406831, wd:Q19828268, wd:Q19873178, wd:Q21005896, wd:Q21016007
   , wd:Q56289584  ,wd:Q2886531,wd:Q660972,wd:Q7960223,wd:Q801410,wd:Q801410,wd:Q3271057,wd:Q3612309,wd:Q3015996,wd:Q4860232,wd:Q4651415
,wd:Q1031183,wd:Q441476,wd:Q441476,wd:Q19574887,wd:Q7460182,wd:Q4380750,wd:Q3015119,wd:Q7275460,wd:Q10845888,wd:Q14707052,wd:Q5038426,wd:Q704394,wd:Q704394,wd:Q156971,wd:Q18618942
,wd:Q1427413,wd:Q1835479,wd:Q371816,wd:Q3274819,wd:Q1433608,wd:Q3017040,wd:Q3913284,wd:Q16899355,wd:Q1766303,wd:Q1766303,wd:Q138896,wd:Q3207485,wd:Q681505,wd:Q1433649,wd:Q12694367
,wd:Q3015575,wd:Q2900666,wd:Q3014539,wd:Q2628568,wd:Q6344047,wd:Q1082819,wd:Q325491,wd:Q5659239,wd:Q6516685,wd:Q1655754,wd:Q706994
,wd:Q706994,wd:Q2071294,wd:Q1407551,wd:Q1030738,wd:Q1030738,wd:Q1031547,wd:Q3015041,wd:Q28449604,wd:Q1031594,wd:Q1031594,wd:Q3016361
,wd:Q7275582,wd:Q1654943,wd:Q3014320,wd:Q5065943,wd:Q3015462,wd:Q1800390,wd:Q784017,wd:Q3271311,wd:Q1498314,wd:Q7199307,wd:Q4651612,wd:Q2828202,wd:Q723474,wd:Q1164127,wd:Q174247,wd:Q7275708
,wd:Q47465,wd:Q47465,wd:Q960480,wd:Q2886509,wd:Q980034,wd:Q1780219,wd:Q8190944,wd:Q1513045,wd:Q3298050
,wd:Q3271063,wd:Q3234808,wd:Q1940767,wd:Q1520213,wd:Q18157236,wd:Q18157236,wd:Q7944502,wd:Q12694595,wd:Q2625576,wd:Q7940661,wd:Q3014516,wd:Q2318365,wd:Q6694741,wd:Q3696011,wd:Q652085,wd:Q8058641,wd:Q8058641,wd:Q1030442
,wd:Q1433506,wd:Q1433636)
         )#Exclude military airports sharing same IATA code than another civilian airport
  FILTER( ?IATA not in ( "LON","PAR","NYC","TYO","MOW","BUH" ,"BUE" ,"CHI", "JKT" ,"MIL", "YMQ" ,"OSA", "RIO" ,"ROM" ,"SAO" ,"SPK" ,"SEL", "STO", "YTO" ,"WAS") )  #exclude metropolitan city codes                                                
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en". }
} GROUP BY ?item ?itemLabel ?countryLabel ?ICAO ?IATA
ORDER BY ?country ?natureLabel

Try it!

@Bouzinac: Possibly something like this. Note that right now it has values ?item {wd:Q7228040} to restrict it to your example airport. Main changes are, more aggregation in the select statement and concomitant removal of items in the GROUP statement; and manually fetching Labels rather than having the label service do so - you cannot aggregate label values fetched by the label service.

PREFIX schema: <http://schema.org/>
SELECT  ?item ?itemLabel (SAMPLE(?natureLabel) AS ?natureLabel) (group_concat(distinct ?countryLabel) as ?countryLabel) (group_concat(distinct ?IATA) as ?iata) (group_concat(distinct ?ICAO) as ?icao) (SAMPLE(?Local_Code) AS ?Local_Code)
WHERE {
  values ?item {wd:Q7228040}
  OPTIONAL { ?item wdt:P238 ?IATA. }
  OPTIONAL { ?item wdt:P239 ?ICAO. }
  OPTIONAL { ?item wdt:P17 ?country. }
  OPTIONAL { ?item wdt:P240  ?Local_Code. }
  OPTIONAL { ?item wdt:P5699 ?Local_Code. }
  OPTIONAL { ?item wdt:P5746 ?Local_Code. }
  OPTIONAL { ?item wdt:P5851 ?Local_Code. }
  OPTIONAL { ?item wdt:P6120 ?Local_Code. }
  OPTIONAL { ?item wdt:P31 ?nature. }
  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
  MINUS    { ?item (wdt:P31/wdt:P279*) wd:Q782667. } # exclude motorways where plane can land
  MINUS    { ?item wdt:P31/wdt:P279* wd:Q44665966.} # exclude  airports being build
  MINUS    { ?item wdt:P31/wdt:P279* wd:Q2265915.}  # exclude vol à voile/gliders
  MINUS    { ?item (wdt:P31/wdt:P279*) wd:Q502074. }               # exclude héliports
  MINUS    { ?item a wdno:P17.}                    #exclude airports that aren't physically based in a country
  FILTER( ?item not in 
  (wd:Q60972, wd:Q2886531,wd:Q176146, wd:Q323983, wd:Q388394, wd:Q472049, wd:Q473055, wd:Q576617, wd:Q577946, wd:Q619976, wd:Q649929, wd:Q761001, wd:Q768542, wd:Q939039, wd:Q955268, wd:Q1031829, wd:Q1032113, wd:Q1095676, wd:Q1148639, wd:Q1298115, wd:Q1338960, wd:Q1350451, wd:Q1403609, wd:Q1416857, wd:Q1433509, wd:Q1444594, wd:Q1493010, wd:Q1529149, wd:Q1636887, wd:Q1654635, wd:Q1656800, wd:Q1657595, wd:Q1658031, wd:Q1658500, wd:Q1751264, wd:Q1800787, wd:Q1836818, wd:Q1836849, wd:Q1893607, wd:Q2026702, wd:Q2360285, wd:Q2361539, wd:Q2371958, wd:Q2602457, wd:Q2663480, wd:Q2872473, wd:Q2886451, wd:Q2886460, wd:Q2886467, wd:Q2886488, wd:Q2886489, wd:Q2886504, wd:Q2886563, wd:Q2886566, wd:Q2886568, wd:Q2902833, wd:Q3196747, wd:Q3218170, wd:Q3499338, wd:Q3612693, wd:Q3635552, wd:Q3887690, wd:Q4115006, wd:Q4115518, wd:Q4257441, wd:Q4381026, wd:Q4402324, wd:Q5135044, wd:Q5365417, wd:Q5453767, wd:Q5607481, wd:Q5669602, wd:Q5721555, wd:Q5721576, wd:Q5733903, wd:Q5753943, wd:Q5754926, wd:Q6358138, wd:Q6413305, wd:Q6747097, wd:Q6906023, wd:Q7158431, wd:Q7188237, wd:Q7242494, wd:Q7274959, wd:Q7274976, wd:Q7277289, wd:Q7331905, wd:Q7368132, wd:Q7419397, wd:Q7419671, wd:Q7427836, wd:Q7580932, wd:Q7930596, wd:Q7942651, wd:Q7966282, wd:Q7969333, wd:Q7992372, wd:Q8243338, wd:Q8243340, wd:Q10822643, wd:Q10853535, wd:Q10860387, wd:Q10860397, wd:Q10860400, wd:Q10860405, wd:Q14679983, wd:Q14709491, wd:Q15221694, wd:Q17082712, wd:Q17512364, wd:Q18126343, wd:Q19406831, wd:Q19828268, wd:Q19873178, wd:Q21005896, wd:Q21016007
   , wd:Q56289584  ,wd:Q2886531,wd:Q660972,wd:Q7960223,wd:Q801410,wd:Q801410,wd:Q3271057,wd:Q3612309,wd:Q3015996,wd:Q4860232,wd:Q4651415
,wd:Q1031183,wd:Q441476,wd:Q441476,wd:Q19574887,wd:Q7460182,wd:Q4380750,wd:Q3015119,wd:Q7275460,wd:Q10845888,wd:Q14707052,wd:Q5038426,wd:Q704394,wd:Q704394,wd:Q156971,wd:Q18618942
,wd:Q1427413,wd:Q1835479,wd:Q371816,wd:Q3274819,wd:Q1433608,wd:Q3017040,wd:Q3913284,wd:Q16899355,wd:Q1766303,wd:Q1766303,wd:Q138896,wd:Q3207485,wd:Q681505,wd:Q1433649,wd:Q12694367
,wd:Q3015575,wd:Q2900666,wd:Q3014539,wd:Q2628568,wd:Q6344047,wd:Q1082819,wd:Q325491,wd:Q5659239,wd:Q6516685,wd:Q1655754,wd:Q706994
,wd:Q706994,wd:Q2071294,wd:Q1407551,wd:Q1030738,wd:Q1030738,wd:Q1031547,wd:Q3015041,wd:Q28449604,wd:Q1031594,wd:Q1031594,wd:Q3016361
,wd:Q7275582,wd:Q1654943,wd:Q3014320,wd:Q5065943,wd:Q3015462,wd:Q1800390,wd:Q784017,wd:Q3271311,wd:Q1498314,wd:Q7199307,wd:Q4651612,wd:Q2828202,wd:Q723474,wd:Q1164127,wd:Q174247,wd:Q7275708
,wd:Q47465,wd:Q47465,wd:Q960480,wd:Q2886509,wd:Q980034,wd:Q1780219,wd:Q8190944,wd:Q1513045,wd:Q3298050
,wd:Q3271063,wd:Q3234808,wd:Q1940767,wd:Q1520213,wd:Q18157236,wd:Q18157236,wd:Q7944502,wd:Q12694595,wd:Q2625576,wd:Q7940661,wd:Q3014516,wd:Q2318365,wd:Q6694741,wd:Q3696011,wd:Q652085,wd:Q8058641,wd:Q8058641,wd:Q1030442
,wd:Q1433506,wd:Q1433636)
         )#Exclude military airports sharing same IATA code than another civilian airport
  FILTER( ?IATA not in ( "LON","PAR","NYC","TYO","MOW","BUH" ,"BUE" ,"CHI", "JKT" ,"MIL", "YMQ" ,"OSA", "RIO" ,"ROM" ,"SAO" ,"SPK" ,"SEL", "STO", "YTO" ,"WAS") )  #exclude metropolitan city codes                                                
  optional {?item rdfs:label ?itemLabel. filter(lang(?itemLabel)="en")}
  optional {?country rdfs:label ?countryLabel. filter(lang(?countryLabel)="en")}
  optional {?nature rdfs:label ?natureLabel. filter(lang(?natureLabel)="en")}
 } GROUP BY ?item ?itemLabel 
ORDER BY ?country ?natureLabel

Try it!

--Tagishsimon (talk) 16:06, 27 November 2018 (UTC)

Query not showing the good elements

Hello

PREFIX wdno: <http://www.wikidata.org/prop/novalue/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>

SELECT DISTINCT ?airport ?airportLabel (SAMPLE(?coord) AS ?coord) ?ICAO ?IATA (SAMPLE(?Local_Code) AS ?Local_Code) WHERE {
  ?airport (wdt:P31/wdt:P279*) wd:Q62447.
  ?airport wdt:P17 wd:Q23334.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr,[AUTO_LANGUAGE]". }
  OPTIONAL { ?airport wdt:P625 ?coord. }
  OPTIONAL { ?airport wdt:P239 ?ICAO. }
  OPTIONAL { ?airport wdt:P238 ?IATA. }
}
GROUP BY ?airport ?airportLabel ?ICAO ?IATA
ORDER BY ?coord

Try it!

It is supposed to show Sukhumi Babushara Airport (Q2347363) but doesn't. I suspect there is a trouble with countries being Georgia (Q230) (deprecated) /Abkhazia (Q23334) (in force) but why? Bouzinac (talk) 08:55, 27 November 2018 (UTC)

@Bouzinac: The reason why Sukhumi Babushara Airport (Q2347363) is not included in the result is that the country (P17) for this object is set to Republic of Abkhazia (Q31354462) while you ask for country (P17) to be Abkhazia (Q23334). --Larske (talk) 09:49, 27 November 2018 (UTC)

Here is a way to allow for both values for country (P17):

SELECT DISTINCT ?airport ?airportLabel (SAMPLE(?coord) AS ?coord) ?ICAO ?IATA WHERE {
  VALUES ?country { wd:Q23334 wd:Q31354462 }
  ?airport (wdt:P31/wdt:P279*) wd:Q62447.
  ?airport wdt:P17 ?country .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr,[AUTO_LANGUAGE]". }
  OPTIONAL { ?airport wdt:P625 ?coord. }
  OPTIONAL { ?airport wdt:P239 ?ICAO. }
  OPTIONAL { ?airport wdt:P238 ?IATA. }
}
GROUP BY ?airport ?airportLabel ?ICAO ?IATA

Try it!

--Larske (talk) 09:56, 27 November 2018 (UTC)

Well, isn't Republic of Abkhazia (Q31354462) abnormal value for a country (P17) ? Bouzinac (talk) 10:06, 27 November 2018 (UTC)

I have no opinion about the politics in that, just trying to explain the reason to the query result. Republic of Abkhazia (Q31354462) is an instance of (P31) state with limited recognition (Q15634554) which is a subclass of (P279) state (Q7275) as well as to country (Q6256) whereas Abkhazia (Q23334) is just an instance of (P31) disputed territory (Q15239622) and first-level administrative division (Q10864048) neither of which is a country (Q6256) or subclass of (P279) country (Q6256). Abkhazia (Q23334) does however have three country (P17) of which one is ... Republic of Abkhazia (Q31354462).

--Larske (talk) 08:53, 28 November 2018 (UTC)

Retrieve item URI by title without knowing language of title

Hello,

I am trying to achieve the same result as the following:

SELECT distinct ?item WHERE{

?item (wdt:P31/wdt:P279*) wd:Q11424. ?item ?label "Aguirre, der Zorn Gottes"@de.

}

but pretending that I don't know this is a German title. Is this possible? Reason&Squalor (talk) 04:09, 28 November 2018 (UTC)

@Reason&Squalor: Yup. First, if you do know it's a de label, then:

SELECT distinct ?item WHERE
{
 ?item wdt:P31/wdt:P279* wd:Q11424 . 
 ?item rdfs:label "Aguirre, der Zorn Gottes"@de .
}

Try it!

and if you do not (and if the timeout gods are good to you) then:

SELECT distinct ?item WHERE
{
 ?item wdt:P31/wdt:P279* wd:Q11424 . 
 ?item rdfs:label ?itemLabel .
 filter (contains(?itemLabel,"Aguirre, der Zorn Gottes"))
}

Try it!

--Tagishsimon (talk) 04:52, 28 November 2018 (UTC)