Wikidata

Wikidata:Request a query/Archive/2021/12

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

List of all Bart Simpson prank call alter egos

I'd like to have a timeline of all Bart Simpson prank call alter egos (e.g. Seymour Butz (Q105974063)). Thank you very much! ★ → Airon 90 07:49, 30 November 2021 (UTC) 

#defaultView:Timeline
SELECT ?item ?itemLabel ?epiLabel ?pub
WHERE { 
  ?item p:P31 ?st.
  ?st ps:P31 wd:Q65048168;
      pq:P642 wd:Q5480.
  OPTIONAL {
    ?epi wdt:P674 ?item;
         wdt:P577 ?pub.
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?pub
Try it!

These are the ones Wikidata knows about. Enjoy. --Infrastruktur (T | C) 00:08, 1 December 2021 (UTC)

Minor issue regarding Humans per DoD query

Hi, @Quesotiotyo: has helped me tremendously creating a query that I'm using as we speak (context). However, there seems to be a small issue regarding persons of which multiple death dates exist in Wikidata.
For instance: Evelyn Laye has two DoD's connected to her. Therefore she shows up in the query results with both DoD-parameter "+1996-02-16" and "+1996-02-17". This is what we want.
However, regarding Robert Kühner this does not happen: connected DoD's to him are '27 February 1996' and '28 February 1996' but Robert does not show up in the results with DoD-parameter "+1996-02-27".
Does someone know what's going on? Regards, Mill 1 (talk) 17:07, 28 November 2021 (UTC) 


SELECT
  ?item ?articlename ?itemLabel ?itemDescription ?sl ?dob
  ?dod ?dod_refs ?cod ?mod
WITH
{
  SELECT
    ?item ?dod_stm ?dod
    (GROUP_CONCAT(CONCAT(?dod_ref_propLabel, ": ", ?dod_ref_valueLabel); SEPARATOR="~!") AS ?dod_refs)
  WHERE
  {
    VALUES ?dod {"+1996-02-27"^^xsd:dateTime}
    ?dod ^wdt:P570 ?item .
    ?item wdt:P31 wd:Q5 .
    OPTIONAL
    {
      ?item p:P570 ?dod_stm .
      ?dod_stm ps:P570 ?dod ;
               psv:P570 [wikibase:timePrecision ?dod_precision].
      FILTER( ?dod_precision >= "11"^^xsd:integer )
      OPTIONAL
      {
        ?dod_stm prov:wasDerivedFrom ?dod_ref .
        ?dod_ref ?dod_ref_pr ?dod_ref_value .
        ?dod_ref_prop wikibase:reference ?dod_ref_pr .
      }
    }
    SERVICE wikibase:label
    {
      bd:serviceParam wikibase:language "en" .
      ?dod_ref_prop rdfs:label ?dod_ref_propLabel .
      ?dod_ref_value rdfs:label ?dod_ref_valueLabel .
    }
  }
  GROUP BY ?item ?dod_stm ?dod
} AS %get_dod_refs
WITH
{
  SELECT
    ?item ?dod_stm ?dod ?dod_refs (GROUP_CONCAT(?codLabel; SEPARATOR=", ") AS ?cod)
  WHERE
  {
    INCLUDE %get_dod_refs
    OPTIONAL
    {
      ?item p:P509 ?cod_stm .
      ?cod_stm ps:P509 ?cod .
    }
    SERVICE wikibase:label
    {
      bd:serviceParam wikibase:language "en" .
      ?cod rdfs:label ?codLabel .
    }
  }
  GROUP BY ?item ?dod_stm ?dod ?dod_refs
} AS %get_cod
WITH
{
  SELECT
    ?item ?dod_stm ?dod ?dod_refs ?cod (SAMPLE(?modLabel) AS ?mod)
  WHERE
  {
    INCLUDE %get_cod
    OPTIONAL
    {
      ?item p:P1196 ?mod_stm .
      ?mod_stm ps:P1196 ?mod .
    }
    SERVICE wikibase:label
    {
      bd:serviceParam wikibase:language "en" .
      ?mod rdfs:label ?modLabel .
    }
  }
  GROUP BY ?item ?dod_stm ?dod ?dod_refs ?cod
} AS %get_mod
WHERE
{
  INCLUDE %get_mod
  ?item wikibase:sitelinks ?sl .
  ?item ^schema:about ?article .
  ?article schema:isPartOf <https://en.wikipedia.org/> ;
           schema:name ?articlename .
  OPTIONAL { ?item wdt:P569 ?dob ;
                   p:P569 ?dob_stm .
             ?dob_stm ps:P569 ?dob .
             ?dob_stm a wikibase:BestRank
           }
  SERVICE wikibase:label
    {
      bd:serviceParam wikibase:language "en" .
      ?item rdfs:label ?itemLabel .
      ?item schema:description ?itemDescription .
    }
  BIND(REPLACE(?itemLabel, "^.*(?<! [Vv][ao]n| [Dd][aeiu]| [Dd][e][lns]| [Ll][ae]) (?!([SJ]r\\.?|[XVI]+)$)", "") AS ?sortname)  
} ORDER BY ASC(UCASE(?sortname)) ASC(UCASE(?itemLabel))
Try it!
The query is for the best ranked date of death. Robert Kühner (Q2226482) has two values for date of death (P570). One is normal rank, and one is preferred rank, so the query will only show the date with preferred rank. --Dipsacus fullonum (talk) 18:36, 28 November 2021 (UTC)
Ah, thanks for clearing that up. Where can I find the data on 'normal/preferred ranked' for this date of death (P570)? Having trouble locating it in the item Robert Kühner (Q2226482). Mill 1 (talk) 18:52, 28 November 2021 (UTC)
@Mill 1: When you go to Q2226482#P570 you can see 3 figures immediately to the left of each date. If the top figure is completely filled in, the value has preferred rank. If the middle figure is completely filled in, the value has normal rank. And if the bottom figure is filled in, the value has deprecated rank. You can read more about ranking at Help:Ranking. --Dipsacus fullonum (talk) 14:10, 2 December 2021 (UTC)
@Dipsacus fullonum: Thank you clearing that up. I never would've guessed! Mill 1 (talk) 17:14, 2 December 2021 (UTC)

Access to QID or creation date

I use 

SELECT ?item
WHERE 
{
  ?item wikibase:statements 0 ; wikibase:sitelinks ?sitelinks .
  FILTER EXISTS {[] schema:about ?item ; schema:isPartOf <https://de.wikipedia.org/> . }
}
Try it!

to get items without any statements that have at least a german wikipedia sitelink.

I want to accelerate this query to restrict it to certain QIDs. How can I filter the QID or the creation date of this item?

I know FILTER(xsd:integer(strafter(str(?item),"Q")) < 2000000) but this is not very fast - is it possible to access the QID number diretly?

I know ?item schema:dateModified ?date FILTER (?date < "2015-01-01T00:00:00Z"^^xsd:dateTime) - is there an alternative to schema:dateModified like schema:dateCreated ?

--Fidoez (talk) 14:57, 29 November 2021 (UTC) 


#defaultView:AreaChart
SELECT ?date ?cumulativecount 
{
    hint:Query hint:optimizer "None".
    {   SELECT ?milestonep (COUNT(?item) as ?cumulativecount)
        WHERE
        {
          { SELECT ?item { ?item wikibase:statements 0 . [] schema:about ?item ; schema:isPartOf <https://de.wikipedia.org/> } }
          BIND( xsd:integer( substr(str(?item), 33)) as ?qid)
          wd:Q38074555 p:P1114 ?milestonep .
          ?milestonep ps:P1114 ?milestone 
          FILTER( ?milestone > ?qid ) 
        }
        GROUP BY ?milestonep
    }          
    ?milestonep pq:P585 ?date
}

Try it! Gets you a chart comparing QIDs to milestones in item creation. It suggests that items without claims with a sitelink to dewiki are either very old or recent. --- Jura 15:03, 29 November 2021 (UTC)

"It suggests that items without claims with a sitelink to dewiki are either very old or recent." YES, you are right. It is because I am working on these item and started at item numbers < 105000000. Now I reached number around 1500000.

Your proposal is a nice presentation but is not a solution for my problem. How can I restrict the query?--Fidoez (talk) 16:43, 29 November 2021 (UTC) 

SELECT * 
{
   ?item wikibase:statements 0 .
   FILTER( REGEX(str(?item), "^http://www.wikidata.org/entity/Q109\\d{6}") ) 
}
LIMIT 100

Try it! I don't think there is other than comparing with a qid you are already using. The filter above might be slightly quicker. --- Jura 17:36, 29 November 2021 (UTC)

OK, so far thanks for the comments related to QID.

But do you have any idea for schema:dateModified? --Fidoez (talk) 20:10, 29 November 2021 (UTC)

If you are looking for very new pages, you might get a query to work that checks first only recently modified pages (and then for QID) --- Jura 20:22, 29 November 2021 (UTC)
@Fidoez: The creation date for items is not stored in WDQS. You can access it by making an MWAPI call to www.wikidata.org, but that is only feasible for small groups of already selected items, not for a general search. As pointed out the QID number is ordered by creation date (but beware that merges can disturb the picture), but they AFAIK are not accessible indexed for searching in WDQS, so a FILTER using the numeric value is slow. --Dipsacus fullonum (talk) 15:32, 2 December 2021 (UTC)

List of German Research Foundation Applicants (with GEPRIS Historisch-ID) (1920 - 1945) with living descendents

I'd like to have a list of living descentants of applicants in the GEPRIS Historisch Database (compare GEPRIS Historisch person ID (P9044)). It would also be nice to have a genealogy graph of this cases. Nice to have would also be a network of all applicants with kinship ties to each other. My SPARQL knowledge is only basic, so I would be happy about some help!

Richirikken (talk) 09:05, 30 November 2021 (UTC) 

SELECT ?item ?itemLabel ?itemDescription ?value ?descendant ?descendantLabel ?descendantDescription ?dob
{
  ?item wdt:P9044 ?value .
  ?item wdt:P40+ ?descendant . 
  ?descendant wdt:P31 wd:Q5 . 
  FILTER NOT EXISTS { ?descendant p:P570 [] } 
  OPTIONAL { ?descendant wdt:P569 ?dob }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de,en". }
}

Try it! --- Jura 12:37, 30 November 2021 (UTC)

Many thanks Jura! That's helpful and intereseting. I didn't know that Olivia Newton-John is a nephew of Max Born!
Anyone interested in programming the second request (network of all applicants with kinship ties to each other)? An additional network question would be a graph with the doctoral advisory ties between all applicants.
Best regards!
Richirikken (talk) 15:24, 2 December 2021 (UTC)

Number of sitelinks based on Wikipedia article name (English)

Hi, I'm looking for a query that will return item, itemLabel and sitelinks count based on the WP article name as input. NB: match the article name as it is defined in the English language/Wikipedia.
Something like this:

SELECT item, itemLabel, count(sl) FROM MyDatasource WHERE articlename = 'Arthur Rudolph'

But then sparquelized ;)
According to the example the result should be:
Q70360|Arthur Rudolph|15

Thank you in advance.Mill 1 (talk) 09:53, 1 December 2021 (UTC)

@Mill 1:
SELECT ?item ?itemLabel ?article ?sitelink
WHERE 
{
  VALUES ?sitelink { "Arthur Rudolph"@en  } .
  ?article schema:about ?item ;
  schema:isPartOf <https://en.wikipedia.org/> ; 
  schema:name ?sitelink .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Tagishsimon (talk) 09:56, 1 December 2021 (UTC)
@Tagishsimon: That's fast! However, I need the number of site links of the item like it is here. See the example result Mill 1 (talk) 10:01, 1 December 2021 (UTC)


@Mill 1: This counts all sitelinks. If you just want sitelinks to language wikipedias, that can also be done. Let us know.
SELECT ?item ?itemLabel ?article ?sitelink ?sitelinks
WHERE 
{
  VALUES ?sitelink {"Arthur Rudolph"@en } .
  ?article schema:about ?item ;
           schema:isPartOf <https://en.wikipedia.org/> ; 
           schema:name ?sitelink .
  ?item wikibase:sitelinks ?sitelinks .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Tagishsimon (talk) 12:43, 1 December 2021 (UTC)
I just need to find out on how many wiki's an item is present. Regarding Arthur Rudolph: 15: ar, arz, de, en .. uk. Your result of 16 seems close enough although I cannot explain the difference of 1. Mill 1 (talk) 12:59, 1 December 2021 (UTC)


@Mill 1: Okay, so that's the difference between all sitelinks, and wikipedia only sitelinks. Arthur has a commons link. So here's the fix for that ... I'm taking it that you can tweak the query - e.g. by deleting columns that you do not want in the results. (You'll have to delete/amend them in the SELECT as well as the GROUP BY).
SELECT ?item ?itemLabel ?article ?sitelink ?all_sitelinks (count(?partOf) as ?wikipedia_sitelinks)
WHERE 
{
  VALUES ?sitelink {"Arthur Rudolph"@en } .
  ?article schema:about ?item ;
           schema:isPartOf <https://en.wikipedia.org/> ; 
           schema:name ?sitelink .
  ?item wikibase:sitelinks ?all_sitelinks . 
  
  ?article1 schema:about ?item ;
           schema:isPartOf ?partOf .
  ?partOf wikibase:wikiGroup "wikipedia"
             
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} GROUP BY ?item ?itemLabel ?article ?sitelink ?all_sitelinks
Try it!
--Tagishsimon (talk) 13:13, 1 December 2021 (UTC)
Ow, That's great! Thank you very much. Cheers, Mill 1 (talk) 14:51, 1 December 2021 (UTC)

How to get a list of people who have name in native language (P1559) in a particular language

I began studying to write a query at Wikidata and need your help. If it is available, how do you get a list of items of human (Q5) with their name in native language (P1559) filled in by Japanese (Q5287) AND whose country of citizenship (P27) is NOT including Japan (Q17)? It was too complicated for me to learn how-to from the introduction document etc. Do you have any good examples? --Doraemonplus (talk) 05:45, 27 November 2021 (UTC)

The problem is that there is currently no triple that gives merely the language of the P1559 value. Accordingly you need to evaluate every value with LANG().
?item wdt:P1559 ?nl . FILTER(lang(?nl)="ja")
An alternative could be to try to detect the language by querying for specific characters. Either approach will tend to time-out. Maybe LIMIT 10 can given you a few.
For Japanese, maybe name in kana (P1814) could do too.
If you want, I can do a more complete query for you. --- Jura 10:52, 27 November 2021 (UTC)
@Doraemonplus:. Here is a query proposal that you can experiment further with. Note that I have excluded a couple of more values for P27, not just Japan.
SELECT ?item ?p1559 ?p27Label {
  ?item wdt:P1559 ?p1559 .
  FILTER(LANG(?p1559)='ja')
  OPTIONAL { ?item wdt:P27 ?p27 }
  FILTER(BOUND(?p27))  #remove this line if items without any P27 should be included
  FILTER NOT EXISTS {?item wdt:P27 wd:Q17 }
  # the following lines excludes some other "Japan like" values for P27, exclude or add more to fit your needs
  FILTER NOT EXISTS {?item wdt:P27 wd:Q188712 }
  FILTER NOT EXISTS {?item wdt:P27 wd:Q28025 }
  FILTER NOT EXISTS {?item wdt:P27 wd:Q205662 }
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Larske (talk) 11:03, 27 November 2021 (UTC)
@Jura1, Larske: Thank you for your advice. The results of query you showed me will also solve my question. OK then, I have one more request. Do you know whether there is a way to find the human (Q5) where their language for name in native language (P1559) is NOT included in its native language (P103)? I’m thinking of correcting inconsistency between them about Japanese people. --Doraemonplus (talk) 06:11, 28 November 2021 (UTC)
@Doraemonplus: Try this query.
SELECT ?item ?p1559 ?p1559_lang ?p103_lang {
  ?item wdt:P31 wd:Q5 . # remove this line if you don't want to limit to Q5
  ?item wdt:P1559 ?p1559 .
  BIND(LANG(?p1559) AS ?p1559_lang)
  ?item wdt:P27 wd:Q17 .  #remove this line if you don't want to limit to Japan
  ?item wdt:P103 ?p103 .
  ?p103 wdt:P424 ?p103_lang .
  FILTER NOT EXISTS {?item wdt:P103/wdt:P424 ?p1559_lang }
  FILTER(!CONTAINS(?p1559_lang,CONCAT(?p103_lang,'-'))) # remove this line if you want to see ?p1559_lang like "ja-hani" and "ja-hira" and "zh-hant"
}
ORDER BY ?lang ?p1559_lang
Try it!
--Larske (talk) 07:41, 28 November 2021 (UTC)
@Larske: Excellent! It surprised me that it can tell the difference among type of characters as well as of languages if it is designated. All right. Let me ask you last two more questions for my study.
  1. How to find human (Q5) whose country of citizenship (P27) includes Japan (Q17) AND with name in native language (P1559) given in any languages except Japanese (Q5287).
  2. How to find human (Q5) to whom is given Japan (Q17) as country of citizenship (P27) OR to whom is given a name in Japanese (Q5287) as name in native language (P1559).
--Doraemonplus (talk) 03:49, 29 November 2021 (UTC)

@Jura1, Larske: Experimented another query below for myself. 

SELECT DISTINCT ?item ?itemLabel ?sitelink WHERE
{
 ?item wdt:P31 wd:Q5.
 ?item wdt:P1814 ?p1814.
  FILTER NOT EXISTS
  {
    {
     ?item wdt:P1477 ?p1477.
     FILTER((LANG(?p1477)) = "ja")
    }
    UNION
    {
     ?item wdt:P1559 ?p1559.
     FILTER((LANG(?p1559)) = "ja")
    }
  }
 ?sitelink schema:about ?item;
           schema:isPartOf <https://ja.wikipedia.org/>.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

Thank you so much. --Doraemonplus (talk) 05:00, 4 December 2021 (UTC)

STRSTARTS (URL)

Hello,

I'm looking for the easy way to research in value type URL with something like STRSTARTS. Ex: FILTER(STRSTARTS(?ext, 'https://collections.si.edu/')

Thanks for your help!

--SAPA bdc (talk) 10:30, 3 December 2021 (UTC)

@SAPA bdc: I don't understand what you want to do. If ?ext isn't of type string, you can convert with the STR function. If you want to search for certain internet domains used in URLs, an MWAPI call using exturlusage is often the most effective way (see an example at Wikidata:SPARQL query service/query optimization#A query that has difficulties), but more information is needed for an answer. --Dipsacus fullonum (talk) 11:02, 3 December 2021 (UTC)
Thanks you @Dipsacus fullonum:! The STR function was what I was looking for. --SAPA bdc (talk) 20:23, 3 December 2021 (UTC)

List unique value violations for a particular P131

To solve duplicates I need to list elements with the same value for the INE municipality code (P772) property. I know the property violations list but I need to create separate queries for different located in the administrative territorial entity (P131) values (for example Galicia (Q3908) ). Thanks in advance —Ismael Olea (talk) 14:31, 6 December 2021 (UTC)

@Olea: Like this? 
SELECT ?item ?itemLabel ?INE1 ?INE2 ?locatedLabel
WHERE
{
  ?item wdt:P772 ?INE1 .
  ?item wdt:P772 ?INE2 .
  FILTER (?INE1 < ?INE2)
  ?item wdt:P131 ?located .
  ?item wdt:P131* wd:Q3908 . # Galicia only
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Dipsacus fullonum (talk) 16:11, 6 December 2021 (UTC)
Well, this query can be useful tool but it's not what I'm looking for.
The idea is to get a list/table of all the elements with the same INE municipality code (P772) value per P131. —Ismael Olea (talk) 18:46, 6 December 2021 (UTC)
@Olea: Check if the following query gives the expected result.
SELECT DISTINCT ?p772 ?item1 ?item1Label ?item2 ?item2Label {
	VALUES ?p131 {wd:Q3908}
    ?item1 wdt:P772 ?p772; wdt:P131* ?p131a .
	?item2 wdt:P772 ?p772; wdt:P131* ?p131b .
    FILTER(?p131a=?p131 || ?p131b=?p131)
	FILTER(STR(?item1)<STR(?item2)) .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "es" } .
}
ORDER BY ?p772
Try it!
--Larske (talk) 20:10, 6 December 2021 (UTC)
Looks like exactly what I'm looking for.
Thanks a lot @Larske and @Dipsacus fullonum! —Ismael Olea (talk) 10:27, 7 December 2021 (UTC)
@Larske: could be possible to output the elements sorted in columns? one column for the older and the other for the newer. This is interesting when merging using QS. —Ismael Olea (talk) 19:04, 7 December 2021 (UTC)
@Olea: The following query puts the item with the lower Q-number in the column older and the item with the higher Q-number in the column newer:
SELECT DISTINCT ?p772 ?older ?olderLabel ?newer ?newerLabel {
	VALUES ?p131 {wd:Q3908}
    ?item1 wdt:P772 ?p772; wdt:P131* ?p131a .
	?item2 wdt:P772 ?p772; wdt:P131* ?p131b .
    FILTER(?p131a=?p131 || ?p131b=?p131)
	FILTER(STR(?item1)<STR(?item2)) .
    BIND(xsd:integer(STRAFTER(STR(?item1),'/Q')) AS ?q1)
    BIND(xsd:integer(STRAFTER(STR(?item2),'/Q')) AS ?q2)
    BIND(IF(?q1<?q2,?item1,?item2) AS ?older)
    BIND(IF(?q1<?q2,?item2,?item1) AS ?newer)
	SERVICE wikibase:label { bd:serviceParam wikibase:language "es" } .
}
ORDER BY ?p772
Try it!
--Larske (talk) 19:31, 7 December 2021 (UTC)
@Larske : awesome! Now I've cleaned some hundreds of duplicated elements. Thanks a lot! —Ismael Olea (talk) 22:40, 7 December 2021 (UTC)
@Olea this should group the Krbot report. Not sure when the next update is though. --- Jura 23:30, 7 December 2021 (UTC)
Awesome! —Ismael Olea (talk) 18:20, 8 December 2021 (UTC)

instance of (P31) scholarly article (Q13442814) with no description in English

Requesting a query that lists instance of (P31) scholarly article (Q13442814) that have no English description. Thank you. Trilotat (talk) 01:34, 11 December 2021 (UTC)

Reminds me of Wikidata:Bot_requests/Archive/2020/03#Add_description_to_items_about_articles --- Jura 12:29, 11 December 2021 (UTC)
I think there are too many instances of scholarly article (Q13442814) to make the list in WDQS, but you can find them using WikibaseCirrusSearch searching for haswbstatement:P31=Q13442814 -hasdescription:en. --Dipsacus fullonum (talk) 15:25, 11 December 2021 (UTC)
The QLever search engine demo available at https://qlever.cs.uni-freiburg.de/ can perform the query incredibly fast (https://qlever.cs.uni-freiburg.de/wikidata/GV1c1x), though it is currently using a copy of Wikidata several months out of date and there appears to be a limit of 100,000 items when downloading results.
--Quesotiotyo (talk) 07:13, 12 December 2021 (UTC)

Straightening out a bowl of spaghetti on subway lines

Hello, given that phenomenon : https://www.wikidata.org/wiki/Q1326180#P197 I'd like a query on London Underground station (Q14562709) and get those that have (one P197) BUT (with mixed P81 and P5051 for that P197). Note that multiples P5051 are OK, but mixed P81 should be written on different claim, one claim per P197/P81. Thanks ! Bouzinac 💬●✒️●💛 21:48, 10 December 2021 (UTC)

"mixed P81 should be written on different claim, one claim per P197/P81" Why? --Tagishsimon (talk) 22:12, 10 December 2021 (UTC)
I think the problem is that you cannot see which line have which destination. Anyway this query should find the cases: 
SELECT DISTINCT ?item ?itemLabel ?next ?nextLabel ?next_stm
{
  ?item wdt:P31 wd:Q14562709 .
  ?item p:P197 ?next_stm .
  ?next_stm ps:P197 ?next .
  ?next_stm pq:P81 ?line1 .
  ?next_stm pq:P81 ?line2 .
  FILTER (STR(?line1) < STR(?line2))
  ?next_stm pq:P5051 ?towards1 .
  ?next_stm pq:P5051 ?towards2 .
  FILTER (STR(?towards1) < STR(?towards2))  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!
--Dipsacus fullonum (talk) 23:29, 10 December 2021 (UTC)
In the https://www.wikidata.org/wiki/Q1326180#P197 case (and others), telling that "next stop from Plaistow tube station (Q1326180) is West Ham station (Q801601) towards Hammersmith tube station (Q1762419) with service District line (Q211265)" is not true. Okay, you can get rules to check but better to separate and help consistency, and help simplify queries, don't u think? Bouzinac 💬●✒️●💛 21:08, 12 December 2021 (UTC)
cf https://fr.wikipedia.org/wiki/Utilisateur:Bouzinac/BrouillonLondres Bouzinac 💬●✒️●💛 21:22, 12 December 2021 (UTC)

Help with a query of physical geography elements of a region

Hi;

I need a query of physical geography (Q52107) elements of a region (Almería Province (Q81802)). I prepared this: 

SELECT DISTINCT ?item WHERE {
    ?item (wdt:P31/(wdt:P279*)) wd:Q271669;
      wdt:P131 wd:Q81802.
}
Try it!

It works but I'm missing elements like Julio Verne (Q107685207). So I've changed wdt:P131 for wdt:P131* 

SELECT DISTINCT ?item WHERE {
    ?item (wdt:P31/(wdt:P279*)) wd:Q271669;
      wdt:P131* wd:Q81802.
}
Try it!

But it doesn't work due to timeout.

As I want to use it in a Listeria query I prepared a test in eswiki user page but Listeria can't create it

Last line: ERROR: run_sparql_query: String("error decoding response body: key must be a string at line 349 column 9")

I really don't know if this message is related with the WDQS timeout or something I made wrong (and I rechecked the template several times).

So:

  • if the query is correct, how can I make Listeria run it?
  • if not, how to fix it?

Thanks in advance.

PS: Not strictly related with the query request but I guess using landform (Q271669) is not enough to create a complete geography list. —Ismael Olea (talk) 20:19, 14 December 2021 (UTC)

Hi Ismael Olea . The query is correct, but runs slowly. Using a technique described at Wikidata:SPARQL query service/query optimization#Inverse property paths you can speed up the query and avoid the timeout by reversing the property path like this: 
SELECT DISTINCT ?item WHERE {
    ?item wdt:P131* wd:Q81802.
    wd:Q271669 ^wdt:P279*/^wdt:P31 ?item.
}
Try it!
I cannot answer the PS. Best regards, --Dipsacus fullonum (talk) 20:39, 14 December 2021 (UTC)
As to the PS, I suspect geographic entity (Q27096213) might be nearer the mark than landform (Q271669) ... not least, it seems like the first point which includes seas as well as terra firma. --Tagishsimon (talk) 21:05, 14 December 2021 (UTC)
Interesting but seems it has thousands of subclasses related with man-made elements instead of just natural terrain features, but thanks :-) —Ismael Olea (talk) 00:04, 15 December 2021 (UTC)

Mountain range names in national / local languages

Hi!

I am trying to extract the names of all mountain ranges in all languages (not only official, but especially local / native / indigenous) spoken in the country where each mountain range occurs. Property: “language used (P2936)”?

For example, the European Alps (Alps) occur in seven countries (Austria, France, Germany, Italy, Liechtenstein, Slovenia, Switzerland). Additionally, according to Wikidata they also occur in Monaco. The ideal output of the query would be something like this:

Mountain Range | Mountain Range name EN | Country | Name translation | Language | Wikimedia Language code (P424)
Q1286 | Alps | Austria| Alpen | German | de
Q1286 | Alps | France | Alpes | French | fr
Q1286 | Alps | Germany| Alpen | German | de
Q1286 | Alps | Italy | Alpi | Italian | it
Q1286 | Alps | Italy| Alpen | German | de
Q1286 | Alps | Liechtenstein| Alpen | German | de
Q1286 | Alps | Slovenia | Alpen | Slovenian | de
Q1286 | Alps | Slovenia | Alpi | Italian | it
Q1286 | Alps | Slovenia | Alpok | Hungarian | de
Q1286 | Alps | Switzerland | Alpes | French | fr
Q1286 | Alps | Switzerland | Alpi | Italian | it
Q1286 | Alps | Switzerland | Alpen | German | de
Q1286 | Alps | Switzerland | Alps | Romansh | rm
+ the same for the many other “languages used” in the Alpine countries
Q1285 | Apennine Mountains | Italy | Appennini | Italian | it
Q1285 | Apennine Mountains | Italy | Apennin | German | de
+ the same for the many other “languages used” in Italy
+ and so forth, for all the mountain ranges in Wikidata

“Mountain Range name EN”: is optional and should not limit the results. If it does not exist the translation in other languages should still appear in the output.

Cheers!

MonedulaWiki (talk) 17:36, 14 December 2021 (UTC)

@MonedulaWiki: There's a bit of apples & pears going on with your request ... what the mountain is called, in any given language, is one thing ... which language is spoken in which countries another entirely. So here's a start, showing just the language label values. If you want to conflate that with country, so that we do get Germany and lang=DE on one row, and Italy and lang=IT on the next, that can be done within limits but probably starts to break down as we come across countries with multiple languages. So (for all I know) it might work in parts of Europe. Less likely to work in e.g. India. Finally, it's not clear if, for a given mountain / range, you are interested in only the countries (and their language(s)) in which the mountain / range is found, or if you want all language translations.
When we've established exactly what you want, we can roll the query out to all mountains / ranges / whatevers.
#title:All languages known to wikidata
SELECT ?item ?itemLabel ?label ?lang
WHERE 
{
  VALUES ?item {wd:Q1286}
  OPTIONAL {?item rdfs:label ?itemLabel. filter(lang(?itemLabel)="en") } 
  OPTIONAL {?item rdfs:label ?label.
  BIND(lang(?label) as ?lang) }
}
All languages known to wikidata 
#title: Official langagues of the country in which the mountin is found
SELECT ?item ?itemLabel ?countryLabel ?label ?lang
WHERE 
{
  VALUES ?item {wd:Q1286}
  OPTIONAL {?item rdfs:label ?itemLabel. filter(lang(?itemLabel)="en") } 
  ?item wdt:P17 ?country . 
  ?country wdt:P37/wdt:P424 ?langcode .
  OPTIONAL {?item rdfs:label ?label.
  BIND(lang(?label) as ?lang) 
  filter(str(?lang) = str(?langcode)) }
  OPTIONAL {?country rdfs:label ?countryLabel . filter(lang(?countryLabel)="en") } 
}
Official langagues of the country in which the mountin is found
--Tagishsimon (talk) 18:41, 14 December 2021 (UTC)
There are 43177 mountain ranges in Wikidata so this is a big task, and it may be impossible to do within 60 seconds to avoid timeout. I think I would do it in subtasks, probably with each as a named subquery:
  1. find all distinct mountain ranges
  2. find all distinct countries with the mountain ranges
  3. find all languages spoken in each country with the mountain ranges
  4. find the relevant languages for each mountain range
  5. find the names for each mountain range in the relevant languages
But I wont actually make the query. It could take several hours for me to do, possibly with no result or a list so big that nobody will read it. --Dipsacus fullonum (talk) 19:45, 14 December 2021 (UTC)
Hi @Tagishsimon, Many thanks for having taken the time to consider my request / problem and offering some thoughts and paths towards its solution. MonedulaWiki (talk) 08:46, 15 December 2021 (UTC)
Hello @MonedulaWiki: and welcome! This query should provide what you are looking for:
SELECT DISTINCT ?item ?itemLabel ?countryLabel ?itemnativeLabel ?langusedLabel ?langcode

WITH {
  SELECT DISTINCT ?item WHERE {
  ?item wdt:P31/wdt:P279?/wdt:P279? wd:Q46831 .
  }
} AS %results1 

WITH {
  SELECT DISTINCT ?item ?country ?itemnativeLabel ?langused ?langcode WHERE {
  INCLUDE %results1 .
  ?item wdt:P17 ?country .
  ?country (wdt:P2936|wdt:P37) ?langused .
  ?langused wdt:P424 ?langcode .
  ?item rdfs:label ?itemnativeLabel .
  FILTER(LANG(?itemnativeLabel) = ?langcode)
  }
} AS %results2 

WHERE {
  INCLUDE %results2 .
  BIND(xsd:integer(SUBSTR(STR(?item), 33)) AS ?num)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  } ORDER BY ?num ?itemLabel ?countryLabel ?langusedLabel ?langcode
Try it!
If you need any further assistance please do not hesitate to ask.
--Quesotiotyo (talk) 23:13, 14 December 2021 (UTC)
Nice. I think there are some issues with the definition of mountain range in Wikidata. I noted that ridge (Q740445) with description "geological feature consisting of a chain of mountains or hills that form a continuous elevated crest for some distance" is a subclass of mountain chain (Q2624046) which is subclass of mountain range (Q46831). This means that, according to Wikidata, hills in a ridge are mountains. The list therefore contains e.g. Fladså Banker (Q23748303), a low ridge with a height of 28 meters above sea level in a low landscape in Denmark. It is a protected natural area, but can hardly be seriously called a mountain range. --Dipsacus fullonum (talk) 00:35, 15 December 2021 (UTC)
Hi @Quesotiotyo, this is absolutely fantastic! Thank you very much for the quick and comprehensive reply. I will have a look at the results more closely, but at first sight it looks like it exactly produced what I was hoping for. I had perhaps hoped that the query would return more local / indigenous names, but that is perhaps because wikidata does not contain that information yet. For example, names in Quechua or Aymara for Bolivian / Peruvian mountain ranges are still few. The query therefore is a good tool to identify gaps and see how to fill them. All best wishes, MonedulaWiki (talk) 08:42, 15 December 2021 (UTC)
@MonedulaWiki: Yes, Wikidata doesn't include labels in many local / indigenous names yet. You would get a few more results if the query also used native label (P1705). Here is a version using P1705 instead of item labels. I cannot get both in 60 seconds: 
SELECT DISTINCT ?item ?itemLabel ?countryLabel ?itemnativeLabel ?langusedLabel ?langcode

WITH {
  SELECT DISTINCT ?item WHERE {
  ?item wdt:P31/wdt:P279* wd:Q46831 .
  }
} AS %results1 

WITH {
  SELECT DISTINCT ?item ?itemnativeLabel ?langused ?langcode WHERE {
  INCLUDE %results1 .
  ?item wdt:P1705 ?itemnativeLabel .
  BIND (LANG(?itemnativeLabel) AS ?langcode)
  ?langused wdt:P424 ?langcode .
  wd:Q34770 ^wdt:P279*/^wdt:P31 ?langused .
  }
} AS %results2

WHERE {
  INCLUDE %results2
  BIND(xsd:integer(SUBSTR(STR(?item), 33)) AS ?num)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  } ORDER BY ?num ?itemLabel ?countryLabel ?langusedLabel ?langcode
Try it!
--Dipsacus fullonum (talk) 10:11, 15 December 2021 (UTC)
Hi @Dipsacus fullonum, many thanks for the comments and this useful extension of the query! MonedulaWiki (talk) 13:37, 15 December 2021 (UTC)

How to find physicists with many articles in different languages (at least 20)

Hello.

I was wondering if there is any way to search for persons (in this case, physicists) who have at least 20 linked articles in different languages (I don't really care about which languages, I am referring to the number of them).

Is there any way to search for this?

@YnitedPR: Yes:
SELECT ?item ?itemLabel (count(distinct ?wikipedia) as ?count)
WHERE 
{
  ?item wikibase:sitelinks ?sitelinks .      # There are linked to ...
  FILTER (?sitelinks >= 20)                  # ... 20 or more wikis (Wikipedias or other)
  ?item wdt:P106/wdt:P279* wd:Q169470.       # they're some sort of physicist
  ?article schema:about ?item ;              # they have a sitelink
  schema:isPartOf ?wikipedia .               # which is part of ...  
  ?wikipedia wikibase:wikiGroup "wikipedia"  # ... one or other wikipedia
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
} group by ?item ?itemLabel having (?count >19)
Try it!
--Tagishsimon (talk) 23:47, 16 December 2021 (UTC)
I inserted a filter for items with 20+ sitelinks into Tagishsimon's fine query. It will cut off potential items with not enough sitelinks to be linked to 20 Wikipedias without using time to test them more. That reduced the running time from 40+ seconds to 20 seconds for me. --Dipsacus fullonum (talk) 00:06, 17 December 2021 (UTC)
Good call. --Tagishsimon (talk) 00:16, 17 December 2021 (UTC)
Thank you!
And is there any way to do the same search but only for physicists who are currently alive? YnitedPR (talk) 01:35, 17 December 2021 (UTC)
@YnitedPR: Sure, albeit you'd need to make some judgements on how aliveness is represented on WD. Something like:
SELECT ?item ?itemLabel (count(distinct ?wikipedia) as ?count)
WHERE 
{
  ?item wikibase:sitelinks ?sitelinks .      # There are linked to ...
  FILTER (?sitelinks >= 20)                  # ... 20 or more wikis (Wikipedias or other)
  ?item wdt:P106/wdt:P279* wd:Q169470.       # they're some sort of physicist
  ?item wdt:P569 ?dateOfBirth.               # they have a date of birth
  hint:Prior hint:rangeSafe true.            # (go faster, little query; all dates are dates)
  FILTER("1911-00-00"^^xsd:dateTime <= ?dateOfBirth) # DoB is after 1910
  FILTER NOT EXISTS {?item wdt:P570 [] .}    # WD doesn't think they're dead - no date of death
  ?article schema:about ?item ;              # they have a sitelink
  schema:isPartOf ?wikipedia .               # which is part of ...  
  ?wikipedia wikibase:wikiGroup "wikipedia"  # ... one or other wikipedia
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
} group by ?item ?itemLabel having (?count >19)
Try it!

Query returning pages with non-empty talk pages

Hi,

I am looking for an efficient way to list items with non-empty talk pages (generic).

I am mainly interested in taxa, so my request would more or less look like: 

#title: Which are the taxa present in Wikidata which have been discussed? (example limited to 1000 results)

SELECT DISTINCT?taxon ?taxon_name WHERE {

  ?taxon wdt:P225 ?taxon_name.                   # get the taxon scientific name

  # IMPLEMENT STEP TO FILTER TAXA WITH DISCUSSION

}

LIMIT 1000
Which are the taxa present in Wikidata which have been discussed? (example limited to 1000 results)

AdrianoRutz (talk) 14:36, 15 December 2021 (UTC)

There is no information about talk pages in WDQS. You could make a query get a list of all item talk pages by using the MWAPI service and then check the corresponding item pages, but the MWAPI service has a hard limit of max 10,000 results so the list will be incomplete. There may be other tools which are better this task. --Dipsacus fullonum (talk) 15:13, 15 December 2021 (UTC)
@Dipsacus fullonum Thank you, I don't care if the list is incomplete, would be great already. How would you do it through MWAPI? AdrianoRutz (talk) 15:29, 15 December 2021 (UTC)
@AdrianoRutz: Here you are. The MWAPI call fetches 10,000 out of 35.000+ talk pages so most results are missing: 
SELECT ?taxon ?taxon_name
WHERE
{
  OPTIONAL # Optional is used as means to have this block executed first
  {
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:api "Generator" .
      bd:serviceParam wikibase:endpoint "www.wikidata.org" .
      bd:serviceParam mwapi:generator "allpages" .
      bd:serviceParam mwapi:gapnamespace "1" .
      bd:serviceParam mwapi:gaplimit "max" .
      ?talk wikibase:apiOutput mwapi:title  .
    }
    BIND(URI(CONCAT("http://www.wikidata.org/entity/", SUBSTR(?talk, 6))) AS ?taxon)
  }
  ?taxon wdt:P225 ?taxon_name .  
}
Try it!
--Dipsacus fullonum (talk) 16:23, 15 December 2021 (UTC)
Exactly what I was looking for! Thank you very much! AdrianoRutz (talk) 16:33, 15 December 2021 (UTC)
@AdrianoRutz, Dipsacus fullonum: this is usually a job for Petscan. The issue here is that "any item with P225" (3.5M items) is a bit excessive, so it does not work in this particular case.
However, one can query as in petscan:20892129 (Talk pages of items that have position held (P39): Federal Chancellor of Germany (Q4970706)). Existing talk pages have a "Page ID" in the results set, non-existing ones do not. —MisterSynergy (talk) 17:07, 15 December 2021 (UTC)
@MisterSynergy Oh, very nice indeed! Added some lines to limit to some taxa only and works like a charm! Thank you AdrianoRutz (talk) 12:10, 17 December 2021 (UTC)

Complementary spouses

Made a statement to find spouses that do not have a complementary spouse statement. It works for the most part, but I'm puzzled why it lists spouses that does have a complementary spouse statement, although with qualifiers and jazz. What's going on here?

PS. I already added the missing spouse statements, meaning this query is supposed to return no items now. 

SELECT DISTINCT ?item ?itemLabel ?spouse ?spouseLabel
WHERE {
  ?item wdt:P31 wd:Q5;
        wdt:P27 wd:Q20;
        wdt:P26 ?spouse.
  MINUS { ?spouse wdt:P26 ?item }
  FILTER(!wikibase:isSomeValue(?spouse))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "nb,en". }
}
ORDER BY (?item)
Try it!

--Infrastruktur (T | C) 12:23, 19 December 2021 (UTC)

wdt:P26 just gets you "best" rank (preferred rank if there is, otherwise normal rank).
try p:P26/ps:P26 instead --- Jura 12:36, 19 December 2021 (UTC)

All places in the Great Catalan Encyclopedia that are not part of the Catalan Countries

Hello! I want to search for all the items in the Great Catalan Encyclopedia that are not part or their cultural area. The query is done, but it seems heavy it can't be run. Is there a way to make it more economical? 

SELECT (COUNT(?item) as ?count) ?herrialdeaLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item wdt:P17 ?herrialdea.
  ?item wdt:P1296 ?cat
  MINUS{?item wdt:P131*/wdt:P706* wd:Q5765.} 
  MINUS{?item wdt:P131*/wdt:P706* wd:Q5705.} 
  MINUS {?item wdt:P131*/wdt:P706* wd:Q5720.} 
  MINUS {?item wdt:P131*/wdt:P706* wd:Q228.} 
  MINUS {?item wdt:P131*/wdt:P706* wd:Q166282.} 
  MINUS {?item wdt:P131*/wdt:P706* wd:Q15580.}
}
GROUP by ?herrialdeaLabel
ORDER by DESC(?count)
Try it!

Thanks! -Theklan (talk) 21:02, 20 December 2021 (UTC)

@Theklan: I only suspect that wdt:P131/wdt:P706* is probably what you need, rather than wdt:P131*/wdt:P706* ... I could be wrong. This takes runtime to about 38 seconds.
SELECT (COUNT(?item) as ?count) ?herrialdeaLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item wdt:P1296 ?cat . hint:Prior hint:runFirst true.
  ?item wdt:P17 ?herrialdea .
  MINUS{?item wdt:P131/wdt:P706* wd:Q5765. hint:Prior hint:gearing "forward" . } 
  MINUS{?item wdt:P131/wdt:P706* wd:Q5705. hint:Prior hint:gearing "forward" . } 
  MINUS {?item wdt:P131/wdt:P706* wd:Q5720. hint:Prior hint:gearing "forward" . } 
  MINUS {?item wdt:P131/wdt:P706* wd:Q228. hint:Prior hint:gearing "forward" . } 
  MINUS {?item wdt:P131/wdt:P706* wd:Q166282. hint:Prior hint:gearing "forward" . } 
  MINUS {?item wdt:P131/wdt:P706* wd:Q15580. hint:Prior hint:gearing "forward" . }
}
GROUP by ?herrialdeaLabel
ORDER by DESC(?count)
Try it!
--Tagishsimon (talk) 21:53, 20 December 2021 (UTC)
It got out of time, too. Actually, I think that only with P131* would work also. I'll give a try to that. Theklan (talk) 22:02, 20 December 2021 (UTC)
Oh! It worked (59 secs) in the secont try! Thanks! Theklan (talk) 22:02, 20 December 2021 (UTC)

Searching only one-way railway stations

Hello, given that first query, https://w.wiki/4avz , how to add a filter that would count adjacent station (P197) and filter out those that have more than one P197 ? Counting only one P197 is not sufficient but would help filter. Eg Sheringham railway station (Q248831) would be a false positive (trains can go up and forth whilst I'm looking only stations where you can go from A then B then C, but not C then B then A). Bouzinac 💬●✒️●💛 11:45, 22 December 2021 (UTC)

Filtering out those that have more than one P197: 
SELECT ?item ?label ?_image WHERE {
  ?item wdt:P1103 +1. .
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en,de" . 
    ?item rdfs:label ?label
  }
  ?item wdt:P197 ?adjacent_station .
  MINUS { ?item (wdt:P576|wdt:P582|wdt:P3999) ?dispar. }#on ne veut pas les stations disparues
  MINUS { ?item wdt:P5817|wdt:P5816 ?interdit.
    VALUES ?interdit {  wd:Q811683  wd:Q63065035 wd:Q12377751 wd:Q97317113 wd:Q55653430 wd:Q30108381 wd:Q55570340 wd:Q11639308 wd:Q104664889
    }
  }#on ne veut pas les cas particuliers, en construction etc
}
GROUP BY ?item ?label ?_image
HAVING (COUNT(?adjacent_station) = 1)
Try it!
Also filtering those where with a P197 from a adjacent station back to the first station: 
SELECT ?item ?label ?_image WHERE {
  ?item wdt:P1103 +1. .
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr,en,de" . 
    ?item rdfs:label ?label
  }
  ?item wdt:P197 ?adjacent_station .
  MINUS { ?adjacent_station wdt:P197 ?item. }
  MINUS { ?item (wdt:P576|wdt:P582|wdt:P3999) ?dispar. }#on ne veut pas les stations disparues
  MINUS { ?item wdt:P5817|wdt:P5816 ?interdit.
    VALUES ?interdit {  wd:Q811683  wd:Q63065035 wd:Q12377751 wd:Q97317113 wd:Q55653430 wd:Q30108381 wd:Q55570340 wd:Q11639308 wd:Q104664889
    }
  }#on ne veut pas les cas particuliers, en construction etc
}
GROUP BY ?item ?label ?_image
HAVING (COUNT(?adjacent_station) = 1)
Try it!
--Dipsacus fullonum (talk) 15:28, 22 December 2021 (UTC)

How to query and filter among ranks

Help:Ranking had an empty section on this. I filled it with a few sample queries. Please expand/correct as it may seem useful to you. --- Jura 00:19, 21 December 2021 (UTC)

Nice. I'm curious; efficiency-wise how does your 'not deprecated' query fare against something like this given a big working set?
SELECT * WHERE {
  VALUES ?truthy {
    wikibase:PreferredRank
    wikibase:NormalRank
  }
  wd:Q692 p:P569 ?st.
  ?st ps:P569 ?value.
  ?st wikibase:rank ?truthy. # <- more or less efficient?
  #MINUS { ?st wikibase:rank wikibase:DeprecatedRank. }
}
Try it!

--Infrastruktur (talk) 12:51, 21 December 2021 (UTC)

Also, there's no explanation about what "a" is. I've been using "?st rdf:type wikibase:BestRank." to test for best rank. --Infrastruktur (talk) 12:59, 21 December 2021 (UTC)
Thanks for your feedback.
Using values is probably more efficient. I had been wondering if I should add that too.
I would be interested in a good explanation about "a" too. --- Jura 13:12, 21 December 2021 (UTC)
You can see in the SPARQL 1.1 Query Language definition at https://www.w3.org/TR/sparql11-query/ section 4.2.4 that a is a case-sensitive keyword that can be used as a predicate instead of the IRI http://www.w3.org/1999/02/22-rdf-syntax-ns#type (which is normally shortened using the rdf: prefix til rdf:type). So in other words "a" is an alternative spelling for "rdf:type". It makes no difference which is used. --Dipsacus fullonum (talk) 22:25, 21 December 2021 (UTC)
  • Infrastruktur had added it to Help:Ranking in the meantime. Thanks to both of you. Here a query to find its values (Without the main usecase: no value statements).
SELECT *
WITH
{
  SELECT DISTINCT ?rdftype (COUNT(*) as ?count)
  WHERE
  {
    _:b2 rdf:type ?rdftype
  }
  GROUP BY ?rdftype
} as %types
WHERE
{
  INCLUDE %types
  MINUS { _:b7 wikibase:novalue ?rdftype }
}
ORDER BY DESC(?count)
Try it!

Wonder if the 80 million schema:Article triples have any use. --- Jura 13:03, 22 December 2021 (UTC)

Added that to Wikidata:Report_a_technical_problem/WDQS_and_Search#delete_triples_from_WDQS:_?a_rdf:type_schema:Article. --- Jura 20:08, 22 December 2021 (UTC)

This forum is a neverending source of inspiration and knowledge, a warm thanks to all its contributors. <3 --Infrastruktur (talk) 13:30, 23 December 2021 (UTC)

list of all games on wikidata

Hello, I am trying to create a dataset with all the videos games on wikidata but I am stuck. My query returns only 3 results, Can someone help? I am new to SPARQL and confused about what I am doing wrong here. 

SELECT DISTINCT ?GameLabel ?GenreLabel ?CountryLabel ?PublicationDateLabel ?DeveloperLabel ?SoftwareEngineLabel ?GameModeLabel ?BusinessModeLabel ?AgeRatingLabel ?UsesLabel
where{
   ?Game wdt:P279 wd:Q7889.
   ?Game wdt:P136 ?Genre.
  ?Game wdt:P495 ?Country. 
  optional {
  ?Game wdt:P577 ?PublicationDate. 
  ?Game wdt:P178 ?Developer. 
  ?Game wdt:P408 ?SoftwareEngine. 
  ?Game wdt:P404 ?GameMode. 
  ?Game wdt:P7936 ?BusinessMode. 
  ?Game wdt:P9897 ?AgeRating. 
  ?Game wdt:P2283 ?Uses.
    }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en" .
  }
}
Try it!

Thank you,  – The preceding unsigned comment was added by ? (talk • contribs).

Hi. I see at least 2 problems with the query:
  1. ?Game wdt:P279 wd:Q7889. Here you asks for subclasses of videogames. I think you mean to ask for instances of (P31) video game or a subclass of video game. You do that with ?Game wdt:P31 / wdt:P279* wd:Q7889. or (same meaning, but faster) wd:Q7889 ^wdt:P279* / ^wdt:P31 ?Game . (The * after wdt:P279 in both variants means that wdt:P279 is invoked zero or more times with no upper limit.)
  2. The optional block contains 7 triples. All of these must be present for the block to be included in the results. So if an item has values for only 1 to 6 of the variables ?PublicationDate, ?Developer, ?SoftwareEngine, ?GameMode, ?BusinessMode, ?AgeRating and ?Uses, none of them will be in the results. If you want any results even if all not is present, you have to split it up into 7 independent optional blocks. --Dipsacus fullonum (talk) 14:51, 23 December 2021 (UTC)
--Dipsacus fullonum (talk) 14:51, 23 December 2021 (UTC)

Metadata about every human in English Wikipedia

Hi everyone. I am new here so please if I am doing something wrong.

As far as I can know, there are around 1.800.000 biographies of people in the English Wikipedia (355.000 women, 1.500.000 men and 1.500 with other gender or no-gender).

What I want is some metadata of every biography/Person(P31:Q5) (item, itemLabel, dateOfBirth, dateOfDeath, gender, occupation) in the English Wikipedia. I have been writing some SPARQL query but it has not been possible for me.

Also, I do not know (because I failed in the first step) if the timeout could be a problem if I want to extract data from 1.800.000 entries.

Thanks in advance.

Hi. I don't think that it is possible using WDQS to make a query returning a result for every biography in the English Wikipedia in under 60 seconds which is the limit for timeout. It may be possible to make several queries each giving a subset of the result, and then combine the subsets afterwards. --Dipsacus fullonum (talk) 14:55, 23 December 2021 (UTC)
Thank you so much for your fast answer.
As you mentioned, the limit for timeout it is 60 seconds. Because of this I am wondering: Is there any REST API (or something similar) that can solve this timeout 'limitation'? I have seen two tools (WikidataQueryServiceR and WikidataR, both in R) but they use the WDQS, so I imagine that they both have similar timeout limitation.
Again, thank you in advance and also thank you for your previous answer. 85.56.131.251 15:45, 23 December 2021 (UTC)

Basics:

Dates

Places

Activities

Other

Languages:

Names:

Images:

For deceased persons:

It may be possible to do similar ones for English Wikipedia. Maybe a bot could generate them one by one for several wikis. --- Jura 16:52, 23 December 2021 (UTC)

pubmed from a given list of QIDs

Happy Boxing Day for those who celebrate such things. I have a list of QIds. Can I get a query that returns a list that includes the QId and the PubMed ID (P698)? Here is a start of my list of QIDs:

  • Q27938560
  • Q27939622
  • Q28508930
  • Q28566968
  • Q28567875
  • Q28584294

Thanks very much (in advance). Trilotat (talk) 16:18, 26 December 2021 (UTC)

@Trilotat:
SELECT ?item ?itemLabel ?PubMed_ID
WHERE 
{
  VALUES ?item {
    wd:Q27938560
    wd:Q27939622
    wd:Q28508930
    wd:Q28566968
    wd:Q28567875
    wd:Q28584294
    }
  OPTIONAL {?item wdt:P698 ?PubMed_ID . } 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}
Try it!
--Tagishsimon (talk) 17:21, 26 December 2021 (UTC)
Thank you so much!! Trilotat (talk) 18:49, 26 December 2021 (UTC)

Days with no photos

Please can we have a query for instances of calendar day of a given year (Q47150325), later than, say, 1900, with no image?

@Pigsonthewing: This sort of thing.
SELECT ?item ?itemLabel ?date
WHERE 
{
  ?item wdt:P31 wd:Q47150325. 
  ?item wdt:P585 ?date . hint:Prior hint:rangeSafe true.
  filter(xsd:integer(year(?date)) >  1900 && xsd:integer(year(?date)) <= 2021 )
  filter not exists {?item wdt:P18 [] . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
} order by desc(?date)
Try it!
--Tagishsimon (talk) 19:25, 30 December 2021 (UTC)
@Tagishsimon: Many thanks: 43491 (!) results, so maybe we need to make if for a given year, or decade? Also need to exclude future dates, please (it's bearable now, but would be a pain on the 1st January). Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 19:40, 30 December 2021 (UTC)
@Pigsonthewing: minimal fiddling with the filter will get you that. now() is also your friend.
SELECT ?item ?itemLabel ?date
WHERE 
{
  ?item wdt:P31 wd:Q47150325. 
  ?item wdt:P585 ?date . hint:Prior hint:rangeSafe true.
  filter(xsd:integer(year(?date)) =  2021 && ?date <= now() )
  filter not exists {?item wdt:P18 [] . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
} order by desc(?date)
Try it!
--Tagishsimon (talk) 20:00, 30 December 2021 (UTC)
@Tagishsimon: Just the job. Splendid work, thank you. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 22:15, 30 December 2021 (UTC)
and only 745 with ... --Infovarius (talk) 22:33, 30 December 2021 (UTC)
Comparing date values directly is much more effective than extracting years and then compare integers: 
SELECT ?item ?itemLabel ?date
WHERE 
{
  ?item wdt:P31 wd:Q47150325. 
  ?item wdt:P585 ?date . hint:Prior hint:rangeSafe true.
  filter("2021-01-01"^^xsd:dateTime <= ?date && ?date <= now() )
  filter not exists {?item wdt:P18 [] . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
} order by desc(?date)
Try it!
--Dipsacus fullonum (talk) 23:22, 30 December 2021 (UTC)
PS. This version is much faster because of the rangeSafe hint and the indexing of the ?date values. The engine can directly select the dates that satisfies the date range filter without testing all values as is necessary in the version using the YEAR function. --Dipsacus fullonum (talk) 23:32, 30 December 2021 (UTC)

Politicians by country (USA; UK; CAN; IN) in English and Hindi Wikipedias

Hello, I am looking to compare the number of articles on politicians from four countries (USA; UK; Canada; India) on two different Wikipedias (English and Hindi). I would be forever grateful for queries that would help me get these numbers!

  • American (Q30) politicians (Q82955) on en.wikipedia.org
  • British (Q145) politicians on en.wikipedia.org
  • Canadian (Q16) politicians on en.wikipedia.org
  • Indian (Q668) politicians on en.wikipedia.org
  • American politicians on hi.wikipedia.org
  • Canadian politicians on hi.wikipedia.org
  • Indian politicians on en.wikipedia.org

Thanks! Seeris (talk)

@Seeris: By the looks of it you'll have to run this query four times, swapping in the relevant country on the indicated line. I can't get a single query to work without timeout.
SELECT ?CoC (count(?article) as ?hi_count) (count(?article2) as ?en_count)  WHERE {
  ?item wdt:P106 wd:Q82955.
  ?item wdt:P27 ?CoC .
  VALUES ?CoC {wd:Q30}   # <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 
  OPTIONAL { ?article schema:about ?item ;
                      schema:isPartOf <https://hi.wikipedia.org/> . }
  OPTIONAL { ?article2 schema:about ?item ;              
                       schema:isPartOf <https://en.wikipedia.org/> . }
} group by ?CoC
Try it!
--Tagishsimon (talk) 01:52, 31 December 2021 (UTC)
Marvelous! Thanks a lot @Tagishsimon:. Here's my blurb about the results:
If we count the number of articles that exist about politicians by nationality, we find that in the English Wikipedia there are 61,800 articles on Americans, 15,000 on Canadians, 12,000 on Indians, and 10,200 on Britons; this represents a disproportionate number of Americans, but also slightly more Canadians than Indians (while India's population is significantly higher than Canada's), and surprisingly, a third more Canadians than Britons (while en.wikipedia is also said to be biased in favor of the British). This ratio is reversed in the Hindi Wikipedia: it has 4,257 articles on Indians, 141 on Americans, 105 on British, and 21 on Canadians; that is, a disproportionate number of Indians, and an almost two hundred-fold gap between Indians and Canadians (whereas it is only four times as large between Americans and Canadians in the English Wikipedia). Conclusion: ethnocentrism seems as prevalent on the Hindi Wikipedia as on the English one! Seeris (talk)

Items whose article in Wikipedia contain a given string

Suppose I have a list of QIDs and I want to know which of these items have an article in English Wikipedia whose body contain the string red banana. The following is a minimal working example I've written for you to test your possible solution 

SELECT ?item {
  VALUES ?item {
    # Group 1: The articles in en.wikipedia.org of these items
    # contain the string "red banana"
    wd:Q164934
    wd:Q490941
    wd:Q4927783
    # Group 2: The articles in en.wikipedia.org of these items
    # don't contain the string "red banana"
    wd:Q12876
    wd:Q249091
    wd:Q1233773
  }

  # TODO: Filter out items whose article in en.wikipedia.org doesn't
  # contain "red banana". That is, the query should show the items
  # from Group 1
}
Try it!

In order to avoid the XY problem, here's what I'm trying to do: I want to list all peruvian female humans whose article in Spanish Wikipedia contain the string "esposa de" because I want to know how many females are defined by mentioning their spouse. This because someone in Wikimedia Peru shared a guide on gender bias on Wikipedia and we started wondering how we could get a list of peruvian female humans that are potentially defined by mentioning their husband. We have already written a query for getting the list of peruvian female humans, but we don't know how to narrow the results to those that contain the string esposa de. Any help is appreciated

Rdrg109 (talk) 17:27, 25 December 2021 (UTC) (please ping on reply)

@Rdrg109: You can search Wikipedia articles for certain words by using the search function in the Wikipedia. The search https://es.wikipedia.org/w/index.php?search=%22esposa+de%22&ns0=1 will thus list all articles with the string "esposa de" in the Spanish Wikipedia. But it is hard to combine that search with a Wikidata query because there is more than 10.000 results (16.707 results to be exact). It is not so difficult to embed a Wikipedia search in a query using the MWAPI service (see https://www.mediawiki.org/wiki/Wikidata_Query_Service/User_Manual/MWAPI), but the problem here is that MWAPI has a hard limit of returning max 10,000 results. But if you change the search to also only include articles in the category es:Categoría:Mujeres (https://es.wikipedia.org/w/index.php?search=%22esposa+de%22+incategory%3AMujeres&ns0=1) you can limit the number of search results to under 3,000 which is manageable. The category Mujeres is normally set by es:Plantilla:NF which uses Wikidata. So here is a query to give articles in Spanish Wikipedia about Peruvian females with the string "esposa de" and placed in category "Mujeres". That is as close as I can get it.
SELECT ?item ?title (URI(CONCAT("https://es.wikipedia.org/wiki/", ?title)) AS ?article)
WITH
{
  SELECT DISTINCT ?item
  WHERE
  {
    { ?item wdt:P27 wd:Q419 . }
    UNION
    { wd:Q419 ^wdt:P131* / ^wdt:P19 ?item . }
    UNION
    { ?item wdt:P1532 wd:Q419 . }

    ?item wdt:P31 wd:Q5 .
    ?item wdt:P21 wd:Q6581072 .
  }
} AS %Peruvian_females
WHERE
{
  OPTIONAL # OPTIONAL is used to force this block to be evaluated first 
  {
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:endpoint "es.wikipedia.org" .
      bd:serviceParam wikibase:api "Generator" .
      bd:serviceParam mwapi:generator "search" .
      bd:serviceParam mwapi:gsrsearch '"esposa de" incategory:Mujeres' .
      bd:serviceParam mwapi:gsrlimit "max" .
      ?title wikibase:apiOutput mwapi:title .
      ?item wikibase:apiOutputItem mwapi:item .
    }
    FILTER BOUND(?item)
  }
  INCLUDE %Peruvian_females
}
Try it!
--Dipsacus fullonum (talk) 10:17, 31 December 2021 (UTC)
@Rdrg109:. I have edited the query above because the "*" after wdt:P131 was missing in first version giving too few results. The change meant that I also had to restructure the query to avoid timeout. --Dipsacus fullonum (talk) 12:47, 31 December 2021 (UTC)

Homonyms inside same subway

Hello, I came across Western (Q3567470), Western (Q3567480), Western (Q3567469), etc and I wondered how to find subways stations that share exactly same name inside same subway network? Bouzinac 💬●✒️●💛 17:24, 30 December 2021 (UTC)

@Bouzinac: Is this what you were looking for?
SELECT ?transitsystemLabel ?stationLabel ?count ?stations WHERE {
{
  SELECT DISTINCT ?transitsystem ?transitsystemLabel ?stationLabel (COUNT(DISTINCT ?station) AS ?count) (GROUP_CONCAT(DISTINCT ?station) AS ?stations) WHERE {
    wd:Q5503 ^wdt:P279*/^wdt:P31 ?transitsystem .
    ?transitsystem ^wdt:P361 ?station .
    ?station wdt:P31/wdt:P279* wd:Q12819564 .
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  } GROUP BY ?transitsystem ?transitsystemLabel ?stationLabel
}
  FILTER(?count > 1)
} ORDER BY DESC (?count) ASC (?transitsystemLabel) ASC (?stationLabel)
Try it!
--Quesotiotyo (talk) 05:08, 31 December 2021 (UTC)
Yes, something like that and appears that 86th Street would beat Western in terms of homonyms (but Western looks more strange to me since there is two Western on the same subway line). I've slightly modified your query this way https://w.wiki/4csT
That query functions provided en label is correct among items... Bouzinac 💬●✒️●💛 09:01, 31 December 2021 (UTC)
Retrieved from "https://www.wikidata.org/w/index.php?title=Wikidata:Request_a_query/Archive/2021/12&oldid=1562160944"