Shortcuts: WD:RAQ, w.wiki/LX

Wikidata:Request a query

From Wikidata
Jump to navigation Jump to search

Request a query
Fishing in the Wikidata river requires both an idea where to look for fish and a suitable fishing method. If you have the former, this page can help you find the latter.

This is a page where SPARQL 1.1 Query Language (Q32146616) queries can be requested. Please provide feedback if a query is written for you.

For sample queries, see Examples and Help:Dataset sizing. Property talk pages include also summary queries for these.

For help writing your own queries, or other questions about queries, see Wikidata talk:SPARQL query service/queries and Wikidata:SPARQL query service/query optimization.

Help resources about Wikidata Query Service (Q20950365) and SPARQL: Wikidata:SPARQL query service/Wikidata Query Help and Category:SPARQL.

To report an issue about the Query Service (interface, results views, export...) please see Wikidata:Contact the development team/Query Service and search.
On this page, old discussions are archived. An overview of all archives can be found at this page's archive index. The current archive is located at 2021/09.

Number of artists / cultural organisations from Québec[edit]

Hi! I am looking to find out how many artists (Q483501) or cultural institutions (Q3152824) / art organizations (Q7062022) from the province of Quebec (Q176) are listed actually on Wikidata (all disciplines combined). I have no clue as to where to start in creating a SPARQL query, but here are some properties I found that could be useful (I think), if someone (anyone) is willing to give me a hand. Thanks in advance for your help.

  • Wikidata property related to art (Q27918607)
  • Québec (Q176)
  • Artists (Q483501)
  • Cultural institution (Q3152824)
  • not-for-profit arts organization (Q7062022)
  • nonprofit organization (Q163740)
  • field of this occupation : Art (Q735)
  • sub-class of : Art (Q2018526)
  • part of / field of work : Culture (Q11042)
  • has quality of : art genre (Q1792379) , art style (Q1792644)
  • Catégorie:Arts (Q4104783)
  • Catégorie:Association ou organisme culturel au Québec (Q49656487)
  • Catégorie:Association ou organisme culturel (Q8809115)

Listing of labels from the new query builder[edit]

Will this query be possible. Originaly buildt with the new querybuilder here https://w.wiki/3yJp and then modifyed like this:

SELECT DISTINCT ?item ?itemLabel ?dob ?dobLabel ?histreg ?histregLabel WHERE {

 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
 {
   SELECT DISTINCT ?item WHERE {
     ?item p:P27 ?statement0.
     ?statement0 (ps:P27/(wdt:P279*)) wd:Q20.
     ?item p:P21 ?statement1.
     ?statement1 (ps:P21/(wdt:P279*)) wd:Q6581072.
     ?item p:P569 ?statement_2.
     ?statement_2 psv:P569 ?statementValue_2.
     ?statementValue_2 wikibase:timeValue ?P569_2.
     ?item p:P4574 ?statement3.
     ?statement3 (ps:P4574) _:anyValueP4574.
     MINUS {
       ?item p:P19 ?statement4.
       ?statement4 (ps:P19/(wdt:P279*)) _:anyValueP19.
    OPTIONAL { ?item wdt:P569 ?dob } 
 
   
    OPTIONAL { ?item wdt:P4574 ?histreg }
     }
     SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],nn,nb,en,sv,fi". }  
   }
   LIMIT 10000
 }

} Pmt (talk) 11:54, 28 August 2021 (UTC)

Also notifying @Mohammed Sadat

@Pmt: Some changes get the blank columns working again. In essence the OPTIONAL clauses were within a MINUS clause.
SELECT DISTINCT ?item ?itemLabel ?dob ?dobLabel ?histreg ?histregLabel WHERE {
 {
   SELECT DISTINCT ?item ?dob ?histreg WHERE {
     ?item p:P27 ?statement0.
     ?statement0 (ps:P27/(wdt:P279*)) wd:Q20.
     ?item p:P21 ?statement1.
     ?statement1 (ps:P21/(wdt:P279*)) wd:Q6581072.
     ?item p:P569 ?statement_2.
     ?statement_2 psv:P569 ?statementValue_2.
     ?statementValue_2 wikibase:timeValue ?P569_2.
     ?item p:P4574 ?statement3.
     ?statement3 (ps:P4574) _:anyValueP4574.
     MINUS {
       ?item p:P19 ?statement4.
       ?statement4 (ps:P19/(wdt:P279*)) _:anyValueP19.
     }
     OPTIONAL { ?item wdt:P569 ?dob } 
     OPTIONAL { ?item wdt:P4574 ?histreg }
#     SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],nn,nb,en,sv,fi". }  
   }
   LIMIT 10000
 }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
}
Try it! --Tagishsimon (talk) 15:13, 31 August 2021 (UTC)
Thanks once again! Pmt (talk) 18:13, 6 September 2021 (UTC)

Commons depicts data for files in a given category[edit]

Hello, I'm trying to obtain all the wdt:P180 labels for files tagged using the ISA Tool for campaign #150, which works on whole categories of files at a time.

My first example is the category 1930s photographs in Auckland Museum.

This is what I've tried so far:

PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX ps: <http://www.wikidata.org/prop/statement/>
PREFIX pq: <http://www.wikidata.org/prop/qualifier/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX bd: <http://www.bigdata.com/rdf#>

SELECT ?file ?title ?label

WITH {
  SELECT * WHERE {
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:api "Generator" .
      bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
      bd:serviceParam mwapi:gcmtitle "Category:1930s photographs in Auckland Museum" .
      bd:serviceParam mwapi:generator "categorymembers" .
      bd:serviceParam mwapi:gcmtype "file" .
      bd:serviceParam mwapi:gcmlimit "max" .
      ?title wikibase:apiOutput mwapi:title .
      ?pageid wikibase:apiOutput "@pageid" .
    }
    BIND (URI(CONCAT('https://commons.wikimedia.org/entity/M', ?pageid)) AS ?file)
  }
} AS %files

WITH {
  SELECT ?depicts WHERE {
    INCLUDE %files .
    ?file wdt:P180 ?depicts .
  }
} AS %depictions

WHERE {
  INCLUDE %files .
  INCLUDE %depictions .
  SERVICE <https://query.wikidata.org/sparql> {
    SERVICE wikibase:label {
      bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,mi".
      ?depicts rdfs:label ?label .
    }
  }
}
LIMIT 50

Try it!

However, running this results in the same label appearing for every entry, and I can't work out why. I have tried commenting-out line 37 (INCLUDE %files .) but this, of course, means the results don't include the title or label! I would like to get each labels in English and te reo Māori, and use (GROUP_CONCAT (?label; SEPARATOR=";") AS ?labels) to concatenate them in the same variable/column.

Any help would be appreciated. Thank you. —Hugh 06:58, 31 August 2021 (UTC)

@HughLilly: I think what's going on is you're getting the Cartesian product of %files x %depictions ... there's nothing in %depictions (a single column of ?depicts) to mesh it to %files (two columns, ?file & ?title). I've amended the SELECT in the %depictions subquery, and removed INCLUDE %files from the final subquery, and added EN and MI labels and aggregated the results; and I think we're now good. Got rid of the PREFIXes too, 'cos not needed for this exercise, and added |project=sdc to the SPARQL template to point to the correct report server. hth.
SELECT ?file ?title (GROUP_CONCAT(DISTINCT ?mi_label;separator="; ") as ?miLabel) (GROUP_CONCAT(DISTINCT ?en_label;separator="; ") as ?enLabel)
WITH {
  SELECT * WHERE {
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:api "Generator" .
      bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
      bd:serviceParam mwapi:gcmtitle "Category:1930s photographs in Auckland Museum" .
      bd:serviceParam mwapi:generator "categorymembers" .
      bd:serviceParam mwapi:gcmtype "file" .
      bd:serviceParam mwapi:gcmlimit "max" .
      ?title wikibase:apiOutput mwapi:title .
      ?pageid wikibase:apiOutput "@pageid" .
    }
    BIND (URI(CONCAT('https://commons.wikimedia.org/entity/M', ?pageid)) AS ?file)
  }
} AS %files

WITH {
  SELECT ?file ?title ?depicts WHERE {
    INCLUDE %files .
    ?file wdt:P180 ?depicts .
  }
} AS %depictions

WHERE {

  INCLUDE %depictions .
  SERVICE <https://query.wikidata.org/sparql> {
      OPTIONAL {?depicts rdfs:label ?mi_label . filter(lang(?mi_label) = "mi")}
      OPTIONAL {?depicts rdfs:label ?en_label . filter(lang(?en_label) = "en")}    
    
  }
} group by ?file ?title
LIMIT 50
Try it! --Tagishsimon (talk) 11:56, 31 August 2021 (UTC)

Secondary questions[edit]

Thank you very much; that's got me a bit further toward my goal. I didn't realise you needed to specify the server in the template or use two separate OPTIONAL calls for the labels, but of course that makes sense.
Using A_waterfall_in_a_forest_(AM_75762-1).jpg as an example, I would also like to get some of the data in the {{Artwork}} template. I'd specifically like to get what's under the source and accession number parameters. Running a direct query on this entity doesn't appear to deliver this data.
1. How can I run a SPARQL query which operates on only one entity?
2. Can I run a query that just gets all triples, to show me what data exists?
3. How can I learn what other possible data exists at mwapi: points? For example, I know mwapi:title exists, but how do I work out what else is available? I've done some reading of the documentation and it seems like I might need to use props=text, like so -- is that correct? Do I then just need to parse the text? But how do I do this in SPARQL?
Thanks again. :) —Hugh 22:32, 31 August 2021 (UTC)
@Dipsacus fullonum: Is there a means, via SPARQL/mwapi, to get data from a wikipedia article or commons file template (such as {{Artwork}} from A_waterfall_in_a_forest_(AM_75762-1).jpg)? --Tagishsimon (talk) 02:16, 9 September 2021 (UTC)
@Hl, Tagishsimon: If you use the parse API (action=parse) with prop=text as above you get the resulting HTML code after parsing all wiki makeup and transcluding all templates. To instead get the source text for the page use prop=wikitext like this: https://commons.wikimedia.org/w/api.php?action=parse&format=jsonfm&prop=wikitext&pageid=65558537. But you cannot access the parse API from the SPARQL mwapi.
An alternative way to get the wikitext for a page – which you can use from SPARQL code – is to use the revision API (action=query&prop=revisions) with the the rvprop=content parameter like this: https://commons.wikimedia.org/w/api.php?action=query&prop=revisions&titles=File:A%20waterfall%20in%20a%20forest%20(AM%2075762-1).jpg&rvslots=main&rvprop=content.
A third method is to extract a dump of the information you can search for in a page with cirrus search like this: https://commons.wikimedia.org/w/api.php?action=query&prop=cirrusbuilddoc&titles=File:A%20waterfall%20in%20a%20forest%20(AM%2075762-1).jpg.
You can search for all wikipages that transclude a given template with the templates API (action=query&prop=templates) or with cirrus search using the parameter hastemplate:. But there is to my knowledge no simple way to extract the content of the template parameters other than trying a insource: search or otherwise analyze the wikitext of the page. --Dipsacus fullonum (talk) 08:45, 9 September 2021 (UTC)
@Dipsacus fullonum, @Tagishsimon, thanks so much. This looks like it has set me up well for exploring what's there and parsing it. —Hugh 23:47, 9 September 2021 (UTC)
Hello again, @Dipsacus fullonum. I wonder if I could trouble you for some more help? Using the Douglas Adams example here, I've worked up this query:
SELECT * WHERE {
  BIND (URI(CONCAT('https://commons.wikimedia.org/entity/M', ?pageid)) AS ?file)
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Generator";
                    wikibase:endpoint "commons.wikimedia.org";
                    mwapi:gcmtitle "Category:1930s photographs in Auckland Museum";
                    mwapi:generator "categorymembers";
                    mwapi:gcmtype "file";
                    mwapi:gcmlimit "max";
                    mwapi:prop "revisions";
                    mwapi:rvprop "content".
    ?title wikibase:apiOutput mwapi:title.
    ?pageid wikibase:apiOutput "@pageid".
    ?contentmodel wikibase:apiOutput 'revisions/rev/@contentmodel'.
    ?contentformat wikibase:apiOutput 'revisions/rev/@contentformat'.
    ?content wikibase:apiOutput 'revisions/rev/text()'.
  }
} 
LIMIT 10
Try it!
Only 2 of the 10 files returned by this query have text in the results, even though all of them have text in their pages. Why is this? Using File:Group posing with a hurricane damaged fence (AM 84399-1).jpg as an example, the structure of the JSON response appears to be the same. I can't find any documentation on the text() portion of the XPath statement—is there anything you could point me to? Thanks.
Edit: I've been trying the above directly on WD because the Commons Query Service has been giving me HTTP 500 errors. However I've combined the queries on there and hope this query might work once the server is back up. —HughLilly (talk) (WP user page) 03:17, 17 September 2021 (UTC)
@HughLilly: Each API call fetched 500 category members (due to gcmlimit=max where max is 500) but there is a lower limit for prop=revisions giving max 50 revisions per API call. That means your query fetched 500 category members of which only 50 included content of the latest revision, and then 490 of the results was randomly discarded due to LIMIT 10, leaving some results with and some without content. Your value for gcmlimit should not exceed 50 (and not the number in the LIMIT clause either as that is a waste). Another thing, you should add rvslots=main to the API call as revisions API calls without specifying the revisions slot is deprecated and may stop to work in the future. However that will change the format of the API results, so the output section of the MWAPI SPARQL code should changed accordingly. About XPath there is plenty of tutorials around e.g. at https://www.w3schools.com/xml/xpath_intro.asp, but your use of XPath wasn't the problem. A modified query will be:
SELECT * WHERE {
  BIND (URI(CONCAT('https://commons.wikimedia.org/entity/M', ?pageid)) AS ?file)
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Generator";
                    wikibase:endpoint "commons.wikimedia.org";
                    mwapi:gcmtitle "Category:1930s photographs in Auckland Museum";
                    mwapi:generator "categorymembers";
                    mwapi:gcmtype "file";
                    mwapi:gcmlimit "10";
                    mwapi:prop "revisions";
                    mwapi:rvslots "main";
                    mwapi:rvprop "content";
                    wikibase:limit "once".
    ?title wikibase:apiOutput mwapi:title.
    ?pageid wikibase:apiOutput mwapi:pageid.
    ?contentmodel wikibase:apiOutput 'revisions/rev/slots/slot/@contentmodel'.
    ?contentformat wikibase:apiOutput 'revisions/rev/slots/slot/@contentformat'.
    ?content wikibase:apiOutput 'revisions/rev/slots/slot/text()'.
  }
}
LIMIT 10
Try it! . --Dipsacus fullonum (talk) 05:16, 17 September 2021 (UTC)
PS. What's the point in fetching contentmodel and contentformat? Wont they always be the same for files? --Dipsacus fullonum (talk) 05:41, 17 September 2021 (UTC)
The reason for that was that I simply copied the Douglas Adams example verbatim. —HughLilly (talk) (WP user page) 01:44, 20 September 2021 (UTC)
@HughLilly: PPS. I forgot to include bd:serviceParam wikibase:limit "once". When you have LIMIT clause in the query, you should both apply the same limit to the API calls, and disable continuation calls. Otherwise all category members will be fetched regardsless. bd:serviceParam wikibase:limit 10 . can also be used for the same purpose. I edited the query. --Dipsacus fullonum (talk) 06:02, 17 September 2021 (UTC)
────────────────────────────────────────────────────────────────────────────────────────────────────
@Dipsacus fullonum, thank you. The LIMIT 10 was purely for testing; I want to get all the files. I take it I don't need to limit the revision by including mwapi:rvlimit "1";, because the API returns only the most recent revision by default? Also, if this search retrieves only the first 50 category members (i.e., files), how do I get the rest? The documentation is of little help.
I've now tried to combine the two queries:
SELECT
?file
?title
(GROUP_CONCAT(DISTINCT ?en_label; separator=", ") as ?labels_en)
(GROUP_CONCAT(DISTINCT ?mi_label; separator=", ") as ?labels_mi)
?content

WITH {
  SELECT * WHERE {
    BIND (URI(CONCAT('https://commons.wikimedia.org/entity/M', ?pageid)) AS ?file)
    SERVICE wikibase:mwapi {
      bd:serviceParam wikibase:api "Generator";
                      wikibase:endpoint "commons.wikimedia.org";
                      mwapi:gcmtitle "Category:1930s photographs in Auckland Museum";
                      mwapi:generator "categorymembers";
                      mwapi:gcmtype "file";
                      mwapi:gcmlimit "50";
                      mwapi:prop "revisions";
                      mwapi:rvslots "main";
                      mwapi:rvprop "content";
                      wikibase:limit "once".
      ?title wikibase:apiOutput mwapi:title.
      ?pageid wikibase:apiOutput mwapi:pageid.
      ?content wikibase:apiOutput 'revisions/rev/slots/slot/text()'.
    }
  }
} AS %files

WITH {
  SELECT * WHERE {
    INCLUDE %files .
    ?file wdt:P180 ?depicts .
  }
} AS %depictions

WHERE {
  INCLUDE %depictions .
  SERVICE <https://query.wikidata.org/sparql> {
    OPTIONAL {?depicts rdfs:label ?mi_label . filter(lang(?mi_label) = "mi")}
    OPTIONAL {?depicts rdfs:label ?en_label . filter(lang(?en_label) = "en")}
  }
}

GROUP BY ?file ?title ?content
Try it!
…but this hasn't worked. I would appreciate some more help if you're able. Thanks. —Hugh (talk) 03:02, 20 September 2021 (UTC)
@HughLilly: WCQS still seems to be down, so I cannot test it. But I only see one error: you should remove bd:serviceParam wikibase:limit "once" again. I inserted that triple to stop the MWAPI service from doing continuation calls for when there was LIMIT clause.
The documentation for the revisions query is at https://commons.wikimedia.org/w/api.php?action=help&modules=query%2Brevisions. You use the revisions query in that it calls mode 1 ("Get data about a set of pages (last revision), by setting titles or pageids.") where you always get the last revision and where rvlimit may not be used. The documentation fails to mention the limit of max 50 results per mode 1 call; I found it by doing the API call manually in order to see how the output is formatted so I could make the XPath code. The limit however is similar to the limits of several other queries that can be embedded in a generator query.
The query should work fine with gcmlimit=50: each API call fetch 50 category members each with their latest revison, and the MWAPI service will make continuation calls as long as there are more category members or until it reaches the limit set by wikibase:limit or the hard-coded limit of 10.000. --Dipsacus fullonum (talk) 06:41, 20 September 2021 (UTC)
Thanks, @Dipsacus fullonum. Must this call be done against the Commons endpoint? Can I not perform it against the WD endpoint? —Hugh (talk) 08:04, 20 September 2021 (UTC)
@HughLilly: Hi. The triple ?file wdt:P180 ?depicts queries the structured data at Commons, while fetching the labels uses data from Wikidata. Using WMAPI can happen from WDQS and WCQS. The query is now written to run at WCQS and access WDQS in a federated subquery. It could also be reversed, but it will still depend on both endpoints working. --Dipsacus fullonum (talk) 11:54, 20 September 2021 (UTC)

────────────────────────────────────────────────────────────────────────────────────────────────────

Applying regex to query[edit]

@Dipsacus fullonum, Thanks once again. Having asked a Stack Overflow question and obtained the regex I need to use to extract the strings from the ?content variable, I'm stuck on trying to implement this in SPARQL. I thought I needed to use BIND and REPLACE, and read the documentation for those functions, but running this query (limited to just one file for testing purposes) does not return what I want; I don't think I actually want to replace anything, but instead extract.

SELECT * WHERE {
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Generator";
                    wikibase:endpoint "commons.wikimedia.org";
                    mwapi:generator "revisions";
                    mwapi:pageids "64827585";
                    mwapi:gcmtype "file";
                    mwapi:gcmlimit "1";
                    mwapi:prop "revisions";
                    mwapi:rvslots "main";
                    mwapi:rvprop "content";
                    wikibase:limit "once".
    ?title wikibase:apiOutput mwapi:title.
    ?pageid wikibase:apiOutput mwapi:pageid.
    ?content wikibase:apiOutput 'revisions/rev/slots/slot/text()'.
  }
  BIND(REPLACE(?content, "section[^=]*=(.*?)\\|", "$1") AS ?section)
}

Try it!

This is, of course, just one of three BIND statements I need to make. I would appreciate it if you could point me in the direction of some documentation that might help me achieve my goal. If it's too difficult or computationally intensive to do this in SPARQL I am happy to switch to a Python script after downloading the results of the query as a CSV, but if I can do this in SPARQL I would love to be able to. Thanks again. —Hugh (talk) 00:44, 21 September 2021 (UTC)

@HughLilly: First, the definition of how REGEX and REPLACE works in SPARQL is in the document https://www.w3.org/TR/xpath-functions/#regex-syntax. The REPLACE function will replace each match with the given replacement string and leave anything else unchanged. Your REPLACE call above did find the value for "section" parameter in the template, and then replaced it with itself. To isolate the value, you need to match the whole string, so it is whole string that gets replaced. For example to get isolate the letter B in the string "ABC", you can use BIND(REPLACE("ABC", "^(.*?)(B)(.*)$", "$2") AS ?B). Note that the whole string is matched and then replaced with capture number 2 which is the found B. With that in mind, your query will work with BIND(REPLACE(?content, "^(.*?section[^=]*?=)(.*?)(\\|.*)$", "$2", "s") AS ?section). The flag "s" is necessary and means that a dot (".") will also match newline characters which it wouldn't without the flag. --Dipsacus fullonum (talk) 07:27, 21 September 2021 (UTC)
PS. Capture 1 and 3 isn't used for anything and can be omitted giving: BIND(REPLACE(?content, "^.*?section[^=]*?=(.*?)\\|.*$", "$1", "s") AS ?section) with the same result. --Dipsacus fullonum (talk) 07:44, 21 September 2021 (UTC)

Optimizing a query with language fallback[edit]

Two weeks ago I asked whether this query can be optimized.

User:Tagishsimon was kind enough to propose an optimization. It is indeed much faster, but unfortunately we discovered that this optimized query does not give the full results. For instance, the results lack the description field for row 't Kasteel van Aemstel (Q2168591), whereas the original query has a value for that field ("Rijksmonument op Nieuwezijds Voorburgwal 67").

This is important, because of the following scenario: Imagine I (a German person) go to Spain and open the app to see nearby Wikidata items. My phone is set to German, so I see labels and descriptions in German when there are German labels and descriptions. Because it is Spain most items have no German label/description, but showing Spanish label/description would be much better than showing nothing.

Is there a way to optimize the query without losing this language fallback ability?
Thanks a lot! Syced (talk) 17:11, 8 September 2021 (UTC)

@Syced: In the line SERVICE wikibase:label { bd:serviceParam wikibase:language "en,nl,[AUTO_LANGUAGE]". you can set the languages returned by the label service. In my example, I see it had just "en,[AUTO_LANGUAGE]". I've added 'nl', from where I copied the above. Add more language codes as you wish - de,fr,es, &c &c. The label service will return the first label it finds, according to the order in which you specify the codes. [AUTO_LANGUAGE] is whatever language your WD user interface is set to. I don't know of a way to get the service to return a label without specfying a set of languages. If your use has an awareness of which language the user might prefer, and you can reconfigure the order of languages, then good. If not then I guess you have to make a call on what your preferred order is. --Tagishsimon (talk) 21:09, 8 September 2021 (UTC)
Thanks again for your help Tagishsimon, that's extremely helpful! I compiled this list from https://en.wikipedia.org/wiki/List_of_Wikipedias:
"${LANG},en,fr,de,es,ja,ru,it,zh,pt,ar,fa,pl,nl,id,uk,he,sv,cs,ko,vi,ca,no,fi,hu,tr,th,hi,bn,ceb,ro,sw,kk,da,eo,sr,lt,sk,bg,sl,eu,et,hr,ms,el,arz,ur,ta,te,nn,gl,az,af,bs,be,ml,ka,is,sq,uz,la,br,mk,lv,azb,mr,sh,tl,cy,ckb,ast,be-tarask,zh-yue,hy,pa,as,my,kn,ne,si,tt,ha,war,zh-min-nan,vo,min,lmo,ht,lb,gu,tg,sco,ku,new,bpy,nds,io,pms,su,oc,jv,nap,ba,scn,wa,bar,an,ksh,szl,fy,frr,als,ia,ga,yi,mg,gd,vec,ce,sa,mai,xmf,sd,wuu,mrj,mhr,km,roa-tara,am,roa-rup,map-bms,bh,mnw,shn,bcl,co,cv,dv,nds-nl,fo,hif,fur,gan,glk,hak,ilo,pam,csb,avk,lij,li,gv,mi,mt,nah,nrm,se,nov,qu,os,pi,pag,ps,pdc,rm,bat-smg,sc,to,tk,hsb,fiu-vro,vls,yo,diq,zh-classical,frp,lad,kw,mn,haw,ang,ln,ie,wo,tpi,ty,crh,nv,jbo,ay,pcd,zea,eml,ky,ig,or,cbk-zam,kg,arc,rmy,ab,gn,so,kab,ug,stq,udm,ext,mzn,pap,cu,sah,tet,sn,lo,pnb,iu,na,got,bo,dsb,chr,cdo,om,sm,ee,ti,av,bm,zu,pnt,cr,pih,ss,ve,bi,rw,ch,xh,kl,ik,bug,dz,ts,tn,kv,tum,xal,st,tw,bxr,ak,ny,fj,lbe,za,ks,ff,lg,sg,rn,chy,mwl,lez,bjn,gom,tyv,vep,nso,kbd,ltg,rue,pfl,gag,koi,krc,ace,olo,kaa,mdf,myv,srn,ady,jam,tcy,dty,atj,kbp,din,lfn,gor,inh,sat,hyw,nqo,ban,szy,awa,ary,lld,smn,skr,mad,dag,shi,nia,ki,gcr"
... with ${LANG} being the phone's language, and it works great, fast and with nice fallback :-) Syced (talk) 10:04, 9 September 2021 (UTC)
@Syced: There are also labels, descriptions and aliases in other languages than the languages which have Wikipedias. So if you want a more complete list of used language codes, please take a look at User:Mr. Ibrahem/Language statistics for items. Although I doubt that any items only have labels in languages not in the list above, it is theoretically possible. --Dipsacus fullonum (talk) 22:00, 11 September 2021 (UTC)

WD Talk page extract[edit]

Hello, is it possible to get the following : (for one P31, says towns of France) get (the towns of France Qid) (first 50 characters of their Wikidata talk page) ? Bouzinac💬✒️💛 09:18, 14 September 2021 (UTC)

@Bouzinac: It is possible but slow because you need a MWAPI call for every item. This query has a limit of 10 to avoid timeout:
SELECT ?item ?itemLabel ?extract
WHERE
{
  ?item wdt:P31 wd:Q484170 .
  BIND (SUBSTR(STR(?item), 32) AS ?item_title)
  OPTIONAL
  {
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:endpoint "www.wikidata.org" .
      bd:serviceParam wikibase:api "Generator" .
      bd:serviceParam mwapi:generator "allpages" .
      bd:serviceParam mwapi:gapnamespace "1" .
      bd:serviceParam mwapi:gapfrom ?item_title .
      bd:serviceParam mwapi:gapto ?item_title .
      bd:serviceParam mwapi:prop "extracts" .
      bd:serviceParam mwapi:explaintext "true" .
      bd:serviceParam mwapi:exchars "50" .
      bd:serviceParam mwapi:exlimit "1" .
      ?extract wikibase:apiOutput "extract/text()".
    }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 10
Try it! --Dipsacus fullonum (talk) 09:27, 15 September 2021 (UTC)
This query https://w.wiki/454A with a small P31 without limit works perfectly. Thanks!

Number of women in Wikidata without a Wikipedia article[edit]

Hi, I am trying to find out the number of women in Wikidata who do not have a Wikipedia article. My first attempt looks like this:

SELECT (COUNT(*) AS ?count) WHERE {
  ?item wdt:P21 wd:Q6581072.
  ?item wdt:P31 wd:Q5.
MINUS {                            
    ?wen schema:about ?item .
    ?wen schema:isPartOf/wikibase:wikiGroup "wikipedia"  . }
}

Try it!

Unfortunately my query times out. Is there a way to modify it (or another way to find out the number)? I don't have much experience with Wikidata and Sparql and would appreciate some help. Thanks! Rrrrraaaa (talk) 19:52, 14 September 2021 (UTC)

@Rrrrraaaa: I tried

SELECT (COUNT(*) AS ?count) WHERE {
  ?item wdt:P21 wd:Q6581072.
  ?item wdt:P31 wd:Q5.
  ?item wikibase:sitelinks 0 .
}

Try it!

and it didn't timeout (53 seconds, very close!): the result is 1020075 as of now. --Epìdosis 21:37, 14 September 2021 (UTC)

To split hairs, that is not drawing a distinction between absence of a wikipedia sitelink, and absence of any sitelink; a non-zero wikibase:sitelinks value does not necessarily indicate a wikipedia article. Humaniki should be able to answer the question, but doesn't seem to be able to. --Tagishsimon (talk) 21:40, 14 September 2021 (UTC)
The query would give a count of zero as was first written: ?item wikibase:sitelinks 0. will match items with 0.0 sitelinks where 0.0 is a decimal number, i.e. none. You need a space between the number and the dot to search for integers. I inserted the space in the query. --Dipsacus fullonum (talk) 05:57, 15 September 2021 (UTC)
Thanks a lot for your help! As @Tagishsimon: wrote, zero sitelinks unfortunately means the person is missing from all sorts of wiki projects. I've been trying to identify only women without Wikipedia articles. I've also tried to find out the number via Humaniki, but had no luck. So I would probably have to download a dump to answer the question, right?
I think so, yes, unless humaniki can help. @Sek2016: might be able to provide some input? --Tagishsimon (talk) 19:36, 15 September 2021 (UTC)

List of all black people[edit]

I understand that race/ethnicity are difficult classifications.

I am hoping for a query that will list all (non-fictional) people that are black in order of most sitelinks. This list would include Barack Obama, Tiger Woods, Malcolm X, Nelson Mandela, Concha Buika etc.

If it's possible, the list would also include any (non fictional) person who has a parent that is black. (Unless that parent is not the biological parent).

Thank you very much, and If I offended, or violated some wikidata rule, I am sorry, this is my first ever query/post/edit of any wiki.

Most items for persons don't have statements for race or ethnicity, so I think that is impossible due lack of data. --Dipsacus fullonum (talk) 05:45, 15 September 2021 (UTC)

The query doesn't need to include all black people, but it does need to exclude people that are fictional, and does need to include anyone with the race or ethnicity of "black people" or a subclass of "black people", or a subclass of a subclass of "black people"...

I managed to write a query that produced a list of 20,000+ African Americans, but some of them were fictional, and when I try to broaden the query, it starts excluding African Americans, even though "African American" is a subclass of "Black People"

You wrote that the list would include Nelson Mandela. Wikidata has no indication that mr. Mandela have an ethnicity of "black people", so the task is impossible. --Dipsacus fullonum (talk) 09:45, 15 September 2021 (UTC)

train stations with IATA Code[edit]

I would love a list of all railway station (Q55488) (and subclasses?) that have a IATA airport code (P238) statement. And if the list showed the IATA code, even better. Thanks in advance!--Geogast 🤲 (talk) 12:36, 15 September 2021 (UTC)

Something like list of IATA-indexed railway stations (Q1655167) should be the result; of course, very, very incomplete until now.--Geogast 🤲 (talk) 12:38, 15 September 2021 (UTC)
@Geogast:
SELECT DISTINCT ?item ?itemLabel ?IATA
WHERE
{
  ?item wdt:P31/wdt:P279* wd:Q55488 .
  ?item wdt:P238 ?IATA .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it! --Dipsacus fullonum (talk) 12:51, 15 September 2021 (UTC)
Oh, that was quick. Thanks a lot!!--Geogast 🤲 (talk) 13:04, 15 September 2021 (UTC)

Items with sitelinks to one wikipedia and not another[edit]

I'm trying to construct a query to return a list of items with enwiki links, but not hewiki sitelinks.

The closest I got without timeouts is to restrict by domain:

#list of items of a certain type with an enwiki sitelink but not a hewiki sitelink
SELECT DISTINCT ?item ?itemLabel ?article1
WHERE {
   wd:Q197 ^wdt:P279*/^wdt:P31 ?item .  # instance or subclass of airplane
   ?article1 schema:about ?item;
   schema:isPartOf <https://en.wikipedia.org/>.
   FILTER NOT EXISTS {
      ?article2 schema:about ?item;
            schema:isPartOf <https://he.wikipedia.org/>.
   }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Is there a way to do this without segmenting by topic? It would be OK if there was no item label, just the list of enwiki articles. Inductiveload (talk) 22:06, 15 September 2021 (UTC)

Probably not. 8.8m EN wiki sitelinks is beyond the scope of a 60s query. --Tagishsimon (talk) 22:16, 15 September 2021 (UTC)
@Tagishsimon: Can it be limited somehow, e.g. so you get 200 results? Inductiveload (talk) 22:19, 15 September 2021 (UTC)
@Inductiveload: Inclined to think this is the more likely way to employ a LIMIT:
#list of items of a certain type with an enwiki sitelink but not a hewiki sitelink
SELECT DISTINCT ?item ?itemLabel ?article1 WITH { SELECT ?item ?itemLabel ?article1 
WHERE {
   ?article1 schema:about ?item;
   schema:isPartOf <https://en.wikipedia.org/>.
} LIMIT 100000 } as %i
WHERE
{
  INCLUDE %i
  FILTER NOT EXISTS {
      ?article2 schema:about ?item;
            schema:isPartOf <https://he.wikipedia.org/>.
   }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Tagishsimon (talk) 22:32, 15 September 2021 (UTC)
Amazing as always, thank you! Inductiveload (talk) 22:44, 15 September 2021 (UTC)
I think it will be it bit faster if you remove DISTINCT so the engine doesn't have to search for duplicates. There wont be any without the property path. ?itemLabel can also be removed from the SELECT clause in the subquery. --Dipsacus fullonum (talk) 04:52, 16 September 2021 (UTC)

Without filtering by topic or using limit, you would have to get a 8848757 - 418251 as result.--- Jura 11:16, 16 September 2021 (UTC)

show qualifier[edit]

I have never understood how to show qualifiers in a Query. 🤷‍♂️
For instance, I would like a query of items with the statement season (P4908) : Sons of Garmadon (Q43133862).
Show me the series ordinal (P1545) qualifier (this is: its value) in the season (P4908) statement.--Geogast 🤲 (talk) 14:57, 16 September 2021 (UTC)

@Geogast: Do you mean like this?
SELECT ?item ?itemLabel ?number
WHERE
{
  ?item p:P4908 ?statement .
  ?statement a wikibase:BestRank . # Include only statements of best rank
  ?statement ps:P4908 wd:Q43133862 . # Item is season of Sons of Garmadon
  OPTIONAL { ?statement pq:P1545 ?number . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it! --Dipsacus fullonum (talk) 15:44, 16 September 2021 (UTC)
PS. series ordinal (P1545) has datatype string. If you want to order the results, you can cast the value to integer:
SELECT ?item ?itemLabel (xsd:integer(?number) AS ?no)
WHERE
{
  ?item p:P4908 ?statement .
  ?statement a wikibase:BestRank . # Include only statements of best rank
  ?statement ps:P4908 wd:Q43133862 . # Item is season of Sons of Garmadon
  OPTIONAL { ?statement pq:P1545 ?number . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
ORDER BY ?no
Try it! --Dipsacus fullonum (talk) 15:53, 16 September 2021 (UTC)
Actually, I didn't really see the difference between these two… However, they work really fine for me. Also, to fiddle around a bit. Thanks a lot!--Geogast 🤲 (talk) 13:27, 17 September 2021 (UTC)
It's the difference between ordering the series ordinals as integers (1, 2, 3, 4, ... 10) rather than as strings (1, 10, 2, 3, 4, ... 9). --Tagishsimon (talk) 00:12, 18 September 2021 (UTC)

People that have married two Olympians[edit]

Hi, I'm trying to get back into writing queries and was wondering if this question "Has anyone in history besides Yuriy Sedykh ever been married to two different Olympic gold medalists?" could be answered with a WD query, or if not that specific question, then the simpler question "Which people have been married to two different Olympians?"

All Olympians at the modern olympics should have Wikidata items with property participant in (P1344), and a value of something that itself has property instance of (P31) and value Summer Olympic Games (Q159821) or Winter Olympic Games (Q82414) I think. For example, participant in (P1344) 1988 Summer Olympics (Q8470).

So I'd be looking for someone who has multiple spouse (P26) values where at least two of those values matches the definition of "olympian" above. I understand the spousal data is incomplete and not all spouses have wikidata items, but I'm looking for the best we have here.

Here is a query I came up with to list all spouses of Olympians:

SELECT ?person ?personLabel
WHERE {
  VALUES ?olympics { wd:Q159821 wd:Q82414 }
  ?person wdt:P26 ?spouse.
  ?spouse wdt:P1344 ?event.
  ?event wdt:P31 ?olympics.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

However it doesn't filter that list to only find people with two or more Olympian spouses. Any help would be appreciated, thank you. --Habst (talk) 02:22, 21 September 2021 (UTC)

@Habst: Probably this sort of thing. Find those with an Olympic partner, then find those with another Olympic partner where P1 < P2 (which avoids getting two rows per set of spouses, which would occur with P1 != P2). Someone with three or more olympic spouses would get two or more rows in this report. See Wikidata:SPARQL query service/query optimization for the named subquery used in this example - trying to do it all in a single query timed-out.
SELECT DISTINCT ?person ?personLabel ?spouse1 ?spouse1Label ?spouse2 ?spouse2Label WITH {
  SELECT ?person ?spouse1 WHERE 
  {
    VALUES ?olympics { wd:Q159821 wd:Q82414 }
    ?person wdt:P26 ?spouse1.
    ?spouse1 wdt:P1344 ?event1.
    ?event1 wdt:P31 ?olympics.
    } } as %i
WHERE
{
  INCLUDE %i
  VALUES ?olympics { wd:Q159821 wd:Q82414 }
  ?person wdt:P26 ?spouse2.
  ?spouse2 wdt:P1344 ?event2.
  ?event2 wdt:P31 ?olympics.
  filter(str(?spouse1) < str(?spouse2) )
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
Try it! --Tagishsimon (talk) 02:44, 21 September 2021 (UTC)
@Tagishsimon: Thanks. It looks like that code doesn't properly check if the 2nd spouse is an Olympian and is only pulling people with two or more spouses, with at least one of them being an Olympian. But it only returns 17 results so I was able to sift through those to find the relevant ones. --Habst (talk) 03:44, 21 September 2021 (UTC)
Yes; I spotted that and fixed it. 11 now. --Tagishsimon (talk) 04:27, 21 September 2021 (UTC)
@Habst: You can find a few more matches by using Olympedia people ID (P8286) (though there is a slight chance of a false positive, as the Olympedia database does contain some non-athletes).
SELECT DISTINCT ?item ?itemLabel ?spouse1 ?spouse1Label ?spouse2 ?spouse2Label
WHERE
{
  ?spouse1 wdt:P8286 [].
  ?spouse2 wdt:P8286 [].
  ?spouse1 p:P26/ps:P26 ?item.
  ?spouse2 p:P26/ps:P26 ?item.
  FILTER(str(?spouse1) < str(?spouse2))
  SERVICE wikibase:label {bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".}
}
Try it!
To answer your original question: Ekaterina Gordeeva has been married to two different gold medalists and is rumored to have married a third (David Pelletier) last year.
--Quesotiotyo (talk) 04:29, 21 September 2021 (UTC)