Wikidata:Request a query/Archive/2018/05

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

Check whether a given list of element IDs fullfills certain criteria and return list of matching items

Hello, for a project of mine I'm trying to filter items of a given list of ${\displaystyle n}$ items (e.g. `{Q14944328,Q2013}`) so that the final list only contains items which fullfill a certain condition (e.g. `wdt:P31/wdt:P279* wd:Q19729799`). How would I be able to do so? Thanks in advance for any pointers --Naseweis520 (talk) 23:02, 30 April 2018 (UTC)

@Naseweis520: If I understand your question, something like this (which right now finds nothing, since the two values you gave are not instances/subclass of the third item you gave. Come back with more questions, if needbe:
```SELECT ?item ?itemLabel WHERE {
VALUES ?item {wd:Q14944328 wd:Q2013}        #this is the given list. Note that the items start with wd: ... add as many as you want
?item wdt:P31/wdt:P279* wd:Q19729799 .     #this requires the item to be an instance of or subclass of the Q19729799 item.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }  #this gets labels for values
}
```
Try it! hth --Tagishsimon (talk) 23:10, 30 April 2018 (UTC)
@Tagishsimon: Thank you very very much. This is exactly what I needed. --Naseweis520 (talk) 18:19, 1 May 2018 (UTC)

Request EVERY items with a specific description

Hi,
I try to get every element with the word "Treaty" in the label :

```SELECT ?item ?label
WHERE
{
?item rdfs:label ?label.
FILTER (lang(?label) = "en").
FILTER(CONTAINS(?label, "Treaty")).
}
```

Obviously, the timeout "wall" rise, even if I put a LIMIT at the end. Someone know if this kind of request is possible ? Simon Villeneuve (talk) 15:21, 1 May 2018 (UTC)

Alphabets sorted by number of letters

We do not seem to indicate directly how many letter (Q9788) are in an alphabet (Q9779), so getting that number and sorting across alphabets is a bit more involved already, and then there are steps like jamo (Q41799425) that complicate matters even further. --Daniel Mietchen (talk) 23:16, 1 May 2018 (UTC)

Query without duplicates

I want all items with P1087, but without P3314. As far as I can see, his query works in principle:

```SELECT DISTINCT ?item ?itemLabel
WHERE
{
?item wdt:P1087 ?elo.
MINUS { ?item wdt:P3314 [] } .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }

}
```

However, it contains a lot of duplicate entries. How are the duplicates removed? Steak (talk) 15:01, 2 May 2018 (UTC)

@Steak: you need to look for DISTINCT values - query above amended. --Tagishsimon (talk) 16:08, 2 May 2018 (UTC)
Thank you! Steak (talk) 17:16, 2 May 2018 (UTC)

Show Wikidata properties that are external identifiers and have a regex

Wikidata property - external ID - list

This comes close, but also lists results from item name space:

```SELECT ?property ?propertyLabel ?regex WHERE {
?property wdt:P31/wdt:P279* wd:Q19847637 .
?property wdt:P1793 ?regex .
SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} ORDER BY ?propertyLabel
```

Any idea why ISNI comes up twice? The two result rows look identical to me.

```SELECT ?property ?propertyLabel ?regex WHERE {
?property wikibase:propertyType wikibase:ExternalId; wdt:P1793 ?regex .
SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} ORDER BY ?propertyLabel
```
Try it!MisterSynergy (talk) 21:23, 27 April 2018 (UTC)

Wikidata property - external ID - group and count

A part from a pure list, grouping would be nice, I fail to display the propertyLabels

```SELECT ?regex (COUNT(?regex) AS ?count)
(GROUP_CONCAT(DISTINCT ?propertyLabel) AS ?propertyLabels)
WHERE {
?property wdt:P31/wdt:P279* wd:Q19847637 .
?property wdt:P1793 ?regex .
SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
GROUP BY ?regex
ORDER BY DESC (?count) ?regex ?propertyLabel
```

85.179.115.121 21:04, 27 April 2018 (UTC)

```SELECT ?regex (COUNT(?regex) AS ?count)
(GROUP_CONCAT(DISTINCT ?propertyLabel) AS ?propertyLabels)
WHERE {
?property wikibase:propertyType wikibase:ExternalId;
wdt:P1793 ?regex .
SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
GROUP BY ?regex
ORDER BY DESC (?count) ?regex ?propertyLabel
```
Try it!

MisterSynergy, thanks for your help above, now improved here with your suggestion. Any idea about displaying the propertyLabels? 85.179.115.121 21:58, 27 April 2018 (UTC)

```SELECT ?regex (COUNT(?regex) AS ?count) (GROUP_CONCAT(?propertyLabel; separator=', ') AS ?propertyLabels) WHERE {
?property wikibase:propertyType wikibase:ExternalId; wdt:P1793 ?regex .
OPTIONAL { ?property rdfs:label ?propertyLabel . FILTER(LANG(?propertyLabel) = 'en') }
} GROUP BY ?regex ORDER BY DESC(?count)
```
Try it! You cannot further process labels that were collected with the label service (as you want to do by group concatenation). `rdfs:label` and a subsequent lang filter is suitable for that purpose. —MisterSynergy (talk) 22:05, 27 April 2018 (UTC)

MisterSynergy would it be possible to have a link to each property and as link label the propertyLabel? 77.179.61.171 21:40, 4 May 2018 (UTC)

To my knowledge this is not possible in the Query Service GUI. You would have to query with a script, reshape the result to the desired format and output it e.g. by bot to a wikipage. Probably even ListeriaBot is not able to do this … —MisterSynergy (talk) 21:57, 4 May 2018 (UTC)

Indian National Highways below 100

Hi, I am looking for Indian National highways below 100

```SELECT ?id ?idLabel ?wikilink
(concat('[[', ?idLabel, ']]') as ?title)
(REPLACE( ?idLabel , "National Highway ", "" ) AS ?high_no)
WHERE
{
?id wdt:P31 wd:Q34442 . # is a road
?id wdt:P17 wd:Q668 . # in India
?id wdt:P16 wd:Q1967342 . # in India
SERVICE wikibase:label { bd:serviceParam wikibase:language 'en'}
}
```

--Naveenpf (talk) 06:14, 3 May 2018 (UTC)

@Naveenpf: Assuming the numbers in the English labels are the correct numbers. (For some roads, i.e. National Highway 44 (Q643144) the numbers in different language labels seem to disagree)
```SELECT ?id ?idLabel ?wikilink
(concat('[[', ?idLabel, ']]') as ?title)
(REPLACE( ?idLabel , "National Highway ", "" ) AS ?high_no)
?number
WHERE
{
?id wdt:P16 wd:Q1967342 . # National Higway (India)
?id rdfs:label ?label . FILTER(lang(?label)='en')
#Note: Different languages seem to have different numbers in the labels for some roads, e.g. for Q643144
BIND(xsd:integer(REPLACE(?label,'\\D','')) AS ?number)
FILTER(?number<100)
SERVICE wikibase:label { bd:serviceParam wikibase:language 'en'}
}
ORDER BY ?number
```
Try it!
--Larske (talk) 07:34, 3 May 2018 (UTC)

Chess elo ratings awarded after player's death

Hi, I want to give deprecated rank to all chess elo ratings that were awarded by FIDE after player's death. Can you please help me with query returning these statements? Thank you. Wesalius (talk) 14:01, 5 May 2018 (UTC)

@Wesalius: This might be along the road you want to travel ... shows (I think) all non-deprecated ELO ratings given post mortem. --Tagishsimon (talk) 14:25, 5 May 2018 (UTC)
```SELECT ?item ?itemLabel ?rating ?date_given ?DoD
WHERE
{
?item p:P1087 ?elo.                         #has an elo rating property
?elo ps:P1087 ?rating; pq:P585 ?date_given. #property statement has a qualifier of point in time
?item wdt:P570 ?DoD.                        #has a date of death
filter (?DoD<?date_given)
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?itemLabel ?date_given
```
Try it!
and more concisely, just the players affected:
```SELECT distinct ?item ?itemLabel ?DoD
WHERE
{
?item p:P1087 ?elo.                         #has an elo rating property
?elo ps:P1087 ?rating; pq:P585 ?date_given. #property statement has a qualifier of point in time
?item wdt:P570 ?DoD.                        #has a date of death
filter (?DoD<?date_given)
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?itemLabel
```
Try it! - hth --Tagishsimon (talk) 14:29, 5 May 2018 (UTC)

Thank you, sir! Wesalius (talk) 05:33, 6 May 2018 (UTC)

Ten most used references for date of birth

I want to have a list of the ten most used references for date of birth (P569) by number. Can someone help?. But excluding imported from Wikipedias. Breg Pmt (talk) 17:15, 6 May 2018 (UTC)

@Pmt:
```SELECT ?count ?property ?propertyLabel ?value ?valueLabel
WITH {
SELECT ?reference (COUNT(*) AS ?count) WHERE {
?entity p:P569 ?statement.
?statement prov:wasDerivedFrom ?reference.
}
GROUP BY ?reference
ORDER BY DESC(?count)
LIMIT 100
} AS %commonReferences
WITH {
SELECT * WHERE {
INCLUDE %commonReferences.
MINUS { ?reference pr:P143/wdt:P31 wd:Q10876391. }
}
ORDER BY DESC(?count)
LIMIT 10
} AS %commonRealReferences
WHERE {
INCLUDE %commonRealReferences.
?reference ?pr ?value.
?property wikibase:reference ?pr.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?count) ASC(?propertyLabel)
```
Try it! – and since the query takes a long time, here are the current results:
– each used between 5000 and 10000 times. --TweetsFactsAndQueries (talk) 20:48, 6 May 2018 (UTC)

What is the query behind "Search Wikidata" ?

The one on top of each wikidata page : it seems to launch an approximate search on every field/property of an object.

It's quite efficient, to say the least. And exactly what I am looking for.

Anyone know the recipe ?

Thanks !

Atopus (talk) 15:21, 3 May 2018 (UTC)

@Atopus: something called CirrusSearch, the tech of which is described here. --Tagishsimon (talk) 14:43, 3 May 2018 (UTC)
@Tagishsimon: Great ! Thank you very much for the hint :) It looks it can easily be used through the mediawiki api. Do you know if the same tool is used for the page behind the "containing" link, which gives even more results ? I cannot find an api endpoint for this one... Atopus (talk) 10:29, 7 May 2018 (UTC)
AFAIK they use the same backend. You may get the best answers at WD:DEV, though. Matěj Suchánek (talk) 18:32, 7 May 2018 (UTC)

Show Wikidata properties that are external identifiers and are used on instances of Q5

Something is wrong, no idea what.

```SELECT ?property ?propertyLabel WHERE {
?property wikibase:propertyType wikibase:ExternalId .
?item wdt:P31 wd:Q5 .
?item ?property [].
SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} ORDER BY ?propertyLabel
```

And if possible show a count for usage. 77.180.23.13 02:39, 7 May 2018 (UTC)

You need to use the claim predicate (?p, `p:P…`) instead of the property itself (?property, `wd:P…`):
```SELECT DISTINCT ?property WHERE {
?property wikibase:propertyType wikibase:ExternalId .
?property wikibase:claim ?p .
?item wdt:P31 wd:Q5 .
?item ?p [] .
}
```
Try it! – however, that query just results in a timeout: I think there are too many human items on Wikidata to do this. --TweetsFactsAndQueries (talk) 09:12, 7 May 2018 (UTC)

Growth of Wikimedia chapters and user groups over time

Hi

I'd like a query that showed a map with timeline to show the growth of Wikimedia chapters and user groups over time. Each chapter or user groups that is present should colour that section of the map in (I realise this excludes the thematic organisations). This would basically be an animated map of the graph that appears in this page.

I want this for two reasons:

1. I'm running a Mozilla Sprint project to encourage people to add information to Wikidata about the open movement see here and would really like to use this as an example.
2. I think a non timeline version could be used as a useful explorer for the Meta page on Chapters and User groups. If it could look like the maps on Wikidata:Wikidata_in_Wikimedia_projects#Maps_and_graphs that would be super amazing, e.g clicking on an area brought up website, contact info etc.

To make sure the query is correct I guess there should be additional queries for number of chapters and usergroups, if a country or region is stated on their item etc. I'm very happy to do the legwork to fill in gaps.

Thanks

--John Cummings (talk) 15:00, 6 May 2018 (UTC)

@John Cummings: Hm, I don’t think that’s possible as you describe it… as far as I’m aware we don’t directly support animated maps. A non-animated map with different layers for different years would in theory be possible, but in practice I don’t think we don’t have enough geoshape (P3896) statements on countries yet. So far, I think we can only create a simple timeline:
```#defaultView:Timeline
SELECT ?org ?orgLabel ?inception (COALESCE(?dissolved, ?endTime) AS ?end) WHERE {
?org p:P31 ?statement;
wdt:P571 ?inception.
?statement ps:P31/wdt:P279* wd:Q5926733.
OPTIONAL { ?statement pq:P582 ?endTime. }
OPTIONAL { ?org wdt:P576 ?dissolved. }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
```
Try it! – but note that many user groups and even chapters are missing their inception (P571): query --TweetsFactsAndQueries (talk) 20:59, 6 May 2018 (UTC)

@TweetsFactsAndQueries:, this is great, thanks. Is it possible to just have a static map like the one on the chapters and user groups page but with details pop up when you hover over an area? --John Cummings (talk) 22:16, 6 May 2018 (UTC)

@John Cummings: that would be possible if we had geoshapes for countries, but currently the only three countries (values of country (P17) statements) with a geoshape are Austrian Empire (Q131964), Qajar dynasty (Q189326) and Prague (Q1085) (query). --TweetsFactsAndQueries (talk) 23:27, 6 May 2018 (UTC)
Thanks for the explanation @TweetsFactsAndQueries:, --John Cummings (talk) 07:29, 7 May 2018 (UTC)
@TweetsFactsAndQueries: Prague (Q1085) is a country? I must investigate this... Matěj Suchánek (talk) 19:14, 7 May 2018 (UTC)
@Matěj Suchánek: I couldn’t even find the statement with a query anymore because you already fixed it :( --TweetsFactsAndQueries (talk) 19:25, 7 May 2018 (UTC)

Get de.wiki article name

In short, I'm wanting to extract the de.wiki article name from an item, and compare it with a string. If we imagine I'm looking for an item which has a de.wiki sitelink named "Monkey Island (England)", how do I achieve that? Some broken SPRQL, below, as a starter for 10. (1. in the borked SPARQL, why are the binds failing? 2. and I appreciate that even if they worked, I'd be extracting a string with underscores in it, rather than space ... presumably we also store the de.wiki name, without underscores, as we see it displayed in the wikipedia links section of the item record. 3. VALUEs ?itemLabel is commented out as I was working on the stringslicing problem.). thx Tagishsimon --16:27, 6 May 2018 (UTC)

```SELECT ?item ?itemLabel ?geni ?article ?out ?length
WHERE
{
#  VALUES ?itemLabel {"Despotović (Familienname)" "Monkey Island (England)" "Iwajlo Marinow"}
VALUES ?item {wd:Q6900750}
?item wdt:P31 [].
schema:isPartOf <https://de.wikipedia.org/> .
bind(xsd:integer(strlen(?article)) as ?length)
bind(substr(?article,31, ?length-1) as ?out)
}
```
```SELECT ?item ?sitelink ?article
WHERE
{
"Despotović (Familienname)"@de
"Monkey Island (England)"@de
"Iwajlo Marinow"@de
} .
schema:isPartOf <https://de.wikipedia.org/> .
}
```
Try it!
Matěj Suchánek (talk) 18:44, 7 May 2018 (UTC)
Thank you, Matěj Suchánek. Much obliged. --Tagishsimon (talk) 19:02, 7 May 2018 (UTC)

Books written in a language the author, supposedly, can't write

Hi, I'm stuck with this. I want a list of books written in X by an author whose native language (P103) or his languages spoken, written or signed (P1412) does not include X or any dialect of X —I'm using subclass of (P279) for this. I got some results but my attempt is not working properly and —even worse— it looks ugly. It keeps showing, for example, Q20103242, written in Spanish (Q1321) by Pompeu Fabra (Q379785), who has Spanish (Q1321) among his languages spoken, written or signed (P1412).

```#Books or suclasses of books (NOT editions) written in a language the author, supposedly, can't write
SELECT DISTINCT ?work ?workLabel ?worklangLabel ?authorLabel ?knownlanguagesLabel ?nativelanguageLabel WHERE {

#Work is an instance or any subclass of book
?work wdt:P31/wdt:P279* wd:Q571 .

#Work is an instance of whatever
?work wdt:P31 ?what .

#BUT is not an instance of a version, translation or edition
FILTER ( ?what != wd:Q3331189 )

#Show inception date of the work if available
OPTIONAL { ?work wdt:P571 ?date . BIND(YEAR( ?date ) as ?year) }

#Get the language of the work
?work wdt:P407 ?worklang .

#AND the author
?work wdt:P50 ?author .

# Be sure author has almost one value for native language OR language known, written, etc.
{ ?author wdt:P1412 ?knownlanguages . } UNION { ?author wdt:P103 ?nativelanguage . }

FILTER NOT EXISTS {
# Author doesn't know ?worklang
{ ?author wdt:P1412 ?worklang . }

# OR ?worklang is his native language
UNION { ?author wdt:P103 ?worklang . }

# OR ?worklang is not a subclass of his known languages
UNION  { ?author wdt:P1412 ?knownlanguages . ?knownlanguages wdt:P279* ?worklang . }

# OR ?worklang is not a subclass of his native language
UNION  { ?author wdt:P103 ?nativelanguage . ?nativelanguage wdt:P279* ?worklang . }
}

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

}
```

SPARQL is not among my languages spoken, written or signed (P1412), definitely. Can someone help me? Thanks in advance. --Wikidelo (talk) 17:47, 10 May 2018 (UTC)

The problem with Pompeu Fabra (Q379785) is that he has Catalan (Q7026) set as the preferred language. I worked around that in my query and but I cannot guarantee it's 100% correct:
```#Books or suclasses of books (NOT editions) written in a language the author, supposedly, can't write
SELECT DISTINCT ?work ?workLabel ?year ?author ?authorLabel ?languageLabel WHERE {

#Work is an instance or any subclass of book
?work wdt:P31/wdt:P279* wd:Q571 .

#AND is not an instance of a version, translation or edition
MINUS { ?work wdt:P31 wd:Q3331189 } .

#Show inception date of the work if available
OPTIONAL { ?work wdt:P571 ?date . BIND( YEAR( ?date ) as ?year ) } .

#Get the language of the work
?work wdt:P407 ?language .

#AND the author
?work wdt:P50 ?author .

MINUS { ?author (wdt:P103|(p:P1412/ps:P1412))/wdt:P279* ?language } .

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

}
```
Try it!
Matěj Suchánek (talk) 12:24, 11 May 2018 (UTC)
@Matěj Suchánek:: Thank you very much! So MINUS is the key. Your query returns a lot of results. I'm baffled. It will be hard to manually check what's wrong with those books. Many thanks again. --Wikidelo (talk) 13:37, 11 May 2018 (UTC)
@Wikidelo: There may be nothing wrong. My books have been translated into languages I can't write - I'm still the author. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 04:14, 12 May 2018 (UTC)
@Pigsonthewing: Of course, so the query excludes version, edition, or translation (Q3331189). Now it also excludes, for example, works written in English (Q1860) by authors who speak British English (Q7979) or American English (Q7976) but it doesn't work viceversa, i.e.: it still shows works written in British English (Q7979) by authors who speak or write plain English (Q1860). I think this can be solved, but the main flaw of the query is in cases of books by two or more people, like The Aquariums of Pyongyang (Q492500), written in French (Q150), where Pierre Rigoulot (Q3386800) knows French (Q150) but Kang Chol-hwan (Q483097) does not, so the book appears in the list. I've not checked the 15000 results, but randomly looking at some of them I can tell there's a lot of people with languages spoken, written or signed (P1412) missing and, definitely, something wrong with a bunch of those books. Cheers. Wikidelo (talk) 10:14, 12 May 2018 (UTC)

Displaying labels in two or more languages

I have:

```SELECT DISTINCT ?item ?itemLabel ?coord WHERE {
?item wdt:P31/wdt:P279* wd:Q33506;
wdt:P131 wd:Q1953;
wdt:P625 ?coord .
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,hy" } .
}
```

which works well; but what should I change, to display the English and Armenian labels, in separate columns? Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 12:46, 11 May 2018 (UTC)

I tried to use two `SERVICE`'s having bound different variables for each but it didn't let me. So the following is one possibility:
```SELECT DISTINCT ?item ?itemLabel ?itemLabelHy ?coord WHERE {
?item wdt:P31/wdt:P279* wd:Q33506;
wdt:P131 wd:Q1953;
wdt:P625 ?coord .
SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
OPTIONAL { ?item rdfs:label ?itemLabelHy FILTER( LANG( ?itemLabelHy ) = 'hy' ) } .
}
```
Try it!
Matěj Suchánek (talk) 13:02, 11 May 2018 (UTC)

Troubleshoot wdt:P31*

I run this query that works excellent finding museums

```#defaultView:Map
SELECT ?museum ?museumLabel ?location ?dist ?img ?article ?URL WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
SERVICE wikibase:around {
?museum wdt:P625 ?location.
bd:serviceParam wikibase:center "[AUTO_COORDINATES]".
bd:serviceParam wikibase:distance ?dist.
}
?museum  wdt:P31 wd:Q33506.
schema:inLanguage "sv"}
OPTIONAL{?museum  wdt:P18 ?img}
OPTIONAL {?museum  wdt:P856 ?URL}
}
```

as museums could be of a specific type I would like to change the query to something like this

```?museum  wdt:P31 wd:Q33506.
```

to

```?museum  wdt:P31* wd:Q33506.
```

==> I get an timeout and I guess because of some bad structure. Question: How do I troubleshoot that

I have tested this workaround

``` {?museum  wdt:P31 wd:Q33506}
UNION
{?museum  wdt:P31/wdt:P31 wd:Q33506}
```

that works great but maybe I miss some more complex structures.... - Salgo60 (talk) 09:38, 12 May 2018 (UTC)

`wdt:P31*` or `wdt:P31/wdt:P31` don't make sense since superclasses are linked to using subclass of (P279) (not instance of (P31)). The correct "complex structure" is `wdt:P31/wdt:P279*`. Note that these queries tend to timeout. Matěj Suchánek (talk) 09:53, 12 May 2018 (UTC)

Captain Cook memorials, and things named after him in Australia

In the Australian press there was discussion about memorials, statues, etc. named after Captain James Cook. There was a comment whether Google could map such memorials, and I thought that it was an interesting query which Wikidata may be able to manage. Any takers to see what we can do?  — billinghurst sDrewth 11:17, 12 May 2018 (UTC)

```#defaultView:Map
SELECT ?item ?itemLabel ?coord
WHERE
{
?item wdt:P138 wd:Q7324;
wdt:P17 wd:Q408;
wdt:P625 ?coord.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
```
Try it!
Things located in Australia that were named after James Cook, shown on a map. The query returns 4 results. Shinnin (talk) 11:26, 12 May 2018 (UTC)
20 results in the example below, because named after (P138) is most often not populated. Changing `"Captain.Cook|James.Cook"` to `"Cook` gives a much larger list, but with heaps of false positives. The ideal is to populate P138 for all items that are actually named after him.
```#defaultView:Map
SELECT ?item ?itemLabel ?coord
WHERE
{
?item wdt:P17 wd:Q408;
wdt:P625 ?coord;
rdfs:label ?itemLabel.
FILTER(LANG(?itemLabel) = 'en')
FILTER REGEX(STR(?itemLabel), "Captain.Cook|James.Cook")
}
```
Try it! - hth --Tagishsimon (talk) 11:47, 12 May 2018 (UTC)

Calculate percentage

This query displays the number of churches by region in France. I'd like to add another column with the percentage of churches with coordinate location (P625).

```SELECT ?item ?INSEE ?itemLabel (CONCAT("[[Wikidata:WikiProject France/Églises/",?INSEE,"\u007C",?itemLabel,"]]") AS ?LIEN) ?depCount
WHERE { {
SELECT ?item ?INSEE (COUNT(?item) AS ?depCount)
WHERE
{
SELECT DISTINCT ?item2 ?item ?INSEE
WHERE {
hint:Query hint:optimizer "None".
?item2 wdt:P131+ wd:Q142.
?item2 (wdt:P31/wdt:P279*) wd:Q16970.
?item2 wdt:P131 ?commune.
?commune p:P131 ?stmt . ?stmt ps:P131 ?item . OPTIONAL { ?stmt pq:P582 ?end } FILTER (!BOUND(?end)) .
?item wdt:P2586 ?INSEE
}
}
GROUP BY ?item ?INSEE
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". } }
ORDER BY ?INSEE
```

Is it possible? Thanks. — Ayack (talk) 15:56, 7 May 2018 (UTC)

It is:
```SELECT ?item ?INSEE ?itemLabel (CONCAT("[[Wikidata:WikiProject France/Églises/",?INSEE,"\u007C",?itemLabel,"]]") AS ?LIEN) ?depCount (CONCAT(STR(ROUND((IF(?_withCoord > ?depCount, ?depCount, ?_withCoord) / ?depCount) * 100 * 100) / 100), "%") AS ?withCoord)
WHERE {
{
SELECT ?item (COUNT(DISTINCT ?item2) AS ?depCount) (COUNT(DISTINCT ?coord) AS ?_withCoord) WHERE {
hint:Query hint:optimizer "None".
?item2 wdt:P131+ wd:Q142;
wdt:P31/wdt:P279* wd:Q16970;
wdt:P131 ?commune .
?commune p:P131 ?stmt .
?stmt ps:P131 ?item .
MINUS { ?stmt pq:P582 [] } .
?item wdt:P2586 [] .
OPTIONAL { ?item2 wdt:P625 ?coord } .
} GROUP BY ?item
} .
?item wdt:P2586 ?INSEE .
SERVICE wikibase:label { bd:serviceParam wikibase:language "fr" } .
}
ORDER BY ?INSEE
```
Try it!
Matěj Suchánek (talk) 18:41, 7 May 2018 (UTC)
Great! Thanks a lot Matěj Suchánek! — Ayack (talk) 11:16, 8 May 2018 (UTC)
In fact, there is an issue: ?depcount is different from the initial query which have the right number. — Ayack (talk) 11:23, 8 May 2018 (UTC)
I made a correction. Matěj Suchánek (talk) 12:37, 8 May 2018 (UTC)
Thanks Matěj Suchánek! Results are right now. Do you know how to round percentages to two decimal places? I have tried ROUND() but it doesn't work. — Ayack (talk) 07:57, 9 May 2018 (UTC)
SPARQL seems not to provide a function for this, so I hacked around it by multiplying the result by 100, rounding it and then dividing by 100. Matěj Suchánek (talk) 14:47, 9 May 2018 (UTC)
If you want the column "withCoord" to be properly sortable, you may want to sacrifice the "%" by skipping the STR and CONCAT stuff and just use
`(ROUND((IF(?_withCoord > ?depCount, ?depCount, ?_withCoord) / ?depCount) * 100 * 100) / 100 AS ?withCoord)`
--Larske (talk) 06:14, 12 May 2018 (UTC)
@Matěj Suchánek, Larske: Thanks a lot! — Ayack (talk) 18:49, 14 May 2018 (UTC)

Unit conversion

I want a list of units, their type, and conversion to SI units. The following query gives that, but is missing the unit for the SI conversion.

```SELECT
?unitLabel ?dimensionLabel ?siconvLabel
WHERE
{
?unit wdt:P2370 ?siconv .
?unit wdt:P111 ?dimension .
SERVICE wikibase:label {
bd:serviceParam wikibase:language "en" .
}
}
```

For example, this query gives me that "1 foot = .3048", but I'd like to know that "1 foot = .3048 m". Can this query be completed?

Meingbg (talk) 17:19, 13 May 2018 (UTC)

@Meingbg: Seems to be something like
```SELECT ?dimensionLabel  ?itemLabel  ?siconvLabel ?unitLabel
WHERE {
?item p:P2370/psv:P2370 ?var.
?var wikibase:quantityAmount ?siconv.
?var wikibase:quantityUnit ?unit.
?item wdt:P111 ?dimension .
SERVICE wikibase:label {bd:serviceParam wikibase:language "en" .}
}
```
Try it! hth --Tagishsimon (talk) 19:00, 13 May 2018 (UTC)
Perfect, thanks!
Meingbg (talk) 09:23, 17 May 2018 (UTC)

Query for complex constraint: if property has value A then qualifier B should be present

The problem is with safety classification and labelling (P4952) and its qualifiers. In many items it looks like

I every case if the set of 4 qualifiers should be present (and in many items right now e.g. P877 is absent). So I'm trying to add a complex constraint (I was advised that it can't be done using normal constraints) for this.

So I have two questions:

1. is it possible to somehow get select all items with , then look if Pxxx is present in each such statement (but without checking P4952 that has other value than Q51139288)?
2. maybe there is simpler/better way to achieve this (by this I mean that: if there is then all 4 qualifiers P993/P994/P995/P877 should be present). Wostr (talk) 20:44, 8 May 2018 (UTC)
@Wostr:, I'm not sure where we're going with this. Your query example is only picking up P4952=Q51139288 and Q51139288 has no P993, so where you say "this is not what I'm looking for, because it finds items where ,,statement||P4952|Q2005334,, has no P993" does not, for me, compute. Slightly amending & expanding your query, below, currently allows us to find P4952=Q51139288 with any one of the four pqs missing, and you can play with different arrangements of `filter not exist` to see different combinations of missingness. The query below demands that a single instance of Q51139288 must have each of the 4 pqs ... it does not deal with a situation in which there are multiple P4952=Q51139288 statements, each having a single qualifier. Do we need to solve that. (Or do we need to find such cases, and knock some sense into them. Umm. Help me out. What direction do you want to go in? I'm not familiar with complex constraints ... maybe I should go read up on them. Read this. As far as I can see, the below will serve as a custom constraint for the condition P4952=Q51139288 and Q51139288 has any one of P877, P993, P994 or P995 missing.
```SELECT DISTINCT ?item ?itemLabel ?v877 ?v993 ?v994 ?v995
WHERE {
?item p:P4952 ?statement.
?statement ps:P4952 wd:Q51139288.
filter not exists {?statement pq:P877 ?v877 .
?statement pq:P993 ?v993 .
?statement pq:P994 ?v994 .
?statement pq:P995 ?v995 .}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 2000
```
Try it! --Tagishsimon (talk) 22:47, 8 May 2018 (UTC)
• @Tagishsimon: the problem is that:
P4952=Q51139288 should always have all 4 qualifiers (P993/P994/P995/P877)
P4952=Q2005334 should always have all 4 qualifiers (P5040/P1033/P5041/P5042; but this is not the problem right now)
The query is picking items where P4952=Q51139288 has no P993/P994/P995/P877 (this is correct) but also items where P4952=Q2005334 has no P993/P994/P995/P877 (this is not). There is e.g. ethanol (Q153) in the results, where
safety classification and labelling (P4952)
NFPA 704: Standard System for the Identification of the Hazards of Materials for Emergency Response (Q51139288) edit
 NFPA Special (P877) no value
▼ 0 reference
Regulation (EC) No. 1272/2008 (Q2005334) edit
▼ 0 reference
So all 4 qualifiers for the first statements are present, but the query looked in the second statement and found no P993/P994/P995/P877 (and there shouldn't be any P993/P994/P995/P877, because it's the different statement). Wostr (talk) 07:07, 9 May 2018 (UTC)
I think ethanol is being selected because of the P877=no value in Q51139288 - see, for instance, this section. I think Q2005334 is a red herring; nothing is being selected because of it. So talk to me about P877=no value. Is that valid, or do we treat P877=no value as being the same as an absent P877? If the latter, then the query is okay. If the former, we need to rejig the query to deal with the no value issue. Not yet sure how to do that, right now, but maybe `?statement a wdno:PQ877` is the direction of travel; the query below, for instance, finds items with a P877 of no value. Not yet sure how to wrangle that into useful filter, and right now I urgently need to go and murder a busker playing very loudly amplified accordian music with an easy listening backing tape. I may be some time. irl, eh? --Tagishsimon (talk) 12:46, 9 May 2018 (UTC)
```SELECT DISTINCT ?item ?itemLabel ?v877 ?v993 ?v994 ?v995
WHERE {
?item p:P4952 ?statement.
?statement ps:P4952 wd:Q51139288.
?statement a wdno:P877 .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 2000
```
Try it!
So, this, maybe, if "no value" is an acceptable record (and thus should be excluded from the report).
```SELECT DISTINCT ?item ?itemLabel ?v877 ?v993 ?v994 ?v995
WHERE {
?item p:P4952 ?statement.
?statement ps:P4952 wd:Q51139288.
filter not exists {?statement pq:P877 ?v877 .
?statement pq:P993 ?v993 .
?statement pq:P994 ?v994 .
?statement pq:P995 ?v995 .}
minus {?statement a wdno:P877 .} #deal with value is "no value"
minus {?statement a wdno:P993 .}
minus {?statement a wdno:P994 .}
minus {?statement a wdno:P995 .}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 2000
```
Try it! --Tagishsimon (talk) 15:37, 9 May 2018 (UTC)
Okay, I think it's working :) thank you very much. In fact, P877=no value is correct and it'll be the most common case when I correct all the statements where P877 is absent. The difference between number of items from your first and second queries seems okay, so it really filtering out all the no values. The only thing is, why your last query returns 1032 items and the query without spaces before "." returns 1036 or 1035 items, depending on... I really don't know on what. Does the spaces in the query matters?
```SELECT DISTINCT ?item ?itemLabel ?v877 ?v993 ?v994 ?v995
WHERE {
?item p:P4952 ?statement.
?statement ps:P4952 wd:Q51139288.
filter not exists {
?statement pq:P877 ?v877.
?statement pq:P993 ?v993.
?statement pq:P994 ?v994.
?statement pq:P995 ?v995.}
minus {?statement a wdno:P877.} #deal with value is "no value"
minus {?statement a wdno:P993.}
minus {?statement a wdno:P994.}
minus {?statement a wdno:P995.}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 2000
```
Try it!
Wostr (talk) 16:52, 9 May 2018 (UTC)
No, wait; now both queries gives 1036, so nevermind ;) Thanks for help. Wostr (talk) 16:54, 9 May 2018 (UTC)

@Wostr: - sorry, we're not quite there. I'd leave the constraint you added in place since I think for practical purposes the issue is on the margins given the current state of the data.

All - the problem is explained in the comments in the report below --Tagishsimon (talk) 00:30, 10 May 2018 (UTC)

```SELECT DISTINCT ?item
# we are looking for P4952=Q51139288 which are missing any of four PQs
# a PQ of 'no value' is a valid PQ.
WHERE {
?item p:P4952 ?statement.
?statement ps:P4952 wd:Q51139288.
filter not exists {
?statement pq:P877 ?v877.
?statement pq:P993 ?v993.
?statement pq:P994 ?v994.
?statement pq:P995 ?v995.}

# Out of the above emerges a list of P4952=Q51139288 where there is one or more missing PQs.
# However the filter treats a PQ value of 'no value' as the absence of a PQ.
# So an item having the 4 PQs, but with one or more of 'no value', will appear in the results.
# an item having the 4 PQs, but with one or more of 'no value' should not be listed.

# the minus statements below were an attempt to deal with the 'no value' issue
# but they'll exclude an item that has e.g. 3 PQs, one of which is 'no value'

# I don't know how to make use of "a wdno:P877" so as to minus the item only where all four PQs are present.

minus {?statement a wdno:P877.} #deal with value is "no value"
minus {?statement a wdno:P993.}
minus {?statement a wdno:P994.}
minus {?statement a wdno:P995.}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
```

@Wostr: Maybe this is what you are looking for.

```SELECT DISTINCT ?item
# we are looking for P4952=Q51139288 which are missing any of four PQs
# a PQ of 'no value' is a valid PQ.
WHERE {
?item p:P4952 ?statement.
?statement ps:P4952 wd:Q51139288.
MINUS {
OPTIONAL { ?statement pq:P877 ?v877 }
FILTER(BOUND(?v877) || EXISTS{?statement a wdno:P877.})  #treat the value "no value" as valid
OPTIONAL { ?statement pq:P993 ?v993 }
FILTER(BOUND(?v993) || EXISTS{?statement a wdno:P993.})
OPTIONAL { ?statement pq:P994 ?v994 }
FILTER(BOUND(?v994) || EXISTS{?statement a wdno:P994.})
OPTIONAL { ?statement pq:P995 ?v995 }
FILTER(BOUND(?v995) || EXISTS{?statement a wdno:P995.})
}

# Out of the above emerges a list of P4952=Q51139288 where there is one or more missing PQs.
# ALSO the filter treats a PQ value of 'no value' as the PRESENCE of a PQ.
# So an item having the 4 PQs, but with one or more of 'no value', will NOT appear in the results.

# Please note that also a PQ of 'unknown value', as e.g. Q331451 and Q21099636 have, is treated as a valid PQ

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

Please note that 'unknown value' is treated as valid PQ in this query. --Larske (talk) 05:33, 12 May 2018 (UTC)

Thank you Larske; 'unknown value' is in fact a valid value in these qualfiers; I'll try to check this query as soon as I can :) Wostr (talk) 19:29, 14 May 2018 (UTC)
It seems that you query found 4 items more and I really don't know why ;) I'll update the constraint though, thanks again. Wostr (talk) 19:31, 20 May 2018 (UTC)

Map of languages

Hi

I'm working on a proposal for a project to try and improve coverage of languages on Wikidata and Wikipedia and want to create a prototype to visualise languages on a map as a way to explore them. Is there some way to create a map which shows which languages are present in which countries? I know there are some issues with lack of shape files for countries and country regions. But I've also seen maps like this? I'm very happy for it to work for a very small region, all I want to show is that its is possible and you can click a button to get to the Wikipedia article for that language. This could either be a map to show all the languages in a region or which countries a language is spoken in or both.

Thanks very much

--John Cummings (talk) 10:11, 15 May 2018 (UTC)

Here's a query that can start to answer this question: a map showing every sovereign state and its official languages. Issues that still need to be fixed are: using shape polygons to cover the countries instead of using a point coordinate per country, and linking to the wiki pages of the languages instead of just showing them in a concatenated field. I'm pretty sure those issues can be fixed with Kartographer, but I haven't worked with Kartographer in the past so I can't be of help there.

The following query uses these:

Features: Map (Q24515275)

``` 1 #defaultView:Map
2  SELECT
3  ?country
4  ?countryLabel
5  ?coordinates
6  (group_concat(?languagesLabel;separator=" , ") as ?official_languages)
7  WHERE {
8    ?country wdt:P31 wd:Q3624078.
9    ?country wdt:P37 ?languages .
10    ?country wdt:P625 ?coordinates .
11    OPTIONAL {
12  		?languages rdfs:label ?languagesLabel.
13  		FILTER(LANG(?languagesLabel) = "en").
14  	}
15    SERVICE wikibase:label { bd:serviceParam wikibase:language 'en'. }
16  } GROUP BY ?country ?countryLabel ?coordinates
```

--CalvinBall (talk) 11:13, 18 May 2018 (UTC)

Query all International Masters awarded in a specific year

How to build a query that queries all title of chess player (P2962): International Master (Q752119) that have been awared in a specific year (start time (P580)), e. g. 1995? Steak (talk) 19:37, 20 May 2018 (UTC)

@Steak: Like this ... the data model is, for these sorts of things, instructive, fwiw.
```SELECT ?item ?itemLabel ?start_time
WHERE
{ ?item p:P2962 ?statement.                            # item has a P2962 statement
?statement ps:P2962 wd:Q752119; pq:P580 ?start_time. # P2962 statement = International Master, and has a P580 qualifier
FILTER (?start_time >= "1995-01-01T00:00:00Z"^^xsd:dateTime) . # P580 qualifier gt or eq this date
FILTER (?start_time < "1996-01-01T00:00:00Z"^^xsd:dateTime) .  # P580 qualifier lt that date
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # get labels
}
```
Try it! --Tagishsimon (talk) 19:48, 20 May 2018 (UTC)
Thank you! Steak (talk) 20:29, 20 May 2018 (UTC)

Querying a value with qualifiers

I would appreciate some help with the following query. I am trying to create a SparQL query that will list all the articles that have on focus list of Wikimedia project (P5008) and Amnesty International Editathon (Q53764742), and also the specific qualifier location (P276) plus Amsterdam (Q727). I created the query I have placed underneath here, but it doesn't give any result(s). (At the moment it should at least produce Nada Kiswanson (Q53766816).) Ideally I would like to also include the qualifier point in time (P585) with value 19th of May 2018 (in combination with Amsterdam as a location), but I honestly have no idea how to format that date part of the query.

```#articles created during editathon
SELECT ?item ?itemLabel
WHERE
{
?item wdt:P5008 wd:Q53764742.
?item p:Q53764742 [ps:P276 pq:Q727;].
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
```

Thank you in advance for all the help , Ecritures (talk) 14:39, 20 May 2018 (UTC)

```#articles created during editathon
SELECT ?item ?itemLabel
WHERE
{
?item p:P5008 ?statement.
?statement ps:P5008 wd:Q53764742;
pq:P276 wd:Q727 .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
```
Try it! Shinnin (talk) 14:46, 20 May 2018 (UTC)
@Ecritures: (ec) this may take you a bit further.
```#articles created during editathon
SELECT ?item ?itemLabel ?locationLabel ?point_in_time
WHERE
{
# ?item wdt:P5008 wd:Q53764742.
# ?item p:Q53764742 [ps:P276 pq:Q727;].
?item p:P5008 ?var.
?var ps:P5008 wd:Q53764742; pq:P276 ?location ; pq:P585 ?point_in_time.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
```
Try it! - hth --Tagishsimon (talk) 14:48, 20 May 2018 (UTC)

Not exactly what you asked for, but all items that *do have an article at nl-wiki* and are present in the Amnesty list:

```select ?itemLabel ?ietsLabel where { ?item wdt:P5008 wd:Q53764742
FILTER EXISTS {?wen schema:about ?item . ?wen schema:inLanguage "nl" }
{service wikibase:label {bd:serviceParam wikibase:language "nl" } }
}
```

Edoderoo (talk) 15:18, 20 May 2018 (UTC)

Thank you all so very, very much! I will be using all these queries for different purposes. Ecritures (talk) 13:18, 21 May 2018 (UTC)

Norwegian citicens holding a political party membership

I want to make a Query listing only Norwegians who has Template:P102. I am able to make this list, but this includes all Norwegians... Breg Pmt (talk) 17:30, 22 May 2018 (UTC)

@Pmt:
```SELECT distinct ?item ?itemLabel ?partyLabel
WHERE
{ ?item wdt:P27 wd:Q20 .       #country of citizenship: Norway
?item wdt:P102 ?party .      #is a member of political party
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],no". } # get labels
}
```
Try it! - hth ... if you'd like other attributes, sorts, etc, let us know. --Tagishsimon (talk) 17:54, 22 May 2018 (UTC)

-Thank you very much. Breg Pmt (talk) 17:39, 23 May 2018 (UTC)

Reference URL's timeout

I would like to query all reference url's of a specific website (https://www.cbs.nl/) but receive a timeout when going over a limit of 26.

current working query:

```SELECT ?statement ?subject ?property ?refURL WHERE {
hint:Query hint:optimizer "None".
?ref pr:P854 ?refURL.
?statement prov:wasDerivedFrom ?ref.
?subject ?p ?statement.
?property wikibase:claim ?p.
?property wikibase:statementProperty ?ps.
FILTER(CONTAINS(STR(?refURL), "http://statline.cbs.nl"))
}
LIMIT 25
```

The Le Figaro example is proposed here as "A query that has difficulties" for automatic optimization. The proposed solution appears to be turning the optimizer off, but my query still times out when the limit is removed. https://www.wikidata.org/wiki/Wikidata:SPARQL_query_service/query_optimization

So i suppose my question is: Is there any way to get find all uses of "http://statline.cbs.nl" in the entire wikidata dataset without getting a timeout?  – The preceding unsigned comment was added by LeviQueryRequest (talk • contribs) at 13:35, 23 May 2018‎ (UTC).

Get all Lexeme lemmas and IDs

With the Lexeme property officially launched, I wanted to do a simple query to return all the current lexeme IDs and their Lemma label. Here's an example of a lexeme. It's modeled similarly to wikidata properties, it seems, using a Lexeme namespace (Lexeme:L1). I tried to query all the lexemes in the same way one would query all the properties, which didn't seem to work. Does anyone know how to properly list all of them currently?

The following query uses these:

```1 #trying to get all Lexeme IDs and lemmas
2
3 SELECT ?lexeme ?lexemeLabel WHERE {
4 ?lexeme rdf:type wikibase:Lexeme .
5         }
```

--CalvinBall (talk) 12:55, 24 May 2018 (UTC)

According to the announcement at WD:PC, lexemes cannot be queried yet in the Query Service. You probably have to wait for this to work. —MisterSynergy (talk) 08:33, 24 May 2018 (UTC)
okay, thanks for the answer! I'm looking forward to being able to query Lexemes in the near future ^-^ --CalvinBall (talk) 12:55, 24 May 2018 (UTC)

Potential articles that might be mosaics, based on categories, but haven't been identified as such on Wikidata

Hey All, I would like to replicate much of the same function as the list at Wikidata:WikiProject_sum_of_all_paintings/Possible_paintings, for looking for other potential pieces of art (in my case, I am really interested in mosaics). Multichill currently runs that list with a both, but running a copy of that bot on my own is beyond my skill, and it would be great if I could run a similar result via Listeria Bot (see conversation at User_talk:Multichill#Mosaics_report_like_Wikidata:WikiProject_sum_of_all_paintings/Possible_paintings). Could someone write a query that could do this? Working with categories, and Wikipedias to create this list in the query service is a bit beyond my understanding, and I can't find a good example that uses categories. Sadads (talk) 18:27, 26 May 2018 (UTC)

@Sadads: Listeria may be of use, such as to keep tabs on the statements associated with an identified set of wikidata items, such as those with a P31 of mosaic (Q133067). But it cannot do the main thing you're after, which is fishing around inside language wikipedia categories to find articles which have no wikidata item, or articles which have an item but do not have a P31 Q133067. A quick look at multichill's code suggests it is taking the various language names for category:paintings from a wikidata item, and then throwing a succession of queries at petscan, and then presumably doing something interesting with the results. So, right now, I'd point you at petscan. Are you familiar with it? Here, for instance, is a report on articles in en.wiki's category:Mosaics, or in categories up to 3 levels below that one, which do have a wikidata item, but do not have Q133067 in the item record. You can amend this to query other language wikipedias, by finding the category name listed in Category:Mosaics (Q8641914) ... so here's a version for the it.wiki (and in both cases, hit the do it button to run the report.
From a quick look at a sample of 1, the article in the mosaics category had a P31 of sculpture, because it is some combination of sculpture and mosaic (w:en:In the Shadow of the Elm) ... so you might find it all tedious going. I'm happy to talk to you some more about petscan, listeria or the query service; but sadly I'm too much of a wimp right now to fork multichill's code. --Tagishsimon (talk) 23:20, 26 May 2018 (UTC)
I am fairly comfortable with petscan, but really would like some type of share report for working from this: otherwise, I will just hack inconsistently at stuff on enwiki and Commons with a combo of petscan and others lists. I guess I really should be making a multichill or listeria fork request for pinging petscan. Sadads (talk) 02:20, 27 May 2018 (UTC)

Age at death

I'd like to run a query for age at death, listing people who were a certain age at death (or a range of ages), e.g. the "27 club". It could be exact or based on birth and death years. I looked up how to handle dates,[1] but I don't understand the query language well enough. Fences and windows (talk) 18:17, 25 May 2018 (UTC)

@Fences and windows: something like this. Your biggest problem is that the timeout is too short to allow you to run the query across all humans, so you have to find subsets of them. In the query below, I've found 73-year old geologists for you. How wide the part of the query that defines how many humans the report will be considering can be drawn, is a matter for experimentation, but you'll be wanting one or more lines in the query along the lines of `?item wdt:P106 wd:Q520549.` where you're saying, the item (human) has a property with this value. (e.g. gender=female, occupation=plumber, country of citizenship=Australia, etc. Finally, my SPARQL may be a bit more clunky than needsbe ... I'm still at the rubbing sticks together hopefully stage of my learning :)
```SELECT ?item ?itemLabel ?dob ?dod ?days ?span
WHERE
{ ?item wdt:P106 wd:Q520549.       # occupation=geologist
?item wdt:P569 ?dob .            # must have DoB
?item wdt:P570 ?dod .            # must have DoD
bind ((?dod - ?dob) as ?days)    # date maths to get lifespan in days
bind (floor(?days/365) as ?span) # convert to years
FILTER (?span <= 73)              # filter for 73-year olds or younger
FILTER (?span > 63)              # filter for 64-year olds or older
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # get labels
}
```
Try it! - hth - --Tagishsimon (talk) 20:21, 25 May 2018 (UTC)
@Tagishsimon:, wow, you're good at this. Thank you. A shame the full search times out - could they not batch larger searches? Fences and windows (talk) 22:48, 26 May 2018 (UTC)
@Fences and windows: Thank you. I'm quite intrepid in the shallow end. I don't know enough about the infrastucture, but odd snippets suggest to me there's no appetite to increase the timeout value; or to provide a quarry-type batch report running facility. I get the impression that queries that would run, a year or more ago, now timeout. So, yes; it's a shame, especially when trying to address large & poorly statemented items such as many of our humans records, or list article items. --Tagishsimon (talk) 23:31, 26 May 2018 (UTC)
@Fences and windows, Tagishsimon: Maybe not important in this case, but the division by 365 gives the wrong result for persons that are near, but not full, 73 years of age at their death. This is because they have lived a number of leap years. See this modified query which will list Emil Bertrand Münster (Q11967330) who was 72 years, 11 months and 18 days at his death:
```SELECT ?item ?itemLabel ?dob ?dod ?days ?span ?age
WHERE
{ ?item wdt:P106 wd:Q520549.       # occupation=geologist
?item wdt:P569 ?dob .            # must have DoB
?item wdt:P570 ?dod .            # must have DoD
bind ((?dod - ?dob) as ?days)    # date maths to get lifespan in days
bind (floor(?days/365) as ?span) # convert to years
FILTER (?span = 73)              # filter for 73-year olds
BIND(year(?dob) AS ?yb)  BIND(year(?dod) AS ?yd)
BIND(month(?dob) AS ?mb) BIND(month(?dod) AS ?md)
BIND(day(?dob) AS ?db) BIND(day(?dod) AS ?dd)
BIND(?yd-?yb AS ?age)                              # set preliminary age as year difference
BIND(IF(?md<?mb,?age-1,?age) AS ?age)              # subtract 1 if month of death is less than month of birth...
BIND(IF(?md=?mb && ?dd<?db, ?age-1,?age) AS ?age)  # ...or if month of death is same as month of birth but day of death is less than day of birth
FILTER(!(?age=?span))                              # filter to show example where the age (with leap years included) differs from the span (without leap years included)
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # get labels
}
```
Try it!
There are some other problems with these querys;
1. They don't take the timePrecision into consideration. Some persons just have a year precision for their birth and/or death years and they can thus be faulty listed (or not listed) as "73 years at death person". Wincenty Kadłubek (Q369562) is one example of a person with birth date with year precision.
2. They don't take the timeCalendarModel into consideration. Some persons might have different calenders for their birth and death dates, Nicholas Roerich (Q208993) is one example of such a person. This might lead them to be faulty listed as well as these calendars differ with a couple of week.
--Larske (talk) 08:02, 27 May 2018 (UTC)
@Larske: thank you; that's all understood, and a fine illustration of deep end versus shallow. Much better analysis of the problem, and an elegant & easy to follow solution. I very much enjoy trying to put together queries, but I'm always properly grateful to be schooled in this way: several things learned, which is the whole point of my lurking on this page. It's fair to say that much of what I've comprehended in the last 6 or 8 months has been as a result of observing your queries, and those of Matěj Suchánek, MisterSynergy, and others. If you could hear how often I say ah, so that's how you do that; such as for your solution in 'Query for complex constraint: if property has value A then qualifier B should be present' where I just couldn't get a handle on `?statement a wdno:P995.` and you came along with `exists{}`. Please, never hesitate to jump in and suggest improvements, because that's what makes this page the best SPARQL tutorial on the interweb. --Tagishsimon (talk) 12:42, 27 May 2018 (UTC)
@Larske: and @Tagishsimon:, can the filter "span" accept a range of values? It works with < or > rather than =. Fences and windows (talk) 21:35, 27 May 2018 (UTC)
@Fences and windows: I've amended my query, above, to show geologists between 64 & 73 by the addition of another filter. Is that what you're after? Obvs, adopting Larske's creation and amending the filter in it, along the lines of the filter in mine, is the way to go. --Tagishsimon (talk) 22:34, 27 May 2018 (UTC)
Or, if you're after a non-sequential set of ages, `FILTER (?span in (62.0, 63.0, 73.0))` seems to work a treat. --Tagishsimon (talk) 22:41, 27 May 2018 (UTC)

Using qualifiers

I have this Query workin fine, but I want to add the qualifier of (P642) Any suggestion

SELECT DISTINCT ?item ?itemLabel ?partyLabel ?partitilh_righet ?partitilh_righetLabel ?f_dselsdato ?Historisk_befolkningsregister_ID ?virkested ?virkestedLabel ?av_innenfor ?av_innenforLabel

```WHERE {
?item wdt:P27 wd:Q20.
?item wdt:P39 wd:Q30185.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],no". }
OPTIONAL { ?item wdt:P102 ?partitilh_righet. }
OPTIONAL { ?item wdt:P569 ?f_dselsdato. }
OPTIONAL { ?item wdt:P4574 ?Historisk_befolkningsregister_ID. }
OPTIONAL { ?item wdt:P937 ?virkested. }
```

}

``` Best Regards Pmt (talk) 19:55, 28 May 2018 (UTC)
```
@pmt:
```SELECT DISTINCT ?item ?itemLabel ?partyLabel ?partitilh_righet ?partitilh_righetLabel ?f_dselsdato ?Historisk_befolkningsregister_ID ?virkested ?virkestedLabel ?av_innenfor ?av_innenforLabel ?ofLabel

WHERE {
?item wdt:P27 wd:Q20.
?item wdt:P39 wd:Q30185.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],no". }
OPTIONAL { ?item wdt:P102 ?partitilh_righet. }
OPTIONAL { ?item wdt:P569 ?f_dselsdato. }
OPTIONAL { ?item wdt:P4574 ?Historisk_befolkningsregister_ID. }
OPTIONAL { ?item wdt:P937 ?virkested. }
OPTIONAL { ?item p:P39 ?statement.
?statement ps:P39 wd:Q30185; pq:P642 ?of. }
} order by desc(?ofLabel)
```
Try it! like this - I've wrapped the necessary clauses in an Optional{} and ordered results so you can see some ofs. I'm presuming you wanted of P39? The statement I added could be combined with your P39 statement; I've chosen not to do that for now. If you visit the RDF model you can probably trace out the connections which necessitate this circumlution. --Tagishsimon (talk) 21:52, 28 May 2018 (UTC)

@Tagishsimon: Thank you very much. Breg Pmt (talk) 22:00, 28 May 2018 (UTC)

Defining formula (P2534)

I am looking for a query for items that have defining formula (P2534) = "c^2=a^2+b^2". The query should return Pythagorean theorem (Q11518). --Rotpunkt (talk) 13:57, 24 May 2018 (UTC)

@Rotpunkt: I have a not very pretty solution. Others may be able to provide a less painful route.
```SELECT ?item ?itemLabel ?f ?f2 WHERE
{
# VALUES ?item {wd:Q11518}                                     # uncomment to focus on known item
?item wdt:P2534 ?f .                                         # item has a defining formula
bind(str(?f) as ?f2)                                         # render formula as string
FILTER REGEX(?f2, "c\\^\\{2\\}\\=a\\^\\{2\\}\\+b\\^\\{2\\}") # regex finds c^{2}=a^{2}+b^{2}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # get labels
}
```
Try it! --Tagishsimon (talk) 15:51, 24 May 2018 (UTC)
Thanks, but actually I was looking for a query without regex. In defining formula (P2534) of Pythagorean theorem (Q11518) if you click "edit" the value is "c^2=a^2+b^2" I want to query for that exact value without regex, in the same way I can do for all other datatypes. I have this problem only with Help:Data_type#Mathematical expression properties. --Rotpunkt (talk) 10:30, 26 May 2018 (UTC)
Good question. Maybe one for Wikidata:Contact the development team. Maybe the "alttext/displaystyle" should be in a separate triple. Its value for the above is alttext="{\displaystyle c^{2}=a^{2}+b^{2}}"-
--- Jura 11:14, 26 May 2018 (UTC)
Crushed as I am by Rotpunkt's reaction to my query ;) there is a certain compelling logic that we should be able to search using the string we used at data input time. (`contains` would allow you to find c^{2}=a^{2}+b^{2} & dispense with regex, but c^2=a^2+b^2 is beyond my reach. Are we certain it is not stored anywhere?) --Tagishsimon (talk) 11:58, 26 May 2018 (UTC)
Wikidata:Contact the development team#Queries for maths formula --Tagishsimon (talk) 12:05, 26 May 2018 (UTC)

Oh, don't be! At least, he did thank you and provided valuable feedback. ;)
In https://www.wikidata.org/wiki/Special:EntityData/Q11518.json there is
"P2534":[{"mainsnak":{"snaktype":"value","property":"P2534","datavalue":{"value":"c^2=a^2+b^2","type":"string"},"datatype":"math"},"type":"statement","id":"Q11518\$fe4a5b31-4f7e-29a3-06da-25f0186d9a42","rank":"normal"}],
Looks like its simplicity somehow got lost in WQS. Maybe @Smalyshev_(WMF): can help?
Personally, I had hoped we could do more with the datatype and I don't think people use it much. So it might primarily work for 3rd party tools. For now, it might be a bit like PDFs in Wikidata.
--- Jura 12:13, 26 May 2018 (UTC)
@Jura1: It does look overly complex to me. But rendering of the type is defined by the implementation of the type (e.g. the extension) so it needs to be changed there. So let's discuss on and figure out how to improve it. Smalyshev (WMF) (talk) 23:21, 29 May 2018 (UTC)
Why, Sir, you find no person, at all intellectual, who is willing to leave Regex. No, Sir, when a person is tired of Regex, she is tired of life; for there is in Regex all that life can afford. --Tagishsimon (talk) 15:18, 26 May 2018 (UTC)
@Rotpunkt, Tagishsimon, Jura1: I’ve created phabricator:T195765 to track this. --Lucas Werkmeister (WMDE) (talk) 12:42, 28 May 2018 (UTC)
Nice, thanks to all. I will monitor that task. --Rotpunkt (talk) 17:09, 28 May 2018 (UTC)

Classification of animals

Hello there,

I'm interacting with a third-party who would like to reuse our classification about animals. I was thinking about making a query with a tree view that would render all animals. Since I never had the time to dig properly into our biology ontologies, I'm a bit lost :)

Can anyone help me to build a query that would give an overview on how we describe and organize data about animals?

Thank you very much, Lea Lacroix (WMDE) (talk) 12:39, 28 May 2018 (UTC)

@Lea Lacroix (WMDE): Scholia has a graph like that in its taxon aspect. --Daniel Mietchen (talk) 00:04, 29 May 2018 (UTC)
@Lea Lacroix (WMDE): There are more than 2.4 million taxon (Q16521) of which some are in the kingdom (Q36732) of animal (Q729). Have a look at this graph to see how the taxonomic rank (Q427626) are connected in some kind of part of (P361) hierarchy from subform (Q12774043) via variety (Q767728), species (Q7432), genus (Q34740), tribe (Q227936), family (Q35409), order (Q36602), class (Q37517) and phylum (Q38348) to kingdom (Q36732). --Larske (talk) 09:59, 29 May 2018 (UTC)
@Lea Lacroix (WMDE):: Please note that in Wikidata all taxa from different taxonomies are included and that it will not be possible to create a "valid" taxonomy out of it - all depends on the sources and hypotheses included. I am sure that it is not possible to built a valid and solid tree of animals in WikiData. Please ask @Succu: on this topic - from my opinion he is the one who best knows about these areas in Wikidata. -- Achim Raschka (talk) 06:00, 30 May 2018 (UTC)
@Lea Lacroix (WMDE): contrary to the names given to taxon (Q16521) in different languages, it does not represent a taxon (a particular group of organisms) but a taxon name, as has been made even clearer recently by the discussion at Property talk:P1420. The relationship between taxon names and taxa is N:M, since a given taxon may have more than one name (e.g. Syrmatium cytisoides (Q15542607) and Acmispon cytisoides (Q15520206) are just two of the names in Wikidata for the same taxon), and a taxon name may apply to more than one taxon (e.g. Araceae (Q48227) is the name of a taxon that includes Lemnaceae (Q14293890) as a subfamily and the name of a taxon that excludes it). Within its severe limitations, particularly its inability to represent taxa, Wikidata attempts to represent multiple taxonomic views, as User:Achim Raschka says, so it should never be used to try to build a self-consistent taxonomic tree. Peter coxhead (talk) 09:02, 30 May 2018 (UTC)
There is a related topic at my talk page. --Succu (talk) 17:34, 30 May 2018 (UTC)

Qualifier reference URL (P854) for inventory number (P217) of artworks

Dear all, I can not figure out how to write what should be a simple query: one that would give me all of the artworks from a specific collection with their inventory number (P217) and the reference URL (P854) qualifier for that inventory number (P217). I have tried this, but I think I am not getting the qualifiers fully:

```SELECT ?Royal_Museum_of_Fine_Arts_Antwerp ?Royal_Museum_of_Fine_Arts_AntwerpLabel ?inventory_number ?reference_url WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
?Royal_Museum_of_Fine_Arts_Antwerp wdt:P195 wd:Q1471477.
?Royal_Museum_of_Fine_Arts_Antwerp wdt:P217 ?inventory_number.
?inventory_number pq:P854 ?reference_url
}
LIMIT 100
```

Could somebody help me? That would be much appreciated! Alina data (talk) 14:18, 29 May 2018 (UTC)

@Alina data: It's not a very straightforward query, but with luck, this works.
```SELECT ?Royal_Museum_of_Fine_Arts_Antwerp ?Royal_Museum_of_Fine_Arts_AntwerpLabel ?inventory_number ?reference_url WHERE {
?Royal_Museum_of_Fine_Arts_Antwerp wdt:P195 wd:Q1471477 .
?Royal_Museum_of_Fine_Arts_Antwerp  p:P217 ?statement .
?statement ps:P217 ?inventory_number.
?statement prov:wasDerivedFrom ?something.
?something pr:P854 ?reference_url.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} limit 100
```
Try it! - hth - --Tagishsimon (talk) 14:57, 29 May 2018 (UTC)
@Tagishsimon: it works perfectly! thank you! Alina data (talk) 08:20, 30 May 2018 (UTC)

Combine two queries

Hi, I would like to combine these two queries into one:

This returns the streets in Haarlem here on Wikidata:

```SELECT DISTINCT ?item ?wikilabel WHERE {
?item rdfs:label ?wikilabel .
?item wdt:P131 wd:Q9920 .
?item wdt:P31 wd:Q79007 .
}
```

&

This returns the streets in Haarlem in the BAG:

```SELECT ?item ?weg ?baglabel {
SERVICE <http://lov.okfn.org/dataset/lov/sparql> {
SERVICE <https://data.pdok.nl/sparql> {
select ?weg ?baglabel
where {
?weg <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://bag.basisregistraties.overheid.nl/def/bag#OpenbareRuimte> .
#?weg <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://bag.basisregistraties.overheid.nl/def/bag#Weg> .
?weg <http://bag.basisregistraties.overheid.nl/def/bag#status> <http://bag.basisregistraties.overheid.nl/id/begrip/NaamgevingUitgegeven> .
?weg <http://www.w3.org/2000/01/rdf-schema#label> ?baglabel .
?weg <http://bag.basisregistraties.overheid.nl/def/bag#bijbehorendeWoonplaats> <http://bag.basisregistraties.overheid.nl/bag/id/woonplaats/2907>
}
}
}
}
```

would like to combine these based on label so I have suggestions on what to add. Seem to have gotten a bit stuck. Maybe a fresh pair of eyes will help? Multichill (talk) 18:45, 31 May 2018 (UTC)

@Multichill:
```SELECT ?item ?weg ?label
WITH {
SELECT DISTINCT ?item (?wikilabel AS ?label) WHERE {
?item rdfs:label ?wikilabel .
?item wdt:P131 wd:Q9920 .
?item wdt:P31 wd:Q79007 .
}
} AS %wikidata
WITH {
SELECT ?weg (?baglabel AS ?label) {
SERVICE <http://lov.okfn.org/dataset/lov/sparql> {
SERVICE <https://data.pdok.nl/sparql> {
?weg <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://bag.basisregistraties.overheid.nl/def/bag#OpenbareRuimte> .
#?weg <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://bag.basisregistraties.overheid.nl/def/bag#Weg> .
?weg <http://bag.basisregistraties.overheid.nl/def/bag#status> <http://bag.basisregistraties.overheid.nl/id/begrip/NaamgevingUitgegeven> .
?weg <http://www.w3.org/2000/01/rdf-schema#label> ?baglabel .
?weg <http://bag.basisregistraties.overheid.nl/def/bag#bijbehorendeWoonplaats> <http://bag.basisregistraties.overheid.nl/bag/id/woonplaats/2907>
}
}
}
} AS %weg
WHERE {
INCLUDE %wikidata.
INCLUDE %weg.
}
```
Try it! --TweetsFactsAndQueries (talk) 19:54, 31 May 2018 (UTC)
Thanks! I put some variants at Property talk:P5207 for the curious reader :-) Multichill (talk) 22:26, 31 May 2018 (UTC)