Shortcuts: WD:RAQ, w.wiki/LX

Wikidata:Request a query

From Wikidata
Jump to navigation Jump to search

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

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

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

Project
chat

Lexicographical
data

Administrators'
noticeboard

Development
team

Translators'
noticeboard

Request
a query

Requests
for deletions

Requests
for comment

Bot
requests

Requests
for permissions

Property
proposal

Properties
for deletion

Partnerships
and imports

Interwiki
conflicts

Bureaucrats'
noticeboard

Requests
for checkuser

Top Living Famous People Who Were Born Or Lived In Specific U.S. State[edit]

Update: I have made some progress, but am fairly certain I am doing it inefficiently.

The below query attempts to list the top living famous people who have meaningful ties to Florida, defined by whether they have done any of the following: - were born in Florida - had residence in Florida - were educated in Florida - worked in Florida

I know have an output that shows the full list of people, but I'd like to add a column that shows the connection type (birthPlace, residence, educatedAt, workedAt). How can I best add that?

Here's what I have so far:

SELECT ?item ?itemLabel ?placeLabel ?fame # we want the Qid of the person and their label in a given language
{
 ?item wikibase:sitelinks ?fame
 FILTER(?fame > 35) . 
 FILTER NOT EXISTS{ ?item wdt:P570 ?date } # only count living people
 {
   ?item wdt:P19 ?place.       # get the person's place of birth (if it is present on the item)
   ?place wdt:P131* wd:Q812. # make sure it's in Florida
 }
 UNION # because we want _either_ places of birth _or_ places of residence, not just items with both together
 {
   ?item wdt:P551 ?place.      # get the person's place of residence (if it is present on the item)
   ?place wdt:P131* wd:Q812. # make sure it's in Florida
 }
 UNION # (similar reasoning to the previous comment on UNION)
 {
   ?item wdt:P937 ?place.      # get the person's place where they worked (if it is present on the item)
   ?place wdt:P131* wd:Q812  # make sure it's in Florida
 }
 UNION # (similar reasoning to the previous comment on UNION)
 {
   ?item wdt:P69 ?place.       # get the person's place where they were educated (if it is present on the item)
   ?place wdt:P131* wd:Q812  # make sure it's in Florida
 }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Thanks in advance for any help!

Here is a more efficiently query that counts fame as the number of Wikipedia articeles and lists all values in Florida for birthPlace, residence, educatedAt and workedAt:
SELECT ?person ?personLabel ?fame ?birthPlace ?residence ?educatedAt ?workedAt
WITH
{
  SELECT DISTINCT ?person
  WHERE
  {
    ?person wdt:P31 wd:Q5. # Is human
    ?person (wdt:P19 | wdt:P551 | wdt:P037 | wdt:P69) / wdt:P131* wd:Q812. # Born/lived/worked/educated in Florida
    hint:Prior hint:gearing "forward". # Search the property chain above forward
    ?person wikibase:sitelinks ?fame
    FILTER (?fame > 35)
    OPTIONAL { ?person wdt:P570 ?date_of_death. }
    FILTER (! BOUND(?date_of_death)) # No date of death, i.e. living
  }
} AS %first_selection
WITH
{
  SELECT ?person (COUNT(?wikipedialink) AS ?fame)
  WHERE
  {
    INCLUDE %first_selection
    ?wikipedialink schema:isPartOf [ wikibase:wikiGroup "wikipedia" ]; schema:about ?person.
  }
  GROUP BY ?person
  HAVING (?fame > 35)
} AS %get_fame
WITH
{
  SELECT ?person ?fame (GROUP_CONCAT(DISTINCT ?placeLabel; SEPARATOR=", ") AS ?birthPlace)
  WHERE
  {
    INCLUDE %get_fame
    OPTIONAL
    {
      ?person wdt:P19 ?place.
      ?place wdt:P131* wd:Q812.
      hint:Prior hint:gearing "forward".
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". ?place rdfs:label ?placeLabel. }
  }
  GROUP BY ?person ?fame
} AS %get_birthPlace
WITH
{
  SELECT ?person ?fame ?birthPlace (GROUP_CONCAT(DISTINCT ?placeLabel; SEPARATOR=", ") AS ?residence)
  WHERE
  {
    INCLUDE %get_birthPlace
    OPTIONAL
    {
      ?person wdt:P551 ?place.
      ?place wdt:P131* wd:Q812.
      hint:Prior hint:gearing "forward".
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". ?place rdfs:label ?placeLabel. }
  }
  GROUP BY ?person ?fame ?birthPlace
} AS %get_residence
WITH
{
  SELECT ?person ?fame ?birthPlace ?residence (GROUP_CONCAT(DISTINCT ?placeLabel; SEPARATOR=", ") AS ?educatedAt)
  WHERE
  {
    INCLUDE %get_residence
    OPTIONAL
    {
      ?person wdt:P69 ?place.
      ?place wdt:P131* wd:Q812.
      hint:Prior hint:gearing "forward".
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". ?place rdfs:label ?placeLabel. }
  }
  GROUP BY ?person ?fame ?birthPlace ?residence
} AS %get_educatedAt
WITH
{
  SELECT ?person ?fame ?birthPlace ?residence ?educatedAt (GROUP_CONCAT(DISTINCT ?placeLabel; SEPARATOR=", ") AS ?workedAt)
  WHERE
  {
    INCLUDE %get_educatedAt
    OPTIONAL
    {
      ?person wdt:P937 ?place.
      ?place wdt:P131* wd:Q812.
      hint:Prior hint:gearing "forward".
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". ?place rdfs:label ?placeLabel. }
  }
  GROUP BY ?person ?fame ?birthPlace ?residence ?educatedAt
} AS %get_workedAt
WHERE
{
  INCLUDE %get_workedAt
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?fame)
Try it! --Dipsacus fullonum (talk) 18:16, 2 May 2020 (UTC)
  • Nice query! I was just wondering how to avoid the likes of Q4617 in such queries. --- Jura 18:30, 2 May 2020 (UTC)

@Dipsacus fullonum Thank you so much this query! One thing I'm realizing would be good to include are people who are / were a 'member of a sports team' in Florida. I couldn't figure out how to get location from a sports team (such as Miami Heat: Q169138) to make this happen. If you can add it, it would help include people like Dwyane Wade (famous Miami Heat player), who is currently excluded.

I think you can use the location a sport team's home venue (home venue (P115)). I am not sure how much that will affect the running time of the query; it may or may not be possible to do it without timeout. I will try later. --Dipsacus fullonum (talk) 17:44, 3 May 2020 (UTC)
 : @Dipsacus fullonum: I think this solution will work, but haven't been able to quite get the query working.
* Thank you! I also realized that people like Eva Mendes (Q170606 - born in Miami) are not showing up for some reason. Not sure what filter is taking her off. --- QueryBeginner 12:30, 3 May 2020
Eva Mendes (Q170606) is on the list when I run the query with fame number 57. --Dipsacus fullonum (talk) 18:46, 3 May 2020 (UTC)
You're absolutely right. Apologies for my error and thanks for your help! --QueryBeginner (talk) 20:13, 3 May 2020 (UTC)
  • I am trying to add Twitter username P2002 and Instagram username P2003 to the output, but am getting non-aggregate errors. Which part of the query is best to add additional columns like these?
Update: Solved this by adding after "INCLUDE %get_workedAt":
OPTIONAL { ?person wdt:P2002 ?twitterHandle. }
OPTIONAL { ?person wdt:P2003 ?instagramHandle. }
@QueryBeginner: I added persons who played for a team with home venue in Florida. That increased the number of results with 27 to 137.
SELECT ?person ?personLabel ?fame ?birthPlace ?residence ?educatedAt ?workedAt ?playedFor
WITH
{
  SELECT DISTINCT ?team
  WHERE
  {
    ?team wdt:P115 ?venue.
    ?venue wdt:P131* wd:Q812.
  }
} AS %get_teams
WITH
{
  SELECT DISTINCT ?person
  WHERE
  {
    ?person wdt:P31 wd:Q5. # Is human
    ?person (wdt:P19 | wdt:P551 | wdt:P037 | wdt:P69) / wdt:P131* wd:Q812. # Born/lived/worked/educated in Florida
    hint:Prior hint:gearing "forward". # Search the property chain above forward
    ?person wikibase:sitelinks ?fame
    FILTER (?fame > 35)
    OPTIONAL { ?person wdt:P570 ?date_of_death. }
    FILTER (! BOUND(?date_of_death)) # No date of death, i.e. living
  }
} AS %first_selection
WITH
{
  SELECT DISTINCT ?person
  WHERE
  {
    INCLUDE %get_teams
    ?person wdt:P54 ?team.
    ?person wikibase:sitelinks ?fame
    FILTER (?fame > 35)
    OPTIONAL { ?person wdt:P570 ?date_of_death. }
    FILTER (! BOUND(?date_of_death)) # No date of death, i.e. living
  }
} AS %get_team_players
WITH
{
  SELECT DISTINCT ?person
  WHERE
  {
    { INCLUDE %first_selection }
    UNION
    { INCLUDE %get_team_players }
  }
} AS %get_persons
WITH
{
  SELECT ?person (COUNT(?wikipedialink) AS ?fame)
  WHERE
  {
    INCLUDE %get_persons
    ?wikipedialink schema:isPartOf [ wikibase:wikiGroup "wikipedia" ]; schema:about ?person.
  }
  GROUP BY ?person
  HAVING (?fame > 35)
} AS %get_fame
WITH
{
  SELECT ?person ?fame (GROUP_CONCAT(DISTINCT ?placeLabel; SEPARATOR=", ") AS ?birthPlace)
  WHERE
  {
    INCLUDE %get_fame
    OPTIONAL
    {
      ?person wdt:P19 ?place.
      ?place wdt:P131* wd:Q812.
      hint:Prior hint:gearing "forward".
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". ?place rdfs:label ?placeLabel. }
  }
  GROUP BY ?person ?fame
} AS %get_birthPlace
WITH
{
  SELECT ?person ?fame ?birthPlace (GROUP_CONCAT(DISTINCT ?placeLabel; SEPARATOR=", ") AS ?residence)
  WHERE
  {
    INCLUDE %get_birthPlace
    OPTIONAL
    {
      ?person wdt:P551 ?place.
      ?place wdt:P131* wd:Q812.
      hint:Prior hint:gearing "forward".
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". ?place rdfs:label ?placeLabel. }
  }
  GROUP BY ?person ?fame ?birthPlace
} AS %get_residence
WITH
{
  SELECT ?person ?fame ?birthPlace ?residence (GROUP_CONCAT(DISTINCT ?placeLabel; SEPARATOR=", ") AS ?educatedAt)
  WHERE
  {
    INCLUDE %get_residence
    OPTIONAL
    {
      ?person wdt:P69 ?place.
      ?place wdt:P131* wd:Q812.
      hint:Prior hint:gearing "forward".
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". ?place rdfs:label ?placeLabel. }
  }
  GROUP BY ?person ?fame ?birthPlace ?residence
} AS %get_educatedAt
WITH
{
  SELECT ?person ?fame ?birthPlace ?residence ?educatedAt (GROUP_CONCAT(DISTINCT ?placeLabel; SEPARATOR=", ") AS ?workedAt)
  WHERE
  {
    INCLUDE %get_educatedAt
    OPTIONAL
    {
      ?person wdt:P937 ?place.
      ?place wdt:P131* wd:Q812.
      hint:Prior hint:gearing "forward".
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". ?place rdfs:label ?placeLabel. }
  }
  GROUP BY ?person ?fame ?birthPlace ?residence ?educatedAt
} AS %get_workedAt
WITH
{
  SELECT ?person ?fame ?birthPlace ?residence ?educatedAt ?workedAt (GROUP_CONCAT(DISTINCT ?teamLabel; SEPARATOR=", ") AS ?playedFor)
  WHERE
  {
    INCLUDE %get_workedAt
    OPTIONAL
    {
      INCLUDE %get_teams
      ?person wdt:P54 ?team.
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". ?team rdfs:label ?teamLabel. }
  }
  GROUP BY ?person ?fame ?birthPlace ?residence ?educatedAt ?workedAt
} AS %get_playedFor
WHERE
{
  INCLUDE %get_playedFor
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?fame)
Try it! --Dipsacus fullonum (talk) 16:59, 8 May 2020 (UTC)
@Dipsacus fullonum: Thank you so much! Works great for me. --QueryBeginner
@Dipsacus fullonum: If I wanted to pull the city location for the values in "educatedAt" and "playedFor", which would be put in separate columns, is that possible without timing out?
@QueryBeginner: As there can be several schools or teams for one person, I think it is better to place the locations in the same columns so I did that. I see that there are two locations listed for Tampa Bay Rowdies (Q1046445). It is because they moved venue in 2011 and both is found. What do you think?
SELECT ?person ?personLabel ?fame ?birthPlace ?residence ?educatedAt ?workedAt ?playedFor
WITH
{
  SELECT DISTINCT ?team
  WHERE
  {
    ?team wdt:P115 ?venue.
    ?venue wdt:P131* wd:Q812.
  }
} AS %get_teams
WITH
{
  SELECT DISTINCT ?person
  WHERE
  {
    ?person wdt:P31 wd:Q5. # Is human
    ?person (wdt:P19 | wdt:P551 | wdt:P037 | wdt:P69) / wdt:P131* wd:Q812. # Born/lived/worked/educated in Florida
    hint:Prior hint:gearing "forward". # Search the property chain above forward
    ?person wikibase:sitelinks ?fame
    FILTER (?fame > 35)
    OPTIONAL { ?person wdt:P570 ?date_of_death. }
    FILTER (! BOUND(?date_of_death)) # No date of death, i.e. living
  }
} AS %first_selection
WITH
{
  SELECT DISTINCT ?person
  WHERE
  {
    INCLUDE %get_teams
    ?person wdt:P54 ?team.
    ?person wikibase:sitelinks ?fame
    FILTER (?fame > 35)
    OPTIONAL { ?person wdt:P570 ?date_of_death. }
    FILTER (! BOUND(?date_of_death)) # No date of death, i.e. living
  }
} AS %get_team_players
WITH
{
  SELECT DISTINCT ?person
  WHERE
  {
    { INCLUDE %first_selection }
    UNION
    { INCLUDE %get_team_players }
  }
} AS %get_persons
WITH
{
  SELECT ?person (COUNT(?wikipedialink) AS ?fame)
  WHERE
  {
    INCLUDE %get_persons
    ?wikipedialink schema:isPartOf [ wikibase:wikiGroup "wikipedia" ]; schema:about ?person.
  }
  GROUP BY ?person
  HAVING (?fame > 35)
} AS %get_fame
WITH
{
  SELECT ?person ?fame (GROUP_CONCAT(DISTINCT ?placeLabel; SEPARATOR=", ") AS ?birthPlace)
  WHERE
  {
    INCLUDE %get_fame
    OPTIONAL
    {
      ?person wdt:P19 ?place.
      ?place wdt:P131* wd:Q812.
      hint:Prior hint:gearing "forward".
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". ?place rdfs:label ?placeLabel. }
  }
  GROUP BY ?person ?fame
} AS %get_birthPlace
WITH
{
  SELECT ?person ?fame ?birthPlace (GROUP_CONCAT(DISTINCT ?placeLabel; SEPARATOR=", ") AS ?residence)
  WHERE
  {
    INCLUDE %get_birthPlace
    OPTIONAL
    {
      ?person wdt:P551 ?place.
      ?place wdt:P131* wd:Q812.
      hint:Prior hint:gearing "forward".
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". ?place rdfs:label ?placeLabel. }
  }
  GROUP BY ?person ?fame ?birthPlace
} AS %get_residence
WITH
{
  SELECT ?person ?fame ?birthPlace ?residence (GROUP_CONCAT(DISTINCT ?schoolAndPlace; SEPARATOR="; ") AS ?educatedAt)
  WHERE
  {
    INCLUDE %get_residence
    OPTIONAL
    {
      ?person wdt:P69 ?school.
      ?school wdt:P131 ?place.
      ?place wdt:P131* wd:Q812.
      hint:Prior hint:gearing "forward".
    }
    BIND (CONCAT(?schoolLabel, ", ", ?placeLabel) AS ?schoolAndPlace)
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
                             ?school rdfs:label ?schoolLabel.
                             ?place rdfs:label ?placeLabel. }
  }
  GROUP BY ?person ?fame ?birthPlace ?residence
} AS %get_educatedAt
WITH
{
  SELECT ?person ?fame ?birthPlace ?residence ?educatedAt (GROUP_CONCAT(DISTINCT ?placeLabel; SEPARATOR=", ") AS ?workedAt)
  WHERE
  {
    INCLUDE %get_educatedAt
    OPTIONAL
    {
      ?person wdt:P937 ?place.
      ?place wdt:P131* wd:Q812.
      hint:Prior hint:gearing "forward".
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". ?place rdfs:label ?placeLabel. }
  }
  GROUP BY ?person ?fame ?birthPlace ?residence ?educatedAt
} AS %get_workedAt
WITH
{
  SELECT ?person ?fame ?birthPlace ?residence ?educatedAt ?workedAt (GROUP_CONCAT(DISTINCT ?teamAndPlace; SEPARATOR="; ") AS ?playedFor)
  WHERE
  {
    INCLUDE %get_workedAt
    OPTIONAL
    {
      INCLUDE %get_teams
      ?person wdt:P54 ?team.
      ?team wdt:P115 ?venue.
      ?venue wdt:P131 ?place.
    }
    BIND (CONCAT(?teamLabel, ", ", ?placeLabel) AS ?teamAndPlace)
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
                             ?team rdfs:label ?teamLabel.
                             ?place rdfs:label ?placeLabel. }
  }
  GROUP BY ?person ?fame ?birthPlace ?residence ?educatedAt ?workedAt
} AS %get_playedFor
WHERE
{
  INCLUDE %get_playedFor
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?fame)
Try it! --Dipsacus fullonum (talk) 09:10, 14 May 2020 (UTC)
@Dipsacus fullonum: This is all I could have hoped for - I can't thank you enough for your help. --QueryBeginner

Release year of the video games[edit]

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 subject[edit]

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!

Query for extracting articles with some conditions[edit]

Hi, I kindly ask for a query that can extract articles in certain categories and certain sizes in Ar Wikipedia. --Mohammad Hijjawi (talk) 23:50, 8 May 2020 (UTC)

@Mohammad Hijjawi: It is possible for a SPARQL query to find articles in certain catagories in a Wikipedia using WMAPI (mw:Wikidata Query Service/User Manual/MWAPI, but I don't think it possible to know the article size in a query. My knowledge of PetScan (meta:PetScan) is limited but I think you can do this with that tool instead. --Dipsacus fullonum (talk) 00:18, 9 May 2020 (UTC)
@Dipsacus fullonum: Hi, Thanks for replying. I use PetScan to do that, but actually It's not working for about two weeks now. --Mohammad Hijjawi (talk) 01:18, 9 May 2020 (UTC)
I'd be interested too, for instance, get the first 300 character from the wikiarticle. If you wish, you might try to adapt this query (which is a string search)
SELECT DISTINCT ?item ?itemLabel
WHERE {
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:srsearch "[your search string] -haswbstatement:P31 -haswbstatement:P279" .
    ?title wikibase:apiOutput mwapi:title.
  }
  BIND(IRI(CONCAT(STR(wd:), ?title)) AS ?item)
  #add any other requirements here
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! Bouzinac (talk) 19:43, 9 May 2020 (UTC)
@Bouzinac: There are no info on Wikidata about the content of articles in Wikipedias. You can in a query invoke a search in a Wikipedia using its WMAPI (the proposed query above searches Wikidata's WMAPI, not the WMAPI of any Wikipedia), but a search gives article titles as result. You cannot retrieve article content from the article that way. --Dipsacus fullonum (talk) 20:06, 9 May 2020 (UTC)
@Mohammad Hijjawi: I was wrong. It is possible to know the size of Wikipedia articles in a Wikidata query. This example query will list articles in en:Category:Horses with less than 10000 bytes:
SELECT ?title ?size
WHERE {
  SERVICE wikibase:mwapi {
      bd:serviceParam wikibase:endpoint "en.wikipedia.org";
                      wikibase:api "Search";
                      mwapi:srsearch "incategory:Horses".
      ?title wikibase:apiOutput mwapi:title.
      ?sizestr wikibase:apiOutput "@size".
  }
  BIND ((xsd:integer(?sizestr)) AS ?size) # Convert from string to integer
  FILTER (?size < 10000)
}
Try it! and this query will do the same and also get the Wikidata item for each page if there is one:
SELECT ?item ?title ?size
WHERE {
  SERVICE wikibase:mwapi {
      bd:serviceParam wikibase:endpoint "en.wikipedia.org";
                      wikibase:api "Generator";
                      mwapi:generator "search";
                      mwapi:gsrsearch "incategory:Horses";
                      mwapi:gsrlimit "max";
                      mwapi:prop "info|pageprops".
      ?title wikibase:apiOutput mwapi:title.
      ?sizestr wikibase:apiOutput "@length".
      ?item wikibase:apiOutputItem mwapi:item.
  }
  BIND ((xsd:integer(?sizestr)) AS ?size) # Convert from string to integer
  FILTER (?size < 10000)
}
Try it! --Dipsacus fullonum (talk) 12:51, 10 May 2020 (UTC)
@Bouzinac: I have been reading manual pages and found a way to get extracts from Wikipedia articles. This query will give an extract of approx. 300 bytes for articles in the category en:Category:Horses in the English Wikipedia. Note that according to the documentation (mw:Extension:TextExtracts) you can max get 20 extracts per API call. The documentation also lists some caveats you may want to see.
SELECT ?item ?title ?extract
WHERE {
  SERVICE wikibase:mwapi {
      bd:serviceParam wikibase:endpoint "en.wikipedia.org";
                      wikibase:api "Generator";
                      mwapi:generator "search";
                      mwapi:gsrsearch "incategory:Horses";
                      mwapi:gsrlimit "max";
                      mwapi:prop "info|pageprops|extracts";
                      mwapi:exchars "300";
                      mwapi:exlimit "max";
                      mwapi:exintro "true".
      ?title wikibase:apiOutput mwapi:title.
      ?extract wikibase:apiOutput "extract/text()".
      ?item wikibase:apiOutputItem mwapi:item.
  }
}
Try it! --Dipsacus fullonum (talk) 14:06, 10 May 2020 (UTC)

@Dipsacus fullonum: @Bouzinac: Thanks a lot, you helped me so much and now I can extract the needed list. Greetings ♥ --Mohammad Hijjawi (talk) 13:49, 12 May 2020 (UTC)

Search to discover new items, not edited by a particular editor[edit]

What I’d like to do is filter the entire contents of a museum catalog (National Maritime Museum, in this case) to show all the items I have not made a change to? Recently I reviewed all of the images, in turn, within a museum and made bespoke edits to the majority of the items by adding to or changing a category; or perhaps by making a more discrete change, to a field,; by adding to a description or altering a date, source, author or license. These changes were only occasionally made by hot cat, they were specific to the individual files. Of course, over time and while this task was being undertaken new images were and are being continuously uploaded to the museum’s categories. What I’d like to do is filter the entire contents of the museum to show all the items I have not made a change to? I'm guessing my name would appear in the history page list. What I would want to see once a search was run, would be the thumbnail images. Is that possible? Broichmore (talk) 19:30, 11 May 2020 (UTC)

@Broichmore: I tried to get a list of all items with the statement location (P276) National Maritime Museum (Q1199924) with information about who edited them. This query using the MediaWiki API should do that:
SELECT DISTINCT ?item ?itemLabel ?contributor
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 "haswbstatement:P276=Q1199924".
    bd:serviceParam mwapi:gsrlimit "50".
    bd:serviceParam mwapi:prop "info|pageprops|contributors".
    bd:serviceParam mwapi:pcexcludegroup "bot|sysop|rollbacker|translationadmin".
    bd:serviceParam mwapi:pclimit "max".
    ?item wikibase:apiOutputItem mwapi:title.
    ?contributor wikibase:apiOutput "contributors/user[@name='Broichmore'][1]/@name".
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
But it only finds three items which you edited. I limited the request to get only 50 search results per API call (gsrlimit=50) in order not to exceed for limit for contributors which are 500, but that didn't help. There may be more limits to the prop=contributors request (https://www.wikidata.org/w/api.php?action=help&modules=query%2Bcontributors) but it do look fine when I look at the API calls separate (https://www.wikidata.org/w/api.php?action=query&generator=search&gsrsearch=haswbstatement:P276=Q1199924&gsrlimit=50&prop=info%7Cpageprops%7Ccontributors&format=xml&pcexcludegroup=bot%7Csysop%7Crollbacker%7Ctranslationadmin&pclimit=max + the continuation calls). Did you edit more items with the statement location (P276) National Maritime Museum (Q1199924) than Ships Trading in the East (Q50921795), The snow Mary Ann (Q50879707) and Scene in Plymouth Sound in August 1815 Sub-Title: The 'Bellerophon' with Napoleon Aboard at Plymouth (26 July - 4 August 1815) (Q50867190)? If so, please give examples and I will look more into it. --Dipsacus fullonum (talk) 09:29, 12 May 2020 (UTC)
Many thanks. That was interesting. I ran "Try it". and picked out some items at random. First: A Fresh Breeze in the Mediterranean (Q50858142); I have never made an edit to this item. I noticed I was taken to the wikidata page. but found no link there back to Commons? There were links to RMG (the museum) and ARTuk. Should we not include a link to commons, or did I miss it? All of the amples I looked at did not link back to commons. Second: The corvette HMS Rover (Q50858802); This item is not on commons. currently the museum shows it in their catalogue but has no picture of it to download. They have given a copy of it to ARTuk, so it could be downloaded, that way. My suspicion is that you have this record from scraping and that the image has never been on the RMG site just the record. ARTuk has exclusivity on the image, donated to it by the museum. Third: The Capture of the slaver Gabriel by HMS Acorn, 6 July 1841 (Q50868900). I have edited this. The report didn't indicate that I had. Fourth: Ships Trading in the East (Q50921795). I have edited this item and the report did indicate that I had. Fifth: Admiral Sir David Milne (1763-1845) (Q50866861). This man has five images. He as an individual is Q2755184. I located the image on commons after some detective work. Interesting. Q50866861 is not mentioned on the particular image commons page, and it's not in the hypertext edit page either. However there is a statement on the commons page which says: File usage on other wikis 'Wikidata:WikiProject sum of all paintings/Image suggestions/Institution and inventory number match'. I have never edited this page. Best regards, hope this of help. Broichmore (talk) 11:50, 12 May 2020 (UTC)
@Broichmore: The query above have nothing to do with Commons. It lists Wikidata items, as I supposed that your request was about Wikidata Items. If it isn't, then please explain again what you requested in a more precise way. --Dipsacus fullonum (talk) 10:17, 13 May 2020 (UTC)
@Dipsacus fullonum: Forgive the naive questions? The search gives us 3473 items each with a Q number, is that the total number of records displayed on the museum website? Each one given a Q record. Totally divorced away from what we have from the museum available on commons? So if we have an item on commons without a Q number, then I can get it from here? If that's the case when is it up to date too, and when will the museum site get scraped again for new entries? What frequency is it scraped? -Broichmore (talk) 17:51, 13 May 2020 (UTC)
@Broichmore: I have no idea about what is on the museum's website. I have never visited the website and I have no plans of doing so. The result contains Wikidata items which have the statement location (P276) National Maritime Museum (Q1199924). I don't know anything about them beyond that, or if there are images on Commons or not, who created the items or when they did it etc. I can help with WDQS queries if you know what to query about, and nothing else. --Dipsacus fullonum (talk) 18:20, 13 May 2020 (UTC)

Querying item with optional images and some info about the image[edit]

I have this query:

SELECT   ?item ?itemLabel ?image
WHERE
{
  ?item wdt:P214 "96987389".
  OPTIONAL  {   ?item wdt:P18 ?image. }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!


I would like along with it to retrieve some metadata about the optional image such as: license,artist, description etc. The data seems to exists in wikimedia query: here

However this is beyond my current sparql capabilities (which are currently very very basic).

Thanks in advance Doron

@Doronuziel: Your API call can be integrated into the query this way:
SELECT DISTINCT ?item ?itemLabel ?image ?imgLicense ?imgArtist
WHERE
{
  ?item wdt:P214 "96987389".
  OPTIONAL  { ?item wdt:P18 ?image. }
  BIND (wikibase:decodeUri(SUBSTR(STR(?image),52)) AS ?image_name)
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "commons.wikimedia.org".
    bd:serviceParam wikibase:api "Generator".
    bd:serviceParam wikibase:limit "once".
    bd:serviceParam mwapi:generator "allpages".
    bd:serviceParam mwapi:gapfrom ?image_name.
    bd:serviceParam mwapi:gaplimit "1".
    bd:serviceParam mwapi:gapnamespace "6".
    bd:serviceParam mwapi:prop "imageinfo".
    bd:serviceParam mwapi:iiprop "extmetadata".
    ?imgLicense wikibase:apiOutput "imageinfo/ii/extmetadata/LicenseShortName/@value".
    ?imgArtist wikibase:apiOutput "imageinfo/ii/extmetadata/Artist/@value".
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Dipsacus fullonum (talk) 16:09, 12 May 2020 (UTC)
@Dipsacus fullonum: Thank you so much

Demographics of Current Members of US Congress[edit]

So, I've never used Wikidata queries before. I am sure this ought to be easy, but right now it is defeating me.

I would like to have a query that reports the demographic characteristics of current members of the US Congress.

Returning something like:

Name, Party, Chamber (House/Senate), State, District (for House), Age, Gender, Ethnic Group (if specified), Religion (if specified)

Thanks in advance. Dragons flight (talk) 11:50, 12 May 2020 (UTC)

@Dragons flight: Here is a query. There are some problems with the data causing in some cases too many results when start time (P580) and end time (P582) qualifiers are missing or when there are multiple values for some properties.
SELECT ?item ?itemLabel ?partyLabel ?chamberLabel ?stateLabel ?districtLabel
       ((NOW() - ?date_of_birth)/365.25 AS ?age)
       ?genderLabel ?etnic_groupLabel ?religionLabel
WHERE
{
  ?item wdt:P31 wd:Q5. # item is a human
  VALUES ?chamber { wd:Q4416090 wd:Q13218630 } # US senator, US representative
  ?item p:P39 ?position_statement.
  ?position_statement ps:P39 ?chamber.
  ?position_statement a wikibase:BestRank.
  OPTIONAL { ?position_statement pq:P582 ?endtime. }
  FILTER (! BOUND(?endtime)) # No end time, ?item still has the position
  OPTIONAL
  {
    ?position_statement ps:P39 wd:Q4416090. # senator
    ?position_statement pq:P768 ?state.
  }
  OPTIONAL
  {
    ?position_statement ps:P39 wd:Q13218630. # representative
    ?position_statement pq:P768 ?district. 
    ?district wdt:P131 ?state.
    ?state wdt:P31 wd:Q35657. # ?state is a US state
  }
  OPTIONAL { ?position_statement pq:P4100 ?parliamentary_group. }
  OPTIONAL { ?position_statement a wdno:P4100. BIND("No party" AS ?parliamentary_group) }
  OPTIONAL { ?item wdt:P102 ?party_member. }
  OPTIONAL { ?item a wdno:P102. BIND("No party" AS ?party_member) }
  BIND (COALESCE(?parliamentary_group,?party_member) AS ?party)
  OPTIONAL { ?item wdt:P569 ?date_of_birth. }
  OPTIONAL { ?item wdt:P21 ?gender. }
  OPTIONAL { ?item wdt:P172 ?etnic_group. }
  OPTIONAL { ?item wdt:P140 ?religion. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it! --Dipsacus fullonum (talk) 13:29, 12 May 2020 (UTC)

Average age[edit]

This query gives me all new chess grandmasters per year:

SELECT ?year (COUNT(*) AS ?cnt) WHERE {
  ?item p:P2962 [ ps:P2962 wd:Q105269; pq:P580 ?start ] .
  BIND(YEAR(?start) AS ?year) .
} GROUP BY ?year ORDER BY ASC(?year)

Try it!

Now, I would like to have another column which contains the average age of the newly awarded grandmasters for each year. This might be difficult, but is it possible? Steak (talk) 12:48, 13 May 2020 (UTC)

@Steak: There might be some inaccuracy due to rounding to whole years, but this shoul do it:
SELECT ?year (COUNT(*) AS ?cnt) (FLOOR(AVG(?year - YEAR(?dob))) AS ?avg_age) WHERE {
  ?item p:P2962 [ ps:P2962 wd:Q105269; pq:P580 ?start ]; wdt:P569 ?dob .
  BIND(YEAR(?start) AS ?year) .  
} GROUP BY ?year ORDER BY ASC(?year)
Try it! --Dipsacus fullonum (talk) 13:18, 13 May 2020 (UTC)
Cool, thank you! Steak (talk) 13:59, 13 May 2020 (UTC)

How to select an additional value or a qualifier of a property[edit]

Hi everybody!

Starting from the following query,

SELECT ?collection ?collectionLabel ?date ?number ?creator ?creatorLabel WHERE {

 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
 ?collection wdt:P127 wd:Q3747226. # is property of 
 ?collection wdt:P31 wd:Q2668072. # type of 

}

I would like to select the date value (P580) from property P127 and the value number (P1114) from property P2670, But I miss something. I found in the manual the following example, but I can't understand the meaning and the origin of the expression "prov:wasDerivedFrom" in the first brackets and what I should write instead.

  1. See also the SPARQL manual
  2. [...]

SELECT ?item ?reference WHERE {

 ?item wdt:P1343 wd:Q51955019 .
 ?item p:P1343 [ prov:wasDerivedFrom [ pr:P958 ?reference ] ] .

}

@ ‎Carlobia: What you are asking for is qualifiers. "prov:wasDerivedFrom" is used to get references, not qualifiers so the example isn't usable here. It can done like this instead:
SELECT ?collection ?collectionLabel ?date ?number ?creator ?creatorLabel ?c WHERE {

 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
 ?collection wdt:P31 wd:Q2668072. # type of
 ?collection p:P127 ?owner_statement.
 ?owner_statement a wikibase:BestRank; ps:P127 wd:Q3747226 . # is property of
 OPTIONAL { ?owner_statement pq:P580 ?date . }
 OPTIONAL { ?collection p:P2670 [ a wikibase:BestRank; pq:P1114 ?number ] . }
}
Try it! --Dipsacus fullonum (talk) 14:44, 13 May 2020 (UTC)
@ ‎Dipsacus fullonum: Thank you very much! How do you choose prefix p, pq, or ps before the property? --Carlobia (talk) 17:57, 13 May 2020 (UTC)
@Carlobia: I suggest that you study the RDF datamodel document at mw:Wikibase/Indexing/RDF Dump Format and also look at this diagram taken from the document:
the data used in the description of a single item
The p: prefix is used to get statements about an item. From the full statements you get statement's value with the ps: prefix and any qualifier's value with pq:. There are also several other prefixes, some but not all of which are shown on the diagram. --Dipsacus fullonum (talk) 18:31, 13 May 2020 (UTC)
@ ‎Dipsacus fullonum: Very goog, thank you! That was what I was looking for! --Carlobia (talk) 13:57, 16 May 2020 (UTC)

Football players born within 100 km of a specific city[edit]

I tried to query that based on the example Places within 1km of the Empire State Building. I have tried many different approaches over many days and failed. This might require a sub-query or other advanced SPARQL knowledge which I do not currently possess. Here is my latest attempt https://w.wiki/Qof which I have tried to narrow down a bit hoping it was just timing out from the large JOIN it would have to do internally.

  • If you want to try yourself once more: start out from football players that have a place of birth, then calculate/filter the distance to whatever place you look for. --- Jura 13:14, 14 May 2020 (UTC)
@Flamur Kasa: The query tried first to find all football players, and there so many that it caused the timeout. It is better first to the places within 100 km of Funchal, so I moved that to a subquey:
SELECT ?player ?playerLabel ?place ?placeLabel ?location ?dist
WITH
{
  SELECT ?place ?location ?loc 
  WHERE
  {
    wd:Q25444 wdt:P625 ?loc .
    SERVICE wikibase:around
    {
      ?place wdt:P625 ?location .
      bd:serviceParam wikibase:center ?loc .
      bd:serviceParam wikibase:radius "100" .
    }
  }
}
AS %find_possible_locations
WHERE
{
  INCLUDE %find_possible_locations
  ?player wdt:P31 wd:Q5 .
  ?player wdt:P106 wd:Q937857 .
  ?player wdt:P413 wd:Q193592 .
  ?player wdt:P19 ?place .

  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
  BIND(geof:distance(?loc, ?location) as ?dist)
}
ORDER BY ?dist
Try it! --Dipsacus fullonum (talk) 13:18, 14 May 2020 (UTC)
I think that Jura's suggestion isn't good. That was essential what was tried and which timed out. --Dipsacus fullonum (talk) 13:21, 14 May 2020 (UTC)
It works for me and gives 61, not 21 results. --- Jura 13:24, 14 May 2020 (UTC)
Oh, I skipped P413. --- Jura 13:26, 14 May 2020 (UTC)

Same birth/death date[edit]

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)

Korean women scientists who have articles on Korean Wikipedia but not on English[edit]

Or whose articles are <500 bytes. I was playing a bit with query building myself, but I cannot figure out how to build this. --Piotrus (talk) 07:10, 16 May 2020 (UTC)

@Piotrus: There is a list of Korean women scientists who have articles on Korean Wikipedia. If an article on English Wikipedia exists, its link and size is shown.
SELECT ?item ?article_ko ?article_en ?size
WHERE
{
  ?item wdt:P31 wd:Q5. # ?item is human
  ?item wdt:P21 wd:Q6581072. # ?item is female
  ?item wdt:P27 wd:Q884. # ?item is South Korean citizen
  ?item wdt:P106/wdt:P279* wd:Q901. # ?item is scientist
  ?article_ko schema:about ?item.
  ?article_ko schema:isPartOf <https://ko.wikipedia.org/>.
  OPTIONAL
  {
    ?article_en schema:about ?item.
    ?article_en schema:isPartOf <https://en.wikipedia.org/>. 
    ?article_en schema:name ?title_en. 
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:api "Generator".
      bd:serviceParam wikibase:endpoint "en.wikipedia.org".
      bd:serviceParam wikibase:limit "once".
      bd:serviceParam mwapi:generator "allpages".
      bd:serviceParam mwapi:gapfrom ?title_en.
      bd:serviceParam mwapi:gaplimit "1".
      ?sizestr wikibase:apiOutput "@length".
    }
    BIND ((xsd:integer(?sizestr)) AS ?size) # Convert from string to integer
  }
}
ORDER BY ?size
Try it! --Dipsacus fullonum (talk) 09:53, 16 May 2020 (UTC)

Pages in Category X on pl wiki which exist on en wiki but don't have the Category:Y[edit]

Please tell me it is possible to get a list of articles like this. For example, en:Category:Warsaw University of Life Sciences alumni I just started on en wiki is well-populated on pl wiki under pl:Kategoria:Absolwenci Szkoły Głównej Gospodarstwa Wiejskiego, but only some of those bios are translated. Bonus points if you can direct me to a bot or such that would just add the relevant categories without me having to manually go and edit each article (I do use HotCat at least). --Piotrus (talk) 07:13, 16 May 2020 (UTC)

@Piotrus: It is possible:
SELECT ?item ?article_pl ?title_pl ?article_en ?title_en
WHERE
{
  {
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:endpoint "pl.wikipedia.org".
      bd:serviceParam wikibase:api "Generator".
      bd:serviceParam mwapi:generator "categorymembers".
      bd:serviceParam mwapi:gcmtitle "Kategoria:Absolwenci Szkoły Głównej Gospodarstwa Wiejskiego".
      bd:serviceParam mwapi:gcmnamespace "0".
      bd:serviceParam mwapi:gcmlimit "max".
      ?item wikibase:apiOutputItem mwapi:item.
      ?title_pl wikibase:apiOutput mwapi:title.
    }
    FILTER BOUND(?item) # This filter is very important. If some category members don't have items, without it
                        # the following triplets will try to get all wikipedia articles which inevitably will cause timeout
    ?article_pl schema:about ?item.
    ?article_pl schema:isPartOf <https://pl.wikipedia.org/>.
    ?article_en schema:about ?item.
    ?article_en schema:isPartOf <https://en.wikipedia.org/>.
    ?article_en schema:name ?title_en.
  }
  MINUS
  {
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:endpoint "en.wikipedia.org".
      bd:serviceParam wikibase:api "Generator".
      bd:serviceParam mwapi:generator "categorymembers".
      bd:serviceParam mwapi:gcmtitle "Category:Warsaw University of Life Sciences alumni".
      bd:serviceParam mwapi:gcmnamespace "0".
      bd:serviceParam mwapi:gcmlimit "max".
      ?item wikibase:apiOutputItem mwapi:item.
    }
  }
}
Try it! --Dipsacus fullonum (talk) 09:13, 16 May 2020 (UTC)

Televisions series, the demonym of their country of origin, their genre[edit]

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 request[edit]

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 disambiguations[edit]

I have the following query:

The following query uses these:

  • Properties: instance of (P31) View with Reasonator View with SQID, subclass of (P279) View with Reasonator View with SQID
    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 one[edit]

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 Oxonienses[edit]

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 query[edit]

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 list[edit]

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 results[edit]

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 of[edit]

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 metres[edit]

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 usage[edit]

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 senators[edit]

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 privacy[edit]

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 description[edit]

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]

WDQS - Table Result View - flipped.png


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 age[edit]

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)

SPARQL[edit]

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]]).

SPARQL[edit]

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 classes[edit]

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.671 83342
human 8.99 17980
Wikimedia category 5.381 10762
star 3.7805 7561
taxon 3.175 6350
galaxy 2.4935 4987
Wikimedia disambiguation page 1.456 2912
gene 1.362 2724
chemical compound 1.2405 2481
protein 1.167 2334
Wikimedia template 0.9805 1961
human settlement 0.7095 1419
village-level division in China 0.705 1410
mountain 0.6085 1217
painting 0.5145 1029
river 0.466 932
street 0.4605 921
astronomical radio source 0.4065 813
hill 0.4015 803
family name 0.3975 795
clinical trial 0.394 788
Wikimedia list article 0.373 746
encyclopedic article 0.3725 745
eclipsing binary star 0.333 666
high proper-motion star 0.3255 651
collection 0.3205 641
village 0.305 610
film 0.2825 565
asteroid 0.2735 547
lake 0.2655 531
building 0.248 496
album 0.2475 495
church building 0.231 462
Wikinews article 0.2305 461
house 0.2195 439
business 0.219 438
calendar day of a given year 0.2175 435
item of collection or exhibition 0.213 426
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 signs[edit]

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)