Wikidata

Wikidata:Request a query/Archive/2020/09

< Wikidata:Request a query‎ | Archive‎ | 2020
This page is an archive. Please do not modify it. Use the current page, even to continue an old discussion.

Query multiple given names

Hi there! I am new to SPARQL. So I am sorry for the case, I am asking a stupid question: How do I query multiple givenNames of a person? I tried something like this:

SELECT ?human ?humanLabel ?familyNameLabel ?givenNameLabel ?givenName2Label ?birthName ?countryOfCitizenShipLabel WHERE { 

 ?human wdt:P31 wd:Q5;
 OPTIONAL { ?human wdt:P734 ?familyName. }
 OPTIONAL { ?human wdt:P735 ?givenName. }
 OPTIONAL { ?human wdt:P735 ?givenName2. }
 OPTIONAL { ?human wdt:P1477 ?birthName. }
 OPTIONAL { ?human wdt:P27 ?countryOfCitizenShip. }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "de". }

}

Do you have an idea, how to select a second or third given name? Or even better: How to query for any given name. Thanks in advance!

You don't need to use ?givenName2, you'll get all given names in ?givenName. Or do you mean series ordinal (P1545)? If you want specific name, just use wd:Qnnn instead of variable. --Infovarius (talk) 21:04, 1 September 2020 (UTC)

Searching for p31=Q13442814 with title that begin with "Erratum" that have no P31=Q1348305

I hope to run a search for instance of (P31)=scholarly article (Q13442814) with that have (en) title (P1476) that begin with the word "Erratum" but also have no instance of (P31)=erratum (Q1348305). Hope this isn't a difficult one (I've never been disappointed with the support found here. Thank you all, Trilotat (talk) 23:30, 31 August 2020 (UTC)

@Trilotat: This query works for items with a label equal to the title of the article. That is the case in all the cases that I havd seen. 
SELECT DISTINCT ?item ?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 "haswbstatement:P31=Q13442814 -haswbstatement:P31=Q1348305 Erratum".
    bd:serviceParam mwapi:gsrlimit "max".
    ?item wikibase:apiOutputItem mwapi:title.
  }
  ?item wdt:P1476 ?title.
  FILTER STRSTARTS(?title, "Erratum")
}
Try it!
--Dipsacus fullonum (talk) 00:41, 1 September 2020 (UTC)
Thanks! This has given me more to do. That's a good thing, particularly now. I appreciate it. Trilotat (talk) 14:28, 1 September 2020 (UTC)

How to get data from wikidata using the QID

I wanted to know how can I get data using QID. I have some names, I use falcon 2.0 entity linker curl command( change it into python script) to get information of its QID. Now I want to use that QID to get the persons gender( male or female) or alias or other information. Can someone give an idea how can it be done. The code to get QID is given below. the link to falcon 2.0 is https://github.com/SDM-TIB/Falcon2.0.

import requests import json

response_list=[] person_names=[]

if __name__ == '__main__': 


   limit=100
   with open(filename, 'r') as in_file:
       in_reader = in_file.readlines()
       for data in in_reader:
           if limit > 0:
               person_names.append(data.rstrip()) 
               limit -=1
           else :
               break
               
               
   """
   Url of post request and header of type json create linking against each line of text.
   """
   
   
   url="https://labs.tib.eu/falcon/falcon2/api?mode=long"
   headers = {'Content-type': 'application/json'}
   for name in person_names:
       data = {"text":name }
       data_json = json.dumps(data)
       response = requests.post(url, data=data_json, headers=headers)
       print(response.content)

It gives output as http://www.wikidata.org/entity/Q42493 for the entity.  – The preceding unsigned comment was added by [[User:|?]] ([[User talk:|talk]] • contribs).

How to add subject to this query

I like this query. I don't remember who was kind enough to create it for me. I'd like to use it to search if appropriate main subject (P921) has been added to the items. How do I add subject in a way that returns results? Thank you. Trilotat (talk) 18:15, 1 September 2020 (UTC) 

SELECT DISTINCT ?item ?title ?doi
WHERE {
  hint:Query hint:optimizer "None".
{  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:srsearch "\"california\" haswbstatement:P31=Q13442814".
    ?page_title wikibase:apiOutput mwapi:title.
  } }
  
  BIND(IRI(CONCAT(STR(wd:), ?page_title)) AS ?item)
  
  ?item wdt:P31 wd:Q13442814;
        wdt:P1476 ?title;
        wdt:P356 ?doi.
  FILTER CONTAINS(LCASE(?title), "california").
  ?item wdt:P356 ?doi . FILTER(STRSTARTS(?doi,'10.1785'))
}
Try it!
Hi Trilotat. The only real change is that I added OPTIONAL { ?item wdt:P921 ?subject. } and the label service for ?subjectLabel, but besides that I also did several optimizations:
  1. Get the item directly from the MWAPI call instead of first ?page_title and then doing a manual conversion to ?item
  2. Don't get ?doi twice
  3. Remove the triple ?item wdt:P31 wd:Q13442814 from the SPARQL code because the MWAPI search already checks that condition. (Well, it doesn't check for rank. If that is important, reinsert the triple.)
  4. Don't turn the built-in optimizer off but instead move the MWAPI call to a named subquery to ensure that it is executed first
SELECT  ?item ?title ?subject ?subjectLabel ?doi
WITH
{
  SELECT ?item
  WHERE
  {
    SERVICE wikibase:mwapi {
      bd:serviceParam wikibase:api "Search";
                      wikibase:endpoint "www.wikidata.org";
                      mwapi:srsearch "\"california\" haswbstatement:P31=Q13442814".
      ?item wikibase:apiOutputItem mwapi:title.
    }
  }
} AS %mwapi
WHERE {
  INCLUDE %mwapi
  ?item wdt:P1476 ?title;
        wdt:P356 ?doi.
  FILTER CONTAINS(LCASE(?title), "california").
  FILTER STRSTARTS(?doi,'10.1785')
  OPTIONAL { ?item wdt:P921 ?subject. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Dipsacus fullonum (talk) 19:20, 1 September 2020 (UTC)
Awesome. I didn't understand half of what you said. Okay, less than half. Thanks, though!

I want to create a mix'n'match catalog with OpenStreetMap Wiki (Q18635431) pages for tags, keys and relations that are still not yet connected to Wikidata. Using Sophox (Q55840137) instead of WDQS, I need, in this order:

  • Entry ID (OSM wiki page URL title)
  • Entry name (OSM wikibase item label)
  • Entry description (OSM wikibase item description)

At an earlier stage, @Yurik suggested this query, but I'm having trouble adapting it to what I need for mix'n'match. I apologize in advance for asking this here as I know Sophox is slightly different from usual pure WDQS queries, but I have few other options at this time. NMaia (talk) 01:19, 28 August 2020 (UTC)

timeout

Dear fellows, I keep receiving timeout. Is there any solution? 

SELECT ?item 
WHERE
{
  ?item wdt:P496 ?reseachID .
  FILTER NOT EXISTS { ?item wdt:P106 [] }
  ?item wdt:P31 wd:Q5. 
}
LIMIT 100

Try it!

Luckyz (talk) 09:48, 30 August 2020 (UTC)

Would a search query be a solution for you? --Matěj Suchánek (talk) 10:02, 2 September 2020 (UTC)
On another look, I didn't receive a timeout with MINUS { ... }. --Matěj Suchánek (talk) 10:04, 2 September 2020 (UTC)
What do you mean with minus? Sorry for being newbie. Luckyz (talk) 10:47, 2 September 2020 (UTC)
SELECT ?item 
WHERE
{
  ?item wdt:P496 ?reseachID .
  MINUS{ ?item wdt:P106 [] }
  ?item wdt:P31 wd:Q5. 
}
LIMIT 100

Try it!

? Luckyz (talk) 10:47, 2 September 2020 (UTC)

Exactly. Sorry for being too brief. --Matěj Suchánek (talk) 12:56, 3 September 2020 (UTC)

License

Could someone here make a query of instances/subclasses of video games that have multiple copyright license (P275) with one of them being either freeware (Q178285) or free-to-play (Q1414510)? Thanks! --Trade (talk) 07:34, 3 September 2020 (UTC)

@Trade: 
SELECT ?item ?itemLabel ?free_license ?free_licenseLabel ?other_license ?other_licenseLabel
WHERE
{
  VALUES ?free_license { wd:Q178285 wd:Q1414510 }
  ?item wdt:P31 / wdt:P279 * wd:Q7889 . # ?item is instance of video game
  ?item wdt:P275 ?free_license .
  ?item wdt:P275 ?other_license .
  FILTER (?free_license != ?other_license)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Dipsacus fullonum (talk) 12:03, 3 September 2020 (UTC)

Find an author who created an item

Hello everyone,

Is it possible to find an author who created a Wikidata item with a SPARQL query?

Thanks in advance!

Yes: 
SELECT ?item ?creator ?timestamp ?edit_comment
WHERE
{
  VALUES ?titles { "Q42" "Q2013" "Q1000000" }
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "www.wikidata.org" .
    bd:serviceParam wikibase:api "Generator" .
    bd:serviceParam mwapi:generator "allpages" .
    bd:serviceParam mwapi:gapfrom ?titles .
    bd:serviceParam mwapi:gapto ?titles .
    bd:serviceParam mwapi:prop "revisions" .
    bd:serviceParam mwapi:rvprop "user|timestamp|comment" .
    bd:serviceParam mwapi:rvdir "newer" .
    bd:serviceParam mwapi:rvlimit "1" .
    ?item wikibase:apiOutputItem mwapi:title .
    ?creator wikibase:apiOutput "revisions/rev/@user" .
    ?timestamp wikibase:apiOutput "revisions/rev/@timestamp" .
    ?edit_comment wikibase:apiOutput "revisions/rev/@comment" .
    bd:serviceParam wikibase:limit "once" .
  }
}
Try it!
--Dipsacus fullonum (talk) 15:13, 2 September 2020 (UTC)
Thank you Dipsacus fullonum! And what I need change to find items by author name, e.g. Sartle.wiki.bot?
I don't think that it is possible to query for items created by a given user using a SPARQL query. You can get a list of a user's contributions with an action=query&list=usercontribs type API call, but these aren't available via the MWAPI service in SPARQL. --Dipsacus fullonum (talk) 17:19, 3 September 2020 (UTC)

Finding items that does not have a frwiki

Hello, I wanted to have any airport, ranked by patronage, that does not have a frwiki. That query times out, any thoughts ? 

SELECT ?item 
(sample(?number) as ?number)
WHERE {
  ?item wdt:P31/wdt:P279* wd:Q1248784.
?item p:P3872 ?statement.
?statement pq:P585 ?time.
?statement ps:P3872 ?number.
bind (YEAR(?time) AS ?year)
FILTER(YEAR(?time) =2017) .
   FILTER NOT EXISTS {
   ?article schema:about ?item .
   ?article schema:isPartOf <https://fr.wikipedia.org/> . #Targeting Wikipedia language where subjects has no article.
 }
} group by ?item 
order by desc(?number ) limit 2
Try it!

Bouzinac 💬●✒️●💛 15:05, 3 September 2020 (UTC)

@Bouzinac: You can do by finding the airports first in a named subquery before using frwiki filter. Note that the construct (sample(?number) as ?number) is illegal SPARQL code. The names of new aggregate variables may not already be in scope where they are created. 
SELECT ?item ?any_number
WITH
{
  SELECT ?item (SAMPLE(?number) AS ?any_number)
  WHERE
  {
    ?item wdt:P31/wdt:P279* wd:Q1248784.
    ?item p:P3872 ?statement.
    ?statement pq:P585 ?time.
    ?statement ps:P3872 ?number.
    BIND (YEAR(?time) AS ?year)
    FILTER (YEAR(?time) = 2017)
  }
  GROUP BY ?item
} AS %get_items
WHERE
{
  INCLUDE %get_items
  FILTER NOT EXISTS
  {
    ?article schema:about ?item .
    ?article schema:isPartOf <https://fr.wikipedia.org/> . #Targeting Wikipedia language where subjects has no article.
  }
}
ORDER BY DESC(?any_number)
Try it!
--Dipsacus fullonum (talk) 17:38, 3 September 2020 (UTC)

Most statements, instance of (P31) = human (Q5), no en wiki

I'm looking to contribute to 500 Women Wiki Scientists and thought I might start with Wikidata items with the most statements, instance of (P31) = human (Q5), sex or gender (P21)=female (Q6581072) with no en wiki. Would someone be kind enough to generate such a query? Many thanks, Trilotat (talk) 21:40, 4 September 2020 (UTC)

@Trilotat: It seems that the list is topped by female chess players with a statement for Elo rating for every month for many years. 
SELECT ?item ?itemLabel ?occupationLabel ?statements ?sitelinks
WHERE
{
  ?item wdt:P31 wd:Q5 .
  ?item wdt:P21 wd:Q6581072 .
  ?item wikibase:statements ?statements . hint:Prior hint:rangeSafe true .
  FILTER (?statements > 160)
  ?item wikibase:sitelinks ?sitelinks .
  MINUS
  {
    ?article schema:about ?item .
    ?article schema:isPartOf <https://en.wikipedia.org/> .
  }
  OPTIONAL { ?item wdt:P106 ?occupation . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?statements)
Try it!
--Dipsacus fullonum (talk) 07:20, 5 September 2020 (UTC)

Nigerian women

Hello,

Please i will like to write a query for Nigerian women missing on English Wikipedia stating their occupation, state of origin/residence and description.

Thanks Kaizenify (talk) 08:51, 5 September 2020 (UTC) 


SELECT DISTINCT ?item ?itemLabel ?itemDescription ?occLabel ?natLabel ?eduLabel ?pobLabel ?podLabel ?worklocLabel ?resLabel
{
    { ?item wdt:P27 wd:Q1033 } 
    UNION { ?item wdt:P19/wdt:P17 wd:Q1033 } 
    UNION { ?item wdt:P20/wdt:P17 wd:Q1033 } 
    UNION { ?item wdt:P551/wdt:P17 wd:Q1033 } 
    UNION { ?item wdt:P937/wdt:P17 wd:Q1033 } 
    UNION { ?item wdt:P69/wdt:P17 wd:Q1033 } 

    ?item wdt:P21 wd:Q6581072 .
    FILTER NOT EXISTS { [] schema:about ?item ; schema:isPartOf <https://en.wikipedia.org/> }

    OPTIONAL { ?item wdt:P106 ?occ }
    OPTIONAL { ?item wdt:P27 ?nat }
    OPTIONAL { ?item wdt:P19 ?pob }
    OPTIONAL { ?item wdt:P20 ?pod }
    OPTIONAL { ?item wdt:P551 ?res }
    OPTIONAL { ?item wdt:P69 ?edu }
    OPTIONAL { ?item wdt:P937 ?workloc  }

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

Try it!

Gives some of it. You can remove elements you don't want to include.

BTW we had Wikidata:Request_a_query/Archive/2020/03#Nigeria_women_biographies_without_photos_on_Wikipedia earlier. --- Jura 09:54, 5 September 2020 (UTC)

Combined age of podium F1

Hey there, I'm trying to get a query to get the age of the podiumplaces (1st, 2nd and 3rd) together for each race in formula 1 season 2020 2020 Formula One World Championship (Q41174436) at that moment in time. I started with one race, to implement the calculations so I could then easily extract it to all the races in the season and optionally to expanding to more seasons: 

SELECT ?item ?itemLabel ?rank ?dob ?eventtime ?age WHERE{
  wd:Q67123914 p:P710 [ps:P710 ?item ; pq:P1352 ?rank].
  wd:Q67123914 wdt:P585 ?eventtime.
  ?item wdt:P569 ?dob.    
  (?eventtime - ?dob) = ?age  # how to calculate this? because this is giving an error.
  Filter ( ?rank in (1, 2, 3)).       
 # and then finally sum the three ages together
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it! Thanks in advance! Q.Zanden questions? 13:17, 8 September 2020 (UTC)

@QZanden: You need to use BIND(a - b as c) for this, as follows:
SELECT ?item ?itemLabel ?rank ?dob ?eventtime ?age WHERE{
  wd:Q67123914 p:P710 [ps:P710 ?item ; pq:P1352 ?rank].
  wd:Q67123914 wdt:P585 ?eventtime.
  ?item wdt:P569 ?dob. 
  bind((?eventtime - ?dob)/365.25 as ?age)  # how to calculate this? because this is giving an error.
  Filter ( ?rank in (1, 2, 3)).       
 # and then finally sum the three ages together
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! 
SELECT ?item ?itemLabel ?rank ?dob ?eventtime ?age WHERE{
  wd:Q67123914 p:P710 [ps:P710 ?item ; pq:P1352 ?rank].
  wd:Q67123914 wdt:P585 ?eventtime.
  ?item wdt:P569 ?dob. 
  bind(floor((?eventtime - ?dob)/365.25) as ?age)  # how to calculate this? because this is giving an error.
  Filter ( ?rank in (1, 2, 3)).       
 # and then finally sum the three ages together
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
Times are normally recorded in days, which can get a bit confusing when their age is reported as eleven thousand! In the first version I've divided the age by 365.25 to give it in years, and in the second I've explicitly rounded it down to the whole year (so Bottas is 30, not 30.85). This might be more useful for adding up, as it's the normal way we report ages. Note that if we only have a year of birth for someone, it will count as 1 January. Andrew Gray (talk) 15:20, 8 September 2020 (UTC)
@QZanden: It seems only the Austrian Grand Prix item at the moment has statements with participant (P710), but when the data are available this code (building on both yours and Andrew Gray's code) will hopefully work: 
SELECT ?event ?eventLabel ?eventtime (SUM(?age) AS ?age_sum)
WHERE
{
  ?event wdt:P361 wd:Q41174436 .
  ?event p:P710 [ps:P710 ?item ; pq:P1352 ?rank] .
  ?event wdt:P585 ?eventtime .
  ?item wdt:P569 ?dob .
  BIND(xsd:integer(FLOOR((?eventtime - ?dob)/365.25)) AS ?age)
  FILTER ( ?rank in (1, 2, 3) )
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
GROUP BY ?event ?eventLabel ?eventtime
Try it!
--Dipsacus fullonum (talk) 20:58, 8 September 2020 (UTC)
@Andrew Gray, Dipsacus fullonum: Thanks for your help! I did'nt notice the other GPs don't have any information on participant (P710), but now also looking back at 2019 and 2018, they also don't have any data about participant (P710).
Q.Zanden questions? 21:10, 8 September 2020 (UTC)

Ordering items in dimensions view

I'm trying to order the items in a dimensions graph output so that the lines minimally cross. 

#defaultView:Dimensions
SELECT ?geneLabel ?proteinLabel ?interacts_withLabel WHERE {
  ?gene wdt:P703 wd:Q82069695 ; wdt:P31 wd:Q7187 .
  ?gene wdt:P688 ?protein .
  OPTIONAL {?protein wdt:P129 ?interacts_with.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  }
LIMIT 10000
Try it!

For example ORF1ab polyprotein gene in the left hand column encodes two proteins in the middle column (ORF1a and orf1ab) that should ideally be placed next to each other. Any ideas on how to do this? Any ideas appreciated! T.Shafee(evo&evo) (talk) 02:12, 9 September 2020 (UTC)

How to include Reference URL for Wikidata SPARQL queries?

Brand new user to Wikidata.

I have this query: https://w.wiki/bdj

I would like to include the referenceUrl on my website https://rembrandt-nono.netlify.app/ so when users click on the more button, they will open a new tab to either a wikipedia page or other source that will have more details on each item.

I looked at the wikidata page for some of the paintings and there some(not all) Reference Url for that item. I haven't been able to figure out how to extract that data in my query. Also if there is a better way to extract the references pls let me know.

Thanks in advance. N

Hi. Note that reference URL (P854) is not used at the item level, but as a part of the references for an item's individual statements. If you e.g. want to see the values of P854 when used in references to statements with the predicate instance of (P31), you can add 
OPTIONAL { ?painting p:P31 / prov:wasDerivedFrom / pr:P854 ?reference_URL. }
to your query. You can see a description of Wikidata's data model at mw:Wikibase/Indexing/RDF Dump Format. --Dipsacus fullonum (talk) 07:27, 9 September 2020 (UTC)

Thank you.

Beautify this query

Hello! I'm playing a little bit with twinned cities and I have built a query to show all twinned cities of Basque municipalities connected with lines. The result is pretty... great and ridiculous. Greatculous! If anyone comes with a better way of visualizing this, it would be awesome! 

#defaultView:Map{"hide":["?coord1", "?coord2", "?line"]}
SELECT DISTINCT ?item ?itemLabel ?hiri_senidetuak ?hiri_senidetuakLabel ?coord1 ?coord2 ?line WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  { ?item wdt:P31 wd:Q2074737. }
  UNION
  { ?item wdt:P31 wd:Q484170. }
  ?item (wdt:P131/(wdt:P131*)/^wdt:P527) wd:Q47588.
  ?item wdt:P190 ?hiri_senidetuak.
  ?item wdt:P625 ?coord1.
  ?hiri_senidetuak wdt:P625 ?coord2.
  ?item p:P625 [ ps:P625 [];  psv:P625 [ wikibase:geoLongitude ?coord1lon; wikibase:geoLatitude ?coord1lat; ] ].
  ?hiri_senidetuak p:P625 [ ps:P625 [];  psv:P625 [ wikibase:geoLongitude ?coord2lon; wikibase:geoLatitude ?coord2lat; ] ].
  BIND(CONCAT('LINESTRING (', STR(?coord1lon), ' ', STR(?coord1lat), ',', STR(?coord2lon), ' ', STR(?coord2lat), ')') AS ?str) .
  BIND(STRDT(?str, geo:wktLiteral) AS ?line) 
}
Try it!

Thanks! -Theklan (talk) 21:52, 9 September 2020 (UTC)

I see the problem. It is impossible to see where in Basque Country a line originates in a map of the whole world. I don't have a solution for that, but I can give a minor tip about SPARQL: instead of wdt:P131/(wdt:P131*) you can just use wdt:P131+ meaning one or more occurrences of wdt:P131. --Dipsacus fullonum (talk) 06:07, 10 September 2020 (UTC)

Persons from a certain time period and country

I would like to get a list of persons from a specific time range and country: The Sengoku Period (year around 1400-1600) of Japan.

I looked at notable figures that is already on Wikidata and found a date of birth field on some of them. How would I query using a date of birth from between 1400-1600 in the country of Japan? And would there be a better way to query this?

Thanks in advance N 

SELECT ?person ?personLabel ?date_of_birth
WHERE
{
  ?person wdt:P31 wd:Q5 . # ?person is human
  ?person wdt:P27 wd:Q17 . # ?person is Japanese
  ?person wdt:P569 ?date_of_birth . hint:Prior hint:rangeSafe true .
  FILTER("1400-00-00"^^xsd:dateTime <= ?date_of_birth &&
         ?date_of_birth < "1600-00-00"^^xsd:dateTime)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!
--Dipsacus fullonum (talk) 07:03, 10 September 2020 (UTC)

SPARQL query to get wikipedia sitelink to article by wikipedia item id in Wikidata

Hello everyone,

Is it possible with SPARQL to get Wikipedia sitelink by Wikipedia ID in wikidata (e.g. https://www.wikidata.org/wiki/Q10000 for Dutch Wikipedia) and article ID in wikidata (e.g. https://www.wikidata.org/wiki/Q5580). Now I getting "Wikimedia database name" with sparql SELECT ?wikiname WHERE {values ?wiki {wd:Q10000}.?wiki wdt:P1800 ?wikiname} to get "nlwiki" value and then using https://www.wikidata.org/w/api.php?action=wbgetentities&ids=Q5580&props=sitelinks/urls&sitefilter=nlwiki to get the link. Is it possible to do it with one SPARQL query? Or how I can optimize it?

Yes: 
SELECT ?sitelink
WHERE
{
  VALUES ?wiki { wd:Q10000 }
  VALUES ?item { wd:Q5580 }
  ?sitelink schema:about ?item . hint:Prior hint:runFirst true .
  ?sitelink schema:isPartOf ?wiki_website .
  ?wiki wdt:P856 ?wiki_website .
}
Try it!
--Dipsacus fullonum (talk) 16:54, 10 September 2020 (UTC)

I want to exclude items from a query if an 'instance of' claim is included in a VALUES list.

(The actual list will come from a subquery, so I'd like to avoid using FILTER with a bunch of hard-coded || operators) Below is my stab at such a query where I'm hoping to omit Jane Austen if she is either a person OR a biblical figure. Apparently, the FILTER NOT EXISTS treats ?ignorableTypes as an AND condition rather than an OR, which isn't want I'm hoping for. 

SELECT ?item ?itemLabel ?typeLabel
WHERE {
  VALUES ?ignorableType {
    wd:Q5
    wd:Q20643955
  }
  
  VALUES ?item {
    wd:Q36322
  }
  
  ?item wdt:P31 ?type .
  
  FILTER NOT EXISTS {?item wdt:P31 ?ignorableType}
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it! Realworldobject (talk) 19:15, 10 September 2020 (UTC)

@Realworldobject: Before the filter is applied you have a result with each value of ?ignorableType for each ?item. The filter do remove results for each match with an ignorable type, but leaves result with unmatched ignorable type. If you know the number of ignorable types, you can count the results for each item and filter out ?item where some results are removed:
SELECT ?item ?itemLabel ?typeLabel
WHERE {
  VALUES ?ignorableType {
    wd:Q5
    wd:Q20643955
  }
  
  VALUES ?item {
    wd:Q36322
  }
  
  ?item wdt:P31 ?type .
  
  FILTER NOT EXISTS {?item wdt:P31 ?ignorableType}
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?item ?itemLabel ?typeLabel
HAVING (COUNT(?item) = 2) # 2 is the number of ignorable types.
Try it!

--Dipsacus fullonum (talk) 07:55, 11 September 2020 (UTC)

Help for freeing data from wikipedia to wikidata

Would it be possible to have a list of ?item wdt:P31 wd:Q5. MINUS{ ?item wdt:P106 [] } present in Category w:it:Categoria:Cantanti lirici italiani and its subcats of the Italian wikipedia? many thanks --Goldmund100 (talk) 10:23, 11 September 2020 (UTC)

@Goldmund100: Yes, certainly: 
SELECT ?title ?item ?article_it
WHERE
{
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:api "Search" .
    bd:serviceParam wikibase:endpoint "it.wikipedia.org" .
    bd:serviceParam mwapi:srnamespace "0" .
    bd:serviceParam mwapi:srsearch 'deepcat:"Cantanti lirici italiani"' .
    ?title wikibase:apiOutput mwapi:title .
  }
  BIND (STRLANG(?title, "it") AS ?title_it)
  ?article_it schema:name ?title_it .
  ?article_it schema:about ?item .
  ?article_it schema:isPartOf <https://it.wikipedia.org/> .
  ?item wdt:P31 wd:Q5 .
  MINUS { ?item wdt:P106 [] } 
}
Try it!
--Dipsacus fullonum (talk) 13:50, 13 September 2020 (UTC)
Thank you very much. For my better understanding, why if I use as deepcat Compositori I get no results whether if I choose a category below Compositori per nazionalità I get 7 results. If I understand, I'm sure I will take the most from this query. Thank you very much. --Goldmund100 (talk) 07:08, 14 September 2020 (UTC)
@Goldmund100: It is caused by a bug in the deep category search. The search 'deepcat:"Compositori"' in the Italian Wikipedia gives only 3,616 results which are too few. The search deepcat:"Compositori per nazionalità" gives 4,579 results. The missing results in deep category searching is tracked in task Phab:T246568 in Phabricator. --Dipsacus fullonum (talk) 09:16, 14 September 2020 (UTC)
@Goldmund100: PS. Please note that even when this bug is fixed, deep category search will not work if the number of subcategories exceeds 256. And the MWAPI service in SPARQL can as maximum return 5,000 results from the Wikipedia API, so the query will never work with too big categories. --Dipsacus fullonum (talk) 10:10, 14 September 2020 (UTC)

Searching for people with a residence within X km from a given location

Hi All,

I've been using the following query to find African American artists in my museum's collections who have a residence listed in their record: 

SELECT ?item ?itemLabel ?itemDescription ?residence ?residenceLabel WHERE {
  ?item wdt:P31 wd:Q5;
    wdt:P172 wd:Q49085;
    wdt:P6379 wd:Q510324;
        wdt:P551 ?residence
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

I'd like to refine the results further by querying artists with a residence within an 80 km radius of Philadelphia. I'm pretty stumped on this one. Thanks in advance for the help!  – The preceding unsigned comment was added by PMAlibcat (talk • contribs). 

#defaultView:Map
SELECT DISTINCT 
  ?item ?itemLabel ?itemDescription
  ?p ?pLabel
  ?coords ?distance
WHERE
{
  wd:Q1345 wdt:P625 ?a0 .
  SERVICE wikibase:around {
      ?p wdt:P625 ?coords .
      bd:serviceParam wikibase:center ?a0 .
      bd:serviceParam wikibase:radius "80" .
      bd:serviceParam wikibase:distance ?distance .      
  }
  hint:Query hint:optimizer "None".
  ?item wdt:P551 ?p .
  ?item wdt:P31 wd:Q5 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?distance

Try it! Maybe the above helps you get there. --- Jura 20:27, 11 September 2020 (UTC)

Thank you so much! I was able to incorporate my other search parameters into the query you posted, too.

Bilingual place name sign (P1766)

Hello, how to list bilinguals place name sign (P1766) ? (and find those obviously bilingual but not designed as bilingual) ? Bouzinac 💬●✒️●💛 09:24, 14 September 2020 (UTC)

@Bouzinac: 
SELECT ?item ?itemLabel ?place_name_sign ?lang1Label ?lang2Label
WHERE
{
  ?item p:P1766 ?sign_statement .
  ?sign_statement a wikibase:BestRank .
  ?sign_statement ps:P1766 ?place_name_sign .
  ?sign_statement pq:P407 ?lang1 .
  ?sign_statement pq:P407 ?lang2 .
  FILTER (STR(?lang1) < STR(?lang2))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Dipsacus fullonum (talk) 10:20, 14 September 2020 (UTC)

Male tennis players who won a Grand Slam singles title

Hi, looking for a list of male (P21=Q6581097) tennis players (P106=Q10833314) who have won a singles title at a Grand Slam tournament sorted by height (P20148) in centimeters with tallest player listed first. Each player should only be listed once. Can this be done? Grand Slam tournaments are Australian Open (Q60874), French Open (Q43605), Wimbledon (Q41520) and US Open (Q123577). --Wolbo (talk) 15:53, 14 September 2020 (UTC)

This is one way to do it, there is missing data though, eg this year's winners, not sure if everyone has a height either
SELECT DISTINCT ?winner ?winnerLabel ?height_in_cm WHERE {
  ?x wdt:P31 wd:Q46190676 .                  # x is a tennis event
  ?x wdt:P2094 wd:Q16893072 .                # x's competiion class is men's singles
  ?x wdt:P361/wdt:P31/wdt:P361 wd:Q102113  . # x is part of a tournament that is part of a grand slam event
  ?x wdt:P1346 ?winner .                     # x has a winner
  ?winner  p:P2048/psn:P2048 [               # the winner has a height
     wikibase:quantityAmount ?height         # get normalized units (in metres)
  ] .
  BIND (100 * ?height AS ?height_in_cm)      # convert to centimetres
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC (?height_in_cm)                # order by height, tallest first
Try it!

Piecesofuk (talk) 19:20, 14 September 2020 (UTC)

My version made simultaneously is the essentially the same but includes players with unknown height: 
SELECT ?player ?playerLabel ?height_in_cm
WHERE
{
  ?player wdt:P31 wd:Q5 . # ?player is human
  ?player wdt:P21 wd:Q6581097 . # ?player is male
  ?player wdt:P106 wd:Q10833314 . # ?player is tennis player
  OPTIONAL {
    ?player p:P2048 / psn:P2048 / wikibase:quantityAmount ?normalized_height .
     BIND (?normalized_height * 100 AS ?height_in_cm)
  }
  
  wd:Q102113 wdt:P527 ?grand_slam . # ?grand_slam is a tennis grand slam tournament
  ?grand_slam_for_year wdt:P31 ?grand_slam . # ?grand_slam_for_year is a grand slam tourmament for a certain year
  ?event wdt:P361 ?grand_slam_for_year . # event is grand slam event
  ?event wdt:P2094 wd:Q16893072 . # event is a men's singles event
  ?event wdt:P1346 ?player . # ?player won ?event
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?player ?playerLabel ?height_in_cm
ORDER BY DESC(?height_in_cm)
Try it!
--Dipsacus fullonum (talk) 19:57, 14 September 2020 (UTC)
@Piecesofuk:, @Dipsacus fullonum:, thanks to both of you, seems too work nicely! --Wolbo (talk) 21:52, 14 September 2020 (UTC)

Timing out

Hello, why is this timing out? I wanted a list of Paris (Q90) streets/roads that havent any place name sign (P1766). 

SELECT ?item ?itemLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item wdt:P31/wdt:P279* wd:Q83620.
  ?item wdt:P131* wd:Q90.
    MINUS {
    ?item wdt:P1766 []  # exclude if there is an image
  }
}
Try it!

Bouzinac 💬●✒️●💛 18:58, 15 September 2020 (UTC)

Corrected myself in removing one *
SELECT ?item ?itemLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item (wdt:P31/(wdt:P279*)) wd:Q79007;
    wdt:P131 wd:Q90.
  MINUS { ?item wdt:P1766 []. }
  MINUS { ?item wdt:P576 []. }          
  MINUS { ?item wdt:P582 []. }  
}
Try it!
@Bouzinac: The first query times out because the SPARQL engine cannot see that the number of items which are a thoroughfare (Q83620) is much higher than the number of items in Paris (Q90). You can fix the timeout by forcing the code ?item wdt:P131* wd:Q90. to be executed first by putting it in a named subquery: 
SELECT DISTINCT ?item ?itemLabel
WITH
{
  SELECT ?item
  WHERE
  {
    ?item wdt:P131* wd:Q90.
  }
} AS %in_Paris
WHERE
{
  INCLUDE %in_Paris
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item wdt:P31/wdt:P279* wd:Q83620.
  MINUS {
    ?item wdt:P1766 []  # exclude if there is an image
  }
}
Try it!
--Dipsacus fullonum (talk) 06:35, 16 September 2020 (UTC)

Wikimedia Commons Query Service

This is about the Commons Query Service, however the concept is, I'd think identical. Since there is nobody sticking around at Commons for this type of questions I'd like to ask here

I have the following query: 

SELECT ?Urheber ?UrheberLabel ?Lizenz ?LizenzLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?Urheber wdt:P170 wd:Q15080600.
  OPTIONAL { ?Urheber wdt:P275 ?Lizenz. }
}
LIMIT 100

It returns everything I would like it to but in an unexpected way:

Urheber UrheberLabel Lizenz LizenzLabel
sdc:M371354 M371354 wd:Q50829104 Q50829104

Query

What I would like to see is:

Urheber UrheberLabel Lizenz LizenzLabel
sdc:M371354 Wiedereröffnung der S-Bahn Berlin am Bahnhof Eichkamp wd:Q50829104 GNU-Lizenz für freie Dokumentation Version 1.2 oder später

Just getting the same but unlinked for the label is not useful as you can imagine. It will be great if someone could assist in getting the label instead of the page id. Thanks for your time. --Marbot (talk) 07:25, 15 September 2020 (UTC)

@Marbot: The license labels are Wikidata data, so you need to make a federated query to the WDQS endpoint to get them: 
SELECT ?Urheber ?UrheberLabel ?Lizenz ?LizenzLabel WHERE {
  ?Urheber wdt:P170 wd:Q15080600.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
    ?Urheber rdfs:label ?UrheberLabel.
  }
  OPTIONAL { ?Urheber wdt:P275 ?Lizenz. }
  SERVICE <https://query.wikidata.org/sparql> {
    SERVICE wikibase:label {
      bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
      ?Lizenz rdfs:label ?LizenzLabel.
    }
  }
}
LIMIT 100
Try it!
--Dipsacus fullonum (talk) 13:44, 15 September 2020 (UTC)

@Dipsacus fullonum: Thanks a lot for this! Yeah, to make a federated query does make sense. And the results work also, however only for one label. As soon as I try to add more labels the results are visibly incorrect. 

SELECT ?Urheber ?UrheberLabel ?relevante_Person ?relevante_PersonLabel ?Ort ?OrtLabel ?Koordinaten_des_Standpunktes ?Gr_ndung__Erstellung_bzw__Entstehung_oder_Erbauung ?Lizenz ?LizenzLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?Urheber wdt:P170 wd:Q98908523.
  OPTIONAL { ?Urheber wdt:P3342 ?relevante_Person. }
  OPTIONAL { ?Urheber wdt:P276 ?Ort. }
  OPTIONAL { ?Urheber wdt:P1259 ?Koordinaten_des_Standpunktes. }
  OPTIONAL { ?Urheber wdt:P571 ?Gr_ndung__Erstellung_bzw__Entstehung_oder_Erbauung. }
  OPTIONAL { ?Urheber wdt:P275 ?Lizenz. }
    SERVICE <https://query.wikidata.org/sparql> {
    SERVICE wikibase:label {
      bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
      ?Lizenz rdfs:label ?LizenzLabel.
      ?relevante_Person rdfs:label ?relevante_PersonLabel.
      ?Ort rdfs:label ?OrtLabel.
    }
  }
}
LIMIT 100
Try it!

The first label detected is just copied over from the top to the bottom. I had hoped that the answer to my short query example would help me adopting the query I originally wanted to create. A complete failure, I am afraid.

I am interested in updating the structured data for all the files I uploaded to Commons. Thus I need to see what I added and also I would like to check for consistency. Thus I need a table showing 

link to file || label of the file || label of relevant person || label of location || coordinates of location || creation date || license label

I does not seem to be possible to omit the link to the relevant object, e.g. for label of relevant person I also get the link of relevant person, which is not necessary for my purpose. If these links can be omitted it will be cool (did not add them to the expected header).

In the end I have the feeling that there will be many more people, e. g. people working on the wiki loves monuments contest to get their data right for the suggested data model by the contest. Thus I think having a cool query here will not just be beneficial for me.

Again, thanks for all your assistance. I really appreciate this.

Cheers --Marbot (talk) 07:56, 17 September 2020 (UTC)

@Marbot: Please note that in my query above, I used the manual mode of the label service for both WCQS and WDQS. (See the label service manual at mw:Wikidata Query Service/User Manual#Label service). In your query, you use the label service for WCQS in automatic mode. I suppose that causes all the variables with names ending with "Label" to be bound before making the federated call to WDQS so you don't get any labels from WDQS. --Dipsacus fullonum (talk) 08:21, 17 September 2020 (UTC)
@Dipsacus fullonum: Thanks for the note. I was not able to convert it into a workable table showing the columns I indicated above, so this is the end of this story since admittedly I not have time to learn SPARQL queries at the moment. I will now do one item at once, i.e. label, person etc. based on you original suggestion. Hope that at least this will work. Anyhow, thanks a ton for trying to make a difference! Cheers --Marbot (talk) 14:21, 17 September 2020 (UTC) PS In the end I can easily do a VLOOKUP in Calc or Excel and merge all tables into one once all the data was added. From that point I can check consistency.
Huu, I'm afraid your query suggestion does not work either. 
SELECT ?Urheber ?UrheberLabel ?Ort ?OrtLabel WHERE {
  ?Urheber wdt:P170 wd:Q98908523.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "de".
    ?Urheber rdfs:label ?UrheberLabel.
  }
  OPTIONAL { ?Urheber wdt:P276 ?Ort. }
  SERVICE <https://query.wikidata.org/sparql> {
    SERVICE wikibase:label {
      bd:serviceParam wikibase:language "de".
      ?Ort rdfs:label ?OrtLabel.
    }
  }
}
LIMIT 1000
Try it!
If no data was added at all, it copies over the data from the last valid result before instead of leaving a blanc cell. So this is not usable at all. Now the story indeed came to an end since my work-around fell apart. What a pity. --Marbot (talk) 14:36, 17 September 2020 (UTC) PS I just see from the first results that this is not the only issue. What a, what a pity. :(

Is there a way to get just the QIDs and not their respective URL?

I use Quickstatements and I always have to search and replace the URL in Google sheets to get just the QIDs. Is there a way to get just the QIDs? Nonoumasy (talk) 23:01, 17 September 2020 (UTC)

@Nonoumasy: You can convert the URI to text and remove the first part: 
SELECT ?item ?item_as_text
{
  VALUES ?item { wd:Q1 }
  BIND (STRAFTER(STR(?item), "http://www.wikidata.org/entity/") AS ?item_as_text)
}
Try it!
--Dipsacus fullonum (talk) 23:45, 17 September 2020 (UTC)

Thanks Dipsacus

Erdos number

I've tried to build a query to find Erdos number of a person by definition 

SELECT ?work1Label ?work2Label ?work3Label WHERE {
  VALUES ?pers0 {wd:Q173746 wd:Q15873}
  VALUES ?persx {wd:Q56812165 wd:Q52015163 wd:Q77342071}
  ?work1 wdt:P50 ?pers0;
         wdt:P50 ?pers1.
  ?work2 wdt:P50 ?pers1;
         wdt:P50 ?pers2.
  ?work3 wdt:P50 ?pers2;
         wdt:P50 ?pers3.
  ?work4 wdt:P50 ?pers3;  wdt:P50 ?persx.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
Try it!

but it times out if E.n.>3. I see this example with gas.service and probably it is possible to adapt it for such relation as "being co-authors of a scientific work", but I failed. Anyone? --Infovarius (talk) 09:59, 16 September 2020 (UTC)

@Infovarius: Like this? 
SELECT ?item ?itemLabel ?calculated_Erdős_number ?stored_Erdős_number
WITH
{
  SELECT ?item ?depth
  WHERE
  {
    SERVICE gas:service
    {
     gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.SSSP" .
     gas:program gas:in wd:Q173746 .
     gas:program gas:out ?item .
     gas:program gas:out1 ?depth .
     gas:program gas:linkType wdt:P50 .
     gas:program gas:traversalDirection "Undirected" .
     gas:program gas:maxIterations 6 .
    }
    FILTER (FLOOR(?depth / 2) * 2 = ?depth) # Use only even depths. The odd numbers are the written papers
  }
} AS %get_items
WHERE
{
  INCLUDE %get_items
  BIND (xsd:integer(?depth/2) AS ?calculated_Erdős_number)
  OPTIONAL { ?item wdt:P2021 ?stored_Erdős_number . }
  SERVICE wikibase:label {bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }              
} 
ORDER BY ?calculated_Erdős_number
Try it!
--Dipsacus fullonum (talk) 20:37, 16 September 2020 (UTC)
@Dipsacus fullonum: I love it! I knew I something don't understand in gas-service... Although the query still times out at maxIterations=10 (ErdowNumber=5) but I got the result I wanted. --Infovarius (talk) 20:14, 18 September 2020 (UTC)

Get Wikipedia URLs (sitelinks) in Wikidata SPARQL query

I would like to get the wikipedia URls of the items from a SPARQL query. Here is my query: https://w.wiki/ccV

I tried using this solution to get the wikipedia articles but didn't have much luck: https://opendata.stackexchange.com/questions/6050/get-wikipedia-urls-sitelinks-in-wikidata-sparql-query

Thanks.--Nonoumasy (talk) 08:55, 18 September 2020 (UTC)Template:Nonoumasy

@Nonoumasy: 
#Battles, Sieges, Military Campaigns, Wars, Rebellions in the Sengoku Period
#Show label, link to English Wikipedia, year, coordinates, startTime
SELECT ?battle ?battleLabel ?wikipedia_link_en ?year ?coordinates
  (GROUP_CONCAT(DISTINCT ?participantLabel; SEPARATOR = " // ") AS ?participants)
WHERE {
  { ?battle (wdt:P361|wdt:P2348) wd:Q204023. }
  OPTIONAL { ?battle wdt:P710 ?participant. }
  OPTIONAL { ?battle (wdt:P585|wdt:P580) ?year. }
  OPTIONAL { ?battle wdt:P625 ?coordinates. }
  OPTIONAL { ?wikipedia_link_en schema:about ?battle ; schema:isPartOf <https://en.wikipedia.org/>. }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,ja".
    ?battle rdfs:label ?battleLabel.
    ?participant rdfs:label ?participantLabel.
  }
}
GROUP BY ?battle ?battleLabel ?wikipedia_link_en ?year ?typeBattle ?coordinates
ORDER BY (?year)
Try it!
--Dipsacus fullonum (talk) 18:01, 18 September 2020 (UTC)

Is it possible to have an array or list of values for a column?

I have this query: https://w.wiki/cVC

I was wondering if value(s) for a column in a query can be extracted as an array. For example, in a battle, there are multiple participants(wdt:P710)

  • wd:Q39040 Battle of Yamazaki 12 July 1582 Point(135.691333333 34.907055555) Toyotomi Hideyoshi
  • wd:Q39040 Battle of Yamazaki 12 July 1582 Point(135.691333333 34.907055555) Akechi Mitsuhide

vs.

  • wd:Q39040 Battle of Yamazaki 12 July 1582 Point(135.691333333 34.907055555) [Toyotomi Hideyoshi, Akechi Mitsuhide]

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


#defaultView:Map{"hide": "?coordinates"}
SELECT ?battle ?battleLabel ?year ?coordinates (GROUP_CONCAT(DISTINCT ?participantLabel; separator=" // ") as ?participants) 
WHERE 
{
  ?battle ( wdt:P361 | wdt:P2348 ) wd:Q204023.
 
  OPTIONAL { ?battle wdt:P710 ?participant. }
  OPTIONAL { ?battle wdt:P585 ?year. }
  OPTIONAL { ?battle wdt:P625 ?coordinates. }

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,ja". 
                         ?battle rdfs:label ?battleLabel .
                         ?participant rdfs:label ?participantLabel .
                         }
}
GROUP BY ?battle ?battleLabel ?year ?coordinates
ORDER BY (?year)

Try it!

Try the above with GROUP_CONCAT. This requires using the label service in manual mode.

BTW, time period (P2348) seems preferable over part of (P361). A bot could move them over for you. --- Jura 10:45, 17 September 2020 (UTC)

Thanks @Jura:

Yes, I would also prefer time period. I was going to use Quick statements to add a statement of 'time period' of "sengoku period" . How does the bot work? Do I do that here? https://www.wikidata.org/wiki/Wikidata:Bot_requests  – The preceding unsigned comment was added by Nonoumasy (talk • contribs).

Yes, or [1] should take care of it. It might take a couple of days. --- Jura 10:36, 19 September 2020 (UTC)

ThanksThanks @Jura:

Scholarly articles with images on Commons

Hello, I would want a request to see all the scholarly article (Q13442814) published in (P1433) ZooKeys (Q219980) publication date (P577) within the year 2018, and to see if the items has (or not) a value for Commons category (P373). Christian Ferrer (talk) 18:21, 19 September 2020 (UTC) 

SELECT ?item ?itemLabel ?date ?commonscat ?commonssitelink WHERE {
  ?item wdt:P1433 wd:Q219980; wdt:P577 ?date; wdt:P31 wd:Q13442814 .
  FILTER(YEAR(?date) = 2018) .
  OPTIONAL { ?item wdt:P373 ?commonscat }
  OPTIONAL { ?commonssitelink schema:about ?item; schema:isPartOf <https://commons.wikimedia.org/> }
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} ORDER BY ASC(?date)
Try it!

 —MisterSynergy (talk) 18:25, 19 September 2020 (UTC)

Several query requests in one

Not sure if I should post several query request. Since they are related I thought I would just post one. I am trying to finish my site. https://sengoku.netlify.app/#

So, I'm trying to refine the query: https://w.wiki/cjq

Many thanks to the help I've gotten so far.

These are my request:

  1. I would like to get the Battles, sieges, and campaigns of the Sengoku Period. So what I did was query the 'part of' (P361) | 'time period'(P2348) of the Sengoku Period(Q204023). However, some of the results are people from this period. Is there a way to subtract people from the query or should I just use a UNION of 'instance of' for 'battle', 'siege', and 'military campaign'.
  2. Can I get Coordinate location(P625) of location (PP276).
  3. Get english or Japanese Wikipedia url links
  4. Create a column called ‘type’ for ‘battle’, ‘siege’, ‘military campaign’.
  5. Use only 1 coordinate location for each item.
  6. Get images(optional) for each participant.

I know this is query is long. Hopefully its ok. Thanks in advance.

--Nonoumasy (talk) 10:34, 19 September 2020 (UTC)Template:Nonoumasy 

SELECT ?battle ?battleLabel 
  ?type ?typeLabel 
  ?wikipedia_link_en ?wikipedia_link_ja 
  (YEAR(?year) as ?someyear)
  (SAMPLE(?coordinates) as ?some_coordinates)
  (GROUP_CONCAT(DISTINCT ?participantLabel; SEPARATOR = " // ") AS ?participants)
  (SAMPLE(?image) as ?some_image)
WHERE
{
  { ?battle (wdt:P361 | wdt:P2348) wd:Q204023. }
  FILTER NOT EXISTS {  ?battle wdt:P31 wd:Q5 } #exclude people
  # ?battle wdt:P31/wdt:P279* wd:Q645883 .     #only military operations or subtypes 
  OPTIONAL { ?battle wdt:P31 ?type }
  OPTIONAL { ?battle wdt:P710 ?participant. 
              OPTIONAL { ?participant wdt:P18 ?image }
           
           }
  ?battle (wdt:P585 | wdt:P580) ?year. 
  OPTIONAL { ?battle wdt:P625 ?coordinates0. }
  OPTIONAL { ?battle wdt:P276 / wdt:P625 ?coordinates1. }
  BIND( COALESCE(?coordinates0, ?coordinates1) as ?coordinates)
  OPTIONAL { ?wikipedia_link_en schema:about ?battle ; schema:isPartOf <https://en.wikipedia.org/>. }
  OPTIONAL { ?wikipedia_link_ja schema:about ?battle ; schema:isPartOf <https://ja.wikipedia.org/>. } 
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,ja".
    ?battle rdfs:label ?battleLabel.
    ?participant rdfs:label ?participantLabel.
    ?type rdfs:label ?typeLabel.
  }
}
GROUP BY ?battle ?battleLabel  ?type ?typeLabel ?wikipedia_link_en ?wikipedia_link_ja ?year ?typeBattle
ORDER BY (?year)

Try it!

  • The above does some of it except #6 (images: it still needs some work to build a presentable gallery).
Currently I just filter out people but eventually you might want to filter by types, to avoid that some other random things slip in, e.g. Chūgoku Ōgaeshi (Q10873539) which uses different location properties. --- Jura 10:55, 19 September 2020 (UTC)

Thanks @Jura:. This is great! I will study and learn from this query.

  1. Is there a way to make the Japanese wikipedia link conditional. Show only if english wikipedia not available? I'm just using it as backup so I don't have an empty value. Ideally I have one link for each item. Do I do a BIND here like you did with coordinates?
  2. Also, is it possible to get multiple image links for the participant? I'll convert this column into an array in Python and then turn it images in the html. is this where GROUP CONCAT would help?
  3. btw url shortener fails

--Nonoumasy (talk) 20:56, 19 September 2020 (UTC)Template:Nonoumasy

@Nonoumasy:
  1. Yes, add BIND( COALESCE(?wikipedia_link_en, ?wikipedia_link_ja) as ?wikipedia_link) and replace ?wikipedia_link_en ?wikipedia_link_ja with ?wikipedia_link in both SELECT and GROUP BY.
  2. Yes, replace (SAMPLE(?image) as ?some_image) with (GROUP_CONCAT(DISTINCT ?image; SEPARATOR = " // ") AS ?images) in SELECT.
  3. Yes, it cannot handle long queries.
--Dipsacus fullonum (talk) 06:37, 20 September 2020 (UTC)

Thanks @Dipsacus fullonum: Template:Nonoumasy

@Jura: Since the images provided are less than ideal. I think I'll fill those in in Python. Having the participantLabels is a good foundation. --Nonoumasy (talk) 11:22, 20 September 2020 (UTC)Template:Nonoumasy

Last names for Norwegian citizens

I want to have listed all last names for Norwegian having country of citizenship (P27) Norway (Q20) and who having last names where the family name (P734) also have been said to be the same as (P460). Breg Pmt (talk) 18:37, 20 September 2020 (UTC) 

SELECT
  ?item ?itemLabel ?count
  (GROUP_CONCAT(DISTINCT ?otherLabel; separator=", ") as ?others) 
WITH
{
  SELECT ?item (COUNT(DISTINCT ?p) as ?count) WHERE { ?p wdt:P27 wd:Q20 ; wdt:P734 ?item } GROUP BY ?item
} as %surnames
WHERE
{
    hint:Query hint:optimizer "None".
    INCLUDE %surnames
    OPTIONAL { ?item wdt:P460 ?other }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
                           ?item rdfs:label ?itemLabel .
                           ?other rdfs:label ?otherLabel .
                           }
}
GROUP BY ?item ?itemLabel ?count
ORDER BY DESC(?count)

Try it! @Pmt:. Maybe "?other" should be filtered for Norwegians as well, but I couldn't get that to work. --- Jura 18:53, 20 September 2020 (UTC)

@Jura1: Not so unexspected the query was timing out, could it be limited in some way, only men in one and women in another for instance? Pmt (talk) 19:40, 20 September 2020 (UTC)
Try with the hint I just added. If it times out, retry once or twice. It worked for me, but close to 60s . --- Jura 19:50, 20 September 2020 (UTC)
Very nice! It works, then next step would be to have a list only for persons having an ID in Histreg.no Norwegian historical register of persons ID (P4574) Pmt (talk) 20:33, 20 September 2020 (UTC)
@Jura1, Pmt: Try to replace COUNT(DISTINCT ?p) with COUNT(*) in the query. The change makes the query to execute in nearly half time (22 seconds instead of 40 seconds for me). It will only give different results if somebody has the same name more than once, which never or almost never happens as far as know, so I don't think that is an issue. --Dipsacus fullonum (talk) 21:45, 20 September 2020 (UTC)
SELECT DISTINCT ?all ?names
WITH
{
SELECT ?other ?nl (COUNT(DISTINCT ?p ) as ?count)
WHERE 
{
    hint:Query hint:optimizer "None".
    ?p wdt:P27 wd:Q20 ; wdt:P21 wd:Q6581072 ; wdt:P734 ?item . 
    ?item wdt:P1705 ?nl . 
    { ?item wdt:P460* ?other } UNION { ?other wdt:P460* ?item } .
} 
GROUP BY ?other ?nl
ORDER BY ?nl
} as %a
WHERE
{
  SELECT ?other (sum(?count) as ?all) (GROUP_CONCAT(DISTINCT CONCAT(?nl, " (",str(?count),"), ")  ) as ?names)
  { INCLUDE %a }
  GROUP bY ?other  
}
ORDER BY DESC(?all)
LIMIT 100

Try it!

@Dipsacus fullonum: good point. Thanks. @Pmt: Above, a version that tries to do one line/count per group of names, e.g. "Hanson, Hansson, Hansen, Hanssen, Hansén, Hanssønn" would have just the total of 396 (Hansen) + 62 (Hanssen) + 32 (Hansson) etc. It's currently limited to females. BTW, P460 isn't strictly symmetric. --- Jura 07:24, 21 September 2020 (UTC)

Get all qualifiers and unit of a property

Hello, I've a Property on an item who have a value with a unit and multiples qualifiers who can contains quantity qualifier.
I want to retrieve :

  • the value
  • the unit of the value if it's a Quantity property
  • the qualifier and it's value
    • if one of the qualifier is a quantity property, get the unit

I can't find a way to correctly retrieve all these data. Thank you, Myst (talk) 17:38, 19 September 2020 (UTC)

@Myst: 
SELECT ?item ?property ?value ?unit ?qualifier ?qualifier_value ?qualifier_unit
WHERE
{
  VALUES ?item { wd:Q658 }
  VALUES ?property { wd:P2054 }
  ?property wikibase:claim ?claim .
  ?property wikibase:statementValue ?statementValue .

  # Get amount and unit for the statement
  ?item ?claim ?statement .
  ?statement ?statementValue [wikibase:quantityAmount ?value; wikibase:quantityUnit ?unit] .
  
  # Get qualifiers
  OPTIONAL
  {
    {
      # Get simple values for qualifiers which are not of type quantity
      ?statement ?pq ?qualifier_value .
      ?qualifier wikibase:qualifier ?pq .
      ?qualifier wikibase:propertyType ?qualifer_property_type .
       FILTER (?qualifer_property_type != wikibase:Quantity)
    }
    UNION
    {
      # Get amount and unit for qualifiers of type quantity
      ?statement ?pqv [wikibase:quantityAmount ?qualifier_value; wikibase:quantityUnit ?qualifier_unit] .
      ?qualifier wikibase:qualifierValue ?pqv .
    }
  }
}
Try it!
--Dipsacus fullonum (talk) 07:46, 20 September 2020 (UTC)
PS. I just modified the query with addition of an OPTIONAL clause around the UNION to be able to handle cases which don't have qualifiers. --Dipsacus fullonum (talk) 08:06, 20 September 2020 (UTC)
Hello Dipsacus fullonum, really thank you for your help. I tested it quickly on my wikibase and this seem perfect. I tried something similar since this morning. I will try to understand your answer to know how it work. Thank you again. Myst (talk) 15:26, 20 September 2020 (UTC)
Hi Myst, you are welcome to ask me questions if needed to understand the code. --Dipsacus fullonum (talk) 17:40, 20 September 2020 (UTC)
I tried to improve the code but without full success. It work on my wikibase but I got a timeout on wikidata. If I understand your code the part "?statement ?statementValue [wikibase:quantityAmount ?value; wikibase:quantityUnit ?unit] ." only work for Quantity type property. I tried to change that to have only ?value if it's not a Quantity and your code otherwise, like you do for the qualifier, but it seem I miss something. Can you show me how to do correctly ? Thanks. Myst (talk) 18:16, 20 September 2020 (UTC)
My try Myst (talk) 19:02, 20 September 2020 (UTC)
@Myst: My comments to the try:
  1. Don'ẗ use { { graph pattern } } where { graph pattern } is sufficient. You just make the code more complicated without any reason.
  2. I think the reason for your timeout was 
      {{
    ?statement ?propertyStatement ?value .
    ?property wikibase:propertyType ?property_type .
    FILTER (?property_type != wikibase:Quantity)
  }}
    which makes a join between all qualifiers, normalized qualifiers etc. and all properties. The previous defined values for ?propertyStatement and ?property are not in scope in this inner graph pattern, so the code produces very many results which will only be pruned much later. Scopes in SPARQL goes bottom-up as explained in this article.
  3. You had ?statement ?pq ?qval . and ?statement ?pqv [wikibase:quantityAmount ?qval; wikibase:quantityUnit ?qunit] . but never used ?qval and ?qunit.
I would rewrite your code to: 
SELECT ?item ?property ?value ?unit ?qualifier ?qualifier_value ?qualifier_unit
WHERE
{
  VALUES ?item { wd:Q658 }
  VALUES ?property { wd:P2054 }

  ?property wikibase:claim ?claim .

  # Get amount and unit for the statement
  ?item ?claim ?statement .
  {
    ?property wikibase:propertyType ?property_type .
    FILTER (?property_type != wikibase:Quantity)
    ?property wikibase:statementProperty ?propertyStatement .
    ?statement ?propertyStatement ?value .
  }
  UNION
  {
    ?property wikibase:statementValue ?statementValue .
    ?statement ?statementValue [wikibase:quantityAmount ?value; wikibase:quantityUnit ?unit] .
  }

  # Get qualifiers
  OPTIONAL
  {
    {
      # Get simple values for qualifiers which are not of type quantity
      ?statement ?pq ?qualifier_value .
      ?qualifier wikibase:qualifier ?pq .
      ?qualifier wikibase:propertyType ?qualifer_property_type .
      FILTER (?qualifer_property_type != wikibase:Quantity)
    }
    UNION
    {
      # Get amount and unit for qualifiers of type quantity
      ?statement ?pqv [wikibase:quantityAmount ?qualifier_value; wikibase:quantityUnit ?qualifier_unit] .
      ?qualifier wikibase:qualifierValue ?pqv .
    }
  }
}
Try it!
--Dipsacus fullonum (talk) 21:18, 20 September 2020 (UTC)
Thank you again for your answer. For the first two points, it's because I tried to "merge" my final code from my python script to your example and the lack of sleep make me miss some parts. (And it seem WDQS automatically convert {{ }} to { }). I use qval and qunit in my script (it's from the previous version of the code, I reuse the same variable, that's why I renamed them in the query).
After comparing the two codes, I can see my timeout issue was from the ?statementValue being outside of the UNION where it's used. And that seem logic.
I'll adapt it for my use this evening. Myst (talk) 10:59, 21 September 2020 (UTC)
@Dipsacus fullonum: Everything is perfect. I've updated the code to retrieve the references and change a variable name 
SELECT ?item ?property ?value ?unit ?pq ?qualifier_value ?qualifier_unit ?ref ?pr ?rval
WHERE
{
  VALUES ?item { wd:Q658 }
  VALUES ?property { wd:P2054 }

  ?property wikibase:claim ?claim .

  # Get amount and unit for the statement
  ?item ?claim ?statement .
  {
    ?property wikibase:propertyType ?property_type .
    FILTER (?property_type != wikibase:Quantity)
    ?property wikibase:statementProperty ?propertyStatement .
    ?statement ?propertyStatement ?value .
  }
  UNION
  {
    ?property wikibase:statementValue ?statementValue .
    ?statement ?statementValue [wikibase:quantityAmount ?value; wikibase:quantityUnit ?unit] .
  }

  # Get qualifiers
  OPTIONAL
  {
    {
      # Get simple values for qualifiers which are not of type quantity
      ?statement ?propQualifier ?qualifier_value .
      ?pq wikibase:qualifier ?propQualifier .
      ?pq wikibase:propertyType ?qualifer_property_type .
      FILTER (?qualifer_property_type != wikibase:Quantity)
    }
    UNION
    {
      # Get amount and unit for qualifiers of type quantity
      ?statement ?pqv [wikibase:quantityAmount ?qualifier_value; wikibase:quantityUnit ?qualifier_unit] .
      ?pq wikibase:qualifierValue ?pqv .
    }
  }

  # get references
  OPTIONAL {
    ?statement prov:wasDerivedFrom ?ref .
    ?ref ?pr ?rval .
    [] wikibase:reference ?pr
  }
}
Try it!
Thank you again. Myst (talk) 16:40, 21 September 2020 (UTC)

Request to get a list of all the paintings and all the properties of the painting.

Hi everyone,

I am trying to get a list of all the paintings and their details like the creator, origin, location, inception data etc.

With a basic SPARQL query like: SELECT ?item ?itemLabel WHERE { ?item wdt:P31 wd:Q3305213 . SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } }

I got approx 450k results.

Now I want all the attributes for each of the painting, so I made a query like:

SELECT ?item ?itemLabel ?ImageLabel ?height ?width ?painterLabel ?movementLabel ?subjectLabel ?depictsLabel ?genreLabel ?materialLabel ?locationLabel ?CountryLabel ?collectionLabel ?inventoryNumberLabel ?copyrightLabel ?Inception ?originLabel ?origin_countryLabel WHERE { ?item wdt:P31 wd:Q3305213 . OPTIONAL { ?item wdt:P18 ?Image }. OPTIONAL { ?item wdt:P170 ?painter }. OPTIONAL { ?item wdt:P571 ?Inception }. OPTIONAL { ?item wdt:P17 ?Country }. #use CountryLabel OPTIONAL { ?item wdt:P195 ?collection }. #use collectionLabel OPTIONAL { ?item wdt:P135 ?movement }. OPTIONAL { ?item wdt:P276 ?location }. #use locationLabel OPTIONAL { ?item wdt:P217 ?inventoryNumber }. #use inventoryNumberLabel OPTIONAL { ?item wdt:P921 ?subject }. #use subjectLabel OPTIONAL { ?item wdt:P186 ?material }. OPTIONAL { ?item wdt:P136 ?genre }. #use genreLabel OPTIONAL { ?item wdt:P1071 ?origin }. #use originLabel OPTIONAL { ?item wdt:P495 ?origin_country }. #use origin_countryLabel OPTIONAL { ?item wdt:P6216 ?copyright }. #use copyrightLabel OPTIONAL { ?item wdt:P2048 ?height }. #height of the painting OPTIONAL { ?item wdt:P2049 ?width }. #width of the painting OPTIONAL { ?item wdt:P180 ?depicts }. #depicts property for a painting SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } }

But with this I get a timeout. I tried making a few changes, where I made a few things like Image and Creator without the optional tag, but in those cases I am not able to get the paintings which do not have these attributes. Is there a way I can do this with SPARQL?

Thanks.

No, I don't think there is anyway to do that with SPARQL under the 1 minut timeout limit with 450k paintings. I would suggest downloading a full or partial database dump or extract the items using the API. --Dipsacus fullonum (talk) 13:21, 21 September 2020 (UTC)

Last wovel for Italians

Hello, I'd like to query the last letter for

and their counts. (curious about how % Italians name have FirstnameO FamilynameI ) Bouzinac 💬●✒️●💛 18:58, 20 September 2020 (UTC)

@Bouzinac: Here is a query for the last letters of the first given name for female Italians: 90 % end with "a". It should trivial to modify the query to query for male given names (70 % end with "o") or family names (51 % end with "i"). 
SELECT ?letter ?count (?count / ?total * 100 AS ?pct)
WITH
{
  SELECT ?letter (COUNT(*) AS ?count)
  WHERE
  {
    ?item wdt:P31 wd:Q5 . # Humans
    ?item wdt:P27 wd:Q38 . # Italians
   # ?item wdt:P21 wd:Q6581097 . # male
    ?item wdt:P21 wd:Q6581072 . # female
    ?item p:P735 ?name_statement . # Given name
    ?name_statement ps:P735 / wdt:P1705 ?name . # Native label for name
    OPTIONAL { ?name_statement pq:P1545 ?ordinal . }
    FILTER (! BOUND(?ordinal) || ?ordinal = "1") # Use the only name (no ordinal value) or name #1
    BIND (SUBSTR(STR(?name), STRLEN(?name)) AS ?letter)
  }
  GROUP BY ?letter
} AS %letters
WITH
{
  SELECT (SUM(?count) AS ?total)
  WHERE
  {
    INCLUDE %letters
  }
} AS %total
WHERE
{
  INCLUDE %letters
  INCLUDE %total
}
ORDER BY DESC(?count)
Try it!
--Dipsacus fullonum (talk) 22:28, 20 September 2020 (UTC)
Hey Dipsacus fullonum (talk • contribs • logs), thanks for this nice try! Good point in separating male/female. 2 remarks:
  • I wished to show the combinations of last letter of both given and birth name. Example Luciano Pavarotti (Q37615) ==> o and i.
  • I see weird results from this detailed subquery . Example, just add this filter filter(?letter='թ') and you will have a combination of armenian and latin letters. Don't you think you should stick to the italian label of the item ?
  • Just to keep you posted, i've changed that filter like {VALUES ?countries { wd:Q38 wd:Q238 }. #italie + Smarin ?item wdt:P27 ?countries . # Italians

} Bouzinac 💬●✒️●💛 21:07, 21 September 2020 (UTC)

@Bouzinac: There are names ending in 'թ' because someone added the statement:
David (Q18057751) native label (P1705) "Դավիթ"@hy
I think that is a case of bad indata rather than a problem in the query. Here is a version that list the combinations of given and family name endings. I added citizens of San Marino as suggested: 
SELECT ?letter1 ?letter2 ?count (?count / ?total * 100 AS ?pct)
WITH
{
  SELECT ?letter1 ?letter2 (COUNT(*) AS ?count)
  WHERE
  {
    ?item wdt:P31 wd:Q5 . # Humans
    VALUES ?countries { wd:Q38 wd:Q238 }. # Italy + San Marino
    ?item wdt:P27 ?countries . # Italians
    ?item wdt:P21 wd:Q6581097 . # male
   # ?item wdt:P21 wd:Q6581072 . # female

    ?item p:P735 ?name1_statement . # Given name
    ?name1_statement ps:P735 / wdt:P1705 ?name1 . # Native label for name
    OPTIONAL { ?name1_statement pq:P1545 ?ordinal1 . }
    FILTER (! BOUND(?ordinal1) || ?ordinal1 = "1") # Use the only name (no ordinal value) or name #1

    ?item p:P734 ?name2_statement . # Family name
    ?name2_statement ps:P734 / wdt:P1705 ?name2 . # Native label for name
    OPTIONAL { ?name2_statement pq:P1545 ?ordinal2 . }
    FILTER (! BOUND(?ordinal2) || ?ordinal2 = "1") # Use the only name (no ordinal value) or name #1

    BIND (SUBSTR(STR(?name1), STRLEN(?name1)) AS ?letter1)
    BIND (SUBSTR(STR(?name2), STRLEN(?name2)) AS ?letter2)
  }
  GROUP BY ?letter1 ?letter2
} AS %letters
WITH
{
  SELECT (SUM(?count) AS ?total)
  WHERE
  {
    INCLUDE %letters
  }
} AS %total
WHERE
{
  INCLUDE %letters
  INCLUDE %total
}
ORDER BY DESC(?count)
Try it!
--Dipsacus fullonum (talk) 01:13, 22 September 2020 (UTC)

SPARQL request leads to JSONDecodeError once the requested timeframe is expanded

Dear Wikidata-team,

First of all, I would like to thank you and the community for your efforts and support. The reason why I am contacting you is that I am facing a problem with a query.

Before going into detail, I will provide a little description of my data need: I research the effect of exposure to financial and human crises on political radicalization among top executives. For this purpose, I created a database including all firm-level information of all joint-stock companies of interwar Germany. Among this information, I see who had been sitting on which board of what company. To enrich the data with additional personal information, I try to merge my data with data provided by Wikidata as many people from my dataset have also entries on Wikidata.

Because board members in 1920 Germany were born all over the world, I basically try to query a dataset including all observations referring to people born between 1830 and 1915. However, my SPARQL query (see below) always returns an error.

After having spent days trying to solve the issue, I opened a thread on stackoverflow (link: https://stackoverflow.com/questions/63968595/sparql-request-using-python-works-but-leads-to-jsondecodeerror-once-the-requeste) According to the comments received there, I tried to reduce the code's complexity by querying monthly data for each month of every year between 1830 and 1915. In the beginning, it worked out nicely, but after a point, more and more errors occurred again.

This is why I am contacting you. Do you have an idea on how to solve my problem? 

SELECT ?person ?personLabel ?dob ?place_of_birth ?place_of_birthLabel ?date_of_death ?place_of_death ?place_of_deathLabel ?political_party ?political_partyLabel ?sex_or_gender ?sex_or_genderLabel ?Wikimedia_import_URL ?occupation ?occupationLabel ?work_location ?work_locationLabel ?educated_at ?educated_atLabel ?imported_from_Wikimedia_project ?imported_from_Wikimedia_projectLabel ?source_website_for_the_property ?stated_in ?stated_inLabel ?religion ?religionLabel ?VIAF_ID ?ISNI ?Deutsche_Biographie_ID ?DBS_ID ?place_of_detention ?place_of_detentionLabel ?country_of_citizenship ?country_of_citizenshipLabel ?member_of_military_unit ?member_of_military_unitLabel ?conflict ?conflictLabel ?military_rank ?military_rankLabel ?military_branch ?military_branchLabel ?participant_in ?participant_inLabel ?award_received ?award_receivedLabel ?described_by_source ?described_by_sourceLabel ?academic_degree ?academic_degreeLabel ?field_of_work ?field_of_workLabel ?noble_title ?noble_titleLabel WHERE {
  ?person wdt:P31 wd:Q5;
    wdt:P569 ?dob.
  FILTER(("1830-01-01"^^xsd:dateTime <= ?dob) && (?dob <= "1915-01-01"^^xsd:dateTime))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de". }
  OPTIONAL { ?person wdt:P19 ?place_of_birth. }
  OPTIONAL { ?person wdt:P570 ?date_of_death. }
  OPTIONAL { ?person wdt:P20 ?place_of_death. }
  OPTIONAL { ?person wdt:P102 ?political_party. }
  OPTIONAL { ?person wdt:P21 ?sex_or_gender. }
  OPTIONAL { ?person wdt:P4656 ?Wikimedia_import_URL. }
  OPTIONAL { ?person wdt:P106 ?occupation. }
  OPTIONAL { ?person wdt:P937 ?work_location. }
  OPTIONAL { ?person wdt:P69 ?educated_at. }
  OPTIONAL { ?person wdt:P143 ?imported_from_Wikimedia_project. }
  OPTIONAL { ?person wdt:P1896 ?source_website_for_the_property. }
  OPTIONAL { ?person wdt:P248 ?stated_in. }
  OPTIONAL { ?person wdt:P140 ?religion. }
  OPTIONAL { ?person wdt:P214 ?VIAF_ID. }
  OPTIONAL { ?person wdt:P213 ?ISNI. }
  OPTIONAL { ?person wdt:P7902 ?Deutsche_Biographie_ID. }
  OPTIONAL { ?person wdt:P4007 ?DBS_ID. }
  OPTIONAL { ?person wdt:P5019 ?occupation. }
  OPTIONAL { ?person wdt:P2632 ?place_of_detention. }
  OPTIONAL { ?person wdt:P27 ?country_of_citizenship. }
  OPTIONAL { ?person wdt:P7779 ?member_of_military_unit. }
  OPTIONAL { ?person wdt:P607 ?conflict. }
  OPTIONAL { ?person wdt:P410 ?military_rank. }
  OPTIONAL { ?person wdt:P241 ?military_branch. }
  OPTIONAL { ?person wdt:P1344 ?participant_in. }
  OPTIONAL { ?person wdt:P166 ?award_received. }
  OPTIONAL { ?person wdt:P1343 ?described_by_source. }
  OPTIONAL { ?person wdt:P512 ?academic_degree. }
  OPTIONAL { ?person wdt:P101 ?field_of_work. }
  OPTIONAL { ?person wdt:P97 ?noble_title. }
}
Try it!

Best regards and thanks in advance :) 145.254.187.169 13:51, 22 September 2020 (UTC)

Your query times out due to the hard 1 minute timeout limit of WDQS, which means that you do not receive a valid JSON object. Add " LIMIT 10" (or so) to the end, and it will work—but you won't see all items of course.
Generally, these sort of queries are very difficult to execute, as they involve a huge amount of items and the service is unable to process all of it within the timeout limit. You somehow need to optimize it. —MisterSynergy (talk) 14:33, 22 September 2020 (UTC)

IMDB people with no English label

SELECT ?item  ?imdb WHERE {

?item wdt:P345 ?imdb. 
 
 FILTER(NOT EXISTS {
   ?item rdfs:label ?lang_label.
   FILTER(LANG(?lang_label) = "en")
 })
  
  
   ?item wdt:P31 wd:Q5.
 
}
Try it!

Should be a simple thing to get, no? But it times out if I set the limit over 1000. Any ideas? Gamaliel (talk) 23:38, 22 September 2020 (UTC)

@Gamaliel: This sort of thing; use an OFFSET for the next tranche. 
SELECT ?item ?imdb WITH { SELECT ?item ?imdb WHERE {
  ?item wdt:P345 ?imdb. 
  ?item wdt:P31 wd:Q5.
 
} limit 200000 } as %i
{
  include %i
  filter not exists { ?item rdfs:label ?lang_label. FILTER(LANG(?lang_label) = "en")}
}
Try it!
--Tagishsimon (talk) 00:35, 23 September 2020 (UTC)
@Tagishsimon: Good idea. I was hoping I wouldn't have to mash a bunch of results together, but 200K is plenty to work with at one time. Thanks!
iirc there are 'only' 360k IMDb people, so a couple of hits should do it. --Tagishsimon (talk) 03:15, 23 September 2020 (UTC)
@Gamaliel, Tagishsimon: You can search directly for the items with this search: https://www.wikidata.org/w/index.php?search=haswbstatement%3AP345+-haslabel%3Aen&&ns0=1 giving 71,600 results at the moment. The search can also be done in SPARQL using the MWAPI interface: 
SELECT ?item ?imdb
WHERE
{
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:api "Search" .
    bd:serviceParam wikibase:endpoint "www.wikidata.org" .
    bd:serviceParam mwapi:srnamespace "0" .
    bd:serviceParam mwapi:srsearch "haswbstatement:P345 -haslabel:en" .
    ?item wikibase:apiOutputItem mwapi:title .
  }
   ?item wdt:P345 ?imdb. 
}
Try it!
but MWAPI will limit the number of results to max 10,000. --Dipsacus fullonum (talk) 06:38, 23 September 2020 (UTC)
To search for people only, use "haswbstatement:P345 haswbstatement:P31=Q5 -haslabel:en" --- Jura 08:59, 23 September 2020 (UTC)
Thanks, Jura. I overlooked that the original query was only for humans. With addition of "haswbstatement:P31=Q5" all results can be found in one search using MWAPI. --Dipsacus fullonum (talk) 09:51, 23 September 2020 (UTC)

querying for information about a given item

Hi! Is there a way to write queries that would

a) tell me what information/statements are available for a specific wikidata entry (for example, for item mass (Q11423) it would tell us that the information that's available with this item is instance of, subclass of, has quality, equivalent class, and more;

b) get the actual information for those statements, e.g., for that same entry, I would want a query that would tell us what mass is an instance of (answer: scalar magnitude), what the recommended unit of measure is (answer: kilogram), what the equivalent class is (answer: https://schema.org/Mass) and so on.

I am basically interested in a template where I would be able to substitute instance of, recommended unit of measure, equivalent class or other statements and it would give me the value for that statement.

Thank you!

Do you mean like this? 
SELECT ?property ?propertyLabel ?value ?valueLabel
{
  VALUES ?item { wd:Q11423 }
  ?item ?claim ?statement .
  ?statement a wikibase:BestRank . # only use statements of best rank
  ?property wikibase:claim ?claim .
  ?property wikibase:statementProperty ?ps .
  ?statement ?ps ?value .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!
--Dipsacus fullonum (talk) 05:42, 24 September 2020 (UTC)

@Dipsacus fullonum: yes! Thank you so much!

Male artists active in the 20th century

This is my first attempt at Wikidata query. I am trying to get a very rough tally of the number of en.wp articles on male visual artists active in the 20th-century.

I reckoned I could get an approximation by looking for an intersection of

  • births between 1860 and 1970
  • still living, or died after the year 1900
  • sex = male
  • occupation = visual artist + all sub-classes
  • has en.wp page

I got as far as this 

SELECT ?item
WHERE {
?item wdt:P21 wd:Q6581097.
?item wdt:P106 wd:Q3391743.
}

And then I got stuck. Please can some kind person help me complete this query? --BrownHairedGirl (talk) 16:03, 24 September 2020 (UTC)

@BrownHairedGirl: Approx. 43000 according to this query: 
SELECT DISTINCT ?item
WHERE {
  ?item wdt:P31 wd:Q5 . # Human
  ?item wdt:P21 wd:Q6581097 . # Male
  ?item wdt:P106 / wdt:P279 *  wd:Q3391743 . # Visual artist
  ?item wdt:P569 ?dob . hint:Prior hint:rangeSafe true . # Date of birth 
  FILTER("1860-00-00"^^xsd:dateTime <= ?dob && ?dob < "1979-00-00"^^xsd:dateTime)
  OPTIONAL { ?item wdt:P570 ?dod . hint:Prior hint:rangeSafe true . } # Date of death
  FILTER (! BOUND(?dod) || "1900-00-00"^^xsd:dateTime <= ?dod)
  [] schema:about ?item ; schema:isPartOf <https://en.wikipedia.org/> . # Has en.wp page
}
Try it!
--Dipsacus fullonum (talk) 17:39, 24 September 2020 (UTC)
Thank you, @Dipsacus fullonum. That's great. --BrownHairedGirl (talk) 08:58, 25 September 2020 (UTC)

Cebuano filter for Wikishootme

Can anyone help me building a SPARQL filter for Wikishootme to sort out all items with coordinates of cebuano origin? I'm frequently using Wikishootme to find Wikidata items without a picture around my area. Then I take a photo and add it to Commons. But the cebuano items are often duplicates or with errors and I dont want to waste my time with them. Wikishootme offers a SPARQL filter (and a help page) but I didn't find out how to use it. Here is a example query, the red "Foce del Savio" item should be filtered out. --Lothur (talk) 16:55, 24 September 2020 (UTC)

@Lothur: What do you mean by items with coordinates of cebuano origin? Please give examples of such items. --Dipsacus fullonum (talk) 17:47, 24 September 2020 (UTC)
@Lothur: From how I read the help page, this SPARQL should do it: 
  ?q p:P625 ?stat.
  ?stat a wikibase:BestRank.
  ?stat ps:P625 ?location.
  FILTER NOT EXISTS { ?stat prov:wasDerivedFrom [pr:P143 wd:Q837615] }
giving this URL: https://wikishootme.toolforge.org/#lat=44.31807359109213&lng=12.345714569091797&zoom=14&sparql_filter=%3Fq%20p%3AP625%20%3Fstat.%20%20%20%3Fstat%20a%20wikibase%3ABestRank.%20%20%20%3Fstat%20ps%3AP625%20%3Flocation.%20%20%20FILTER%20NOT%20EXISTS%20%7B%20%3Fstat%20prov%3AwasDerivedFrom%20%5Bpr%3AP143%20wd%3AQ837615%5D%20%7D
However, it seems to remove all Wikidata locations from the map. --Dipsacus fullonum (talk) 18:41, 24 September 2020 (UTC)
Thanks for helping! Yes this seems to delete all items from the map. But this is the right direction: I want to exclude items, that have coordinate location with reference on cebuano wikipedia. I linked this problem in the repo of Wikishootme, probably they know whats wrong with this solution.--Lothur (talk) 20:08, 24 September 2020 (UTC)

Getting all statements pointing to a specific item

I want to query all Statments that have a specific entity as their value. Just like the From related items panel from the wikidata resonator:

Related to Douglas Adams (Q42)
Property Referrer
founded by (P112) h2g2 (Q285194)
founded by (P112) The Digital Village (Q3520623)
named after (P138) Tetramorium adamsi (Q14473301)
named after (P138) 25924 Douglasadams (Q2473108)
template has topic (P1423) Template:Douglas Adams (Q22898962)
and so on…

how do I do that? --Shisma (talk) 09:16, 20 September 2020 (UTC)

By adding ^ before the property. --- Jura 09:18, 20 September 2020 (UTC)

I don't know the property at this point. --Shisma (talk) 09:20, 20 September 2020 (UTC) 

SELECT ?prop ?propLabel ?item ?itemLabel ?itemDescription
WITH
{
  SELECT *
  WHERE 
  {
    ?item ?wdt wd:Q42 .
    ?prop wikibase:directClaim ?wdt .
  } 
} as %test
WHERE
{
  hint:Query hint:optimizer "None".
  INCLUDE %test
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }          
}

Try it!--- Jura 09:24, 20 September 2020 (UTC)

🤩 Thats precisely what I need. Thank you --Shisma (talk) 09:27, 20 September 2020 (UTC)

@Jura1: as this is eating a lot of performance for highly interconnected items like United States of America (Q30) can I limit it to 10 items per property? --Shisma (talk) 11:09, 26 September 2020 (UTC)

I am looking for a general approach of how to generate a breadcrumb navigation (Q846205) for all classes. let's say I have no idea what Halloween episode (Q79769107) is but I am sure I know something that Halloween episode (Q79769107) is a subclass of. 

SELECT ?item ?linkTo {
  wd:Q79769107 wdt:P279* ?item
  OPTIONAL { ?item wdt:P279 ?linkTo }
}

Try it!

will give my a two dimensional graph which is nice but I need something one dimensional instead. In this case I'd expect to see something like

entity (Q35120) → artificial object (Q16686448) → work (Q386724) → intellectual work (Q15621286) → creative work (Q17537576) → audiovisual work (Q2431196) → television series episode (Q21191270) → Halloween episode (Q79769107)

the only parent of Halloween episode (Q79769107) is television series episode (Q21191270) so in this case the last to crumbs are simple. for the rest of the chain I choose an arbitrary path to get two entity (Q35120). There are at least 5 ways to get from Halloween episode (Q79769107) to entity (Q35120) but I could also walk into the dead end of audiovisual (Q758901).

What I need has to be at least consistent. I could for instance use either the shortest way to the next dead end (audiovisual (Q758901)) or the longest way to the furthest dead end (entity (Q35120)). Whatever has the best performance.

Any idea how to do this? Or do you have better ideas what might be the most helpful?--Shisma (talk) 12:21, 26 September 2020 (UTC)

@Shisma: I suggest adding labels and default graph view to your query above: 
#defaultView:Graph
SELECT ?item ?itemLabel ?linkTo ?linkToLabel {
  wd:Q79769107 wdt:P279* ?item.
  OPTIONAL { ?item wdt:P279 ?linkTo. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!
--Dipsacus fullonum (talk) 15:14, 26 September 2020 (UTC)

Check for duplicates of evolutionary lines

I'd like to know if there are some duplicates of Pokémon evolutionary line (Q15795637): all items which have instance of (P31) + Pokémon evolutionary line (Q15795637) must have distinct has part(s) (P527). I need to know if an item appears in two distinct Pokémon evolutionary line (Q15795637) as value of has part(s) (P527). Thank you very much! --★ → Airon 90 08:29, 23 September 2020 (UTC)

No, duplicates: 
SELECT ?line1 ?line1Label ?line2 ?line2Label ?part_of_line ?part_of_lineLabel
WHERE
{
  ?line1 wdt:P31 wd:Q15795637 .
  ?line2 wdt:P31 wd:Q15795637 .
  FILTER (?line1 != ?line2)
  ?line1 wdt:P527 ?part_of_line .
  ?line2 wdt:P527 ?part_of_line .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!
--Dipsacus fullonum (talk) 10:02, 23 September 2020 (UTC)
@Dipsacus fullonum: No duplicates... by now :P I'm working on evolutionary lines so I may mistakenly create a duplicate of an evolutionary line. Thank you for your query! --★ → Airon 90 12:30, 27 September 2020 (UTC)

Consistency of evolutionary lines

Hi, I need to know if all Pokémon evolutionary lines are consistent. In an item which defines an evolutionary line there is instance of (P31) + evolutionary line of Oddish (Q3331733) and has part(s) (P527) contains all Pokémon of that evolutionary line. In order to be consistent, all items appearing in has part(s) (P527) of the evol line, must have part of (P361) + the item of the evol line. As qualifiers they must have both follows (P155) and followed by (P156): an evol line should start with Pokémon egg (Q18129517) (I don't think it will start with novalue but I'm not 100% sure about that), then goes along with all Pokémon belonging to the evol line and then it ends with novalue. Be careful, some evolutionary lines may have a bifurcation.

I'd like to know which evolutionary lines aren't correctly modelled. *If possible* I'd like to even know why it isn't correctly modelled. --★ → Airon 90 08:39, 23 September 2020 (UTC)

@Airon90: Here is a query to check for missing P361. There are so many that I didn't bother to check for qualifiers too. 
SELECT ?line ?lineLabel  ?part_of_line ?part_of_lineLabel
WHERE
{
  ?line wdt:P31 wd:Q15795637 .
  ?line wdt:P527 ?part_of_line .
  FILTER NOT EXISTS { ?part_of_line wdt:P361 ?line . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!
--Dipsacus fullonum (talk) 10:14, 23 September 2020 (UTC)
Dear Dipsacus fullonum thank you for your work and time spent for me but I need what I wrote as I need to check the (un)correctness of the work of some users :) Take your time, as I am working right now to evolutionary lines --★ → Airon 90 12:32, 27 September 2020 (UTC)

All sources referenced as "stated in" for "cites work" property

Hi all. I need to list all entities (sources) indicated as "stated in" (P248) in the references field of the "cites work" (P2860) property of instances of (subclasses of) "scholarly publication" (Q591041) (*). I tried this and it works, but I had to include a LIMIT clause on line 8 because the query would otherwise timeout. Is there a more efficient way to do it?If not, is there somewhere I can ask to have the query run for me?

(*) Ideally I would like it to be "of instances of any class" and have the class selected as well. I tried this, but it is timing out even with LIMIT 10. I must be doing something wrong. --Diegodlh (talk) 14:55, 25 September 2020 (UTC)

@Diegodlh: I may be wrong but I don't think that you do do anything wrong. There are just too much data to run these queries in under 60 seconds. Consider that there is 174 million statements with P2860 and 69 million references with P248. --Dipsacus fullonum (talk) 16:49, 25 September 2020 (UTC)
@Dipsacus fullonum: With my limited query above, I have found "CrossRef" (Q5188229) and "PubMed Central" (Q229883) as values for "stated in" references in "cites work" properties.
I changed the query slightly to try a few other possible values explicitly:
- Microsoft Academic Graph (Q62056662), https://w.wiki/dek: 0 items
- Semantic Scholar (Q22908627), https://w.wiki/deq: 1 item
- OpenCitations Corpus (Q26382154), https://w.wiki/dex: 0 items
In these three cases the query completed successfully without timing out. So I wondered if I could maybe repeat my original query excluding CrossRef and PubMed Central. I tried this, but it times out :(. Is there another way to say "match any property value except" other than using FILTER?
Alternatively, somewhere I can ask to have my original query run for me? Thanks! --Diegodlh (talk) 20:16, 25 September 2020 (UTC)
@Diegodlh: The queries where you look for a certain source are fast because the SPARQL engine can look for possible values for ?refNode directly using Q62056662, Q22908627 and Q26382154 as indexes. There is no point in asking the engine to look through all 69 million triples with pr:P248 and then exclude 2 values. It will take the same time as nearly all the 69 million triples will have to be searched anyway. I haven't heard of anyone executing long-running queries for other. I would download a database dump and analyze the content to do this. --Dipsacus fullonum (talk) 05:40, 26 September 2020 (UTC)
@Diegodlh: PS. You can get partial database dump with all statements with P2860 using the WDumper tool at https://wdumps.toolforge.org/. Then will you will be able to search for P248 in the references of these statements using any tool for searching in texts like for instance grep. --Dipsacus fullonum (talk) 06:43, 26 September 2020 (UTC)
@Dipsacus fullonum: Thank you! I wasn't aware of the possibility of downloading a database dump! I'm gonna give it a try. Thanks!! --Diegodlh (talk) 22:36, 26 September 2020 (UTC)

cited articles that have been retracted

Is it possible to generate a list of scholarly article (Q13442814) that cite articles that have the property is retracted by (P5824)? Perhaps the query could include publication date (P577) for both the retracted article and the citing article so it can highlight if it was cited AFTER the retraction. This might be a useful as a standing list if it doesn't time out. Thank you. Trilotat (talk) 18:51, 27 September 2020 (UTC)

@Trilotat: 
SELECT ?article ?articleLabel ?article_release_date ?item ?itemLabel ?item_release_date
WHERE
{
  ?item wdt:P5824 [] . # ?item is retracted by something
  OPTIONAL { ?item wdt:P577 ?item_release_date . }
  ?article wdt:P2860 ?item . # ?article cites ?item
  OPTIONAL { ?article wdt:P577 ?article_release_date . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!
--Dipsacus fullonum (talk) 19:29, 27 September 2020 (UTC)

cited articles that have an erratum

Is it possible to generate a list of scholarly article (Q13442814) that cite articles that have the property corrigendum / erratum (P2507). Perhaps the query could include publication date (P577) for both the article with the erratum and the citing article so it can highlight if it was cited AFTER the erratum. This might be a useful as a standing list if it doesn't time out. Thank you. Trilotat (talk) 18:51, 27 September 2020 (UTC)

@Trilotat: It is like above but with 345,708 results, so I had to remove (outcomment) the labels to avoid timeout. If necessary the OPTIONAL clauses for release dates can also be removed/outcommented.
SELECT ?article ?articleLabel ?article_release_date ?item ?itemLabel ?item_release_date
WHERE
{
  ?item wdt:P2507 [] . # ?item is corrected by something
  OPTIONAL { ?item wdt:P577 ?item_release_date . }
  ?article wdt:P2860 ?item . # ?article cites ?item
  OPTIONAL { ?article wdt:P577 ?article_release_date . }
#  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!

--Dipsacus fullonum (talk) 19:39, 27 September 2020 (UTC)

Mountains in Møre og Romsdal

Good afternoon! Based upon the example query I would like to have a query showing all mountains in Møre og Romsdal (Q50627). But as the mountains are in P131 municipality of Norway (Q755707) who is also an located in the administrative territorial entity (P131) I do not find it possible ti use wd:Q8052/P279. do you have a solution.

And question no. 2 If I want to list/plot all mountains in Norway I just want to have the mountains with hight above 500 meter

Re; query Map of mountains in Austria showing their heights; from red for the lowest through green to black for the highest (Source) in https://www.wikidata.org/wiki/Wikidata:Project_chat/Archive/2020/09#Wikidata_weekly_summary_#433 (the short URL doesn't work)


Extract
  1. The heights of mountains in SwitzerlandMøre og Romsdal
  2. defaultView:Map{"hide":["?x_coords","?rgb"]}
SELECT ?x ?xLabel ?description ?x_coords ?rgb WITH {
  1. extract the data
SELECT ?data ?x ?x_coords WHERE {
?x wdt:P31 wd:Q8502 .
?x p:P2044/psn:P2044 [
wikibase:quantityAmount ?data
] .
?x wdt:P131 wd:Q50627 .
?x wdt:P625 ?x_coords .
}
} AS %totals
osv!!!!

Breg Pmt (talk) 19:07, 27 September 2020 (UTC)

@Pmt: This query can show a table or a map in one color. I may try with color graduation later. 
SELECT ?mountain ?mountainLabel ?height ?coords
WHERE
{
#  ?mountain wdt:P31 / wdt:P279 * wd:Q8502 . # Mountain
  ?mountain wdt:P31 wd:Q8502 . # Mountain
  ?mountain wdt:P17 wd:Q20 . # in Norway
  ?mountain wdt:P131 + wd:Q50627 . # in Møre og Romsdal county
  OPTIONAL { ?mountain p:P2044 / psn:P2044 / wikibase:quantityAmount ?height . }
  OPTIONAL { ?mountain wdt:P625 ?coords . }
#  FILTER (?height > 500) # Uncomment this line to show mountains over 500 m only
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,no,nb,nn" . }
}
Try it!
--Dipsacus fullonum (talk) 20:00, 27 September 2020 (UTC)

Thanks a lot! I now have this query [2]

(The short URL is not working) giving me a result, unfortunately plenty of these mountains have hights from the Cebuano "thing" and gives wrong heigths And will it even work for Denmark. Breg Pmt (talk) 21:25, 27 September 2020 (UTC)

Pokélist

I need some lists of Pokémon:

  1. all Pokémon which have one type but the statement doesn't have applies to part (P518) + first type (Q25931659) as qualifier (e.g. Falinks (Q76878577), in opposite to Vulpix (Q2739954))
  2. all Pokémon which have a second type defined but not a first type (e.g. Beldum (Q2639444) is a steel-psychic Pokémon but on Wikidata it is not a steel Pokémon)
  3. all evolutionary lines (instance of (P31) + Pokémon evolutionary line (Q15795637)) which have has part(s) (P527) with elements without any series ordinal (P1545) as qualifier
  4. all Pokémon with an evolutionary lines (Pokémon + part of (P361) + its evol line item) which doesn't have series ordinal (P1545) as qualifier
  5. Pokémon without an evolutionary line (Pokémon + part of (P361) + Pokémon without evolution (Q25707067)) which doesn't have either follows (P155) or followed by (P156) as qualifier
  6. Pokémon without an evolutionary line which have both P155 and P156 but they are not set either both to novalue or at least P155 to Pokémon egg (Q18129517)
  7. Last but not least, I need a list of all Pokémon, ordered by code in National Pokédex (Q20005020), with their type(s):
  Pokémon    Type1    Type2

I need it to be used with Template:Wikidata list

That's all by now :) Thank you very very very much in advance to the users who will help me! --★ → Airon 90 12:45, 27 September 2020 (UTC)

@Airon90: I will start with number 1 which gave 570 results: 
  1. SELECT ?item ?itemLabel ?type ?typeLabel
WHERE
{
  ?item p:P31 ?instance_of_statement .
  ?instance_of_statement a wikibase:BestRank .
  ?instance_of_statement ps:P31 ?type .
  ?type wdt:P279 + wd:Q1266830 . # ?type is subclass of Pokémon type
  FILTER NOT EXISTS { ?instance_of_statement pq:P518 wd:Q25931659 . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
    Try it!
    --Dipsacus fullonum (talk) 16:20, 27 September 2020 (UTC)
Thank you, Dipsacus fullonum, but the query is not correct. It returned Q13099447, which is a dark-fire pkmn, and it is correctly mapped.
See this query. These items define type(s) of a Pokémon and it is used as value for instance of (P31). I need a list of all Pokémon with P31 + PKMN_TYPE and without a qualifier for P518 (to be honest I need to check for values first type (Q25931659) and/or second type (Q25931668)). I hope I was clearer now :) Thank you anyway! --★ → Airon 90 17:19, 27 September 2020 (UTC)
@Airon90: Houndour (Q13099447) does have a statement with instance of (P31) with a value that a Pokémon type without the P518 qualifier first type (Q25931659). You didn't say that second type (Q25931668)) is also an allowed qualifier value. Here is a version that allows both: 
SELECT ?item ?itemLabel ?type ?typeLabel
WHERE
{
  ?item p:P31 ?instance_of_statement .
  ?instance_of_statement a wikibase:BestRank .
  ?instance_of_statement ps:P31 ?type .
  ?type wdt:P279 + wd:Q1266830 . # ?type is subclass of Pokémon type
  FILTER NOT EXISTS {
    VALUES ?allowed_P518_qualifier_value { wd:Q25931659 wd:Q25931668 }
    ?instance_of_statement pq:P518 ?allowed_P518_qualifier_value .
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!
--Dipsacus fullonum (talk) 18:17, 27 September 2020 (UTC)
@Airon90: Here is query 2: 
  2. SELECT ?item ?itemLabel ?type ?typeLabel
WHERE
{
  ?item p:P31 ?instance_of_statement .
  ?instance_of_statement a wikibase:BestRank .
  ?instance_of_statement ps:P31 ?type .
  ?type wdt:P279 + wd:Q1266830 . # ?type is subclass of Pokémon type
  ?instance_of_statement pq:P518 wd:Q25931668 . # Have second type qualifier
  FILTER NOT EXISTS {
    VALUES ?allowed_P518_qualifier_value { wd:Q25931659 wd:Q25931668 }
    ?item p:P31 ?other_instance_of_statement .
    ?other_instance_of_statement a wikibase:BestRank .
    ?other_instance_of_statement ps:P31 ?type2 .
    ?type2 wdt:P279 + wd:Q1266830 . # ?type2 is subclass of Pokémon type
    ?other_instance_of_statement pq:P518 wd:Q25931659 . # The other type have first type qualifier
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
    Try it!
    --Dipsacus fullonum (talk) 18:31, 27 September 2020 (UTC)
@Airon90: Here is query 3: 
  2. SELECT DISTINCT ?item ?itemLabel
WHERE
{
  ?item wdt:P31 wd:Q15795637 . #?item is instance of Pokémon evolutionary line
  ?item p:P527 ?has_part_statement .
  ?has_part_statement a wikibase:BestRank .
  FILTER NOT EXISTS { ?has_part_statement pq:P1545 [] . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
    Try it!
    --Dipsacus fullonum (talk) 18:39, 27 September 2020 (UTC)
@Airon90: Here is query 7. For use with {{Wikidata list}} you may want to remove the label service line and the labels from the SELECT clause as Listeriabot can get labels on its own. 
  2. SELECT DISTINCT ?item ?itemLabel ?type1Label ?type2Label ?Pokédex
WHERE
{
  ?item wdt:P31 / wdt:P279 wd:Q3966183 .
  OPTIONAL {
    ?item p:P31 ?type1_statement .
    ?type1_statement ps:P31 ?type1 .
    ?type1_statement pq:P518 wd:Q25931659 .
  }
  OPTIONAL {
    ?item p:P31 ?type2_statement .
    ?type2_statement ps:P31 ?type2 .
    ?type2_statement pq:P518 wd:Q25931668 .
  }
  OPTIONAL {
    ?item p:P1685 ?Pokédex_statement .
    ?Pokédex_statement ps:P1685 ?Pokédex_value .
    ?Pokédex_statement pq:P972 wd:Q20005020 . # Use catalog National Pokédex
    BIND (IF(wikibase:isSomeValue(?Pokédex_value), "unknown value", ?Pokédex_value) AS ?Pokédex)
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
ORDER BY ?Pokédex
    Try it!
    --Dipsacus fullonum (talk) 11:25, 28 September 2020 (UTC)
Thank you very much, Dipsacus fullonum! Could you please make the query show its type (X) instead of X-type Pokémon? It would be very useful to check errors --★ → Airon 90 12:20, 28 September 2020 (UTC)
@Airon90: Do you mean like this? 
SELECT DISTINCT ?item ?itemLabel ?type1Label ?type2Label ?Pokédex
WHERE
{
  ?item wdt:P31 / wdt:P279 wd:Q3966183 .
  OPTIONAL {
    ?item p:P31 ?type1_statement .
    ?type1_statement pq:P518 wd:Q25931659 .
    ?type1_statement ps:P31 / wdt:P279 ?type1 .
    ?type1 wdt:P279 wd:Q1266830 .
  }
  OPTIONAL {
    ?item p:P31 ?type2_statement .
    ?type2_statement pq:P518 wd:Q25931668 .
    ?type2_statement ps:P31 / wdt:P279 ?type2 .
    ?type2 wdt:P279 wd:Q1266830 .
  }
  OPTIONAL {
    ?item p:P1685 ?Pokédex_statement .
    ?Pokédex_statement ps:P1685 ?Pokédex_value .
    ?Pokédex_statement pq:P972 wd:Q20005020 . # Use catalog National Pokédex
    BIND (IF(wikibase:isSomeValue(?Pokédex_value), "unknown value", ?Pokédex_value) AS ?Pokédex)
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
ORDER BY ?Pokédex
Try it!
--Dipsacus fullonum (talk) 20:03, 28 September 2020 (UTC)

Biodiversity quantification

How can I get the count of all animals and plants of a given country? This number could be used to build a biodiversity index. --AntoineLogean

You can't using Wikidata as there is no data for that here as far as I know. --Dipsacus fullonum (talk) 16:22, 27 September 2020 (UTC)
@Dipsacus fullonum: But for each animal or plant in wikipedia I can find an item on Wikidata. So by taking all instances of species or sub-species I should get all animal entries, right? Then for localisation it could be more tricky. What about Wikispecies ? How is the synchronisation/interoperability between Wikidata and Wikispecies? If I would like to address this biodiversity quantification with wikidata, what is missing and how should be wikidata extended? --AntoineLogean
@AntoineLogean: Yes, there are items for all species of at least higher animal species, but no way to see if they live in a given country. That information generally isn't available at Wikispecies either. --Dipsacus fullonum (talk) 08:32, 28 September 2020 (UTC)

Items having both country (P17) : Republic of Artsakh (Q244165) and Azerbaijan (Q227)

hello, thanks for the listing without statements qualified with statement disputed by (P1310) Bouzinac 💬●✒️●💛 10:13, 28 September 2020 (UTC)

@Bouzinac: 147 items have statements for both countries. Only 3 item use the P1310 qualifier as shown in this query: 
SELECT ?item ?itemLabel ?disputed1Label ?disputed2Label
{
  ?statement1 ps:P17 wd:Q244165 .
  ?statement2 ps:P17 wd:Q227 .
  ?item p:P17 ?statement1 .
  ?item p:P17 ?statement2 .
  OPTIONAL { ?statement1 pq:P1310 ?disputed1 . }
  OPTIONAL { ?statement2 pq:P1310 ?disputed2 . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!
--Dipsacus fullonum (talk) 10:39, 28 September 2020 (UTC)

List of conflicts in Canada

I'm trying to get a list/map of conflicts in Canada that somehow matches the list in List of conflicts in Canada

I've tried 

#defaultView:Map
SELECT ?conflictLabel ?conflictDescription ?coord WHERE {
  ?conflict (wdt:P31/(wdt:P279*)) wd:Q180684;
    wdt:P17 wd:Q16;
    wdt:P625 ?coord.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en, fr". }
}
Try it!

but it only shows a subset.

Any suggestions?

@Darcy Quesnel: Most items for conflicts don't have geographic coordinates. If you make getting ?coord optional, you will go from 36 to 671 results in the query: 
SELECT DISTINCT ?conflictLabel ?conflictDescription ?coord WHERE {
  ?conflict (wdt:P31/(wdt:P279*)) wd:Q180684;
    wdt:P17 wd:Q16.
   OPTIONAL { ?conflict wdt:P625 ?coord.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en, fr". }
}
Try it!
--Dipsacus fullonum (talk) 15:19, 28 September 2020 (UTC)
@Dipsacus fullonum: Thanks! -- Darcy Quesnel (talk) 19:40, 28 September 2020 (UTC)

One time list needed

I just created an article on the English Wikipedia for Carex elata (Q161128), a species of sedge. I was shocked to find that it already had articles in 24 other Wikipedias. Can somebody whip me up a list of similar cases and drop it on my talk page? Or a query, if it can be done. I don't have the ability. The problem is that there is no property "species of plant", just taxon rank (P105) with species (Q7432) as the value, and when I do run a query on species missing an en.wiki article from a large genus, it often times out, so running on all species won't be possible for me. Anyway, if it is possible, could I get a list of all angiosperms (Q25314) species with no English Wikipedia article but that do have, say, 20 or more existing articles on other Wikpedias? Thanks, Abductive (talk) 20:42, 27 September 2020 (UTC)

@Abductive: This query will find taxons for angiosperm species with 22 or more sitelinks but no sitelink to English Wikipedia. 22 sitelinks will typically be 1 to Commons, 1 to Specieswiki, and 20 to Wikipedias but other combinations are possible. 
SELECT ?taxon ?taxonLabel ?sitelinks
WITH
{
  SELECT ?taxon ?sitelinks
  WHERE
  {
    ?taxon wdt:P105 wd:Q7432 . # taxon rank is species
    ?taxon wikibase:sitelinks ?sitelinks . hint:Prior hint:rangeSafe true .
    FILTER (?sitelinks >= 22)
  }
} AS %subquery1
WITH
{
  SELECT ?taxon ?sitelinks
  WHERE
  {
    INCLUDE %subquery1
    FILTER NOT EXISTS { [] schema:about ?taxon ; schema:isPartOf <https://en.wikipedia.org/> . }
  }
} AS %subquery2    
WITH
{
  SELECT ?taxon ?sitelinks
  WHERE
  {
    INCLUDE %subquery2
    ?taxon wdt:P171 + wd:Q25314 . # is angiosperm
  }
} AS %subquery3
WHERE
{
  INCLUDE %subquery3
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!
--Dipsacus fullonum (talk) 23:06, 27 September 2020 (UTC)
It timed out for me. Abductive (talk) 07:56, 28 September 2020 (UTC)
@Abductive: It didn't timeout for me when I made the query, and it didn't timeout just now, so you can try again. To make it run faster, you can also remove label service line to omit the labels, and remove the line ?taxon wdt:P171 + wd:Q25314 . # is angiosperm – I think it reduced the number of results from 9 species of all types to 6 angiosperms. --Dipsacus fullonum (talk) 08:24, 28 September 2020 (UTC)
It works about 50% of the time. Thanks, I got what I needed! Now, is there a library these queries get saved to? Abductive (talk) 01:55, 29 September 2020 (UTC)
@Abductive: I don't think so but this page is archived and you can search the archives from the page Wikidata:Request a query/Archive. The link is also at top of the page. --Dipsacus fullonum (talk) 06:07, 29 September 2020 (UTC)

Commons Query Service-Missing Labels and descriptions

Seeking for query in Wikimedia Commons Query Service for the following requirement. Get the IDs / images of all US presidents which does not have labels in Arabic language.--Akbarali (talk) 16:17, 28 September 2020 (UTC)

@Akbarali: This query lists IDs for files which depict a US president and which don't have a label in Arabic language. 
SELECT ?image 
WITH
{
  SELECT ?item
  WHERE
  {
    SERVICE <https://query.wikidata.org/sparql>
    {
      ?item wdt:P39 wd:Q11696 .  # position held: President of the United States
    }
  } 
} AS %get_items
WHERE
{
  INCLUDE %get_items
  ?image wdt:P180 ?item .
  FILTER NOT EXISTS { ?image rdfs:label ?label. FILTER (LANG(?label) = "ar") }
}
Try it!
--Dipsacus fullonum (talk) 16:40, 29 September 2020 (UTC)

All settlement names and wikipedia articles in Hebrew and Yiddish

  1. I am trying to query and download all settlements that have alternatives names in Hebrew articles in Hebrew Wikipedia. This query times out. Could you please assist?
SELECT ?human_settlement ?human_settlementLabel ?human_settlementDescription ?human_settlementAltLabel ?article ?location WHERE {

    ?human_settlement wdt:P31 wd:Q486972 ; # human settlement
            wdt:P625 ?location .# And location

    ?article schema:about ?human_settlement .
    ?article schema:isPartOf <https://he.wikipedia.org/>.


    SERVICE wikibase:label {
       bd:serviceParam wikibase:language "he"
Try it!

}}} --סיני.ר (talk) 06:31, 28 September 2020 (UTC)

@סיני.ר: I don't understand the problem. The query above gives 81 results. It doesn't timeout and it has no limit clause. --Dipsacus fullonum (talk) 10:51, 28 September 2020 (UTC)
@Dipsacus fullonum: Sorry - you are right. I edited the question now - the problem is simply the timeout.
@סיני.ר: The edited query gave 1747 results in 13685 ms. I see no timeout. --Dipsacus fullonum (talk) 19:48, 28 September 2020 (UTC)
@Dipsacus fullonum: thanks! it finally worked for me too. I guess It depends on the time of the query. Can I follow up with a request? Now I would like all ?human_settlementAltLabel in Hebrew for those settlements that have no wikidata pages...-- 16:43, 29 September 2020 (UTC)
@סיני.ר: I don't understand. If they have no Wikidata page, they also have no AltLabel (aliases) as they are on the Wikidata page. --Dipsacus fullonum (talk) 06:47, 30 September 2020 (UTC)

Shortest and longest wars

Hello, I'd like to query the top 10 quickest wars (of any type war (Q198)) : those that did'nt last very long, be it by end time (P582) - start time (P580) or by duration (P2047). Should be having Anglo-Zanzibar War (Q170850)... And the top 10 longest ? Bouzinac 💬●✒️●💛 15:01, 29 September 2020 (UTC)

@Bouzinac: There is only one item for war where P2047 is used (Anglo-Zanzibar War (Q170850)). This query gives the quickest wars using both methods: 
SELECT ?item ?itemLabel ?start ?end ?days1 ?days2 ?days
{
  ?item wdt:P31 / wdt:P279 * wd:Q198 .
  OPTIONAL
  {
    ?item p:P2047 / psn:P2047 / wikibase:quantityAmount ?duration_seconds .
    BIND (?duration_seconds / 86400 AS ?days1)
  }
  OPTIONAL
  {
    ?item p:P580 / psv:P580 [wikibase:timeValue ?start; wikibase:timePrecision 11 ] .
    ?item p:P582 / psv:P582 [wikibase:timeValue ?end; wikibase:timePrecision 11 ] .
    BIND (xsd:integer(?end - ?start) AS ?days2)
  }
  BIND (COALESCE(?days1, ?days2) AS ?days)
  FILTER BOUND(?days)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
ORDER BY ?days ?start
LIMIT 15
Try it!
--Dipsacus fullonum (talk) 21:57, 29 September 2020 (UTC)
@Bouzinac: To get the longest wars, replace ORDER BY ?days ?start with ORDER BY DESC(?days) ?start. --Dipsacus fullonum (talk) 22:09, 29 September 2020 (UTC)
Oh, and remove the requirement for time precision day, i.e. remove ; wikibase:timePrecision 11 in two places. --Dipsacus fullonum (talk) 22:15, 29 September 2020 (UTC)
Oh yes, thank you Dipsacus fullonum. My goal of data quality has I'd have another query for you : the items having both start time (P580) end time (P582) point in time (P585), showing those that having point in time (P585) very much not between start time (P580) and end time (P582) ? Thanks Bouzinac 💬●✒️●💛 05:42, 30 September 2020 (UTC)
Retrieved from "https://www.wikidata.org/w/index.php?title=Wikidata:Request_a_query/Archive/2020/09&oldid=1291374412"