Wikidata:Request a query/Archive/2023/05

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

Musical families

I would like to query for all people, which have a sister or brother with a Discogs ID at Wikidata. Thanks in advance!-- Jackie Bensberg (talk) 21:46, 30 April 2023 (UTC)

@Jackie Bensberg: This:

SELECT ?artist ?artistLabel ?id ?sibling ?siblingLabel WITH {
  SELECT DISTINCT ?artist ?sibling ?id WHERE 
  {
    ?artist wdt:P1953 ?id. 
    { ?sibling wdt:P3373 ?artist . }
    UNION
    { ?artist wdt:P3373 ?sibling . }
  } } as %i
WHERE
{
  INCLUDE %i
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 22:23, 30 April 2023 (UTC)

Thanks – this looks really powerful to me. Would it be possible to query for

just siblings of the other sex?
both having a Discogs ID?

--Jackie Bensberg (talk) 08:15, 1 May 2023 (UTC)

nested minuses

Following on from Wikidata:project chat, I want to get fortifications that are not part of a bigger fortification, unless that bigger fortification is itself not labelled as a cultural heritage ensemble (Q1516079). So I wrote

SELECT DISTINCT ?item ?itemLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
  {
    SELECT DISTINCT ?item WHERE {
      ?item wdt:P31/wdt:P279* wd:Q57821.
      ?item wdt:P17 wd:Q145.
      MINUS {
        ?item p:P361 ?partof.
        ?partof ps:P361/wdt:P31/wdt:P279* wd:Q57821.
        MINUS {
          ?partof wdt:P31 wd:Q1516079.
        }
      }
      MINUS {
        ?item wdt:P31 wd:Q1516079.
      }
      ?item rdfs:label ?name. 
      FILTER (lang(?name) = "en")
      FILTER(CONTAINS(?name, "Brougham")).
    }
  }
}

Try it!

hoping that would give Brougham Castle (Q2968700) and not Brougham Roman fort and Brougham Castle (Q17642723), but it doesn't, I guess because I don't understand how the nested MINUSes work Vicarage (talk) 09:24, 1 May 2023 (UTC)

@Vicarage: Is this more what you were after? AFAICS, the below will NOT minus the item if the item of which it is a part is not marked as a cultural heritage ensemble. Remove the filter not exists {} is you want hte item removed when the parent item DOES have cultural heritage ensemble.

SELECT DISTINCT ?item ?itemLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
  {
    SELECT DISTINCT ?item WHERE {
      ?item wdt:P31/wdt:P279* wd:Q57821.
      ?item wdt:P17 wd:Q145.
      MINUS {
        ?item p:P361/ps:P361 ?partof.
        ?partof wdt:P31/wdt:P279* wd:Q57821.
        filter not exists {?partof wdt:P31 wd:Q1516079.}     
      }
      MINUS {
        ?item wdt:P31 wd:Q1516079.
      }
      ?item rdfs:label ?name. 
      FILTER (lang(?name) = "en")
      FILTER(CONTAINS(?name, "Brougham")).
    }
  }
}

Try it!

--Tagishsimon (talk) 17:05, 1 May 2023 (UTC)

That's producing sensible lists that aren't too cluttered, thanks Vicarage (talk) 19:09, 1 May 2023 (UTC)

Deleted articles

Hello everyone. Theses past few days, I created many interlingual articles of Wikidata Page Vesoul (Q203309) but I noticed that WikiBayer has deleted all of them, without informing me. Theses articles were well made and I was about to add new contents in it. Could anynone please provide an explication ? Ghkkj (talk) 09:28, 1 May 2023 (UTC)

Answered here: User_talk:WikiBayer#Deleted_articles. tl;dr - spam. --Tagishsimon (talk) 22:30, 1 May 2023 (UTC)

Instances of Wikibase reason for deprecated rank (Q27949697)

Trying to find all instances of Wikibase reason for deprecated rank (Q27949697), as well as instances of its subclasses and subclasses of its instances, I figured that the following query should do:

SELECT ?item
WHERE 
{
  ?item wdt:P279*/wdt:P31/wdt:P279* wd:Q27949697.
}

Try it!

However, the result (some implausible 23,176 rows) contains items such as antihero (Q110910) or magician (Q148401). What am I doing wrong? --2A02:8108:50BF:C694:4881:39D7:411A:F021 10:00, 2 May 2023 (UTC)

wikibase:around not including itself

Normally when I use wikibase:around it returns the original item, but not in this case. I wonder if is because there are 2 locations. One was preferred, I changed the other temporarily to deprecated, but that made no difference.

SELECT DISTINCT ?item ?itemLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en-GB,en". }
  {
    SELECT DISTINCT ?item  WHERE {
#      hint:Query hint:optimizer "None"
      SERVICE wikibase:label { bd:serviceParam wikibase:language "en-GB,en". }
      wd:Q26676412 wdt:P625 ?targetLoc .
      SERVICE wikibase:around {
        ?item wdt:P625 ?location .
        bd:serviceParam wikibase:center ?targetLoc .
        bd:serviceParam wikibase:radius "30" .
        bd:serviceParam wikibase:distance ?dist.
      }
      ?item wdt:P31/wdt:P279* wd:Q91122. # bunker

    }
  }
}

Try it!

Vicarage (talk) 07:01, 3 May 2023 (UTC)

Wikipedia articles related to pics in Commons category

Hey, how can I get all (image) files within a Commons category, then the P180 (depicts) Q-value entered for each file (if entered), and then the Wikipedia article for Swedish language (if it exists) for that Q-value?

The Commons category I’d like to work with is https://commons.wikimedia.org/wiki/Category:Finland%20framst%C3%A4ldt%20i%20teckningar

Many thanks! Robertsilen (talk) 15:26, 4 May 2023 (UTC)

@Robertsilen: This?

SELECT ?file ?title ?P180 ?P180Label ?article ?sitelink
WITH
{
  SELECT ?file ?title
  WHERE
  {
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:api "Generator" .
      bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
      bd:serviceParam mwapi:gcmtitle "Category:Finland framstäldt i teckningar" .
      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 %get_files
WHERE
{
  INCLUDE %get_files
  OPTIONAL {?file wdt:P180 ?P180 .     
    OPTIONAL {
      SERVICE <https://query.wikidata.org/sparql> {
      OPTIONAL {?article schema:about ?P180 ;
        schema:isPartOf <https://sv.wikipedia.org/> ; 
        schema:name ?sitelink .}
      SERVICE wikibase:label {
        bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" .
        ?P180 rdfs:label ?P180Label . }        
      }
    }
  }
}

Try it!

--Tagishsimon (talk) 15:41, 4 May 2023 (UTC)

@Tagishsimon: Perfect! Thank you! Robertsilen (talk) 07:12, 5 May 2023 (UTC)

Biological Parents

I am trying to get items together with their biological parents, i.e. the values of father (P22) and mother (P25) statements without a kinship to subject (P1039) qualifier marking the relationship as (some subclass of) non-biological parent (Q66363468). The following query should do, but times out:

SELECT DISTINCT ?child ?father ?mother
WHERE
{
  ?child p:P22 ?fatherProperty;
         p:P25 ?motherProperty.
  ?fatherProperty a wikibase:BestRank;
                  ps:P22 ?father.
  ?motherProperty a wikibase:BestRank;
                  ps:P25 ?mother.
  
  OPTIONAL
  {
    ?fatherProperty pq:P1039 ?fatherKinship.
    ?fatherKinship wdt:P279* wd:Q66363468.
  }
  FILTER(!BOUND(?fatherKinship))
  
  OPTIONAL
  {
    ?motherProperty pq:P1039 ?motherKinship.
    ?motherKinship wdt:P279* wd:Q66363468.
  }
  FILTER(!BOUND(?motherKinship))
}

Try it!

Any ideas how to optimize it? --2A02:8108:50BF:C694:9C21:58CE:1CE2:C2CA 15:58, 4 May 2023 (UTC)

Zu viele Dingen. If you limit the working set to, for instance just citizens of one country, it will run just fine. Infrastruktur (talk) 08:50, 5 May 2023 (UTC)

Hm, OK, bummer. :( Thanks --2A02:8108:50BF:C694:F8D4:BB09:A40B:DB2C 09:38, 5 May 2023 (UTC)

Using VALUES to get one item times out if OPTIONAL used

SELECT DISTINCT ?itemLabel ?item ?itemDescription ?extra WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en-GB,en". }
  { SELECT DISTINCT ?item (GROUP_CONCAT(DISTINCT ?extralist; SEPARATOR = "#") AS ?extra) WHERE {
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en-GB,en". }

      VALUES ?item {wd:Q7534755}. # Sketchfab
      OPTIONAL {
          {?item wdt:P17 ?extra} # country
        ?extra rdfs:label ?extralist. FILTER (LANG(?extralist) = "en")
      }
    }
    GROUP BY ?item
  }
}

Try it!

is fast if the OPTIONAL block is commented out, but times out with it in place. Why doesn't SPARQL just accept I want one item and them move on to find out its country? Vicarage (talk) 13:57, 5 May 2023 (UTC)

@Vicarage: It's regrettable. Another data point is, use ?item outside the OPTIONAL{} and all is well. In other news, I think your last two queries have had two calls to the label service - SERVICE wikibase:label { bd:serviceParam wikibase:language "en-GB,en". } - when one call would have done.

SELECT DISTINCT ?itemLabel ?item ?itemDescription ?extra WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en-GB,en". }
  { SELECT DISTINCT ?item (GROUP_CONCAT(DISTINCT ?extralist; SEPARATOR = "#") AS ?extra) WHERE {
      VALUES ?item {wd:Q7534755}. # Sketchfab
      ?item wikibase:statements ?statements.
      OPTIONAL {
          {?item wdt:P17 ?extra} # country
           ?extra rdfs:label ?extralist. FILTER (LANG(?extralist) = "en")
          }
    }
    GROUP BY ?item
  }
}

Try it!

--Tagishsimon (talk) 14:06, 5 May 2023 (UTC)r

Cheers, I'd never have thought of that. Vicarage (talk) 15:39, 5 May 2023 (UTC)

This is an interesting one. Another way of fixing this one would be to put the values clause inside the optional, making the left side of the left join an empty set. This is functionally equivalent to not using an optional block. Not sure why it struggles here, looks like another bug to me as there is no need to access the graph on the left side of the left join. Yet another workaround would be to put the values clause inside a subquery to force materialization of the set. The most pragmatic fix is Tagishsimon's suggestion however. Infrastruktur (talk) 05:35, 6 May 2023 (UTC)

Given an organisation, list all entries that use their id

SELECT DISTINCT ?item ?itemLabel ?property ?value WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en-GB,en". }
  {
    SELECT DISTINCT ?item ?property ?value WHERE {
      wd:Q355 wdt:P1687 ?property.
      #?item ?property ?value
      ?item wdt:P2013 ?value.      
      ?item wdt:P31/wdt:P279* wd:Q1785071.
    }
  }
}

Try it!

Works to show me all the forts that have a Facebook ID. But I want the more general case where I can use the ?property value I've found as the selector, rather than hard coding P2013, ie using a correct version of the commented out line. Vicarage (talk) 11:14, 7 May 2023 (UTC)

@Vicarage: This, at least for truthy uses of the property. Change 'directClaim' per this list - https://www.mediawiki.org/wiki/Wikibase/Indexing/RDF_Dump_Format#Properties - if you want all statements.

SELECT DISTINCT ?item ?itemLabel ?property ?value WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en-GB,en". }
  {
    SELECT DISTINCT ?item ?property ?value WHERE {
      wd:Q355 wdt:P1687 ?property.
      ?item ?predicate ?value.
      ?property wikibase:directClaim ?predicate .
#      ?item wdt:P2013 ?value.      
      ?item wdt:P31/wdt:P279* wd:Q1785071.
    }
  }
}

Try it!

--Tagishsimon (talk) 13:11, 7 May 2023 (UTC)

Excellent, I've got that working for https://warlike.johnbray.org.uk/Q355, much appreciated. Vicarage (talk) 14:47, 7 May 2023 (UTC)

Query returning RDF triples for States, Territories, and Cities in India

I'm getting to understand Wikidata well enough that I can almost write this myself but I'm under a time crunch and the one thing I can't figure out how to do is to return triples rather than some tabular format. I want triples because I want to add the data into a knowledge graph built with OWL and RDF. I'm going to describe the query for states because I think the best way to do it would be two different queries, one for states and one for territories (the graph database I'm using can easily detect and remove duplicate triples) but if there is a better way, whatever works.

In words what I want are all the major cities, states, and territories in India with their rdfs:label, latitude and longitude, population, abstract (or whatever the overview property in Wikidata is called) and locatedIn values. My understanding of Indian geography is that states and territories are different administrative entities so there isn't a mapping such as states area always locatedIn territories or vice versa... but I could be wrong about that.

So more formally (for states):

All states locatedIn India All clities locatedIn a state The locatedIn values for each city and state The rdfs:label for each city and state as well as the population, latitude, longitude, and abstract.

Again, ideally if the results can be RDF triples that would be great. Michael DeBellis aka MadScientistX11 (talk) 17:33, 5 May 2023 (UTC)

@MadScientistX11: Slightly difficult to know exactly what you mean by RDF triples. But in the spirit of the question, the first query heads in the direction of getting the items on your shopping list. The second query expresses all of the truthy properties, as well as the EN rdfs:label and schema:desciption, for all of the items. My presumption is that playing with the columns in the second query may give you what you want. I've selected ?property in the SELECT, but for all I know, ?predicate might work better there for you; if so the BINDs on rdfs:label and schema:desciption should probably be to ?predicate rather than ?property. I've taken 'city' to mean a couple of specific QIds, city (Q515) and big city (Q1549591). Right now, where the data has plural truthy values for a statement, the query will deliver al of these. And, obvs, this gives you more than you wish - all truthy statements. It would be possible to cut down the number of rows by identifying just the properties for which you values, using VALUES ?property {wd:P31 wd:Pxxx wd:Pyyyy}.

Does that help?

SELECT distinct ?item ?itemLabel ?itemDescription ?type ?typeLabel ?locatedIn ?locatedInLabel ?pop ?coord
WHERE 
{
  VALUES ?type {
    wd:Q12443800 # state
    wd:Q467745 # union territory
    wd:Q515 # city
    wd:Q1549591 # big city 
  }
  ?item wdt:P31 ?type.  
  ?item wdt:P17 wd:Q668.
  ?item wdt:P131 ?locatedIn.
  OPTIONAL {?item wdt:P1082 ?pop. }
  OPTIONAL {?item wdt:P625 ?coord . } 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

SELECT distinct ?item ?property ?propertyLabel ?value ?valueLabel WITH {
  SELECT DISTINCT ?item WHERE {
  VALUES ?type {
    wd:Q12443800 # state
    wd:Q467745 # union territory
    wd:Q515 # city
    wd:Q1549591 # big city 
  }
  ?item wdt:P31 ?type.  
  ?item wdt:P17 wd:Q668.
  } } as %i
WHERE
{
  INCLUDE %i
  {?item ?predicate ?value .
  ?property wikibase:directClaim ?predicate .}
  UNION
  {?item rdfs:label ?value. filter(lang(?value)="en") BIND(rdfs:label as ?property) }
  UNION
  {?item schema:description ?value. filter(lang(?value)="en") BIND(schema:description as ?property) }  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 18:47, 5 May 2023 (UTC)

This is best achieved using a construct query: https://w.wiki/6fgK . Thanks to Tagishsimon for providing the bulk of the content for the query. To download the data using the Wikidata query service GUI you can click on "Link" in the middle right of the screen, then "SPARQL endpoint" to get a link which you can use with for instance the tool "curl". But since you run a triplestore on your own computer, chances are it already has a tool that is better suited to running queries for you. Apache Jena has the "rsparql" tool for instance, just redirect the output to a textfile and you're good. If you want to get a little fancy you can also use sparql update with federation to insert the data directly into your triplestore, I'll leave the latter as an exercise for the reader. Infrastruktur (talk) 06:13, 6 May 2023 (UTC)

First, apologies, I've been very busy this week. Have 3 papers due on Monday so I forgot I even asked this and that my Wikipedia account is linked to an email I don't check much (have to change that). I haven't had time to go through the answers but they look outstanding. Thanks very much. MadScientistX11 (talk) 14:48, 10 May 2023 (UTC)

List of biographies on en wiki that use a particular template

This templateː https://en.wikipedia.org/wiki/Special:WhatLinksHere/Template:Wikisourcelang

All the biographies here should use a slightly modified template - but before a bot can be run, we need a list of affected biographies (between 1-2k I think), since the template is also used (correctly) on non-bio pages. Piotrus (talk) 08:33, 11 May 2023 (UTC)

@Piotrus All enwiki pages using Template:Wikisourcelang Vojtěch Dostál (talk) 09:24, 11 May 2023 (UTC)

And only those which are personal biographies (based on Wikidata). Vojtěch Dostál (talk) 09:26, 11 May 2023 (UTC)

@Vojtěch Dostál Thank youǃ Piotrus (talk) 10:49, 11 May 2023 (UTC)

Persons who dont have a picture

I have written the following query that filter out Swedish PMs with the string "vilde" in the partyname and then find Wikicommons pictures for those people....

My problem is how to find the same people BUT has no picture i.e. people in Wikidataitems that has no pictures...

My "wikicommons" query:

#title: First/Sec chamber members pictures "vilde" how they are presented in "Porträttbooks"
#defaultView:ImageGrid
SELECT DISTINCT ?file ?wd ?name ?image (CONCAT(?party," ", ?timevilde) AS ?vilde) (concat("Book published ",str(year(?booktime))) AS ?bookPublished)
WITH 
{ SELECT distinct ?wd ?name ?itemDescription ?party ?timevilde ?startvilde ?endvilde WHERE
  { 
    SERVICE <https://query.wikidata.org/sparql> 
    {
      VALUES ?position { wd:Q81531912 wd:Q33071890 }
      ?wd wdt:P31 wd:Q5;
          wdt:P39 ?position.
      ?wd rdfs:label ?name. FILTER(lang(?name)="sv")
      {
       ?wd p:P102 ?PartyWD. 
       ?PartyWD ps:P102 ?p
       OPTIONAL {?PartyWD pq:P580 ?startvilde}
       OPTIONAL {?PartyWD pq:P582 ?endvilde}
       BIND (concat(str(year(?startvilde))," - ", str(year(?endvilde))) AS ?timevilde)
       ?p rdfs:label ?party.
       FILTER(LANG(?party) ="sv").
       FILTER(CONTAINS(?party, 'vilde'))
        SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
       #FILTER (?wd = wd:Q5555629)
      }
    }
  }
} AS %Wikidataitems

WHERE 
{  INCLUDE %Wikidataitems .
  ?file wdt:P180 ?wd.
  VALUES ?booksP1433 { 
                       wd:Q116445396 # 1894
                       wd:Q110380539 # 1897 
                       wd:Q110380456 # 1900
                       wd:Q110375618 # 1903
                       wd:Q110376088 # 1906 
                       wd:Q116313186 # 1909
                     }
  
   SERVICE <https://query.wikidata.org/sparql> 
    {
      ?booksP1433 wdt:P585 ?booktime
    }
  FILTER (?startvilde <= ?booktime)
  FILTER (?endvilde >= ?booktime)
  ?file wdt:P1433 ?booksP1433.
  ?file schema:contentUrl ?url. 
  bind(iri(concat("http://commons.wikimedia.org/wiki/Special:FilePath/", wikibase:decodeUri(substr(str(?url),53)))) AS ?image)
} 
order by ?name ?startvilde

First/Sec chamber members pictures "vilde" how they are presented in "Porträttbooks"

my github issue #139

- Salgo60 (talk) 19:09, 11 May 2023 (UTC)

Did a small test with chatGPT who told me to use OPTION and _FILTER (!BOUND(?file))_ no missing files found maybe this is getting more complex than I thought... - Salgo60 (talk) 20:17, 11 May 2023 (UTC)

@Salgo60: I switched the SPARQL template to SDC, for a commons query, which I presume this is. Roughly speaking, you need to put OPTIONALs around elements of the second query which will not be satisfied by the clauses in the second query by dint of not having a ?file. So something in the direction set out below (and switch it into table view to see the rows). All of the book stuff is not working well right now, though.

#title: First/Sec chamber members pictures "vilde" how they are presented in "Porträttbooks"
#defaultView:ImageGrid
SELECT DISTINCT ?file ?wd ?name ?image (CONCAT(?party," ", ?timevilde) AS ?vilde) (concat("Book published ",str(year(?booktime))) AS ?bookPublished)
WITH 
{ SELECT distinct ?wd ?name ?itemDescription ?party ?timevilde ?startvilde ?endvilde WHERE
  { 
    SERVICE <https://query.wikidata.org/sparql> 
    {
      VALUES ?position { wd:Q81531912 wd:Q33071890 }
      ?wd wdt:P31 wd:Q5;
          wdt:P39 ?position.
      ?wd rdfs:label ?name. FILTER(lang(?name)="sv")
      {
       ?wd p:P102 ?PartyWD. 
       ?PartyWD ps:P102 ?p
       OPTIONAL {?PartyWD pq:P580 ?startvilde}
       OPTIONAL {?PartyWD pq:P582 ?endvilde}
       BIND (concat(str(year(?startvilde))," - ", str(year(?endvilde))) AS ?timevilde)
       ?p rdfs:label ?party.
       FILTER(LANG(?party) ="sv").
       FILTER(CONTAINS(?party, 'vilde'))
        SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
       #FILTER (?wd = wd:Q5555629)
      }
    }
  }
} AS %Wikidataitems

WHERE 
{  INCLUDE %Wikidataitems .
  OPTIONAL {?file wdt:P180 ?wd.}
  OPTIONAL {
  VALUES ?booksP1433 { 
                       wd:Q116445396 # 1894
                       wd:Q110380539 # 1897 
                       wd:Q110380456 # 1900
                       wd:Q110375618 # 1903
                       wd:Q110376088 # 1906 
                       wd:Q116313186 # 1909
                     }
   SERVICE <https://query.wikidata.org/sparql> 
    {
  
      ?booksP1433 wdt:P585 ?booktime
    }
  FILTER (?startvilde <= ?booktime)
  FILTER (?endvilde >= ?booktime)
  ?file wdt:P1433 ?booksP1433.
  ?file schema:contentUrl ?url. 
  bind(iri(concat("http://commons.wikimedia.org/wiki/Special:FilePath/", wikibase:decodeUri(substr(str(?url),53)))) AS ?image)
  }
} 
order by ?name ?startvilde

First/Sec chamber members pictures "vilde" how they are presented in "Porträttbooks"

--Tagishsimon (talk) 20:40, 11 May 2023 (UTC)

Thanks - Salgo60 (talk) 20:49, 11 May 2023 (UTC)

Taxonomy from a specific entity

I want to get the whole tree of entities up (entities for which the given entity is a subclass/instance) starting from a specific entity, for example from Q30642 (natural language processing). So, I would like to get for example Q21198 (computer science), then Q11862829 (academic discipline) and so on. And so - for all parents of the entity Q30642 (natural language processing). Is it possible to do this in one request and how? Thank you! 31.163.119.81 11:36, 12 May 2023 (UTC)

Probably You're looking gor gas:service & BFS:

SELECT ?subclass ?subclassLabel ?child ?childLabel ?depth WHERE
 {
  SERVICE gas:service {
       gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.BFS" ; gas:in wd:Q30642 ; gas:linkType wdt:P279 ; gas:out ?subclass ; gas:out1 ?depth ; gas:out2 ?child . }

SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} order by ?depth

Try it!

regards, Piastu (talk) 11:48, 12 May 2023 (UTC)

Thank you very much! 31.163.119.81 12:00, 12 May 2023 (UTC)

Query of items located within an administrative territorial entity

I am attempting to query all performance halls located within the province of Quebec: https://w.wiki/6h2c. A province is the largest territorial entity within Canada. Municipalities can be up to three levels down from a province. Can we restrict the query so it doesn't search into every neighbourhood? Fjjulien (talk) 15:48, 11 May 2023 (UTC)

Yes, but you'd have to specify the pattern of the items to be excluded. --Tagishsimon (talk) 17:38, 11 May 2023 (UTC)

If you just wanted something that runs fast, let's know. Infrastruktur (talk) 10:19, 12 May 2023 (UTC)

@Infrastruktur Yes, I'm primarily interested in a query that runs fast enough not to time out. Fjjulien (talk) 13:19, 12 May 2023 (UTC)

Ok. https://w.wiki/6h4M . Infrastruktur (talk) 20:16, 12 May 2023 (UTC)

@Infrastruktur Thank you very much for this query. I really like how the query displays performance halls by municipalities.

I tried to adapt the same query to retrieves performing arts buildings in Quebec (https://w.wiki/6hyp). However, this one times out. The issue could be performing arts building (Q57660343) and its 85 subclasses. I tried narrowing down the search by excluding instances of nightclub (Q622425) but it still timed out. What if we only looked for items that are a direct instance (not instance of a subclass) of performing arts building (Q57660343) and a few of the main subclasses such as Q3469910, Q24354, Q1060829, Q153562, and Q153562? How could we write this in a query? Fjjulien (talk) 13:53, 15 May 2023 (UTC)

Weird, Wikidata is struggling to keep up. I restricted it to max 4th order subclasses of performing arts building and that seemed to have done the trick. https://w.wiki/6i2J . Infrastruktur (talk) 17:37, 15 May 2023 (UTC)

@Infrastruktur Thank you for this other query! Fjjulien (talk) 01:30, 16 May 2023 (UTC)

Taxonomy of specified entity

I want to get the whole tree of entities up (entities for which the given entity is a subclass/instance) starting from a specific entity, for example from Q30642 (natural language processing). So, I would like to get for example Q21198 (computer science), then Q11862829 (academic discipline) and so on. And so - for all parents of the entity Q30642 (natural language processing).

Recently I was prompted the following solution using gas:service & BFS: [1]

SELECT ?subclass ?subclassLabel ?child ?childLabel ?depth WHERE
{
 SERVICE gas:service {
      gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.BFS" ; gas:in wd:Q21198 ;  gas:linkType wdt:P31; gas:out ?subclass ; gas:out1 ?depth ; gas:out2 ?child . }
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } } order by ?depth

It works fine, but I need to list the features in the query. For example gas:linkType (wdt:P31|wdt:P279). How can I take all parent entities both on the feature of P31 and on the feature of P279? 90.151.86.98 12:45, 16 May 2023 (UTC)

Hi again. Maybe ask twice? regards, Piastu (talk) 13:34, 18 May 2023 (UTC)

SELECT ?subclass ?subclassLabel ?child ?childLabel ?depth {
  {
  SERVICE gas:service {
    gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.BFS";
                gas:in wd:Q21198; gas:linkType wdt:P31; gas:out ?subclass; gas:out1 ?depth; gas:out2 ?child. }
  }
  UNION
  {
  SERVICE gas:service {
    gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.BFS";
                gas:in wd:Q21198; gas:linkType wdt:P279; gas:out ?subclass; gas:out1 ?depth; gas:out2 ?child. }
  }
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} order by ?depth

Try it!

Articles in an English Wikipedia category that have a corresponding article in the Slovene Wikipedia

Hello. I would like to request a query that finds

1) which articles in a given English (en) Wikipedia category have a corresponding (interwiki linked) article in the Slovene (sl) Wikipedia;
2) and further (if possible) don't yet contain a corresponding interwiki linked category in the Slovene Wikipedia.

An example category would be en:Category:Victory steles.

Thank you. TadejM (talk) 15:14, 16 May 2023 (UTC)

@TadejM: This. Amend the mwapi:gcmtitle value if you want to change the category.

SELECT distinct ?item ?itemLabel ?sitelink_en ?article_en ?sitelink_sl ?article_sl where
{
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
     bd:serviceParam wikibase:endpoint "en.wikipedia.org";
                     wikibase:api "Generator";
                     mwapi:generator "categorymembers";
                     mwapi:gcmtitle "Category:Victory steles" ;         # specifically here
                     mwapi:gcmprop "ids|title|type";
                     mwapi:gcmlimit "max".
     # out
     ?name wikibase:apiOutput mwapi:title.        # en-wikipedia article / category name
     ?item wikibase:apiOutputItem mwapi:item.            # wikidata QId for the person's item
    }
  FILTER(BOUND(?item))
  ?article_en schema:about ?item ;
            schema:isPartOf <https://en.wikipedia.org/> ; 
            schema:name ?sitelink_en .
  optional {?article_sl schema:about ?item ;
            schema:isPartOf <https://sl.wikipedia.org/> ; 
            schema:name ?sitelink_sl .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 20:27, 21 May 2023 (UTC)

Tagishsimon, thank you a lot! This is really well formatted. It will be of great use. --TadejM (talk) 20:43, 21 May 2023 (UTC)

settlements above 4000 meters

Can I have the human settlements that are over 4000 meters above sea level? 122.59.56.224 06:00, 20 May 2023 (UTC)

SELECT ?item ?itemLabel ?altitude
WHERE 
{
  ?item wdt:P31 wd:Q486972. # Must be a human settlement
  ?item wdt:P2044 ?altitude.
  FILTER(?altitude >= 4000).  # above 4km
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

ProgVal (talk) 17:18, 21 May 2023 (UTC)

Oh, we have even the height of 16883! (Q7267899). We have to apply unit conversion here too... --Infovarius (talk) 20:06, 21 May 2023 (UTC)

Am I missing something, or does P2044 not have normalised values?

SELECT ?item ?itemLabel ?altitude ?pred ?val
WHERE 
{
  values ?item {wd:Q338235}
  ?item wdt:P31 wd:Q486972. # Must be a human settlement
  ?item p:P2044/psv:P2044/wikibase:quantityNormalized  ?altitude.
  ?altitude ?pred ?val .
#  FILTER(?altitude >= 4000).  # above 4km
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 20:21, 21 May 2023 (UTC)

deletion interwiki

Hello everyone, I come here to get some help. Yesterday, I created a pair of interlingual articles of Wikidata Page Vesoul (Q203309) but I noticed that WikiBayer has kept deleted all of them with justification "No meaningful content". Theses articles were well made and I'm pretty sure there content were meaningful, at least more than most of articles : for exemple I created a page of Vesoul in Runa Simi (.qu) and he actually deleted it while this page were meaningful content et references (cf a draft of this page in Runa Simi). Can anyone assure me that this page is more useless than other page in the same language dealing of other French city like Montpellier or Saint-Étienne ? In my opinion, this person is clearly abusing his power as an administrator. Could anynone try to stop him from doing anyting he want ? Ghkkj (talk) 02:40, 23 May 2023 (UTC)

It's a matter for the wikipedia on which the deletion took place; perhaps take this to their community talk page? There's nothing that can be done from this page. --Tagishsimon (talk) 23:10, 23 May 2023 (UTC)

All given names for a person.

Hello all, I have the following query:

SELECT ?human ?lastName ?givenNames ?dob
 WHERE {
  ?human wdt:P106 wd:Q82955 .
  ?human wdt:P735 ?givenNames.
  ?human wdt:P734 ?lastName.
  
  ?human wdt:P569 ?dob . hint:Prior hint:rangeSafe true .
    FILTER(?dob >= xsd:dateTime("1946-06-14T00:00:00Z") &&
           ?dob < xsd:dateTime("1946-06-15T00:00:00Z")) .
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
 }

Try it!

While I get results, it seems that I only get the first given name and not all given names. What would I need to change, to get all given names? 2A02:21B4:92C4:2A00:18D1:1587:4466:C39E 19:08, 23 May 2023 (UTC)

wdt: predicates will return only 'truthy' (or wikibase:bestRank) values. Donald Trump has two given names, but 'Donald" is marked as preferred rank, and so of the two is the 'truthy' value. p:P735/ps:P735, a property path which finds the statement node and then the statement value, returns truthy and non-truthy values, as below.

SELECT ?human ?humanLabel ?lastName ?givenNames ?dob
 WHERE {
  ?human wdt:P106 wd:Q82955 .
  ?human p:P735/ps:P735 ?givenNames.
  ?human wdt:P734 ?lastName.
  
  ?human wdt:P569 ?dob . hint:Prior hint:rangeSafe true .
    FILTER(?dob >= xsd:dateTime("1946-06-14T00:00:00Z") &&
           ?dob < xsd:dateTime("1946-06-15T00:00:00Z")) .
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
 }

Try it!

--Tagishsimon (talk) 21:54, 23 May 2023 (UTC)

This is amazing. I would have never guessed this. Thanks a lot Tagishsimon for taking the time! 2A02:21B4:92C4:B800:D9C:790F:6D2F:75F8 06:21, 24 May 2023 (UTC)

Query timePrecision on a date qualifier

There's an example to query timePrecision on a statement node:

#title: timePrecision for a date
SELECT ?time ?timeprecision
WHERE
{
     { wd:Q5598  p:P569/psv:P569 ?timenode. }  # Jul 15, 1606
     ?timenode wikibase:timeValue         ?time.
     ?timenode wikibase:timePrecision     ?timeprecision.
     SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

timePrecision for a date

However, I can't find a way to do something similar when the date is in a qualifier, like earliest date (P1319) for the inception (P571) of La Voz de Vizcaya (Q115426903). Any ideas would be welcome!

Pruna.ar (talk) Pruna.ar (talk) 00:48, 24 May 2023 (UTC)

@Pruna.ar:

#title: timePrecision for a qualifier date
SELECT ?time ?timeprecision
WHERE
{
     { wd:Q115426903  p:P571/pqv:P1319 ?timenode. }  
     ?timenode wikibase:timeValue         ?time.
     ?timenode wikibase:timePrecision     ?timeprecision.
     SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

timePrecision for a qualifier date

--Tagishsimon (talk) 02:52, 24 May 2023 (UTC)

Thanks @Tagishsimon!! So (besides my error in the item) the key is to do the "p:P571/pqv:P1319"!

~~ Pruna.ar (talk) 19:57, 25 May 2023 (UTC)

Only using the first matching UNION?

I have the following query, to display a map of depiction of dragon I wrote a while ago and enhanced today:

#defaultView:Map
SELECT DISTINCT ?item ?itemLabel ?coords ?coordsApprox ?image ?location WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,fr,de". }
  {
    SELECT DISTINCT ?item ?coords ?coordsApprox ?image ?location ?place WHERE {
      ?item p:P180 ?depict.
      { ?depict (ps:P180/wdt:P31*/wdt:P279*) wd:Q7559. }
      UNION
      { ?depict (ps:P180/wdt:P31*/wdt:P279*) wd:Q30170627. }
      
      {
        ?item p:P625 ?statement1.
        ?statement1 (ps:P625) ?coords.
      } UNION {
        ?item p:P276 ?statement2.
        ?statement2 (ps:P276) ?place.
        ?place p:P625 ?statement3.
        ?statement3 (ps:P625) ?coordsApprox.
      }
      
      OPTIONAL { ?item wdt:P18 ?image. }
      
    }
    LIMIT 500
  }
}

Try it!

In particular, I added the second UNION for taking either the coordinate of the entry itself, or a coordinate of the place the object is in (the second one). It now return me a bunch of duplicate. How can I avoid that?

(As I also have a javascript web interface, I’ll do deduplication in the front-end in the meantime) Marius851000 (talk) 08:13, 27 May 2023 (UTC)

@Marius851000: Probably something along these lines, which removes duplicate images and coords by sampling, makes the coords simple statements, not unioned and so avoids Cartesian products of coords versus coordsApprox.

#defaultView:Map
SELECT DISTINCT ?item ?itemLabel ?coords ?coordsApprox ?image ?location WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,fr,de". }
  {
    SELECT DISTINCT ?item (sample(?coords_) as ?coords) (sample(?coordsApprox_) as ?coordsApprox) (sample(?image_) as ?image) ?location ?place WHERE {
      ?item p:P180 ?depict.
      { ?depict (ps:P180/wdt:P31*/wdt:P279*) wd:Q7559. }
      UNION
      { ?depict (ps:P180/wdt:P31*/wdt:P279*) wd:Q30170627. }
      
      OPTIONAL {
        ?item p:P625 ?statement1.
        ?statement1 (ps:P625) ?coords_.
        } 
      OPTIONAL {
        ?item p:P276 ?statement2.
        ?statement2 (ps:P276) ?place.
        ?place p:P625 ?statement3.
        ?statement3 (ps:P625) ?coordsApprox_.
      }
      
      OPTIONAL { ?item wdt:P18 ?image_. }
      
    } group by ?item  ?location ?place
    LIMIT 500
  }
}

Try it!

--Tagishsimon (talk) 11:41, 27 May 2023 (UTC)

Thanks for this example! Aggregate functions (https://en.wikibooks.org/wiki/SPARQL/Aggregate_functions) was indeed what I needed! (and I now have another issue. I’ll open another thread, as it’s a different one) Marius851000 (talk) 08:49, 28 May 2023 (UTC)

only using the shortest path?

I have said query:

#defaultView:Table
SELECT DISTINCT ?item ?itemLabel ?coords ?coordsApprox ?image ?placeLabel ?nature ?natureLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,fr,de". }
  {
    SELECT DISTINCT ?item (MIN(?coords_) AS ?coords) ?coordsApprox (MIN(?image_) AS ?image) (MIN(?place_) AS ?place) (MIN(?nature_) AS ?nature) WHERE {
      {
        ?item p:P180 ?depict.
        { ?depict (ps:P180/wdt:P31*/wdt:P279*) wd:Q7559. } # dragon
        UNION
        { ?depict (ps:P180/wdt:P31*/wdt:P279*) wd:Q30170627. } # dragon of fiction
      } UNION {
        ?item p:P31 ?nature.
        ?nature (ps:P31/wdt:P279*) wd:Q60985310. # chinese dragon colum
      }
      
      OPTIONAL { ?item wdt:P276 ?place_. }
      OPTIONAL { ?item wdt:P625 ?coords_. }
      
      # This optional is used to generate the list of dragon depiction with no coordinates
      OPTIONAL {
        ?item p:P276 ?statement2.
        ?statement2 (ps:P276/wdt:P276*/wdt:P361*) ?place. # TODO: this need to be improved. For example a Louvre wing and the Louvre will both be returned, but we want the most precise info
        ?place wdt:P625 ?coordsApprox.
      }
      
      OPTIONAL { ?item wdt:P31 ?nature_. }
      OPTIONAL { ?item wdt:P18 ?image_. }
      
    } GROUP BY ?item ?coordsApprox
    LIMIT 10000
  }
} ORDER BY ?item

Try it!

But now, I have a problem with the complex OPTIONAL statement (the one with #TODO). It find the place of the object by following the place property once, then any number of time, then the part of property any number of time. The problem is that it now result a bunch of place. One of them is the best answer (probably the one with the less depth), but some other are pretty bad (like some room is located in some wing is located in some museum is located on some country is located on some continent is located on earth, etc).

Otherwise, I’ll resort to not using expansion, and deciding which one to choose on the client side. Marius851000 (talk) 09:13, 28 May 2023 (UTC)

@Marius851000: Shortest path may not be an effective way of sorting the issue; the shortest path here is a room, there is a town &c. You may have to have a bank of OPTIONAL{} clauses with differing path expectations (e.g. P276, P276/P276) each bound to a separate variable; and then use BIND(COALESCE(?this,?that,?theOther) as ?foo) to grab the first qualifying variable; and the optionals may need another clause which identifies what the location must be (e.g. building, museum, whatever) or identifying what the location must not be (e.g. room, or P361 'part of' a building). --Tagishsimon (talk) 12:55, 28 May 2023 (UTC)

Pages in an English Wikipedia category with corresponding pages in the Hebrew Wikipedia per Wikidata links

The source category is en:Category:International observances. Which of the included pages have a corresponding page in the Hebrew Wikipedia, according to their interwiki links in Wikidata? The purpose of this query is to create and populate this category in the HE WP. -- Deborahjay (talk) 13:22, 30 May 2023 (UTC)

@Deborahjay:

SELECT distinct ?item ?itemLabel ?sitelink_en ?article_en ?sitelink_he ?article_he where
{
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
     bd:serviceParam wikibase:endpoint "en.wikipedia.org";
                     wikibase:api "Generator";
                     mwapi:generator "categorymembers";
                     mwapi:gcmtitle "Category:International observances" ;         # specifically here
                     mwapi:gcmprop "ids|title|type";
                     mwapi:gcmlimit "max".
     # out
     ?name wikibase:apiOutput mwapi:title.        # en-wikipedia article / category name
     ?item wikibase:apiOutputItem mwapi:item.            # wikidata QId for the person's item
    }
  FILTER(BOUND(?item))
  ?article_en schema:about ?item ;
            schema:isPartOf <https://en.wikipedia.org/> ; 
            schema:name ?sitelink_en .
  optional {?article_he schema:about ?item ;
            schema:isPartOf <https://he.wikipedia.org/> ; 
            schema:name ?sitelink_he .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Many thanks for the swift response, @Tagishsimon:. However, my attempt to Run it yielded - no results? Is that possible, or did I omit something in my query? - -- Deborahjay (talk) 13:33, 30 May 2023 (UTC)

@Deborahjay: idk what the issue here is; I get 139 results. --Tagishsimon (talk) 13:35, 30 May 2023 (UTC)

So evidently "It's-not-you-it's me," @Tagishsimon: Perhaps I'm not looking where the results appear (i.e. names of matching pages in HE WP with the EN WP pages; this category has 135 at present). It's been ages since my last SPARQL query and they (crafted by you!) were thoroughly useful. -- Deborahjay (talk) 13:42, 30 May 2023 (UTC)

@Deborahjay: Try https://w.wiki/6mjA ... any better? --Tagishsimon (talk) 13:59, 30 May 2023 (UTC)

@Tagishsimon:, absolutely on target! And most satisfactory results: plenty to create and populate a category, but not beyond my scope. Great collaboration; my local squad will be pleased! Cheers, -- Deborahjay (talk) 14:19, 30 May 2023 (UTC)

I think what was going on here is that the SPARQL template provided a "Run it" link, which does not actually run the report, merely transfers the user to WDQS, where the play button needs to be pressed to get the report to run. Previously the link said "Try it". I've reverted the link label to 'Try it'. @MSGJ: FYI. --Tagishsimon (talk) 22:43, 30 May 2023 (UTC)

Wikidata:Request a query/Archive/2023/05

Contents

Musical families

nested minuses

Deleted articles

Instances of Wikibase reason for deprecated rank (Q27949697)

wikibase:around not including itself

Wikipedia articles related to pics in Commons category

Biological Parents

Using VALUES to get one item times out if OPTIONAL used

Given an organisation, list all entries that use their id

Query returning RDF triples for States, Territories, and Cities in India

List of biographies on en wiki that use a particular template

Persons who dont have a picture

Taxonomy from a specific entity

Query of items located within an administrative territorial entity

Taxonomy of specified entity

Articles in an English Wikipedia category that have a corresponding article in the Slovene Wikipedia

settlements above 4000 meters

deletion interwiki

All given names for a person.

Query timePrecision on a date qualifier

Only using the first matching UNION?

only using the shortest path?

Pages in an English Wikipedia category with corresponding pages in the Hebrew Wikipedia per Wikidata links

Navigation menu

Wikidata:Request a query/Archive/2023/05

Musical families

nested minuses

Deleted articles

Instances of Wikibase reason for deprecated rank (Q27949697)

wikibase:around not including itself

Wikipedia articles related to pics in Commons category

Biological Parents

Using VALUES to get one item times out if OPTIONAL used

Given an organisation, list all entries that use their id

Query returning RDF triples for States, Territories, and Cities in India

List of biographies on en wiki that use a particular template

Persons who dont have a picture

Taxonomy from a specific entity

Query of items located within an administrative territorial entity

Taxonomy of specified entity

Articles in an English Wikipedia category that have a corresponding article in the Slovene Wikipedia

settlements above 4000 meters

deletion interwiki

All given names for a person.

Query timePrecision on a date qualifier

Only using the first matching UNION?

only using the shortest path?

Pages in an English Wikipedia category with corresponding pages in the Hebrew Wikipedia per Wikidata links

Navigation menu

Search