Wikidata:Request a query

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 queries [Q114898838] can be requested. Please provide feedback if a query is written for you.

You can also request help to rewrite queries that don't work anymore, due to the WDQS graph split.

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.

Translate this header box!

Start a new discussion

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 2024/12.

Missing LIS terms in Hungarian

I think that this discussion is resolved and can be archived. If you disagree, don't hesitate to replace this template with your comment. author  TomT0m / talk page 10:15, 1 December 2024 (UTC)

Hi, I would need some help. I would like to retrieve library and information science (Q13420675) terms that are not translated into Hungarian, so we can translate the missing terms all at once. I expect no more than 1-200 terms. Example: document retrieval (Q1638872).

The properties I would need: QID, EN preferred label, EN description, EN alternative label(s), DE preferred label, DE alternative label(s). Thank you in advance! Bencemac (talk) 20:19, 12 November 2024 (UTC)

@Bencemac: Hello, I try it. But it is not easy to find all items for library and information science (Q13420675). So I make another solution. I check for "is part of".

PREFIX schema: <http://schema.org/>
#defaultView:Table
SELECT ?item ?label_en ?desc_en ?label_de ?desc_de ?label_hu ?desc_hu
WHERE {
  
  ?item wdt:P31/wdt:P361* wd:Q199655 .   # part of library science (244 items)
  #?item wdt:P31/wdt:P361* wd:Q1235196.   # part of documentation science (only 1 item)
  #?item wdt:P31/wdt:P361* wd:Q16387.      # part of information science (978 items)

  OPTIONAL {?item rdfs:label        ?label_en. filter(lang(?label_en)="en"). }  
  OPTIONAL {?item schema:description ?desc_en. filter(lang( ?desc_en)="en"). }  
  
  OPTIONAL {?item rdfs:label        ?label_de. filter(lang(?label_de)="de"). }  
  OPTIONAL {?item schema:description ?desc_de. filter(lang( ?desc_de)="de"). }  
  
  OPTIONAL {?item rdfs:label        ?label_hu. filter(lang(?label_hu)="hu"). }  
  OPTIONAL {?item schema:description ?desc_hu. filter(lang( ?desc_hu)="hu"). }  

  FILTER(!BOUND(?label_hu))  # no label_hu 
  #FILTER(!BOUND( ?desc_hu))  # no desc_hu

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de,en,hu". }
}
#limit 50

Try it!

I hope this first SPARQL-Query will help you. You can choose what you want in line 6-8. Also in line 19 and 20 you can filter for no label or no description. Best regards. --sk (talk) 10:01, 17 November 2024 (UTC)

Thank you very much! :) Bencemac (talk) 14:19, 17 November 2024 (UTC)

railway platforms from Spanish Wikipedia

Hi! I could use a query listing all items with a number of platform tracks (P1103) and/or number of platform faces (P5595) statement, but only if that statement is imported from Wikimedia project (P143) Spanish Wikipedia (Q8449) (and is not deprecated). I would love another column showing country (P17) of each item (besides columns with the P1103 and P5595 values). Geogast 🤲 (talk) 15:53, 20 November 2024 (UTC)

@Geogast: Here is my solution:

#defaultView:Map
SELECT ?item ?itemLabel ?itemDescription ?npt ?npf ?countryLabel ?coordinate
WHERE {
  ?item wdt:P1103 ?npt.                       # number of platform tracks

  ?item p:P1103 ?statnode .
  ?statnode prov:wasDerivedFrom ?refnode . 
  ?refnode pr:P143 wd:Q8449.
  
  OPTIONAL {?item wdt:P1103 ?npf}    # number of platform faces
  OPTIONAL {?item wdt:P17 ?country.}
  OPTIONAL {?item wdt:P625 ?coordinate.}
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]"}
}

Try it!

But I dont include the "and is not deprecated". Because what did you mean. The platform is deprecated or the statement imported from Wikimedia project (P143) is deprecated. But I hope my query will help you. Best regards --sk (talk) 14:53, 21 November 2024 (UTC)

Oh, thanks a lot! It helped me a lot to get an overview. However, something is weird: the query gives wrong numbers. Conceição (Q5158299) has the statements: P5595=2; P1103=4 – but the query gives 4 and 4. Same with Botujuru train station (Q11681623). General Gutiérrez station (Q5842425) only has a P1103 statement (not P5595), but the query gives 2 and 2. The "not deprecated" issue seems fine (so I don't need to explain what I meant, right now). Geogast 🤲 (talk) 22:54, 22 November 2024 (UTC)

Now I see that in line 4, it says P1103 and "number of platform tracks" – and in line 10: P1103 again – but "number of platform faces". Is it possible that the issue is there? In fact, the query should give waaay more results where P1103 and P5595 values differ. Thanks for your work!! Geogast 🤲 (talk) 23:06, 22 November 2024 (UTC)

COUNT giving unexpected values

I think that this discussion is resolved and can be archived. If you disagree, don't hesitate to replace this template with your comment. --author  TomT0m / talk page 10:05, 1 December 2024 (UTC)

I'm trying to get a list of the parent classes of an item, sorted by how many intermediate classes lie between the item and that class. The counts should be consistent with the subclass tree of the item, but they are not. This query lists all the intermediate classes instead of counting them, and produces the expected result - e.g., there are two classes between house cat (Q146) and Carnivora (Q25306). This query, however, says there are 14 classes between those two. The only difference in the queries is that in the latter one, I'm grouping by all but intermediate class, and returning (COUNT(?intermediate) AS ?intermediate_classes). How is it getting 14?! Swpb (talk) 18:11, 26 November 2024 (UTC)

@Swpb not sure of what's going on but "count( distinct ?var )" seems to have fix it. Maybe the "distinct" globally was removing the duplicates but it occurs after the query. To say you do not want to count all the paths in the property path, here, you have to precise this "groupped variables by group variable".

The order seems to be "query => grouping => count => remove duplicates" in you query, with "count distinct" it seems to be "query => remove duplicates for the groupped variable => count => remove duplicate globally" author  TomT0m / talk page 11:25, 27 November 2024 (UTC)

Thank you!!! I had tried "distinct", but I had the syntax wrong (outside the innermost parentheses), so I didn't know it could be used inside "count" like that. Swpb (talk) 15:50, 27 November 2024 (UTC)

Uniqueness problem

I think that this discussion is resolved and can be archived. If you disagree, don't hesitate to replace this template with your comment. --author  TomT0m / talk page 10:05, 1 December 2024 (UTC)

I have the following query to create a map of mountains in a range to be used by kartographer. So the outer SELECT must return defined columns like ?id ?geo or ?description.

#defaultView:Map
#title:Mountains of range
SELECT DISTINCT ?id ?geo
  (concat(?article, (SAMPLE(?h)), '\\n', '[[File:Wikidata-logo S.svg|16px|link=d:', substr(str(?id),32,13), ']]', (SAMPLE(?idBild))) as ?description)
  ('mountain' as ?marker_symbol) ('small' as ?marker_size)

WITH {
  SELECT ?id ?geo ?img
  #SELECT ?id ?geo (SAMPLE(?img) as ?img_sample )
    WHERE { #(SAMPLE(?img) as ?img_sample)
    ?id wdt:P4552* wd:Q592910.
    ?id wdt:P31 wd:Q8502.
    ?id wdt:P625 [].
    ?id wdt:P625 ?geo.
    OPTIONAL {
      ?id wdt:P18 ?img.
    }
  }
} AS %sub 

WHERE {
  INCLUDE %sub.
  OPTIONAL {
    ?id          p:P2044                     ?stmnode.    # elevation
    ?stmnode       psv:P2044                   ?valuenode.
    ?valuenode     wikibase:quantityAmount     ?height.
    ?valuenode     wikibase:quantityUnit       ?unit.
    # conversion to SI unit
    ?unit          p:P2370                 ?unitstmnode.   # conversion to SI unit
    ?unitstmnode   psv:P2370               ?unitvaluenode. 
    ?unitvaluenode wikibase:quantityAmount ?conversion.
    ?unitvaluenode wikibase:quantityUnit   wd:Q11573.      # meter
  }
      
  #OPTIONAL { ?id wdt:P18 ?img.}
  BIND(IF(BOUND(?img), concat('\\n', '[[File:', substr(str(?img), 52, 400), '|250px]]'), '') AS ?idBild) .
  BIND(IF(BOUND(?height), concat(' (', str(?height * ?conversion),' m)'), '') AS ?h).

  OPTIONAL {
  ?link schema:about ?id .
  ?link schema:isPartOf <https://de.wikipedia.org/> .
    }
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'de' .
                           ?id rdfs:label ?idLabel . }
  BIND(IF(BOUND(?link), concat('[[:de:', substr(str(?link),31,100), '|', ?idLabel, ']]'), ?idLabel) AS ?article).
}
GROUP BY ?id ?geo ?article ?h ?idBild

Mountains of range

The problem now is that there are matching mountains like Kitzbüheler Horn (Q671193) with two image (P18), resp. like Hochetzkogel (Q21877978) with two elevation above sea level (P2044). I just want no duplicate mountains in that case. Did not get SAMPLE to work as expected. help is appreciated. --Herzi Pinki (talk) 21:31, 28 November 2024 (UTC)

@Herzi_Pinki:

#defaultView:Map
#title:Mountains of range


SELECT DISTINCT ?id ?geo
  (
    concat(
      ?article, 
      coalesce(SAMPLE(?h),""), 
      '\\n', 
      '[[File:Wikidata-logo S.svg|16px|link=d:', 
       substr(str(?id),32,13),
      ']]', 
      coalesce(SAMPLE(?idBild),"")
    ) as ?description
  )
  ('mountain' as ?marker_symbol) 
  ('small' as ?marker_size)

WITH {
  SELECT ?id ?geo ?idBild
    WHERE { #(SAMPLE(?img) as ?img_sample)
    ?id wdt:P4552* wd:Q592910.
    ?id wdt:P31 wd:Q8502.
    ?id wdt:P625 [].
    ?id wdt:P625 ?geo.
    OPTIONAL {
      ?id wdt:P18 ?img.
      bind(concat('\\n', '[[File:', substr(str(?img), 52, 400), '|250px]]') as ?idBild)
    }
     # values ?id {  wd:Q21877978 wd:Q671193} 
  }
} AS %sub 

WHERE {
  INCLUDE %sub.
  OPTIONAL {
    ?id p:P2044/psn:P2044/wikibase:quantityAmount ?height # using normalized quantity in meter
    bind(concat(' (', str(?height),' m)') AS ?h)
  }
      
  #OPTIONAL { ?id wdt:P18 ?img.}

  OPTIONAL {
    ?link schema:about ?id ;
          schema:isPartOf <https://de.wikipedia.org/> ;
          schema:name ?article_name . 
    bind(concat('[[:de:', ?article_name, '|', ?idLabel, ']]') AS ?article_with_name)
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'de' .
                           ?id rdfs:label ?idLabel . }
  bind(coalesce(?article_with_name, ?idLabel, '') as ?article)

}
GROUP BY ?id ?geo ?article

Mountains of range

I think this should work. I changed a few things : used the now existing normalized form for the quantity that exists in the export, I deleted some of the "if bound" by moving stuffs if the "optional" clauses they are bound to. I removed the "group by ?h" in the main query because this made impossible to aggregate the height. I used "coalesce" which is a simple way to avoid "if(bound", the first value chosen is the first bound in the list of parameters. Also to ensure one not bound variable has a value anyway, because if you use a function on an unbound value in sparql the result is empty, "coalesce" is also a nice way to do that, like assinging the empty string to an unbound value. author  TomT0m / talk page 13:37, 29 November 2024 (UTC)

Oh, also I used "schema:name" to get the title of the article, this avoid parsing the url. author  TomT0m / talk page 13:42, 29 November 2024 (UTC)

@TomT0m: thanks, makes it easier. Thanks also for explaining. Duplicates are gone now. But gone is also the height (cannot figure out, where ?conversion comes from) and the links back to WP (if any). --Herzi Pinki (talk) 14:33, 29 November 2024 (UTC)

@Herzi Pinki: Sorry did not pay enough intention, the "?conversion" is indeed buggy, was from your own query, forgot to remove it. I'm investigating for the rest. author  TomT0m / talk page 15:10, 29 November 2024 (UTC)

@Herzi_Pinki: This one (seems to) work as intended :

#defaultView:Map
#title:Mountains of range


SELECT DISTINCT ?id ?geo
  (
    concat(
      ?article, 
      coalesce(SAMPLE(?h),""), 
      '\\n', 
      '[[File:Wikidata-logo S.svg|16px|link=d:', 
       substr(str(?id),32,13),
      ']]', 
      coalesce(SAMPLE(?idBild),"")
    ) as ?description
  )
  ('mountain' as ?marker_symbol) 
  ('small' as ?marker_size)
  ?article 
WITH {
  SELECT ?id ?geo ?idBild
    WHERE { #(SAMPLE(?img) as ?img_sample)
    ?id wdt:P4552* wd:Q592910.
    ?id wdt:P31 wd:Q8502.
    ?id wdt:P625 [].
    ?id wdt:P625 ?geo.
    OPTIONAL {
      ?id wdt:P18 ?img.
      bind(concat('\\n', '[[File:', substr(str(?img), 52, 400), '|250px]]') as ?idBild)
    }
     # values ?id {  wd:Q21877978 wd:Q671193} 
  }
} AS %sub 

WHERE {
  INCLUDE %sub.
  OPTIONAL {
    ?id p:P2044/psn:P2044/wikibase:quantityAmount ?height # using normalized quantity in meter
    bind(concat(' (', str(?height),' m)') AS ?h)
  }
      
  #OPTIONAL { ?id wdt:P18 ?img.}

  OPTIONAL {
    ?link schema:about ?id ;
          schema:isPartOf <https://de.wikipedia.org/> ;
          schema:name ?article_name . 
  }
  #optional {
  #  ?id rdfs:label ?idLabel filter (langmatches(lang(?idLabel), "de")) .
  #}
  SERVICE wikibase:label { 
    bd:serviceParam wikibase:language "de".
    ?id rdfs:label ?idLabel
  }
  bind(
    coalesce(
      concat('[[:de:', ?article_name, '|', ?idLabel, ']]'),      # we have both ?article_name and ?idLabel
      concat('[[:de:', ?article_name, '|', ?article_name, ']]'), # we have only ?article_name
      ?idLabel,                                                  #         only ?idLabel
      ""                                                         #         none
    )
    as ?article
  )
  

}
GROUP BY ?id ?geo ?article ?article_name ?article_with_name

Mountains of range

We have to pay attention to which variable is bound inside the "optional" clauses, especially using the query service. It's possible to avoid using it using the commented optional, especially if you use only one language (it's customary to use english as a default, or "mul" now even). But I think this form is not too bad :) author  TomT0m / talk page 15:37, 29 November 2024 (UTC)

thanks a lot, works as a charm now. best --Herzi Pinki (talk) 20:39, 29 November 2024 (UTC)

P549 duplication

Hi guys !

It's been a while since I last bugged you with my requests.

I've been associating items of astronomers with their scientific articles items for a while now, and I noticed that several Mathematics Genealogy Project ID (P549) items are duplicates of existing ones (example : 1 and 2). I've been trying to automatically identify these duplicates for some time, but, of course, my query attempts on the subject time out (see below), with or without limits, with or without more identifiers for the non-549 items, etc..

Do you have any ideas on what could be done?

SELECT DISTINCT ?item1 ?item2 ?l1 ?l2
WHERE
{
	?item1 wdt:P31 wd:Q5 ;
	      wdt:P549 [] ;
          wikibase:identifiers 1 ;
          rdfs:label ?l1 .
   ?item2 wdt:P31 wd:Q5 ;
          wdt:P496 [] ;
          rdfs:label ?l2 .
       FILTER(LANG(?l1) IN ("en")).
       FILTER(LANG(?l2) IN ("en")).
  MINUS{?item2 wdt:P549 [].} .
       FILTER(?l1 = ?l2).
}

Try it!

Simon Villeneuve (talk) 12:07, 5 December 2024 (UTC)

@Simon Villeneuve: Quitte à procéder en deux temps c'est plus simple, on peut commencer par lister les items avec des homonymes, et trouver les homonymes pour chacun d'entre eux. On trouve environ 8000 noms, certains avec des dizaines d'homonymes :

SELECT distinct ?item1 ?l1 WHERE {
  ?item1 wdt:P31 wd:Q5;
         wdt:P549 _:b101;
         wikibase:identifiers 1 ;
         rdfs:label ?l1.

    ?itemHomonym wdt:P31 wd:Q5;
           wdt:P496 _:b102;
           rdfs:label ?l1.
    FILTER((STR(?itemHomonym)) > (STR(?item1)))
    MINUS { ?itemHomonym wdt:P549 _:b103. }

  FILTER(LANG(?l1) IN("en"))
}

Try it!

Il n'est pas nécessaire de filter sur "?l1=?l2" on peut juste réutiliser "?l1" dans les deux motifs de graphes .

Je prépare un notebook pour aider à la suite si nécessaire. author  TomT0m / talk page 15:26, 5 December 2024 (UTC)

Coucou,
Merci !
C'est la première fois que je vois une notation comme _:b101. Pourquoi utilises-tu celle-ci plutôt que les crochets ([]) ?
Sinon, en ajoutant ?itemHomonym dans les variables de début, j'arrive à 55 000 résultats.
Penses-tu que ces possibles homonymes méritent d'être listés quelque part ? Si oui, où ? Simon Villeneuve (talk) 16:24, 5 December 2024 (UTC)

Salut, j'ai fait un notebook : https://public-paws.wmcloud.org/User:TomT0m/Nobels/Duplicate_mathematicians.ipynb (tout n'est pas listé, il y en a trop et si on affiche tout ça ne fonctionne pas, il faut recalculer dans son espace perso et virer ce que j'ai mis pour n'afficher que le début et la fin). Voir le tableau à la fin du notebook, il faudrait peut-être

Les syntaxes pour les nœuds blancs c'est pour nommer les nœuds et c'est juste parce que j'ai cliqué sur le diamand pour formater la requête dans l'éditeur, ça n'a pas d'importance particulière. Ça permet éventuellement d'identifier un nœud blanc dans la requête pour le réutiliser je crois (?). author  TomT0m / talk page 19:22, 5 December 2024 (UTC)

Comparison of existing native label with name in native language

I am tracking here Wikidata:WikiProject Badminton/Name missing names in native languages. Nevertheless there are sometimes already labels in the native language and I would like to add the native label from the label in the native language. Means for instance: for occupation (P106) badminton player (Q13141064): filter exists label in Japanese, filter not exists name in native language (P1559). Thanks in advance very much for the help. Florentyna (talk) 22:09, 5 December 2024 (UTC)

An attempts : @Florentyna:

select ?item ?nameInLang (lang(?nameInLang) as ?langCode){
  ?item wdt:P106 wd:Q13141064 .
  
  ?item wdt:P1559 ?nameInLang .
  
  minus {
    ?item rdfs:label ?nameInLang .
  }
  minus {
   ?item skos:altLabel ?nameInLang . # filter out those with the name present as an alias
  }
  
}

Try it!

It's not perfect of course, maybe there are languages here that are not possible to put as Wikidata item labels. Could not find how to filter them out in pure sparql.

So here is a second attempt, so a longer one, but the approach seems not to work good enough in performances :

select ?item ?nameInLang (lang(?nameInLang) as ?langCode){
  ?item wdt:P106 wd:Q13141064 .
  ?item wdt:P1559 ?nameInLang  .
  minus {
    ?item rdfs:label ?nameInLang .
  }
  minus {
   ?item skos:altLabel ?nameInLang . # filter out those with the name present as an alias
  }
  
  filter (lang(?nameInLang) = ?labelCode).
  values ?labelCode{
  "aa" "aae" "ab" "abs" "ace" "acf" "acm" "ady" "ady-cyrl" "aeb" "aeb-arab" "aeb-latn" "af" "agq" "ak" "aln" "als" "alt" "am" "ami" "an" "ang" "ann" "anp" "apc" "ar" "arc" "arn" "arq" "ary" "arz" "as" "ase" "ast" "atj" "av" "avk" "awa" "ay" "az" "azb" "ba" "bag" "ban" "ban-bali" "bar" "bas" "bat-smg" "bax" "bbc" "bbc-latn" "bbj" "bcc" "bci" "bcl" "bdr" "be" "be-tarask" "be-x-old" "bew" "bfd" "bg" "bgc" "bgn" "bh" "bho" "bi" "bjn" "bkc" "bkh" "bkm" "blk" "bm" "bn" "bo" "bpy" "bqi" "bqz" "br" "brh" "bs" "btm" "bto" "bug" "bxr" "byv" "ca" "cak" "cal" "cbk-zam" "ccp" "cdo" "cdo-hant" "cdo-latn" "ce" "ceb" "ch" "chn" "cho" "chr" "chy" "ckb" "cnh" "co" "cps" "cpx" "cpx-hans" "cpx-hant" "cpx-latn" "cr" "crh" "crh-cyrl" "crh-latn" "crh-ro" "cs" "csb" "cu" "cv" "cy" "da" "dag" "de" "de-at" "de-ch" "de-formal" "dga" "din" "diq" "dsb" "dtp" "dty" "dua" "dv" "dz" "ee" "efi" "egl" "el" "eml" "en" "en-ca" "en-gb" "en-us" "eo" "es" "es-419" "es-formal" "et" "eto" "etu" "eu" "ewo" "ext" "fa" "fat" "ff" "fi" "fit" "fiu-vro" "fj" "fkv" "fmp" "fo" "fon" "fr" "frc" "frp" "frr" "fur" "fy" "ga" "gaa" "gag" "gan" "gan-hans" "gan-hant" "gcf" "gcr" "gd" "gl" "gld" "glk" "gn" "gom" "gom-deva" "gom-latn" "gor" "got" "gpe" "grc" "gsw" "gu" "guc" "gur" "guw" "gv" "gya" "ha" "hak" "hak-hans" "hak-hant" "hak-latn" "haw" "he" "hi" "hif" "hif-latn" "hil" "hke" "hno" "ho" "hr" "hrx" "hsb" "hsn" "ht" "hu" "hu-formal" "hy" "hyw" "hz" "ia" "iba" "ibb" "id" "ie" "ig" "igl" "ii" "ik" "ike-cans" "ike-latn" "ilo" "inh" "io" "is" "isu" "isv-cyrl" "isv-latn" "it" "iu" "ja" "jam" "jbo" "jut" "jv" "ka" "kaa" "kab" "kai" "kbd" "kbd-cyrl" "kbp" "kcg" "kea" "ker" "kg" "kge" "khw" "ki" "kiu" "kj" "kjh" "kjp" "kk" "kk-arab" "kk-cn" "kk-cyrl" "kk-kz" "kk-latn" "kk-tr" "kl" "km" "kn" "knc" "ko" "ko-kp" "koi" "kr" "krc" "kri" "krj" "krl" "ks" "ks-arab" "ks-deva" "ksf" "ksh" "ksw" "ku" "ku-arab" "ku-latn" "kum" "kus" "kv" "kw" "ky" "la" "lad" "lb" "lbe" "lem" "lez" "lfn" "lg" "li" "lij" "liv" "lki" "lld" "lmo" "ln" "lns" "lo" "loz" "lrc" "lt" "ltg" "lua" "lus" "luz" "lv" "lzh" "lzz" "mad" "mag" "mai" "map-bms" "mcn" "mcp" "mdf" "mg" "mh" "mhr" "mi" "min" "mk" "ml" "mn" "mnc" "mnc-latn" "mnc-mong" "mni" "mnw" "mo" "mos" "mr" "mrh" "mrj" "ms" "ms-arab" "mt" "mua" "mui" "mus" "mwl" "my" "myv" "mzn" "na" "nah" "nan" "nan-hani" "nan-hant" "nan-latn-pehoeji" "nan-latn-tailo" "nap" "nb" "nds" "nds-nl" "ne" "new" "ng" "nge" "nia" "nit" "niu" "nl" "nl-informal" "nla" "nmg" "nmz" "nn" "nnh" "nnz" "no" "nod" "nog" "nov" "nqo" "nr" "nrm" "nso" "nup" "nv" "ny" "nyn" "nyo" "nys" "oc" "ojb" "olo" "om" "or" "os" "osa-latn" "ota" "pa" "pag" "pam" "pap" "pap-aw" "pcd" "pcm" "pdc" "pdt" "pfl" "pi" "pih" "pl" "pms" "pnb" "pnt" "prg" "ps" "pt" "pt-br" "pwn" "qu" "quc" "qug" "rgn" "rif" "rki" "rm" "rmc" "rmf" "rmy" "rn" "ro" "roa-rup" "roa-tara" "rsk" "ru" "rue" "rup" "ruq" "ruq-cyrl" "ruq-latn" "rut" "rw" "rwr" "ryu" "sa" "sah" "sat" "sc" "scn" "sco" "sd" "sdc" "sdh" "se" "se-fi" "se-no" "se-se" "sei" "ses" "sg" "sgs" "sh" "sh-cyrl" "sh-latn" "shi" "shi-latn" "shi-tfng" "shn" "shy" "shy-latn" "si" "simple" "sjd" "sje" "sju" "sk" "skr" "skr-arab" "sl" "sli" "sm" "sma" "smj" "smn" "sms" "sn" "so" "sq" "sr" "sr-ec" "sr-el" "srn" "sro" "srq" "ss" "st" "stq" "sty" "su" "sv" "sw" "syl" "szl" "szy" "ta" "tay" "tcy" "tdd" "te" "tet" "tg" "tg-cyrl" "tg-latn" "th" "ti" "tig" "tk" "tl" "tly" "tly-cyrl" "tn" "to" "tok" "tpi" "tpv" "tr" "tru" "trv" "ts" "tt" "tt-cyrl" "tt-latn" "ttj" "tum" "tvu" "tw" "ty" "tyv" "tzm" "udm" "ug" "ug-arab" "ug-latn" "uk" "ur" "uz" "uz-cyrl" "uz-latn" "ve" "vec" "vep" "vi" "vls" "vmf" "vmw" "vo" "vot" "vro" "vut" "wa" "wal" "war" "wes" "wls" "wo" "wuu" "wuu-hans" "wuu-hant" "wya" "xal" "xh" "xmf" "xsy" "yas" "yat" "yav" "ybb" "yi" "yo" "yrl" "yue" "yue-hans" "yue-hant" "za" "zea" "zgh" "zgh-latn" "zh" "zh-classical" "zh-cn" "zh-hans" "zh-hant" "zh-hk" "zh-min-nan" "zh-mo" "zh-my" "zh-sg" "zh-tw" "zh-yue" "zu" "mul"
}

 hint:Prior hint:runLast true # run this at last, the query optimizer gets confused and it is slow
}

Try it!

this filters a few for which you won't find a languages to put a label to the item, not much. author  TomT0m / talk page 14:20, 6 December 2024 (UTC)

Thanks a lot @TomT0m:, version 1 works quite good, for version 2 I get zero results. Florentyna (talk) 16:25, 6 December 2024 (UTC)

@Florentyna: I messed up, I corrected the second one it works now :) author  TomT0m / talk page 16:53, 6 December 2024 (UTC)

Swiss towns with deprecated instance of (P31)

In order to determine the dimension of the problem mentioned at Wikidata_talk:WikiProject_Switzerland#Ranks_for_former_Amtsbezirk, I would like to know how many items with instance of (P31) as municipality of Switzerland (Q70208) have values marked as deprecated for located in the administrative territorial entity (P131). Thank you. -- Flominator (talk) 04:01, 6 December 2024 (UTC)

@Flominator: 411 :

select distinct ?item ?itemLabel{
  ?item p:P31 [ps:P31/wdt:P279* wd:Q70208].
  ?item p:P131 [ ps:P131 ?loc ; wikibase:rank wikibase:DeprecatedRank ] . 
        
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],mul,en". }
}

Try it!

author  TomT0m / talk page 12:16, 6 December 2024 (UTC)

items from mi.wiki without en labels / descriptions

Items from mi.wiki were imported a long time ago. Some of them never received en labels on descriptions. I would like a query that finds items with a link to mi.wiki and a label in mi but missing either an en label or en description. Stuartyeates (talk) 03:18, 7 December 2024 (UTC)

@Stuartyeates:

select ?item ?article ?title ?label ?description (bound(?description) as ?has) {
  ?article schema:about ?item ;
           schema:isPartOf <https://mi.wikipedia.org/> ;
           schema:name ?title .
  
  #minus {
    SERVICE wikibase:label { 
      bd:serviceParam wikibase:language "mi".
      ?item rdfs:label ?label .
      ?item schema:description ?description
    }
  filter (!(bound(?label) && bound(?description))) .
}

Try it!

I think there are no missing label, if the query is right. There is a tool to do this in a user friendly way, terminator by @Magnus Manske: but it seems to be broken, see an attempt to set it up for your question. author  TomT0m / talk page 10:28, 7 December 2024 (UTC)

Adding located in the administrative territorial entity in a query

I have this query https://w.wiki/CLJV but need in addition located in the administrative territorial entity (P131) Pmt (talk) 17:24, 7 December 2024 (UTC)

SELECT DISTINCT ?item ?itemLabel ?administration ?admLabel WHERE {
  {
    SELECT DISTINCT * WHERE {
      ?item p:P31 ?statement0.
      ?statement0 (ps:P31/(wdt:P279*)) wd:Q11315.
      ?item wdt:P17 wd:Q20;
            wdt:P131/wdt:P279* ?administration.
    }
    LIMIT 1000
  }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE], en, sv, nb" .
    ?item rdfs:label ?itemLabel . ?item schema:description ?description .
    ?administration rdfs:label ?admLabel .
  }
}

Try it!

Here You go, regards, Piastu (talk) 15:24, 8 December 2024 (UTC)

Retrieve multi-field values in a single cell

Hi! I have this query https://w.wiki/CNu4 , but the field ?job is multi-values. I would like to retrieve all values in ?job in a sigle cell separating them with a pipete (|). I have trying with this query but doesn't work. Any ideia? Thanks

SELECT ?item ?itemLabel ?genre ?genreLabel ?birthdate (GROUP_CONCAT(?jardueraLabel; SEPARATOR="|") AS ?jarduerak) ?herrialdeaLabel
WHERE 
{
  ?item wdt:P31 wd:Q5. # instance of (P31) human (Q5).
  ?item wdt:P10069 ?TabakaleraID. # with a Tabakalera Identities ID (P10069)
  ?item wdt:P21 ?genre.
  ?item wdt:P569 ?birthdate.
  ?item wdt:P106 ?jarduera.
  ?item wdt:P27 ?herrialdea.

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

GROUP BY ?item ?itemLabel ?genre ?genreLabel ?birthdate ?herrialdeaLabel
}

Try it!

 – The preceding unsigned comment was added by Lmerice (talk • contribs).

@Lmerice: I tried several stuffs with the Wikidata end point but trying to get all the labels seems to make the query timeout for some reason. There are several possibilities depending on what you are trying to do, like forgetting about the label and just get the items at first and get the label in a second time. For example ListeriaBot for WikidataList does not require getting the labels for them to be displayed in the end.

With another endpoint, qlever, it can be quite fast however, but we have to rewrite a bit because there is no label service, see this query on qlever, in "es" (and in "eu" language as you might prefer : https://qlever.cs.uni-freiburg.de/wikidata/9pxzcQ?exec=true notice the changed langtags) author  TomT0m / talk page 15:33, 11 December 2024 (UTC)

@Lmerice: You can try this one, You don't have to search for humans (Q5), as P10069 (TabakaleraID) is only for them (i think so, cause only humans in WD have this ID):

SELECT DISTINCT ?item ?label ?TabakaleraID ?gender ?genderLabel ?birthdate (GROUP_CONCAT(DISTINCT ?joblabel; SEPARATOR="paste-separator-here") AS ?jobs) {
  {
    SELECT * {
      ?item wdt:P10069 ?TabakaleraID;
            wdt:P21 ?gender;
            wdt:P569 ?birthdate;
            wdt:P106 ?job.
      
      SERVICE wikibase:label {
        bd:serviceParam wikibase:language "[AUTO_LANGUAGE]" .
        ?job rdfs:label ?joblabel .
      }
    }
  }
  OPTIONAL {?gender rdfs:label ?genderLabel .
            FILTER(LANG(?genderLabel) = "[AUTO_LANGUAGE]")}
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE]" .
    ?item rdfs:label ?label .
#     ?gender rdfs:label ?genderLabel . #for some reason slower than 'optional-two-lines' before service
  }
}
GROUP BY ?item ?label ?TabakaleraID ?gender ?genderLabel ?birthdate

Try it!

, regards, Piastu (talk) 17:07, 11 December 2024 (UTC) (why pipe as separator is a problem? Piastu (talk) 17:08, 11 December 2024 (UTC))

thanks, Just what I need! Lmerice (talk) 11:40, 12 December 2024 (UTC)

Basketball players of single nationality, and no other professions

Let's assume I would like to mass describe Polish basketball players as "Polish basketball player". I'm not sure if there is a way to create this query the way I want it. I can create a query for instance of Human, nationality Poland, gender male, profession basketball player (https://w.wiki/CPCN). But is there a way to exclude results that have more than one nationality, and more than one profession? Example of result I would like to avoid: https://www.wikidata.org/wiki/Q9369971 (more Ukrainian than Polish), https://www.wikidata.org/wiki/Q24074 (multi sport athlete whose main sport was hockey). Tupungato (talk) 20:30, 11 December 2024 (UTC)

@Tupungato: Several actually : one is using the template {{Unique values}}, using it twice as something like {{Unique values|P106|?item}} in the following gives

select ?item {
  ?item wdt:P106 wd:Q3665646 .
  ?item wdt:P27 wd:Q36 .
  
     ?item wdt:P106  ?item_P106_val1
                        filter not exists { 
                          ?item wdt:P106  ?item_P106_val2
                        filter (?item_P106_val1 != ?item_P106_val2) .
                        }
  
     ?item wdt:P27  ?item_P27_val1
                        filter not exists { 
                          ?item wdt:P27  ?item_P27_val2
                        filter (?item_P27_val1 != ?item_P27_val2) .
                        }
}

Try it!

one over one is using a "group by / count" construction that counts the statements

select ?item {
  ?item wdt:P106 wd:Q3665646 .
  ?item wdt:P27 wd:Q36 .
  
  ?item p:P106 ?stmtCountry .
  ?item p:P27 ?stmtProfession .
  
} group by ?item having (count(?stmtCountry) = 1 && count(?stmtProfession) = 1)

Try it!

Both works and seem to give the same results. Maybe you would like to look at country for sport (P1532) also. author  TomT0m / talk page 21:28, 11 December 2024 (UTC)

Thank you for your help. I'll try these tomorrow. Problem with Property:P1532 (country for sport) is that it is rarely used, mostly in high profile contemporary athletes. Tupungato (talk) 21:43, 11 December 2024 (UTC)

Is there a way to Query items that have no description in certain language?

I've been dabbling in Query tool and QuickStatements to mass edit items. Adding mostly descriptions. I'm doing "safe" items where potential overwriting does no damage. For example adding Polish description "anglican church in Manchester" for anglican churches in Manchester. I would also like to have a look at some batches of items where most popular items may already have description that might be more meaningful. Is there a way to use query tool to generate a list/CSV of object of certain instance, but only those that have no description in certain language? For example instances of Q18618819 (national park of Australia) that have no Polish description (Dpl in QuickStatements commands)?

Or, to look at the issue from other side, is there a way to prevent QuickStatements from overwriting descriptions in items that already have description? --Tupungato (talk) 08:50, 12 December 2024 (UTC)

Ok, I figured it out myself :) Here is a query for rivers in Iceland that miss Dutch description.

select ?item {
  ?item wdt:P31 wd:Q4022 .
  ?item wdt:P17 wd:Q189 .
  
  FILTER(NOT EXISTS {
?item schema:description ?itemdesc_nl.
    FILTER(LANG(?itemdesc_de) = "nl") # with missing Dutch description
})
}

Try it!

Tupungato (talk) 12:41, 13 December 2024 (UTC)

Map of people died by Mafia

Hello there, I'm trying to design a map as told. I guess the relation is "killed by instance of human member of (collect mafia organizations)" I just arrived at map of people murdered. Anyone can help going further? Thanks in advance. PS: the better result would be a layered map by mafia type organization, similar to economists died in that era. Tassoman (talk) 21:03, 12 December 2024 (UTC)

@Tassoman: An attempt :

# People died by homicide on a map
#defaultView:Map

SELECT distinct ?victim ?name ?deathplace ?coord ?date ?mafiaLabel ?murderer 
       ?murdererLabel 
       (?mafiaLabel as ?layer) # color by mafia
WHERE
{
  ?victim wdt:P31 wd:Q5;
          wdt:P1196 wd:Q149086;
          wdt:P570 ?date;
          wdt:P20 ?place .
  ?victim rdfs:label ?name FILTER (lang(?name) = "it") .
  ?place wdt:P625 ?coord .
  ?place rdfs:label ?deathplace FILTER (lang(?deathplace) = "it") .
  
  #?victim wdt:P157 ?org_or_member .
  
  # any organization of this type
  ?mafia wdt:P31/wdt:P279* ?type .
  values ?type {
    wd:Q18550    # mafia
    wd:Q1788992  # crim. org.
  }
  
  # Two possibilities here, handled in either branch of the following "union" 
  {
    ?murderer wdt:P463 ?mafia .  # The murderer is a member of some crim. org
    ?victim wdt:P157 ?murderer .

  } union {
    ?victim wdt:P157 ?mafia .    # The whole organisation is noted as murderer
  }                             
  
  service  wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],mul,en". }
}
ORDER BY DESC(?date)

Try it!

It's layered by criminal org. not by organisation type as the classification of mafias is probably not an easy topic. Actually to get results I had to generalize to "any criminal org.". I don' really want to get into the specifics myself. author  TomT0m / talk page 15:44, 13 December 2024 (UTC)

Hello there, thank you for your example. I got better understanding of SparkQL. I also discovered that criminal organization have country as a regular attribute. But it's impossible to distinguish between terroristic organization and mafia organization. So your example takes in political terroristic organizations also, a little too much but still interesting. Tassoman (talk) 18:56, 13 December 2024 (UTC)