Wikidata:Request a query/Archive/2017/09

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

Contents

Wikidata items with a sitelink to any Wikisource, sorted by Wikisource language

Haven't seen any query example with Wikisource, so this could be a good start. --Daniel Mietchen (talk) 02:04, 13 August 2017 (UTC)

Only seems to work with a LIMIT (or perhaps it’s just the spotty WiFi), but here you go:
SELECT ?item ?wikisourceSitelink ?wikisourceLanguage WHERE {
  ?wikisourceSitelink schema:isPartOf [ wikibase:wikiGroup "wikisource" ];
                      schema:inLanguage ?wikisourceLanguage;
                      schema:about ?item.
}
ORDER BY ?wikisourceLanguage
LIMIT 100
Try it! --TweetsFactsAndQueries (talk) 14:16, 13 August 2017 (UTC)
Saw this only now — thanks! --Daniel Mietchen (talk) 01:00, 2 September 2017 (UTC)

Multiple item search

Is it possible to search multiple items (max. 100 search terms) by their labels without running into timeout? Take for example, I want to search items with names Prolog, Python, PHP etc. in one single query. Thanks. John Samuel 09:09, 3 September 2017 (UTC)

Term search is always tricky, perhaps you could get better performace with SQL. Anyway:
SELECT ?item ?label {
  VALUES ?label { "Prolog"@en "Python"@en "PHP"@en } .
  ?item rdfs:label ?label .
}
Try it!
Matěj Suchánek (talk) 16:38, 3 September 2017 (UTC)

Species whose parent taxon is not a genus

Hello, would it be possible to have a list of species (taxon rank (P105) = species (Q7432)) where the parent taxon (P171) is not a genus (Q34740)? (Example) Thanks, Korg (talk) 11:10, 3 September 2017 (UTC)

SELECT ?item WHERE {
  ?item wdt:P105 wd:Q7432 .
  ?item wdt:P171 ?genus .
  MINUS { ?genus wdt:P105 wd:Q34740 } .
  } LIMIT 10000
Try it!
@Korg: You probably add a bit more filtering. To filter out things like subgenus (Q3238261). Just add more "MINUS" lines. Multichill (talk) 11:46, 3 September 2017 (UTC)
@Multichill: Thank you very much! Korg (talk) 12:02, 3 September 2017 (UTC)

Commonswiki link and P910

Please, a query for items which:

  • have a commonswiki link, beginning by "Category:"
  • have P910

Visite fortuitement prolongée (talk) 13:29, 3 September 2017 (UTC)

SELECT ?item ?sitelink ?category {
  ?item wdt:P910 ?category;
        ^schema:about [ schema:isPartOf <https://commons.wikimedia.org/>; schema:name ?sitelink ] .
  FILTER( STRSTARTS( ?sitelink, 'Category:' ) ) .
} LIMIT 1000
Try it!
Matěj Suchánek (talk) 16:35, 3 September 2017 (UTC)
@Visite fortuitement prolongée: this is also one of the constraints on Property talk:P910, the report is at Wikidata:Database reports/Complex_constraint_violations/P910#Items_that_link_to_a_Commons_category. Multichill (talk) 20:46, 3 September 2017 (UTC)
Thank you! Visite fortuitement prolongée (talk) 20:49, 3 September 2017 (UTC)

Commonswiki link and P373

Please, a query for items which:

  • have a commonswiki link, beginning by "Category:"
  • have not P373

Visite fortuitement prolongée (talk) 13:29, 3 September 2017 (UTC)

SELECT ?item ?sitelink {
  ?item ^schema:about [ schema:isPartOf <https://commons.wikimedia.org/>; schema:name ?sitelink ] .
  FILTER( STRSTARTS( ?sitelink, 'Category:' ) ) .
  MINUS { ?item wdt:P373 [] } .
} LIMIT 1000
Try it!
Matěj Suchánek (talk) 16:36, 3 September 2017 (UTC)
Thank you! Visite fortuitement prolongée (talk) 20:50, 3 September 2017 (UTC)

Painters with duplicate labels in English

Sometimes we have multiple painters with the same name like for example Jan Steen (Q205863) and Jan Steen (Q2483000). These often get mixed up so different from (P1889) gets added so we can find these mistakes. I would like to get an overview of painter pairs where both have the same label in English, but not linked to each other with different from (P1889). I keep running into time-outs so I hope someone else has a smart way of fixing this:

SELECT ?item ?item2 WHERE {
  ?item wdt:P106 wd:Q1028181 .
  ?item rdfs:label ?itemLabel.
  FILTER(LANG(?itemLabel) = "en") .
  ?item2 rdfs:label ?item2Label .
  FILTER(LANG(?item2Label) = "en" && !(str(?item)=str(?item2)) && ?itemLabel=?item2Label ) .
  ?item2 wdt:P106 wd:Q1028181 .
  } LIMIT 10

Try it!

If this can get fixed, than the next step would be to expand to other languages and/or aliases. Multichill (talk) 11:53, 3 September 2017 (UTC)

SELECT ?item ?item2 ?itemLabel 
WHERE
{
  ?item wdt:P106 wd:Q1028181 .
  ?item rdfs:label ?itemLabel .
  FILTER(LANG(?itemLabel) = "en") .
  ?item2 wdt:P106 wd:Q1028181 .
  ?item2 rdfs:label ?itemLabel .
  FILTER(str(?item) < str(?item2))
}
LIMIT 1000

Try it! It can be shorter, see above.
--- Jura 12:05, 3 September 2017 (UTC)

Btw I adjusted the query to painters with same year of born and lowered the limit, it returned a bunch of items to merge. Matěj Suchánek (talk) 18:11, 3 September 2017 (UTC)
@Jura1: @Matěj Suchánek: thanks, I started Wikidata:WikiProject sum of all paintings/Creators same name based on your input. Still needs a bit of tweaking because multiple same ?item gets returned and Listeriabot just throws it all in one line. Multichill (talk)
For Listeria, using "!=" gets you two lines and might make it easier to read. The question is if you want to add different from (P1889) to any two painters with the same name and maybe insufficient dates to differentiate them.
--- Jura 11:03, 4 September 2017 (UTC)

Birth place and gender of the "List of articles every Wikipedia should have" biographies

Hello! Is there an easy way to search for the gender and the birth places of the people in this list? -Theklan (talk) 13:06, 4 September 2017 (UTC)

SELECT ?item ?itemLabel ?itemDescription ?genderLabel ?place ?placeLabel {
  ?item wdt:P972 wd:Q5460604;
        wdt:P31 wd:Q5 .
  OPTIONAL { ?item wdt:P21 ?gender } .
  OPTIONAL { ?item wdt:P19 ?place } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } .
}
Try it!
Matěj Suchánek (talk) 13:39, 4 September 2017 (UTC)

Who is in space now?

Should we have a complete data for any moment since 1961? --Infovarius (talk) 10:11, 4 September 2017 (UTC)

@Infovarius: I do not understand your question. Which properties do you want to know who is in space?--Micru (talk) 14:32, 8 September 2017 (UTC)
One part of the question was exactly this - which property can I use for it? The rest part is what data is insufficient now and should be added? I see e.g. astronaut mission (P450) but I don't see time qualifiers. Looking at Soyuz TMA-19M (Q14288150)    , one can see dates, so probably if there is no time of spacecraft landing (P620) for a spaceship all their pilots can be assumed to be there still. --Infovarius (talk) 21:02, 10 September 2017 (UTC)

Q2657718 without qualifiers

I want to have a list of items that have described by source (P1343) Armenian Soviet Encyclopedia (Q2657718) without any qualifiers. Sjoerd de Bruin (talk) 15:22, 9 September 2017 (UTC)

SELECT ?item {
  ?item p:P1343 ?statement .
  ?statement ps:P1343 wd:Q2657718 .
  MINUS { ?statement ?pq [] . ?pq ^wikibase:qualifier [] } .
}
Try it!
Matěj Suchánek (talk) 15:59, 9 September 2017 (UTC)

All ketones that boil between 175°C and 185°C

Similar to the above, and also from User:Walkerma. --Daniel Mietchen (talk) 20:39, 27 August 2017 (UTC)

Based on the query in the section above, I got
SELECT
  ?item ?itemLabel 
  ?bp ?bpunit ?bpunitLabel 
WHERE
{
    ?item wdt:P31 / wdt:P279* wd:Q170744 .

    ?item p:P2102/psv:P2102 [ wikibase:quantityAmount ?bp ; wikibase:quantityUnit ?bpunit ] .
    FILTER ( ( ?bp > 175 && ?bpunit = wd:Q25267 ) \u007C\u007C ( ?bp > 347 && ?mpunit = wd:Q42289 )  ) 
    FILTER ( ( ?bp < 185 && ?bpunit = wd:Q25267 ) \u007C\u007C ( ?bp < 365 && ?mpunit = wd:Q42289 )  ) 

    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 1000
Try it!
but it currently does not yield any results. @Walkerma: can you perhaps add a few values to see whether the query works in principle? --Daniel Mietchen (talk) 11:12, 29 August 2017 (UTC)
SELECT
  ?item ?itemLabel 
  ?bp ?bpunit ?bpunitLabel 
WHERE
{
    ?item wdt:P31* / wdt:P279* wd:Q170744 .
    OPTIONAL { ?item p:P2102/psv:P2102 [ wikibase:quantityAmount ?bp ; wikibase:quantityUnit ?bpunit ] }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 1000

Try it!

gives just 6. None has boiling point (P2102). Looks like WQS doesn't know much more about ketones than I.
--- Jura 11:19, 29 August 2017 (UTC)

Thanks. Pinging Walkerma again to see whether he could help us get things started in this space. --Daniel Mietchen (talk) 01:42, 2 September 2017 (UTC)
He might want to add P31:Q170744 or P279:Q170744 to https://petscan.wmflabs.org/?psid=1254707 (login to Widar, then reload the page and a box will appear on the right side).
--- Jura 06:14, 2 September 2017 (UTC)
@Daniel Mietchen:, I sent an email on Wednesday night with a screenshot of a problem I was having - can you respond? w:Cycloheptanone looks like a good test example that I was trying to work with. Thanks, Walkerma (talk) 16:39, 2 September 2017 (UTC)

OK, it turns out that the problem I had was that the boiling point was reported as a range, and the field for boiling point will only accept a single number. Thanks, @Daniel Mietchen:! I then entered a boiling point for cyclohexyl methyl ketone, successfully this time (my source gave a single number). However, when I run the query, I do not see that compound show up. (BTW, I had to put the reference for the BP on the talk page, since I was unable to enter my source into the references field.) Do you have any idea, @Jura1:, why this still fails? Walkerma (talk) 03:59, 11 September 2017 (UTC)

cyclohexyl methyl ketone (Q27290697) hadn't been classified as a ketone yet. Now that it is, it actually does show up in the query. --Daniel Mietchen (talk) 06:27, 11 September 2017 (UTC)
Excellent! All I need now is some way to cite my source, and perhaps I can start adding some BP values for other compounds? Is there some way to stop the incorrect results? Thanks, Walkerma (talk) 12:50, 11 September 2017 (UTC)

Matrices

I want every item which is subclass of (P279) matrix (Q44337) or subclass of subclasses. --Bigbossfarin (talk) 08:38, 12 September 2017 (UTC)

SELECT ?link WHERE { ?link (wdt:P279+) wd:Q44337. }

Try it!

-- I think got it --Bigbossfarin (talk) 08:46, 12 September 2017 (UTC)

@Bigbossfarin: some explanation in 6.1.4 d1g (talk) 13:03, 12 September 2017 (UTC)

Population and Area as of 1990-12-31

Can you modify this ListeriaList gives me P1028 and P2046 as of 1990-12-31 as long it is found with preferred or normal rank? -- Innocent bystander (talk) 18:09, 12 September 2017 (UTC)

I suppose you meant P1082. w:sv:Special:Diff/40821607. Matěj Suchánek (talk) 18:32, 12 September 2017 (UTC)

Hierarchical Queries

I believe this topic was discussed many times but I wasn't able to find something clear using a search.

I need to find all astronomical objects that have an image. So I need to find all descendants of "astronomical object type" wd:Q17444909 and then filter them by image presence, piece of cake in SQL but pretty tricky with SPARQL. I can semi-manually execute "?link wdt:P31 wd:Q17444909." and then "?link wdt:P31 ..." against all results recursively but maybe there is some way to write such a hierarchical query here on Wikidata?  – The preceding unsigned comment was added by Fusanari Shimizu (talk • contribs).

use OPTIONAL{?item wdt:P18 ?image}, then order by ?image
when you need to exclude items with images: MINUS {?item wdt:P18 ?image}
@Fusanari Shimizu: explanation in 7 and 9 d1g (talk) 19:02, 11 September 2017 (UTC)
Thank you very much d1g! I've played with queries as it's suggested in the article you referred and made one that do the job. I'm not sure that I still understand the magic :) and that result is complete but according to brief review it returns (almost) all I interested in.
SELECT ?link ?linkLabel ?image
WHERE
{
  ?link wdt:P31/wdt:P279*/wdt:P31* wd:Q17444909.
  { ?link wdt:P18 ?image }
}
Try it!

--Fusanari Shimizu (talk) 03:24, 12 September 2017 (UTC)

If you about to fill missing images (e.g. Kepler-90g (Q16586552)) then use slightly different query:

The following query uses these:

d1g (talk) 13:00, 12 September 2017 (UTC)

Thanks again! BTW one more question in regards to this topic. I'd like to add "distance from Earth" parameter to readout but it has not only numeric value itself but also "Unit". Could you please advise how I can include this parameter? "?distanceUnit" doesn't work :)
SELECT ?link ?linkLabel ?distance ?distanceUnit ?discovered ?typeLabel ?image
WHERE
{
  ?link wdt:P31/wdt:P279*/wdt:P31* wd:Q17444909.
  { ?link wdt:P18 ?image }
  OPTIONAL { ?link wdt:P31 ?type }
  OPTIONAL { ?link wdt:P2583 ?distance }
  OPTIONAL { ?link wdt:P575 ?discovered }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
Try it!
--Fusanari Shimizu (talk) 15:51, 12 September 2017 (UTC)
?link p:P2583/psv:P2583 [ wikibase:quantityAmount ?distance; wikibase:quantityUnit ?distanceUnit ] does that. Matěj Suchánek (talk) 16:21, 12 September 2017 (UTC)
Thank you Matěj! Works great. By some reason it adds complete duplicates to the readout (see here https://i.imgur.com/AjmhB1z.png) but it's really not a big deal. I put the final query here just in case if anybody else would like to create an astronomical flashcards or background slideshow.
SELECT DISTINCT ?link ?linkLabel ?typeLabel ?distance ?distanceUnitLabel ?discovered ?constellationLabel ?image
WHERE
{
  ?link wdt:P31/wdt:P279*/wdt:P31* wd:Q17444909.
  { ?link wdt:P18 ?image }
  OPTIONAL { ?link wdt:P31 ?type }
  OPTIONAL {?link p:P2583/psv:P2583 [ wikibase:quantityAmount ?distance; wikibase:quantityUnit ?distanceUnit ]}
  OPTIONAL { ?link wdt:P575 ?discovered }
  OPTIONAL { ?link wdt:P59 ?constellation }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
Try it!
--Fusanari Shimizu (talk) 18:38, 12 September 2017 (UTC)
@Fusanari Shimizu: final piece is to change "SELECT" to "SELECT DISTINCT"
Explanation: it may start from different positions in wdt:P31/wdt:P279*/wdt:P31* - so it would get Q2 from multiples starting points.
This is less an issue when more strict paths. d1g (talk) 18:55, 12 September 2017 (UTC)
(edit conflict) Those duplicates may be caused by some property having more than one value or by multiple possible ways in the hierarchy. Adding DISTINCT after SELECT should handle this. Matěj Suchánek (talk) 18:56, 12 September 2017 (UTC)
Got it, thanks! Updated the query inline. --Fusanari Shimizu (talk) 19:01, 12 September 2017 (UTC)

List of cities in french ending "ovie"

Hello, I'd like to list any cities that has a french name ending in "ovie", like "Varsovie","Cracovie", "Charcovie", etc + their countries. Thanks!

--Bouzinac (talk) 12:37, 14 September 2017 (UTC)Alexis

@Bouzinac: Here's a first stab at a query, but I feel we ought to be able to do better than this -- at the moment it's only returning 3 hits.
SELECT DISTINCT ?item ?item_name ?country_name ?coords WHERE {
  {?item wdt:P31/wdt:P279* wd:Q515} UNION {?item wdt:P31/wdt:P279* wd:Q3957} .
  ?item rdfs:label ?item_name. FILTER(LANG(?item_name) = "fr") .
  FILTER(STRENDS(?item_name, "ovie")) .
  OPTIONAL {
      ?item wdt:P17 ?country .
      ?country rdfs:label ?country_name. FILTER(LANG(?country_name) = "en") .
  }
  OPTIONAL {
      ?item wdt:P625 ?coords .
  }
}
Try it! -- Jheald (talk) 13:15, 14 September 2017 (UTC)

Thanks Jheald (talkcontribslogs) , could you also include in your search the aliases? For instance Kharkiv is also (not mainly) know under "Charcovie", it's in the aliases. --Bouzinac (talk) 13:22, 14 September 2017 (UTC)

@Bouzinac: Updated query, that now also lists any aliases that end in "ovie". I had to limit it to cities only, because the UNION statement it timed out.:
SELECT DISTINCT ?item ?item_name ?item_alias ?country_name ?coords WHERE {
  ?item wdt:P31/wdt:P279* wd:Q515 .
#  {?item wdt:P31/wdt:P279* wd:Q515} UNION {?item wdt:P31/wdt:P279* wd:Q3957} .
  ?item rdfs:label ?item_name. FILTER(LANG(?item_name) = "fr") .
  OPTIONAL {
    ?item skos:altLabel ?item_alias. 
    FILTER(LANG(?item_alias) = "fr") .
    FILTER(STRENDS(?item_alias, "ovie"))
  }
  FILTER(STRENDS(?item_name, "ovie") || bound(?item_alias)) .
  OPTIONAL {
      ?item wdt:P17 ?country .
      ?country rdfs:label ?country_name. FILTER(LANG(?country_name) = "en") .
  }
  OPTIONAL {
      ?item wdt:P625 ?coords .
  }
}
Try it! -- Jheald (talk) 13:38, 14 September 2017 (UTC)
It looks better! Thank you Jheald (talkcontribslogs)

Description containing a certain word

Hello, I'd like to find items whose description contains a certain word, e.g. "annelidologist". I tried the following query but I get a timeout error:

SELECT ?item ?description
WHERE
{
  ?item wdt:P31 wd:Q5 ;
        schema:description ?description .
  FILTER(CONTAINS(?description, "annelidologist"@en)) .
}
LIMIT 10

Try it!

Korg (talk) 15:36, 5 September 2017 (UTC)

No response for (almost) a day, so let me comment: there is no index for the query service to my knowledge, thus searches are inefficient. It might be a better idea to user Quarry and query the wb_terms table of the wikidatawiki_p database with SQL, or to use the regular Mediawiki search functionality (Cirrus, e.g. this result; might also be available via the API). —MisterSynergy (talk) 11:15, 6 September 2017 (UTC)
Thank you for your answer! I thought of the query service in order to be able to add more conditions to the search (e.g. items with no occupation (P106) defined). I'm not sure if Quarry can do that. After some search, I found the following related requests: [1], [2]. Korg (talk) 14:48, 6 September 2017 (UTC)

This is the weakest point of all Wikidata, I think. I'd like to see all items with Property:P1216 that have "cinema" in the label (or descripition), but not P31 = Q41253. Impossible to do in any of the tools AFAIK. Neither BECAON, nor Petscan, nor SPARQL are of any help. I cannot even get a list with all items that have P1216, plus the English label. These are over 400.000 items, so SPARQL always times out; Petscan offers it in HTML only for the first 10.000, but the CSV that is supposed to contain the same data simply doesn't have the labels. BEACON doesn't list P1216 as possibility, and it wouldn't render the labels anyway. So we have a data grave of 400.000 badly categorized bot-generated entries, and it's very very difficult to make them any better. I tried my luck with the built-in Wikidata Search, but it's just not good enough. --Anvilaquarius (talk) 17:20, 12 September 2017 (UTC)

The last resort is the Linked Data Fragment service, which literally just returns triples. This can be used to extract the P1216 statements, 100 at a time: [3]. I am not sure if there is then a batch label look-up service. I seem to remember that there is, but I can't remember where. Jheald (talk) 17:40, 12 September 2017 (UTC)
You may be able to results if you batch by county. This query suggests the numbers should be reasonable: tinyurl.com/yb6rdnr4. Jheald (talk) 17:53, 12 September 2017 (UTC)
Actually, the lookup for "cinema" in the description with no matching P31 seems to be fairly efficient, even without batching:
SELECT ?item ?itemLabel ?description ?admLabel WHERE {
        ?item wdt:P1216 [] .
#        ?item wdt:P131+ wd:Q21 .
        OPTIONAL {?item wdt:P131 ?adm} .
        ?item schema:description ?description .
        FILTER(CONTAINS(?description, "cinema"@en)) .
        MINUS {?item wdt:P31 wd:Q41253} .
        SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    }
Try it!
Jheald (talk) 18:03, 12 September 2017 (UTC)
Still 216,947 items without any P31 though. tinyurl.com/y7zy5jxt Only 38,307 without some kind of description tinyurl.com/y7ec8f7g; but many of those descriptions may only be street addresses. Jheald (talk) 18:16, 12 September 2017 (UTC)
With the help of your query (and some variations) I could reduce those 216,947 to under 200,000 already (by catching most of those many many listed barns, granaries, garden walls, headstones etc.) More to come. Thanks again! --Anvilaquarius (talk) 19:47, 14 September 2017 (UTC)
Thanks for that query! I wasn't able to make something like this work from the SPARQL examples and manuals in Wikidata, so thanks for your expertise. --Anvilaquarius (talk) 09:24, 14 September 2017 (UTC)
With some googling, I found out how to do that for labels as well. Just for the record (and for the benefit of people searching for a solution of this problem):
SELECT ?item ?itemLabel WHERE {
        ?item wdt:P1216 [] .
        ?item rdfs:label ?itemLabel. 
        FILTER(CONTAINS(LCASE(?itemLabel), "cinema"@en)). 
        MINUS {?item wdt:P31 wd:Q41253} .
        SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    }
    LIMIT 50
Try it!

--Anvilaquarius (talk) 10:09, 14 September 2017 (UTC)

Average distance between birthplace and deathplace by year

Hi,

I need some help to refine this query :

#Average distance between birthplace and deathplace by year
#defaultView:LineChart
SELECT ?year ?average WHERE {
    SELECT ?year (AVG(?dist) AS ?average) WHERE {
      ?person wdt:P27 wd:Q142 ; wdt:P19 ?birthplace ; wdt:P20 ?deathplace ; wdt:P570 ?date .
      ?birthplace wdt:P625 ?coordBirth .
      ?deathplace wdt:P625 ?coordDeath .
      BIND(geof:distance(?coordBirth, ?coordDeath) as ?dist)
      BIND(year(?date) as ?year )
      FILTER(?year > 1000 )
    }
  GROUP BY ?year
}

Try it!

First, I knew how to fix the bug of the y-axis beginning at zero instead of the first value but I forgot it…

Second, I try not to be limited to country of citizenship (P27) = France (Q142) but it times out… (probably too many people, but a large number of people is needed to smooth a little bit the graph). Any ideas?

Cdlt, VIGNERON (talk) 18:28, 17 September 2017 (UTC)

Musicians with honorary degrees

I saw this question on Quora:

Approximately how many pop/rock musicians have gotten honorary music degrees, and why do you say so?

and thought it would make for a good example query (though I suspect our data is far from complete). Maybe drop the "music" qualifier. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 18:06, 16 September 2017 (UTC)

Hi Pigsonthewing,
Not sure how to query that. Here is a try. First how many « pop/rock musicians » do we have?
SELECT DISTINCT ?person WHERE {
  ?person wdt:P106/wdt:P279* wd:Q639669 ; wdt:P136/wdt:P279* wd:Q373342 .
}
Try it!
Only 35 878 results (which is already quite low).
Then I'm lost and I fear we don't have enough data. I tried those with a academic degree (P512) but there is only 13 results! With educated at (P69) we have 3869 results but most of them are not musical school.
In the end, I've got 727 results (so only 2%):
SELECT DISTINCT ?person ?schoolLabel WHERE {
  ?person wdt:P106/wdt:P279* wd:Q639669 ; wdt:P136/wdt:P279* wd:Q373342 ; wdt:P69 ?school .
  ?school wdt:P31/wdt:P279* wd:Q30892578 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
Finally, the biggest questions: how representative are Wikidata data? and what is their quality and completeness/exhaustiveness?
Cdlt, VIGNERON (talk) 18:47, 17 September 2017 (UTC)

Query for organisms whose wikipedia page is not linked to the scientific name

Hi. I have a query here to help me identify species of animal/plant with a "common name of" property. This is to help me find wikidata items whose wikipedia links are mostly concentrated in their wikidata common name item (e.g. "dog") rather than the wikidata scientific name item ("Canis lupus familiaris"). But this query returns a number of items that I don't want. In particular (A) I only want items whose "common name of" property points to a wikidata item that itself is an instance of a "taxon" (Q16521) and (B) I might further like to restrict this to cases where the wikidata item pointed to by "common name of" contains no (english) wikipedia sitelinks. This is true e.g. in the case of "dog" (Q144) which is a common name of Q20717272 which itself has no en.wikipedia sitelinks (in fact, in this case, no sitelinks at all). Can anyone help me modify my query to add restrictions (A) and maybe (B)? Cheers HYanWong (talk) 09:26, 18 September 2017 (UTC)

@HYanWong: this is quite technic so I maybe made an error. Is this query what you wanted:
SELECT ?item ?itemLabel ?pq_obj ?pq_objLabel WHERE {
  ?item p:P31 ?statement .
  ?statement ps:P31 wd:Q502895 .
  ?statement pq:P642 ?pq_obj .
  ?pq_qual wikibase:qualifier pq:P642 .
  ?pq_obj wdt:P31 wd:Q16521 .
  MINUS { ?article schema:about ?pq_obj. ?article schema:isPartOf <https://en.wikipedia.org/> }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,de,fr,pl" }
}
Try it!
Cdlt, VIGNERON (talk) 12:14, 18 September 2017 (UTC)
Thanks a lot VIGNERON. I think that's exactly it! Much appreciated HYanWong (talk) 15:25, 18 September 2017 (UTC)

Life Sciences-related queries

Queries related to life sciences topics.

Get all parts of the human body (Q23852)

I'm trying to get the hierarchy of the human body anatomy. Here is the query:

# Get human anatomy
SELECT ?item ?itemLabel WHERE {
  ?item wdt:P361+ wd:Q23852 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
order by (?item)
Try it!

As you can see, It does not return (for example) neither kidney (Q9377), nor liver (Q9368) nor heart (Q1072).

Is there a way to get the complete hierarchy of anatomical parts?

@Floatingpurr: for this kind of request, you could use this other tool.
Meanwhile, the three organs you mentioned kidney (Q9377), liver (Q9368) or heart (Q1072)) are not linked to the human body (Q23852), it's logical they don't appear in the results (which is to be expected as these items are not about the organ for human but for all living species). You should ask on Wikidata:WikiProject Anatomy for more informations.
Cdlt, VIGNERON (talk) 17:22, 18 September 2017 (UTC)
@VIGNERON:, Thank you! That tools is incredibly useful for my analysis. As you mentioned, my query cannot return kidney (Q9377), liver (Q9368) or heart (Q1072) since they are not linked to the human body (Q23852). I agree with you: those are broader concepts, not juste entities for describing human organs. Probably their human counterpart does not exist yet. This is not a query problem, it is a data problem.
Thanks! Floatingpurr (talk) 00:17, 19 September 2017 (UTC)

demonym

I need a query which will give me Bengali (bn) & Italian (it) value of demonym (P1549) for all country (P31=Q6256). Like this:

Q bn value it value
Q902 বাংলাদেশী bengalese
Q38 ইতালীয় italiano/italiana*

(*Italian word have masculine & feminine gender but Bengali don't) --Aftabuzzaman (talk) 20:56, 18 September 2017 (UTC)

@Aftabuzzaman: here is a first and simple try:
SELECT ?item ?bn (GROUP_CONCAT(DISTINCT(?it); separator="/") as ?it) {
  ?item wdt:P31 wd:Q6256 .
  OPTIONAL {
    ?item p:P1549 ?bn_statement .
    ?bn_statement ps:P1549 ?bn .
    FILTER( LANG( ?bn ) = 'bn' ) .
  }
  OPTIONAL {
    ?item p:P1549 ?it_statement .
    ?it_statement ps:P1549 ?it .
    FILTER( LANG( ?it ) = 'it' ) .
  } .
}
GROUP BY ?item ?itemLabel ?bn
Try it!
Apparently, there is very few demonym in italian (or maybe something is wrong with my query... these kind of lexical data are always tricky, I can't wait to have the L-items for managing this kind of stuff).
Cdlt, VIGNERON (talk) 22:15, 18 September 2017 (UTC)
ThanksAftabuzzaman (talk) 22:34, 18 September 2017 (UTC)

Disaster-related queries

Inspired by the disaster response discussions at Wikimania, I am looking into Wikidata queries that would be helpful in response to recent disasters or in terms of understanding past or even predicted ones. I plan to post several of such queries here, so chose to make a dedicated umbrella section where they could be grouped together until the set matures into something we can put somewhere more useful. I am conscious that some of the queries might time out, so feel free to reshape them such that they do work to some extent. I also understand that we may well be short of data in many cases and see the queries then as a tool to help us get the corresponding data models started, the data gathered and data consistency checked. Thanks for your help with this, --Daniel Mietchen (talk) 22:52, 31 August 2017 (UTC)

I have drafted some basic templates below. Matěj Suchánek (talk) 10:42, 1 September 2017 (UTC)
Thanks. Inspiring! --Daniel Mietchen (talk) 01:34, 2 September 2017 (UTC)

Total population living within 50km of epicenter of an earthquake at the time of the earthquake

--Daniel Mietchen (talk) 22:52, 31 August 2017 (UTC)

(Newer than 2007.)
SELECT ?item ?itemLabel ?epicenter ?time (SUM(?population) AS ?total_population) (SAMPLE(?place) AS ?sample_place)  WITH {
  SELECT * {
    ?item wdt:P31 wd:Q7944;
          wdt:P625 ?epicenter;
          wdt:P585 ?time .
    FILTER( YEAR( ?time ) > 2007 ) .
    SERVICE wikibase:around {
      ?place wdt:P625 ?location .
      bd:serviceParam wikibase:center ?epicenter .
      bd:serviceParam wikibase:radius 50 .
    } .
  }
} AS %places WHERE {
  INCLUDE %places .
  ?place p:P1082 [ ps:P1082 ?population; pq:P585 ?date ] .
  FILTER( ?time >= ?date ) .
  MINUS {
    ?place p:P1082/pq:P585 ?date2 .
    FILTER( ?time >= ?date2 && ?date2 > ?date ) .
  } .
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" .
    ?item rdfs:label ?itemLabel .
  } .
} GROUP BY ?item ?itemLabel ?epicenter ?time
Try it!
Matěj Suchánek (talk) 10:42, 1 September 2017 (UTC)
Thanks! That's a great start. Will play around with it a bit to see what else we can do on this basis. --Daniel Mietchen (talk) 01:18, 2 September 2017 (UTC)
@Matěj Suchánek: How can I add one more column to show earthquake magnitude on the moment magnitude scale (P2527)? I tried adding "wdt:P2527 ?magnitude;" but it gives me an error.--Micru (talk) 14:28, 8 September 2017 (UTC)
Change:
    ?item wdt:P31 wd:Q7944;
          wdt:P625 ?epicenter;
          wdt:P585 ?time .
to
    ?item wdt:P31 wd:Q7944;
          wdt:P625 ?epicenter;
          wdt:P585 ?time;
          wdt:P2527 ?magnitude .
You have probably only confused the punctuation. Matěj Suchánek (talk) 14:32, 8 September 2017 (UTC)
And extend GROUP BY ?item ?itemLabel ?epicenter ?time to GROUP BY ?item ?itemLabel ?epicenter ?time ?magnitude. Matěj Suchánek (talk) 14:33, 8 September 2017 (UTC)

Total population living within 50km of epicenter of a recent earthquake

Right after the initial shock, there is often very little information available other than the (estimated) epicenter and an (estimated) magnitude. So I'm trying to adapt the above query for 2017 Oaxaca and Chiapas Earthquake argentina (Q38982786):

SELECT ?item ?itemLabel ?epicenter ?time (SUM(?population) AS ?total_population) (SAMPLE(?place) AS ?sample_place)  WITH {
  SELECT * {
    VALUES ?item {wd:Q38982786}. # Q38982786 is the Wikidata identifier for the 2017 Chiapas earthquake; change this value if you want to run the query for another earthquake
    ?item wdt:P625 ?epicenter.
    OPTIONAL {?item wdt:P585 ?time .}
    SERVICE wikibase:around {
      ?place wdt:P625 ?location .
      bd:serviceParam wikibase:center ?epicenter .
      bd:serviceParam wikibase:radius 150 .
    } .
  }
} AS %places WHERE {
  INCLUDE %places .
  ?place p:P1082 [ ps:P1082 ?population; pq:P585 ?date ] .
  FILTER( ?time >= ?date ) .
  MINUS {
    ?place p:P1082/pq:P585 ?date2 .
    FILTER( ?time >= ?date2 && ?date2 > ?date ) .
  } .
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" .
    ?item rdfs:label ?itemLabel .
  } .
} GROUP BY ?item ?itemLabel ?epicenter ?time
Try it!

This does not yield any results, and the grouping does not make much sense anyway, since the item is predefined. I tried other arrangements but always ran into errors that I could not yet sort out. --Daniel Mietchen (talk) 13:15, 8 September 2017 (UTC)

Changed the range to 150km, and now there is at least the expected earthquake listed. Not sure about the population part yet. --Daniel Mietchen (talk) 13:21, 8 September 2017 (UTC)

I then went for listing the places directly:

SELECT ?place ?placeLabel ?population WITH {
  SELECT * {
    VALUES ?item {wd:Q38982786}. # Q38982786 is the Wikidata identifier for the 2017 Chiapas earthquake; change this value if you want to run the query for another earthquake
    ?item wdt:P625 ?epicenter.
    OPTIONAL {?item wdt:P585 ?time .}
    SERVICE wikibase:around {
      ?place wdt:P625 ?location .
      bd:serviceParam wikibase:center ?epicenter .
      bd:serviceParam wikibase:radius 150 .
    } .
  }
} AS %places WHERE {
  INCLUDE %places .
  ?place p:P1082 [ ps:P1082 ?population; pq:P585 ?date ] .
  FILTER( ?time >= ?date ) .
  MINUS {
    ?place p:P1082/pq:P585 ?date2 .
    FILTER( ?time >= ?date2 && ?date2 > ?date ) .
  } .
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" .
    ?place rdfs:label ?placeLabel .
  } .
} GROUP BY ?place ?placeLabel ?population
Try it!

In doing so, I did not see a way to do something like (SUM(?population) AS ?total_population) to get the total population of all the listed places. --Daniel Mietchen (talk) 13:31, 8 September 2017 (UTC)

Now let's try to plot those places on a map:

#defaultView:Map
SELECT ?place ?placeLabel ?population ?location WITH {
  SELECT * {
    VALUES ?item {wd:Q38982786}. # Q38982786 is the Wikidata identifier for the 2017 Chiapas earthquake; change this value if you want to run the query for another earthquake
    ?item wdt:P625 ?epicenter.
    OPTIONAL {?item wdt:P585 ?time .}
    SERVICE wikibase:around {
      ?place wdt:P625 ?location .
      bd:serviceParam wikibase:center ?epicenter .
      bd:serviceParam wikibase:radius 150 .
    } .
  }
} AS %places WHERE {
  INCLUDE %places .
  ?place p:P1082 [ ps:P1082 ?population; pq:P585 ?date ] .
  FILTER( ?time >= ?date ) .
  MINUS {
    ?place p:P1082/pq:P585 ?date2 .
    FILTER( ?time >= ?date2 && ?date2 > ?date ) .
  } .
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" .
    ?place rdfs:label ?placeLabel .
  } .
} GROUP BY ?place ?placeLabel ?population ?location
Try it!

This reveals that several places are in the area that do not yet pop up in the results. Will check why not. --Daniel Mietchen (talk) 13:45, 8 September 2017 (UTC)

One of the reasons why places don't show up here is that they may simply not have population statements, or that these may lack dates. I have added #Most widely used references for most recent population statements of places within 50km of epicenters of an earthquake to address that, so that we can use these sources to dig for population data for other places in the same area. --Daniel Mietchen (talk) 10:30, 9 September 2017 (UTC)

Total population living within 50km of epicenters of earthquakes with a moment magnitude of 7 or higher at the time of the earthquake

--Daniel Mietchen (talk) 22:52, 31 August 2017 (UTC)

SELECT ?item ?itemLabel ?epicenter ?time (SUM(?population) AS ?total_population) (SAMPLE(?place) AS ?sample_place) WITH {
  SELECT * {
    ?item wdt:P31 wd:Q7944;
          wdt:P625 ?epicenter;
          wdt:P585 ?time;
          wdt:P2527 ?moment .
    FILTER( ?moment >= 7 ) .
    SERVICE wikibase:around {
      ?place wdt:P625 ?location .
      bd:serviceParam wikibase:center ?epicenter .
      bd:serviceParam wikibase:radius 50 .
    } .
  }
} AS %places WHERE {
  INCLUDE %places .
  ?place p:P1082 [ ps:P1082 ?population; pq:P585 ?date ] .
  FILTER( ?time >= ?date ) .
  MINUS {
    ?place p:P1082/pq:P585 ?date2 .
    FILTER( ?time >= ?date2 && ?date2 > ?date ) .
  } .
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" .
    ?item rdfs:label ?itemLabel .
  } .
} GROUP BY ?item ?itemLabel ?epicenter ?time
Try it!
Matěj Suchánek (talk) 10:42, 1 September 2017 (UTC)
Thanks. I modified the query slightly (changed radius to 100km and magnitude to 7), which gave a total population of over 2 billion for 1997 Umbria and Marche earthquake (Q2609430), with Italy (Q38) as sample place.
SELECT ?item ?itemLabel ?epicenter ?time (SUM(?population) AS ?total_population) (SAMPLE(?place) AS ?sample_place) WITH {
  SELECT * {
    ?item wdt:P31 wd:Q7944;
          wdt:P625 ?epicenter;
          wdt:P585 ?time;
          wdt:P2527 ?moment .
    FILTER( ?moment >= 6 ) .
    SERVICE wikibase:around {
      ?place wdt:P625 ?location .
      bd:serviceParam wikibase:center ?epicenter .
      bd:serviceParam wikibase:radius 100 .
    } .
  }
} AS %places WHERE {
  INCLUDE %places .
  ?place p:P1082 [ ps:P1082 ?population; pq:P585 ?date ] .
  FILTER( ?time >= ?date ) .
  MINUS {
    ?place p:P1082/pq:P585 ?date2 .
    FILTER( ?time >= ?date2 && ?date2 > ?date ) .
  } .
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" .
    ?item rdfs:label ?itemLabel .
  } .
} GROUP BY ?item ?itemLabel ?epicenter ?time
Try it!
This means that something is wrong with query and/ or data, but I haven't yet figured out what this might be. Pointers most welcome. --Daniel Mietchen (talk) 01:22, 2 September 2017 (UTC)
On a closer look, the issue seems to affect other results as well, e.g. 2010 Haiti earthquake (Q43777) gives a total population of over 300 million, which is off by about one order of magnitude. --Daniel Mietchen (talk) 01:39, 2 September 2017 (UTC)
?place needs to be specified, now Italy (Q38) or Roman Empire (Q2277) are included, also in case of items with historic population all statements are counted.--Jklamo (talk) 01:41, 2 September 2017 (UTC)
There may be a problem with layers as well: city/county/province/state/region all being summed up.
--- Jura 06:07, 2 September 2017 (UTC)
Idea: MINUS { ?place wdt:P150 [] }. Matěj Suchánek (talk) 08:59, 2 September 2017 (UTC)
Maybe. Alternatively, identifying an administrative layer of sufficient granularity (and completeness) and then querying these only. The appropriate layer might vary from one country to the other.
--- Jura 10:01, 9 September 2017 (UTC)
Would be great to have a query for that as well, i.e. for identifying the useful level of granularity for the area(s) of interest. I just came here to add a query in a similar vein — #Most widely used references for most recent population statements of places within 50km of epicenters of an earthquake — below. --Daniel Mietchen (talk) 10:28, 9 September 2017 (UTC)
Maybe Wikidata:Property proposal/admin level could use a second attempt.
--- Jura 10:37, 9 September 2017 (UTC)
Another approach could be to collect population density from places within the epicenter and then use these to provide a minimum and maximum value for the area. Important: please read Project:General_disclaimer
--- Jura 08:42, 10 September 2017 (UTC)

Most widely used references for most recent population statements of places within 50km of epicenters of an earthquake

Population statements may be incomplete for places within a given area, but we usually have some. So the idea here is to check which sources were used most to back up population claims for places in the area, so as to get some potential starting points to find sources for population data for other places in the area. Of course, even if population statements are there, the dates may be missing, so they should be optional in the query. --Daniel Mietchen (talk) 10:28, 9 September 2017 (UTC)

Total current population living within 50km of epicenters of earthquakes with a moment magnitude of 7 or higher that occurred over the last 200 years

--Daniel Mietchen (talk) 22:52, 31 August 2017 (UTC)

SELECT ?item ?itemLabel ?epicenter ?time (SUM(?population) AS ?total_population) (SAMPLE(?place) AS ?sample_place) (SAMPLE(?placeLabel) AS ?sample_placeLabel)
WITH {
  SELECT * {
    ?item wdt:P31 wd:Q7944;
          wdt:P625 ?epicenter;
          wdt:P585 ?time .
    #?item wdt:P2527 ?moment FILTER( ?moment >= 7 ) .
    FILTER( YEAR( NOW() ) - YEAR( ?time ) <= 200 ) .
    SERVICE wikibase:around {
      ?place wdt:P625 ?location .
      bd:serviceParam wikibase:center ?epicenter .
      bd:serviceParam wikibase:radius 50 .
    } .
  }
} AS %places WHERE {
  INCLUDE %places .
  ?place wdt:P1082 ?population .
  MINUS { ?place wdt:P150 [] } .
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" .
    ?item rdfs:label ?itemLabel .
    ?place rdfs:label ?placeLabel .
  } .
} GROUP BY ?item ?itemLabel ?epicenter ?time ORDER BY DESC(?total_population)
Try it!
Matěj Suchánek (talk) 09:07, 2 September 2017 (UTC)

Total current population living within 50km of epicenters of earthquakes with a moment magnitude of 7 or higher predicted for the next 100 years

--Daniel Mietchen (talk) 22:52, 31 August 2017 (UTC)

Languages spoken within 50km of the epicenter of an earthquake at the time of the earthquake

--Daniel Mietchen (talk) 22:52, 31 August 2017 (UTC)

Monuments within 50km of the epicenter of an earthquake at the time of the earthquake

--Daniel Mietchen (talk) 22:52, 31 August 2017 (UTC)

Buildings within 50km of the epicenter of an earthquake at the time of the earthquake

--Daniel Mietchen (talk) 22:52, 31 August 2017 (UTC)

Population and altitude of settlements on the coasts surrounding off-shore earthquakes

--Daniel Mietchen (talk) 22:52, 31 August 2017 (UTC)

Dams and locks along a river and its tributaries

--Daniel Mietchen (talk) 22:52, 31 August 2017 (UTC)

Hospitals within a geoshape

This one came up last week at the Humanitarian Open StreetMap Summit: Given a geoshape of an administrative unit, what are all the hospitals/ schools/ other "points of interest" in there? I see we have geoshape (P3896) but could not find a query example for how this is actually to be used, e.g. to find hospitals within any of the currently ca. 400 geoshapes that we actually have on Commons:

The following query uses these:

  • Properties: geoshape (P3896)    
    1 SELECT DISTINCT ?geoshape ?item WHERE {
    2   ?item wdt:P3896 ?geoshape .
    3 }
    

--Daniel Mietchen (talk) 04:48, 21 September 2017 (UTC)

@Daniel Mietchen: never see used either but I'm eager to see it works too (there is still very few geoshape (P3896) but I plan to add all the French départements very soon). Maybe it worth a ticket on Phabricator (FYI @Lea Lacroix (WMDE):). Cdlt, VIGNERON (talk) 07:31, 21 September 2017 (UTC)
Hello,
Indeed, using and displaying geoshapes in the Query Service is not enabled yet. There is a ticket here. The first step (including geoshapes in the RDF export) so we hope to work on this soon :) Lea Lacroix (WMDE) (talk) 10:32, 21 September 2017 (UTC)

list of country calling codes (Q34768)

SELECT ?item ?internationale_Telefonvorwahl WHERE {
  ?item wdt:P31 wd:Q3624078.
  MINUS { ?item wdt:P31 wd:Q3024240. }
  OPTIONAL { ?item wdt:P474 ?internationale_Telefonvorwahl. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de". }
}

Try it!

I wanted to add this code to list of country calling codes (Q34768), but it is not possible because of the formatter URL (P1630) in Wikidata SPARQL query equivalent (P3921). Is there still a possibility to add it? Bigbossfarin (talk) 10:07, 20 September 2017 (UTC)

Hi Bigbossfarin,
Wikidata SPARQL query equivalent (P3921) is not an URL but the SPARQL code. So this Special:Diff/562508888 doesn't work, but Special:Diff/562539105 does work (it's limited to 100 results for obvious reason, but it works).
Cdlt, VIGNERON (talk) 10:17, 20 September 2017 (UTC)
Yes, but through the restriction in Wikidata SPARQL query equivalent (P3921) it is not possible to show country calling code (P474) in the list of the query. The list only shows which countries do have a P474.--Bigbossfarin (talk) 10:24, 20 September 2017 (UTC)
Oh, I see. No idea to fix it though. The best thing to do, is to ask the users who use this property on Property talk:P3921. Cdlt, VIGNERON (talk) 10:33, 20 September 2017 (UTC)
  • Wikidata SPARQL query equivalent (P3921) was originally designed to work for category items, but then somehow lists where included as well. The latter don’t work that nicely.
  • The values are meant to be used in Wikipedia templates on category pages (and maybe lists). The templates should include all non-defining information, such as the SELECT part, complementing further information (e.g. querying more properties such as dates of birth for humans), label service, etc. The URL formatter should only make it simple to get a quick overview whether the right items are found at all. Mind that the Wikidata web frontend is mainly a tool for editors, not for data users.
  • So you need another property if you want to present SPARQL code for the entire list.

MisterSynergy (talk)

dates with multiple calendars

I am looking for items that have dates of birth (or death) saved using different statements using different calendars. I tried:

SELECT ?item ?cal1 ?cal2
{
  ?item p:P569 ?date1 .
  ?item p:P569 ?date2 FILTER( ?date2 != ?date1 ) . # more than one statement
  ?date1 psv:P569 [ wikibase:timeCalendarModel ?cal1; ] .
  ?date2 psv:P569 [ wikibase:timeCalendarModel ?cal2; ] FILTER(?cal1 != ?cal2 ) .
}          
LIMIT 10

Try it!

but it did not work. Can someone fix this query? --Jarekt (talk) 14:46, 21 September 2017 (UTC)

You've got a typo there: wikibase:timeCalendarModewikibase:timeCalendarModel. Matěj Suchánek (talk) 14:58, 21 September 2017 (UTC)
@Jarekt: +1 (typo found using ctrl+space to auto-complete), and probably an other one ?cal1 = ?cal2 instead of ?cal1 != ?cal2. Cdlt, VIGNERON (talk) 15:06, 21 September 2017 (UTC)
Thank you both. It is working now. --Jarekt (talk) 15:12, 21 September 2017 (UTC)

Physicists from Africa

Hi. I am trying to build a list of Physicists whose country_of_citizenship (P27) is in Africa.

SELECT ?item ?itemLabel ?sex ?country_of_citizenship WHERE {
  ?item wdt:P106 wd:Q169470.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en", "de", "ar", "fr". }
  OPTIONAL { ?item wdt:P21 ?sex. }
  OPTIONAL { ?country_of_citizenship wdt:P27 {WHERE{?country_of_citizenship wdt:P27 wd:Q15. }}
}
LIMIT 10

Many thanks. —M@sssly 17:00, 21 September 2017 (UTC)

SELECT ?item ?itemLabel ?sexLabel ?country_of_citizenship ?country_of_citizenshipLabel WHERE {
  ?item wdt:P106 wd:Q169470;
        wdt:P27 ?country_of_citizenship .
  ?country_of_citizenship wdt:P30 wd:Q15 .
  OPTIONAL { ?item wdt:P21 ?sex } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,de,ar,fr" } .
}
Try it!
Matěj Suchánek (talk) 18:18, 21 September 2017 (UTC)

MINUS vs FILTER NOT EXISTS

Why most queries on this page use MINUS? I think that FILTER NOT EXISTS is more natural and faster. They are not equivalent in all cases. See eg:

--Vladimir Alexiev (talk) 12:19, 19 September 2017 (UTC)

@Vladimir Alexiev: good question. For me, I got used to use the MINUS (the first one I've discovered and it's shorter to write). Plus, I've never seen a real difference between the two syntaxes (always the same results and at roughly same speed). Could you provide a simple example where the results are not the same? Cdlt, VIGNERON (talk) 13:55, 19 September 2017 (UTC)
@VIGNERON: See https://www.w3.org/TR/sparql11-query/#neg-notexists-minus. A well-optimizing query evaluator may evaluate them the same. Otherwise, an early-placed NOT EXISTS is faster for "wide" result sets, because it cuts out solutions before you fetch all the extra attributes you need. Whereas MINUS evaluates the first resultset **in full**, and then subtracts solutions --Vladimir Alexiev (talk) 14:41, 19 September 2017 (UTC)
@Vladimir Alexiev: I've seen this one example many times but it's unclear to me. Do you have an example with real data (if possible Wikidata data, and if possible a simple example) where there is a difference? It would be very useful. Cdlt, VIGNERON (talk) 14:48, 19 September 2017 (UTC)
Personally, since I learnt SPARQL earlier than SPARQL, the word EXISTS implies to me that a subquery is invoked. And subqueries in SQL are known to be slow...
Another possiblity that deals with negation is OPTIONAL + BOUND. Matěj Suchánek (talk) 14:21, 19 September 2017 (UTC)
@Matěj Suchánek: NOT EXISTS is the SPARQL 1.1 standardization of OPTIONAL... FILTER(!BOUND()) --Vladimir Alexiev (talk) 14:41, 19 September 2017 (UTC)
Nevertheless, sometimes the OPTIONAL construction runs faster/slower.
It really is a black art, we could do with some better pages on query optimisation. Jheald (talk) 20:32, 19 September 2017 (UTC)
I originally tended use to use FILTER NOT EXISTS, but my experience is that MINUS seems to run faster on WDQS. But perhaps my results sets haven't been 'wide'. Jheald (talk) 20:30, 19 September 2017 (UTC)
FILTER NOT EXISTS will timeout in WDQS on common queries, so no reason to suggest it. d1g (talk) 16:48, 23 September 2017 (UTC)

Occupations of people born in Utah

I'm trying to teach myself how to use SPARQL and I thought it would be fun to see the occupations of people born in Utah. I was able to make a query that gives results, but it doesn't include persons who were born in a specific city in Utah. What would be the best way to include them? thanks, Rachel Helps (BYU) (talk) 20:54, 21 September 2017 (UTC)

#occupations of people born in Utah
SELECT ?person ?personLabel ?occupationLabel

WHERE {
  ?person wdt:P19 wd:Q829.
  ?person wdt:P106 ?occupation.

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
#occupations of people born in Utah
SELECT ?person ?personLabel ?occupationLabel

WHERE {
  {?person wdt:P19 wd:Q829.}
  UNION
  {?person wdt:P19 ?placeofbirth.
   ?placeofbirth wdt:P131+ wd:Q829.}        
  ?person wdt:P106 ?occupation.

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

Selects all persons born in Utah or in a place within Utah. --Tkarcher (talk) 21:01, 21 September 2017 (UTC)

Oh, neat, and thank you! I didn't realize that "located in the administrative territorial entity" was a possible statement. I was just spot-checking with Q4725969, who was born in Salt Lake City. For some reason she didn't show up in the query results. Salt Lake City is in the administrative territorial entity of Salt Lake County, and Salt Lake County is in the administrative territorial entity of Utah. Does the query automatically include sub-locations? Just trying to learn how this works. Rachel Helps (BYU) (talk) 21:18, 21 September 2017 (UTC)
Yes, this query does include sub-locations, due to the plus sign after wdt:P131. See wikibooks:SPARQL/Property_paths for details. --Tkarcher (talk) 21:56, 21 September 2017 (UTC)

Combine two queries to match paintings with painters

I have two collections of paintings that don't include the full name of the painter. This gives the paintings missing the creator:

SELECT DISTINCT ?item ?description WHERE {
  ?item wdt:P31 wd:Q3305213 .
  { ?item wdt:P195 wd:Q18600731 } UNION { ?item wdt:P195 wd:Q28045665 } .
  MINUS { ?item p:P170 [] } .
  ?item schema:description ?description .
  FILTER(LANG(?description) = "en") 
  }

Try it!

And this gives the creators in a suitable format:

SELECT DISTINCT ?painter ?painterLabel ?description WHERE {
  ?painter wdt:P106 wd:Q1028181 .
  {?painter rdfs:label ?painterLabel } UNION { ?item skos:altLabel ?painterLabel } .
  BIND(REPLACE(?painterLabel, '^(.)[^\\s]+\\s(.+)$', "painting by $1. $2") AS ?description) .
  FILTER(REGEX(?description, "painting by .+"))
  } LIMIT 1000

Try it!

Any idea how to combine the two in a way that doesn't time out? Suggestions should be something this:

Matěj Suchánek helped on irc and after some fiddling around I got:

SELECT DISTINCT * WITH {
  SELECT DISTINCT ?item (STR(?description) AS ?description1) WHERE {
    ?item wdt:P31 wd:Q3305213 .
    { ?item wdt:P195 wd:Q28045665 } # UNION { ?item wdt:P195 wd:Q18600731 }.
    MINUS { ?item p:P170 [] } .
    ?item schema:description ?description FILTER( LANG( ?description ) = 'en' ) .
  } 
} AS %paintings WITH {
  SELECT DISTINCT ?painter (?description AS ?description2) WHERE {
    ?painter wdt:P106 wd:Q1028181 .
    ?painting wdt:P170 ?painter; wdt:P195/wdt:P17 wd:Q55 .
    {
      ?painter rdfs:label ?painterLabel .
      FILTER( LANG( ?painterLabel ) = 'en' ) .
    } UNION {
      ?painter skos:altLabel ?painterLabel .
      FILTER( LANG( ?painterLabel ) = 'en' ) .
    } .
    BIND( REPLACE( STR( ?painterLabel ), '^(.)[^\\s]+\\s(.+)$', 'painting by $1. $2' ) AS ?description ) .
    FILTER( REGEX( ?description, '^painting by .+' ) ) .
  } 
} AS %painters WHERE {
  INCLUDE %paintings .
  INCLUDE %painters .
  FILTER( ?description1 = ?description2 ) .
} LIMIT 150

Try it!

Suggestions for improvements are welcome! Multichill (talk) 11:11, 23 September 2017 (UTC)

I raised the limit to 150. Also, it seems that comparing plain strings is faster than comparing language texts. Matěj Suchánek (talk) 11:37, 23 September 2017 (UTC)

Fehlende Artikel in de.wp mit existenten Artikeln in anderen Sprachen (Missing dewiki articles with articles in other Wikipedias)

Wie finde ich so etwas, begrenzt auf eine Kategorie? Bin des SPARQL nicht mächtig. Grüße, --Ghilt (talk) 13:56, 23 September 2017 (UTC)

Sorry, in English: how do i find articles in other wikipedias in a certain category that are non-existant in de.wp. I'm not SPARQL proficient, cheers, --Ghilt (talk) 14:00, 23 September 2017 (UTC)

(moved from Wikidata talk:SPARQL query service/Query Helper)

Hi Ghilt,
It depend what you call a category (is it the narrow sense Wikipedia category or a wider sense of type of things, the first is still possible but a bit more complicated). Does this query answer you question:
SELECT DISTINCT ?item ?itemLabel ?page1 WHERE {
  ?item wdt:P131 wd:Q647 . #things located in Rennes
  ?page1 schema:about ?item .
  ?page2 schema:about ?item .
  ?page1 schema:isPartOf <https://en.wikipedia.org/> . #with articles on en.wikipedia.org
  FILTER NOT EXISTS { ?page2 schema:isPartOf <https://de.wikipedia.org/> .} #without article on de.wikipedia.org
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
I can improve and refine this query if needed (caution, this kind of query is quite heavy and can take a long time to process). Cdlt, VIGNERON (talk) 15:03, 23 September 2017 (UTC)
PS: it's weird, something is wrong and the query doesn't work as expected. @Matěj Suchánek, Pasleim, Jheald: any idea? Cdlt, VIGNERON (talk) 15:12, 23 September 2017 (UTC)
You need to move the ?page2 schema:about ?item into the filter, eg like this:
SELECT DISTINCT ?item ?itemLabel ?page1 WHERE {
  ?item wdt:P131 wd:Q647 . #things located in Rennes
  ?page1 schema:about ?item .  
  ?page1 schema:isPartOf <https://en.wikipedia.org/> . #with articles on en.wikipedia.org
  MINUS {                                              #without article on de.wikipedia.org
    ?page2 schema:about ?item .
    ?page2 schema:isPartOf <https://de.wikipedia.org/> .
  } 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
Now ?item is the common variable between the two blocks, so items will be excluded if they are in the second block. Before only ?page2 was excluded. That would still allow ?items if a ?page2 could be found for them that was not German (even if they had another page that was German). Moving the ?page2 schema:about ?item line fixes this. Jheald (talk) 15:37, 23 September 2017 (UTC)
Jheald was faster than me, I wanted to suggest the same. Matěj Suchánek (talk) 15:42, 23 September 2017 (UTC)
@Ghilt: in case you are looking for a category in the Wikipedia sense, you could use Petscan without any SPARQL code. Just scan for the category in the other wiki (with or without subcategories), and additionally add in the “Other sources” tab: wikidata=dewiki, combination=categories NOT wikidata. Example: German male rowers at enwiki, but not dewiki. This is still not very performant, but works fine for categories in the other wiki with a couple of thousand entries. —MisterSynergy (talk) 16:12, 23 September 2017 (UTC)

Thanks a lot for the help, and for so many replies on a saturday! Indeed, i meant a wikipedia category, in my case 'Biochemistry' (and possibly its subcategories). The suggested Pet-Scan worked fine ([4]). I had already tried a Pet-Scan (PSID 1289979) with the category Biochemistry in the Categories-Tab and a filter from the Wikidata-Tab: Has none of these site links with dewiki which retrieved 115.881 results that were mainly non-biochemical articles. What is the difference in the two Pet-Scans and why did my approach not work?

And a short notice on the side: not being overly stupid, i still must admit that Query Helper is far from intuitive for non-coders. The suggested query helper searches are of limited understandability for me. And if i see the above correctly, even the Pros have difficulties with the syntax. WikiData really gives me that feeling of 'stay out'. And that limits the use of Wikidata for me to the level that it merely provides the interwiki-links, which used to be managed by bots (EmausBot) locally in the different wikipedia projects. The rest works for me by Pet-Scan. So, is it redundant? Sorry for the rant, --Ghilt (talk) 16:38, 23 September 2017 (UTC)

Your Petscan approach works for me as well, even much faster than my own suggestion. It also works for your query: [5]. Maybe you should check all the options again … ;-)
The Petscan tool has a lot of useful options, but maybe it is sometimes too much. I typically improve my queries until they eventually yield the desired results. The tool itself is not part of Wikidata, but coded by Magnus Manske. Nevertheless does it provide access to Wikimedia data that no other tools can provide—it is particularly powerful when it comes to cross-wiki querying, as in your case. The Wikidata Query Service on the other hand has not much information about Wikipedia content, apart from page titles. It does not know how articles are categorized. However, for Wikidata-centric queries it is the most powerful tool.
Regarding SPARQL: I have completely taught myself how to use it, and feel meanwhile quite comfortable with most things it can do. At the time I did that I was already very familiar to Wikidata, and I had some pre-existing knowledge about querying relational databases (which works differently). But I guess the self-teaching approach only works if one uses SPARQL regularly. For occasional users we have this page which is well-visited by SPARQL experts, and invite everybody to ask for help or complete queries here. I don’t think that the querying process can be simplified much, and I don’t like the Query Helper as well…
MisterSynergy (talk) 16:58, 23 September 2017 (UTC)
I did, but it only works without subcategories, when i enter a value other than '0' both of our approaches don't work. By the way, i had already asked the 'technical wishes project' of WMDE for something much more wysiwyg for queries. I already knew Magnus made Pet-Scan, hence my redundancy question regarding wikidata. Cheers, --Ghilt (talk) 17:05, 23 September 2017 (UTC)
Perhaps the en:Category:Biochemistry category has too many members, if you use a larger level of subcategories? If you skip the dewiki filtering, it has ~830.000 articles at 12 levels of subcategories. This indicates some serious categorization flaws at enwiki, because I doubt that 1/7th of enwiki is about biochemistry… At least 1, 2, or 3 levels work for me and yield useful results even with filtering. —MisterSynergy (talk) 17:17, 23 September 2017 (UTC)
Thanks again! Funny, depth = 3 or 4 work, while 5 and 9 didn't... --Ghilt (talk) 17:31, 23 September 2017 (UTC)

List of airports in a given administrative région (or a wikipedia category ?)

Hello everyone, I'd like to export the coordinates, names and aliases of any airports in a given region or category. For instance Region : Nouvelle-Aquitaine or catégorie on french wikipédia = "Aéroport en Nouvelle-Aquitaine" ? Thank you for the help! --Bouzinac (talk) 20:01, 22 September 2017 (UTC) I tried this one but not working:

PREFIX psv: <http://www.wikidata.org/prop/statement/value/>
PREFIX geo: <http://www.opengis.net/ont/geosparql#>

#defaultView:Map
SELECT ?item ?coords_ ?itemLabel WHERE {
  ?item wdt:P131 wd:Q18678082.
  ?item (p:P625/psv:P625) ?coords.
  ?coords wikibase:geoLatitude ?lat.
  ?coords wikibase:geoLongitude ?lon.
  BIND(STRDT(CONCAT("Point(", IF(?lon < 0, STR(?lon + 360), STR(?lon)), " ", STR(?lat), ")"), geo:wktLiteral) AS ?coords_)
  ?item wdt:P31 wd:Q1248784.
}

Try it!

#defaultView:Map
SELECT ?item ?coords ?itemLabel WHERE {
  ?item wdt:P131+ wd:Q18678082.
  ?item wdt:P625 ?coords.
  ?item wdt:P31 wd:Q1248784.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }  
}
Try it! --Pasleim (talk) 20:38, 22 September 2017 (UTC)
Bouzinac: is this query good enough for you? Technically, it is possible to query from the Wikipedia category (see mw:Wikidata query service/User Manual#Mediawiki API) but I don't know the Mediawiki API well enough for that… Cdlt, VIGNERON (talk) 15:20, 23 September 2017 (UTC)
@Bouzinac: You can also do this with OpenStreetMap data, using Overpass turbo, and including Wikidata IDs where available, which will allow you to make a comparison, and fill in any gaps, on either side. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 15:36, 23 September 2017 (UTC)
@Bouzinac, Pigsonthewing: here is the Overpass query (but for all the South-West of France and not only Nouvelle-Aquitaine ; and sadly, OpenStreetMap doesn't make the distinction between airport and aerodrome). For the moment, there is not link from OSM to Wikidata, but if there was, I could have done a OSM+Wikidata Query too (see this query comparing name of churches in Ille-et-Vilaine). Cdlt, VIGNERON (talk) 16:35, 23 September 2017 (UTC)
Hi Pasleim (talkcontribslogs), VIGNERON (talkcontribslogs) it's a nice start. Can we add the filter date = the airport must either have no "end date P582" or this end date is in the future. Thanks!--Bouzinac (talk) 16:04, 25 September 2017 (UTC)

Wikimedia permanent duplicate item (Q21286738) that have country (P17)

Can you? --Liuxinyu970226 (talk) 12:46, 25 September 2017 (UTC)

Sure :)
SELECT ?item ?itemLabel ?countryLabel WHERE {
  ?item wdt:P31/wdt:P279* wd:Q21286738;
        wdt:P17 ?country.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --TweetsFactsAndQueries (talk) 13:34, 25 September 2017 (UTC)

Directors of the Scuola Normale Superiore

Hi! I need a query for all items with position held (P39) Director of the Scuola Normale Superiore (Q41210386), ordered through the qualifier series ordinal (P1545); display: it label, date of birth (P569) (only the year), date of death (P570) (only the year, if exists), qualifier start time (P580) (only the year), qualifier end time (P582) (only the year), Treccani ID (P3365) (if exists). Thank you! --Epìdosis 06:45, 29 September 2017 (UTC)

SELECT ?item ?itemLabel ?ordinal ?birthyear ?deadyear ?startyear ?endyear ?trec
WHERE 
{
  ?item wdt:P39 wd:Q41210386.
  ?item p:P39 ?position .
  ?position ps:P39 wd:Q41210386 .
  ?position pq:P1545 ?ordinal .  
  OPTIONAL { ?item wdt:P569 ?db. }
  OPTIONAL { ?item wdt:P570 ?dd. }
  OPTIONAL { ?position pq:P580 ?st. }
  OPTIONAL { ?position pq:P582 ?en. }
  OPTIONAL { ?item wdt:P3365 ?trec. }
  BIND(str(YEAR(?db)) AS ?birthyear)
  BIND(str(YEAR(?dd)) AS ?deadyear)
  BIND(str(YEAR(?st)) AS ?startyear)
  BIND(str(YEAR(?en)) AS ?endyear)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it". }
}
ORDER BY ASC(xsd:integer(?ordinal))
Try it! --Jklamo (talk) 11:24, 29 September 2017 (UTC)