Wikidata

Wikidata:Request a query/Archive/2018/06

< Wikidata:Request a query‎ | Archive‎ | 2018
This page is an archive. Please do not modify it. Use the current page, even to continue an old discussion.

Wikidata Query and Wikipedia statistics/meta informations

Is it possible by Wikidata Query reach the Wikipedia statistics/meta informations like page views, number of editors, watchers etc? I'd like to e.g. compare number of visitors of an article with number of visitors "technical" pages like its history or talk are, find out articles about singers with the most number of revisions etc. --Nesnera (talk) 10:56, 1 June 2018 (UTC)

No, it isn't (yet). Matěj Suchánek (talk) 13:16, 1 June 2018 (UTC)
It is a pity. And is it planned? Approximate date?--Nesnera (talk) 14:52, 1 June 2018 (UTC)
I don't know. You can ask at WD:DEV but the priorities seem to be different at the moment. Matěj Suchánek (talk) 15:43, 1 June 2018 (UTC)

Counting appearances as talk show guests

I'm trying to count how often people have apppeared on a particular talk show as guests. However, all the count values are 1, although some people like Q1082694 do appear in different episodes. 

select ?person ?personLabel (count(distinct ?episode) as ?count) where
{
  ?episode wdt:P179 wd:Q565894.
  ?episode wdt:P5030 ?person.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?person ?personLabel order by desc(?count)
Try it!

--37.201.29.101 10:46, 1 June 2018 (UTC)


    • Thanks a lot! I've added personLabel as a secondary sort criterion. This works, except for very recently created elements (e.g. Anna Firth) which appear at the very end of the list. Seems like a bug to me, are all queries run on two databases (established records and new ones) and then concatenated? 
      select ?person ?personLabel (count(distinct ?episode) as ?count) where
{
  ?episode wdt:P179 wd:Q565894.
  ?episode wdt:P5030 ?person.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?person ?personLabel order by desc(?count) asc(?personLabel)
      Try it!
      --37.201.29.101 17:00, 2 June 2018 (UTC)

lang() and ?lang

  1. BIND("en" as ?lang ) . FILTER (lang(?label)=?lang)
  2. FILTER (lang(?label)="en" )

The two above should be equivalent, but sometimes I think they aren't. I haven't really managed to find a query to illustrate it, but sometimes they seem to fail because of that. Maybe someone else has come across this. Maybe it's another problem.
--- Jura 13:29, 1 June 2018 (UTC)

My experience also is that BIND can be slightly odd - I think the optimiser doesn't really like it, and perhaps tries to defer it, expecting it to be something that gets done at the end of a query, to patch together output formats, etc.
If you want to set a variable to a particular constant, a better option may be VALUES, as in
VALUES ?lang {"en"}
This I think the optimiser does know that it should do first; it allows very easy extension to allow a set of values, eg {"en", "fr", "de"}. Jheald (talk) 13:47, 1 June 2018 (UTC)
I tend to avoid VALUES as I think it leads to timeouts. The optimizer thing might explain why it's hard to reproduce ..
--- Jura 15:53, 1 June 2018 (UTC)
Did you consider variable scope problems? These (constructed) queries have different output: 
SELECT ?label ?l WHERE {
  BIND('de' AS ?lang) .
  OPTIONAL {
    wd:Q4115189 rdfs:label ?label .
    FILTER(LANG(?label) = ?lang) .
  }
  BIND(LANG(?label) AS ?l) .
}
Try it! 
SELECT ?label ?l WHERE {
  OPTIONAL {
    wd:Q4115189 rdfs:label ?label .
    FILTER(LANG(?label) = 'de') .
  }
  BIND(LANG(?label) AS ?l) .
}
Try it!
—MisterSynergy (talk) 17:44, 1 June 2018 (UTC)
SELECT * WHERE 
{
  ?item wdt:P31 wd:Q4167836 ; wdt:P971 wd:Q36224 .
  [] schema:about ?item  ; schema:isPartOf / wikibase:wikiGroup "wiktionary" ; schema:inLanguage ?lang ; schema:name ?title
  FILTER NOT EXISTS { ?item rdfs:label ?l . FILTER (lang(?l)=?lang  ) }
}
LIMIT 250000
Try it!
  • Good point. In at least one query that was a problem. Is it the same with the above? It outputs different results each time one changes the LIMIT (> than potential results).
    --- Jura 18:21, 1 June 2018 (UTC)
    • At least, I think it did ..
      --- Jura 18:23, 1 June 2018 (UTC)
    • That’s weird, but very likely a different problem. A LIMIT with numbers larger than the number of potential results leads to duplication of plenty results. Never seen that… —MisterSynergy (talk) 18:40, 1 June 2018 (UTC)
    • Btw. if you don’t use that weird LIMIT condition, it is interestingly one of the rare situations where FILTER NOT EXISTS{} and MINUS {} yield different results (38 vs 2014). Not sure whether due to variable scope issues or something else… —MisterSynergy (talk) 18:44, 1 June 2018 (UTC)
      • Well, at least the problem is reproducible ;)
        It might be an issue between the multiple servers. "Q8797476 af Kategorie:Voornaamwoorde" appears on top of the MINUS{}-version, but that item has it defined.
        --- Jura 18:57, 1 June 2018 (UTC)
        • I rather suspect variable scope issues here as well. ?lang apparently isn’t visible in the MINUS{} block, thus it effectively removes no rows from the result. 2014 is just the number of results that you get if you remove the last filter completely. —MisterSynergy (talk) 19:10, 1 June 2018 (UTC)

P31/P279 woe

From the above thread ... in the query below, ?carica wdt:P31/wdt:P279* wd:Q3858501. causes zero results; ?carica wdt:P279 wd:Q3858501. gets lots of results. I plain don't understand why the wdt:P31/wdt:P279* fails. Can someone enlighten me, please. What manner of stupid am I suffering from today? --Tagishsimon (talk) 20:44, 1 June 2018 (UTC) 

SELECT distinct ?item ?itemLabel ?office ?officeLabel ?governoLabel ?inizio ?fine
WHERE 
{
 # ?item wdt:P39/wdt:P279* wd:Q3858501 .
  ?item p:P39 ?statement .
  ?statement ps:P39 ?office . 
  ?office wdt:P279* wd:Q3858501.
#  ?office wdt:P31/wdt:P279* wd:Q3858501.
  OPTIONAL{?statement pq:P5054 ?governo. }
  OPTIONAL{?statement pq:P580 ?inizio .}
  OPTIONAL{?statement pq:P582 ?fine .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it". }
}
ORDER BY ?itemLabel ?inizio ?fine
Try it!
Changed the query a bit. The offices have subclass relations to Italian minister (Q3858501), not a property path instance of (P31) -> subclass of (P279)*. Example: . —MisterSynergy (talk) 20:52, 1 June 2018 (UTC)
So is it the case that wdt:P31/wdt:P279* is saying "a P31 value which is a P279 of xyz", rather than "a value which is either of a P31 or a P279 of xyz?" --Tagishsimon (talk) 21:13, 1 June 2018 (UTC)
Yes. Imagine a giant graph of linked nodes where the entire information of Wikidata is linked to each other, and in which you look for subject-object pairs which are connected though a so-called property path (triple patterns). In the simplest case, both subject and object are items and the property path is just a direct link as in the claim of my previous comment. However, the concept is much more general, so that both subjects and objects can have a different type than items (can be: properties, statements, reference handles, sitelinks, wikibase internals, all the other data types, and some more), and the property path does not need to be a direct link. Examples for non-item subjects: in ?statement ps:P39 ?office of the query above, a statement handle is the subject, it appears as something like wds:Q366104-B650064C-589C-4C1A-991D-B28948B53B43. Property paths can link subject and object of a triple pattern via multiple other entites; the path wdt:P31/wdt:P279* finds triples in which the subject (an item or a property in this case) needs to have P31 with a value that itself has an arbitrary number (including zero) of P279 claims to the object. Regex-like quantifiers such as ?, +, and * make the path more flexible. For example, wdt:P31?/wdt:P279* in your example above would also work, since the P31 is now optional due to the question mark.
Worth to mention here that your idea of “a value which is either of a P31 or a P279 of xyz” is also a possible property path: it reads wdt:P31|wdt:P279* with a pipe, not a slash. I recommend reading the SPARQL reference occasionally, although it is a complicated technical document. There are many things one needs to think about over and over again until it becomes clear, but that’s probably something which even the SPARQL experts here have to deal with :-) —MisterSynergy (talk) 21:39, 1 June 2018 (UTC)
Thank you, MisterSynergy. I'm very much obliged & will do the reading :) --Tagishsimon (talk) 21:50, 1 June 2018 (UTC)

Multiple values and ranks

I am still a bit lost in cases with multiple values. I need items that have at least one value with end time (P582) qualifier but no value with preferred rank. 

SELECT ?item ?itemLabel
WHERE
{
	hint:Query hint:optimizer "None" .
    ?item wdt:P31 wd:Q4830453 .
  	?item p:P127 ?vlast .
    ?vlast pq:P582 [] . 
    ?item p:P127 ?vlast2 .  
    FILTER NOT EXISTS  {?vlast2 wikibase:rank wikibase:PreferredRank }.
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
LIMIT 100
Try it!

--Jklamo (talk) 16:37, 2 June 2018 (UTC) 

SELECT DISTINCT ?item WHERE {
  ?item wdt:P31 wd:Q4830453; p:P127/pq:P582 [] .
  MINUS { ?item p:P127/wikibase:rank wikibase:PreferredRank }
}
Try it!
—MisterSynergy (talk) 16:43, 2 June 2018 (UTC)
Thanks!--Jklamo (talk) 16:52, 2 June 2018 (UTC)

Show the value in this query

How is possible show the value of P39 in this query? Add simply ?item wdt:P39 ?carica. don't work. 

SELECT distinct ?item ?itemLabel ?caricaLabel ?governoLabel ?inizio ?fine
WHERE 
{
  ?item wdt:P39/wdt:P279* wd:Q3858501 .
  #?item wdt:P39 ?carica.
  OPTIONAL{?item p:P39 ?statement .}
  {?statement pq:P5054 ?governo .}
  OPTIONAL{?statement pq:P580 ?inizio .}
  OPTIONAL{?statement pq:P582 ?fine .}
   
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it". }
}

ORDER BY ?itemLabel ?inizio ?fine
Try it!

--ValterVB (talk) 17:08, 1 June 2018 (UTC)

@ValterVB: your optional clauses were a little awry. This might be the fix:
SELECT distinct ?item ?itemLabel ?caricaLabel ?governoLabel ?inizio ?fine
WHERE 
{
  ?item wdt:P39/wdt:P279* wd:Q3858501 .
  ?item wdt:P39 ?carica.
  OPTIONAL{?item p:P39 ?statement .}
  OPTIONAL{?statement pq:P5054 ?governo .}
  OPTIONAL{?statement pq:P580 ?inizio .}
  OPTIONAL{?statement pq:P582 ?fine .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it". }
}
ORDER BY ?itemLabel ?inizio ?fine
Try it!
- hth --Tagishsimon (talk) 17:32, 1 June 2018 (UTC)
No, don't work. Look for example in the first item, position held (P39)=member of the Italian Senate (Q13653224) query show this property with qualifier but in item there aren't. --ValterVB (talk) 17:42, 1 June 2018 (UTC)
@ValterVB: True. This may have similar leakage, but it's closer.
SELECT distinct ?item ?itemLabel ?caricaLabel ?governoLabel ?inizio ?fine
WHERE 
{
  ?item wdt:P39/wdt:P279* wd:Q3858501 .
  OPTIONAL{?item p:P39 ?statement .}
  OPTIONAL{?statement ps:P39 ?carica. }
  OPTIONAL{?statement pq:P5054 ?governo. }
  OPTIONAL{?statement pq:P580 ?inizio .}
  OPTIONAL{?statement pq:P582 ?fine .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it". }
}
ORDER BY ?itemLabel ?inizio ?fine
Try it!
--Tagishsimon (talk) 17:52, 1 June 2018 (UTC)
In this case show also the triple that don't fall under ?item wdt:P39/wdt:P279* wd:Q3858501 . --ValterVB (talk) 18:17, 1 June 2018 (UTC)
@ValterVB:. I'm having difficulty with wdt:P31/wdt:P279. Presuming all positions are P279, then we /might/ be there.
SELECT distinct ?item ?itemLabel ?caricaLabel ?governoLabel ?inizio ?fine
WHERE 
{
 # ?item wdt:P39/wdt:P279* wd:Q3858501 .
  ?item p:P39 ?statement .
  ?statement ps:P39 ?carica. 
  ?carica wdt:P279 wd:Q3858501.
  OPTIONAL{?statement pq:P5054 ?governo. }
  OPTIONAL{?statement pq:P580 ?inizio .}
  OPTIONAL{?statement pq:P582 ?fine .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it". }
}
ORDER BY ?itemLabel ?inizio ?fine
Try it!
--Tagishsimon (talk) 18:30, 1 June 2018 (UTC)
This is what I want: look for example Q366104#P39. Prime Minister of Italy (Q796897) and Italian Minister of Justice (Q25973167) are subclass of Italian minister (Q3858501) the other 2 no. in the query I want only Prime Minister of Italy (Q796897) and Italian Minister of Justice (Q25973167) with their qualifiers. --ValterVB (talk) 18:41, 1 June 2018 (UTC)
Prime Minister of Italy (Q796897) is not currently a subclass of Italian minister (Q3858501), which might be part of the problem? --Tagishsimon (talk) 18:54, 1 June 2018 (UTC)
Yes, this is the problem, thanks a lot for the query. --ValterVB (talk) 19:02, 1 June 2018 (UTC)
To have also Prime Minister of Italy (Q796897) I added an UNION and it work. Is there a better system?
SELECT distinct ?item ?itemLabel ?caricaLabel ?governoLabel ?inizio ?fine
WHERE 
{
  {
  ?item p:P39 ?statement .
  ?statement ps:P39 ?carica. 
  ?carica wdt:P279* wd:Q3858501.
  OPTIONAL{?statement pq:P5054 ?governo. }
  OPTIONAL{?statement pq:P580 ?inizio .}
  OPTIONAL{?statement pq:P582 ?fine .}
  }
UNION
 {
  ?item p:P39 ?statement .
  ?statement ps:P39 ?carica.
  ?statement ps:P39 wd:Q796897.
  OPTIONAL{?statement pq:P5054 ?governo. }
  OPTIONAL{?statement pq:P580 ?inizio .}
  OPTIONAL{?statement pq:P582 ?fine .}
 }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "it". }
}
ORDER BY ?caricaLabel ?inizio ?fine
Try it!

--ValterVB (talk) 07:48, 2 June 2018 (UTC)

This, maybe ... not better, but shorter.
SELECT distinct ?item ?itemLabel ?caricaLabel ?governoLabel ?inizio ?fine
WHERE 
{
  VALUES ?role {wd:Q3858501 wd:Q796897}
  ?item p:P39 ?statement .
  ?statement ps:P39 ?carica. 
  ?carica wdt:P279* ?role.
  OPTIONAL{?statement pq:P5054 ?governo. }
  OPTIONAL{?statement pq:P580 ?inizio .}
  OPTIONAL{?statement pq:P582 ?fine .}
 SERVICE wikibase:label { bd:serviceParam wikibase:language "it". }
}
ORDER BY ?caricaLabel ?inizio ?fine
Try it!
--Tagishsimon (talk) 09:55, 2 June 2018 (UTC)
Yes, it work ,is more shorter and more manageble, but strangely it takes a lot more time: 23213 ms versus 616 ms. --ValterVB (talk) 11:22, 2 June 2018 (UTC)
Yup. Jura mentioned that exact point in the lang() and ?lang discussion, above ... VALUES leading to timeouts. I was surprised that ?carica wdt:P279* wd:Q3858501|wd:Q796897. did not work. But it didn't. Another approach seems to be:
SELECT distinct ?item ?itemLabel ?caricaLabel ?governoLabel ?inizio ?fine
WHERE 
{
  ?item p:P39 ?statement .
  ?statement ps:P39 ?carica. 
  {?carica wdt:P279* wd:Q3858501 .}
  UNION
  {?carica wdt:P279* wd:Q796897 .}
  OPTIONAL{?statement pq:P5054 ?governo .}
  OPTIONAL{?statement pq:P580 ?inizio .}
  OPTIONAL{?statement pq:P582 ?fine .}
 SERVICE wikibase:label { bd:serviceParam wikibase:language "it". }
}
ORDER BY ?caricaLabel ?inizio ?fine
Try it!
which is back down to ~850ms. --Tagishsimon (talk) 12:07, 2 June 2018 (UTC)
Perfect, more or less same time and more manageble. Thanks a lot. --ValterVB (talk) 07:07, 3 June 2018 (UTC)

Multiplication (a*b*c); PRODUCT(?factor) instead of SUM(?factor)

Tracked in Phabricator
Task T196872
SELECT ?item ?value
	# (SUM(?factor) as ?a) 
	(GROUP_CONCAT(?factor;separator="*") as ?product)
WHERE
{
	?item wdt:P5236 / wdt:P1181 ?factor .
	?item wdt:P1181 ?value 
}
GROUP BY ?item ?value
Try it!

Is there a way to get the numeric value of ?product in the above query?

I tried to find a Blazegraph function, but I'm not sure if I looked at the right place.
--- Jura 07:19, 3 June 2018 (UTC)

  • The idea is to add a complex constraint on Property talk:P5236 to check if the values are correct.
    Maybe a way to do it is to do a Federated Query to a SPARQL endpoint that has more functions installed.
    --- Jura 21:02, 5 June 2018 (UTC)

Bordered cities

I want to get a list of populated places (not regions!) which share borders but a query always exceeds time limit... 

SELECT DISTINCT ?item1 ?item1Label ?item2 WHERE {
  ?item1 wdt:P31/wdt:P279* wd:Q486972.
 # ?item2 wdt:P31/wdt:P279* wd:Q486972.
  ?item1 wdt:P47 ?item2.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "ru,[AUTO_LANGUAGE],en". }
}
Try it!

How to overcome this? (I expect very little results actually) --Infovarius (talk) 20:48, 3 June 2018 (UTC)

410,759 results (comment out the wikibase:label line). Drop labels or make selects more granular - by continent, etc. --Tagishsimon (talk) 21:18, 3 June 2018 (UTC)
Oh, I don't know how did you get this number but really the result is huge, I didn't expected. Even there are >50 pairs in Africa (I was looking for Brazzavile):
SELECT DISTINCT ?item1 ?item1Label ?item2 ?item2Label WHERE {
  ?item1 (wdt:P31/wdt:P279*) wd:Q515.
  ?item2 (wdt:P31/wdt:P279*) wd:Q515.
  ?item1 wdt:P47 ?item2.
  ?item1 wdt:P17 ?state.
  ?state wdt:P30 wd:Q15.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "ru,[AUTO_LANGUAGE],en". }
}
Try it!

And so many in Japan! --Infovarius (talk) 08:22, 6 June 2018 (UTC)

List of item with P380 = somevalue

Hi,

I know how to get all the items with Mérimée ID (P380) = novalue : 

SELECT ?item ?itemLabel WHERE {
  ?item wdt:P17 wd:Q142 ; rdf:type wdno:P380 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

But how to do the same thing for somevalue ?

Cdlt, VIGNERON (talk) 21:07, 4 June 2018 (UTC) 

SELECT ?item ?itemLabel WHERE {
  ?item wdt:P17 wd:Q142; wdt:P380 ?p380 .
  FILTER(ISBLANK(?p380)) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

—MisterSynergy (talk) 21:09, 4 June 2018 (UTC)

Tahnk you. Cdlt, VIGNERON (talk) 07:16, 5 June 2018 (UTC)

Call for reduction of code length and/or running time

Can anyone help to reduce the code length and/or running time or somehow simplify the following query? It almost exceeds the maximal query length (at least when fed to Template:Wikidata list which seems to add some more code) and often times out! It is used to find duplicates of visual artist items at Wikidata:WikiProject Visual arts/Artists same name. I already had to remove the documentation. This is the structure:

  1. Take visual artist items
  2. Look for matching English labels or aliases in items
  3. Filter out those connected via either different from (P1889), said to be the same as (P460) or sibling (P3373)
  4. For date of birth (P569) and date of death (P570):
    1. Break down to smallest common precision
    2. Calculate difference
  5. Sort by:
    1. mean difference of date of birth (P569) and date of death (P570)
    2. number of date differences
    3. precision of date differences
SELECT DISTINCT ?itemName
                ?item ?dob1dispStr ?dod1dispStr ?occ1
                ?item2 ?dob2dispStr ?dod2dispStr ?occ2
                ?dobdiff ?doddiff ?meandiff ?meanPrec with 
{
SELECT DISTINCT ?item ?item2 ?itemName ?occ1 ?occ2 WHERE {
  ?item wdt:P106 ?occ1 .
  ?occ1 wdt:P279* wd:Q3391743 .
  { ?item rdfs:label ?itemName. } UNION { ?item skos:altLabel ?itemName. }
  ?item2 wdt:P106 ?occ2 .
  ?occ2 wdt:P279* wd:Q3391743 .
  { ?item2 rdfs:label ?itemName. } UNION { ?item2 skos:altLabel ?itemName. }
  FILTER((LANG(?itemName)) = "en")
  FILTER((STR(?item)) < (STR(?item2)))
}
LIMIT 100000
} as %items
where {
include %items
  MINUS { ?item wdt:P1889 \u007C wdt:P460 \u007C wdt:P3373 ?item2. }
  MINUS { ?item2 wdt:P1889 \u007C wdt:P460 \u007C wdt:P3373 ?item. }
  
  OPTIONAL {
    ?item p:P569/psv:P569 [
      wikibase:timeValue ?dob1Val;
      wikibase:timePrecision ?dob1Prec ] .
    BIND(STR(?dob1Val) AS ?dob1fullStr)
    BIND(IF(?dob1Prec <= 10, 3, 0) AS ?dob1insigDayChars)
    BIND(IF(?dob1Prec <= 9, 3, 0) AS ?dob1insigMonthChars)
    BIND(IF(?dob1Prec <= 8, 9-?dob1Prec, 0) AS ?dob1insigYearChars)
    BIND(?dob1insigDayChars + ?dob1insigMonthChars + ?dob1insigYearChars AS ?dob1insigDateChars)
    BIND(SUBSTR(?dob1fullStr, 1, STRLEN(?dob1fullStr) - 10 - ?dob1insigDateChars) AS ?dob1SignStr)
    BIND(CONCAT(?dob1SignStr, SUBSTR("0000000000000000", 1, ?dob1insigYearChars)) AS ?dob1dispStr)
    }

  OPTIONAL {
    ?item2 p:P569/psv:P569 [
      wikibase:timeValue ?dob2Val;
      wikibase:timePrecision ?dob2Prec ] .
    BIND(STR(?dob2Val) AS ?dob2fullStr)
    BIND(IF(?dob2Prec <= 10, 3, 0) AS ?dob2insigDayChars)
    BIND(IF(?dob2Prec <= 9, 3, 0) AS ?dob2insigMonthChars)
    BIND(IF(?dob2Prec <= 8, 9-?dob2Prec, 0) AS ?dob2insigYearChars)
    BIND(?dob2insigDayChars + ?dob2insigMonthChars + ?dob2insigYearChars AS ?dob2insigDateChars)
    BIND(SUBSTR(?dob2fullStr, 1, STRLEN(?dob2fullStr) - 10 - ?dob2insigDateChars) AS ?dob2SignStr)
    BIND(CONCAT(?dob2SignStr, SUBSTR("0000000000000000", 1, ?dob2insigYearChars)) AS ?dob2dispStr)
    }

  BIND(IF(?dob1insigDateChars > ?dob2insigDateChars, ?dob1insigDateChars, ?dob2insigDateChars) AS ?dobComInsigDateChars)
  BIND(SUBSTR(?dob1fullStr, 1, STRLEN(?dob1fullStr) - 10 - ?dobComInsigDateChars) AS ?dob1ComSigStr)
  BIND(SUBSTR(?dob2fullStr, 1, STRLEN(?dob2fullStr) - 10 - ?dobComInsigDateChars) AS ?dob2ComSigStr)
  BIND(STRDT(CONCAT(?dob1ComSigStr, SUBSTR("0000000000000000-01-01T00:00:00Z", 23 - ?dobComInsigDateChars)), xsd:dateTime) AS ?dob1ComSig)
  BIND(STRDT(CONCAT(?dob2ComSigStr, SUBSTR("0000000000000000-01-01T00:00:00Z", 23 - ?dobComInsigDateChars)), xsd:dateTime) AS ?dob2ComSig)
  BIND((CEIL(ABS((?dob1ComSig - ?dob2ComSig) / 3.652425))) / 100 AS ?dobdiff)


  OPTIONAL {
    ?item p:P570/psv:P570 [
      wikibase:timeValue ?dod1Val;
      wikibase:timePrecision ?dod1Prec ] .
    BIND(STR(?dod1Val) AS ?dod1fullStr)
    BIND(IF(?dod1Prec <= 10, 3, 0) AS ?dod1insigDayChars)
    BIND(IF(?dod1Prec <= 9, 3, 0) AS ?dod1insigMonthChars)
    BIND(IF(?dod1Prec <= 8, 9-?dod1Prec, 0) AS ?dod1insigYearChars)
    BIND(?dod1insigDayChars + ?dod1insigMonthChars + ?dod1insigYearChars AS ?dod1insigDateChars)
    BIND(SUBSTR(?dod1fullStr, 1, STRLEN(?dod1fullStr) - 10 - ?dod1insigDateChars) AS ?dod1SignStr)
    BIND(CONCAT(?dod1SignStr, SUBSTR("0000000000000000", 1, ?dod1insigYearChars)) AS ?dod1dispStr)
    }

  OPTIONAL {
    ?item2 p:P570/psv:P570 [
      wikibase:timeValue ?dod2Val;
      wikibase:timePrecision ?dod2Prec ] .
    BIND(STR(?dod2Val) AS ?dod2fullStr)
    BIND(IF(?dod2Prec <= 10, 3, 0) AS ?dod2insigDayChars)
    BIND(IF(?dod2Prec <= 9, 3, 0) AS ?dod2insigMonthChars)
    BIND(IF(?dod2Prec <= 8, 9-?dod2Prec, 0) AS ?dod2insigYearChars)
    BIND(?dod2insigDayChars + ?dod2insigMonthChars + ?dod2insigYearChars AS ?dod2insigDateChars)
    BIND(SUBSTR(?dod2fullStr, 1, STRLEN(?dod2fullStr) - 10 - ?dod2insigDateChars) AS ?dod2SignStr)
    BIND(CONCAT(?dod2SignStr, SUBSTR("0000000000000000", 1, ?dod2insigYearChars)) AS ?dod2dispStr)
    }

  BIND(IF(?dod1insigDateChars > ?dod2insigDateChars, ?dod1insigDateChars, ?dod2insigDateChars) AS ?dodComInsigDateChars)
  BIND(SUBSTR(?dod1fullStr, 1, STRLEN(?dod1fullStr) - 10 - ?dodComInsigDateChars) AS ?dod1ComSigStr)
  BIND(SUBSTR(?dod2fullStr, 1, STRLEN(?dod2fullStr) - 10 - ?dodComInsigDateChars) AS ?dod2ComSigStr)
  BIND(STRDT(CONCAT(?dod1ComSigStr, SUBSTR("0000000000000000-01-01T00:00:00Z", 23 - ?dodComInsigDateChars)), xsd:dateTime) AS ?dod1ComSig)
  BIND(STRDT(CONCAT(?dod2ComSigStr, SUBSTR("0000000000000000-01-01T00:00:00Z", 23 - ?dodComInsigDateChars)), xsd:dateTime) AS ?dod2ComSig)
  BIND((CEIL(ABS((?dod1ComSig - ?dod2ComSig) / 3.652425))) / 100 AS ?doddiff)
  
  BIND(BOUND(?dobdiff) AS ?dobdiffbound)
  BIND(BOUND(?doddiff) AS ?doddiffbound)

  BIND(IF(BOUND(?dobdiff), ?dobdiff, 0) AS ?dobdiffSf)
  BIND(IF(BOUND(?doddiff), ?doddiff, 0) AS ?doddiffSf)
  BIND((xsd:integer(?dobdiffbound) + xsd:integer(?doddiffbound)) AS ?numOfDateDiffs)
  BIND((CEIL((?dobdiffSf + ?doddiffSf) / ?numOfDateDiffs * 100) / 100) AS ?meandiff)

  BIND(IF(?dob1Prec < ?dob2Prec, ?dob1Prec, ?dob2Prec) AS ?dobComPrec)
  BIND(IF(?dod1Prec < ?dod2Prec, ?dod1Prec, ?dod2Prec) AS ?dodComPrec)
  BIND(BOUND(?dobComPrec) AS ?dobComPrecBound)
  BIND(BOUND(?dodComPrec) AS ?dodComPrecBound)
  BIND(IF(BOUND(?dobComPrec), ?dobComPrec, 0) AS ?dobComPrecSf)
  BIND(IF(BOUND(?dodComPrec), ?dodComPrec, 0) AS ?dodComPrecSf)
  BIND((xsd:integer(?dobComPrecBound) + xsd:integer(?dodComPrecBound)) AS ?numOfComPrecBound)
  BIND((?dobComPrecSf + ?dodComPrecSf) / ?numOfComPrecBound AS ?meanPrec)
}
ORDER BY (!(?dobdiffbound) && !(?doddiffbound)) ?meandiff (?numOfDateDiffs * -1) (?meanPrec * -1) ?itemName
LIMIT 500
Try it!

Thanks a lot for any help! And thanks for pinging me! --Marsupium (talk) 13:04, 8 June 2018 (UTC), 13:07, 8 June 2018 (UTC)

Rank

SELECT ?item ?label
WHERE
{
  	{ ?item wdt:P131 wd:Q59150 }
}
Try it!
  • How to have only the items that have rank=preffered?
  • How to have only the items that are not rank=deprecated?

Xaris333 (talk) 19:06, 8 June 2018 (UTC)

@Xaris333: I think maybe something like this ... the query, as is, finds NormalRank values, but comment out that line and choose one of the others for +Preferred or -Deprecated. As far as I can see, none of the item P131 statements have anything other than normal rank. See RDF_Dump_Format#Statement_representation for more details.
SELECT ?item ?itemLabel
WHERE
{
  ?item p:P131 ?statement .
  ?statement ps:P131 wd:Q59150 .
  ?item p:P131/wikibase:rank wikibase:NormalRank .              # comment this line out and substitute one of the commented-out lines below
#  ?item p:P131/wikibase:rank wikibase:PreferredRank .          # item statement must have preferred rank
#  minus {?item p:P131/wikibase:rank wikibase:DeprecatedRank .} # item statement must not have deprecated rank
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Tagishsimon (talk) 20:59, 8 June 2018 (UTC)
rank=preferred 
SELECT ?item { ?item p:P131 [ ps:P131 wd:Q59150; wikibase:rank wikibase:PreferredRank ] . }
Try it!
rank=!deprecated 
SELECT ?item { ?item p:P131 [ ps:P131 wd:Q59150; wikibase:rank ?rank ] . FILTER( ?rank != wikibase:DeprecatedRank ) . }
Try it!
Matěj Suchánek (talk) 07:49, 9 June 2018 (UTC)

Union

Hello. I want to find the items with wdt:P131 wd:Q59150 and wdt:P31 wd:Q29414133 PLUS the items with wdt:P131 wd:Q59150 and wdt:P31 wd:Q16739079 .

This is not working. 

SELECT ?item ?itemLabel ?any
WHERE
{
 ?item wdt:P131 wd:Q59150 .
  ?item wdt:P31 wd:Q29414133 .
  UNION
 ?item wdt:P131 wd:Q59150 .
  ?item wdt:P31 wd:Q16739079 . 
}
Try it!

Xaris333 (talk) 22:09, 8 June 2018 (UTC)

@Xaris333: UNION seems to unionise statements which precede & succeed it, and which are wrapped in their own {}. So it would have been possible to wrap both of your first two ?item lines in a single {} and ditto your second two ?item lines ... but since the P131 is common, the below is probably the way to go.
SELECT ?item ?itemLabel
WHERE
{
  ?item wdt:P131 wd:Q59150 .
  {?item wdt:P31 wd:Q29414133 .}
  UNION
  {?item wdt:P31 wd:Q16739079 . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Tagishsimon (talk) 22:37, 8 June 2018 (UTC)
SELECT DISTINCT ?item { VALUES ?class { wd:Q16739079 wd:Q29414133 } . ?item wdt:P131 wd:Q59150; wdt:P31 ?class . }
Try it!
Matěj Suchánek (talk) 07:47, 9 June 2018 (UTC)

How extract human name information from wikidata API?

i have a problem understanding how wikidata.org is working ! even the documentation is not clear as facebook API , well my target is to downliad

ALL available human names (full name , first name (in all languages )) + human language + Gender + country.

Please do not -1 , i am learning and honestly i tried a lot but it is very obscure how wikidata query is working.

and how can i download all database without timeout ?  – The preceding unsigned comment was added by 194.68.44.79 (talk • contribs) at 9. 6. 2018, 05:23 (UTC).

You will find more information at Wikidata:Data access. Matěj Suchánek (talk) 07:45, 9 June 2018 (UTC)

Creating a query making a list of all norwegians born between the year 1710 to 1911

i am easily able to create a list of all Norwegians on wikidata (44500 abt.). But can anyone show me how to limit the list to persons born between 1710 to 1911. Breg Pmt (talk) 15:32, 9 June 2018 (UTC)

@Pmt: like this:
SELECT ?item ?itemLabel ?dob
WHERE 
{ ?item wdt:P27 wd:Q20.               # country of citizenship=Norway
  ?item wdt:P569 ?dob .               # must have dob
  bind (year(?dob) as ?year)          # convert dob to years
  FILTER (?year >= 1710)              # filter for dob later or equal to 1710
  FILTER (?year <= 1911)              # filter for dob prior to or equal to 1911
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],no". } # get labels
} order by ?dob
Try it!
--Tagishsimon (talk) 17:17, 9 June 2018 (UTC)
If the dob presented in the query result is to be used further it is important to know that not all presented "January 1st" is a date with day precision. It could be a date with less precision like month, year, decade or century precision. In the following query I have added a column in the result to show the time precison for the dob.
SELECT ?item ?itemLabel ?dob ?precision
WHERE 
{ ?item wdt:P27 wd:Q20.               # country of citizenship=Norway
  ?item p:P569 ?p569stm .
  ?p569stm ps:P569 ?dob .             # must have dob
  ?p569stm psv:P569 [wikibase:timePrecision ?dp] . # find out time precision
  BIND(IF(?dp=11,'day',IF(?dp=10,'month',IF(?dp=9,'year',IF(?dp=8,'decade',IF(?dp=7,'century','other'))))) AS ?precision) # convert code for time precision to English
  BIND (year(?dob) as ?year)          # convert dob to years
  FILTER (?year >= 1710)              # filter for dob later than or equal to 1710
  FILTER (?year <= 1911)              # filter for dob prior to or equal to 1911
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],no,en". } # get labels
} ORDER BY ?dob
Try it!
If you sort the result by item rather than by dob you will find that there are some items with more than one dob. The following query lists them with an additional column specifying the type of problem. It can either be two dates with different time precision or two (or more) dates with the same time precision but different time values.
SELECT ?item ?itemLabel ?dob1 ?precision1 ?dob2 ?precision2 ?problem_type
WHERE 
{ ?item wdt:P27 wd:Q20.               # country of citizenship=Norway
  ?item p:P569 ?p569stm1 .
  ?p569stm1 ps:P569 ?dob1 .             # must have dob
  ?p569stm1 psv:P569 [wikibase:timePrecision ?dp1] . # find out time precision
  ?item p:P569 ?p569stm2 .
  ?p569stm2 ps:P569 ?dob2 .
  ?p569stm2 psv:P569 [wikibase:timePrecision ?dp2] .
  FILTER(?dp1<?dp2 || (?dp1=?dp2 && ?dob1<?dob2))                  # more than one dob and with different precision or with same precision but different date
  BIND(IF(?dp1<?dp2,'different time precision','different dates') AS ?problem_type)
  BIND(IF(?dp1=11,'day',IF(?dp1=10,'month',IF(?dp1=9,'year',IF(?dp1=8,'decade',IF(?dp1=7,'century','other'))))) AS ?precision1) # convert code for time precision to English
  BIND(IF(?dp2=11,'day',IF(?dp2=10,'month',IF(?dp2=9,'year',IF(?dp2=8,'decade',IF(?dp2=7,'century','other'))))) AS ?precision2) # convert code for time precision to English
  BIND (year(?dob1) as ?year)          # convert dob to years
  FILTER (?year >= 1710)              # filter for dob later than or equal to 1710
  FILTER (?year <= 1911)              # filter for dob prior to or equal to 1911
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],no,en". } # get labels
} ORDER BY ?itemLabel
Try it!
--Larske (talk) 19:37, 9 June 2018 (UTC)

ISNI - GROUP and COUNT

Below ISNI are grouped, maximum found is two. But COUNT gives higher numbers, even for single-ISNI-items e.g.

  • Q551845 Emil Bretschneider 0000000110264673,0000000083454614 8
  • Q21607917 Carl Christian Schiemann 0000000118918926 8
SELECT ?human ?humanLabel ?sl
(GROUP_CONCAT(DISTINCT ?dateOfBirth) AS ?dateOfBirths)
(GROUP_CONCAT(DISTINCT ?dateOfDeath) AS ?dateOfDeaths)
(GROUP_CONCAT(DISTINCT ?bblid) AS ?bblids) 
(GROUP_CONCAT(DISTINCT (replace(?isni, " ", "")); SEPARATOR = ",") AS ?isnis)
(GROUP_CONCAT(DISTINCT ?viaf) AS ?viafs) 
(GROUP_CONCAT(DISTINCT ?gnd) AS ?gnds) 
(GROUP_CONCAT(DISTINCT ?geni) AS ?genis)
(COUNT(?isni) AS ?isniCount)
WHERE {
    ?human wdt:P31 wd:Q5 .      
    ?human wdt:P2580 ?bblid .
    ?human wikibase:sitelinks ?sl.
    OPTIONAL{?human wdt:P569 ?dateOfBirth .}
    OPTIONAL{?human wdt:P570 ?dateOfDeath .}
    {?human wdt:P213 ?isni .}
    OPTIONAL{?human wdt:P214 ?viaf .}
    optional{?human wdt:P227 ?gnd .}
    OPTIONAL{?human wdt:P2600 ?geni .}
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}
GROUP BY ?human ?humanLabel ?sl
ORDER BY DESC(?isniCount) DESC(?sl) ?dateOfBirths ?dateOfDeaths
Try it!

85.180.29.96 08:43, 10 June 2018 (UTC)

You need to (COUNT(DISTINCT ?isni) AS ?isniCount) to get a correct count. —MisterSynergy (talk) 09:14, 10 June 2018 (UTC)
MisterSynergy, confirmed, thanks a lot! 85.180.29.96 10:10, 10 June 2018 (UTC)

Look after an IATA or ICAO code

Hello, is it possible for, say, look after the wikidata element for an airport with IATA code = AAA or ICAO code = NTGA ? I'd like to see one result with name of airport, wikilink to English wikipedia page, name of city served by the airport. Thanks ! --Bouzinac (talk) 07:16, 3 June 2018 (UTC)

@Bouzinac: it is; the query below meets the exact letter of your specification; but by doing do, is not very general.
SELECT distinct ?item ?itemLabel ?sitelink ?place_servedLabel WHERE {
  {?item wdt:P238 "AAA" .}                    #item has a P238 value of AAA                
  UNION                                       # and/or
  {?item wdt:P239 "NTGA" .}                   #item has a P239 of NTGA          
  optional {?item wdt:P931 ?place_served .}   #item may have a value for P931, which is put into a variable ?place_served               
  ?sitelink schema:about ?item;                             #item has a sitelink
            schema:isPartOf <https://en.wikipedia.org/>.    #the sitelink points to en.wikipedia
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } . #this bit gets labels for Qid values
}
Try it!
--Tagishsimon (talk) 15:47, 3 June 2018 (UTC)
Yes, thank you. And what about same result for all IATA/ICAO of airports from country X, say, France? Thanks!--Bouzinac (talk) 08:37, 9 June 2018 (UTC)
@Bouzinac: Perhaps like this - presuming we're using the item's country (P17) to determine country, rather than looking for the P17 of the place served by transport hub (P931). Note the approach is completely different: now we check that the item is an instance of an airport, rather than relying merely on it having an IATA or ICAO code.
SELECT distinct ?item ?itemLabel ?sitelink ?place_servedLabel ?iata ?icao WHERE {
  ?item wdt:P31/wdt:P279* wd:Q1248784.                     # item must be an instance of an airport, or of a subclass of an airport
  optional {?item wdt:P238 ?iata .}                        # item may have a P238 (IATA) value
  optional {?item wdt:P239 ?icao .}                        # item may have has a P239 (ICAO) value         
  optional {?item wdt:P931 ?place_served .}                # item may have a value for P931, which is put into a variable ?place_served               
  ?item wdt:P17 wd:Q142 .                                  # ?item has country = France
  ?sitelink schema:about ?item;                            # item has a sitelink
            schema:isPartOf <https://en.wikipedia.org/>.   # the sitelink points to en.wikipedia
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } . #this bit gets labels for Qid values
} order by ?itemLabel
Try it!
and like this if we base it on the P17 of the place served by transport hub (P931). Note that both result sets will give us duplicates of airports, where an airport has more than one IATA or ICAO code, or more than one place served - e.g. for EuroAirport Basel-Mulhouse-Freiburg (Q156971).
SELECT distinct ?item ?itemLabel ?sitelink ?place_servedLabel ?iata ?icao WHERE {
  ?item wdt:P31/wdt:P279* wd:Q1248784.                     # item must be an instance of an airport, or of a subclass of an airport
  optional {?item wdt:P238 ?iata .}                        # item may have a P238 (IATA) value
  optional {?item wdt:P239 ?icao .}                        # item may have has a P239 (ICAO) value         
  ?item wdt:P931 ?place_served .                           # item has a value for P931, which is put into a variable ?place_served               
  ?place_served wdt:P17 wd:Q142 .                          # ?place_served has country = France
  ?sitelink schema:about ?item;                            # item has a sitelink
            schema:isPartOf <https://en.wikipedia.org/>.   # the sitelink points to en.wikipedia
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } . #this bit gets labels for Qid values
} order by ?itemLabel
Try it!
--Tagishsimon (talk) 09:50, 9 June 2018 (UTC)
And, to avoid the duplicate rows, this might be an approach:
SELECT ?item ?itemLabel ?sitelink (group_concat(distinct ?serve;separator=", ") as ?serves) (group_concat(distinct ?iata_c;separator=", ") as ?iata) (group_concat(distinct ?icao_c;separator=", ") as ?icao)
WHERE {
  ?item wdt:P31/wdt:P279* wd:Q1248784.                     # item must be an instance of an airport, or of a subclass of an airport
  optional {?item wdt:P238 ?iata_c .}                      # item may have a P238 (IATA) value
  optional {?item wdt:P239 ?icao_c .}                      # item may have has a P239 (ICAO) value         
  optional {?item wdt:P931 ?place_served .                 # item may have a value for P931  
           ?place_served rdfs:label ?serve.                # and if so, we get the label for the value directly (so group_concat will work)
           filter(lang(?serve)="en")}                      # but we only get the english language label                  
  ?item wdt:P17 wd:Q142 .                                  # ?item has country = France
  ?sitelink schema:about ?item;                            # item has a sitelink
            schema:isPartOf <https://en.wikipedia.org/>.   # the sitelink points to en.wikipedia
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } . #this bit gets labels for Qid values
} group by ?item ?itemLabel ?sitelink order by ?itemLabel
Try it!
--Tagishsimon (talk) 10:15, 9 June 2018 (UTC)
Thanks a lot Tagishsimon (talk • contribs • logs), the latter is almost perfect. I've happened across some "errors" and I wonder how to filter the defuncts airports + those that haven't even opened (projects). Is it possible? :) --Bouzinac (talk) 20:23, 9 June 2018 (UTC)
@Bouzinac: that depends on whether there's any/enough data to identify these two conditions, e.g. by start or inception dates, end dates, etc. Can you point me to any examples? We can then go fishing. --Tagishsimon (talk) 21:08, 9 June 2018 (UTC)
Also, it's possible to identify (some) defunct airports via petscan and then use the list of Qids to drive reports, exclude from reports, or drive quickstatements. --Tagishsimon (talk) 22:15, 9 June 2018 (UTC)

Yes, for instance closed airport : Orly Air Base (Q7103340) ; example of project (airport whose project has even been abandoned) Aéroport du Grand Ouest (Q140205) Thank you! --Bouzinac (talk) 08:07, 10 June 2018 (UTC)

@Bouzinac: sadly Aéroport du Grand Ouest (Q140205) and Sälen/Scandinavian Mountains Airport (Q10687975) seem to be the only airports which have a P31 of project (and which have en.wikipedia links - the query still demands these ... though even removing that requirement finds no more). So maybe we don't have more items, maybe we don't have data denoting project status, or maybe there's some other way of denoting project status:
SELECT ?item ?itemLabel ?sitelink (group_concat(distinct ?serve;separator=", ") as ?serves) (group_concat(distinct ?iata_c;separator=", ") as ?iata) (group_concat(distinct ?icao_c;separator=", ") as ?icao)
WHERE {
  ?item wdt:P31/wdt:P279* wd:Q62447.                     # item must be an instance of an airport, or of a subclass of an airport
  ?item wdt:P31/wdt:P279* wd:Q170584.                      # item must also be an instance of a project, or of a subclass of an airport
  optional {?item wdt:P238 ?iata_c .}                      # item may have a P238 (IATA) value
  optional {?item wdt:P239 ?icao_c .}                      # item may have has a P239 (ICAO) value         
  optional {?item wdt:P931 ?place_served .                 # item may have a value for P931  
           ?place_served rdfs:label ?serve.                # and if so, we get the label for the value directly (so group_concat will work)
           filter(lang(?serve)="en")}                      # but we only get the english language label                  
  ?sitelink schema:about ?item;                            # item has a sitelink
            schema:isPartOf <https://en.wikipedia.org/>.   # the sitelink points to en.wikipedia
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } . #this bit gets labels for Qid values
} group by ?item ?itemLabel ?sitelink order by ?itemLabel
Try it!
We can look at end times per Orly Air Base (Q7103340) - again, we're still demanding an en.wiki sitelink. I've used a list of different items that are airports (and there's probably a better way to do that bit, but I've not cracked it yet - look for a P31/P279* of aerodrome (Q62447) but using p: and ps: rather than wdt:), and the end_date values resolve to 1 January in that year if the underlying data is merely a year value, so there's more that could be done to improve that column. But, at least it illustrates the report principle and - given ~40 results - again points to likely shortcomings in data; or that endedness is respresented in some other way. Note that in this set of results, we're getting multiple rows for an airport which has closed and opened several times - such as Butzweilerhof (Q1431809). As with the IATA and ICAO codes, it's possible to rewrite to concatenate a set of date values so we end up with a single row per airport. Still: my expectation is that the majority of defunct airports probably lack an indication of their defunct status :(
SELECT ?item ?itemLabel ?sitelink (group_concat(distinct ?serve;separator=", ") as ?serves) (group_concat(distinct ?iata_c;separator=", ") as ?iata) (group_concat(distinct ?icao_c;separator=", ") as ?icao) ?end_date
WHERE {
  VALUES ?type {wd:Q1248784 wd:Q695850 wd:Q62447 }         # define a list of airport / military airbase / aerodrome 
  ?item p:P31 ?statement.                                  # item has a P31 property
  ?statement ps:P31 ?type.                                 # which resolves to something in the list above
  ?statement pq:P582 ?end_date.                            # and which has an end time qualifier
  optional {?item wdt:P238 ?iata_c .}                      # item may have a P238 (IATA) value
  optional {?item wdt:P239 ?icao_c .}                      # item may have has a P239 (ICAO) value         
  optional {?item wdt:P931 ?place_served .                 # item may have a value for P931  
           ?place_served rdfs:label ?serve.                # and if so, we get the label for the value directly (so group_concat will work)
           filter(lang(?serve)="en")}                      # but we only get the english language label
  ?sitelink schema:about ?item;                            # item has a sitelink
            schema:isPartOf <https://en.wikipedia.org/>.   # the sitelink points to en.wikipedia
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } . #this bit gets labels for Qid values
} group by ?item ?itemLabel ?sitelink ?end_date order by ?itemLabel
Try it!
--Tagishsimon (talk) 22:39, 11 June 2018 (UTC)

Query the server config?

Is there a way to get information about the query server through sparql? e.g. the software being used?

What triples are available beyond the ones that come from items and properties? I did find https://query.wikidata.org/#SELECT%20%2a%20%7B%20wikibase%3ADump%20%3Fp%20%3Fo%20%7D
--- Jura 11:06, 8 June 2018 (UTC)

I don't think SPARQL has such data. The database is the same for every server, so it can't contain details about the particular server. That said, which data are you interested in? Maybe there's some other way to get it. Smalyshev (WMF) (talk) 19:30, 11 June 2018 (UTC)

Is there a way to get list of typical file extensions of source code files?

For example .py, .c, .cbl, .java... Mateusz Konieczny (talk) 07:12, 11 June 2018 (UTC)

@Mateusz Konieczny: perhaps just extensions associated with programming languages, rather than specificaly for source code:
SELECT distinct ?item ?itemLabel ?langLabel ?ext
where {
   { ?item wdt:P31 wd:Q235557.         # Instance of file format
    ?item wdt:P277 ?lang. }            # must be a programming language
  UNION                                # and/or
   { ?item wdt:P31/wdt:P279* wd:Q9143 .       # Instance of programming language
     ?item rdfs:label ?langLabel.      # and get its Label 
     filter(lang(?langLabel)="en")}    # in English 
  ?item wdt:P1195 ?ext                 # must have a file extension
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } # get Labels
} order by ?langLabel ?ext
Try it!
--Tagishsimon (talk) 07:51, 11 June 2018 (UTC)
Thank you very much! I though that this data is not present in Wikidata! I tried searching for "py" https://www.wikidata.org/w/index.php?title=Special:Search&limit=500&offset=0&profile=default&search=.py&searchToken=58xcafsmr4zevjfcbzal6cci9 and tried searching for .py at https://www.wikidata.org/wiki/Q28865 Mateusz Konieczny (talk) 17:03, 11 June 2018 (UTC)

Union of two instances of (P31)

How can I create a Query where I both have

⟨ P31 ⟩ Q51077473 Search ⟨ object or value ⟩

and

⟨ P31 ⟩ Q18636219 Search ⟨ object or value ⟩

I tried something like below but Ii may need a statement like UNION? 

SELECT ?Wikidata_egenskap_med_datatype__tidspunkt_ ?Wikidata_egenskap_med_datatype__tidspunkt_Label WHERE {
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
 ?Wikidata_egenskap_med_datatype__tidspunkt_ wdt:P31 wd:Q18636219.
 ?Wikidata_egenskap_med_datatype__tidspunkt_ wdt:P31 wd:Q51077473.

} LIMIT 100

Best regards Pmt (talk) 17:30, 11 June 2018 (UTC) 

SELECT DISTINCT ?item ?itemLabel
WHERE
{
  { ?item wdt:P31 wd:Q18636219 . }
  UNION
  { ?item wdt:P31 wd:Q51077473. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 100

Try it! 

SELECT DISTINCT ?item ?itemLabel
WHERE
{
  VALUES ?instanceof { wd:Q18636219 wd:Q51077473 }
  ?item wdt:P31 ?instanceof . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 100
Try it!

One of the above two.
--- Jura 17:37, 11 June 2018 (UTC)

Thank you breg Pmt (talk) 18:52, 11 June 2018 (UTC)

Using federation

3 or 9

SELECT * WHERE
{
    # { BIND(1 as ?a) } UNION { BIND(2 as ?a) } UNION { BIND(3 as ?a) }  
    # VALUES ?a { 1 2 3 }
    # { SELECT ?a { ?n wdt:P5176 ?a . FILTER(xsd:integer(?a) < 4 ) } }
    SERVICE <https://query.wikidata.org/bigdata/namespace/dcatap/sparql> { SELECT ?s ?p ?o { ?s ?p ?o } LIMIT 1 }
}
Try it!

Any idea why the first line when uncommented gets me 3, the other 9 results? How to change the others to get 3 (without using distinct)?

1 or 3

SELECT * WHERE
{
    # { BIND(1 as ?a) } UNION { BIND(2 as ?a) } UNION { BIND(3 as ?a) }  
    # VALUES ?a { 1 2 3 }
    # { SELECT ?a { ?n wdt:P5176 ?a . FILTER(xsd:integer(?a) < 4 ) } }
    SERVICE <https://query.wikidata.org/bigdata/namespace/dcatap/sparql>  { SELECT ?a ?s ?p ?o { ?s ?p ?o } LIMIT 1 }
}
Try it!

Any idea why the first line when uncommented gets me 3, the other 1 result? How to change the others to get 3?
--- Jura 12:16, 12 June 2018 (UTC)

Labels in Sami languages in Wikidata

Hello! I haven't even tried to formulate Sparql-queries in Wikidata myself, so I would need help with my query need: Is it possible to find out how many items in Wikidata have labels in different Sami languages, especially in Northern Sami (Q33947), Skolt Sami (Q13271) and Inari Sámi (Q33462)? (I'm not even sure, if there are labels in all those three...) And how to get lists of items which have e.g. a Finnish label, but the Northern / Skolt / Inari Sámi label is missing? With kind regards, grateful for your help Pia Virtanen / Yle, the Finnish Broadcasting Company

@‎Pikrvi: others will be able to say for sure, but ... I've found labels in Northern Sami & Southern Sami, but no other branch of the language. (I see in preferences-languages that there's mention of Lule Sami in the translation list.).
To the limit of my skills, there's a time-out problem counting the number of items with a label in a given language, so I can't give you any counts, sadly.
Here, fwiw, is a simple report restricted to the set of items for which the P17 Country property=Finland, showing items with Northern Sami labels. Change the filter to "sma" for Southern. You could try any of the other Sami lanuage codes in the filter (see, for instance, the list in the infobox of Sami_languages).
SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P17 wd:Q33.
  ?item rdfs:label ?itemLabel.
  filter(lang(?itemLabel)="se")
  }
Try it!
And here - again with the Country=Finland stipulation so we get some results back before the time-out - is a report on items with a Finnish language label but without a Northern Sami label. Taking the two reports together, we can see that there are ~100 times as many Finnish labels as Northern Sami.
SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P17 wd:Q33.                              # item has Country=Finland
  ?item rdfs:label ?itemLabel.                       # and has a label
  filter(lang(?itemLabel)="fi")                      # in Finnish
  filter not exists {                                # and does not have
    ?item rdfs:label ?itemLabel2.                    # a label
    filter(lang(?itemLabel2)="se")                   # in Northern Sami 
  }
}
Try it!
- hth --Tagishsimon (talk) 16:03, 13 June 2018 (UTC)

Can't figure out how to exclude statements

Sorry for the artificial example, but I'm having trouble excluding things from a query result. I can do it easily for specific individuals using FILTER and booleans like so:

The following query uses these:

  • Properties: sibling (P3373)     
    SELECT DISTINCT ?include ?includeLabel
WHERE {
  wd:Q36322 wdt:P3373 ?include. 
  FILTER (?include != wd:Q3101398 && ?include != wd:Q2505245)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

In my real case, though, the values to be excluded must be derived rather than hardcoded. The situation can be approximated using the VALUES clause like so:

The following query uses these:

  • Properties: sibling (P3373)     
    SELECT DISTINCT ?include ?includeLabel
WHERE {
  VALUES ?exclude {
    wd:Q3101398
    wd:Q2505245
  }
  
  wd:Q36322 wdt:P3373 ?include. 
  FILTER (?include != ?exclude)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

I understand why this particular FILTER solution doesn't work, but I can't figure out an alternative that does. I've tried variation on FILTER NOT EXISTS and/or MINUS, but to no avail. Maybe it's not even possible? Any clues would be greatly appreciated. Thanks! Realworldobject (talk) 20:39, 13 June 2018 (UTC)

I'm not sure whether I get you correctly. Does something like this help?
SELECT DISTINCT ?include ?includeLabel
WHERE {
  VALUES ?exclude {
    wd:Q3101398
    wd:Q2505245
  } .
  wd:Q36322 wdt:P3373 ?include .
  MINUS { wd:Q36322 wdt:P3373 ?exclude } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
Matěj Suchánek (talk) 07:58, 14 June 2018 (UTC)
SELECT DISTINCT ?include ?includeLabel
WHERE {
  wd:Q36322 wdt:P3373 ?include .
  FILTER NOT EXISTS { 
    wd:Q36322 wdt:P3373 ?include .
    VALUES ?include { wd:Q3101398 wd:Q2505245 }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

Number of International Masters awarded per year

How to build a query that queries the number of title of chess person (P2962): International Master (Q752119) that have been awared in a specific year (start time (P580)), e. g. 1995? This query gives me a list of respective items, but I just need the number of those items, not the list: 

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!

Steak (talk) 07:27, 15 June 2018 (UTC) 

SELECT ?year (COUNT(*) AS ?cnt) WHERE {
  ?item p:P2962 [ ps:P2962 wd:Q752119; pq:P580 ?start ] .
  BIND(YEAR(?start) AS ?year) .
} GROUP BY ?year ORDER BY ASC(?year)
Try it!
This should give you counts for all years. If you are interested in only one year, go with 
SELECT (COUNT(*) AS ?cnt) WHERE {
  ?item p:P2962 [ ps:P2962 wd:Q752119; pq:P580 ?start ] .
  FILTER(YEAR(?start) = 1995) .
}
Try it!
—MisterSynergy (talk) 08:04, 15 June 2018 (UTC)
Thank you! Steak (talk) 08:11, 15 June 2018 (UTC)

Colgar una traducción

Hola! No es la primera vez que hago traducciones para wikipedia y que éstas se publican (y de hecho siguen publicadas) en dicha fuente didáctica. Antes el procedimiento era mucho más sencillo que el actual, ahora hay que leerse la página "Ayuda:¿Cómo añadir enlaces interlingüísticos?" desde el principio hasta el final pero sin conseguir el objetivo final... ¿Alguien podría ayudarme? Se trata de la versión al español de "Weinheim"... Gracias de antemano y saludos de Marinabeatriz  – The preceding unsigned comment was added by Marinabeatriz (talk • contribs) at 19:30, 12 June 2018 (UTC).

Resuelto. --Marsupium (talk) 16:40, 16 June 2018 (UTC)
I think that this discussion is resolved and can be archived. If you disagree, don't hesitate to replace this template with your comment. Marsupium (talk) 16:40, 16 June 2018 (UTC)

All family names

I've been trying to get a list of all instance of (P31)=family name (Q101352) items (for Wikidata:Requests for permissions/Bot/Pi bot 5). A single query times out, so I've been fetching them in sets of 500 and stepping an offset, see the query below. However, that times out with offsets in the 40k region. Can anyone suggest a better query / way to get the complete list? Thanks. Mike Peel (talk) 22:18, 15 June 2018 (UTC) 

SELECT ?item ?itemLabel WHERE {
 ?item wdt:P31 wd:Q101352 .
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } .
} LIMIT 500 OFFSET 42000
Try it!
A complete list in one step is probably not possible, but I’d suggest to go with this strategy: 
SELECT ?item ?itemLabel WITH {
  SELECT DISTINCT ?item WHERE {
    ?item wdt:P31 wd:Q101352 .
  } OFFSET 200000 LIMIT 10000 
} AS %s WHERE {
  INCLUDE %s .
  SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE],en' }
}
Try it!
Here demonstrated with a 10k limit and a 200k offset (~30 s execution time). There are ~243k family items in total, thus I think you should be able to access all of them with this strategy. Not sure, however, whether you really want to look for English labels. native label (P1705) might be better suited, as you don’t make mistakes when transcriptions are involved. —MisterSynergy (talk) 22:32, 15 June 2018 (UTC)
@MisterSynergy: Thanks! I'll give that a go. native label (P1705) would be good, but it's only used in ~286k items, not all of which are family items. If there's a way to check that, and fall back to the English label if it exists, that might be useful though. Mike Peel (talk) 22:41, 15 June 2018 (UTC)
That ran nicely in steps of 1k up to the 243k mark, thanks! Mike Peel (talk) 23:08, 15 June 2018 (UTC)

Anarchists with featured or good articles on any language Wikipedias

#Anarchists with featured or good articles on any language Wikipedias
SELECT ?item ?itemLabel ?lang ?reviewedstatusLabel ?sitelink WHERE {
  ?item wdt:P31 wd:Q5.
  ?item wdt:P1142 wd:Q6199.
  ?sitelink schema:about ?item.
  ?sitelink wikibase:badge ?reviewedstatus.
  VALUES ?reviewedstatus {
    wd:Q17437796  # featured
    wd:Q17437798  # good
  }   
  ?sitelink schema:inLanguage ?lang .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?itemLabel
Try it!

I'd like to:

  1. combine the rows such that Alexander Berkman would show as "en, pt" rather than separate rows for "en" and "pt"
  2. map the respective sitelinks to the language abbreviation [[:pt:Alexander Berkman|pt]]

Any ideas? czar 12:09, 18 June 2018 (UTC)

@Czar: something like this - we can haggle about whether article quality should be grouped or not, and if so whether we want a distinct list rather than repetitions...
SELECT ?item ?itemLabel (group_concat(?lang;separator=", ") as ?languages) (group_concat(?rsl;separator=", ") as ?statuses) (group_concat(?a_n;separator=", ") as ?article_links) WHERE {
  ?item wdt:P31 wd:Q5.
  ?item wdt:P1142 wd:Q6199.
  ?sitelink schema:about ?item.
  ?sitelink wikibase:badge ?reviewedstatus.
  ?reviewedstatus rdfs:label ?rsl. filter(lang(?rsl)="en")
  ?sitelink schema:name ?sitelink_label .
  ?sitelink schema:inLanguage ?lang . 
  bind(concat("[[:",str(?lang),":", str(?sitelink_label), "|", str(?lang),"]]") as ?a_n)
  VALUES ?reviewedstatus {
    wd:Q17437796  # featured
    wd:Q17437798  # good
  }   
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?item ?itemLabel
ORDER BY ?itemLabel
Try it!
- hth - --Tagishsimon (talk) 17:34, 18 June 2018 (UTC)
Great—thanks! I can build from here czar 18:26, 18 June 2018 (UTC)
Hi @Tagishsimon, have a follow up: I think I can kill the `?languages` and `?statuses` columns and instead add icons to the sitelinks, e.g., "pt  , ca  ". Can you help me encode the images as wikicode? latest version czar 22:41, 18 June 2018 (UTC)
@Czar: I'd be more tempted by this sort of approach ... any good? --Tagishsimon (talk) 23:01, 18 June 2018 (UTC)

  Resolved czar 12:02, 19 June 2018 (UTC)

Custom link formatting in WDQS results

Is it possibile to show the link The Godfather instead of <https://en.wikipedia.org/wiki/The_Godfather> in the sitelink column of the following query? Thanks in advance. 

SELECT ?item ?itemLabel ?sitelink
WHERE {
  ?item wdt:P345 "tt0068646" .
  ?sitelink schema:about ?item; schema:isPartOf <https://en.wikipedia.org/> .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
LIMIT 10
Try it!

--Rotpunkt (talk) 23:01, 18 June 2018 (UTC)

@Rotpunkt: Not sure if any of these variations get you far enough. I don't know whether it's possible to make my creations clickable within WDQS.
SELECT ?item ?itemLabel ?sitelink ?sitelink_label ?sitelink2 ?sitelink3
WHERE {
  ?item wdt:P345 "tt0068646" .
  ?sitelink schema:about ?item ; schema:isPartOf <https://en.wikipedia.org/> .
  ?sitelink schema:name ?sitelink_label .
  ?sitelink schema:inLanguage ?lang .
  bind(concat("[[",str(?sitelink_label),"]]") as ?sitelink2)
  bind(concat("[[:",str(?lang),":",str(?sitelink_label),"|",str(?sitelink_label),"]]") as ?sitelink3)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
LIMIT 10
Try it!
Hi Tagishsimon, I'm looking for a real custom link: The Godfather, not a string. --Rotpunkt (talk) 23:57, 18 June 2018 (UTC)
I feared as much; beyond me. --Tagishsimon (talk) 00:29, 19 June 2018 (UTC)
I am asking here to know if it's possible, thanks anyway. --Rotpunkt (talk) 00:34, 19 June 2018 (UTC)
@Rotpunkt: In a word: No. It's not currently possible within WDQS.
The best workaround may be to create a Listeria wikipage for the query: see the templates {{Wikidata list}} and {{Wikidata list end}}.
Listeria offers a number of different ways to format a column corresponding to a Wikidata item, or as a last resort you can get your query to output a suitable column of wikitext, which will be appropriately interpreted. Jheald (talk) 06:06, 19 June 2018 (UTC)
@Jheald Ok thanks. I already use Listeria but it would be a nice feature to have. Wikidata items and Commons pictures already have a short link based on their title page in WDQS results, sitelinks instead can be very long and are also unescaped. --Rotpunkt (talk) 06:44, 19 June 2018 (UTC)
@Rotpunkt: As I understand it, the problem is that the WDQS rendering module renders different types of column content in different ways on the basis of them having different built-in data-types.
But there is no data-type (at least, not at the moment) for "link-url + link-text"; so the rendering module is not able to recognise this as a thing, and render it in a special way.
I did suggest adding such types [1], so writers of queries could then construct & cast particular variables into this form, and the interface would then recognise it, and output something appropriate. But User:Smalyshev (WMF) didn't really go for the idea.
If you want to add your thoughts on this topic, and explain why going via Listeria isn't sufficient for what you need, phab:T150937 may be the canonical ticket for the issue on Phabricator. Jheald (talk) 08:49, 19 June 2018 (UTC)
Nice thanks! phab:T150937 suits my needs. --Rotpunkt (talk) 09:53, 19 June 2018 (UTC)

List of article that exist on Simple English Wikipedia but not on English Wikipedia

I tried building a query but I get "Query timeout limit reached".

I tried 

SELECT ?item ?itemLabel (COUNT(distinct ?sitelink) as ?count)
WHERE
{
	?item wdt:P31 wd:Q5 .
	?sitelink schema:about ?item .
	FILTER EXISTS { ?wen schema:about ?item . ?wen schema:inLanguage "simple" }
	FILTER NOT EXISTS { ?wfr schema:about ?item . ?wfr schema:inLanguage "en" }
	SERVICE wikibase:label {
		bd:serviceParam wikibase:language "simple,en" .
	}
}
GROUP BY ?item ?itemLabel
ORDER BY DESC(?count)
Try it!

Based on the example query of "#French people with an article on the English Wikipedia but not on the French one"

Is there an error in the query or is it to tasking on the system? If so, how can I make it work? The above query is for people only, not sure how to subdivide it more without a lot of annoying work (run it separately for each country or language, etc.). --Piotrus (talk) 09:28, 19 June 2018 (UTC)

@Piotrus: the query below is a bit of a mess and has arbitrary changes of approach from yours (because reasons, few of them good) but seems to work. Main trick is to reduce the set of items in a first query, which is passed to the second query where more time-expensive things can be done.
SELECT ?item ?itemLabel ?linkcount
with { 
  select ?item ?itemLabel ?sitelink WHERE
{
	?item wdt:P31 wd:Q5 .
	?sitelink schema:about ?item .
	?sitelink schema:isPartOf <https://simple.wikipedia.org/> .
   }      
} as %s
where {
 include %s
 FILTER NOT EXISTS { ?wfr schema:about ?item . ?wfr schema:inLanguage "en". }
 OPTIONAL {?item rdfs:label ?itemLabel. filter(lang(?itemLabel)="en")}
 OPTIONAL {?item wikibase:sitelinks ?linkcount .} 
}
ORDER BY DESC(?linkcount)
Try it!
- hth --Tagishsimon (talk) 10:36, 19 June 2018 (UTC)

How to get all categories of a string literal in wikidata?

Hi I am trying to find all categories of a string literal using wikidata. Example "Jaguar" shows in category section "British | brandsCar | brandsJaguar | CarsJaguar | vehicles". So is there a way that I can type in a string literal and it can give me the categories.

Wikidata does not have any data on the categories of a wikipedia article. So nothing doing here, I'm afraid. It's possible that a report in quarry might do what you want, but I'm sufficiently unfamiliar with it to be of little use. --Tagishsimon (talk) 22:16, 12 June 2018 (UTC)

If in what you are interested is just in category labels, as I figure, your query is near to: 

SELECT ?item ?txtcat WHERE {
  ?item wdt:P31 wd:Q4167836 .
  ?item rdfs:label ?txtcat .
  FILTER(CONTAINS(STR(?txtcat), "Jaguar")) .
}
LIMIT 50
Try it!

But consider these issues and fix it if you work it out:

  • Selecting categories if every language.
  • Time out withou LIMIT

--Tsaorin (talk) 10:09, 21 June 2018 (UTC)

Find with property, qualifier, source

I want to find the items with:

1) population (P1082) (with any value)

2) point in time (P585) = 1901 as a qualifier

3) retrieved (P813) = 2 January 2018 as a source

4) stated in (P248) with not Cyprus census 1901 (Q29639091) as a source (the item must have P248 as a source but with not Q29639091)

(I need one query for all those, not 4 different queries).

Xaris333 (talk) 18:28, 20 June 2018 (UTC)

Assuming you’d like to see condition 3) and 4) within the same reference, it should be 
SELECT ?item ?population ?time ?source WHERE {
  ?item p:P1082 [ ps:P1082 ?population; pq:P585 ?time; prov:wasDerivedFrom [ pr:P813 '2018-01-02T00:00:00Z'^^xsd:dateTime; pr:P248 ?source ] ] .
  FILTER(YEAR(?time) = 1901) .
  FILTER(?source != wd:Q29639091) .
}
Try it!
—MisterSynergy (talk) 18:59, 20 June 2018 (UTC)

Yes. Thanks! Xaris333 (talk) 19:10, 20 June 2018 (UTC)

Date in ISO format and without over-precision

Taken from Wikidata:SPARQL_query_service/queries/examples#People_born_before_year_1880_with_no_death_date 

SELECT ?h ?date
WHERE
{
	?h wdt:P31 wd:Q5 .
	?h wdt:P569 ?date .
	FILTER (?date < "1880-01-01T00:00:00Z"^^xsd:dateTime)
	FILTER (!bound(?d))
}
LIMIT 100
Try it!

Is it possible to output the information

  1. in ISO format (YYYY-MM-DD)
  2. without precision that is not in the source, e.g. "1 January" is wrong if only the year is known. 92.230.138.218 09:57, 22 June 2018 (UTC)
The following is probably the most unholy cludge, but might illustrate the basic technique for extracting date precision. I have a couple of filter(?time_precision >n) statements, one commented out. In short, the query calls for date precision to at least a year (9) and the commented out filter allows you to bump up the precision to day (11). I expect my binds are horrible and my logic shaky. So it goes. --Tagishsimon (talk) 12:56, 22 June 2018 (UTC)
SELECT ?item ?dob ?time_precision ?year ?month ?day ?ISO
WHERE
{
  ?item wdt:P31 wd:Q5 .
  ?item p:P569 ?p569stm1 .
  ?p569stm1 ps:P569 ?dob .             # must have dob
  ?p569stm1 psv:P569 [wikibase:timePrecision ?time_precision] .
  filter(?time_precision >8)
  bind(year(?dob) as ?year)
  bind(if(?time_precision >9,month(?dob),"00") as ?m)
  bind(if(?time_precision >10,day(?dob),"00") as ?d)
  bind(if(strlen(?m)=1,concat("0",str(?m)),?m) as ?month)
  bind(if(strlen(?d)=1,concat("0",str(?d)),?d) as ?day)
  bind(concat(str(?year),"-",str(?month),"-",str(?day)) as ?ISO)
 # filter (?time_precision >10)
	}
LIMIT 100
Try it!

Tagishsimon, thanks a lot! I replaced / with - in the ISO string, as this is the seperator defined in ISO 8601. 92.230.138.218 15:04, 22 June 2018 (UTC)

To avoid the -00 one can do
bind(concat(str(?year),"-",str(?month),"-",str(?day)) as ?ISOzero)
bind(replace(str(?ISOzero), "-00", "") AS ?ISO)
92.230.138.218 16:00, 22 June 2018 (UTC)

Article needing image map

Hello. I'd like to get a query which :

  • SELECT Title of the article and GPS coordinates for every article of this category (only those which have GPS coordinates of course)
  • DISPLAY them on a map

I found this :(Q32088211) which may help, but no label is actually defined. Thanks for any help.

Waterced (talk) 16:58, 23 June 2018 (UTC)

@Waterced: Wikidata is not aware of the category membership of articles on language wikipedias. So the only approach I can think for this query is; harvest the wikidata item Q IDs via petscan and then use them within a WDQS query. That means every time you want to run it, you need to fetch an updated batch of Q IDs from petscan. --Tagishsimon (talk) 17:44, 23 June 2018 (UTC)
@Tagishsimon: Wikidata is not but WDQS is. But the implementation is a bit immature yet, so at most 500 articles could be displayed. Matěj Suchánek (talk) 19:03, 23 June 2018 (UTC)
@Matěj Suchánek: thanks; and for the two other bits of advice / improvement on other questions in the last day or so. All very much appreciated, and I slowly learn. --Tagishsimon (talk) 20:06, 23 June 2018 (UTC)
MWAPI looks like an excellent new rabbit-hole to fall down. --Tagishsimon (talk) 20:54, 23 June 2018 (UTC)
Thanks for these interesting answers. Waterced (talk) 21:05, 23 June 2018 (UTC)

Finding an item by an URL

Why doesn't this query find Gulliver's Fugitives (Q54806953)? --Shisma (talk) 18:05, 23 June 2018 (UTC) 

SELECT ?Q54806953 WHERE {
  ?Q54806953 wdt:P973 "http://de.memory-alpha.wikia.com/wiki/Gullivers_Fl%C3%BCchtlinge".
}
Try it!
@Shisma: Looks like stringification was in order:
SELECT ?Q54806953 WHERE {
  ?Q54806953 wdt:P973 ?url .
  filter(str(?url)="http://de.memory-alpha.wikia.com/wiki/Gullivers_Fl%C3%BCchtlinge")
}
Try it!
--Tagishsimon (talk) 18:17, 23 June 2018 (UTC)
Better would be:
SELECT ?item {
  BIND( URI( "http://de.memory-alpha.wikia.com/wiki/Gullivers_Fl%C3%BCchtlinge" ) AS ?url ) .
  ?item wdt:P973 ?url .
}
Try it!
Your example has O(n), ie. needs to scan all described at URL (P973) entries. Matěj Suchánek (talk) 19:00, 23 June 2018 (UTC)
  • Without bind, that would be:
SELECT ?Q54806953 WHERE {
  ?Q54806953 wdt:P973 <http://de.memory-alpha.wikia.com/wiki/Gullivers_Fl%C3%BCchtlinge> 
}
Try it!


--- Jura 06:12, 24 June 2018 (UTC)

A list of notable English people born in the last 70 years

is it possible to obtain a listing of wikipedia people who were born in England [UK could do] aged 70 or less, dead or alive..... ideally including those raised in England and not born there

[there is a list of [English people...] is that them all?]

and if it's relevant:
I'd very much like it to be in a machine readable format that I could combine with geolocation, wikipedia pages and other data

--FactoProphyl (talk) 17:34, 17 June 2018 (UTC)

@FactoProphyl: here's a starter for ten. You'll find various download options for the data. However, this is for country of citizenship = UK rather than England; and notability is taken to be established by their wikidata record. And we could discuss things like date precision, which has a marginal impact on whether some folk are listed or not. And it would have been possible to look for people born in a location that is in England, rather than looking for people with UK citizenship. Pros & cons attach to whichever route we take. Can start to tweak based on your feedback, if you're interested. --Tagishsimon (talk) 20:23, 17 June 2018 (UTC)
SELECT ?item ?itemLabel ?dob ?span WITH {
  SELECT ?item ?dob ?span WHERE {
  ?item wdt:P27 wd:Q145.          #item has country of citizenship = UK
  ?item wdt:P569 ?dob.            #item has a DoB
  bind((now()-?dob)/365 as ?span) #span in years measured backwards from now 
  filter(?span<71)                #span less than 71
  } 
} as %i where
{ include %i
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Tagishsimon (talk) 20:23, 17 June 2018 (UTC)
@Tagishsimon:

forgive me if I'm responding the wrong way or if I should use talk instead.. I'm sorry, I missed your message until today.

You are a god and I thank you, this is what I am seeking, But there's always a but, the list has to be as reliable as possible, I have to be able to show that virtually every UK/English notable has been included and from what I understand I do not think tag dependent queries provide that. My worry is that they miss those pages that are unconstrained by infobox or other fields or tags. Many pages have all their info in body text of /mediawiki/page/revision/text. They have no boxes or {{categories| etc. As far as I understand pages that are empty of tags just do not get found... I would like very much for someone to confirm or reject this assumption

to be honest I'd be happy with a CSV of /mediawiki/page/title and /mediawiki/page/revision/text I could then, if they exist, create birthdate and other tags in infobox from this body text. it would seem nice to be able return the data to relevant wikipages but that task is completely beyond me...

Secondly, this search is on Wikidata, which I understand uses a different or less current dataset. for example, Wikidata has a 'human' or 'people' field which I don't see on any wikpedia page... Again, I'm not sure if my assumption is correct.

I ended up thinking a bit differently. Is there a way I could search the body text of /mediawiki/page/revision/text as it appears on the wikimedia pages for say 'United Kingdom' or 'England' to whittle the results down that way?

Thanks again for providing the query I've been informed more than a few times that the kinds of search I am trying are virtually impossible because of the way wikimedia handles [mishandles, I could say] the data. Thanks for showing me it's very possible --FactoProphyl (talk) 11:29, 24 June 2018 (UTC)

obtain label of subclasses of the selection, while excluding the starting class

I am new to SPARQL and I'm trying to figure out how to retrieve all items with, for example, an occupation that is a subclass of "journalist" but not journalist itself—while excluding any other occupation statements that the item has. There are two variants of this: one would ensure that "journalist" itself does not appear on any of the occupation statements, but one or more subclasses of journalist do appear; the second would not be so fussy (journalist may appear on another statement, but I only want the statement(s) that are subclasses of journalist). I would appreciate both versions, for learning, if possible.

I am working from the following. At root, I can't figure out how to fill in "occLabel" (which will contain things like "photojournalist", "reporter"--subclasses of "journalist" but not "journalist"). 

SELECT distinct ?item ?itemLabel ?occLabel WHERE {
  ?item wdt:P106/wdt:P279* wd:Q1930187 .
  FILTER NOT EXISTS {?item wdt:P106 wd:Q1930187 }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 100
Try it!

thanks, Outriggr (talk) 00:54, 23 June 2018 (UTC)

@Outriggr: Let's deal with the label first. You need to get the P106 value and assign it to a variable, which I do with ?item wdt:P106 ?occ. ... which feels counter-intuitive given the preceding wdt:P106/wdt:P279*. I think the version below, by dint of your FILTER NOT EXISTS {?item wdt:P106 wd:Q1930187 } will exclude any ?items that have a P106 of journalist, which fits your << ensure that "journalist" itself does not appear on any of the occupation statements >>. Your second version is beyond me, at least without a break for tea... --Tagishsimon (talk) 01:51, 23 June 2018 (UTC)
SELECT distinct ?item ?itemLabel ?occLabel WHERE {
  ?item wdt:P106/wdt:P279* wd:Q1930187 .          #item has occ of journalist or occ which is subclass of journalist
  ?item wdt:P106 ?occ.                            #get an occ value for the Label
  ?occ wdt:P279* wd:Q1930187 .                    # Outriggr addition: get rid of non-sub-journalist occupation ROWS (or so it seems)
  FILTER NOT EXISTS {?item wdt:P106 wd:Q1930187 } #discard the item if it has occ=journalist
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 100
Try it!
--Tagishsimon (talk) 01:51, 23 June 2018 (UTC)
Perhaps this does the second one? We're finding an ?occ, checking it is a P279 of journalist, but checking also that it is not itself journalist. We return the item in the results only if an ?occ can be found which is a P279 of J~ and which is not J~. (I should caution that I'm that dangerous thing, a person with a little knowledge of SPARQL...) In passing, we're maybe not dealing very elegantly with people having multiple occupations ... we may get multiple rows for a single individual. --Tagishsimon (talk) 01:58, 23 June 2018 (UTC)
SELECT distinct ?item ?itemLabel ?occLabel WHERE {
  ?item wdt:P106 ?occ.                 #item hsa an occ
  ?occ wdt:P279* wd:Q1930187 .         #occ is a subclass of journalist
  filter(?occ not in (wd:Q1930187))    #but occ is not journlist
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 100
Try it!

I'm impressed, thanks! I was "researching" this after your first response, but all my thoughts are in terms of SQL which just doesn't seem to help ("get a subquery of all the relevant subclasses..."). I will need to read a book. It just didn't seem like a challenge to access the name of the subclass involved.

I removed the filter line from yours out of curiosity--with a limit of 5000 it still doesn't return "journalist" anywhere--I don't know if that is coincidence due to the limit, or only subclasses are returned with the second line.

A bonus problem (which I don't in any way need, I'm just talking) would be to exclude items that DO have journalist along with the wanted subclass "war correspondent" (for example, the second query returned this guy, who has both). That may either be really easy or quite hard, but I will be able to use your second query.

Thanks very much. Outriggr (talk) 02:57, 23 June 2018 (UTC)

@Outriggr: I think your last paragraph confused me. Let me recap where I think we are. (I think ...) The first query will not return an Item which has journalist in it, even if it has a subclass of Journalist in it. For those items which are selected on the basis of having a subclass of journalist, it will return one row per occupation per person, irrespective of whether the occupation is a subclass of journalist (so a war correspondent & car mechanic will get one row for each of these). The second query will return items that have an occ of journalist, if there is also an occ which is a subclass of journalist; but will not provide a row specifying occupation = journalist; and all of the occupations/rows output will be subclasses of journalist, and so if an item has multiple journalist subclasses, we'll get one row per occupation. So the initial query is your last paragraph's exclude items that DO have journalist along with the wanted subclass "war correspondent". I've added comments to the code fwiw. hth --Tagishsimon (talk) 03:38, 23 June 2018 (UTC)
Sorry to confuse you. You are correct. I would then change my bonus problem ( :-D ) to being able to remove the non-journo occupations from the first list. I believe I have figured this out myself by adding the commented row in your first query. (I'm so confused I've pressed Publish instead of Preview three times during this discussion.) Regards, Outriggr (talk) 04:20, 23 June 2018 (UTC)
@Outriggr: Confusion is good. Keeps the brain warm. Your amendment works; but if you remove the limit from the first query & run it, you'll get 7765 results ... if you then comment out the P31/P279 code, you get the same number of hits, since your new bit of code renders the P31/P279 redundant :) --Tagishsimon (talk) 20:39, 23 June 2018 (UTC)
This section in the SPARQL reference might help you. With ?occupation wdt:P279+ wd:Q1930187 you select all items that are subclasses of journalist (Q1930187), but not journalist (Q1930187) itself (the + means “one or more levels” in contrast to the * quantifier, which means “zero or more levels”). —MisterSynergy (talk) 05:34, 23 June 2018 (UTC)
Yes: that's exactly the page of the reference I was in need of reading; thank you. I used an InversePath yesterday and had made a mental note to track down documentation to check my use of it. And now I have. --Tagishsimon (talk) 20:39, 23 June 2018 (UTC)

@Tagishsimon: I see! ... I thought I'd place the final queries below. Removed "distinct" as I don't think it was ever relevant.

Return items with statement value that is a subclass of X (but exclude items where class X itself is involved), and get the name of the subclasses for a label
SELECT ?item ?itemLabel ?occLabel WHERE {
  ?item wdt:P106 ?occ .               #get the occ value/subclass for the Label
  ?occ wdt:P279+ wd:Q1930187 .        #get occ's that are subclasses of "journalist", but exclude "journalist"
  FILTER NOT EXISTS {?item wdt:P106 wd:Q1930187 } 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?itemLabel
LIMIT 100 # number of rows ~7700
Try it!
this time, allow the class to be used directly by the item's statement, but don't include it as a row
SELECT ?item ?itemLabel ?occLabel WHERE {
  ?item wdt:P106 ?occ.                 #item has an occ
  ?occ wdt:P279* wd:Q1930187 .         #occ is a subclass of journalist, or journalist
  filter(?occ not in (wd:Q1930187))    #don't include rows that equal "journalist", but the item's P106 ''may still have "journalist"''
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?itemLabel
LIMIT 100 # number of rows ~12000
Try it!

Not to beat a dead horse, but this all comes down to the filter statement then... in the first case, the filter definition operates at the item level, In the second, it operates at the row level by saying "not in". I guess that is because the filter operates on $item in the first, but operates on $occ in the second. Yay? Outriggr (talk) 03:48, 24 June 2018 (UTC)

@Outriggr: Yes. And as we don't shoot horses on this board, final versions, especially with P279+, are very welcome :) --Tagishsimon (talk) 10:00, 24 June 2018 (UTC)

Query for Japanese anime

Hello, I want a query that shows all Japanese anime with optional images for them, and optional coordinates for the city where they were created. Thanks. --Mohammad Hijjawi (talk) 19:00, 23 June 2018 (UTC)

@Mohammad Hijjawi: I think you may be disappointed, at least as far as location and coordinates are concerned ... unless you can point me to an example anime which has location info in a property that I've not thought of. Still: this is a starting query for you.
SELECT distinct ?item ?itemLabel ?location ?geo ?img  WHERE {
  ?item wdt:P31/wdt:P279* wd:Q1107.                                #item is an instance or subclass of anime
  ?item wdt:P495 wd:Q17.                                           #country of origin is Japan
  optional {?item wdt:P276 ?location . ?location wdt:P625 ?geo .}  #optionally has a location which has coordinate
  optional {?item wdt:P18 ?img .}                                  #optionally has an image
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,jp". }
} order by desc(?img) ?itemLabel
Try it!
--Tagishsimon (talk) 20:25, 23 June 2018 (UTC)
narrative location (P840) has a value for 20 of the more than 1000 animes. --Larske (talk) 08:51, 24 June 2018 (UTC)

Specify "any property"

Hi, is there a good way to specify "any property" in a query? I'm using the following: 

    ?item ?prop ?value.
    FILTER STRSTARTS(STR(?prop), STR(wdt:)).

Is there a better way to do this? Especially this does not work with "p:" instead of "wdt:" since the pathes overlap. Also its not that fast in some situations. -- MichaelSchoenitzer (talk) 17:02, 24 June 2018 (UTC)

@MichaelSchoenitzer: well, wikibase:directClaim would be the way to do it in my opinion, but if you don’t post a full query I can’t very well judge if it times out or not :) but here’s a query for all statements of Douglas Adams (Q42), for example: 
SELECT ?subject ?propertyLabel ?value WHERE {
  VALUES ?subject { wd:Q42 }
  ?property a wikibase:Property;
            wikibase:directClaim ?wdt.
  ?subject ?wdt ?value.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--TweetsFactsAndQueries (talk) 10:02, 27 June 2018 (UTC)
The main thing I wanted to do was to find all items with have a reference pointing to a specific URL. For example generalizing this form the five listed properties to any property:
SELECT DISTINCT ?item ?itemLabel ?url where {
  ?item ?prop ?val.
  VALUES ?prop { p:P348 p:P306 p:P275 p:P277 p:P178 }.
    
  ?val prov:wasDerivedFrom ?source.
  ?source pr:P854 ?url.
  FILTER (CONTAINS(str(?url), "github")).
  
  FILTER NOT EXISTS {
    ?item wdt:P1324 ?repo.
    }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
Not surprisingly this times out if using wikibase:claim without any further limits on the items. But it works if I limit to software only:
SELECT DISTINCT ?item ?itemLabel ?url where {
  ?item wdt:P31/wdt:P279* wd:Q7397.
  ?item ?prop ?val.
 
  ?property a wikibase:Property;
            wikibase:claim ?prop.
    
  ?val prov:wasDerivedFrom ?source.
  ?source pr:P854 ?url.
  FILTER (CONTAINS(str(?url), "github")).
  
  FILTER NOT EXISTS {
    ?item wdt:P1324 ?repo.
    }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
Still thinking about how I can include more items that miss a instance of (P31)software (Q7397)… (ideas welcome) But thanks! wikibase:claim was probably was I was looking for! -- MichaelSchoenitzer (talk) 11:39, 27 June 2018 (UTC)

Duplication

Hi, I wrote a code that shows all UN's members (193 countries), code is in here , as you can see, South Africa is mentioned three times because it has got three capitals.

I want it to be mentioned once and its three capitals to be mentioned once too in capital's Label (I want to remove duplication), Can this be done? 

select ?countryLabel ?capitalLabel ?pop ?area ?headLabel ?imagehead ?currencyLabel ?flag ?coord
where{
  ?country wdt:P31 wd:Q6256;
           wdt:P463 wd:Q1065;
           optional {?country wdt:P36 ?capital }
  optional {?country wdt:P1082 ?pop }
  optional {?country wdt:P2046  ?area }
  optional {?country wdt:P35 ?head.
           ?head wdt:P18 ?imagehead}
  optional {?country wdt:P38 ?currency }
  optional {?country wdt:P41 ?flag }
  optional {?country wdt:P625 ?coord }
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
Try it!

--Mohammad Hijjawi (talk) 08:28, 25 June 2018 (UTC)

@Mohammad Hijjawi: yes - this sort of approach:
select ?countryLabel (group_concat(?capLabel;separator=", ") as ?capitalLabel) ?pop ?area ?headLabel ?imagehead ?currencyLabel ?flag ?coord
where{
  ?country wdt:P31 wd:Q6256;
           wdt:P463 wd:Q1065;
           optional {?country wdt:P36 ?cap. ?cap rdfs:label ?capLabel. filter(lang(?capLabel)="en")}
  optional {?country wdt:P1082 ?pop }
  optional {?country wdt:P2046  ?area }
  optional {?country wdt:P35 ?head.
           ?head wdt:P18 ?imagehead}
  optional {?country wdt:P38 ?currency }
  optional {?country wdt:P41 ?flag }
  optional {?country wdt:P625 ?coord } 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  } group by ?countryLabel ?pop ?area ?headLabel ?imagehead ?currencyLabel ?flag ?coord
Try it!
--Tagishsimon (talk) 08:42, 25 June 2018 (UTC)

Getting the datetime value to output in +1948-04-12T00:00:00Z format

How do I get the datetime value in this query in the format +1948-04-12T00:00:00Z rather than 12 April 1948? (You'll see the remains of me poking around inside the model, to no good effect, in the query). thanks --Tagishsimon (talk) 00:31, 27 June 2018 (UTC) 

SELECT ?item ?itemLabel ?datez ?date ?approvalLabel
WHERE 
{
  ?item p:P1435 ?thing.
  ?thing ps:P1435 wd:Q17504995.
  optional {?thing pqv:P580 ?datez. ?datez wikibase:timeValue ?date} 
  optional {?thing pq:P790 ?approval}  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

Date with timePrecision

Hello. How i can select date of birth/death with timePrecision 8, 9, 10, 11? 

?birth_date_node wikibase:timePrecision "8"^^xsd:integer .
Try it!

working is great, but how i can add 9, 10, 11?

If i’m use filter (see below) then query not working — timeout limit reached. 

SELECT ?a ?aLabel ?birth_date ?death_date WHERE {
   ?a wdt:P21 wd:Q6581097 .
   ?a p:P569/psv:P569 ?birth_date_node .            
   ?a p:P570/psv:P570 ?death_date_node .
   ?birth_date_node wikibase:timePrecision ?precision1 .
   ?death_date_node wikibase:timePrecision ?precision2 .
   ?birth_date_node wikibase:timeValue ?birth_date .
   ?death_date_node wikibase:timeValue ?death_date .
   FILTER ( (year(?death_date) - year(?birth_date)) <= 20
            && (year(?death_date) - year(?birth_date)) >= 30 ) .
  FILTER((?precision1 >= 7) && (?precision1 <= 11) && (?precision2 >= 7) && (?precision2 <= 11)).
   SERVICE wikibase:label { bd:serviceParam wikibase:language "ru" }
 }
ORDER BY ?birth_date ?aLabel
LIMIT 5000
Try it!

Sorry for my English.--Exoped (talk) 08:28, 27 June 2018 (UTC)

@Exoped: something like this - and note I have changed the age filter, as your query asked for people who were <=20 && >=30 :). Sorry about my Russian - much worse than your English.
SELECT ?a ?aLabel ?birth_date ?death_date with {select ?a ?aLabel ?birth_date ?death_date WHERE {
   ?a wdt:P21 wd:Q6581097 .
   ?a p:P569/psv:P569 ?birth_date_node .            
   ?a p:P570/psv:P570 ?death_date_node .
   ?birth_date_node wikibase:timePrecision ?precision1 .
   ?death_date_node wikibase:timePrecision ?precision2 .
   ?birth_date_node wikibase:timeValue ?birth_date .
   ?death_date_node wikibase:timeValue ?death_date .
   FILTER ( (year(?death_date) - year(?birth_date)) >= 20
            && (year(?death_date) - year(?birth_date)) <= 30 ) .
  FILTER((?precision1 >= 7) && (?precision1 <= 11) && (?precision2 >= 7) && (?precision2 <= 11)).
  } LIMIT 5000
}as %i where {include %i
   SERVICE wikibase:label { bd:serviceParam wikibase:language "ru" }
 }
ORDER BY ?birth_date ?aLabel
Try it!
--Tagishsimon (talk) 08:42, 27 June 2018 (UTC)
By the way, you can make selecting dates by precision faster (O(n) → O(1)) with:
 VALUES ?precision1 { 7 8 9 10 11 } .
 VALUES ?precision2 { 7 8 9 10 11 } .
instead of using FILTER (like in #Finding an item by an URL). Matěj Suchánek (talk) 10:34, 27 June 2018 (UTC)
Yes. Slightly abashed; must remember to check whether filters can be implemented via values. Although, empirically, I'm getting 8,000-10,000ms for the filter query and 14000-20000ms for the values version ... Jura1 remarked on the experience of values leading to timeouts earier this month. --Tagishsimon (talk) 11:12, 27 June 2018 (UTC)

Query to extract Wikipedia article content based on a list of ISBNs

I would like to write a query to extract all the text content of Wikipedia article for a list of books based on the ISBN-13 number.

Is this possible?

I have a list of ISBN-13 numbers, here are a few...

9780709706922 9788466305853 9780688065706

Wikidata query service cannot do this for you. It can find wikidata items that have the ISBN13s, and point to the en wiki article on the publication (if there is one). You'd likely need to scrape the content of the pages based on the URLs returned by a query, with another tool. (There's a possibility that MWAPI can be employed to fetch the content of linked articles, but right now that's a bit above my pay grade.) Here, fwiw, is an ISBN query which can do the first part of the requirement ... it does not seem to be finding items for your three ISBNs.
select ?item ?itemLabel ?isbn13 ?isbn_13 ?article ?wen
where{
values ?item { wd:Q4356108}      #remove this row when used in anger
  ?item wdt:P212 ?isbn13.
bind(if(substr(?isbn13,1,1)!="-",substr(?isbn13,1,1),"") as ?1)
bind(if(substr(?isbn13,2,1)!="-",substr(?isbn13,2,1),"") as ?2)
bind(if(substr(?isbn13,3,1)!="-",substr(?isbn13,3,1),"") as ?3)
bind(if(substr(?isbn13,4,1)!="-",substr(?isbn13,4,1),"") as ?4)
bind(if(substr(?isbn13,5,1)!="-",substr(?isbn13,5,1),"") as ?5)
bind(if(substr(?isbn13,6,1)!="-",substr(?isbn13,6,1),"") as ?6)
bind(if(substr(?isbn13,7,1)!="-",substr(?isbn13,7,1),"") as ?7)
bind(if(substr(?isbn13,8,1)!="-",substr(?isbn13,8,1),"") as ?8)
bind(if(substr(?isbn13,9,1)!="-",substr(?isbn13,9,1),"") as ?9)
bind(if(substr(?isbn13,10,1)!="-",substr(?isbn13,10,1),"") as ?10)
bind(if(substr(?isbn13,11,1)!="-",substr(?isbn13,11,1),"") as ?11)
bind(if(substr(?isbn13,12,1)!="-",substr(?isbn13,12,1),"") as ?12)
bind(if(substr(?isbn13,13,1)!="-",substr(?isbn13,13,1),"") as ?13)
bind(if(substr(?isbn13,14,1)!="-",substr(?isbn13,14,1),"") as ?14)
bind(if(substr(?isbn13,15,1)!="-",substr(?isbn13,15,1),"") as ?15)
bind(if(substr(?isbn13,16,1)!="-",substr(?isbn13,16,1),"") as ?16)
bind(if(substr(?isbn13,17,1)!="-",substr(?isbn13,17,1),"") as ?17)
bind(concat(?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17) as ?isbn_13)
filter(?isbn_13 in ("9784163191409","9784163191410", "9780709706922", "9788466305853", "9780688065706"))   #add ISBNs here
optional { ?wen schema:about ?item . ?wen schema:name ?article. ?wen schema:inLanguage "en".}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
Try it!
--Tagishsimon (talk) 17:07, 25 June 2018 (UTC)
you can simplify this query to
SELECT ?item ?itemLabel ?isbn13 ?isbn_13 ?article ?wen WHERE{
  ?item wdt:P212 ?isbn13.
  BIND(REPLACE(?isbn13, "-", "") AS ?isbn_13)
  FILTER(?isbn_13 in ("9784163191409","9784163191410", "9780709706922", "9788466305853", "9780688065706"))   #add ISBNs here
  OPTIONAL { ?wen schema:about ?item . ?wen schema:name ?article. ?wen schema:inLanguage "en".}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Pasleim (talk) 07:36, 27 June 2018 (UTC)
Hi,
Just two quick points:
These queries should *not* give these results. ISBN are for editions only (see ISO 2108) and Wikipedia articles are about works (most of the times). A correct query should ask for articles linked to works themselves having ISBN but sadly bibliographical data are in a very bad shape on Wikidata. The Last Don (Q2880168) is the perfect example of what should never be done…
For the separation of the different part of the ISBN (either with spaces, dashes or hyphens, both are corrects - maybe not dashes - and the hyphens being more common), removing them is very very bad idea (and technically, even if not represented in the sequence the separation is still there). It very easy to remove everything that is not a number while adding automatically the separation at the right place is technically possible but can be a bit tricky (as the part can have varying length, but someone should build a bot for that). Plus, the part are meaningful (so much that we have a property for it ISBN publisher prefix (P3035)).
Cdlt, VIGNERON (talk) 16:34, 27 June 2018 (UTC)
  • It seems these could be formatted consistently similar to ISNI, but this isn't as easy as for ISNI and currently none can do it. When searching, one would need to ensure that separations are either stripped or added in the same way.
    --- Jura 05:25, 28 June 2018 (UTC)

List of specific people and whether they have WP articles in the EU official languages

I'm looking to create a table, looking at about 20 specific women scientists/artists and whether they have Wikipedia articles in the 24 E.U. official languages (European Union (Q458) -> official language (P37)) + other active Wikipedia editions added on demand (e.g. Catalan, Nowegian, Welsh, Breton...). The people are Marie Curie (Q7186), Judith Leyster (Q232423), Sofonisba Anguissola (Q236038), Gerda Wegener (Q266086), Josefa de Óbidos (Q269338), Maria Wiik (Q4110933) and about a dozen others (I can add those to the query itself later on). It would also be useful to add an optional component of whether any WP article was a "good" or "featured" article in that language. Does this make sense? Wittylama (talk) 07:49, 28 June 2018 (UTC)

Here's the query so far, it doesn't look pretty yet but it returns all the URLS of which there ARE wikipedia pages for the person in question for all EU languages. CalvinBall (talk) 08:39, 28 June 2018 (UTC)

The following query uses these:

  • Properties: official language (P37)     , Wikimedia language code (P424)     
    #women scientists/artists on European wikipedia pages
SELECT ?wikiarticle ?pioneer ?pioneerLabel
WHERE {
VALUES ?pioneer {wd:Q7186 wd:Q232423 wd:Q236038 }
  wd:Q458 wdt:P37 ?langs .
  ?langs wdt:P424 ?langcode .  
  BIND(IRI(CONCAT("https://",str(?langcode),".wikipedia.org/"))AS ?wikilangs)
?wikiarticle schema:about ?pioneer .
?wikiarticle schema:isPartOf  ?wikilangs.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
@Wittylama: Or a couple of more manual approaches, below, for 4 language wikipedias ... happy to do more - e.g. expand to many language versions - if you give a steer about preferred layout; or for you to take these away and play. (I'm liking CalvinBall's language code selection method, above, but suspect you're after a wider, shorter table than that report outputs.) --Tagishsimon (talk) 08:53, 28 June 2018 (UTC)
SELECT ?person ?personLabel ?article_en ?badge_enLabel ?article_fr ?badge_frLabel ?article_it ?badge_itLabel ?article_de ?badge_deLabel WHERE {
  values ?person {wd:Q7186 wd:Q232423 wd:Q236038 wd:Q266086 wd:Q269338 wd:Q4110933}. 
  optional { ?wen schema:about ?person . ?wen schema:name ?article_en . ?wen schema:isPartOf <https://en.wikipedia.org/> . optional {?wen wikibase:badge ?badge_en .} } 
  optional { ?wfr schema:about ?person . ?wfr schema:name ?article_fr . ?wfr schema:isPartOf <https://fr.wikipedia.org/> . optional {?wfr wikibase:badge ?badge_fr .} }
  optional { ?wit schema:about ?person . ?wit schema:name ?article_it . ?wit schema:isPartOf <https://it.wikipedia.org/> . optional {?wit wikibase:badge ?badge_it .} }
  optional { ?wde schema:about ?person . ?wde schema:name ?article_de . ?wde schema:isPartOf <https://de.wikipedia.org/> . optional {?wde wikibase:badge ?badge_de .} }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
 }
Try it! 
SELECT ?person ?personLabel ?English ?French ?Italian ?German  
 WHERE {
  values ?person {wd:Q7186 wd:Q232423 wd:Q236038 wd:Q266086 wd:Q269338 wd:Q4110933}. 
  optional { ?wen schema:about ?person . ?wen schema:name ?article_en . ?wen schema:isPartOf <https://en.wikipedia.org/> . optional {?wen wikibase:badge ?badge_en .} } 
  optional { ?wfr schema:about ?person . ?wfr schema:name ?article_fr . ?wfr schema:isPartOf <https://fr.wikipedia.org/> . optional {?wfr wikibase:badge ?badge_fr .} }
  optional { ?wit schema:about ?person . ?wit schema:name ?article_it . ?wit schema:isPartOf <https://it.wikipedia.org/> . optional {?wit wikibase:badge ?badge_it .} }
  optional { ?wde schema:about ?person . ?wde schema:name ?article_de . ?wde schema:isPartOf <https://de.wikipedia.org/> . optional {?wde wikibase:badge ?badge_de .} } 
  bind(if(bound(?article_en),"Yes","") as ?En)
  bind(if(bound(?badge_en),if(?badge_en=wd:Q17437798,"Good","Featured"),"") as ?EnQual)
  bind(if(?EnQual!="",?EnQual,?En) as ?English)
  bind(if(bound(?article_fr),"Yes","") as ?Fr)
  bind(if(bound(?badge_fr),if(?badge_fr=wd:Q17437798,"Good","Featured"),"") as ?FrQual)
  bind(if(?FrQual!="",?FrQual,?Fr) as ?French)
  bind(if(bound(?article_it),"Yes","") as ?It)
  bind(if(bound(?badge_it),if(?badge_it=wd:Q17437798,"Good","Featured"),"") as ?ItQual)
  bind(if(?ItQual!="",?ItQual,?It) as ?Italian)
  bind(if(bound(?article_de),"Yes","") as ?De)
  bind(if(bound(?badge_de),if(?badge_de=wd:Q17437798,"Good","Featured"),"") as ?DeQual)  
  bind(if(?DeQual!="",?DeQual,?De) as ?German)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
 }
Try it!
--Tagishsimon (talk) 08:53, 28 June 2018 (UTC)
I really like the tabular view you have in that second query, @Tagishsimon:! I think it's best if we implement that with the more 'automated' language selection of the first query. Working on that atm. CalvinBall (talk) 09:06, 28 June 2018 (UTC)
Good ... my brain hasn't figured out how to combine the two approaches. I look forward to saying "of course!" once you've shown the way ;) --Tagishsimon (talk) 09:20, 28 June 2018 (UTC)
Thank you for these quick responses Tagishsimon and CalvinBall! The second query that you provided Tagishsimon is what i'm after - I think it's very clever how you've got the 'good' and 'featured' elements in there as optionals. Here are the 24 EU official languages, but I'll also need to add others upon community request (see column 1 of the colourful table here for my best guess: Wikidata:Europeana migration vocabulary). So, since there will be more languages being tracked than people, I think it makes sense to have the people across the top row, and languages down the first column. How would I go about adding more people, and more languages? Wittylama (talk) 09:20, 28 June 2018 (UTC)
Calvin's on the case with auto-language, Liam ... let's pause until we see that. People columns & language rows is ... interesting. We'll ping you when we have more. --Tagishsimon (talk) 09:29, 28 June 2018 (UTC)
I don't have time in the next few hours to work on the query sadly. I don't really know how to combine the two approaches yet either, so we'll have to discover it together :D I suspect using UNION to make columns out of the language codes is the correct approach, but I'll need some time to figure out the actual syntax, especially when it comes to using a UNION on a single variable (?langcode) with multiple values. I'll get back and see how this has progressed tonight! Your second query is the most user-friendly (and technically impressive, I might say) one, Tagish, I suggest we continue working on that one. CalvinBall (talk) 09:50, 28 June 2018 (UTC)
@CalvinBall, Wittylama: So here's version 2. Sadly, it dispenses with Calvin's magic language selection, so as to support WittyLama's wish to add additional languages on an ad hoc basis.
How it works: the easy bit is adding / subtracting languages - there's a Language Value Statement with pairs of language values - e.g. ("French" "fr"). Add or remove statements like this to suit.
As to people: you need to specify the people/columns in three places: 1) in the first row select statement, as a variable - i.e. in SELECT ?language ?Marie_Curie ?Judith_Leyster ... and they become your column headers; 2) as Qids in the People Value Statements - i.e. in values ?person1 to values ?person6 rows, and then 3) as the final variable in the Bind Statements (using the same variable name as was used in the first row select) - e.g. in bind(if(?bp1!="",?bp1,?ap1) as ?Marie_Curie). If you want more or less people/columns ... well ... I hope you see the clear pattern of statements and variable names employed in the statements and can do that ... you add or remove people variable names in the first row select statement; and add or remove their counterparts in the People Values Statements, Optional Statements and Bind Statements sections. Good luck! --Tagishsimon (talk) 11:07, 28 June 2018 (UTC)
SELECT ?language ?Marie_Curie ?Judith_Leyster ?Sofonisba_Anguissola ?Gerda_Wegener ?Josefa_de_Obidos ?Maria_Wiik
 WHERE {
#LANGUAGE VALUE STATEMENT
  values (?language ?langcode) { ("French" "fr") ("German" "de") ("Italian" "it") ("Polish" "pl") ("Spanish" "es") ("Finnish" "fi") ("English" "en") ("Portuguese" "pt") ("Croatian" "hr") ("Dutch" "nl") ("Romanian" "ro") ("Bulgarian" "bg") ("Swedish" "sv") ("Danish" "da") ("Czech" "cs") ("Slovak" "sk") ("Slovene" "sl") ("Hungarian" "hu") ("Estonian" "et") ("Latvian" "lv") ("Lithuanian" "lt") ("Greek" "el") ("Irish" "ga") ("Maltese" "mt") } .
  BIND(IRI(CONCAT("https://",str(?langcode),".wikipedia.org/"))AS ?wikilangs)
#PEOPLE VALUE STATEMENTS
  values ?person1 {wd:Q7186} .    #put the Qid of the first selected person here
  values ?person2 {wd:Q232423} .
  values ?person3 {wd:Q236038} . 
  values ?person4 {wd:Q266086} .
  values ?person5 {wd:Q269338} .
  values ?person6 {wd:Q4110933} . #put the Qid of the sixth selected person here
#OPTIONAL STATEMENTS
  optional { ?wp1 schema:about ?person1 . ?wp1 schema:name ?article_p1 . ?wp1 schema:isPartOf ?wikilangs . optional {?wp1 wikibase:badge ?badge_p1 .}  } 
  optional { ?wp2 schema:about ?person2 . ?wp2 schema:name ?article_p2 . ?wp2 schema:isPartOf ?wikilangs . optional {?wp2 wikibase:badge ?badge_p2 .}  } 
  optional { ?wp3 schema:about ?person3 . ?wp3 schema:name ?article_p3 . ?wp3 schema:isPartOf ?wikilangs . optional {?wp3 wikibase:badge ?badge_p3 .}  } 
  optional { ?wp4 schema:about ?person4 . ?wp4 schema:name ?article_p4 . ?wp4 schema:isPartOf ?wikilangs . optional {?wp4 wikibase:badge ?badge_p4 .}  } 
  optional { ?wp5 schema:about ?person5 . ?wp5 schema:name ?article_p5 . ?wp5 schema:isPartOf ?wikilangs . optional {?wp5 wikibase:badge ?badge_p5 .}  } 
  optional { ?wp6 schema:about ?person6 . ?wp6 schema:name ?article_p6 . ?wp6 schema:isPartOf ?wikilangs . optional {?wp6 wikibase:badge ?badge_p6 .}  } 
#BIND STATEMENTS
  bind(if(bound(?article_p1),"Yes","") as ?ap1)
  bind(if(bound(?badge_p1),if(?badge_p1=wd:Q17437798,"Good","Featured"),"") as ?bp1)
  bind(if(?bp1!="",?bp1,?ap1) as ?Marie_Curie)                                      #put the variable name for the first selected person here
   
  bind(if(bound(?article_p2),"Yes","") as ?ap2)
  bind(if(bound(?badge_p2),if(?badge_p2=wd:Q17437798,"Good","Featured"),"") as ?bp2)
  bind(if(?bp2!="",?bp2,?ap2) as ?Judith_Leyster)                                    #put the variable name for the second selected person here

  bind(if(bound(?article_p3),"Yes","") as ?ap3)
  bind(if(bound(?badge_p3),if(?badge_p3=wd:Q17437798,"Good","Featured"),"") as ?bp3)
  bind(if(?bp3!="",?bp3,?ap3) as ?Sofonisba_Anguissola)

  bind(if(bound(?article_p4),"Yes","") as ?ap4)
  bind(if(bound(?badge_p4),if(?badge_p4=wd:Q17437798,"Good","Featured"),"") as ?bp4)
  bind(if(?bp4!="",?bp4,?ap4) as ?Gerda_Wegener)

  bind(if(bound(?article_p5),"Yes","") as ?ap5)
  bind(if(bound(?badge_p5),if(?badge_p5=wd:Q17437798,"Good","Featured"),"") as ?bp5)
  bind(if(?bp5!="",?bp5,?ap5) as ?Josefa_de_Obidos)

  bind(if(bound(?article_p6),"Yes","") as ?ap6)
  bind(if(bound(?badge_p6),if(?badge_p6=wd:Q17437798,"Good","Featured"),"") as ?bp6)
  bind(if(?bp6!="",?bp6,?ap6) as ?Maria_Wiik)

 } order by ?language
Try it!

Excellent, thank you very much Tagishsimon. Here is the resulting query I created with 12 names and 25 languages - and I understand now how to add or swap any of those elements. Thanks! Wittylama (talk) 13:03, 28 June 2018 (UTC)

Yup, you've got it. Get those interwiki & translation gnomes to work. (The report could be slightly simplified ... it's carrying cruft from the version 1; but I think we'll leave it as is.) --Tagishsimon (talk) 15:10, 28 June 2018 (UTC)

Different translations of given topic?

I would like to query form wikidata a certain word and get its translations (for where it can be found) and wikidata would be one source for getting a "valid label" over all typoed versions. Got by examples this far, but cannot figure out, which property to use e.g. in order to ask for topic in high level like 'Berlin' instead of that geonames id. (The word given might not be a place, so it would be also useful to get response also to tell what the founded item is.) Thanks a million! 

SELECT DISTINCT ?city ?label 
  WHERE 
  {
    ?city wdt:P1566 "2950157" .
    ?city rdfs:label ?label
      filter( lang(?label) = 'en' || lang(?label) = 'fi' || lang(?label) = 'sv' )
}
@Viipeli: Perhaps something like this? It could be refined in a number of directions; not least, this version picks up all sorts of Berlins.
SELECT DISTINCT ?city ?label_en ?cityDescription ?label_fr ?label_de ?label_it 
  WHERE 
  {
   values ?label_en {"Berlin"@en }
   ?city rdfs:label ?label_en . 
   optional {?city rdfs:label ?label_fr . filter(lang(?label_fr)="fr") }
   optional {?city rdfs:label ?label_de . filter(lang(?label_de)="de") }
   optional {?city rdfs:label ?label_it . filter(lang(?label_it)="it") }
   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en"}
}
Try it!
- hth - --Tagishsimon (talk) 15:58, 28 June 2018 (UTC)
You can also load up a set of values rather than just look for a single one: try values ?label_en {"New York"@en "Paris"@en "Berlin"@en "Munich"@en} --Tagishsimon (talk) 16:32, 28 June 2018 (UTC)

Counting the number of words in a text

As detailed here, I was exploring ways to count the number of words in a text using SPARQL. I came as far as getting the number of works of art indexed in Wikidata for which the title has more than a given number X of spaces, so for X=33, this gives

The following query uses these:

Try it!

However, I have not yet found a way to get the number of words (I'm fine with them being defined as number of spaces for the moment, but more refined definitions are welcome) within a given text (or, for simplicity, string) using either REGEX or COUNT or any of the string operations, though I suspect there are various ways to get there. --Daniel Mietchen (talk) 17:22, 28 June 2018 (UTC)

@Daniel Mietchen: REPLACE and STRLEN might be your friends. This version seeks to replace anything that's not a space with "" and measures the length of the resulting string of spaces. (And doesn't bother adding 1, which it probably should.) Presumably with better regex-fu you'd be able to implement refined definitions.
SELECT DISTINCT ?work ?title ?spacecount
WHERE
{
  ?work wdt:P31/wdt:P279* wd:Q838948;
        wdt:P1476 ?title.
  bind(replace(?title, "\\S", "") as ?space)
  bind(strlen(?space) as ?spacecount)
  FILTER(REGEX(?title, "^\\s*\\S+(?:\\s+\\S+){33,}\\s*$", "i")).
}
ORDER BY STR(?title)
Try it!
--Tagishsimon (talk) 18:03, 28 June 2018 (UTC)
Thanks. I had considered STRLEN somewhat but not at all REPLACE — thanks for bringing this up. --Daniel Mietchen (talk) 19:03, 28 June 2018 (UTC)
Pasleim schooled me on bind(replace in the ISBN thread, above, and now I'm dangerous. --Tagishsimon (talk) 19:49, 28 June 2018 (UTC)

Find all German verbs (lexemes)

How can I get a list of all German (Q188) verbs (Q24905) as lexemes (namespace=146)? Betterknower (talk) 12:38, 29 June 2018 (UTC)

This is not possible at the moment, lexems are not yet exported to the Query Service. Matěj Suchánek (talk) 13:50, 29 June 2018 (UTC)
Thanks, --Betterknower (talk) 14:38, 29 June 2018 (UTC)

Conditional sum fail

This is a report on number of crew-members for space-shuttle missions ... the variable ?applies indicates if the crew member was aboard only for launch, or only for takeoff. So I'm wanting to count where ?applies="wd:Q854248" and where ?applies="wd:Q844947" ... and right now failing to do that. Might I have a steer, please; thanks --Tagishsimon (talk) 19:38, 29 June 2018 (UTC) 

SELECT ?item ?itemLabel ?launch (count(?tom) as ?crew) (sum(if(!bound(?applies),1,0)) as ?both_ways) (sum(if(?applies=wd:Q854248,1,0)) as ?up_only) (sum(if(?applies=wd:Q844947,1,0)) as ?down_only)
WHERE 
{
  values ?item {wd:Q384250} .
  ?item wdt:P31 wd:Q752783.
  ?item wdt:P361 wd:Q1775296.
  optional {?item p:P1029 ?tom.
           optional {?tom pq:P518 ?applies. } }
  optional {?item wdt:P619 ?launch.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?item ?itemLabel ?launch
Try it!

Hmm. This works. Still interested in the fail on (sum(if(?applies=wd:Q854248,1,0)) as ?up_only) in the first query. 

SELECT ?item ?itemLabel ?launch (count(?tom) as ?crew) (sum(if(!bound(?applies),1,0)) as ?both_ways) (sum(?up) as ?up_only) (sum(?down) as ?down_only)
WHERE 
{
  ?item wdt:P31 wd:Q752783.
  ?item wdt:P361 wd:Q1775296.
  optional {?item p:P1029 ?tom.
           optional {?tom pq:P518 ?applies. } }
  bind(if(?applies=wd:Q854248,1,0) as ?up)
  bind(if(?applies=wd:Q844947,1,0) as ?down)
  optional {?item wdt:P619 ?launch.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?item ?itemLabel ?launch order by ?launch
Try it!
@Tagishsimon: The reason for sum fail is that the variable ?applies is not bound, i.e. it is sort of undefined and can't be compared with an object without raising an error. The reason why the other query works is that the BOUND() function is capable of handling this undefined/unbound value, in fact it is exactly what it does. See the following query where I have used the COALESCE() function to demonstrate that ?applies for some of the crew has a value that raises an error.
SELECT ?item ?itemLabel ?tLabel (COALESCE(?applies,'error:applies is not bound') AS ?test_of_applies) (if(?applies=wd:Q854248,1,0) as ?up_only) (if(BOUND(?applies)&&?applies=wd:Q844947,1,0) as ?down_only)
WHERE 
{
  values ?item {wd:Q384250} .
  optional {?item p:P1029 ?tom. 
  optional {?tom pq:P518 ?applies. 
            ?tom ps:P1029 ?t  } }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
For more information, see 17.4.1.2 IF and 17.4.1.3 COALESCE in the SPARQL 1.1 Query Language. --Larske (talk) 11:50, 30 June 2018 (UTC)
@Larske: Thanks Larske; your query is an excellent demonstration of "In SPARQL, evaluating an unbound variable raises an error" ... I guess I'd expected the sum behaviour to be "this row has no ?applies, so add 0" rather than error, much as COUNT seems unbothered by the absence of a variable to count. Can I trouble you for one more comment, please: is it possible to conditionally bind a variable, along the lines of if ?applies=wd:Q854248 then bind 1 as ?up else don't bind anything such that instead of bind(if(?applies=wd:Q854248,1,0) as ?up) and (sum(?up) as ?up_only) in my second query, I could use (count(?up) as ?up_only)? thx --Tagishsimon (talk) 12:27, 30 June 2018 (UTC)
@Tagishsimon:You can make use of the fact that COUNT() "counts the number of times a given expression has a bound, and non-error value" by giving the variable ?up an error value, like 1/0, when the the value for ?applies don't match your criteria. Like this:
SELECT ?item ?itemLabel (COUNT(?up) AS ?up_only)
WHERE 
{
  values ?item {wd:Q384250} .
  optional {?item p:P1029 ?tom. 
  optional {?tom pq:P518 ?applies.  } }
    BIND(IF(?applies=wd:Q854248,1,1/0) AS ?up)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?item ?itemLabel
Try it!
--Larske (talk) 13:06, 30 June 2018 (UTC)
lol. That's such a wicked thing to have to do :) --Tagishsimon (talk) 13:09, 30 June 2018 (UTC)

Items where a Commons cat is the only sitelink

Can anyone think of a way to optimise this to within the time-limit? (Or, alternatively, an SQL query to do the same thing?)

I've tried a couple of thoughts, but both time out: 

SELECT (COUNT(DISTINCT(?item)) AS ?count) 
WHERE {
   ?sitelink schema:isPartOf <https://commons.wikimedia.org/>.
   FILTER (STRSTARTS(str(?sitelink), "https://commons.wikimedia.org/wiki/Category:")) . 
   ?sitelink schema:about ?item .

   OPTIONAL {
       ?sitelink2 schema:about ?item .
       FILTER(?sitelink2 != ?sitelink) .
   }
   FILTER(!bound(?sitelink2)) 
  
#   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

Approach 2: 

SELECT (COUNT(DISTINCT(?item)) AS ?count) 
WHERE {
   { 
       SELECT ?item (SAMPLE(?sitelink) AS ?sl) WHERE {
           ?sitelink schema:about ?item .
       } GROUP BY ?item
       HAVING (COUNT(?sitelink) = 1)
   }
   ?sl schema:isPartOf <https://commons.wikimedia.org/>.
   FILTER (STRSTARTS(str(?sl), "https://commons.wikimedia.org/wiki/Category:")) . 
}
Try it!

The second one can't even do the inner query.

Thanks! Jheald (talk) 17:36, 30 June 2018 (UTC)

I find ~25k items using this query: 
SELECT (COUNT(*) AS ?count) WHERE { ?sitelink schema:about [ wikibase:sitelinks 1; wdt:P31/wdt:P279* wd:Q4167836 ]; schema:isPartOf <https://commons.wikimedia.org/> }
Try it!
wikibase:sitelinks is not 100% safe to rely on (phab:T145712), but to my experience robust enough for a solid estimation. —MisterSynergy (talk) 19:04, 30 June 2018 (UTC)
Retrieved from "https://www.wikidata.org/w/index.php?title=Wikidata:Request_a_query/Archive/2018/06&oldid=711061955"