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 1.1 Query Language (Q32146616) queries can be requested. Please provide feedback if a query is written for you.

For sample queries, see Examples. 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.

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 2020/05.

Release year of the video gamesEdit

Hello. I am trying to query all the video games (Q7889) with only their earliest release years. For example Final Fantasy VII has release years of 2013, 2015, 2016, 1997 and 1998, so I am only interested of the 1997 one. How to accomplish this? Thanks

A simple solution:
SELECT DISTINCT ?videogame ?videogameLabel ?earliest_release_date
{
  ?videogame wdt:P31/wdt:P279* wd:Q7889. # videogames
  ?videogame wdt:P577 ?earliest_release_date.
  FILTER NOT EXISTS
  {
    ?videogame wdt:P577 ?release_date.
    FILTER (?release_date < ?earliest_release_date)
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! And a solution somewhat faster:
SELECT ?videogame ?videogameLabel ?earliest_release_date
WITH
{
  SELECT DISTINCT ?videogame
  WHERE
  {
    ?videogame wdt:P31/wdt:P279* wd:Q7889. # videogames
  }
} AS %get_games
WITH
{
  SELECT ?videogame ?earliest_release_date
  WHERE
  {
    INCLUDE %get_games
    ?videogame wdt:P577 ?earliest_release_date.
    FILTER NOT EXISTS
    {
      ?videogame wdt:P577 ?release_date.
      FILTER (?release_date < ?earliest_release_date)
    }
  }
} AS %get_release_date
WHERE
{
  INCLUDE %get_release_date
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Dipsacus fullonum (talk)

Select all relations with labels (and also knowns) and subject values also with labels for a specific subjectEdit

Hi,

I need this selection with labels and also knowns for predicate and object. But i need also the values of predicate and object.


SELECT * WHERE {wd:Q15 ?predicate ?object. }

Thank you.

@Jan.zhouf: Your code already gives you all labels, aliases, statements and more. What is you want that you don't already have? --Dipsacus fullonum (talk) 00:35, 7 May--Jan.zhouf (talk) 17:32, 20 May 2020 (UTC)--Jan.zhouf (talk) 17:32, 20 May 2020 (UTC) 2020 (UTC)
@Dipsacus fullonum: Hi, when I sent this request to https://query.wikidata.org/sparql, I get only this: {'head': {'vars': ['predicate', 'object']}, 'results': {'bindings': [{'predicate': {'type': 'uri', 'value': 'http://www.wikidata.org/prop/direct-normalized/P646'}, 'object': {'type': 'uri', 'value': 'http://g.co/kg/m/0dg3n1'}}, {'predicate': {'type': 'uri', 'value': 'http://www.wikidata.org/prop/direct-normalized/P906'}, 'object': {'type': 'uri', 'value': 'http://libris.kb.se/resource/auth/138939'}}, {'predicate': {'type': 'uri', 'value': 'http://www.wikidata.org/prop/direct-normalized/P1566'}, (truncated)

I need also label and also knowns in Czech language for example for http://www.wikidata.org/prop/direct-normalized/P646 (first URI in response)

Jan.zhouf (talk) 17:32, 20 May 2020 (UTC)
@Jan.zhouf: I don't understand the request then. The query you wrote above gives all relations with Q15 as subject including Czech label and aliases. --Dipsacus fullonum (talk) 18:26, 20 May 2020 (UTC)
@Dipsacus fullonum: I need also labels for the properties and i do not see it in the response. --Jan.zhouf (talk) 18:41, 20 May 2020 (UTC)
@Jan.zhouf: Do you mean like this? The following query lists all claims and directclaims for Q15 with Czech labels and aliases for the claims, but no labels or aliases for Q15 itself as I thought it should.
SELECT ?predicate ?propertyLabel ?propertyAltLabel ?object
WHERE
{
  wd:Q15 ?predicate ?object.
  ?property (wikibase:claim | wikibase:directClaim) ?predicate.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "cs". }
}
Try it!

Same birth/death dateEdit

Hi all! I would like a query searching all the cases in which at least two items regarding humans ( instance of (P31) human (Q5)):

Let me know if it is too complex! --Epìdosis 08:06, 15 May 2020 (UTC)

@Epìdosis: I think it is too extensive. There are thousands of human with FAST ID, and each of them will have thousands of other humans with the same birth or death year and ru or uk sitelinks, giving many, many millions of possible results. --Dipsacus fullonum (talk) 08:26, 15 May 2020 (UTC)
@Dipsacus fullonum: That was my doubt :( Maybe would it be possible to start from the third point, or it's still too big? --Epìdosis 08:28, 15 May 2020 (UTC)
@Epìdosis: I am not sure what you mean. The number of results is the same independent of there you start. It is impossible to get that many results from a query. --Dipsacus fullonum (talk) 08:40, 15 May 2020 (UTC)
@Dipsacus fullonum: Applying LIMIT 100 or LIMIT 50 would help? --Epìdosis 08:43, 15 May 2020 (UTC)
@Epìdosis:. Sure. I can easily make samples. Please stand by. --Dipsacus fullonum (talk) 08:45, 15 May 2020 (UTC)
@Epìdosis:. Here is a query which gives a very small subset of the original request with some hundred results:
# ?item1 is human, have FAST ID and English label
# ?item2 is human and sitelink to ruwiki
# ?item1 and ?item2 are born the same date
SELECT ?item1 ?FAST_ID ?label_en ?birth ?item2 ?ruwiki_sitelink
WITH
{
  SELECT ?item1 ?FAST_ID ?label_en
  WHERE
  {
    ?item1 wdt:P31 wd:Q5. #Human
    ?item1 wdt:P2163 ?FAST_ID.
    ?item1 rdfs:label ?label_en.
    FILTER (LANG(?label_en) = "en")
  }
  LIMIT 50
} AS %get_humans_with_FAST_ID
WHERE
{
  INCLUDE %get_humans_with_FAST_ID
  ?item1 wdt:P569 ?birth.
  ?item2 wdt:P569 ?birth.
  FILTER (?item1 != ?item2)
  ?item2 wdt:P31 wd:Q5. #Human
  ?ruwiki_sitelink schema:about ?item2 .
  ?ruwiki_sitelink schema:isPartOf <https://ru.wikipedia.org/>.
}
Try it! --Dipsacus fullonum (talk) 09:03, 15 May 2020 (UTC)
@Dipsacus fullonum: Great! This will be very useful, thank you so much! --Epìdosis 09:10, 15 May 2020 (UTC)
@Dipsacus fullonum: Last idea: how can I add "if ?item1 has date of death (P570), ?item2 should also have the same date of death (P570)"? --Epìdosis 09:48, 15 May 2020 (UTC)
@Epìdosis: The simple way is to add ?item1 wdt:P570 ?death. ?item2 wdt:P570 ?death. and increase the LIMIT in the subquery like here:
# ?item1 is human, have FAST ID and English label
# ?item2 is human and sitelink to ruwiki
# ?item1 and ?item2 are born and died the same date
SELECT ?item1 ?FAST_ID ?label_en ?birth ?death ?item2 ?ruwiki_sitelink
WITH
{
  SELECT ?item1 ?FAST_ID ?label_en
  WHERE
  {
    ?item1 wdt:P31 wd:Q5. #Human
    ?item1 wdt:P2163 ?FAST_ID.
    ?item1 rdfs:label ?label_en.
    FILTER (LANG(?label_en) = "en")
  }
  LIMIT 1000
} AS %get_humans_with_FAST_ID
WHERE
{
  INCLUDE %get_humans_with_FAST_ID
  ?item1 wdt:P569 ?birth.
  ?item2 wdt:P569 ?birth.
  ?item1 wdt:P570 ?death.
  ?item2 wdt:P570 ?death.
  FILTER (?item1 != ?item2)
  ?item2 wdt:P31 wd:Q5. #Human
  ?ruwiki_sitelink schema:about ?item2 .
  ?ruwiki_sitelink schema:isPartOf <https://ru.wikipedia.org/>.
}
Try it! But that will mostly find cases where the precision of the dates are year, so the persons was born and died the same year on unspecified dates. Is that good enough or should only the few cases with exact dates be allowed? --Dipsacus fullonum (talk) 10:13, 15 May 2020 (UTC)

@Dipsacus fullonum: I edited your last query a bit to obtain the following:

# ?item1 is the imported one
# ?item2 is human and sitelink to ruwiki
# ?item1 and ?item2 are born the same date
SELECT ?item1 ?label_en ?birthyear ?deathyear ?item2 ?label_ru
WITH
{
  SELECT ?item1 ?FAST_ID ?label_en
  WHERE
  {
    #VALUES ?item1 { wd:??? } .
    ?item1 p:P214 [ps:P214 ?viaf ; prov:wasDerivedFrom [pr:P248 wd:Q3294867] ] .
    ?item1 rdfs:label ?label_en .
    FILTER(LANG(?label_en) = "en")
    FILTER(CONTAINS(?label_en,"vich"))
  }
  #LIMIT 20
} AS %get_humans_with_FAST_ID
WHERE
{
  INCLUDE %get_humans_with_FAST_ID
  ?item1 wdt:P569 ?birth.
  ?item2 wdt:P569 ?birth.
  BIND(str(YEAR(?birth)) AS ?birthyear)
  ?item1 wdt:P570 ?death.
  ?item2 wdt:P570 ?death.
  BIND(str(YEAR(?death)) AS ?deathyear)
  FILTER (?item1 != ?item2)
  ?item2 wdt:P31 wd:Q5.
  ?ruwiki_sitelink schema:about ?item2 .
  ?ruwiki_sitelink schema:isPartOf <https://ru.wikipedia.org/>.
  { ?item2 wdt:P27 wd:Q159. } UNION { ?item2 wdt:P27 wd:Q15180 . } UNION { ?item2 wdt:P27 wd:Q34266 . }
  ?item2 rdfs:label ?label_ru.
  FILTER (LANG(?label_ru) = "ru")
}
ORDER BY ?label_en

Try it!

last question: would it be possible to order firstly by ?label_en, secondly by ?label_ru: so that, if two rows have the same ?label_en, they appear not in casual order, but according to ?label_ru? --Epìdosis 10:54, 15 May 2020 (UTC)

@Epìdosis: Yes, add ?label_ru to the ORDER clause: ORDER BY ?label_en ?label_ru. You might also want to add the keyword DISTINCT after SELECT to avoid duplicate results for persons with more than one citizenship found by the UNION. --Dipsacus fullonum (talk) 11:33, 15 May 2020 (UTC)
(uri(CONCAT("https://tools.wmflabs.org/quickstatements/index_old.html#v1=MERGE%09", strafter(str(?item1),"y/"), "%09",  strafter(str(?item2),"y/") )) as ?click)

Try it!

@Epìdosis, Dipsacus fullonum: I found this most helpful to identify items to merge (varying some of the criteria or chosing year precision for birth or death date). Above an addition to do a 2-click merge. --- Jura 10:19, 18 May 2020 (UTC)

@Epìdosis, Dipsacus fullonum, Jura1: Maybe it will make sense to create a separate page for sharing queries that can detect duplicate Q5 elements? I searched for pairs of persons that have the same date of birth AND share a place of birth with population lower than a certain threshold. Here is the query for Brazil (in reality I use a bit more complex queries):

SELECT ?person1 ?person1Label ?person2 ?person2Label ?date_of_birth ?place_of_birth WHERE {
  ?place_of_birth wdt:P17 wd:Q155; #in Brazil
    wdt:P1082 ?population.
  FILTER(?population < 100000 ) # for places with huge population, use additional constraints for person1/person2 pair
  ?person1 wdt:P31 wd:Q5; #human
    wdt:P19 ?place_of_birth;
    wdt:P569 ?date_of_birth.
  ?person2 wdt:P31 wd:Q5; #human, empty P31 is also useful here
    wdt:P19 ?place_of_birth;
    wdt:P569 ?date_of_birth.
  FILTER(!(((DAY(?date_of_birth)) = 1 ) && ((MONTH(?date_of_birth)) = 1 ))) # I usually exclude January 1, too many false positives even with 1 day precision
  FILTER( ?person1 != ?person2)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "pt,es,[AUTO_LANGUAGE]". }
  }
  ORDER BY DESC(?date_of_birth)

Try it!

83 pairs of likely duplicates at the moment. I have found and merged hundreds of duplicates with this (or similar) method. I am also dreaming of universal Bayesian approach. --Hwem (talk) 14:34, 23 May 2020 (UTC)

@Hwem: Very good idea, thank you very much! I've added this query and mine to Wikidata:WikiProject Duplicates/Queries. Feel free to add other useful queries! Bye, --Epìdosis 15:14, 23 May 2020 (UTC)

Televisions series, the demonym of their country of origin, their genreEdit

Hello.

I need a query to extract all television series (item id only), the demonym of their country of origin and their genre. I currently have this. The problem with it is that it shows the name of the country of origin, not the demonym. Is it possible to get the demonym, instead of the country name? Thanks. Ahmadtalk 14:07, 16 May 2020 (UTC)

@Ahmad252: In what language do you what the demonym? Your query has labels in Farsi (fa) but I can find no demonyms for countries in Farsi on Wikidata. No country items has demonym (P1549) values in Farsi, and no senses of Farsi lexemes has demonym of (P6271) values for countries. --Dipsacus fullonum (talk) 15:23, 16 May 2020 (UTC)
PS. Well, I wasn't entirely correct. There are demonyms in Farsi for about 5 countries – but hardly enough for use for use in a query. --Dipsacus fullonum (talk) 16:06, 16 May 2020 (UTC)
@Dipsacus fullonum: In Farsi (it's actually a part of a larger effort to add Farsi descriptions to items about movies, video games, TV series and some other works; I'm going to file a bot task request for that). To be honest, I only check the US (that has the Farsi demonym), but that's fine. I will add Farsi demonyms to countries (based on a query, I think there are 181 items that are an instance of a country, so it should be rather easy). Thank you. Ahmadtalk 13:40, 17 May 2020 (UTC)
@Ahmad252: A simple query would be:
SELECT ?item ?demonym ?genreLabel WHERE {
  ?item wdt:P31 wd:Q5398426.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fa". }
  OPTIONAL {
    ?item wdt:P495 ?country_of_origin.
    OPTIONAL {
      ?country_of_origin wdt:P1549 ?demonym.
      FILTER (LANG(?demonym) = "fa").
     }
  }
  OPTIONAL { ?item wdt:P136 ?genre. }
}
Try it! However, that query may timeout with over 50,000 results, so I also made an optimized query with a lot of subqueries:
SELECT ?item ?demonym ?genreLabel
WITH
{
  SELECT ?item
  WHERE
  {
    ?item wdt:P31 wd:Q5398426.
  }
} AS %get_items    
WITH
{
  SELECT ?country
  WHERE
  {
    INCLUDE %get_items
    ?item wdt:P495 ?country.
  }
  GROUP BY ?country
} AS %get_countries
WITH
{
  SELECT ?country ?demonym
  WHERE
  {
    INCLUDE %get_countries
    ?country wdt:P1549 ?demonym.
    FILTER (LANG(?demonym) = "fa")
  }
} AS %get_countries_and_demonyms
WITH
{
  SELECT ?genre
  WHERE
  {
    INCLUDE %get_items
    ?item wdt:P136 ?genre.
  }
  GROUP BY ?genre
} AS %get_genres           
WITH
{
  SELECT ?genre ?genreLabel
  WHERE
  {
    INCLUDE %get_genres
    SERVICE wikibase:label {
      bd:serviceParam wikibase:language "fa".
      ?genre rdfs:label ?genreLabel.
    }
  }
} AS %get_genres_and_labels
WHERE
{
  INCLUDE %get_items
  OPTIONAL {
    ?item wdt:P495 ?country.
    OPTIONAL { INCLUDE %get_countries_and_demonyms }
  }
  OPTIONAL {
    ?item wdt:P136 ?genre.
    OPTIONAL { INCLUDE %get_genres_and_labels }
  }
}
Try it! --Dipsacus fullonum (talk) 12:53, 18 May 2020 (UTC)
Thank you so much! Both queries work perfectly for me. Thanks again. Ahmadtalk 19:25, 18 May 2020 (UTC)

Gender breakdown of organizations (that are "part of" bigger organizations)?Edit

Hey! I am trying to figure out the gender breakdown of board members of an organization (for example, Siemens Q81230). Siemens is part of Dax (Q155718).

a) How do I get all organizations that are "part of" Dax (it says so on the wikidata profile, but it does not always do that) b) How do I get a collective list of all board members of all organizations that are "part of" Dax c) how do I get the gender breakdown of all of these in total?

Thanks SO much! --PPEscientist (talk) 19:38, 16 May 2020 (UTC)

@PPEscientist: a) One method is to look for items with part of (P361) DAX (Q155718) statements with no end time:
SELECT ?item ?itemLabel
{
  ?item p:P361 ?part_of_statement.
  ?part_of_statement a wikibase:BestRank.
  ?part_of_statement ps:P361 wd:Q155718. # Part of DAX
  FILTER NOT EXISTS { ?part_of_statement pq:P582 []. } # There must be no endtime
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de,en". }
}
Try it! That gives 31 results. Another way is look for DAX (Q155718)'s has part (P527) statements:
SELECT ?item ?itemLabel
{
  wd:Q155718 wdt:P527 ?item.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de,en". }
}
Try it! That gives 32 results. You will have to find what list is most correct, and possibly correct the data. --Dipsacus fullonum (talk) 20:24, 16 May 2020 (UTC)
@PPEscientist: b) You can use statements with board member (P3320) and no end time:
SELECT ?item ?itemLabel ?board_member ?board_memberLabel
{
  wd:Q155718 wdt:P527 ?item.
  ?item p:P3320 ?board_member_statement.
  ?board_member_statement a wikibase:BestRank.
  ?board_member_statement ps:P3320 ?board_member.
  FILTER NOT EXISTS { ?board_member_statement pq:P582 []. } # There must be no endtime
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de,en". }
}
Try it! However that only gives 26 results for 32 companies so data is clearly missing. --Dipsacus fullonum (talk) 20:35, 16 May 2020 (UTC)
@PPEscientist: c) Find statements for sex or gender (P21) in the result for b), group by gender and count:
SELECT ?gender ?genderLabel (COUNT(?board_member) AS ?count)
{
  wd:Q155718 wdt:P527 ?item.
  ?item p:P3320 ?board_member_statement.
  ?board_member_statement a wikibase:BestRank.
  ?board_member_statement ps:P3320 ?board_member.
  FILTER NOT EXISTS { ?board_member_statement pq:P582 []. } # There must be no endtime
  OPTIONAL { ?board_member wdt:P21 ?gender. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de,en". }
}
GROUP BY ?gender ?genderLabel
Try it! --Dipsacus fullonum (talk) 20:41, 16 May 2020 (UTC)

Wow that was super quick! Thank you, works perfect. What would you do if the organizations do not have "part of" in their profiles? Is there a workaround to this? --PPEscientist (talk) 20:45, 16 May 2020 (UTC)

Add it with a reference. --Dipsacus fullonum (talk) 20:53, 16 May 2020 (UTC)

Query requestEdit

Hello,

Would it be possible to create a query for all Higher Education Institutions in Canada, including number of students, Faculties, Programs, languages of instructions. I would appreciate your help!

It is limited which data that is available for each institution, but this query gives some of the wanted info:
SELECT ?item ?itemLabel ?students ?employees
{
  ?item wdt:P31/wdt:P279* wd:Q38723. # ?item is a higher education institution
  ?item wdt:P17 wd:Q16. # ?item is in Canada
  OPTIONAL { ?item wdt:P2196 ?students. }
  OPTIONAL { ?item wdt:P1128 ?employees. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,fr". }
}
Try it! --Dipsacus fullonum (talk) 00:01, 17 May 2020 (UTC)

Doubts about a query to obtain disambiguationsEdit

I have the following query:

The following query uses these:

  • Properties: instance of (P31)    , subclass of (P279)    
    1 SELECT DISTINCT ?name ?nameLabel
    2 WHERE
    3 {
    4   ?name wdt:P31/wdt:P279* wd:Q4167410.
    5   FILTER (?name = "FFCC").
    6   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    7 }
    

But for some reason it doesn't return results when it should return FFCC (Q534132). What am I doing wrong?

Another question I have is, is it possible to obtain from a query the articles linked to a disambiguation page? In this case they would be Final Fantasy Crystal Chronicles (Q1470373), Florida Film Critics Circle (Q3074282), Flowery Field Cricket Club (Q5462329), Full Faith and Credit Clause (Q5508034), Fédération française de la course camarguaise (Q3091816), railway (Q22667), Q3091789 and Q17628740.--190.31.205.42 11:48, 17 May 2020 (UTC)

"What am I doing wrong?" The filter is wrong. The variable ?name will never equal the string "FFCC", but will contain items expressed as IRIs like e.g. wd:Q534132. The items have labels (strings with language tags) and sitelinks where you can test for the value "FFCC". For instance a test for an English label will look like this:
SELECT DISTINCT ?name ?nameLabel
WHERE
{
  ?name wdt:P31/wdt:P279* wd:Q4167410.
  ?name rdfs:label "FFCC"@en.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! The answer to the second question is yes. I will return with a query for that later. --Dipsacus fullonum (talk) 12:52, 17 May 2020 (UTC)
Here is a query for the second question as promised:
# 1) Find all items for Wikimedia disambiguation pages with a label "FFCC" in any language.
# 2) Find all Wikimedia pages for the items.
# 3) Find all outgoing links (titles and items) on the disambiguation pages.
SELECT DISTINCT ?item ?article ?site ?title ?endpoint ?link_item ?link_title
WHERE
{
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "www.wikidata.org".
    bd:serviceParam wikibase:api "Generator".
    bd:serviceParam mwapi:generator "search".
    bd:serviceParam mwapi:gsrsearch "inlabel:FFCC".
    bd:serviceParam mwapi:gsrlimit "max".
    ?item wikibase:apiOutputItem mwapi:title.
  }
  ?item rdfs:label ?label.
  FILTER (STR(?label) = "FFCC")
  ?item wdt:P31/wdt:P279* wd:Q4167410.
  ?article schema:about ?item.
  ?article schema:isPartOf ?site.
  ?article schema:name ?title.
  BIND (STRBEFORE(STRAFTER(STR(?site), "https://"), "/") AS ?endpoint)
  OPTIONAL
  {
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:endpoint ?endpoint.
      bd:serviceParam wikibase:api "Generator".
      bd:serviceParam mwapi:generator "links".
      bd:serviceParam mwapi:titles ?title.
      bd:serviceParam mwapi:gpllimit "max".
      bd:serviceParam mwapi:gplnamespace "0".
      ?link_item wikibase:apiOutputItem mwapi:item.
      ?link_title wikibase:apiOutput mwapi:title.
    }
  }
}
Try it! --Dipsacus fullonum (talk) 20:42, 17 May 2020 (UTC)
Thanks for the query, it works pretty well, but how do I get only the articles from the main namespace to be displayed (i.e. excluding discussions, help pages and the like)? I was thinking in adding a FILTER(CONTAINS(?link_title, ":") = false) but it can give false positives.--190.31.205.42 11:10, 18 May 2020 (UTC)
You can modify the MWAPI call to only request links from the article namespace. I have added bd:serviceParam mwapi:gplnamespace "0". to the query. --Dipsacus fullonum (talk) 12:04, 18 May 2020 (UTC)

multiple querrys in oneEdit

What is the correct way to make a query like the following using the or operator?

SELECT ?Argentona ?ArgentonaLabel WHERE {
 ?Argentona wdt:P131 wd:Q11539;
   wdt:P19 wd:Q11539;
   wdt:P20 wd:Q11539;
  wdt:P551 wd:Q11539;
 SERVICE wikibase:label { bd:serviceParam wikibase:language "ca". }
}
SELECT ?Argentona ?ArgentonaLabel WHERE {
  { ?Argentona wdt:P131 wd:Q11539 } union
  { ?Argentona wdt:P19 wd:Q11539 } union
  { ?Argentona wdt:P20 wd:Q11539 } union
  { ?Argentona wdt:P551 wd:Q11539 } 
 SERVICE wikibase:label { bd:serviceParam wikibase:language "ca". }
}

Try it!

This should do it - any item that has P131, P19, P20 or P551 with the specified value. Andrew Gray (talk) 15:18, 18 May 2020 (UTC)

Alumni OxoniensesEdit

A list of every instance of (P31) of biographical article (Q19389637) published in (P1433) Alumni Oxonienses: the Members of the University of Oxford, 1715-1886 (Q19588619), each with their page(s) (P304) and volume (P478) (attached to published in (P1433)) and also main subject (P921). Might need it for a long-term project and possibly more projects. ミラP 15:18, 18 May 2020 (UTC)

@Miraclepine:
SELECT ?item ?itemLabel ?page ?volume ?subject ?subjectLabel
WHERE
{
  ?item wdt:P31 wd:Q19389637.
  ?item p:P1433 ?published_in_statement.
  ?published_in_statement ps:P1433 wd:Q19588619.
  ?published_in_statement a wikibase:BestRank.
  OPTIONAL { ?published_in_statement pq:P304 ?page. }
  OPTIONAL { ?published_in_statement pq:P478 ?volume. }
  OPTIONAL { ?item wdt:P921 ?subject. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Dipsacus fullonum (talk) 15:38, 18 May 2020 (UTC)
@Dipsacus fullonum: It works! I'm planning to move all the Q19588619 to Q19036877 because all the entries at WS are technically those of the edition. ミラP 15:44, 18 May 2020 (UTC)

Also is it possible to restrict the search to a certain page or a range of pages? ミラP 20:47, 18 May 2020 (UTC)

@Miraclepine: Yes:
  • Change OPTIONAL { ?published_in_statement pq:P304 ?page. } to ?published_in_statement pq:P304 "77". to restrict to page 77.
  • Add FILTER (xsd:integer(?page) >= 50 && xsd:integer(?page) <= 100) to restrict to pages in the range 50–100.
--Dipsacus fullonum (talk) 21:20, 18 May 2020 (UTC)
@Dipsacus fullonum: Both work. Thanks. ミラP 21:24, 18 May 2020 (UTC)

Performance issue when a MINUS is added to my queryEdit

Hi everyone.

I have a fast query that looks like this:

 select ?o ?olabel 
 WITH {
 select distinct ?o where {?s   <http://www.wikidata.org/prop/direct/P31>  <http://www.wikidata.org/entity/Q5> .  ?s <http://www.wikidata.org/prop/direct/P106> ?o  } LIMIT 50} AS %examples
 WHERE {
 INCLUDE %examples . OPTIONAL { ?o <http://www.w3.org/2000/01/rdf-schema#label> ?olabel . FILTER (lang(?olabel)='en') }
 }

I have tested this variant for my %examples named query:

 select distinct ?o where {?s   <http://www.wikidata.org/prop/direct/P31>  <http://www.wikidata.org/entity/Q5> .  ?s <http://www.wikidata.org/prop/direct/P106> ?o  .  
                           MINUS { ?o   <http://www.wikidata.org/prop/direct/P31>  <http://www.wikidata.org/entity/Q28640>}  } LIMIT 50

which is fast too.

But when I use the latter query as my %examples named query, then everything becomes VERY slow.

 select ?o ?olabel 
 WITH {
 select distinct ?o where {?s   <http://www.wikidata.org/prop/direct/P31>  <http://www.wikidata.org/entity/Q5> .  ?s <http://www.wikidata.org/prop/direct/P106> ?o  .  
                           MINUS { ?o   <http://www.wikidata.org/prop/direct/P31>  <http://www.wikidata.org/entity/Q28640>}  } LIMIT 50} AS %examples
 WHERE {
 INCLUDE %examples . OPTIONAL { ?o <http://www.w3.org/2000/01/rdf-schema#label> ?olabel . FILTER (lang(?olabel)='en') }
 }

Any help is welcome.

Time between two dates with the format "X years, X months, X days"Edit

Hello,

To illustrate my request, here is a query with the age of the kings of France when they accessed to the throne :

SELECT ?itemLabel ?ccc ?bbb ?dddLabel ?ageInDays
WHERE 
{
  ?item p:P39 ?aaa. 
  ?aaa ps:P39 ?ddd.     
  ?ddd wdt:P279* wd:Q18384454. 
  ?aaa pq:P580 ?bbb. 
  
  ?item wdt:P569 ?ccc. 
  
     BIND(?bbb - ?ccc AS ?ageInDays).

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

Try it!

With this query I've got their exact age in days. However, because it depends of when they were born in the year and/or how many leap years there were during their life, we can't guess their age in the format "X years, X months, X days" only with their age in days.

Is it possible to also show their age with the format "X years, X months, X days" with a SPARQL query ?

@NanarBoulier: Yes, it is possible using the functions YEAR, MONTH, DAY, IF and some arithmetic. In the query below I also use a calculation with a duration of one month to find the number of days in the last full month. It should automatically adjust for leap years. But I give no guarantee that I got everything correct.
SELECT ?item ?itemLabel ?born ?position ?positionLabel ?position_start ?ageInDays ?years ?months ?days
WHERE 
{
  ?item p:P39 ?position_stm.
  ?position_stm ps:P39 ?position.
  ?position wdt:P279* wd:Q18384454.
  ?position_stm pq:P580 ?position_start.  
  ?item wdt:P569 ?born. 
  BIND (?position_start - ?born AS ?ageInDays)

  BIND (YEAR(?born)  AS ?Y1)
  BIND (YEAR(?position_start)  AS ?Y2)
  BIND (MONTH(?born) AS ?M1)
  BIND (MONTH(?position_start) AS ?M2)
  BIND (DAY(?born)   AS ?D1)
  BIND (DAY(?position_start)   AS ?D2)
  BIND (?Y2 - ?Y1 - IF(?M1 > ?M2 || (?M2 = ?M1 && ?D1 > ?D2), 1, 0) AS ?years)
  BIND (IF(?M2 > ?M1 || (?M2 = ?M1 && ?D2 >= ?D1), ?M2 - ?M1, 12 + ?M2 - ?M1) - IF(?D2 < ?D1, 1, 0) AS ?months)
  BIND (IF(?D2 >= ?D1, ?D2 - ?D1, xsd:integer(?position_start - (?position_start - "P1M"^^xsd:duration)) + ?D2 - ?D1) as ?days)

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Dipsacus fullonum (talk) 18:12, 18 May 2020 (UTC)

Query timing out with a long VALUES listEdit

Hi folks! I'm trying to create a search for a list of cultural institutions in a group of 24 counties in Georgia. I did this in SPARQL by creating a VALUES array for those counties, but of course it's timing out. I know it should work logically, but is there any way to do this that's more efficient so it won't time out?

SELECT DISTINCT ?library ?libraryLabel ?located_at_street_address ?coordinate_location ?phone_number ?e_mail_address ?countyLabel WHERE {
VALUES (?located_in_the_administrative_territorial_entity ?countyLabel) { (wd:Q491547 "Union County") (wd:Q503538 "Towns County") (wd:Q503546 "Rabun County") (wd:Q492040 "Lumpkin County") (wd:Q389365 "White County") (wd:Q501096 "Habersham County") (wd:Q498362 "Stephens County") (wd:Q492012 "Hall County") (wd:Q488201 "Banks County") (wd:Q385931 "Franklin County") (wd:Q491301 "Hart County") (wd:Q486838 "Barrow County") (wd:Q486137 "Jackson County") (wd:Q156387 "Madison County") (wd:Q492016 "Elbert County") (wd:Q498312 "Walton County") (wd:Q492026 "Oconee County") (wd:Q112061 "Clarke County") (wd:Q491525 "Oglethorpe County") (wd:Q491759 "Wilkes County") (wd:Q491519 "Lincoln County") (wd:Q501101 "Newton County") (wd:Q493083 "Morgan County") (wd:Q486765 "Greene County") }
FILTER NOT EXISTS { ?library wdt:P576 []}
?library (wdt:P31/(wdt:P279*)) wd:Q5193377; wdt:P131 ?located_in_the_administrative_territorial_entity.
OPTIONAL { ?library wdt:P6375 ?located_at_street_address. }
OPTIONAL { ?library wdt:P625 ?coordinate_location. }
OPTIONAL { ?library wdt:P1329 ?phone_number. }
OPTIONAL { ?library wdt:P968 ?e_mail_address. }
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} ORDER BY ?library LIMIT 10000

Try it!

Thanks in advance! Clifflandis (talk) 17:07, 18 May 2020 (UTC)

@Clifflandis: The query optimizer failed to correctly estimate the number of results for each triplet and tried to first get all cultural institutions, and then limit the results to those in the selected counties. It is better first to get all items located in the counties, and then limit the results to cultural institutions. It can be fixed with a query hint:
SELECT DISTINCT ?library ?libraryLabel ?located_at_street_address ?coordinate_location ?phone_number ?e_mail_address ?countyLabel
WHERE {
  VALUES (?located_in_the_administrative_territorial_entity ?countyLabel)
  {
    (wd:Q491547 "Union County") (wd:Q503538 "Towns County") (wd:Q503546 "Rabun County")
    (wd:Q492040 "Lumpkin County") (wd:Q389365 "White County") (wd:Q501096 "Habersham County")
    (wd:Q498362 "Stephens County") (wd:Q492012 "Hall County") (wd:Q488201 "Banks County")
    (wd:Q385931 "Franklin County") (wd:Q491301 "Hart County") (wd:Q486838 "Barrow County")
    (wd:Q486137 "Jackson County") (wd:Q156387 "Madison County") (wd:Q492016 "Elbert County")
    (wd:Q498312 "Walton County") (wd:Q492026 "Oconee County") (wd:Q112061 "Clarke County")
    (wd:Q491525 "Oglethorpe County") (wd:Q491759 "Wilkes County") (wd:Q491519 "Lincoln County")
    (wd:Q501101 "Newton County") (wd:Q493083 "Morgan County") (wd:Q486765 "Greene County")
  }
  FILTER NOT EXISTS { ?library wdt:P576 [] }
  ?library (wdt:P31/(wdt:P279*)) wd:Q5193377.
  ?library wdt:P131 ?located_in_the_administrative_territorial_entity. hint:Prior hint:runFirst true.
  OPTIONAL { ?library wdt:P6375 ?located_at_street_address. }
  OPTIONAL { ?library wdt:P625 ?coordinate_location. }
  OPTIONAL { ?library wdt:P1329 ?phone_number. }
  OPTIONAL { ?library wdt:P968 ?e_mail_address. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY ?library
LIMIT 10000
Try it! --Dipsacus fullonum (talk) 19:18, 18 May 2020 (UTC)
@Dipsacus fullonum: This is perfect and worked like a charm....Thanks! Clifflandis (talk) 12:35, 19 May 2020 (UTC)

Excluding imprecise dates from the resultsEdit

Hi,

When running a query implying dates as a variable, it's very common to get dates like "1st january XXXX", because the exact date is not indicated in the wikidata page.

To exclude all the imprecise dates of my query, I'm looking for a filter that excludes every dates that are "1st of january", regardless of the year. I tried multiples things that don't work, for example this :

SELECT ?item ?itemLabel ?dob
WHERE 
{
  ?item wdt:P39 wd:Q19546.
  ?item wdt:P569 ?dob. 
  
 MINUS {
  FILTER (   MONTH(?dob) = 01 && DAY(?dob) = 01). }
  
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }

Try it! }

@NanarBoulier: Your approach will also exclude genuine dates of 1 January. I suggest that you instead test for the precision of the timevalue and exclude the value if the precision isn't date:
SELECT ?item ?itemLabel ?dob
WHERE
{
  ?item wdt:P39 wd:Q19546.
  ?item p:P569 ?dob_statement.
  ?dob_statement psv:P569 ?dob_value.
  ?dob_value wikibase:timeValue ?dob.
  ?dob_value wikibase:timePrecision 11 . # Precision must be date (value 11)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Dipsacus fullonum (talk) 18:32, 18 May 2020 (UTC)

Thank you very much for your answers Dispacus fullonum (signed NanarBoulier)

Query that lists instances ofEdit

For an automatically generated list in https://www.wikidata.org/wiki/Wikidata:WikiProject_Anatomy/Ontology_of_Anatomy/draft I would like a query that lists all instances of anatomical metaclass (Q94945104) and item that links with is metaclass for (P8225) towards the instance if available. ChristianKl❫ 11:28, 20 May 2020 (UTC)

@ChristianKl: I am not sure I understand the request correctly. Is it this you mean:
SELECT ?item ?itemLabel ?metaclass ?metaclassLabel
WHERE
{
  ?metaclass wdt:P31 wd:Q94945104. # ?metaclass is instance of anatomical metaclass
  OPTIONAL { ?metaclass wdt:P8225 ?item. } # ?metaclass is metaclass for ?item
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Dipsacus fullonum (talk) 14:16, 20 May 2020 (UTC)
That was rougly what I was looking for. I tried to make a list out of it on https://www.wikidata.org/wiki/Wikidata:WikiProject_Anatomy/Ontology_of_Anatomy/draft but failed. Would it be possible to have all the properties for this type (P1963) in a third column? ChristianKl❫ 16:54, 20 May 2020 (UTC)
@ChristianKl: Like this?
SELECT ?item ?itemLabel ?metaclass ?metaclassLabel ?properties_for_type ?properties_for_typeLabel
WHERE
{
  ?metaclass wdt:P31 wd:Q94945104. # ?metaclass is instance of anatomical metaclass
  OPTIONAL { ?metaclass wdt:P8225 ?item. } # ?metaclass is metaclass for ?item
  OPTIONAL { ?metaclass wdt:P1963 ?properties_for_type. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! BTW. I'm no expert about ListeriaBot, but the {{Wikidata list end}} template were missing on WikiProject page. --Dipsacus fullonum (talk) 18:43, 20 May 2020 (UTC)

Query optimization - getting and ordering elevation in metresEdit

Hi,

I'm trying to retrieve the elevation in metres and order it.

This works and returns within a reasonable time, but contains values in both feet and metres:

SELECT distinct ?elevation ?item
WHERE
{?item wdt:P31 wd:Q8502.
 ?item wdt:P2044 ?elevation.
 }order by desc(?elevation) limit 100

Try it!

This only gets values in metres, but takes almost twice as long:

SELECT distinct ?elevation ?item
WHERE
{?item wdt:P31 wd:Q8502.
 ?item p:P2044/psv:P2044 ?hnode.
 ?hnode wikibase:quantityUnit wd:Q11573.
 ?hnode  wikibase:quantityAmount ?elevation.
 
 }order by desc(?elevation) limit 100

Try it!

Why is getting nodes slower? And is it possible to write a query that gets elevation in metres, that's also reasonably fast?  – The preceding unsigned comment was added by [[User:{{{1}}}|{{{1}}}]] ([[User talk:{{{1}}}|talk]] • [[Special:Contributions/{{{1}}}|contribs]]).

  • It gets more than twice as many triples .. --- Jura 15:22, 20 May 2020 (UTC)
@‎Matsjsk: The second is slower because it has to check more data (more triples) for each of the 314,829 mountains in Wikidata. It is somewhat faster if you use the normalized values (psn:) of the height which is always in meter as that saves a triple in the query. Besides it will also use data from mountains where the unnormalized height is given in foot.
SELECT DISTINCT ?elevation ?item
WHERE
{
  ?item wdt:P31 wd:Q8502.
  ?item p:P2044 / psn:P2044 ?hnode.
  ?hnode wikibase:quantityAmount ?elevation.
}
ORDER BY DESC(?elevation)
LIMIT 100
Try it! --Dipsacus fullonum (talk) 15:38, 20 May 2020 (UTC)

@Jura1: is wdt:P2044 not accessing the same node as p:P2044/psv:P2044? Am I not just unpacking more values from the same node? – The preceding unsigned comment was added by [[User:{{{1}}}|{{{1}}}]] ([[User talk:{{{1}}}|talk]] • [[Special:Contributions/{{{1}}}|contribs]]).

I'm not really an engineer, but, if you are using query server, I think you select from triples. There are:
A wdt:P2044 numericvalue
and
A p:P2044 somekey .
somekey psv:P2044 someotherkey .
someotherkey wikibase:quantityAmount numericvalue .
someotherkey wikibase:quantityUnit ?unit .
so different nodes (key in my list). Four times as many triples. mw:Wikibase/Indexing/RDF_Dump_Format#Quantity tries to show that.
BTW, if you are looking for metric units, you could use directly the "psn" triple, see mw:Wikibase/Indexing/RDF_Dump_Format#Normalized_quantity.
As the unit should be meter, it could save you one triple and gets you more results.
There is a graphic at mw:Wikibase/Indexing/RDF_Dump_Format#Data_model that summaries it, but I still find it confusing. --- Jura 15:43, 20 May 2020 (UTC)

Mwapi usageEdit

Is there way to get this list through sparql/mwapi? --- Jura 15:24, 20 May 2020 (UTC)

@Jura1: I see no reason why it shouldn't be possible. However the API call to use (https://en.wikipedia.org/w/api.php?action=query&generator=wblistentityusage&gwbeuentities=Q42&gwbeulimit=max&format=xml) seems to give an RuntimeException. I have no idea why as I see nothing wrong with the API call. --Dipsacus fullonum (talk) 16:35, 20 May 2020 (UTC)
Is there a way to merely get the count (in namespace 0)? --- Jura 16:46, 20 May 2020 (UTC)
@Jura1: No, not until the API call can be run without an RuntimeException. This may be related to phab:T196962. --Dipsacus fullonum (talk) 17:56, 20 May 2020 (UTC)
Mwapi calls (when they don't work) are still mysterious to me. --- Jura 22:42, 20 May 2020 (UTC)

The websites of acting US senatorsEdit

I'm trying to select the websites of acting US senators. I got the websites of all senators:

SELECT ?person ?personLabel ?official_website WHERE {
  ?person wdt:P39 wd:Q4416090.
  ?person wdt:P856 ?official_website.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

But this query (1) does not exclude senators with ended terms, (2) missing some websites present on the senator's wikidata.org/wiki/Q page.

I selected senators' "end time":

SELECT ?person ?personLabel ?position_heldLabel ?endtime WHERE {
  ?person wdt:P39 wd:Q4416090.
  ?person p:P39 ?statement.
  ?statement ps:P39 ?position_held.
  ?statement pq:P582 ?endtime.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

But this query returns all jobs, not only "United States senator" (wd:Q4416090).

How can I get all the websites of senators who is currently in office?

AntonTarasenko (talk) 13:02, 21 May 2020 (UTC)

@AntonTarasenko: Try this:

SELECT distinct ?person ?personLabel ?official_website WHERE {
  ?person wdt:P31 wd:Q5 . ?person p:P39 ?statement.
  ?statement ps:P39 wd:Q4416090. 
  filter not exists { ?statement pq:P582 ?endtime.}
  ?person wdt:P856 ?official_website.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

The P31 element means they have to be a real person (otherwise you get one fictional senator). Then it finds all P39 statements which have Q4416090 as the value (all Senators), and filters out any statement with an end time (only current Senators). However, a handful of Senators have end dates set in the future, so they get filtered out - but this isn't how current positions are normally modelled, even if we expect someone will be retiring. Might need fixing. Andrew Gray (talk) 15:33, 21 May 2020 (UTC)

@Andrew Gray: It's perfect. Thank you very much!

Add column related to living people to Wikidata:WikiProject Properties/Wikidata properties that may violate privacyEdit

The list for Wikidata properties that may violate privacy is great, but some of the numbers are a bit misleading because several properties listed there are widely used for organizations, and not so much for living people, for whom the privacy risk is most acute. Would anyone here be able to add a column that indicates the number of (probably) living people to whom each property is applied? The list of properties likely to be challenged has that feature and I tried to replicate that list, replacing property likely to be challenged (Q44597997) with property that may violate privacy (Q44601380), but that query times out... and creating a fresh query of such complexity is frankly beyond my capabilities. I think it would be cool if anyone who takes up this challenge would go ahead and just update the page directly. In any case, thanks for looking into this! Spinster 💬 10:59, 23 May 2020 (UTC)

  • The simplest approach is probably to create a query for each property and place that on a separate listeria. The result can then be used elsewhere, see e.g. Wikidata:Statistics/count/human. --- Jura 11:08, 23 May 2020 (UTC)

Query in SPARQL - extraction from descriptionEdit

Hello, I failed to program that in wikidata query, could you fix that please?


SELECT ?item ?itemLabel ?itemDescription WHERE {

 ?item wdt:P31 wd:Q5.
 ?item wdt:P27 wd:Q142.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
 FILTER(regex(str(?itemDescription), "poète français" ) ).

}

 – The preceding unsigned comment was added by 176.153.20.155 (talk • contribs) at 15:27, 23 May 2020‎ (UTC).

SELECT ?item ?itemLabel ?itemDescription WHERE {
  ?item schema:description 'poète français'@fr .
  ?item wdt:P31 wd:Q5 .
  ?item wdt:P27 wd:Q142 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'fr' }
}

Try it!  —MisterSynergy (talk) 15:50, 23 May 2020 (UTC)

The query by MisterSynergy finds items where the French description is exactly "poète français". By using the search function in Wikidata's MediaWiki API, you can also search descriptions which contains the string as substring:
SELECT ?item ?itemLabel ?description WHERE {
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "www.wikidata.org";
                    wikibase:api "Generator";
                    mwapi:generator "search";
                    mwapi:gsrsearch "poète français";
                    mwapi:gsrlimit "max".
    ?item wikibase:apiOutputItem mwapi:title.
  }
  ?item wdt:P31 wd:Q5.
  ?item wdt:P27 wd:Q142.
  ?item schema:description ?description.
  FILTER (LANG(?description) = "fr")
  FILTER CONTAINS(?description, "poète français")
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
}
Try it! --Dipsacus fullonum (talk) 16:01, 23 May 2020 (UTC)

Result view table (flipped)Edit

 


How can I activate the above view? It is supposed to be active when the results are lengthy compared to the screen, but with URLs it doesn't necessarily happen. --- Jura 12:05, 24 May 2020 (UTC)


I asked at Wikidata:Contact_the_development_team#Query_server_result_view:_flipped_table_(26_May). --- Jura 13:54, 26 May 2020 (UTC)

Find all periodicals older than a certain ageEdit

I've made a query to get all the periodicals and their websites that meet a certain criteria (age in this case):

SELECT DISTINCT ?periodical ?periodicalLabel ?periodicalTypeLabel ?inception ?website WHERE {
  VALUES ?periodicalType {
    wd:Q11032    # newspaper
    wd:Q1110794  # daily newspaper
    wd:Q2305295  # weekly newspaper
    wd:Q41298    # magazine
  }.
  ?periodical wdt:P31 ?periodicalType.
  ?periodical wdt:P571 ?inception.
  ?periodical wdt:P856 ?website.
  FILTER (YEAR(?inception) < 1920).
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

I'm not sure about completeness of "?periodicalType". The NY Times is listed as a "daily newspaper", Time is an instance of three types. How do I get all the newspapers and magazines regardless of their publishing cycle?

AntonTarasenko (talk) 14:43, 24 May 2020 (UTC)


SELECT DISTINCT ?periodical ?periodicalLabel ?periodicalTypeLabel ?inception ?website
WHERE
{
  ?periodical wdt:P31/wdt:P279* wd:Q1002697 .
  ?periodical wdt:P31 ?periodicalType.
  ?periodical wdt:P571 ?inception.
  ?periodical wdt:P856 ?website.
  FILTER (YEAR(?inception) < 1920).
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

@AntonTarasenko: maybe with the above? --- Jura 14:54, 24 May 2020 (UTC)

Just be aware that it will only find periodicals with a website. For a more complete list change ?periodical wdt:P856 ?website. to OPTIONAL { ?periodical wdt:P856 ?website. } --Dipsacus fullonum (talk) 18:38, 24 May 2020 (UTC)
@Jura1: Exactly what I've been looking for. One question, if you don't mind: how can I locate documentation for operators "/" and "*" in the ?periodical wdt:P31/wdt:P279* wd:Q1002697 line? What are they called?
@AntonTarasenko: SPARQL 1.1 Query Language calls them "SequencePath" and "ZeroOrMorePath" (see "Property Path Syntax"), but I tend to think that to understand the spec one already needs to know about it. Wikidata:SPARQL_tutorial#Property_paths tries to explain it. --- Jura 16:29, 25 May 2020 (UTC)

SPARQLEdit

Lugar de nacimiento de 25 actores franceses  – The preceding unsigned comment was added by [[User:{{{1}}}|{{{1}}}]] ([[User talk:{{{1}}}|talk]] • [[Special:Contributions/{{{1}}}|contribs]]).


SELECT ?item ?itemLabel ?itemDescription ?pob ?pobLabel
{
    ?item wdt:P106 wd:Q33999 .
    ?item wdt:P19 ?pob .
    ?item wdt:P27 wd:Q142 .
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 25

Try it!

 – The preceding unsigned comment was added by [[User:{{{1}}}|{{{1}}}]] ([[User talk:{{{1}}}|talk]] • [[Special:Contributions/{{{1}}}|contribs]]).

SPARQLEdit

Consigue el título, autor y fecha de Pinturas en las que salgan perros y que estén en el Museo del Louvre.

SELECT ?item ?itemLabel ?painter ?painterLabel ?date ?precision
WHERE
{
   ?item wdt:P31 / wdt:P279* wd:Q3305213. # Painting
   ?item wdt:P195 wd:Q3044768.            # at Louvre
   ?item wdt:P180 / wdt:P279* wd:Q144.    # depicts dog
   ?item wdt:P170 ?painter.
   ?item p:P571 ?date_statement.
   ?date_statement a wikibase:BestRank.
   ?date_statement psv:P571 ?date_value.
   ?date_value wikibase:timePrecision ?precision. # 11=day, 10=month, 9=year, 8=decade, 9=century
   ?date_value wikibase:timeValue ?date.
   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],es,en". }
}
Try it! --Dipsacus fullonum (talk) 19:02, 25 May 2020 (UTC)

Most common classesEdit

We have as of just now, 7,654,855 items with P31=Q5. But what are the most used P31 values? I'll settle for, say, a top 20. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 11:49, 25 May 2020 (UTC)

This list is periodically updated by a bot. Manual changes to the list will be removed on the next update!

WDQS | PetScan | YASGUI | TABernacle | Find images Recent changes | Query: select ?item (count(?item) / 200000 * 100 as ?percent) (count(?item) as ?count) { service bd:sample { [] wdt:P31 ?item. bd:serviceParam bd:sample.limit 200000 . } } group by ?item having (?percent > 0.2) order by desc(?count)
class percent_on_sample raw_number_on_200000_sample
scholarly article 41.506 83012
human 8.973 17946
Wikimedia category 5.3995 10799
star 3.782 7564
taxon 3.2135 6427
galaxy 2.4795 4959
Wikimedia disambiguation page 1.5415 3083
gene 1.3715 2743
chemical compound 1.225 2450
protein 1.141 2282
Wikimedia template 1.0215 2043
village-level division in China 0.684 1368
human settlement 0.682 1364
mountain 0.632 1264
painting 0.5165 1033
street 0.463 926
river 0.4505 901
astronomical radio source 0.4425 885
clinical trial 0.4255 851
family name 0.3925 785
hill 0.3745 749
Wikimedia list article 0.374 748
eclipsing binary star 0.3535 707
high proper-motion star 0.3415 683
encyclopedic article 0.34 680
collection 0.3185 637
village 0.302 604
lake 0.292 584
asteroid 0.2725 545
film 0.2715 543
building 0.255 510
album 0.253 506
Wikinews article 0.239 478
item of collection or exhibition 0.227 454
calendar day of a given year 0.2245 449
church building 0.223 446
house 0.222 444
business 0.2065 413
watercourse 0.2015 403
End of automatically generated list.

@Pigsonthewing: something like that, sample on 20,000,000 items, left only the classes with more than 0.2% of the classes on the sample. author  TomT0m / talk page 14:42, 25 May 2020 (UTC)

Artists with unknown dod but properties with end/start time qualifiers to detect life signsEdit

I want a query that detects humans with any start and end time as qualifier in the properties to detect lifesigns for an artist. For example, I want artists that have a statement residence Amsterdam from 2009 untill 2010. I need this to itereate through such collection to check the lates life sign. Below the query I currently use, it takes all human within museum collections with properties residence and working place. But this still doesnt include the property spouse for example. Also, the collection is too broad as I doesn't include humans without qualifier start/endtime. So instead, I would like to check all humans within collection with start/end time qualifiers for any statement. --Hannolans (talk) 11:14, 26 May 2020 (UTC)

SELECT DISTINCT ?item ?authorLabel ?timeprecision (YEAR(?dob) as ?year)
WHERE {
  ?item wdt:P31 wd:Q5 .
  ?item wdt:P6379 ?collection. #in a collection
#  ?item wdt:P6379 wd:Q17153751.
  {?item wdt:P937 ?werklocatie}#has a work location
        UNION
  {?item wdt:P108 ?employer}#or an employer
        UNION
  {?item wdt:P551 ?woonplaats}#or living place
#  ?item wdt:P6379 ?institution .
#  ?institution wdt:P17 ?country.#  country
#  ?country wdt:P463 wd:Q458. # member EU
  FILTER NOT EXISTS { ?item wdt:P7763 [] } .#no copyright status
  FILTER NOT EXISTS { ?item wdt:P570 [] }.  #no dod
  FILTER NOT EXISTS { ?item wdt:P1317 [] } .#no floruit
#  ?item     p:P569/psv:P569 ?timenode.
#  ?timenode wikibase:timePrecision     ?timeprecision.
#  FILTER(?timeprecision > 8) #only year or better precision
}

Try it!

Hannolans said: "I want a query that detects humans with any start and end time as qualifier in the properties to detect lifesigns for an artist." You can try something like this:
SELECT ?item ?itemLabel ?claim ?start ?startprecision
WHERE {
  ?item wdt:P31 wd:Q5.
  ?item wdt:P6379 wd:Q17153751.
  
  # Get any truthy statements for ?item with P580 as qualifier.
  ?item ?claim ?statement.
  ?statement a wikibase:BestRank.
  ?statement pqv:P580 ?startnode.
  ?startnode wikibase:timePrecision ?startprecision.
  ?startnode wikibase:timeValue ?start.
  FILTER (?startprecision > 8) #only year or better precision

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],es,en". }
}
limit 5
Try it! and likewise for endtimes. --Dipsacus fullonum (talk) 12:46, 26 May 2020 (UTC)
  • BTW, I suppose you already checked a few other properties: floruit, work period start, work period end, date of baptism in early childhood, date of disappearance .. --- Jura 13:56, 26 May 2020 (UTC)
Cool! Yes! Tried to make a combination to check for either end time, start time and point in time. Tried OPTIONAL and UNION but not sure how to do this as it results in strange dates. Any suggestion? I currently check for dod and floruit, but not for date of burial and disappearance, good catch and will add that. --Hannolans (talk) 14:10, 26 May 2020 (UTC)
@Hannolans: You wrote "Tried to make a combination to check for either end time, start time and point in time." Here is a way to do it:
SELECT ?item ?itemLabel ?property ?propertyLabel ?qualifier ?qualifierLabel ?time ?precision
WHERE
{
  {
    SELECT ?item ?claim ?qualifiervalue ?time ?precision
    WHERE
    {
      ?item wdt:P31 wd:Q5.
      ?item wdt:P6379 wd:Q17153751.
  
      # Get truthy statements for ?item with any of time qualifiers in ?qualifiervalue:
      VALUES ?qualifiervalue { pqv:P580 pqv:P582 pqv:P585 }
      ?item ?claim ?statement.
      ?statement a wikibase:BestRank.
      ?statement ?qualifiervalue ?timenode.
      ?timenode wikibase:timePrecision ?precision.
      ?timenode wikibase:timeValue ?time.
      FILTER (?precision > 8) #only year or better precision
    }
  }
  hint:Prior hint:runFirst "true".
  
  #Get labels for item, property and qualifier:
  ?qualifier wikibase:qualifierValue ?qualifiervalue.
  ?property wikibase:claim ?claim.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Dipsacus fullonum (talk) 15:21, 26 May 2020 (UTC)
Yes! Wow. Nice and clean! --Hannolans (talk) 19:35, 26 May 2020 (UTC)

Finding a US President's Father, GrandFather, Great-GrandFather and so on...Edit

I want to write a query to get a president's father, grand father , great grand father and so on... upto some level or till it has information for it.

For 2 levels , I wrote a query like this :

  1. Father - GrandFather and so on ...


SELECT  ?childLabel ?fatherLabel ?grandFatherLabel ?greatGrandFatherLabel
WHERE {
 ?child wdt:P31 wd:Q5.
 ?child  wdt:P27 wd:Q30.
 ?child wdt:P106 wd:Q82955.
 ?child wdt:P22 wd:Q11806.
 OPTIONAL { ?child wdt:P22 ?father. }
 OPTIONAL { ?father wdt:P22 ?grandFather. }
 OPTIONAL { ?grandFather wdt:P22 ?greatGrandFather. }--2401:4900:3148:4A80:508A:3D33:65CF:BFA8 22:36, 26 May 2020 (UTC)
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}


Now I dont want to manually type like this to get all the list. is there any use to do this automatically ?

# Find patrilineal ancestors of US presidents
SELECT DISTINCT ?president ?presidentLabel ?ancestor ?ancestorLabel 
WHERE
{
  ?president wdt:P31 wd:Q5. # ?president is humam
  ?president wdt:P39 wd:Q11696. # ?president has position president of US
  ?president wdt:P22+ ?ancestor.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Dipsacus fullonum (talk) 07:56, 27 May 2020 (UTC)

Dates precision around JesusChristusEdit

Hello, is it possible to query anything dated in the range -9BC to 9AD, with the times precisions being either dates, months, years, décades ? Thanks ! Bouzinac (talk) 13:52, 27 May 2020 (UTC)

@Bouzinac: Yes, certainly.
SELECT ?entity ?entityLabel ?property ?propertyLabel ?qualifier ?qualifierLabel ?timevalue ?precision 
WHERE
{
  VALUES ?precision { 11 10 9 8 } # Precision is day (11), month (10), year (9) or decade (8)
  ?time wikibase:timePrecision ?precision.
  ?time wikibase:timeValue ?timevalue. hint:Prior hint:rangeSafe true.
  FILTER (?timevalue >= "-0008-00-00"^^xsd:dateTime && ?timevalue < "0010-00-00"^^xsd:dateTime)

  {
    # ?time used as qualifier
    ?statement ?qualifiervalue ?time.
    ?qualifier wikibase:qualifierValue ?qualifiervalue.
    ?entity ?claim ?statement.
    ?property wikibase:claim ?claim.
  }
  UNION
  {
    # ?time used as main value
    ?statement ?statementvalue ?time.
    ?property wikibase:statementValue ?statementvalue.
    ?property wikibase:claim ?claim.
    ?entity ?claim ?statement.
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Dipsacus fullonum (talk) 14:42, 27 May 2020 (UTC)
Thank you Dipsacus fullonum (talkcontribslogs) but I'm somewhat confused. https://www.wikidata.org/wiki/Q1419#P569 does not look to give same date between item and the query result? Bouzinac (talk) 15:53, 27 May 2020 (UTC)
@Bouzinac: I have no explation for the different dates and have reported it at phab:T253771. Dipsacus fullonum (talk) 17:01, 27 May 2020 (UTC)
@Bouzinac: The dates for Vespearian's date of birth on the item page uses the proleptic Julian calendar (Q1985786) calendar model, but are converted to proleptic Gregorian calendar (Q1985727) in the query result. That explains the difference according to phabicator. --Dipsacus fullonum (talk) 17:19, 27 May 2020 (UTC)

How can I get all subclasses, sub-subclasses, sub-sub-subclasses etc. of an entityEdit

How can I find all subclasses, sub-subclasses, sub-sub-subclasses etc. of an entity, for example, https://www.wikidata.org/wiki/Q386724?

With a property path: Add a * to a property to use it zero or any number of times.
# Subclasses of Q386724
SELECT ?subclass ?subclassLabel
{
  ?subclass wdt:P279* wd:Q386724.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! Warning: This query gave 100,707 result when I tried it. It may timeout. If so, try omitting the labels. --Dipsacus fullonum (talk) 09:43, 28 May 2020 (UTC)

In How can I get all subclasses, sub-subclasses, sub-sub-subclasses etc. of an entity, I was taught that the * allows for recursion.

How can I restrict the number "degrees" this recursion happens?

Say:

SELECT ?subclass ?subclassLabel ?immedisuperclass ?immedisuperclassLabel {

 ?subclass wdt:P279* wd:Q386724.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }

}

I only wish the recursion to happen 3 times. How to do it?

I read your "etc" to mean all levels. With a maximum recursion depth you cannot use "*", but have write each wanted level explicit:
# Subclasses of level 1-3 of Q386724
SELECT ?subclass ?subclassLabel
{
  ?subclass wdt:P279 | ( wdt:P279 / wdt:P279 ) | ( wdt:P279 / wdt:P279 / wdt:P279 ) wd:Q386724.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it! --Dipsacus fullonum (talk) 10:31, 28 May 2020 (UTC)

Or with the 3 levels side by side in different columns:
# Subclasses of level 1-3 of Q386724
SELECT ?subclass1 ?subclass1Label ?subclass2 ?subclass2Label ?subclass3 ?subclass3Label
{
  ?subclass1 wdt:P279 wd:Q386724.
  OPTIONAL
  {
    ?subclass2 wdt:P279 ?subclass1.
    OPTIONAL { ?subclass3 wdt:P279 ?subclass2. }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Dipsacus fullonum (talk) 10:37, 28 May 2020 (UTC)

How can I specify the number of "degrees" of recursion by the * operator?Edit

In How can I get all subclasses, sub-subclasses, sub-sub-subclasses etc. of an entity, I was taught that the * allows for recursion.

How can I restrict the number "degrees" this recursion happens?

Say:

SELECT ?subclass ?subclassLabel ?immedisuperclass ?immedisuperclassLabel {

 ?subclass wdt:P279* wd:Q386724.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }

}

I only wish the recursion to happen 3 times. How to do it?

That isn't possible with *- Please see above for solutions. --Dipsacus fullonum (talk) 10:40, 28 May 2020 (UTC)

Asking for entity creation timestampEdit

First, thanks in advance for your time and attention, and really sorry if this request is "wrongly placed".

I would like to have a query, that for a given wikidata entity (for example wd:Q1346018), it would answer me if this entity existed before a given date (for example 01.01.2015). Thx once more. David.

Hi David. The request is not wrongly placed. It is possible to do by calling Wikidata's MediaWiki API:
SELECT ?item ?created (IF(xsd:dateTime(?created) <= "2015-01-01"^^xsd:dateTime, "yes", "no") AS ?existed_before_given_date)
WHERE
{
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "www.wikidata.org".
    bd:serviceParam wikibase:api "Generator".
    bd:serviceParam wikibase:limit "1".
    bd:serviceParam mwapi:generator "revisions".
    bd:serviceParam mwapi:titles "Q1346018".
    bd:serviceParam mwapi:prop "revisions".
    bd:serviceParam mwapi:grvprop "timestamp".
    bd:serviceParam mwapi:grvdir "newer".
    bd:serviceParam mwapi:grvlimit "1".
    ?item wikibase:apiOutputItem mwapi:title.
    ?created wikibase:apiOutput "revisions/rev/@timestamp".
  }
}
Try it! --Dipsacus fullonum (talk) 14:26, 28 May 2020 (UTC)

First, thanks a lot. It works really fine from the Wikidata query service (https://query.wikidata.org/). One minor problem I get is that when executing this query from Jena against the wikidata sparql endpoint (https://query.wikidata.org/sparql), the answer Content-Type is text/html. Any other sparql query I send from Jena is ok. Any clue why when changing the query String with the one provided, it answers text/html. Thanks 1.000 for all your time and attention. David.

@‎David.garciahz: I don't know why as I have never used Jena, but could it be because the variable ?created in the query above does contain a text string? You can convert it to the dateTime type with the function call xsd:dateTime(?created) as it is done in expression to create ?existed_before_given_date in the select clause. --Dipsacus fullonum (talk) 17:33, 28 May 2020 (UTC)

The query works perfectly. It was all my "double" mistake. If useful for anyone: My first problem was because in Apache-Jena it is necessary adding the prefixes: PREFIX wd: <http://www.wikidata.org/entity/> PREFIX wdt: <http://www.wikidata.org/prop/direct/> PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> PREFIX wikibase: <http://wikiba.se/ontology#> PREFIX bd: <http://www.bigdata.com/rdf#> PREFIX mwapi: <https://www.mediawiki.org/ontology#API/>

My second problem was because I added one of them wrongly as "https" instead of "http"... in this case, the Jena trace is pretty misleading :-) Thanks once more for all your support and incredible query. You are a "Master". Regards, David.

Italian comune without anybody born or deadEdit

Hi friends, I would like to extract a list of italian comune (wdtP31 wd:Q747074) where there's nobody born there (P19) or dead there (P20). I don't know where to start from. Could you help me? Thanks Luckyz (talk) 12:05, 28 May 2020 (UTC)

@Luckyz: This should do it:
# Italian comunes where no humans are born or died
SELECT ?comune ?comuneLabel
WHERE
{
  ?comune wdt:P31 wd:Q747074.
  MINUS
  {
    ?person wdt:P31 wd:Q5.
    ?person (wdt:P19 | wdt:P20 ) / wdt:P131* ?comune.
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],it". }
}
Try it! --Dipsacus fullonum (talk) 12:42, 28 May 2020 (UTC)

@Luckyz, Dipsacus fullonum: Interesting question. I went a step further, here is one with a map:

#defaultView:Map
SELECT ?item ?itemLabel ?itemDescription ?coor ?pop 
{
    ?item wdt:P31 wd:Q747074 
    MINUS { [] wdt:P19/wdt:P276*/wdt:P131* ?item }     
    MINUS { [] wdt:P20/wdt:P276*/wdt:P131* ?item }
    MINUS { ?item wdt:P31 wd:Q3685476 }
    OPTIONAL { ?item wdt:P625 ?coor }
    OPTIONAL { ?item wdt:P1082 ?pop }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Immortals seem to be mainly NOT around Florence ;)

To cross-check, a similar query for those with:

#defaultView:Map
SELECT DISTINCT ?item ?coor
{
    ?item wdt:P31 wd:Q747074 .
    [] (wdt:P19|wdt:P20)/wdt:P131* ?item .
    ?item wdt:P625 ?coor 
}

Try it!

--- Jura 12:54, 28 May 2020 (UTC)

😍 thank you friends! It's more than I expected. It's really incredible Tuscany. Luckyz (talk) 13:39, 28 May 2020 (UTC)

Objects at border, but only assigned to one of the countriesEdit

I want to find border objects (objects at border between two areas, countries, etc., countries for now) that are only assigned via country (P17) to one of the neighbouring countries. The poor man's approach is to map all objects, but filter away those objects having already more than one country assigned. An example for Austria and neighbouring countries. And then to find incomplete objects by checking objects at or near the border at the generated map.

#defaultView:Map
SELECT ?item ?itemLabel ?coord (sample (?layerLabel) as ?oneLayerLabel) (count(?item) as ?countries) {
  VALUES ?country { wd:Q38 wd:Q40 wd:Q39 wd:Q183 wd:Q215}
  ?item wdt:P31 wd:Q8502 .
  ?item wdt:P17 ?country . BIND(?country AS ?layer) 
  ?item wdt:P625 ?coord . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?item ?itemLabel ?coord
HAVING (?countries = 1)

Try it!

Alas, coloring per country in different layer colors does not work. The previous try to accomplish my request was:

#defaultView:Map
SELECT ?item ?itemLabel ?coord ?layerLabel (count(?item) as ?countries) {
  VALUES ?country { wd:Q38 wd:Q40 wd:Q39 wd:Q183 wd:Q215}
  ?item wdt:P31 wd:Q8502 .
  ?item wdt:P17 ?country . BIND(?country AS ?layer) 
  ?item wdt:P625 ?coord . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?item ?itemLabel ?coord ?layerLabel
HAVING (?countries = 1)

Try it!

where filtering away objects with more than one country did nor work.

Coloring is essential to easily find the borderline. Ignoring objects already assigned to two states is essential to know what still has to be done. Any idea on how to combine these queries correctly or get the results with an even more simpler approach (e.g. looking only at objects near the borders). --Herzi Pinki (talk) 09:26, 29 May 2020 (UTC)

@Herzi Pinki: Layering in the first query doesn't work beacuse neither ?layer nor ?layerLabel are selected in the SELECT clause. Filtering away objects with more than one country doesn't work in the second query because you include ?layerLabel (identical to ?country) in GROUP BY. I would suggest a different color for each country for items having one country, and other colors for items having more than one country to clearly mark mountains on a border:
#defaultView:Map
SELECT ?item ?itemLabel ?coord (COUNT(?item) AS ?countries) (IF(?countries = 1, SAMPLE(?country), ?countries) AS ?layer) {
  VALUES ?country { wd:Q38 wd:Q40 wd:Q39 wd:Q183 wd:Q215}
  ?item wdt:P31 wd:Q8502 .
  ?item wdt:P17 ?country .
  ?item wdt:P625 ?coord . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?item ?itemLabel ?coord
Try it! --Dipsacus fullonum (talk) 09:57, 29 May 2020 (UTC)
thanks. perfect --Herzi Pinki (talk) 10:08, 29 May 2020 (UTC)

Get all Information about Food as RDFEdit

Hello, I want to extract all information (both taxometrys and all types of attributes) about Food (wd:Q2095) from Wikidata. I have already found out

-- Taxometry (Attribut still missing)
SELECT distinct ?node ?Unterklasse_von  
WHERE {
  ?node (wdt:P279*) wd:Q2095.
  OPTIONAL { ?node wdt:P279 ?Unterklasse_von. }
}


-- all involved elements
-- Taxometry 
SELECT ?subclass  ?predicate  ?entity
WHERE {
  ?subclass (wdt:P279*) wd:Q2095.
  { ?entity ?predicate ?subclass. }
  UNION
  { ?subclass ?predicate ?entity. }
}

Try it!

What I need is this query with all attributes (including the missing attributes like Labels and so on...) and than the same as rdf for further editing in protege. I understand that I need to use the Construct Operartor, but I don't know how.

Thank you

You can get all information in RDF format for food (Q2095) at the special page https://www.wikidata.org/wiki/Special:EntityData/Q2095.rdf. --Dipsacus fullonum (talk) 13:55, 29 May 2020 (UTC)
Something like that ? [1]
The query is
construct{
  ?subclass  ?rpredicate  ?entity.
  ?entity ?predicate ?subclass
}
WHERE {
  ?subclass (wdt:P279*) wd:Q2095.
  { ?entity ?predicate ?subclass. [] wikibase:directClaim ?predicate }
  UNION
  { ?subclass ?rpredicate ?entity. [] wikibase:directClaim ?rpredicate }
}
Try it! It does only get the « truthy predicates » (the statements main values) and not the full statements with qualifiers and so on. author  TomT0m / talk page 14:24, 29 May 2020 (UTC)

List all first level administrative divisions by country and their respective isoEdit

So far i have this, the regions seem to be correct with their countries, but the iso column doesnt match, whats missing?

select ?countryLabel ?regionLabel ?iso WHERE {

 ?item wdt:P297 ?iso .
 ?region wdt:P17 ?country .
 ?region wdt:P31 wd:Q10864048 .
 
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } 

}

Thanks in advance

@PedroSilva1989: There is nothing to connect ?iso with the variables ?region and ?country, so you see a join where all combinations are put together. Try change ?item wdt:P297 ?iso . to ?country wdt:P297 ?iso . to make a connection between ?country and ?iso. --Dipsacus fullonum (talk) 20:09, 29 May 2020 (UTC)

Select Distinctly All The Properties That Have Actually Been Applied To A Set of EntitiesEdit

Select Distinctly All The Properties That Have Actually Been Applied To A Set of Entities

Say I have a group of entities in mind, perhaps all those that are instances of "film" (https://www.wikidata.org/wiki/Q11424).

How do I select distinctly all the properties that have actually been applied to all the entities fulfilling the criteria?

In principle this way:
SELECT DISTINCT ?property
WHERE
{
  ?item wdt:P31 wd:Q11424. # ?item is instance of film.
  ?item ?claim [].
  ?property wikibase:claim ?claim.
}
Try it! But I cannot get that query to run without timeout. There are 245,283 items which are instances of film which are probably too many to handle for that kind of query. You could try with a smaller group of entities and/or smaller entities. If good enough, you can also limit the query to a random sample of films:
SELECT DISTINCT ?property
WHERE
{
  SERVICE bd:sample
  {
    ?item wdt:P31 wd:Q11424. # ?item is instance of film. 
    bd:serviceParam bd:sample.limit 25000 . # Get a sample with 25000 items
  }
  ?item ?claim [].
  ?property wikibase:claim ?claim.
}
Try it! --Dipsacus fullonum (talk) 07:57, 31 May 2020 (UTC)