Wikidata:Request a query/Archive/2020/12

From Wikidata
Jump to navigation Jump to search

Norwegians born between 1820 untill 1920

May I have a list of norwegians born between 1820 (inluded 1820) to 1920 (included 1920) Pmt (talk) 18:08, 1 December 2020 (UTC)

@Pmt: This sort of thing - example is based on country of citizenship. If you want to go wider we could look at place of birth, or coutry for sport or whatnot.
SELECT ?item ?itemLabel ?dob
WHERE 
{
  ?item wdt:P27 wd:Q20.
  ?item wdt:P569 ?dob .
  hint:Prior hint:rangeSafe true .
  FILTER("1820-00-00"^^xsd:dateTime <= ?dob && ?dob <= "1920-00-00"^^xsd:dateTime)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Tagishsimon (talk) 18:26, 1 December 2020 (UTC)

@Tagishsimon: nice thank you! I added a line 3 wdt:P569 ?dob. And then had 14116 items, whithout P569 I have 13791. Thinking that it shows "double years" where an item has two values for P569 for instance both yy and ddmmyy. Breg Pmt (talk) 18:53, 1 December 2020 (UTC)

@Pmt: Yes, duplicates b/c two or more truthy DoBs. There are ways of dealing with that, should you ever need, such as aggregation and sampling, or selecting the min or max, or even concatenating them. Let us know if so. --Tagishsimon (talk) 19:21, 1 December 2020 (UTC)
@Tagishsimon: so in genela double P5569 shoud not be removed or depratched?
@Tagishsimon: For the query revistied I have added family name (P734) And then I want to sort out those items not having a last name. And in addition, using listeriabot i can have a line sort by and number (of each item) is that possible? I can see the total. Pmt (talk) 19:52, 1 December 2020 (UTC)
@Pmt: Certainly best to have a single truthy DoB, which can often be achieved by: deletion, or by assigning preferred rank to the best date (e.g. most accurate, most precise) and/or assigning deprecated rank (to an incorrect but sourced date).
For listeria, shall we start here - User:Tagishsimon/junk2. Listeria has dealth with suplicate DoBs ... not sure how; sample, perhaps. It'll only output a single row per ?item. The underlying query is selecting Norwegians with a DoB in the right range, and not having a family name (P734) ... is that what you wanted? --Tagishsimon (talk) 20:18, 1 December 2020 (UTC)
@Tagishsimon:Nice, I was unprecise. The original queery, but listing only the person not having P734 if possible? Pmt (talk) 20:28, 1 December 2020 (UTC)
@Pmt: I think that's what's in the Listeria query, which is, Norwegian, with dob, without P734, dob within date bounds. So, I confused :) ... if you want multiple rows for people with multiple DoBs, that can be done; but do you?
SELECT ?item ?dob
WHERE 
{
  ?item wdt:P27 wd:Q20.
  ?item wdt:P569 ?dob .
  hint:Prior hint:rangeSafe true .
  filter not exists {?item wdt:P734 []. } 
  FILTER("1820-00-00"^^xsd:dateTime <= ?dob && ?dob <= "1920-00-00"^^xsd:dateTime)
 }
Try it!
--Tagishsimon (talk) 20:43, 1 December 2020 (UTC)

@Tagishsimon: I got it now! Thank you. Pmt (talk) 21:54, 1 December 2020 (UTC)

Subtract all items with a publication date outside the given range

I'm trying to query all non-series video games with two-word titles published in 2019 for platforms other than the Nintendo Switch: 

SELECT ?game ?gameLabel
WHERE
{
  # All video games
  ?game wdt:P31/wdt:P279* wd:Q7889; 
        
    # having a date of publication
    wdt:P577 ?publication;
    # and a label
    rdfs:label $gameLabel.
  
  # Published in the year 2019
  FILTER("2019-01-01"^^xsd:dateTime <= ?publication && ?publication < "2020-01-01"^^xsd:dateTime).
  
  # Not published before 2019
  MINUS {FILTER(?publication < "2019-01-01"^^xsd:dateTime).} # doesn't work
  
  # Not part of a series
  MINUS {?game wdt:P179 []}
  
  MINUS { # not on Nintendo Switch
    ?game wdt:P400 wd:Q19610114.
  }
  
  # List only games with English labels
  FILTER(LANG(?gameLabel) = "en").
  # "^\P{Zs}+\p{Zs}+\P{Zs}+$" doesn't work
  FILTER(REGEX(?gameLabel, "^[^ ]+ +[^ ]+$")).
}
Try it!

However, the trouble with this query as it stands is that it includes all games which were re-published in 2019, ie. games with a publication date before 2019 as well as one within it. I want to exclude those rows, but I'm not sure how to express this constraint in SPARQL: you can see my best attempt on the "MINUS FILTER" line above, but that only seems to filter the publication date that matched the original filter anyway. The only "non-present data" constraint I can find in the SPARQL Wikibook negating the presence of an assertion is "FILTER NOT EXISTS", and that doesn't look like it works within expressions like "before given date".

This, I think. (Technically, this is only looking at truthy values; we could look at p:/ps: ?publication dates did you think that necessary). I've also added a filter to check that publication2 is before 2019.
SELECT ?game ?gameLabel
WHERE
{
  # All video games
  ?game wdt:P31/wdt:P279* wd:Q7889; 
        
    # having a date of publication
    wdt:P577 ?publication;
    # and a label
    rdfs:label $gameLabel.
  
  # Published in the year 2019
  FILTER("2019-01-01"^^xsd:dateTime <= ?publication && ?publication < "2020-01-01"^^xsd:dateTime).
  
  FILTER NOT EXISTS 
  {
    ?game wdt:P577 ?publication2.
    FILTER(?publication2 < "2019-00-00"^^xsd:dateTime) 
    FILTER (?publication2 < ?publication)
  }
    
  # Not part of a series
  MINUS {?game wdt:P179 []}
  
  MINUS { # not on Nintendo Switch
    ?game wdt:P400 wd:Q19610114.
  }
  
  # List only games with English labels
  FILTER(LANG(?gameLabel) = "en").
  # "^\P{Zs}+\p{Zs}+\P{Zs}+$" doesn't work
  FILTER(REGEX(?gameLabel, "^[^ ]+ +[^ ]+$")).
}
Try it!
--Tagishsimon (talk) 22:52, 1 December 2020 (UTC)

Spanish query

(This is picking up on a twitter thread https://twitter.com/espejolento/status/1333603749948846081 ... )

Once you have a list of items from the MWAPI search, you can do all the normal stuff. So here I limit the result set to items having a label in Spanish ( ?item rdfs:label ?itemLabel . filter(lang(?itemLabel)="es") ) and I've commented out a look-see whether there are any ?titles in Spanish; there are not. Let us know how or in what direction you'd like the query modified.
SELECT DISTINCT ?item ?itemLabel 
WHERE {
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:srsearch "literatura en haswbstatement:P31=Q13442814".
    ?title wikibase:apiOutput mwapi:title.
  }
  BIND(IRI(CONCAT(STR(wd:), ?title)) AS ?item)

  ?item rdfs:label ?itemLabel . filter(lang(?itemLabel)="es")
#  ?item wdt:P1476 ?title . filter(lang(?title)="es")

}
Try it!
--Tagishsimon (talk) 18:18, 1 December 2020 (UTC)
I suggest getting ?item directly from the MWAPI call. The predicate wikibase:apiOutputItem will interpret the object as an item name and constructs the corresponding item URI: 
SELECT DISTINCT ?item ?itemLabel 
WHERE {
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:srsearch "literatura en haswbstatement:P31=Q13442814".
    ?item wikibase:apiOutputItem mwapi:title.
  }

  ?item rdfs:label ?itemLabel . filter(lang(?itemLabel)="es")
#  ?item wdt:P1476 ?title . filter(lang(?title)="es")

}
Try it!
--Dipsacus fullonum (talk) 23:39, 1 December 2020 (UTC)
This is so cool, thank you so much @Dipsacus fullonum: and @Tagishsimon:!
I tried this variation:
SELECT DISTINCT ?item ?itemLabel 
WHERE {
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:srsearch "méxico en haswbstatement:P31=Q13442814".
    ?item wikibase:apiOutputItem mwapi:title.
  }

  ?item rdfs:label ?itemLabel . filter(lang(?itemLabel)="es")
#  ?item wdt:P1476 ?title . filter(lang(?title)="es")

}
Try it!
And it was interesting to note two things: 1) that it retrieves words from the description and not just the label (see Q64853421 which does not have "Mexico" in the Spanish label [Les] but in the Des) 2) that the commented line didn't retrieve what I thought it would: I would expect it to return ?itemLabel as the title string but it didn't, see https://w.wiki/ozX
Anyhow, thank you so much for your help and suggestions! Silva Selva (talk) 04:50, 2 December 2020 (UTC)
title is in ?title . So ?title needs to appear after SELECT. --- Jura 08:41, 2 December 2020 (UTC)
@Silva Selva: If you want to limit the search to labels and aliases you can use inlabel=méxico in the search string. And if you want to limit the search to Spanish labels and aliases you can use inlabel=méxico@es. See the details for these keywords at mw:Help:Extension:WikibaseCirrusSearch. --Dipsacus fullonum (talk) 10:33, 2 December 2020 (UTC)

All items that have both a category's main topic (P301) and a main subject (P921) claim

I have the suspicion that category's main topic (P301) and main subject (P921) have mostly the same meaning and thus there are no items that use both with different values. Can someone give me a query that shows all items that use both of those? ChristianKl ❪✉❫ 16:27, 2 December 2020 (UTC)

@ChristianKl: You are correct. See:
SELECT ?category ?categoryLabel ?mt ?ms
WHERE {
  ?category wdt:P301 ?mt .
  ?category wdt:P921 ?ms . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
If you have any possible discussion about categories, write in Wikidata talk:WikiProject Categories. Bye, --Epìdosis 16:32, 2 December 2020 (UTC)

Fixing WD:WikiProject_Periodicals/numbers/journals/general_properties

https://www.wikidata.org/wiki/Wikidata:WikiProject_Periodicals/numbers/journals/general_properties currently shows a table that doesn't provide useful information. Can anyone fix it to be more informative? ChristianKl ❪✉❫ 23:48, 2 December 2020 (UTC)

@ChristianKl: Which information do you want in the table? --Dipsacus fullonum (talk) 08:55, 3 December 2020 (UTC)
I see the labels was fixed by User:Mahir256. Shall it really only show 6 properties which aren't the most used for journals? --Dipsacus fullonum (talk) 09:21, 3 December 2020 (UTC)
It now looks less broken then before, but as far as the content question goes I'm not sure. I'll ping the project for it.

WikiProject Periodicals has more than 50 participants and couldn't be pinged. Please post on the WikiProject's talk page instead. ChristianKl ❪✉❫ 11:01, 3 December 2020 (UTC)


@ChristianKl: A few suggestions as a starter: publisher (P123), ISSN (P236), publication interval (P2896). Simon Cobb (User:Sic19 ; talk page) 18:35, 3 December 2020 (UTC)

Also editor (P98). Simon Cobb (User:Sic19 ; talk page) 18:37, 3 December 2020 (UTC)

Remove from output

From this query I would like to remove all item entries where the Olympiad is one of the following: 3rd unofficial Chess Olympiad (Q194793), 1st unofficial Chess Olympiad (Q166246), 2nd unofficial Chess Olympiad (Q194811), 42nd Chess Olympiad (Q2109886), 43rd Chess Olympiad (Q18432165). How to do this? 

SELECT DISTINCT ?item ?id ?olimpLabel
WHERE 
{
  ?olimp wdt:P31 wd:Q428303 .
  ?item p:P1344 ?stat .
  
  ?stat ps:P1344 ?olimp .
  
  FILTER NOT EXISTS {?stat pq:P1351 ?test . } 
  ?item wdt:P3940 ?id. 
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

Additional question out of curiosity: Whats the difference between "FILTER NOT EXISTS" and "MINUS"? ----Steak (talk) 18:52, 3 December 2020 (UTC)

Try adding
FILTER ( ?olimp NOT IN ( wd:Q194793, wd:Q166246 , wd:Q194811 , wd:Q2109886 , wd:Q18432165 ) )
Try it!
--- Jura 20:25, 3 December 2020 (UTC)
@Steak: In pattern1 MINUS { pattern2 } a result is removed from pattern1 if shared variables between pattern1 and pattern2 have the same values. In case of no shared variables nothing is removed. In pattern1 FILTER NOT EXISTS { pattern2 } a result is removed from pattern1 if any solution can be found in pattern2 given the variable bindings of pattern1. In case of no shared variables either nothing or all results are removed depending on if any solution for pattern2 exists. In case of shared variables the two expressions give the same results. --Dipsacus fullonum (talk) 00:35, 4 December 2020 (UTC)
Okay, thanks for the query help and the explanation! Steak (talk) 07:49, 4 December 2020 (UTC)

List all items where has characteristic (P1552) is used as a qualifier on properties for this type (P1963)

I want to know about how has characteristic (P1552) is currently used as a qualifier on properties for this type (P1963). Can someone write a query that lists all uses of that combination? ChristianKl ❪✉❫ 10:27, 4 December 2020 (UTC)

@ChristianKl: 
SELECT ?item ?itemLabel ?properties_for_this_type ?properties_for_this_typeLabel ?has_quality ?has_qualityLabel
WHERE
{
  ?item p:P1963 ?stm .
  ?stm ps:P1963 ?properties_for_this_type .
  ?stm pq:P1552 ?has_quality .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!
--Dipsacus fullonum (talk) 12:33, 4 December 2020 (UTC)

Member of EU (without UK)

How can this query be updated to not consider the UK. I would point that United Kingdom (Q145) has the member of (P463) but with a end time (P582) qualifier that should be used:

SELECT ?item ?itemLabel WHERE { 

 ?item p:P463/ps:P463 wd:Q458.
 MINUS {

...................... 

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

} order by (?itemLabel)

@FabC: 
select ?item ?itemLabel where {
  
 ?item wdt:P463 wd:Q458.
 MINUS {?item p:P463/pq:P582 [] .}
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
 
} order by (?itemLabel)
Try it!

Vojtěch Dostál (talk) 20:03, 4 December 2020 (UTC)

@Vojtěch Dostál: No, that that would exclude all countries which are former members of something. The object of the statement with the P582 qualifier must be tested. --Dipsacus fullonum (talk) 23:59, 4 December 2020 (UTC)
@Vojtěch Dostál, FabC: Here is a version that works (27 results instead 14): 
select ?item ?itemLabel where {
  
 ?item p:P463 ?stm.
 ?stm ps:P463 wd:Q458.
 ?stm a wikibase:BestRank. # Check rank when wdt: isn't used
 MINUS { ?stm pq:P582 [] . }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
 
} order by (?itemLabel)
Try it!
--Dipsacus fullonum (talk) 00:26, 5 December 2020 (UTC)
True, sorry :)Vojtěch Dostál (talk) 07:32, 5 December 2020 (UTC)
Thank you all! Not clear to me if the wikibase:BestRank statement can be removed --FabC (talk) 11:00, 5 December 2020 (UTC)

The ranks on Q145#P463 should really be fixed, so that wdt:P463 works. --- Jura 08:19, 5 December 2020 (UTC)

Definitively yes, they are not alternatives with a preferred option. --FabC (talk) 11:00, 5 December 2020 (UTC)
I started out with Wikidata:Property_proposal/reason_for_normal_rank and try figure out what tool to use to do it in one edit. --- Jura 11:04, 5 December 2020 (UTC)

Films by number of Wikipedia languages

Hi, I want to know all films with Wikipedia articles in more than 50 languages, but my query result doesn't show labels. If I add ?filmLabel to the Select statement, it stops working and only does once I hide the ?wikipage and ?count parts. This is my first time using the query, can you help? 

SELECT ?film (count(?wikipage) AS ?count) WHERE {
   hint:Query hint:optimizer "None".
   ?film wdt:P31 wd:Q11424.
   ?wikipage schema:about ?film .
   ?wikipage schema:isPartOf/wikibase:wikiGroup "wikipedia" .
   SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} GROUP BY ?film HAVING (?count > 50) ORDER BY DESC(?count)
Try it!

--π π π (talk) 22:20, 4 December 2020 (UTC)

@Π π π: With ?filmLabel added in your query, it will try to fetch labels for film in over 700000 Wikipedia articles, That is impossible to do without timeout. The solution is only fetch the labels after the aggregation and limiting to ?count > 50 You can do that with a named subquery: 
SELECT ?film ?filmLabel ?count
WITH
{
  SELECT ?film (COUNT(?wikipage) AS ?count)
  WHERE
  {
    hint:Query hint:optimizer "None" .
    ?film wdt:P31 wd:Q11424 .
    ?wikipage schema:about ?film .
    ?wikipage schema:isPartOf/wikibase:wikiGroup "wikipedia" .
  }
  GROUP BY ?film HAVING (?count > 50)
} AS %get_films
WHERE
{
  INCLUDE %get_films
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
}
ORDER BY DESC(?count)
Try it!
--Dipsacus fullonum (talk) 00:19, 5 December 2020 (UTC)
Wow, thanks a lot! There is no example of a number of languages query in Wikidata:SPARQL query service/queries/examples or mention of it in Wikidata:SPARQL query service/queries (as far as I can see), maybe that would have helped. --π π π (talk) 09:03, 5 December 2020 (UTC)
There is a slightly different one at Wikidata:WikiProject_Movies/Numbers#number_of_sitelinks. --- Jura 09:09, 5 December 2020 (UTC)
The technique is explained in Wikidata:SPARQL query service/query optimization#Named subqueries. --Dipsacus fullonum (talk) 11:11, 5 December 2020 (UTC)

Countries existing in a specific date

Hello, I'd like to query any sovereign state (existing or have existed) that was present on 1941-01-01 ?  – The preceding unsigned comment was added by ? (talk • contribs).

Adding english description to this query

How can I add the english language description in the results of this query? 

SELECT ?Lunar_meteorite ?Lunar_meteoriteLabel ?Lunar_meteoriteDescription ?Meteoritical_Bulletin_Database_ID WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?Lunar_meteorite wdt:P31 wd:Q60186.
  OPTIONAL { ?Lunar_meteorite wdt:P824 ?Meteoritical_Bulletin_Database_ID. }
}
LIMIT 1000
Try it!

Thank you. Trilotat (talk) 00:08, 6 December 2020 (UTC)

@Trilotat: Added - ?Lunar_meteoriteDescription --Tagishsimon (talk) 00:11, 6 December 2020 (UTC)

Query for external identifier containing a string

How would I query for items with P31 = Q13442814 and P356 contains the string "ODNB"? Thanks! - PKM (talk) 21:58, 5 December 2020 (UTC)

I don't think that it is possible in under 60 seconds, so a query will timeout. I would download a partial database dump with all scholarly article (Q13442814) and then do a grep or other form of text search for ODNB. WDumper can make the dump. --Dipsacus fullonum (talk) 23:24, 5 December 2020 (UTC)
PS. You can get some of the item: 
SELECT ?item ?doi
WHERE
{
  ?item wdt:P31 wd:Q13442814 .
  ?item wdt:P356 ?doi .
  FILTER CONTAINS ( ?doi, "ODNB" )
}
LIMIT 100
Try it!
--Dipsacus fullonum (talk) 23:35, 5 December 2020 (UTC)
@Dipsacus fullonum: that query is perfect! I’m planning to change the P31 to “biographical article” (among a bunch of other things), so I should be able to run the query after each batch is done and get the next 100. Thanks! - PKM (talk) 04:58, 6 December 2020 (UTC)
@PKM: I doubt you will be able to find all that way. When the frequency of search hits gets lower, it will take longer to do the search and at one point you cannot any longer find more articles without timeout. --Dipsacus fullonum (talk) 09:35, 6 December 2020 (UTC)
Right you are; I got the first 200 (out of ?477 based on a search).
SELECT DISTINCT ?item ?itemLabel ?doi
WHERE 
{
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi 
  {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:srsearch "ODNB haswbstatement:P356 haswbstatement:P31=Q13442814".
                    ?item wikibase:apiOutputItem mwapi:title.
  }
  ?item wdt:P356 ?doi .
  FILTER CONTAINS ( ?doi, "ODNB" )
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

BTW DOI formatter (P8404) on Oxford Dictionary of National Biography ID (P1415) includes "ODNB": Property:P1415#P8404. --- Jura 10:11, 6 December 2020 (UTC)

I wasn't aware that you can search for content of external identifiers with CirrusSearch. Thank you, that is good to know. In hindsight it isn't surprising, --Dipsacus fullonum (talk) 12:35, 6 December 2020 (UTC)
If you pick one of the items, e.g. Q56001432, you can inspect what Cirrus (currently) indexes: https://www.wikidata.org/w/index.php?title=Q56001432&action=cirrusdump . Obviously, if there is some other string before or after ODNB, it wont work. So maybe there are more than 1900. --- Jura 12:54, 6 December 2020 (UTC)
I've processed 200 of these today. I'll tackle the rest later. To be clear, my goal is to export a .csv file with the QID and DOI so I can extract the ODNB identifier from the DOI. - PKM (talk) 00:07, 7 December 2020 (UTC)
The good news is I waited an hour and got another 100 items on the query. The bad news is I notice that the ~250 items I did via QS have an error which I need to fix (published in (P1433) was inserted as a qualifier on P31 instead of as a statement). I guess I'll work through my contributions list by hand and fix those unless someone knows how to query for P31=Q19389637 with qualifier P1433... PKM (talk) 01:55, 7 December 2020 (UTC)
@PKM:
SELECT ?item ?itemLabel ?qualy
WHERE 
{
  ?item p:P31 ?stat .
  ?stat ps:P31 wd:Q19389637.
  ?stat pq:P1433 ?qualy.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Tagishsimon (talk) 02:33, 7 December 2020 (UTC)
@Tagishsimon: ❤️❤️❤️ I’ll get these sorted on Monday my time. - 03:08, 7 December 2020 (UTC)
And all done now. Thanks everyone for your help! - PKM (talk) 01:05, 8 December 2020 (UTC)

Expanding the listeria list for anatomical metaclasses

At https://www.wikidata.org/wiki/Wikidata:WikiProject_Anatomy/Ontology_of_Anatomy/draft there's a list with anatomical metaclasses. I wanted to list both the description for the metaclass and the description for the underlying class. I can figure out a general description command but not how to get the two different descriptions.

In cases where there are multiple metaclasses for the same class all metaclasses should have their own row.

Additional it would be nice to list all model item (P5869) instances of each metaclass in an additional column. ChristianKl ❪✉❫ 14:15, 6 December 2020 (UTC)

  • @ChristianKl: I think the second description needs to come from sparql. There is (or was) a problem using the default ?classDescription variable. I edited the Listeria. It's now somewhat suboptimal if there are several classes. --- Jura 14:59, 7 December 2020 (UTC)

Dates ranges

Hello, for a given date property, say date of birth (P569), I'd like to query that with qualifiers earliest date (P1319) latest date (P1326) such as there, having date of birth (P569) precision less precise than decade. Thanks !

@Bouzinac: Something like this - you'll not be able to do this across all DoBs in the database; rather, you'll need to select a subset of items against which to enquire about the date issue. In this example, I've used a VALUES statement to restrict the query to the item you pointed to.
Note that the codes for precision are 0: billion years, 1: hundred million years, 3: million years, 4: hundred thousand years, 5: ten thousand years, 6: millennium, 7: century, 8: decade, 9: year, 10: month, 11: day, 12: hour, 13: minute, 14: second ... I seem to have given you decade or lower; you may want to tweak this to get < decade.
#Cats
SELECT ?item ?itemLabel ?precision ?value ?earliest ?latest
WHERE 
{
  VALUES ?item {wd:Q2827148}
  ?item p:P569 ?stat . 
  ?stat psv:P569 [wikibase:timePrecision ?precision; wikibase:timeValue ?value].
  OPTIONAL {?stat pq:P1319 ?earliest . }
  OPTIONAL {?stat pq:P1326 ?latest . }
  filter(?precision < 9)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Tagishsimon (talk) 17:34, 7 December 2020 (UTC)
Thank you Tagishsimon, I've refined your query this way https://w.wiki/pZ$ , thanks againBouzinac 💬●✒️●💛 19:21, 7 December 2020 (UTC)
@Bouzinac, Tagishsimon: A tip for optimization: It is normally considerably faster to search for exact values than to apply a filter. In this case query will be considerably faster by changing 
filter(?precision < 8)
to 
VALUES ?precision { 0 1 2 3 4 5 6 7 }
This might worth mentioning at Wikidata:SPARQL query service/query optimization. --Dipsacus fullonum (talk) 10:49, 8 December 2020 (UTC)

Query that finds all items that are subclasses of direct anatomical metaclass (Q103997018) but not instances of an instance of anatomical entity (Q27043950)

To query items about anatomical entities that don't yet have an anatomical metaclass I need a query that finds all items that are subclasses of direct anatomical metaclass (Q103997018) but not instances of an instance of anatomical entity (Q27043950). ChristianKl ❪✉❫ 20:33, 7 December 2020 (UTC)

@ChristianKl: The only item that are a subclass of Q103997018 is Q103997018 itself: 
SELECT ?item ?itemLabel
WHERE
{
  ?item wdt:P279* wd:Q103997018 . # Subclass of direct anatomical metaclass
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!
but a query for your request (with the same result) is: 
SELECT ?item ?itemLabel
WHERE
{
  ?item wdt:P279* wd:Q103997018 . # Subclass of direct anatomical metaclass
  MINUS
  {
    ?item wdt:P31/wdt:P31 wd:Q27043950 . # Not instance of an instance of anatomical entity
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!
--Dipsacus fullonum (talk) 11:10, 8 December 2020 (UTC)
Thanks, I did means instances of direct anatomical metaclass (Q103997018) and not subclasses of it I guess. I changed the query to: 
SELECT ?item ?itemLabel
WHERE
{
  ?item wdt:P31* wd:Q103997018 . # Subclass of direct anatomical metaclass
  MINUS
  {
    ?item wdt:P31/wdt:P31 wd:Q27043950 . # Not instance of an instance of anatomical entity
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!
Unfortunately, earwax (Q76418) which instance of (P31) biogenic substance type (Q103907369) which is instance of (P31) direct anatomical metaclass (Q103997018) is still in the list. I'm searching for a query that filter that out. ChristianKl ❪✉❫ 14:24, 8 December 2020 (UTC)

Hi,

I would like to list every featured article (Q17437796) from EN, ES, DE, ZH, RU or PT which are also in deepcats from french stubs (Q7046062 fr:Catégorie:Catégorie d'ébauche), is that possible ? Also, if there's a way to sort stubs by portals, it would be great! Thanks. --LD (talk) 14:18, 8 December 2020 (UTC)

@LD: You cannot do a deepcat search on fr:Catégorie:Catégorie d'ébauche due to too many subcategories (try it). It would be possible to extract all subcategories using the categories namespace but it isn't possible to get more than 10000 category members anyway due a limit in the MWAPI service. So the answer is that it isn't possible using a SPARQL query. --Dipsacus fullonum (talk) 14:35, 8 December 2020 (UTC)
Maybe with Petscan. --- Jura 14:38, 8 December 2020 (UTC)
Hm, thanks for answers. Petscan won't work if there's too many subcat. Please, what would be the code for listing articles, without restrictions, may I ask ?
I can still try to do an intersection on my own ^^' --LD (talk) 14:40, 8 December 2020 (UTC)
Some with Petscan: https://petscan.wmflabs.org/?psid=17975754 --- Jura 14:42, 8 December 2020 (UTC)
Hm, I tried, even with 3 or 5 deth it works. Thanks. Anyway to sort this out by portal ? ^^' --LD (talk) 14:52, 8 December 2020 (UTC)
You can filter by portal: https://petscan.wmflabs.org/?psid=17975787 --- Jura 14:55, 8 December 2020 (UTC)
@LD: Here is a query as requested using a much smaller stub category: 
SELECT ?title_fr ?sitelink_fr ?item ?article ?wiki
WITH
{
  SELECT ?title_fr
  WHERE
  {
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:api "Search" .
      bd:serviceParam wikibase:endpoint "fr.wikipedia.org" .
      bd:serviceParam mwapi:srnamespace "0" .
      bd:serviceParam mwapi:srsearch 'deepcat:"Wikipédia:ébauche personnalité danoise"' .
      ?title wikibase:apiOutput mwapi:title .
    }
    BIND (STRLANG(?title, "fr") AS ?title_fr)
  }
} AS %get_stubs
WITH
{
  SELECT ?title_fr ?sitelink_fr
  WHERE
  {
    INCLUDE %get_stubs
    ?sitelink_fr schema:name ?title_fr.
  }
} AS %get_sitelinks
WITH
{
  SELECT ?title_fr ?sitelink_fr ?item
  WHERE
  {
    INCLUDE %get_sitelinks
    ?sitelink_fr schema:isPartOf <https://fr.wikipedia.org/>.
    ?sitelink_fr schema:about ?item.
  }
} AS %get_items

WHERE
{
  INCLUDE %get_items
  VALUES ?wiki {
    <https://en.wikipedia.org/> <https://es.wikipedia.org/> <https://de.wikipedia.org/>
    <https://zh.wikipedia.org/> <https://ru.wikipedia.org/> <https://pt.wikipedia.org/>
  }
  ?article schema:isPartOf ?wiki.
  ?article schema:about ?item.
  ?article wikibase:badge wd:Q17437796.
}
Try it!
--Dipsacus fullonum (talk) 15:00, 8 December 2020 (UTC)
Oh, that's really more complex than what I was trying to do haha, thanks a lot, I'll do my ant work now. Have a good day! --LD (talk) 15:03, 8 December 2020 (UTC)

Query about books written by women

Hi all. I am trying to do a query in which I get a list of books written by women that exist in en.wikipedia but not in pt.wikipedia, and in the query list I wanted to see the url to the en.wiki article... kinda like this, but with columns saying "article in portuguese (book), article in portuguese (writer), article in english (book), article in english (writer), item wikidata, site links.

Can someone help me with this? Tuga1143 (talk) 17:31, 9 December 2020 (UTC)

@Tuga1143: The basic problem here, I think, it determining what items represent "books" in Wikidata's what-passes-for-an-ontology. AFAIK, we start from {{|Q47461344}} ... ideally we'd also look at its subclass tree, but that is 70k items; and whether we look for wdt:P31/wdt:P279+ or wdt:P31/wdt:P279* (both interrogating the subclass tree) we quickly run into 10s of millions of academic papers and, in short, the query times out.
A structure for the query might be as follows. Others might have additional views on how to recognise books:
SELECT ?item ?itemLabel ?author ?authorLabel ?sitelink ?article WITH { SELECT ?item ?sitelink ?article
WHERE 
{
  ?item wdt:P31 wd:Q47461344.            #written work
  ?article schema:about ?item ;          #on en wiki
          schema:isPartOf <https://en.wikipedia.org/> ;
          schema:name ?sitelink .
} } as %i
WHERE
{
  INCLUDE %i
  ?item wdt:P50 ?author . 
  ?author wdt:P21 wd:Q6581072 .          #female author
  filter not exists {                    #not on pt wiki
  ?article2 schema:about ?item ;
           schema:isPartOf <https://pt.wikipedia.org/> .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Tagishsimon (talk) 19:37, 9 December 2020 (UTC)

Hi @Tagishsimon:, thank you so much for your help. I was trying to generate some lists about written works by women, sculptures made by women, paintings, etc, for the portuguese wikiproject about women. Right now I'm trying to make a table like this one using the query you gave me, so that users in pt.wiki may see a simple table with the red link of the written work for pt.wiki, the link to the written work in en.wiki and the wikidata item. I'll try to do it. If i have any problem, could I continue to make questions and ask for help? Tuga1143 (talk) 19:58, 9 December 2020 (UTC)

@Tuga1143: Thanks. Yes of course, we'll do all we can to help; all questions welcome. --Tagishsimon (talk) 20:09, 9 December 2020 (UTC)
I'm sorry for being terrible at this... jesus christ.
I'm trying to do a table for the query, having something like:
LIMIT 100
|section=
|sort=
|columns= label:article in portuguese,article in english wiki,item:Item Wikidata
|links= red_only
|row_template= subst:Usuário:Tuga1143/Listas template
|thumb=
|freq=
|min_section=
But, in the line about columns, what can I write for it to be possible to generate a table having three columns saying "article in portuguese (redlink), article in english wiki (with link), item:Item Wikidata" and show red links only? Tuga1143 (talk) 20:15, 9 December 2020 (UTC)

Hi @Tagishsimon:, how are you? I hope you're ok. I've managed to do what I was thinking about, as you can see here. The second and third columns are perfectly fine... but im trying to make the first column show only the red articles and, instead, it is showing me blue articles + wikidata links... do you have any idea? IF it helps, the template im using is this one. Thank you so much. Tuga1143 (talk) 09:22, 10 December 2020 (UTC)

@Tuga1143: In the query at pt:Usuário:Tuga1143/Testes15 you have the filter 
filter not exists {                    #not on pt wiki
 ?article schema:about ?item ;
          schema:isPartOf <https://pt.wikipedia.org/> .}
But it doesn't filter out anything because the variable ?article is already bound to a sitelink to the book on English Wikipedia, and the English sitelink never exists on Portugese Wikipedia. If you look above you will see that Tagishsimon used a new variable named ?article2 in the filter. When you do that it will filter out the result if it is possible to find a value for ?article2 on Portuguese Wikipedia. --Dipsacus fullonum (talk) 11:22, 10 December 2020 (UTC)
@Dipsacus fullonum: I see... so it's impossible to make query showing the red links like in the example I gave but, instead of biographies, showing books written by women still missing in pt.wiki, sculptures made by an artist women and still missing in pt.wiki, art work made by women still missing in pt.wiki, etc? Tuga1143 (talk) 11:41, 10 December 2020 (UTC)
@Tuga1143: No, I didn't say that it is not possible. There is one problem though: You cannot know the article name on Portuguese Wikipedia which may be different from the Portuguese label at Wikidata due to disambiguation or other reasons. Otherwise you can change the name for article variable in the filter to an unused name to get the filter to work as intended. --Dipsacus fullonum (talk) 11:55, 10 December 2020 (UTC)
Sorry, I didnt want to put words on your mouth. Could you help me and tell me how to change the article variable in the filter to an unsused name (equal to the one in en.wiki) to get the filter to work as intended? Tuga1143 (talk) 11:58, 10 December 2020 (UTC)

Duplicate BHL items

The items:

represent the same publication, for which separate DOIs have been issued in error.

Our friends at the Biodiversity Heritage Library have asked for our our help in identifying similar cases, in order that they may manually check and resolve them in their data (and thus in ours).

We could use a query to find pairs of items with different DOIs, but where titles (case insensitive?) and perhaps other properties, such as author (item or string), date, etc., match closely if not exactly (note the minor difference in labels, above).

To reduce processing, checks can be limited to items with a BHL bibliography ID (P4327) property.

The above example should be found by such a query. Accordingly, I will not merge the items yet.

Can anyone assist, please? Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 09:04, 10 December 2020 (UTC)

@Pigsonthewing: calculate proximity between strings is not easy (to other people with SPARQL-fu: is there even a way ta do a Levenshtein distance in SPARQL?).
That said, here is already a query for pair of publication with the exact same author (item), exact same date and exact same title (limited to 1000 first results):
SELECT * WHERE {
  ?q1 wdt:P356 ?doi1 ; wdt:P4327 ?id1 ; wdt:P50 ?aut ; wdt:P577 ?date ; wdt:P1476 ?title .
  FILTER regex (?doi1, "^10.5962/BHL.").
  ?q2 wdt:P356 ?doi2 ; wdt:P4327 ?id2 ; wdt:P50 ?aut ; wdt:P577 ?date ; wdt:P1476 ?title  .
  FILTER regex (?doi2, "^10.5962/BHL.").
  FILTER ( str(?q1) < str(?q2) )
}
LIMIT 1000
Try it!
There is probably some false-positive and a lot to check. I'll let you check and tell me if it helps, how it can be more helpful, and so on.
Cheers, VIGNERON (talk) 13:51, 10 December 2020 (UTC)
@VIGNERON: That's very helpful, even with strict matching. Thank you. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 14:17, 10 December 2020 (UTC)

Getting Wikidata QIds for ~1000 Wikipedia articles (various languages)?

I'm trying to bring three lists together: people's names, wikipedia article links and wikidata ids. I've obtained ~7k wikidata ids for my 40k names, and have worked out how to ask for the wikipedia article name associated with a wikidata id in each given language in turn. So far, so good, but I don't know how to approach the wikipedia->wikidata pairing. I have ~1000 names+wplink pairs, and I'd like to get the missing wikidata id. How can I get hold of the QId from a lang+wplink? eg: en.wikipedia.org/wiki/Fernando_Botero Thanks! Scarabocchio (talk) 20:24, 10 December 2020 (UTC)

@Scarabocchio: With the schema:about predicate. E.g. 
SELECT ?item ?itemLabel ?itemDescription
WHERE
{
  <https://en.wikipedia.org/wiki/Fernando_Botero> schema:about ?item .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Dipsacus fullonum (talk) 02:22, 11 December 2020 (UTC)


SELECT ?item ?itemLabel ?itemDescription
WHERE
{
  [] schema:about ?item ; schema:isPartOf <https://en.wikipedia.org/> ; schema:name "Fernando Botero"@en
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

@Scarabocchio: Another way: slightly different, but essentially the same. --- Jura 14:18, 11 December 2020 (UTC)

Prefix should be s: but it return wds:

Hi, I expect this to return s: prefix but how come it return with prefix wds: instead ? wds:Q36949-91bc1581-43b0-78c1-4970-c2480d22c56c

Because according to this entity ttl https://www.wikidata.org/wiki/Special:EntityData/Q36949.ttl

The value prefix is s: not wds: , you can search Q36949-91bc1581-43b0-78c1-4970-c2480d22c56c at that ttl. 

select * 
WHERE {
  wd:Q36949 p:P2218 ?vv.
}
Try it!
It's not really my area, but AFAICS the turtle file uses @prefix s: <http://www.wikidata.org/entity/statement/> . where the RDF representation is PREFIX wds: <http://www.wikidata.org/entity/statement/> [3]. Why the powers that be in WMDE chose to be inconsistent in this matter I cannot say. You could ask at Wikidata:Contact the development team/Query Service and search, but tbh I doubt anyone will know. --Tagishsimon (talk) 11:43, 11 December 2020 (UTC)

Optimizing a SPARQL query -- times out when I add Labels

I am attempting to write a query to return all QIDs with occupation (P106) of visual artist (and all subclasses) with en wiki articles, with optional inclusion of sex or gender (P21) and date of birth (P569). Without labels, it runs without timeout (~90,000 results). Adding labels causes the time out. The query is here: https://w.wiki/pYR and below 

SELECT DISTINCT ?item ?itemLabel ?itemDescription ?dob ?gender WHERE {
  {
  SELECT ?item  WHERE {
  ?item wdt:P106/wdt:P279* wd:Q3391743.   #art Q483501  visart Q3391743 /wdt:P279*
  ?item wdt:P31 wd:Q5.
  ?article schema:about ?item ; schema:isPartOf <https://en.wikipedia.org/> ;  schema:name ?page_titleEN .
  OPTIONAL {?item wdt:P21 ?gender.}
  OPTIONAL {?item wdt:P569 ?dob.}
  }  
  ORDER BY DESC(?item)
#  LIMIT 50
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }

}
LIMIT 10000
Try it!

My research led me to this Phabricator page, which shows these timeouts are an ongoing issue, as the database has grown so much. Per the suggestions on that page, I tried to nest the queries, putting the Label service outside the main SELECT, though this didn't have a huge impact. The Phab page also mentions query hints, but I couldn't get that to work, or find documentation on it.

Also, I seem to get duplicate entries, when the subject has two dates of birth list (e.g. there is historical uncertainty as to which is correct). I can clean this up afterwards, but it might be simple to do now.

Can someone offer me guidance on how to optimize this to fit the timeout? Thanks. --Theredproject (talk) 13:37, 7 December 2020 (UTC)

@Theredproject: This is your big problem: 400k distinct items. As a rule of thumb, getting labels for more than ~100k items is an ask.
SELECT DISTINCT ?item WHERE {
  ?item wdt:P106/wdt:P279* wd:Q3391743.   hint:Prior hint:gearing "forward".
}
Try it!
I'd suggest getting the results in three bites, using a named subquery; here's the second bite. Remove OFFSET 30000 to get the first bite; use OFFSET 60000 LIMIT 40000 for the final bite. If you have not come across it, Wikidata:SPARQL query service/query optimization may be your friend. Finally I removed schema:name ?page_titleEN since it was not doing anything useful.
SELECT DISTINCT ?item ?itemLabel ?itemDescription ?dob ?gender WITH { 
SELECT ?item WHERE {
  ?item wdt:P106/wdt:P279* wd:Q3391743.   hint:Prior hint:gearing "forward".
  ?article schema:about ?item ; schema:isPartOf <https://en.wikipedia.org/> .
} OFFSET 30000 LIMIT 30000}  as %i
WHERE
{
  INCLUDE %i
  ?item wdt:P31 wd:Q5.
  OPTIONAL {?item wdt:P21 ?gender.}
  OPTIONAL {?item wdt:P569 ?dob.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }

}
Try it!
--Tagishsimon (talk) 17:25, 7 December 2020 (UTC)
Nice .. tried it, but hadn't thought of OFFSET and LIMIT --- Jura 20:54, 7 December 2020 (UTC)
@Tagishsimon: ThAnKyOu! I was able to get through the query. I'm still getting duplicates. Is that because of problems endemic to Wikidata, or because according to W3, "Using LIMIT and OFFSET to select different subsets of the query solutions will not be useful unless the order is made predictable by using ORDER BY." Does the query need an ORDER BY? If so, where?
I had thought about segmenting it, but couldn't think of a way to do that with subclasses. Didn't think about OFFSET and LIMIT. Also, didn't know that named subquery is faster -- I see that now in the Query Optimization documentation. I read through that earlier, but I saw it earlier in my research and I couldn't parse it. I did catch the small to big Order Tweaking. Having seen your edits here, I think I have a better angle into the Optimization docs. TX --Theredproject (talk) 12:12, 8 December 2020 (UTC)
@Theredproject: Yes, you need to use ORDER BY to be sure to get all results when slicing up with LIMIT and OFFSET. That is because without ORDER BY the order of the results is undefined and may change each time you run the query. The only thing here you can order by is ?item which is fine to use because all values are unique. Insert ORDER BY ?item just before OFFSET 30000 LIMIT 30000. --Dipsacus fullonum (talk) 14:04, 8 December 2020 (UTC)
PS: Well, you could also order by ?article with the same effect. However, I wouldn't recommend that because that would remove an opportunity to eliminate that variable by the optimizer as it is not otherwise used. I don't know if the opportunity is used, but why not make it possible? --Dipsacus fullonum (talk) 14:15, 8 December 2020 (UTC)

┌────────────────────────────────────────────────────────────────────────────────────────────────────┘ @Tagishsimon, Dipsacus fullonum: This time the query returned results in order, but I noticed that the results seemed to have gaps at the start/end of each chunk. I counted and total number of lines is ~13,000 less. The previous query that returned without labels had 94963 results. This trio had 81755 total. The new queries still have duplicate QIDs, where the item has two dates of birth. Here is the tail of the first query (sorry, I don't know how to format a CSV for a talk page (without going through the creation of a wiki table, which is unnecessary and also would make me cry;-):

And the head of the second query:

So it seems like it is skipping some chunks. Any idea why? Also, do you know how to suppress the second result per QID when there is a second birthday value? Thanks. --Theredproject (talk) 20:48, 8 December 2020 (UTC)

@Theredproject: I have four comment/answers:
  1. I don't understand what you mean by "this trio". What trio of what?
  2. The total number of results are unchanged as you can see by either removing the label service and the use of LIMIT/OFFSET to get all results at once, or by adding the number of results in all the slices.
  3. No, the results don't come in order now. The order is still undefined because there is no ordering on the outer query which may reorder the results from the ordered subquery in any way. Therefore it makes no good sense to talk about gaps. Did you sort the results before making the lists of head and tail of query results you quote above?
  4. The standard way to eliminate multiple values for variables such as ?dob here is to group by all other variables and use some kind of aggregation of the variable with several values. Do that by adding GROUP BY ?item ?itemLabel ?itemDescription ?gender at the very end of the query, and replace ?dob in the SELECT clause by an aggregation. You can use (GROUP_CONCAT(?dob) AS ?dobs) to list all values or e.g. (SAMPLE(?dob) AS ?sample_dob) to get one sample. --Dipsacus fullonum (talk) 09:17, 9 December 2020 (UTC)
@Dipsacus fullonum: thanks for this final bit of guidance. I got it to work. Posting it here, in case others stumble upon this thread trying to figure out something similar. https://w.wiki/qF4 --Theredproject (talk) 22:11, 11 December 2020 (UTC)

Altering this query to include all subclasses of meteorite (Q60186)

I'd like this query to expand coverage and include all subclasses of meteorite (Q60186). Can someone edit the query below? 

SELECT ?item ?itemLabel ?Meteoritical_Bulletin_Database_ID WHERE {
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en". }
  OPTIONAL {
    ?item wdt:P824 ?Meteoritical_Bulletin_Database_ID.
    BIND(REPLACE(?Meteoritical_Bulletin_Database_ID, "^([0-9]+).*$", "$1") AS ?number)
    BIND(STRAFTER(?Meteoritical_Bulletin_Database_ID, ?number) AS ?after)
  }
}
ORDER BY (xsd:integer(?number)) (?after)
LIMIT 1000
Try it!

Thank you. Trilotat (talk) 21:16, 11 December 2020 (UTC) 

SELECT DISTINCT ?item ?itemLabel ?Meteoritical_Bulletin_Database_ID ?instanceofLabel
WHERE 
{
  { ?item wdt:P279*/wdt:P31 wd:Q60186 } UNION { ?item wdt:P824 [] }   #both
  # ?item wdt:P279*/wdt:P31 wd:Q60186                                 #only Q60186 and subclasses
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en". }
  OPTIONAL {
    ?item wdt:P824 ?Meteoritical_Bulletin_Database_ID.
    BIND(REPLACE(?Meteoritical_Bulletin_Database_ID, "^([0-9]+).*$", "$1") AS ?number)
    BIND(STRAFTER(?Meteoritical_Bulletin_Database_ID, ?number) AS ?after)
  }
  OPTIONAL { ?item wdt:P31 ?instanceof }
}
ORDER BY (xsd:integer(?number)) (?after)
LIMIT 1000

Try it!

@Trilotat: As you want it to be expanded, I take it you'd want both to be included. --- Jura 00:05, 12 December 2020 (UTC)

I'd like a list of authors with links to Wikisource but not any other project. The catch is that there must also be an item which is a subclass of written work (Q47461344) linking to that author. NMaia (talk) 06:32, 13 December 2020 (UTC)

@NMaia:
SELECT ?p ?pLabel (SAMPLE(?ẁork) as ?work)
{
    ?work wdt:P50 ?p .
    ?p wikibase:sitelinks 1 . 
    [] schema:about ?p ; schema:isPartOf / wikibase:wikiGroup "wikisource" .
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}  
GROUP BY ?p ?pLabel
LIMIT 10

Try it!. It's not limited to written works, but P50 should get you mostly that. --- Jura 12:19, 13 December 2020 (UTC)

That query only finds authors with link to one Wikisource. The request said "links to Wikisource" (plural). NMaia, did you mean authors with links to more than one Wikisource? --Dipsacus fullonum (talk) 20:12, 13 December 2020 (UTC)
@Dipsacus fullonum: It can link to more than one Wikisource. Only one would be unnecessarily limiting. NMaia (talk) 01:31, 14 December 2020 (UTC)
@NMaia: I made a query which is almost what you requested. It excludes the authors from other projects than Wikisource by checking that the number of counted Wikisources equals the total number of sitelinks. The difference from the request is that the query only includes authors which are instance of (P31) human (Q5). I had to include that limitation to avoid timeout. 
SELECT ?author ?authorLabel
WITH
{
  SELECT ?author
  {
    [] schema:about ?author ; schema:isPartOf / wikibase:wikiGroup "wikisource" .
    ?author wikibase:sitelinks ?sitelinks . 
    ?author wdt:P31 wd:Q5 .
  }
  GROUP BY ?author
  HAVING (COUNT(?author) = SAMPLE(?sitelinks))
} AS %1
WITH
{
  SELECT ?author
  WHERE
  {
    INCLUDE %1
    FILTER EXISTS
    {
      ?work wdt:P50 ?author .
      ?work wdt:P31/wdt:P279* wd:Q47461344 . # Written work exists
      hint:Prior hint:gearing "forward" .
    }
  }
} AS %2
WHERE
{
  INCLUDE %2
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!
--Dipsacus fullonum (talk) 03:11, 14 December 2020 (UTC)
@Dipsacus fullonum: Thank you! I think this should realistically cover most cases. NMaia (talk) 03:15, 14 December 2020 (UTC)
@NMaia: The previous query did miss 1869 authors who either aren't one human or aren't marked as such. Here is a query for these cases: 
SELECT ?author ?authorLabel
WITH
{
  SELECT DISTINCT ?author
  WHERE
  {
    ?work wdt:P50 ?author .
    [] schema:about ?author ; schema:isPartOf / wikibase:wikiGroup "wikisource" .
    MINUS { ?author wdt:P31 wd:Q5 . }
  }
} AS %0
WITH
{
  SELECT ?author
  {
    INCLUDE %0
    [] schema:about ?author ; schema:isPartOf / wikibase:wikiGroup "wikisource" .
    ?author wikibase:sitelinks ?sitelinks . 
  }
  GROUP BY ?author
  HAVING (COUNT(?author) = SAMPLE(?sitelinks))
} AS %1
WITH
{
  SELECT ?author
  WHERE
  {
    INCLUDE %1
    FILTER EXISTS
    {
      ?work wdt:P50 ?author .
      ?work wdt:P31/wdt:P279* wd:Q47461344 . # Written work exists
      hint:Prior hint:gearing "forward" .
    }
  }
} AS %2
WHERE
{
  INCLUDE %2
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!
--Dipsacus fullonum (talk) 06:28, 14 December 2020 (UTC)

@NMaia: I found a way to get all results at once without timeout. This query finds approx. 4232 authors where the previous two queries each found about 2366 humans and 1869 other authors. 

SELECT ?author ?authorLabel
WITH
{
  SELECT ?author
  {
    ?wikisource schema:about ?author ; schema:isPartOf / wikibase:wikiGroup "wikisource" .
    ?author wikibase:sitelinks ?sitelinks . 
    ?work wdt:P50 ?author .
  }
  GROUP BY ?author
  HAVING (COUNT(DISTINCT ?wikisource) = SAMPLE(?sitelinks))
} AS %1
WITH
{
  SELECT ?author
  WHERE
  {
    INCLUDE %1
    FILTER EXISTS
    {
      ?work wdt:P50 ?author .
      ?work wdt:P31/wdt:P279* wd:Q47461344 . # Written work exists
      hint:Prior hint:gearing "forward" .
    }
  }
} AS %2
WHERE
{
  INCLUDE %2
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!

--Dipsacus fullonum (talk) 07:08, 14 December 2020 (UTC)

Exclude members of duo

I would like to revise this query w:en:Wikipedia:WikiProject Women in Red/Missing articles by dictionary/Encyclopædia Britannica Online to exclude rows that are members of a duo/married couple with their own encyclopedia article. E.g., exclude Jeanne-Claude (Q3351266), who is part of (P361) Christo and Jeanne-Claude (Q158477), because the latter QID already has its own enwp article. czar 18:34, 14 December 2020 (UTC)

@Czar: Done in the page's SPARQL (diff). --Tagishsimon (talk) 18:42, 14 December 2020 (UTC)
@Czar: In addition, it is possible to remove individual entries, by adding SPARQL along the lines of FILTER ( ?item NOT IN ( wd:Q12345, wd:Q67890 ) ) - which might be necessary to properly weed out rows which are never going to yield an article. --Tagishsimon (talk) 19:09, 14 December 2020 (UTC)

Once working, no longer working query

Hello, what's wrong with that query ? It is supposed to show a count of countries per year (not sure if it's the list or the count). Many thanks, 

select ?country (group_concat(?NatureLabelFr;separator=" ,") as ?NatureLabelFr) ?countryLabel  ?creationDate ?dissolutionDate
with {
select ?country (coalesce(?countryLabelFr, ?countryLabelEn,  ?country) as ?countryLabel) ?creationDate ?dissolutionDate{
?country wdt:P31/wdt:P279* wd:Q3624078 # sovereign states, I don’t know if it’s the right item
MINUS { ?country (wdt:P31/wdt:P279*) wd:Q1790360. }#empires coloniaux
MINUS { ?country (wdt:P31/wdt:P279*) wd:Q1371288. } #états vassals
optional { ?country rdfs:label ?countryLabelFr filter(lang(?countryLabelFr)= "fr")} .
optional { ?country rdfs:label ?countryLabelEn filter(lang(?countryLabelEn)= "en")}
optional { ?country wdt:P571 ?creationDate }
optional { ?country wdt:P576 ?dissolutionDate }
} order by ?countryLabel
} as %datas
where {
include %datas .
optional{
?country wdt:P31/rdfs:label ?NatureLabelFr filter(lang(?NatureLabelFr)= "fr") .
}
} group by ?country ?countryLabel  ?creationDate ?dissolutionDate
Try it!
@Bouzinac : The query fails because it is invalid SPARQL code. Variable names after "AS" may not already be in scope so the construct group_concat(?NatureLabelFr; separator=" ,") as ?NatureLabelFr) is not allowed. You have to use a new variable name. At one point the engine was more forgiving for that error but now if fails without a proper error message. It is tracked in phab:T235540. --Dipsacus fullonum (talk) 00:56, 16 December 2020 (UTC)

Getting the point-in-time of a property?

I think I'm confused about property hierarchy. (And maybe using the wrong terminology). For this query: 

SELECT ?item ?pl ?signatory ?date2 ?short ?itemLabel (CONCAT("Public Law ",?pl, ". ", STRBEFORE(?stat,"-"), " Stat ", STRAFTER(?stat,"-"),".") as ?text) 
WHERE {
  ?item wdt:P3825 ?stat ;
        wdt:P3837 ?pl .
  OPTIONAL { ?item wdt:P585 ?date . }
  OPTIONAL { ?item wdt:P1813 ?short . }
  OPTIONAL { ?item p:P1891 ?signatory .
             #?signatory wdt:P585 ?date2 .
           }
  OPTIONAL { 
}
  FILTER (CONTAINS(?pl, '116-')) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en"  }
}
LIMIT 10
Try it!

I would like ?date2 to be the point in time (P585) property of the signatory signatory (P1891) property e.g. as shown in https://www.wikidata.org/wiki/Q62774908#Q62774908$d9cef975-4de4-7ad7-08b8-1a686db021a0 but…it doesn't seem to work. Either in its own OPTIONAL stanza or together with the ?signatory definition. How do I do I get that? (Also, for bonus points, format the date as "January 16, 2019" rather than "16 January 2019"). Thanks! This is for w:Talk:List_of_acts_of_the_116th_United_States_Congress#Wikidata_table_experiment Jhawkinson (talk) 01:58, 18 December 2020 (UTC)

@Jhawkinson: This. Tutorial on qualifiers. You'd have to do some string slicing to form the date in your preferred format; there's no native control of the date format.
SELECT ?item ?pl ?signatory ?date2 ?short ?itemLabel (CONCAT("Public Law ",?pl, ". ", STRBEFORE(?stat,"-"), " Stat ", STRAFTER(?stat,"-"),".") as ?text) 
WHERE {
  ?item wdt:P3825 ?stat ;
        wdt:P3837 ?pl .
  OPTIONAL { ?item wdt:P585 ?date . }
  OPTIONAL { ?item wdt:P1813 ?short . }
  OPTIONAL { ?item p:P1891 ?stat2 .
            ?stat2 ps:P1891 ?signatory; .
            OPTIONAL {?stat2 pq:P585 ?date2 .}
           }
  OPTIONAL { 
}
  FILTER (CONTAINS(?pl, '116-')) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en"  }
}
Try it!
--Tagishsimon (talk) 02:17, 18 December 2020 (UTC)
Thank you, @Tagishsimon:. I have to say, that syntax and model seem particularly…arcane? I'm not sure how anyone could figure out that's how it worked from either looking at existing examples at wd:SPARQL_query_service/queries/examples or even from a casual glance at the SPARQL spec (maybe I need to be looking at the RDF spec? I dunno. I looked in a bunch of places). Of course the specification resources are pretty formal and set-in-stone, but it does seem like there should be some examples that make use of the
p -> OPTIONAL { pq }
pattern to help people out. Although I now see there are some examples that do use
p -> pq
they are not well-commented or documented as to what is going on there. (Or at least, they did not seem to be the first time I read them, trying to figure this out). But the tutorial you linked to is particularly helpful, yes! It's also striking to me that that's an off-site resource and there doesn't seem to be an analogous resource in the Wikidata documentation, although maybe there is and I just didn't find it. Also: I was confused about the ps: part, which I guess is not necessary here.
I suspect it's possible to avoid the use of ?stat2…ah yes, the "blank node" syntax. That is, we can turn
OPTIONAL { ?item p:P1891 ?stat2 . ?stat2 ps:P1891 ?signatory; OPTIONAL {?stat2 pq:P585 ?date2 .} }
into<
OPTIONAL { ?item p:P1891 [ pq:P585 ?date2 ] }
which seems to be a win for clarity. Anyhow, thank you again. I don't think I'm experienced enough to tackle trying to improve the examples and help here (at a minimum I would have to read more of them and think about them), but I did want to flag the issue with the wide-open eyes of a newcomer. Jhawkinson (talk) 13:56, 18 December 2020 (UTC)
@Jhawkinson: OPTIONAL { ?item p:P1891 [ pq:P585 ?date2 ] } is fine if you only want to know the date but not who the signatory is. But it is not good if you want to know both because if you extract the signatory in another independent part of the query, you will not be able to pair the signatories and dates to each other in case that there are more than one statement with signatory (P1891). Even if that is never the case, it would be an unlogical data model and also less efficient. --Dipsacus fullonum (talk) 15:59, 18 December 2020 (UTC)

What are the most frequently used external identifiers?

and how to find out? --Loominade (talk) 14:40, 21 December 2020 (UTC)

Top 5 is:
  1. PubMed publication ID (P698) (60 million)
  2. DOI (P356) (27 million)
  3. PMC publication ID (P932) (11 million)
  4. SIMBAD ID (P3083) (8 million)
  5. VIAF ID (P214) (6 million)
You find out by looking at Wikidata:Database reports/List of properties/all. There are too many external identifiers and statements to make the list with SPARQL query without timeout. --Dipsacus fullonum (talk) 01:58, 22 December 2020 (UTC)

including point in time (P585)

I'd like a list of winners of Wollaston Medal (Q832424) that includes and is sorted by point in time (P585). I've searched the examples for one that I could, but they are all too complex and fail when I edit them. Thanks for any help. Trilotat (talk) 17:51, 21 December 2020 (UTC)

@Trilotat: Hi, try this: 
SELECT ?recipient ?recipientLabel (YEAR(?time) AS ?year)
WHERE
{
  ?recipient p:P166 ?statement .
  ?statement ps:P166 wd:Q832424 . # Wollaston Medal
  OPTIONAL { ?statement pq:P585 ?time . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
ORDER BY ?time
Try it!
--Dipsacus fullonum (talk) 00:59, 22 December 2020 (UTC)

Add references to a query

Hello, given that existing query which functions perfectly, I'd like to improve it with the (max 1) reference reference URL (P854) / Wikimedia import URL (P4656) of their each annualised statement. Thanks ! Bouzinac 💬●✒️●💛 10:47, 21 December 2020 (UTC)

@Bouzinac: That is done here. I also reformatted the query to make it more readable (otherwise I was not able to edit it) and removed the defintion of the unused variable ?where: 
SELECT ?year ?item ?itemLabel (MAX(?number) AS ?passengers)
  (SAMPLE(COALESCE(?reference_URL, ?monthly_reference_URL)) AS ?sample_reference_URL)
WITH
{
  SELECT ?item ?statement ?year ?timevalue ?numberperperiod ?reference_URL
  WHERE
  {
    ?item wdt:P238 ?IATA.
    VALUES ?IATA 
    {
      "SOF" "VAR" "BOJ" "EPU"
    }
    ?item p:P3872 ?statement.
    ?statement pqv:P585 ?timevalue;
               ps:P3872 ?numberperperiod.
    ?timevalue wikibase:timeValue ?date.
    OPTIONAL { ?statement pq:P518 ?applies. }
    OPTIONAL { ?statement prov:wasDerivedFrom / (pr:P854|pr:P4656) ?reference_URL. }
    FILTER (BOUND(?applies)=false || ?applies = wd:Q2165236 )
    MINUS { ?statement wikibase:rank wikibase:DeprecatedRank }
    BIND (YEAR(?date) AS ?year)
    FILTER (?year > 2010)
    FILTER (?year < YEAR(NOW()))
  }
} AS %airport
WHERE
{
  {
    SELECT ?item ?year (SUM(?numberperperiod) AS ?number) (SAMPLE(?reference_URL) AS ?monthly_reference_URL)
    WHERE
    {
      INCLUDE %airport
      ?timevalue wikibase:timePrecision ?prec.
      FILTER (?prec > 9)
    }
    GROUP BY ?item ?year
  }
  UNION
  {
    ?timevalue wikibase:timePrecision 9 .
    BIND (?numberperperiod AS ?number)
    BIND (?reference_URL AS ?sample_reference_URL)
    INCLUDE %airport
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
}
GROUP BY ?item ?itemLabel ?year
ORDER BY ?item DESC (?year)
Try it!
--Dipsacus fullonum (talk) 12:51, 21 December 2020 (UTC)
Thank you, works perfectly like there. Thanks again ! Bouzinac 💬●✒️●💛 13:36, 21 December 2020 (UTC)
Hello again [[User:|]] ([[User talk:|talk]] • contribs • logs), there seems to be a pb if there is 2 references for same month (looks it sums double) eg : SVO
SELECT ?year ?item ?itemLabel (MAX(?number) AS ?passengers)
  (SAMPLE(COALESCE(?reference_URL, ?monthly_reference_URL)) AS ?sample_reference_URL)
WITH
{
  SELECT ?item ?statement ?year ?timevalue ?numberperperiod ?reference_URL
  WHERE
  {
    ?item wdt:P238 ?IATA
    VALUES ?IATA 
    {
 "SVO"
    }
    ?item p:P3872 ?statement.
    ?statement pqv:P585 ?timevalue;
               ps:P3872 ?numberperperiod.
    ?timevalue wikibase:timeValue ?date.
    OPTIONAL { ?statement pq:P518 ?applies. }
    OPTIONAL { ?statement prov:wasDerivedFrom / (pr:P854|pr:P4656) ?reference_URL. }
    FILTER (BOUND(?applies)=false || ?applies = wd:Q2165236 )
    MINUS { ?statement wikibase:rank wikibase:DeprecatedRank }
    BIND (YEAR(?date) AS ?year)
    FILTER (?year >2016).
    FILTER (?year < YEAR(NOW()))
  }
} AS %airport
WHERE
{
  {
    SELECT ?item ?year (SUM(?numberperperiod) AS ?number) (SAMPLE(?reference_URL) AS ?monthly_reference_URL)
    WHERE
    {
      INCLUDE %airport
      ?timevalue wikibase:timePrecision ?prec.
      FILTER (?prec > 9)
    }
    GROUP BY ?item ?year
  }
  UNION
  {
    ?timevalue wikibase:timePrecision 9 .
    BIND (?numberperperiod AS ?number)
    BIND (?reference_URL AS ?sample_reference_URL)
    INCLUDE %airport
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
}
GROUP BY ?item ?itemLabel ?year
ORDER BY ?item DESC (?year)
Try it!
Thank you Bouzinac 💬●✒️●💛 15:12, 21 December 2020 (UTC)
@Bouzinac: Yes, you are right. I think it is necessary with another inner subquery to select a single sample reference URL inside the subquery to add monthly values. So I hope this is better: 
SELECT ?year ?item ?itemLabel (MAX(?number) AS ?passengers)
  (SAMPLE(COALESCE(?reference_URL, ?monthly_reference_URL2)) AS ?sample_reference_URL)
WITH
{
  SELECT ?item ?statement ?year ?timevalue ?numberperperiod ?reference_URL
  WHERE
  {
    ?item wdt:P238 ?IATA.
    VALUES ?IATA 
    {
      "SOF" "VAR" "BOJ" "EPU"
    }
    ?item p:P3872 ?statement.
    ?statement pqv:P585 ?timevalue;
               ps:P3872 ?numberperperiod.
    ?timevalue wikibase:timeValue ?date.
    OPTIONAL { ?statement pq:P518 ?applies. }
    OPTIONAL { ?statement prov:wasDerivedFrom / (pr:P854|pr:P4656) ?reference_URL. }
    FILTER (BOUND(?applies)=false || ?applies = wd:Q2165236 )
    MINUS { ?statement wikibase:rank wikibase:DeprecatedRank }
    BIND (YEAR(?date) AS ?year)
    FILTER (?year > 2010)
    FILTER (?year < YEAR(NOW()))
  }
} AS %airport
WHERE
{
  {
    # Get the sum of monthly values within a year
    SELECT ?item ?year (SUM(?numberperperiod) AS ?number) (SAMPLE(?monthly_reference_URL) AS ?monthly_reference_URL2)
    WHERE
    {
      # Get a sample reference URL for each monthly value
      {
        SELECT ?item ?year ?numberperperiod (SAMPLE(?reference_URL) AS ?monthly_reference_URL)
        WHERE
        {
          INCLUDE %airport
          ?timevalue wikibase:timePrecision ?prec.
          FILTER (?prec > 9)
        }
        GROUP BY ?item ?statement ?year ?numberperperiod
        # Include ?statement in the GROUP BY because ?numberperperiod may not be unique
      }
    }
    GROUP BY ?item ?year
  }
  UNION
  {
    ?timevalue wikibase:timePrecision 9 .
    BIND (?numberperperiod AS ?number)
    BIND (?reference_URL AS ?sample_reference_URL)
    INCLUDE %airport
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
}
GROUP BY ?item ?itemLabel ?year
ORDER BY ?item DESC (?year)
Try it!
--Dipsacus fullonum (talk) 00:45, 22 December 2020 (UTC)
Wonderful! Thank you again dude Bouzinac 💬●✒️●💛 10:51, 22 December 2020 (UTC)

Steer on combining subquery results

The aim of this query is to find candidate town/parish items that match town council items based on a string match. First subquery finds the councils and extracts the town name from the label as ?town_name. Second subquery finds town and parish items, extracting their labels into ?town_name. The grand idea is that the query delivers rows linking council items to town/parish items based on a ?town_name match, but instead, horrow. I've tried a variety of other approaches, but timeout. Clearly I don't know how to combine two disparate subquery sets in a useful fashion. 

SELECT ?item ?itemLabel ?town ?town_name 
WITH { 
  SELECT ?item ?itemLabel ?town ?town_name WHERE {
    ?item wdt:P31 wd:Q3507268.
    ?item rdfs:label ?itemLabel . filter(lang(?itemLabel)="en")
    ?item wdt:P17 wd:Q145  .
    FILTER NOT EXISTS { ?item wdt:P1001 []. }
    BIND(REPLACE( ?itemLabel, " Town Council","") as ?town_name)
  } } AS %councils
WITH {
  SELECT DISTINCT ?town ?town_name WHERE {
    ?town wdt:P17 wd:Q145 .
    {?town wdt:P31 wd:Q1115575.}
    UNION
    {?town wdt:P31 wd:Q3957 .}
    ?town rdfs:label ?town_name . filter(lang(?town_name)="en")
  } } AS %towns
WHERE {
   {INCLUDE %councils} 
   UNION 
   {INCLUDE %towns}
}
Try it!
Hi. The idea is good and it almost works. When combining patterns or subqueries based on matching variables you don't use the UNION keyword, as that means that you want the results from each part without matching the variables (as you already do in the %towns subquery). Just remove the UNION keyword in the main query, and it will work. --Dipsacus fullonum (talk) 09:30, 22 December 2020 (UTC)
lol. There's no hope for me :) ... thank you, Dipsacus fullonum --Tagishsimon (talk) 09:50, 22 December 2020 (UTC)

version, edition, or translation

Hello!
I would like a list/query of all instance of (P31)=version, edition or translation (Q3331189) that have the statement: edition or translation of (P629)=X. And X has the statement instance of (P31)=Christian hymn (Q856713). --Geogast (talk) 14:09, 22 December 2020 (UTC)

@Geogast: These, concisely:
SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P31 wd:Q3331189;
        wdt:P629/wdt:P31 wd:Q856713 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
More sensibly:
SELECT ?item ?itemLabel ?x ?xLabel
WHERE 
{
  ?item wdt:P31 wd:Q3331189;
        wdt:P629 ?x . 
  ?x wdt:P31 wd:Q856713 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Tagishsimon (talk) 14:18, 22 December 2020 (UTC)
Now, that was quick! Exactly what I needed. Thanks a lot!--Geogast (talk) 14:26, 22 December 2020 (UTC)

List of countries

Sorry for posting such a basic question, but my attempt at getting all countries of the world miserably fails: 

SELECT ?item
WHERE 
{
  ?item wdt:P31 wd:Q6256. # item is a country
}
Try it!

The result strangely does not contain Albania (Q222), which is an instance of (P31) of country (Q6256). What am I doing wrong? Thanks! Syced (talk) 15:36, 22 December 2020 (UTC)

@Syced: wdt: will only find truthy values; for Albania, Sovereign State is marked as the preferred value, so it truthy. Country is marked as normal preference and so is not truthy. Use p:/ps: to see all values. Arcane documentation here. And although the tutorial doesn't explicitly deal with truthiness, this section - statements-with-qualifiers - describes p:/ps: business well enough.
SELECT ?item ?itemLabel
WHERE 
{
  ?item p:P31 [ps:P31 wd:Q6256] . # item is a country
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?itemLabel
Try it!
--Tagishsimon (talk) 15:44, 22 December 2020 (UTC)

Ah, I had not noticed that! Understood, thanks! :-) Syced (talk) 23:20, 22 December 2020 (UTC)

Timeout in a query about Wikidata pages

I am trying to write a query to get the list of all Wikipedias that have an article about a given entity, with the respective titles.

This seems to work: 

SELECT DISTINCT ?lang ?name WHERE {
  ?article schema:about wd:QXXXXXX;
              schema:inLanguage ?lang ;
              schema:name ?name ;
              schema:isPartOf [ wikibase:wikiGroup "wikipedia" ] .
  FILTER (!CONTAINS(?name, ':')) .
}
Try it!

The problem is that it times out for some entities, for example Q15 and Q16. For most entities, it runs in less than a second, even those with relatively long lists, for example Q2, and I don't know how I could improve it. Tcp-ip (talk) 21:17, 22 December 2020 (UTC)

@Tcp-ip: Near as I can work out, the optimiser is doing ill-advised stuff with the schema:isPartOf element. This solves it, at least for Q15. Come back if you find more timeouts. See also Wikidata:SPARQL query service/query optimization fwiw.
SELECT DISTINCT ?lang ?name WHERE {
  hint:Query hint:optimizer "None".
  ?article schema:about wd:Q15 .
  ?article schema:name ?name .
  ?article schema:inLanguage ?lang .
  ?article schema:isPartOf [ wikibase:wikiGroup "wikipedia" ] .
  FILTER (!CONTAINS(?name, ':')) .
}
Try it!
--Tagishsimon (talk) 21:43, 22 December 2020 (UTC)
@Tcp-ip: Yes, when _:blanknode wikibase:wikiGroup "wikipedia" has fewer results than ?article schema:about wd:QXXX it will try to resolve the former pattern first. You can force another order of execution in several ways. My solution which was made before I saw Tagishsimon's is: 
SELECT DISTINCT ?lang ?name
WITH
{
  SELECT ?article WHERE { ?article schema:about wd:Q15 . }
} AS %1
WHERE
{
  INCLUDE %1
  ?article schema:inLanguage ?lang ;
              schema:name ?name ;
              schema:isPartOf [ wikibase:wikiGroup "wikipedia" ] .
  FILTER (!CONTAINS(?name, ':')) .
}
Try it!
--Dipsacus fullonum (talk) 22:51, 22 December 2020 (UTC)
And for those following along at home, that's a named subquery approach, in which WDQS is compelled to execute the first (named AS %1) subquery first, before delivering its results (INCLUDE %1) to constrain the action of the second query. --Tagishsimon (talk) 23:00, 22 December 2020 (UTC)
Thanks to both, I have tried Tagishsimon's solution to download all the 1000 "List of articles every Wikipedia should have", and it works for all of them. My query timed out on about 20% of them. Tcp-ip (talk) 23:25, 22 December 2020 (UTC)

Data quality : those having official website (P856) but no country (P17)

Hello, this times out : any means to have it function better ? Thanks Bouzinac 💬●✒️●💛 08:40, 23 December 2020 (UTC) 

SELECT ?item ?itemLabel ?site WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item wdt:P856 ?site. 
  OPTIONAL { ?item wdt:P17 ?country. }
FILTER(!BOUND(?country))
MINUS { ?item (wdt:P31/wdt:P279*) wd:Q10876391}
} limit 20
Try it!
@Bouzinac: This sort of thing, probably. You can also use OFFSET 250000 LIMIT 100000 (or whatever) to get additional chunks.
SELECT ?item ?itemLabel ?site WITH 
{
  SELECT ?item ?site WHERE 
  {
    ?item wdt:P856 ?site. 
  } LIMIT 250000 } as %i
WITH 
{
  SELECT ?item ?site WHERE
  {
    INCLUDE %i
    OPTIONAL { ?item wdt:P17 ?country. }
    FILTER(!BOUND(?country))
    MINUS { ?item wdt:P31/wdt:P279* wd:Q10876391 .
            hint:Prior hint:gearing "forward". }
    } } as %j        
WHERE
{
  INCLUDE %j
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
Try it!
--Tagishsimon (talk) 09:01, 23 December 2020 (UTC)
Thank you Tagishsimon but when I'm trying with an additionnal MINUS, it times out... Bouzinac 💬●✒️●💛 09:09, 23 December 2020 (UTC)
SELECT ?item ?itemLabel ?site WITH 
{
  SELECT ?item ?site WHERE 
  {
    ?item wdt:P856 ?site. 

  } LIMIT 100000 } as %i
WHERE
{
  INCLUDE %i
  OPTIONAL { ?item wdt:P17 ?country. }
  FILTER(!BOUND(?country))
    MINUS { ?item (wdt:P31/wdt:P279*) wd:Q5}
    MINUS { ?item (wdt:P31/wdt:P279*) wd:Q10876391}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
Try it!


@Bouzinac: Hints :)
SELECT ?item ?itemLabel ?site WITH 
{
  SELECT ?item ?site WHERE 
  {
    ?item wdt:P856 ?site. 
  } LIMIT 100000 } as %i
WITH 
{
  SELECT ?item ?site WHERE
  {
    INCLUDE %i
    OPTIONAL { ?item wdt:P17 ?country. }
    FILTER(!BOUND(?country))
    MINUS { ?item wdt:P31/wdt:P279* wd:Q5 . 
            hint:Prior hint:gearing "forward" . }
    MINUS { ?item wdt:P31/wdt:P279* wd:Q10876391 .
            hint:Prior hint:gearing "forward" . }
    } } as %j        
WHERE
{
  INCLUDE %j
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
Try it!
  • You might want to check for absence of "country of origin" or the presence of coordinates as well. --- Jura 10:30, 23 December 2020 (UTC)
    Yes, I'm working this [way https://w.wiki/ros] . Is it possible to group all "countries" properties to help build that query and avoid that fuzzy code OPTIONAL { ?item wdt:P17 ?country. }
   OPTIONAL { ?item wdt:P8047 ?countryPav. }
   OPTIONAL { ?item wdt:P495 ?countryPav2. }
  FILTER(!BOUND(?countryPav))
  FILTER(!BOUND(?country))
  FILTER(!BOUND(?countryPav2))
@Bouzinac: You can use pipes: OPTIONAL { ?item wdt:P17|wdt:P495|wdt:P8047 ?country. } --Tagishsimon (talk) 12:48, 23 December 2020 (UTC)

This simple query does not seem to work

EDIT: I solved the problem by actually copying on the Albania problem asked by Syced a bit up, but this is a bit confusing for me...So 'wdt' might not turn back all results, but 'p/ps' does. I am not sure now that all my previous queries worked normally, is it better to use 'p/ps' all the time? Is there a performance issue (in particular with the label service)?

Here is the original post:

Hi, I am new in the world of Queries, but I still found some strangeness that might be an anomaly, so I report it here (I'm not sure where else I should).

If this is my wrongdoing, then please see it as a 'query request' and help me build a successful query!

Here it is: 

select ?notableWork  where { ?notableWork  wdt:P138 wd:Q81082. }

This query should return all notable works from Henri Poincaré, and only returns one match.

As you can see on his page, he actually has plenty of notable works:

https://www.wikidata.org/wiki/Q81082

So why only one result? The query actually works if I ask for another person, such as d'Alembert:

https://www.wikidata.org/wiki/Q153232

The query

select ?notableWork where {wd:Q153232 wdt:P800 ?notableWork. }

returns all three results...

Thanks

The issue is statement rank. wdt: queries fetch only truthy property statements. Need to use p:/ps: to get property statement values for all property statements irrespective of rank. See https://www.mediawiki.org/wiki/Wikibase/Indexing/RDF_Dump_Format#Statement_types ... if you look at Q81082#P800 you'll see that the entry "Science and Hypothesis" has an up-arrow to the left of the property value. This specifies 'Preferred rank' and so causes that value to be the only one returned when one asks for wdt:P138. See also the data model which is a roadmap of the properties paths to be used to get various sorts of values, and the tutorial which deals with qualifiers - getting ps: values is much like getting qualifier values and uses the technique explained and illustrated there.
The query you supplied was looking at items which have a named after (P138) property statement pointing at Henri Poincaré (Q81082). The two queries below look at the notable work (P800) of Henri Poincaré (Q81082). The first gets truthy values only. The second gets all values.
SELECT ?notableWork ?notableWorkLabel 
WHERE 
{
  VALUES ?item {wd:Q81082}
  ?item wdt:P800 ?notableWork . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! 
SELECT ?notableWork ?notableWorkLabel 
WHERE 
{
  VALUES ?item {wd:Q81082}
  ?item p:P800 [ps:P800 ?notableWork] . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
hth --Tagishsimon (talk) 18:02, 23 December 2020 (UTC)

Image captured with ...

Hello, here is a querry to show all the images depicting Coleoptera and with the Featured Picture assessment : 

#shows files that depict Coleoptera and with the Featured Picture assessment 
#defaultView:ImageGrid
SELECT ?file ?image
WITH
{
  SELECT ?item 
  WHERE
  {
    SERVICE <https://query.wikidata.org/sparql>
    {
        ?item wdt:P171/wdt:P171* wd:Q22671.
     } 
  }
} AS %get_items
WHERE
{
  INCLUDE %get_items
  ?file wdt:P180 ?item .
  ?file schema:contentUrl ?url .
  ?file wdt:P6731 wd:Q63348049 .
  BIND(IRI(CONCAT("http://commons.wikimedia.org/wiki/Special:FilePath/", wikibase:decodeUri(SUBSTR(STR(?url),53)))) AS ?image)
}
Try it!
I would like the same thing but showing only the images that have been taken with a specific camera brand(eg. Canon (Q63554165)), the property used in SDC is captured with (P4082) however this property store values of specific cameras and not camera brands, the property in Wikidata is brand (P1716). Christian Ferrer (talk) 13:39, 24 December 2020 (UTC)
@Christian Ferrer: This, probably. (Top marks, btw, for specifying the properties & the value involved. Makes answering v.much easier; thank you.)
#shows files that depict Coleoptera and with the Featured Picture assessment 
#defaultView:ImageGrid
SELECT ?file ?image
WITH
{
  SELECT ?item 
  WHERE
  {
    SERVICE <https://query.wikidata.org/sparql>
    {
        ?item wdt:P171/wdt:P171* wd:Q22671.
     } 
  }
} AS %get_items
WHERE
{
  INCLUDE %get_items
  ?file wdt:P180 ?item .
  ?file schema:contentUrl ?url .
  ?file wdt:P6731 wd:Q63348049 .
  ?file wdt:P4082 ?camera.
  SERVICE <https://query.wikidata.org/sparql>
    {
          ?camera wdt:P1716 wd:Q63554165 .
     } 
  BIND(IRI(CONCAT("http://commons.wikimedia.org/wiki/Special:FilePath/", wikibase:decodeUri(SUBSTR(STR(?url),53)))) AS ?image)
}
Try it!
--Tagishsimon (talk) 14:24, 24 December 2020 (UTC)

List of lonely items of tlwiki

From my experience, the Tagalog Wikipedia has very many lonely items that users forgot to link to Wikidata. I have found and merged probably a few hundred of them by looking for articles with missing categories (which are usually forgotten as well when linking to Wikidata is forgotten), but I am sure there is a lot more to be found. Is it possible to generate a list of all lonely articles of tlwiki ? Thanks in advance. --Glennznl (talk) 18:21, 24 December 2020 (UTC)

@Glennznl: Duplicity is your friend for this - https://wikidata-todo.toolforge.org/duplicity.php?wiki=tlwiki&mode=list - (updated daily). Beyond that, Petscan can be used - here's an example - https://petscan.wmflabs.org/?psid=18076183 - but it does require you to seed it with a category. Requires the obvious settings in the Categories tab, and "Only pages without item (enables Wikidata item creation mode)" checked in the wikidata tab. Advantage of Petscan is that it'll show name matches with items, and provides facilities for adding sitelinks or new items; you need to be logged in to Petscan for some of that. hth --Tagishsimon (talk) 18:58, 24 December 2020 (UTC)
@Glennznl: If you mean pages not connected to Wikidata you can also use the special page tl:Natatangi:UnconnectedPages on tlwiki (besides the good suggestions by Tagishsimon). If you mean pages connected to Wikidata items without links to any other wikis, you can use this query: 
SELECT ?page ?item ?title
WHERE
{
  ?page schema:about ?item .
  ?page schema:isPartOf <https://tl.wikipedia.org/> .
  ?page schema:name ?title .
  ?item wikibase:sitelinks 1 .
}
Try it!
--Dipsacus fullonum (talk) 01:24, 25 December 2020 (UTC)

Cannot reproduce results of a query in a 2018 video

I'm a beginner and I wanted to test some of the queries shown in this video from 2018 : https://www.youtube.com/watch?v=kJph4q0Im98&t=5465s However, after typing the exact same query into query.wikidata.com However the results are not what I expected, I noticed that the number of results was too large and indeed it returned non american politician. After that I added the line `?father wdt:P27 wd:Q30 .` to signify that the father should also be american and it worked like in the video. But the video doen't feature this particular line, so I ask the question : Is it normal? or do I'm missing something? I expect that when I use the line `?child wdt:P27 wd:Q30 .` it mean that I only want American Politician, it is a AND statement so it should not return non american politician. Why?? Something doesn't add up. 

# Get all the americans politicians born after 1950 which have as father a politician
# From https://youtu.be/kJph4q0Im98?t=4961
SELECT ?child ?childLabel ?fatherLabel ?dob
WHERE {
  ?child wdt:P31 wd:Q5 . # item must be instance of human
  ?child wdt:P27 wd:Q30 . # also citizenship USA
  ?child wdt:P106 wd:Q82955 . # has occupation politician
  ?child wdt:P22 ?father . # and has father
  ?father wdt:P106 wd:Q82955 . # which is a politician
  
  ?child wdt:P569 ?dob .
  FILTER (YEAR(?dob) > 1950)
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO LANGUAGE],en" . }
}
Hi Matthieu2743. The triple ?child wdt:P27 wd:Q30 will ensure that the variable ?child will only be bound to entities with the statement country of citizenship (P27) United States of America (Q30) with best rank. The entities will have that statement, but they may also have other statements with other values for people with than one citizenship. Could you please be more precise about what unexpected results you see? It is hard to explain when I don't know what you see as a problem. --Dipsacus fullonum (talk) 01:05, 25 December 2020 (UTC)

Get all museums within 5km from a given latitude and longitude

I need to get all museum within 5km from my restaurant json file. each restaurant has a latitude and a longitude. How do I build that kind of query? I aldready saw the barcelona example but it was of no use for my case, instead of the city name i need to cast each restaurant so that my query can adapt to the situation.

thank you so much, you saved my life!!

Class and subclasses tree

I'd like to have a query to show the hierarchical tree of the subclasses of a specific class. The objective is to have something like the "Wikidata generic tree" that is not showing the labels anymore (I hope it will be fixed). I think the #defaultView:Tree would give a useful data visualization. Thanks in advance! --FabC (talk) 17:40, 27 December 2020 (UTC)

@FabC: Do you mean like this? 
#defaultView:Tree
SELECT
  ?class1 ?class1Label
  ?class2 ?class2Label
  ?class3 ?class3Label
  ?class4 ?class4Label
  ?class5 ?class5Label
WHERE
{
  VALUES ?class1 { wd:Q6256 }
  ?class1 ^wdt:P279 ?class2 .
  OPTIONAL { ?class2 ^wdt:P279 ?class3 .
    OPTIONAL { ?class3 ^wdt:P279 ?class4 .
      OPTIONAL { ?class4 ^wdt:P279 ?class5 . }
    }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO LANGUAGE],en" . }
}
Try it!
--Dipsacus fullonum (talk) 18:19, 27 December 2020 (UTC)
@Dipsacus fullonum: Exactly! Thank you for very fast reply --FabC (talk) 18:40, 27 December 2020 (UTC)

Pages in a category, in another WP language project

I couldn't understand how to adapt this query from the examples. What I seek: which pages in en:Category:Al Jazeera people exist in the Hebrew WP. -- Deborahjay (talk) 11:54, 28 December 2020 (UTC)

@Deborahjay: This, probably:
SELECT distinct ?item ?itemLabel ?article ?sitelink where
{
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
     bd:serviceParam wikibase:endpoint "en.wikipedia.org";
                     wikibase:api "Generator";
                     mwapi:generator "categorymembers";
                     mwapi:gcmtitle "Category:Al Jazeera people " ;         
                     mwapi:gcmprop "ids|title|type";
                     mwapi:gcmlimit "max".
     # out
     ?item wikibase:apiOutputItem mwapi:item.            # wikidata QId for the person's item
    }
 ?article schema:about ?item ;
          schema:name ?sitelink ;
          schema:isPartOf <https://he.wikipedia.org/> .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],he". }
}
Try it!
--Tagishsimon (talk) 12:13, 28 December 2020 (UTC)
@Tagishsimon:, huge thanks to you for this! Got six results, which is enough to create the matching category in the HE WP. However, can you explain why one item from that category (that I know of: Dima Khatib) wasn't picked up on this query? -- Deborahjay (talk) 12:23, 28 December 2020 (UTC)
@Deborahjay: I get 7 results, one of which is Dima. If you're getting 6, then whichever report-server you're hitting has not been updated with the 08:17, 28 December 2020 edit which told Wikidata of the he. article. It's one of the minor annoyances of wikidata that report servers are rarely in synch, even if the problem is only marginal. I've edited her item in the expectation that that might cause servers to get updated. --Tagishsimon (talk) 12:41, 28 December 2020 (UTC)

Politician's careers

Is it possible to report on the set of periods, and the start & end dates of the periods, in which a politician held a specific UK Parliament seat (pq:P768) for a specific party (pq:P4100) for a set of contiguous parliamentry terms?

A set of contiguous parliamentry terms is defined as terms that are linked back-to-back by the dissolution of one parliament (pq:P1534 wd:Q741182) and the election at a general election of the next parliament (pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424).

This is a query from User:Andrew Gray which bubbled up on Twitter earlier today, in this example concentating specifically on Herbert Morrison Q335791#P39 and his "Member of the nth Parliament of the United Kingdom" statements (ps:P39 ?term . ?term wdt:P279 wd:Q16707842).

Andrew's best shot in SPARQL, below, is tantalisingly close, but no cigar. I regret to inform that he has reached for python. Can he be helped in SPARQL? (So for Morrison, the report should have 5 rows each having a start & end date, not 10 rows each with either a start or an end date.) 

SELECT distinct ?mp ?mpLabel ?partyLabel ?seatLabel ?start ?end WHERE {
  VALUES ?mp { wd:Q335791 } # Herbert Morrison
                            # find all details for this MP's terms
  ?mp p:P39 ?ps. ?ps ps:P39 ?term . ?term wdt:P279 wd:Q16707842. 
  ?ps pq:P768 ?seat . ?ps pq:P4100 ?party .  
                            # focus on term start date
                            # skip any where this term began at general and last one ended at dissol
  { ?ps pq:P580 ?start.
  filter not exists { 
                            # this term started with a general election
    ?ps pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . 
                            # but the MP is involved in an immediately previous term
    ?mp p:P39 ?ps0 . ?ps0 ps:P39 ?term0 . ?term0 wdt:P156 ?term .
                            # for same seat & party, which ended in a dissolution
    ?ps0 pq:P4100 ?party . ?ps0 pq:P768 ?seat . ?ps0 pq:P1534 wd:Q741182 . } }
  union
                            # focus on term end date
                            # skip any where this term ends at dissol and next begins at general
  { ?ps pq:P582 ?end.
  filter not exists { 
    ?ps pq:P1534 wd:Q741182 .
    ?mp p:P39 ?ps2 . ?ps2 ps:P39 ?term2 . ?term2 wdt:P155 ?term . 
    ?ps2 pq:P4100 ?party . ?ps2 pq:P768 ?seat . ?ps2 pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . } }

  BIND(COALESCE(?start, ?end) AS ?order)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY (?mp) (?order) ?start
Try it!

--Tagishsimon (talk) 01:40, 29 December 2020 (UTC)

  • Very grateful for any suggestions - I banged my head against the walll for hours before giving up and writing a script to reassemble the lines (which merely changed the wall I was hitting my head against... :-).)
The target here is one line per "period of continuing service in the same seat and party", so as a couple of other test cases Herbert Morrison (Q335791) should have five entries (he had four periods of continuing service, but the last one was over two seats). Winston Churchill (Q8016) should have six (two of which are the same seat different parties), and Paul Marsden (Q3330215) should have three (all the same seat, but switched into and out of a party). Someone straightforward like Tony Blair (Q9545) should have just one (never changed seat or party). Andrew Gray (talk) 11:08, 29 December 2020 (UTC)
@Tagishsimon: I *think* I've got it! This query runs the same item as two subqueries, one finding all the seat/party/start date pairs, and one finding all the seat/party/end date pairs. Each one throws out any with blank fields so each one is returning only "valid" sets. We know that the corresponding end date is logically the one immediately following the start date - because terms shouldn't overlap.
So we can now deduce what the end date should be for each start date line. The one clause in the outer query says "for a given start date, the end date has to be larger" (obviously), throwing out any that are earlier. The main SELECT clause then has a MIN(?end) element, meaning that it will select the lowest date left to it - which is the next one in sequence. It seems to give the right results for Morrison (below). but also for Churchill and Marsden Andrew Gray (talk) 12:15, 29 December 2020 (UTC)
SELECT distinct ?mp ?mpLabel ?partyLabel ?seatLabel ?start (min(?end) as ?end2) where {
# find all seat-party-start pairs
{ SELECT distinct ?mp ?mpLabel ?partyLabel ?seatLabel ?start
WHERE {
  VALUES ?mp { wd:Q335791 }
  ?mp p:P39 ?ps. ?ps ps:P39 ?term . ?term wdt:P279 wd:Q16707842. ?ps pq:P768 ?seat . ?ps pq:P4100 ?party . 
  { ?ps pq:P580 ?start. filter not exists { ?ps pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . ?mp p:P39 ?ps0 . ?ps0 ps:P39 ?term0 . ?term0 wdt:P156 ?term .
    ?ps0 pq:P4100 ?party . ?ps0 pq:P768 ?seat . ?ps0 pq:P1534 wd:Q741182 . } }
  union
  { ?ps pq:P582 ?end. filter not exists { ?ps pq:P1534 wd:Q741182 . ?mp p:P39 ?ps2 . ?ps2 ps:P39 ?term2 . ?term2 wdt:P155 ?term . 
    ?ps2 pq:P4100 ?party . ?ps2 pq:P768 ?seat . ?ps2 pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . } }
   filter(bound(?start))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}ORDER BY (?mp) ?start }
# and all possible end dates
{ SELECT distinct ?mp ?mpLabel ?partyLabel ?seatLabel ?end
WHERE {
  VALUES ?mp { wd:Q335791 }
  ?mp p:P39 ?ps. ?ps ps:P39 ?term . ?term wdt:P279 wd:Q16707842. ?ps pq:P768 ?seat . ?ps pq:P4100 ?party . 
  { ?ps pq:P580 ?start. filter not exists { ?ps pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . ?mp p:P39 ?ps0 . ?ps0 ps:P39 ?term0 . ?term0 wdt:P156 ?term .
    ?ps0 pq:P4100 ?party . ?ps0 pq:P768 ?seat . ?ps0 pq:P1534 wd:Q741182 . } }
  union
  { ?ps pq:P582 ?end. filter not exists { ?ps pq:P1534 wd:Q741182 . ?mp p:P39 ?ps2 . ?ps2 ps:P39 ?term2 . ?term2 wdt:P155 ?term . 
    ?ps2 pq:P4100 ?party . ?ps2 pq:P768 ?seat . ?ps2 pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . } }
 filter(bound(?end))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}ORDER BY (?mp)?end }
  # now take our starts as the key, and match each to its appropriate end - the next one along
  # this is the *smallest* end date which is still *larger* than the start date
  # so filter by larger here, and smallest using min in the SELECT clause
  filter(?end > ?start) . # note > not >= 
} group by ?mp ?mpLabel ?partyLabel ?seatLabel ?start order by ?start
Try it!
It turns out we can simplify this further - the subqueries currently find all start and end dates, and then throw out the ones which are blank. But we can just ask it to find the start/end dates on their own, and it works smoothly. With a couple of other tweaks, here's the final(?) version, again for Morrison:
SELECT distinct ?mp ?mpLabel ?partyLabel ?seatLabel ?start (min(?end) as ?end2) where {
  VALUES ?mp { wd:Q335791 } # set MP here
  # find all seat-party-start pairs for each continuing period of office
  { SELECT distinct ?mp ?mpLabel ?partyLabel ?seatLabel ?start
    WHERE {
      ?mp p:P39 ?ps. ?ps ps:P39 ?term . ?term wdt:P279 wd:Q16707842. 
      ?ps pq:P768 ?seat . ?ps pq:P4100 ?party . ?ps pq:P580 ?start. 
      # find all terms of office with seat and party, and their start date
      filter not exists { ?ps pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . 
                          # omit any terms which started at a general election
                          ?mp p:P39 ?ps0 . ?ps0 ps:P39 ?term0 . ?term0 wdt:P156 ?term .
                          ?ps0 pq:P4100 ?party . ?ps0 pq:P768 ?seat . ?ps0 pq:P1534 wd:Q741182 . }
                          # and where the MP served for the same party & seat at dissolution in the previous term
      filter not exists { ?ps pq:P1534 wd:Q50393121 } # omit any double-return seats which were not taken up
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
      # labels inside the queries to catch any oddities where seat item changes, but name does not  
  } ORDER BY (?mp) ?start }
  # and all corresponding seat-party-end pairs
  { SELECT distinct ?mp ?mpLabel ?partyLabel ?seatLabel ?end
    WHERE {
      ?mp p:P39 ?ps. ?ps ps:P39 ?term . ?term wdt:P279 wd:Q16707842.
      ?ps pq:P768 ?seat . ?ps pq:P4100 ?party . ?ps pq:P582 ?end.
      # find all terms of office with seat and party, and their end date
      filter not exists { ?ps pq:P1534 wd:Q741182 . 
                          # omit any terms which end at dissolution
                          ?mp p:P39 ?ps2 . ?ps2 ps:P39 ?term2 . ?term2 wdt:P155 ?term . 
                          ?ps2 pq:P4100 ?party . ?ps2 pq:P768 ?seat . 
                          ?ps2 pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . }
                          # and where the MP comes back for the same party & seat at the next general election
      filter not exists { ?ps pq:P1534 wd:Q50393121 } # omit any double-return seats which were not taken up
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
      # labels inside the queries to catch any oddities where seat item changes, but name does not  
  } ORDER BY (?mp) ?end }
  # now take our starts as the key, and match each to its appropriate end - the next one along
  # this is the *smallest* end date which is still *larger* than the start date
  # so filter by larger here, and smallest using min in the SELECT clause
  filter(?end > ?start) . # note > not >= 
} group by ?mp ?mpLabel ?partyLabel ?seatLabel ?start order by ?start
Try it!
I think this is one of those odd cases where it's not quite obvious why the end version should work reliably, but it does. Andrew Gray (talk) 13:13, 29 December 2020 (UTC)

Search by wikibase:geoLatitude and wikibase:geoLongitude

Hello,

I'm not sure why this query finds no match when I uncomment the first VALUES statement: 

SELECT ?lake ?lakeLabel ?loc ?lat ?lon ?lonType WHERE {
#   VALUES (?lat ?lon) {
#     ("44.007874"^^xsd:decimal "-86.756451"^^xsd:decimal)
#   }
  VALUES ?lake {
    wd:Q1169
  }
  ?lake (wdt:P31/(wdt:P279*)) wd:Q23397;
    wdt:P17 wd:Q30;
    wdt:P625 ?loc;
    p:P625 ?ps.
  ?ps psv:P625 ?node.
  ?node wikibase:geoLatitude ?lat;
    wikibase:geoLongitude ?lon.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

I tried without "casting" to xsd:decimal and still no luck. I'm assuming it has something to do with wikibase:geoPrecision, but I'm not sure how to construct the query. Any help you could offer would be appreciated.

Regards, Gettinwikiwidit (talk) 09:01, 29 December 2020 (UTC)

Hi, Gettinwikiwidit, won't this query help out? Bouzinac 💬●✒️●💛 09:16, 29 December 2020 (UTC)
Bouzinac Thanks for the pointer. I think Tagishsimon pointed to the reference I was looking for. Regards, Gettinwikiwidit (talk) 13:02, 29 December 2020 (UTC)
Per RDF_Dump_Format#Globe_coordinate, double not decimal.
SELECT ?lake ?lakeLabel ?loc ?lat ?lon ?lonType WHERE {
  VALUES (?lat ?lon) { ("44.007874"^^xsd:double "-86.756451"^^xsd:double) }
  VALUES ?lake {wd:Q1169 }
  ?lake (wdt:P31/(wdt:P279*)) wd:Q23397;
    wdt:P17 wd:Q30;
    wdt:P625 ?loc;
    p:P625 ?ps.
  ?ps psv:P625 ?node.
  ?node wikibase:geoLatitude ?lat;
    wikibase:geoLongitude ?lon.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Tagishsimon (talk) 09:32, 29 December 2020 (UTC)

Commons categories not using the infobox (SQL)

Hi all. I have an SQL question rather than SPARQL, I hope that's OK. For the deployment of commons:Template:Wikidata Infobox on Commons, I've been using this SQL query (run directly on toolforge rather than through quarry). The problem is that page_props is not always updated (see phab:T233520). Is there another way to do this query without depending on page_props, please? Thanks. Mike Peel (talk) 14:44, 29 December 2020 (UTC)

Timing out query

Hello ; could you help me build out that query ? It should be around 6k items but refuses to work. Thanks ! Bouzinac 💬●✒️●💛 11:51, 29 December 2020 (UTC) 

SELECT DISTINCT ?airport ?airportLabel ?country ?countryLabel WHERE {
  ?airport (wdt:P31/wdt:P279*) wd:Q62447.#any aerodromes
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,fr,de,kk". }
  optional {?airport wdt:P239|wdt:P238|wdt:P240|wdt:P5699|wdt:P5746|wdt:P5851|wdt:P6120 ?code.}#any airport code
  filter(!Bound(?code)).#who does not have any airport code

  FILTER NOT EXISTS { ?airport p:P239 [rdf:type wdno:P239].} #we do not want the ?item if ICAO has a <no value>   
  MINUS {?airport wdt:P576 [].} # remove items with P576 (dissolved, abolished or demolished) as a main property
  MINUS {?airport wdt:P582 [].} # remove items with P582 (end date) as a main property
    MINUS {?airport a wdno:P17.}                                    #exclude airports that aren't physically based in a country

  MINUS {?airport (wdt:P31/wdt:P279*) (wd:Q695850 wd:Q2265915 wd:Q1311670 wd:Q7373622 wd:Q782667 wd:Q502074 wd:Q44665966 wd:Q2071567).} 
  #except those special instances of unnotheworthy aerodromes
  OPTIONAL {?airport wdt:P17 ?country. }
} order by ?countryLabel
Try it!
@Bouzinac: This, for instance.
SELECT DISTINCT ?airport ?airportLabel ?country ?countryLabel WITH {
  SELECT ?airport WHERE {
  ?airport (wdt:P31/wdt:P279*) wd:Q62447. hint:Prior hint:gearing "forward". #any aerodromes
  optional {?airport wdt:P239|wdt:P238|wdt:P240|wdt:P5699|wdt:P5746|wdt:P5851|wdt:P6120 ?code.}#any airport code
  filter(!Bound(?code)).#who does not have any airport code
} } as %i
WHERE
{
  INCLUDE %i
  FILTER NOT EXISTS { ?airport p:P239 [rdf:type wdno:P239].} #we do not want the ?item if ICAO has a <no value>   
  MINUS {?airport wdt:P576 [].} # remove items with P576 (dissolved, abolished or demolished) as a main property
  MINUS {?airport wdt:P582 [].} # remove items with P582 (end date) as a main property
    MINUS {?airport a wdno:P17.}                                    #exclude airports that aren't physically based in a country

  MINUS {?airport (wdt:P31/wdt:P279*) ?thing . 
         VALUES ?thing {wd:Q695850 wd:Q2265915 wd:Q1311670 wd:Q7373622 wd:Q782667 wd:Q502074 wd:Q44665966 wd:Q2071567} }
  #except those special instances of unnotheworthy aerodromes
  OPTIONAL {?airport wdt:P17 ?country. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,fr,de,kk". }
  } order by ?countryLabel
Try it!
--Tagishsimon (talk) 15:28, 29 December 2020 (UTC)

Display geo:wktLiterals

Hello, I want to do something like the following to be able to visually compare points in Wikidata with points pulled from other sources: 

SELECT ?place ?placeLabel ?location ?layer WHERE {
  VALUES (?ps ?place ?gnisPt ?gnisid ?distance) {
    (wds:q7335953-42836CD9-7979-4F71-87D7-91F1519254E6 wd:Q7335953 "Point(-84.7642201,45.1918512)"^^geo:wktLiteral "1614835" "0.6701477714685942"^^xsd:double)
    (wds:q6475804-48CD1BCD-4573-4385-95CD-8C3BDB15FEA1 wd:Q6475804 "Point(-113.9135721,48.7499294)"^^geo:wktLiteral "771253" "0.07547227642263711"^^xsd:double)
    (wds:q6477077-08DF184C-258E-4FD8-84BB-F811EC7427A2 wd:Q6477077 "Point(-76.4369827,43.3081268)"^^geo:wktLiteral "958316" "0.038288271056463825"^^xsd:double)
    (wds:q6475271-A124F3C5-56DE-4F4A-A8FE-56FDC80B5612 wd:Q6475271 "Point(-93.2347173,30.2282451)"^^geo:wktLiteral "553954" "0.11095681412047988"^^xsd:double)
    (wds:q6477136-35814F23-B203-439A-A0B7-6F38CD948C79 wd:Q6477136 "Point(-87.9080654,45.2566848)"^^geo:wktLiteral "1570331" "0.1083946745211315"^^xsd:double)
    (wds:q6477143-763568E7-6158-4A91-BCB3-A7FAB06F660E wd:Q6477143 "Point(-81.5410478,28.939347)"^^geo:wktLiteral "306117" "0.16027131623330992"^^xsd:double)
    (wds:q6693772-8075AC7F-DA34-4570-8D35-634FE0D19BFA wd:Q6693772 "Point(-74.1931089,44.3028384)"^^geo:wktLiteral "956107" "0.41559208749788973"^^xsd:double)
    (wds:q6476251-89FAD984-5259-452C-A87A-AEE4B803AE36 wd:Q6476251 "Point(-84.2390238,30.6312498)"^^geo:wktLiteral "284452" "0.1788844992337819"^^xsd:double)
    (wds:q5665721-81992C6B-A823-430F-9504-091EBFE78FFF wd:Q5665721 "Point(-113.7705404,48.5167701)"^^geo:wktLiteral "784584" "0.1789714719728492"^^xsd:double)
  }
  {
    ?place wdt:P625 ?location.
    BIND("wiki" AS ?layer)
  }
  UNION
  {
    BIND(?gnisPt AS ?location)
    BIND("gnis" AS ?layer)
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
Try it!

Trying to rebind ?gnisPt with a new name isn't working and neither is plotting the literals. Is there a way to fix this query? Regards, Gettinwikiwidit (talk) 10:37, 31 December 2020 (UTC)

This isn't terrible: 
#defaultView:Map
#defaultView:Map
SELECT ?place ?placeLabel ?location ?layer WITH {
  SELECT ?place ?gnisPt WHERE {
    VALUES (?ps ?place ?gnisPt ?gnisid ?distance) {
      (wds:Q30623689-0D440056-3566-461C-A245-3CBE33655C3B wd:Q30623689 "Point(-74.1964155,43.675418)"^^geo:wktLiteral "961941" "22.00675386330437"^^xsd:double )
      (wds:Q30623572-41B8044F-9374-47A5-A941-B6528E473DE6 wd:Q30623572 "Point(-74.7266356,43.7651884)"^^geo:wktLiteral "948948" "24.97341688722456"^^xsd:double )
      (wds:Q30623652-063205BD-E9FA-4604-87AD-A3293D10F81D wd:Q30623652 "Point(-74.1709696,43.7919114)"^^geo:wktLiteral "943832" "25.55307493643543"^^xsd:double )
      (wds:Q30623645-05564F3F-B9C8-4E71-95CA-73C449BE6A20 wd:Q30623645 "Point(-74.4079355,43.8417304)"^^geo:wktLiteral "948828" "27.275239034760396"^^xsd:double )
      (wds:Q30623567-AA1ABB5D-21D3-41C1-A45F-2DD590D241D6 wd:Q30623567 "Point(-74.7682523,43.8765356)"^^geo:wktLiteral "956109" "28.517545871307057"^^xsd:double )
      (wds:Q30624120-5E77C22A-B94A-491F-AF79-9B4867F62AC9 wd:Q30624120 "Point(-73.857884,42.9388878)"^^geo:wktLiteral "942933" "29.921966350265333"^^xsd:double )
      (wds:q14693123-9AF8C312-7B85-43B5-9CD5-5DC4765A0BC6 wd:Q14693123 "Point(-93.8131042,46.2055552)"^^geo:wktLiteral "654101" "37.03149311144129"^^xsd:double )
      (wds:Q18351593-4C7C6091-75F5-424F-AA3F-839CACF3F182 wd:Q18351593 "Point(-95.2902336,45.6867788)"^^geo:wktLiteral "639323" "43.36540396906205"^^xsd:double )
      (wds:Q22975579-9BEF5BBB-37CF-4BC7-9F95-97480A4C58CB wd:Q22975579 "Point(-91.3490062,46.3422776)"^^geo:wktLiteral "1580140" "184.69535342185696"^^xsd:double )
      (wds:Q33289642-F7C7A93E-8A4B-41E5-A8F1-A71E6F8A737D wd:Q33289642 "Point(-74.1279491,43.8454085)"^^geo:wktLiteral "957417" "237.53915222908313"^^xsd:double )
    }
  }
} AS %vals
WHERE {
  {
    INCLUDE %vals
    ?place wdt:P625 ?location.
    BIND( 'wiki' as ?layer )
  } UNION {
    INCLUDE %vals
    BIND( ?gnisPt AS ?location )
    BIND( 'gnis' as ?layer )
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

Large query request

Hi

I have a project based on WikiData, where I represent mathematicians of a given country in an interactive graph (https://grafviz.github.io/graph-v3.html?load_from_WD=true&wdKey=frMaths). I also intend to generalise it to other type of hierarchical data, I have for instance also treated mammals (https://grafviz.github.io/graph-v3.html?wdjs=true&wdKey=mammals).

I would like to do it for mathematicians of all over the globe (not just French), but the query times out because I am requesting many features and labels as wells. Is there the possibility to make a single large query once?

Thanks :@Tagishsimon: ! Well the project has no point if I can't get all of them, but I could only sample one of each optional properties for instance. What would be your advice to arrive at something feasible? The paradox is also that I will consume more query-time of Wikidata by trying those out than doing my single query.

Alternatively you can help me optimise it? I am a beginner wt writing queries (either with SPARQL or other): 

SELECT ?id ?idLabel ?country ?countryLabel ?parentId ?optionAward ?optionAwardLabel ?mgid ?optionNotable_work ?optionNotable_workLabel ?optionWikipedia_article ?occupation ?influencer (SAMPLE(?student) AS ?student) (SAMPLE(?img) AS ?img) WHERE {
  {
    ?id wdt:P106 wd:Q170790;
      wdt:P27 ?country.
    VALUES ?occupation {
      wd:Q170790
    }
    OPTIONAL {
      ?optionWikipedia_article schema:about ?id;
        schema:isPartOf <https://en.wikipedia.org/>.
    }
    OPTIONAL { ?id wdt:P18 ?img. }
    OPTIONAL {
      ?id p:P800 _:b102.
      _:b102 ps:P800 ?optionNotable_work.
    }
    OPTIONAL { ?id wdt:P166 ?optionAward. }
    OPTIONAL {
      ?id (wdt:P802|wdt:P185) ?student.
      ?student wdt:P106 wd:Q170790.
    }
  }
  UNION
  {
    ?id wdt:P549 ?mgid;
      wdt:P27 ?country.
  }
  OPTIONAL {
    ?id (p:P1066|p:P184) _:b103.
    _:b103 (ps:P1066|ps:P184) ?parentId.
    ?parentId wdt:P106 wd:Q170790.
  }
  OPTIONAL { ?id wdt:P737 ?influencer. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?id ?idLabel ?country ?countryLabel ?occupation ?parentId ?optionAward ?optionAwardLabel ?mgid ?optionNotable_work ?optionNotable_workLabel ?optionWikipedia_article ?influencer
Try it!
@Ralfalfa83: I've converted this to a named subquery, collecting a set of ?id, and then using LIMIT 1000 to send that set to the terminal query, which fetches the various OPTIONALs and the labels.
I think the big problem you have here is that you're getting the Cartesian product of all of the optionals: 1000 IDs produces 25k result rows (Your milage may vary - depends which set of 1000 IDs is chosen, different report servers will serve up different sets; I've reduced the LIMIT to avoid timeouts). So maybe you have to rethink the approach, using perhaps GROUP_CONCAT() on the various OPTIONALS, with a separator, or a set of distinct queries. iirc there are likely to be more mathematicians in wikidata that will be reported on in a single query, especially where you are looking at property statements & ps: values. You'll almost certainly have to grab data in chunks using OFFSET and LIMIT.
SELECT ?id ?idLabel ?country ?countryLabel ?occupation ?parentId ?optionAward ?optionAwardLabel ?mgid ?optionNotable_work ?optionNotable_workLabel ?optionWikipedia_article ?influencer (SAMPLE(?student) AS ?student) (SAMPLE(?img) AS ?img) 
WITH {SELECT DISTINCT ?id WHERE 
{
  { ?id wdt:P106 wd:Q170790 . }
  UNION
  { ?id wdt:P549 ?mgid . }
  } LIMIT 500 } as %i
WHERE
{
  INCLUDE %i
  ?id wdt:P27 ?country.
  OPTIONAL {
       ?optionWikipedia_article schema:about ?id;
       schema:isPartOf <https://en.wikipedia.org/>.
            }
   OPTIONAL { ?id wdt:P18 ?img. }
   OPTIONAL {
     ?id p:P800 _:b102.
     _:b102 ps:P800 ?optionNotable_work.
            }
   OPTIONAL { ?id wdt:P166 ?optionAward. }
   OPTIONAL {
     ?id (wdt:P802|wdt:P185) ?student.
     ?student wdt:P106 wd:Q170790.
            }
 OPTIONAL {
   ?id (p:P1066|p:P184) _:b103.
   _:b103 (ps:P1066|ps:P184) ?parentId.
   ?parentId wdt:P106 wd:Q170790.
           }
 OPTIONAL { ?id wdt:P737 ?influencer. }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }

} GROUP BY ?id ?idLabel ?country ?countryLabel ?occupation ?parentId ?optionAward ?optionAwardLabel ?mgid ?optionNotable_work ?optionNotable_workLabel ?optionWikipedia_article ?influencer
Try it!
--Tagishsimon (talk) 11:45, 31 December 2020 (UTC)

How to get ask a query to find all the given names and their meanings

Hi

I'm struggeling with the following: I need a list of given names, by country , and their meaning, origin, related names.

Can you please help?

Thx in advance!

I would like to query the demographics of a location using the MediaWiki API Service

I see that I can query the population of a location like so: 

SELECT ?population WHERE {
  SERVICE wikibase:mwapi {
      bd:serviceParam mwapi:search "Singapore" .    
      bd:serviceParam mwapi:language "en"
      bd:serviceParam wikibase:api "EntitySearch" .
      bd:serviceParam wikibase:endpoint "www.wikidata.org" .
      bd:serviceParam wikibase:limit 1 .
      ?item wikibase:apiOutputItem mwapi:item .
  }
  ?item wdt:P1082 ?population
}
Try it!

Is there a way to query the population based on ethnicity of that location?

Retrieved from "https://www.wikidata.org/w/index.php?title=Wikidata:Request_a_query/Archive/2020/12&oldid=1341108678"