Shortcuts: WD:RAQ, w.wiki/LX

Wikidata:Request a query

From Wikidata
Jump to navigation Jump to search

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

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

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

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

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

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


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

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)Reply[reply]

@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)Reply[reply]

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

settlements above 4000 meters[edit]

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

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)Reply[reply]
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)Reply[reply]
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)Reply[reply]

deletion interwiki[edit]

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)Reply[reply]

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)Reply[reply]

All given names for a person.[edit]

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)Reply[reply]

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)Reply[reply]
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)Reply[reply]

Query timePrecision on a date qualifier[edit]

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)Reply[reply]

@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)Reply[reply]
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)Reply[reply]

Only using the first matching UNION?[edit]

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)Reply[reply]

@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)Reply[reply]
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)Reply[reply]

only using the shortest path?[edit]

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)Reply[reply]

@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)Reply[reply]

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

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)Reply[reply]

@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)Reply[reply]
@Deborahjay: idk what the issue here is; I get 139 results. --Tagishsimon (talk) 13:35, 30 May 2023 (UTC)Reply[reply]
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)Reply[reply]
@Deborahjay: Try https://w.wiki/6mjA ... any better? --Tagishsimon (talk) 13:59, 30 May 2023 (UTC)Reply[reply]
@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)Reply[reply]
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)Reply[reply]