Wikidata:Request a query/Archive/2017/03

This page is an archive. Please do not modify it. Use the current page, even to continue an old discussion.

Position holders that have a position multiple times

Hoi, Can someone help me for Listeria with Presidents of Haiti ? Thanks, GerardM (talk) 14:41, 26 February 2017 (UTC)

I asked Magnus.. His tool Listeria has indeed a problem. Given that the official list software is being worked on, there is little point in revamping the software.. I hope the wait will not be long. Thanks, GerardM (talk) 20:18, 1 March 2017 (UTC)

Sorted or Unique Output Results

hello everyone. Would someone please help by a query which exports unique (or sorted) results of Wikipedia content (articles)? In the query [1] I get 1000 Articles of arwiki when I run it. Now, how can one make it outputs different 1000 articles of arwiki in a new run? More specific, how to get the next 1000 Articles (may be the articles are stored (sorted) in a binary way or so?) Best Thanks!--Sky xe (talk) 21:39, 27 February 2017 (UTC)

Add an OFFSET 1000 to get 1001-2000; and OFFSET 2000 to get 2001-3000, etc, as below. --Tagishsimon (talk) 22:24, 27 February 2017 (UTC)

SELECT ?item  ?article ?itemLabel WHERE {
  ?item ^schema:about ?article .
  ?article schema:isPartOf <https://ar.wikipedia.org/> .
 SERVICE wikibase:label { bd:serviceParam wikibase:language "ar" }
}
limit 1000 offset 1000

Try it!

Great, thank you a lot @Tagishsimon:!

It worked perfect for not large offsets. But I get time out limit if the offset value is larger than 200k (test). Is there a solution for that? --Sky xe (talk) 23:03, 27 February 2017 (UTC)

I made an optimization for the query but it behaves strangely. Sometimes it's as quick as those other ones, sometimes I get no response. Not sure whether everybody has this problem.

SELECT ?item ?article ?itemLabel WITH {
  SELECT ?item ?article WHERE {
    ?item ^schema:about ?article .
    ?article schema:isPartOf <https://ar.wikipedia.org/> .
  } LIMIT 1000 OFFSET 200000
} AS %subquery WHERE {
  INCLUDE %subquery .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "ar" } .
}

Try it!

Matěj Suchánek (talk) 14:55, 1 March 2017 (UTC)

Now it works and I don't get the problem of "No Response" for all offset or output ranges I tested. Awesome! Thanks Matěj Suchánek.--Sky xe (talk) 23:46, 1 March 2017 (UTC)

village (Q13100073)

Im asking for a query, but excluding items with descriptions. I want to add descriptions to the ones without. Is it possible? For example, I want it to retrieve Q2767051, but not Q4008. MechQuester (talk) 21:10, 28 February 2017 (UTC)

SELECT ?item {
  ?item wdt:P31 wd:Q13100073 .
  MINUS {
    ?item schema:description ?desc .
    FILTER ( LANG ( ?desc ) = 'en' ) .
  } .
} LIMIT 1000

Try it!

When executing COUNT, I get 480,000+ items. Matěj Suchánek (talk) 13:22, 2 March 2017 (UTC)

Bengali alias set in latin alphabet

I need a query for finding item that have bnwiki sitelink and have alias but not in bengali language (Like this one) --Aftabuzzaman (talk) 21:50, 28 February 2017 (UTC)

SELECT ?item ?alias {
  ?item ^schema:about/schema:isPartOf/^wdt:P856 wd:Q427715 .
  ?item skos:altLabel ?alias .
  FILTER( LANG( ?alias ) = 'bn' && REGEX( LCASE( STR( ?alias ) ), '[a-z]' ) ) .
} LIMIT 100

Try it!

Feel free to come back if you think it could work better. Matěj Suchánek (talk) 13:27, 2 March 2017 (UTC)

Awesome, thanks! --Aftabuzzaman (talk) 16:31, 3 March 2017 (UTC)

Query of NH in India

Hi, I am looking for a query to use in NH map, I am try to get en wiki links in the query. https://en.wikipedia.org/wiki/User:Naveenpf/sandbox#NHs_in_India . Can someone help ? -- Naveenpf (talk) 10:28, 2 March 2017 (UTC)

Can you specify, what do you mean by abbreviation NH? --Jklamo (talk) 17:51, 4 March 2017 (UTC)

The query in you page work

SELECT ?id ?idLabel ?wikilink (concat('[', str(?wikilink) , '|' , str(?idLabel), ']') as ?title_new)
WHERE
{
     ?id wdt:P31 wd:Q34442 . # is a road
     ?id wdt:P17 wd:Q668 . # in India
     ?id wdt:P16 wd:Q1967342 . # in India
     ?wikilink schema:about ?id; schema:isPartOf <https://en.wikipedia.org/> .
      SERVICE wikibase:label { bd:serviceParam wikibase:language 'en','ml','hi' }
}

Try it!

Probably the problem is another. --ValterVB (talk) 18:14, 4 March 2017 (UTC)

Q13100073 in 1 province

Hi, , I was wondering if a query could be done for Q13100073, but in 1 province like Q19206. Possible? MechQuester (talk) 03:46, 3 March 2017 (UTC)

@MechQuester: I'm not sure whether I understand. You may either want to get list of villages which are in a single province (regardless of which it is), or which are in a specific single province, or which is in a specific province (I realize now it's unlikely that a village is in multiple provinces). Matěj Suchánek (talk) 09:17, 4 March 2017 (UTC)

meant to say in that specific province. Sorry. MechQuester (talk) 17:35, 4 March 2017 (UTC)

SELECT ?item WHERE {
  ?item wdt:P31 wd:Q13100073;
        wdt:P131+ wd:Q19206
}

Try it!

--Pasleim (talk) 22:28, 4 March 2017 (UTC)

Query problem

Hey. I created a query but for some reason every item shows up 30 times, any way to fix it?

SELECT ?item WHERE { 
  {?item wdt:P17 wd:Q142 }
  UNION  {?item wdt:P27 wd:Q142 }
  ?sitelink schema:about ?item . 
FILTER NOT EXISTS { ?wfr schema:about ?item . ?wfr schema:inLanguage "he" } .
OPTIONAL {?item rdfs:label ?label1 filter(lang(?label1) = "he")}.
FILTER BOUND (?label1).
}LIMIT 6000

Try it!

--Mikey641 (talk) 00:05, 5 March 2017 (UTC)

Try using 'select distinct' instead of 'select'. I had to reduce the limit to avoid timeout, but this query doesn't return any duplicates.

SELECT DISTINCT ?item WHERE { 
  {?item wdt:P17 wd:Q142 }
  UNION  {?item wdt:P27 wd:Q142 }
  ?sitelink schema:about ?item . 
FILTER NOT EXISTS { ?wfr schema:about ?item . ?wfr schema:inLanguage "he" } .
OPTIONAL {?item rdfs:label ?label1 filter(lang(?label1) = "he")}.
FILTER BOUND (?label1).
}LIMIT 800

Try it!

Shinnin (talk) 01:18, 5 March 2017 (UTC)

Finding alternative Label for missing Label in Ta

I need to find Missing Label but available in another item, we can use English as base language but need to find the label in Tamil. for example I get the results with empty labels in Tamil. but I can get திண்டிவனம் (Q2465629) = Tindivanam. I expect output as Q16556631, Tindivanam, திண்டிவனம்.

SELECT ?item ?itemLabel ?desc  WHERE {  
#  BIND (wd:Q668 as ?country)
#  BIND (wd:Q16556631 as ?Tindivanam)
  BIND (wd:Q3429959 as ?loksabha)
  {  	
  ?item wdt:P31 ?loksabha
   } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
   OPTIONAL {
    
     ?item rdfs:label ?desc filter (lang(?desc) = "ta").
   }  
  filter (!(Bound(?desc))) .
  
}
limit 10

Try it!

.

Thanks--Mdmahir (talk) 06:56, 5 March 2017 (UTC)

demonym (P1549)

Hey.
I need a list of demonym (P1549) of all the sovereign state (Q3624078) in singular sorted into male and female in french.
For example Norway (Q20):

country	countryLabel	male	female
Q20	Norvège	Norvégien	Norvégienne

Thanks. --Mikey641 (talk) 17:57, 1 March 2017 (UTC)

SELECT ?item ?itemLabel ?male ?female {
  ?item wdt:P31 wd:Q3624078 .
  VALUES ?male_val { wd:Q6581097 wd:Q499327 } .
  VALUES ?female_val { wd:Q6581072 wd:Q1775415 } .
  OPTIONAL {
    ?item p:P1549 ?male_statement .
    ?male_statement (pq:P366|pq:P518) ?male_val; ps:P1549 ?male .
    FILTER( LANG( ?male ) = 'fr' ) .
    MINUS { ?male_statement (pq:P366|pq:P518) wd:Q146786 } .
  } .
  OPTIONAL {
    ?item p:P1549 ?female_statement .
    ?female_statement (pq:P366|pq:P518) ?female_val; ps:P1549 ?female .
    FILTER( LANG( ?female ) = 'fr' ) .
    MINUS { ?female_statement (pq:P366|pq:P518) wd:Q146786 } .
  } .
  FILTER( BOUND( ?male ) && BOUND( ?female ) ) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr" } .
}

Try it!

The query is quite complex because determining the gender is not unified. Matěj Suchánek (talk) 13:45, 2 March 2017 (UTC)

WOW that's amazing! I tried writing a query my self but it wouldn't work because of the gender. Thank you very much.--Mikey641 (talk) 16:38, 3 March 2017 (UTC)

Matěj Suchánek. Hey, is it possible to also add german (divided to male an woman), and danish [da] (in danish it's not divided to male and female so you should just remove the plural). I want that if there is only female or only male in german/french then you should not show the german/french a all but if there is male+female french but no german it should still show the french.--Mikey641 (talk) 22:16, 4 March 2017 (UTC)

SELECT ?item ?itemLabel ?fr_male ?fr_female ?de_male ?de_female ?da {
  ?item wdt:P31 wd:Q3624078 .
  VALUES ?male_val { wd:Q6581097 wd:Q499327 } .
  VALUES ?female_val { wd:Q6581072 wd:Q1775415 } .

  ?item p:P1549 ?fr_male_statement; p:P1549 ?fr_female_statement .
  ?fr_male_statement (pq:P366|pq:P518) ?male_val; ps:P1549 ?fr_male .
  ?fr_female_statement (pq:P366|pq:P518) ?female_val; ps:P1549 ?fr_female .
  FILTER( LANG( ?fr_male ) = 'fr' && LANG( ?fr_female ) = 'fr' ) .
  MINUS { ?fr_male_statement (pq:P366|pq:P518) wd:Q146786 } .
  MINUS { ?fr_female_statement (pq:P366|pq:P518) wd:Q146786 } .
  OPTIONAL {
    ?item p:P1549 ?de_male_statement; p:P1549 ?de_female_statement .
    ?de_male_statement (pq:P366|pq:P518) ?male_val; ps:P1549 ?de_male .
    ?de_female_statement (pq:P366|pq:P518) ?female_val; ps:P1549 ?de_female .
    FILTER( LANG( ?de_male ) = 'de' && LANG( ?de_female ) = 'de' ) .
    MINUS { ?de_male_statement (pq:P366|pq:P518) wd:Q146786 } .
    MINUS { ?de_female_statement (pq:P366|pq:P518) wd:Q146786 } .
  } .
  OPTIONAL {
    ?item p:P1549 ?da_statement .
    ?da_statement ps:P1549 ?da .
    FILTER( LANG( ?da ) = 'da' ) .
    MINUS { ?da_statement (pq:P366|pq:P518) wd:Q146786 } .
  } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr" } .
}

Try it!

It's not perfect but it works. Matěj Suchánek (talk) 10:02, 5 March 2017 (UTC)

A count of the Wikipedias that have an article

For "catalog" "Black Lunch Table" I have some 900 people who may or may not have an article on a Wikipedia. What I would like is a query that counts the number of articles in any Wikipedia and returns the total number per Wikipedia. Thanks, GerardM (talk) 08:04, 5 March 2017 (UTC)

SELECT ?wikiLabel (count(*) as ?count) WHERE
{
  ?item wdt:P972 wd:Q28781198 .
  ?item ^schema:about/schema:isPartOf/^wdt:P856 ?wiki .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
} GROUP BY ?wikiLabel ORDER BY DESC(?count)

Try it!

--Pasleim (talk) 10:45, 5 March 2017 (UTC)

Finding scientists who were born or died in Berlin

I tried to alter an existing query with the following (only births in this case) but it produced no results. What am I doing wrong? Shyamal (talk) 18:24, 4 March 2017 (UTC)

SELECT ?item ?dob WHERE {
  { ?item wdt:P19 wd:Q64. }
  UNION
  {
    ?item wdt:P19 ?pob.
    ?pob wdt:P131* wd:Q64.
  }
  ?item wdt:P279 wd:Q901.
  OPTIONAL { ?item wdt:P569 ?dob. }
}
ORDER BY ?dob
LIMIT 4000

SELECT ?item ?dob WHERE {
  { ?item wdt:P19 wd:Q64. }
  UNION
  {
    ?item wdt:P19 ?pob.
    ?pob wdt:P131* wd:Q64.
  }
  ?item wdt:P106 wd:Q901.
  OPTIONAL { ?item wdt:P569 ?dob. }
}
ORDER BY ?dob
LIMIT 4000

Try it!

@Shyamal: replace p279 with p106 --Mrutyunjaya Kar (talk) 19:05, 4 March 2017 (UTC)

THanks a lot. I tried your other query as well but it does not pick up Q62357 who should be a scientist as well but perhaps we need to use subclass or has "ornithologist" not been defined as a kindof scientist? Shyamal (talk) 06:33, 6 March 2017 (UTC)

SELECT DISTINCT ?item ?itemLabel ?itemDescription ?dob ?dod 
WHERE{
  ?item wdt:P106/wdt:P279*  wd:Q901 .		# scientist/subclass of scientist 
	{ ?item wdt:P19 wd:Q64 }			# place of birth = Berlin (Q64)
		UNION	{ ?item wdt:P20 wd:Q64 }	# place of death = Berlin (Q64)
	OPTIONAL { ?item wdt:P569 ?dob  }
  	OPTIONAL { ?item wdt:P570 ?dod  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
ORDER BY ?dob

Try it!

--Mrutyunjaya Kar (talk) 09:16, 6 March 2017 (UTC)

Super! Thanks a ton. @MKar: Shyamal (talk) 11:27, 6 March 2017 (UTC)

subclass of subclass of

I have the following tree of instances/subclasses:

protected area of Moldova (Q12720953)

• scientific reserve (Q28861097)
• national park (Q28861137)
• nature monument (Q28861145)
  • geological and paleonthological natural monument of Moldova (Q28861155)
  • hydrological natural monument of Moldova (Q28861304)
  • botanical natural monument of Moldova (Q28861305)
    • typical plot of forest vegetation of Moldova (Q28861309)
    • secular tree of Moldova (Q28861310)
• nature reserve (Q28861372)
  • forest nature reserve of Moldova (Q28861464)
  • nature reserve of herbs of Moldova (Q28861520)
  • mixed nature reserve of Moldova (Q28861578)
• landscape reserve (Q28861616)
• resource reserve (Q28861659)
• multi-purpose protected area (Q28861694)
  • typical plot of steppe vegetation of Moldova (Q28861723)
  • typical plot of meadow vegetation of Moldova (Q28861729)
  • windbreak of Moldova (Q28861735)
• biosphere reserve (Q28861739)
• dendrological garden (Q28861752)
• monument of landscape architecture (Q28861757)
• zoological garden (Q28861760)
• wetland of international importance (Q28861769)

How to query all the individual protected areas that have an instance of (P31) any of these? I tried the following query but it looks like it only displays items with instanceof level 1 types (scientific reserve (Q28861097), nature monument (Q28861145), biosphere reserve (Q28861739), etc.) although I need the level 2 and level 3 types too (hydrological natural monument of Moldova (Q28861304), secular tree of Moldova (Q28861310)...).

#defaultView:Map
SELECT DISTINCT ?monumentLabel ?monument ?coor ?image WHERE {
  ?monument (wdt:P31/wdt:P279*) wd:Q12720953.
  ?monument ?range wd:Q217.
  OPTIONAL { ?monument wdt:P625 ?coor }
  OPTIONAL { ?monument wdt:P18 ?image }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "ro". }
}

Try it!

Thank you. --Gikü (talk) 16:13, 8 March 2017 (UTC)

 Resolved Looks like all I had to do is get rid of the ?range thing that was there from a sample query. Works ok without the ?range line. --Gikü (talk) 17:14, 8 March 2017 (UTC)

Tennis books

The following query lists all books that have tennis (Q847) as the main subject (P921) and displays item and itemlabel.

SELECT DISTINCT ?item ?itemLabel WHERE {
    ?item wdt:P31 wd:Q571 ; 
          wdt:P921 wd:Q847 ; 
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
}

Try it!

Can someone show me how to extend the list to also display the authorname, year of publication, publisher and number of pages? Thanks.--Wolbo (talk) 14:59, 7 March 2017 (UTC)

This seems to do it. Someone more clever might be able to make the date a little more friendly. --Tagishsimon (talk) 20:58, 7 March 2017 (UTC)

SELECT DISTINCT ?item ?itemLabel ?authorLabel ?dateLabel ?publisherLabel ?page WHERE {
    ?item wdt:P31 wd:Q571 ; 
          wdt:P921 wd:Q847 ; 
          optional {?item wdt:P50 ?author}
          optional {?item wdt:P577 ?date}
          optional {?item wdt:P123 ?publisher}
          optional {?item wdt:P1104 ?page}
  
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
}

Try it!

Thx!. So the trick to listing fields in the result without actually filtering on them is to use the 'optional' condition? The 'date' field needs formatting to display only the year, I'm sure examples exist showing how to do that.--Wolbo (talk) 10:02, 8 March 2017 (UTC)

The query above is malfunctioning a little as it makes no sense to look up a label for a date (and a number of pages):

SELECT DISTINCT ?item ?itemLabel ?authorLabel (YEAR(?date) AS ?year) ?publisherLabel ?page WHERE {
  ?item wdt:P31 wd:Q571; wdt:P921 wd:Q847 .
  OPTIONAL { ?item wdt:P50 ?author } .
  OPTIONAL { ?item wdt:P577 ?date } .
  OPTIONAL { ?item wdt:P123 ?publisher } .
  OPTIONAL { ?item wdt:P1104 ?page } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}

Try it!

I have also made display only the year. Matěj Suchánek (talk) 16:50, 8 March 2017 (UTC)

Thanks for the refinement.--Wolbo (talk) 20:33, 8 March 2017 (UTC)

Select Item and qualifier with OR

I need a list of item that have P131=Q16287 OR P131=Q18288162. Query must show: Item, P131 value, and if available qualifier of P131 P580 and or P582. --ValterVB (talk) 20:40, 8 March 2017 (UTC)

SELECT ?item ?itemLabel ?admin ?adminLabel ?start ?end WHERE {
  VALUES ?admin { wd:Q16287 wd:Q18288162 }
  ?item p:P131 ?stat.
  ?stat ps:P131 ?admin.
  OPTIONAL { ?stat pq:P580 ?start. }
  OPTIONAL { ?stat pq:P582 ?end. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

--TweetsFactsAndQueries (talk) 09:38, 9 March 2017 (UTC)

Thanks but the result it isn't what I want. Examlpe: Q10193 has P31=Q16287 with P582=Dec 31, 2014 AND has P31=Q18288162 with P580=Jan 01, 2015 but the query show only a row for P31=Q16287 I neea also a row for the second value. --ValterVB (talk) 12:22, 9 March 2017 (UTC)

Found it (maybe):

SELECT ?item ?itemLabel ?admin ?adminLabel ?start ?end WHERE {
  VALUES ?admin { wd:Q16287 wd:Q18288162 }
  ?stat ps:P131 ?admin.
  
  ?item p:P131 ?stat; #The trick is the semicolon
  OPTIONAL { ?stat pq:P580 ?start. }
  OPTIONAL { ?stat pq:P582 ?end. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} ORDER BY ?item

Try it!

--ValterVB (talk) 07:28, 10 March 2017 (UTC)

Addendum: this kind of query (more rows with the same item) don't work with {{Wikidata list}} :( --ValterVB (talk) 20:27, 10 March 2017 (UTC)

@ValterVB: maybe something like that ?

SELECT DISTINCT ?item ?itemLabel ?startPro ?endPro ?startCity ?endCity WHERE {
  { ?item wdt:P131 wd:Q16287 . } UNION { ?item wdt:P131 wd:Q18288162 . }
  OPTIONAL { ?item p:P131 [ ps:P131 wd:Q16287 ; pq:P580 ?startPro ] . }
  OPTIONAL { ?item p:P131 [ ps:P131 wd:Q16287 ; pq:P582 ?endPro ] . }
  OPTIONAL { ?item p:P131 [ ps:P131 wd:Q18288162 ; pq:P580 ?startCity ] . }
  OPTIONAL { ?item p:P131 [ ps:P131 wd:Q18288162 ; pq:P582 ?endCity ] . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,it". }
}

Try it!

Not very elegant but at least on one row. Cdlt, VIGNERON (talk) 11:43, 11 March 2017 (UTC)

Good solution, now work like I want. Thanks. --ValterVB (talk) 13:09, 11 March 2017 (UTC)

list of sortable properties?

I had some problem with ORDER BY clauses when working with items. I stumbled upon 3 instances of orderable Wikidata property (Q18668171). Now I want to understand what is sortable and what is not.

SELECT DISTINCT ?property ?propertyLabel ?type ?moddate
WHERE
{
    ?property                a    wikibase:Property  .
  
#   sortable properties, according to Q18668171
#   ?property            wdt:P31        wd:Q18668171 .
  
    ?property       wikibase:propertyType ?type      .
    ?property         schema:dateModified ?moddate   .
    
#   FILTER ( ?type NOT IN (wikibase:WikibaseItem, wikibase:ExternalId, 	wikibase:CommonsMedia, wikibase:Math, wikibase:GlobeCoordinate, wikibase:WikibaseProperty) )

    SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }	
} ORDER BY DESC(?type) DESC(?property)

Try it!

d1g (talk) 13:26, 11 March 2017 (UTC)

finding wikipedia article creator & creation date, bytes

Is it possible to query wikipedia article created by, created date and article size by using sparql? I want to pass user id and get the above details for certain (between) dates. Thanks --Mdmahir (talk) 16:49, 11 March 2017 (UTC)

Not possible in SPARQL with query.wikidata.org/ (the data you want are not stored in Wikidata) but there is other tools that can probably give you want you want (you should look at quarry.wmflabs.org/ or some advanced edit counters like X!'s Tools). Cdlt, VIGNERON (talk) 18:57, 11 March 2017 (UTC)

Query with qualifier

Hi - how do I make a query for an instance of something but only if it has a specific qualifier? I'm looking for items that are instances of (P31) a common name (Q502895) with the 'of' qualifier (P642) set. I'd like to get out the original data item and the 'of' items, so e.g. Q144 would match and return the mapping Q144 (dog) -> [Q20717272 (Canis familiaris), Q26972265 (Canis lupus familiaris)], whereas Q152 would not be returned because although it has Q502895 is it not qualified by P642. Thanks for any help. HYanWong (talk) 01:01, 13 March 2017 (UTC)

Could it be, that there is only these results?

SELECT ?item ?itemLabel ?pq_obj ?pq_objLabel WHERE {
  ?item p:P31 ?statement .
  ?statement ps:P31 wd:Q502895 .
  ?statement pq:P642 ?pq_obj .
  ?pq_qual wikibase:qualifier pq:P642  .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,de,fr,pl" }
}

Try it!

--Edgars2007 (talk) 11:07, 13 March 2017 (UTC)

Yes, I don't think there are many. Thanks a lot. HYanWong (talk) 16:31, 13 March 2017 (UTC)

Get all entities from wikimedia category

How to write a query to get all wikidata entities from some wikimedia category? In my case I'd like to get all entities (articles in Ukrainian wiki) from the category https://www.wikidata.org/wiki/Q7027996 (Category:Ukrainian writers). Thanks for help. --Gutsul (talk) 10:49, 13 March 2017 (UTC)

Not possible with SPARQL. If you want to get Wikidata item IDs for ukwiki articles from this category, then this should be enough. --Edgars2007 (talk) 11:10, 13 March 2017 (UTC)

Query of authors for Open Library

Hoi, I want a query that provides a name, a Wikidata ID, a VIAF ID and an Open Library ID. They need to be human, and the VIAF ID is necessary. Thanks, GerardM (talk) 08:34, 18 March 2017 (UTC)

With VIAF ID (P214) AND Open Library ID (P648)

SELECT ?item ?label ?Viaf ?Libr WHERE {
  ?item wdt:P31 wd:Q5.
  ?item wdt:P214 ?Viaf.
  ?item wdt:P648 ?Libr.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en" . 
    ?item rdfs:label ?label
  }
}
LIMIT 100

Try it!

With VIAF ID (P214), Open Library ID (P648) not mandatory

SELECT ?item ?label ?Viaf ?Libr WHERE {
  ?item wdt:P31 wd:Q5.
  ?item wdt:P214 ?Viaf.
  OPTIONAL { ?item wdt:P648 ?Libr. }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en" . 
    ?item rdfs:label ?label
  }
}
LIMIT 100

Try it!

--ValterVB (talk) 10:31, 18 March 2017 (UTC)

I like it but to run it on the full database, it times out. Thanks, GerardM (talk) 12:57, 18 March 2017 (UTC)

If you can avoid the label, these queries work: 95238 item for the first query, 890329 for the second query. Example

SELECT ?item  ?Viaf ?Libr WHERE {
  ?item wdt:P31 wd:Q5.
  ?item wdt:P214 ?Viaf.
  ?item wdt:P648 ?Libr.

Try it!

--ValterVB (talk) 14:53, 18 March 2017 (UTC)

I was successful with this query:

SELECT ?item ?itemLabel ?viaf ?lib WHERE {
  ?item wdt:P648 ?lib; wdt:P214 ?viaf; wdt:P31 wd:Q5 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}

Try it!

When I made the Open Library ID non-mandatory, it was so heavy that my laptop had spent almost 1GB RAM when I interrupted it. So that isn't the right way to go. Matěj Suchánek (talk) 16:08, 18 March 2017 (UTC)

You can get most of this using Beacon, though it won't give you the name. However, you can paste the results from Beacon into Google Sheets and use the Wikipedia add-on's "WIKIDATALABELS" to get the labels from the QIDs. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 17:17, 18 March 2017 (UTC)

Thank you for your support. I have a list of many items with both VIAF and Open Library. I send them to Open Library and they are happy to see if and how they can get it into their author data.. They do concentrate on books though. Thanks, GerardM (talk) 09:10, 19 March 2017 (UTC)

provincial parks in a specific canadian province

Hi, I actually done a query about all the provincial park of Canada (Q2006279) in Canada. But I like tho have a list for a specific province, like Newfoundland and Labrador (Q2003). It's actually I want to create a subclass of each province. --Fralambert (talk) 00:59, 19 March 2017 (UTC)

Is it necessary to create a subclass for parks in different provinces? Is there an difference between parks in Newfoundland and Labrador (Q2003) and, say, Quebec (Q176)? (If the answer is yes, then this applies to the first question as well.)

SELECT ?item ?itemLabel WITH {
  SELECT DISTINCT ?item {
    ?item wdt:P31 wd:Q2006279; wdt:P17 wd:Q16; wdt:P131+ wd:Q2003 .
  }
} AS %subquery WHERE {
  INCLUDE %subquery .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}

Try it!

(You may use only the %subquery part for a pure list of items.) Matěj Suchánek (talk) 14:24, 19 March 2017 (UTC)

Thanks. Well, each province have is own park law. But the other reason is that I try to add the items for missing provincial parks on Wikidata, and it's easier by province. --Fralambert (talk) 15:54, 19 March 2017 (UTC)

It also helped me to found the two missing parks Newfoundland T'Railway (Q7017867) and J.T. Cheeseman Provincial Park (Q17513989). --Fralambert (talk) 16:23, 19 March 2017 (UTC)

Items with a redirect link on wikipedias

Is there a query that shows items that contains a redirect link, for example: Ratanang (Q7295470) with redirect on en.wp en:Ratanang. Q.Zanden questions? 11:51, 19 March 2017 (UTC)

Impossible with SPARQL but we are looking for a way how to clean the up, see phab:T145522. Matěj Suchánek (talk) 14:16, 19 March 2017 (UTC)

Too bad it is not yet possible, but thanks for your reply! Q.Zanden questions? 16:10, 19 March 2017 (UTC)

100 women (BBC)

How do I query for "award received" Q16153104 for a specific year ie 2016 or 2013 .. Thanks, GerardM (talk) 13:10, 19 March 2017 (UTC)

Like this:

SELECT ?item ?itemLabel {
  ?item p:P166 [ ps:P166 wd:Q16153104; pq:P585 ?date ] .
  FILTER( YEAR( ?date ) = 2013 ) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}

Try it!

Matěj Suchánek (talk) 14:11, 19 March 2017 (UTC)

Number of values of a property of each instance

How to get the number of values of a property of an item? Take for example, I would like to know the number of official website for each instance of a programming language (wdt:P31/wdt:P279*). Somehow the following query is not correct.

SELECT ?lang (count(?website) as ?count)
WHERE {
        ?lang wdt:P31/wdt:P279* wd:Q9143.
        ?lang wdt:P856 ?website.
        SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
GROUP BY ?lang

Jsamwrites (talk) 15:34, 19 March 2017 (UTC)

@Jsamwrites: I’m not sure if this is what you mean (you didn’t specify in which ways your query is not correct), but I fixed the following problems:

• Use COUNT(DISTINCT ?website) so that multiples paths from ?lang to programming language (Q9143) don’t increase the count.
• Use p:P856 instead of wdt:P856 to count all official website (P856) statements, even if not all have the same rank.
• Add the ?langLabel to the GROUP BY and SELECT.

SELECT ?lang ?langLabel (COUNT(DISTINCT ?website) as ?count)
WHERE {
  ?lang wdt:P31/wdt:P279* wd:Q9143.
  ?lang p:P856 ?website.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
GROUP BY ?lang ?langLabel
ORDER BY DESC(?count)

Try it!

--TweetsFactsAndQueries (talk) 09:51, 20 March 2017 (UTC)

Thanks TweetsFactsAndQueries. This is exactly what I was looking for. Jsamwrites (talk) 10:08, 20 March 2017 (UTC)

Find city name and given names that overlap

I would like to compare the labels of items that are subclasses of given name (Q202444) with the labels that are subclasses of city (Q515) in one language to find overlaps. One item for example that should appear in the results is Sofia (Q472) and Sofia (Q18201520) (or one of the other items for similar names). I tried around for a while but not sure how to write a filter function for that. --Tobias1984 (talk) 19:35, 20 March 2017 (UTC)

• Items used: given name (Q202444)   

• Properties used: instance of (P31)   , subclass of (P279)   , coordinate location (P625)   

• Features used: map (Q24515275)   

#defaultView:Map
SELECT *
{
  	?given_name_item wdt:P31/wdt:P279* wd:Q202444 .
  	?given_name_item rdfs:label ?name .
  	?place wdt:P625 ?coor  .
  	?place rdfs:label ?name .
}
LIMIT 1000

Try it!

@Tobias1984: maybe like the above. Using twice "/wdt:P279*" in one query tends to time-out.
--- Jura 20:04, 20 March 2017 (UTC)

Any possibility of an English language translation of what is that query doing, Jura? Sadly my SPARQL-fu is still weak. thx --Tagishsimon (talk) 20:40, 20 March 2017 (UTC)

The first two lines select given names in any language (rdfs:label has multiple values each with a language code attached).

The second part looks for items with coordinates and the same label.
--- Jura 20:50, 20 March 2017 (UTC)

Thanks. I'm up to speed now. ?name is the same in 2 & 4, so ?given_name_item is the same name as ?place. And as a noob, I'm looking at the blank space where a WHERE would be expected. Is there a story behind that? --Tagishsimon (talk) 22:47, 20 March 2017 (UTC)

You could add it and compare the two.
--- Jura 11:29, 21 March 2017 (UTC)

True ;) --Tagishsimon (talk) 23:23, 22 March 2017 (UTC)

Most used identifiers

[Assuming we don't have this already]

Which external-ID type properties are used the most? Please provide a list, with usage, ranked by use. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 17:00, 14 March 2017 (UTC)

Here’s the best query I’ve got, but it times out:

SELECT ?p (COUNT(*) AS ?count) WITH {
  SELECT ?p WHERE {
    ?property wikibase:propertyType wikibase:ExternalId;
              wikibase:claim ?p.
    BIND(xsd:integer(SUBSTR(STR(?property), STRLEN(STR(wd:))+2)) AS ?pNumber).
    #FILTER(0000 <= ?pNumber && ?pNumber < 4000).
  }
} AS %properties WHERE {
  INCLUDE %properties.
  [] ?p [].
}
GROUP BY ?p

Try it!

However, it seems that it’s possible to run the query in batches on a range of properties (see the commented-out line). I’ll try to do that, merge the results and upload them for you. --TweetsFactsAndQueries (talk) 19:59, 14 March 2017 (UTC)

If you only need an overview try Wikidata:Database reports/List of properties/all. --Succu (talk) 20:13, 14 March 2017 (UTC)

Thanks, I’ll stop harassing the query service then :) for convenience, here’s the ordered list of all external-id properties (sorry, the format isn’t very pretty, I just copied the table cells). --TweetsFactsAndQueries (talk) 20:31, 14 March 2017 (UTC)

For those who are interested: my way of doing things. Run SQL query and get all external identifier props from SPARQL and then simply glue together things. Of course, if you're using some bot. This solution isn't for manual work. --Edgars2007 (talk) 16:41, 23 March 2017 (UTC)

Visual artists dead more than 70 years

Hi all, I would like to run a query that gives all visual artists that are dead more than 70 years and that have been dead between 70 years and 90 years, because of public domain notifications in Europe. So far, I have come up with this query (mix n matched from existing queries), but I get a time out and I do not know why this is. Can you help me, please?

SELECT ?item ?itemLabel ?_date_of_death ?_country ?_occupation ?_date_of_birth WHERE {
  ?person wdt:P106/wdt:P279* wd:Q483501 . #person with occupation (P106) any subclass(P279) of artist (Q483501)
  ?person wdt:P570 ?death .
  FILTER (?death >= "1946-01-01T00:00:00Z"^^xsd:dateTime) .
      
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  OPTIONAL { ?item wdt:P570 ?_date_of_death. }
  OPTIONAL { ?item wdt:P17 ?_country. }
  OPTIONAL { ?item wdt:P106 ?_occupation. }
  OPTIONAL { ?item wdt:P569 ?_date_of_birth. }
}
LIMIT 500

Try it!

Mtmlan84 (talk) 09:35, 16 March 2017 (UTC)

SELECT ?person ?personLabel ?death ?_country ?_occupation ?_date_of_birth WHERE {
  ?person wdt:P570 ?death .
  FILTER ( YEAR( ?death ) >= 1927 && YEAR( ?death ) < 1947 ) .
  ?person wdt:P106/wdt:P279* wd:Q483501 . #person with occupation (P106) any subclass(P279) of artist (Q483501)
  OPTIONAL { ?person wdt:P27 ?_country } .
  OPTIONAL { ?person wdt:P106 ?_occupation } .
  OPTIONAL { ?person wdt:P569 ?_date_of_birth } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}
LIMIT 500

Try it!

Note that you used ?item and ?person for the same thing, as well as ?death and ?_date_of_death. Also country (P17) doesn't make sense for people, I changed it to country of citizenship (P27). Now this works without limit but you will certainly experience problems with the same artist listed more than once. Matěj Suchánek (talk) 09:56, 16 March 2017 (UTC)

Thanks so much for this, very much appreciated! Mtmlan84 (talk) 15:45, 22 March 2017 (UTC)

SELECT ?person ?personLabel 
(GROUP_CONCAT( ?_occupation) as ?occupation)
(SAMPLE(?_country) as ?_country)
 
WHERE {
  ?person wdt:P570 ?death .
  FILTER ( YEAR( ?death ) >= 1927 && YEAR( ?death ) < 1947 ) .
  ?person wdt:P106/wdt:P279* wd:Q483501 . #person with occupation (P106) any subclass(P279) of artist (Q483501)
  OPTIONAL { ?person wdt:P27 ?_country } .
  OPTIONAL { ?person wdt:P106 ?_occupation } .
  OPTIONAL { ?person wdt:P569 ?_date_of_birth } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}
GROUP BY ?person ?personLabel

Try it!

I asked around and ended up with this query using GROUP_CONCAT to group multiple occupations per artist. Now, I would like to get the labels for the occupations back instead of the URLS to the Q numbers. Any idea how I can go about this? Thanks so much! Mtmlan84 (talk) 08:55, 23 March 2017 (UTC)

SELECT ?person ?personLabel ?_date_of_birth
(GROUP_CONCAT(?_occupationLabel; separator=', ') AS ?occupation)
(SAMPLE(?_countryLabel) AS ?country)

WITH {
  SELECT DISTINCT ?person {
    ?person wdt:P570 ?death .
    FILTER ( YEAR( ?death ) >= 1927 && YEAR( ?death ) < 1947 ) .
    ?person wdt:P106/wdt:P279* wd:Q483501 . #person with occupation (P106) any subclass(P279) of artist (Q483501)
  } # put a LIMIT here if it times out
} AS %subquery WHERE {
  INCLUDE %subquery .
  OPTIONAL { ?person wdt:P27 ?_country } .
  OPTIONAL { ?person wdt:P106 ?_occupation } .
  OPTIONAL { ?person wdt:P569 ?_date_of_birth } .
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en" .
    ?person rdfs:label ?personLabel .
    ?_country rdfs:label ?_countryLabel .
    ?_occupation rdfs:label ?_occupationLabel .
  } .
} GROUP BY ?person ?personLabel ?_date_of_birth

Try it!

It's really hackish way but should work. Matěj Suchánek (talk) 13:17, 23 March 2017 (UTC)

Removing duplicate pairs

Hi,

I stumble upon a duplication problem I don't know how to solve. How can I remove the duplicate pairs in the following request? (as adjacent station (P197) is symmetric, all resultat are duplicated : « stationX, stationY » and then « stationY, stationX »).

SELECT DISTINCT ?stationA ?stationALabel ?stationB ?stationBLabel ?slope ?altA ?altB ?coordA ?coordB ?diff ?dist
WHERE
{
	?stationA wdt:P197 ?stationB .
	?stationA wdt:P2044 ?altA ; wdt:P625 ?coordA .
	?stationB wdt:P2044 ?altB ; wdt:P625 ?coordB .
	BIND( geof:distance( ?coordA , ?coordB) as ?dist ) .
	BIND( abs(?altA - ?altB) as ?diff )
	BIND( (?diff / ?dist) as ?slope )
	SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en" }
} order by desc(?slope)

Try it!

Cdlt, VIGNERON (talk) 10:59, 24 March 2017 (UTC)

SELECT DISTINCT ?stationA ?stationALabel ?stationB ?stationBLabel ?slope ?altA ?altB ?coordA ?coordB ?diff ?dist
WHERE
{
    ?stationA wdt:P197 ?stationB .
    FILTER( STR( ?stationA ) < STR( ?stationB ) ) .
    ?stationA wdt:P2044 ?altA ; wdt:P625 ?coordA .
    ?stationB wdt:P2044 ?altB ; wdt:P625 ?coordB .
    BIND( geof:distance( ?coordA , ?coordB) AS ?dist ) .
    BIND( ABS(?altA - ?altB) AS ?diff ) .
    BIND( (?diff / ?dist) AS ?slope ) . 
    SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en" } .
} ORDER BY DESC(?slope)

Try it!

Matěj Suchánek (talk) 20:34, 24 March 2017 (UTC)

@Matěj Suchánek: thank you a lot! Could you be kind to explain a bit how did it work? (I'm not sure to understand how the less-than works with strings...). Cdlt, VIGNERON (talk) 12:03, 25 March 2017 (UTC)

I have already seen a query with this hack, so I assume strings are compared alphabetically (like in Python). So STR( wd:Q1 ) → http://www.wikidata.org/entity/Q1 "is lower than" STR( wd:Q2 ) → http://www.wikidata.org/entity/Q2 etc. Matěj Suchánek (talk) 12:12, 25 March 2017 (UTC)

image (P18) references

Hello. I need help with a SPARQL involving image (P18) and references [2]. I have checked the examples list but I haven't found anything useful for this. I need a query that retrieves all items which use image (P18) without references. Thanks. Emijrp (talk) 11:48, 25 March 2017 (UTC)

List cities 30km around New York City

Hi, I would like to have a list with names of the cities 30km around NYC. I tried to run the "Airports 100km around Berlin" sample query but it times out.

Salut @Noux: it seems indeed that « ?place wdt:P31/wdt:P279* wd:Q515 » cause a times out... (which is to be be exepected as city (Q515) is a very wide concept with a *lot* a subconcept, maybe you should use a more precise concept). Without P279* it works :

#Cities within 30km of New York
#defaultView:Map
SELECT ?place ?placeLabel ?location
WHERE
{
  # New York coordinates
  wd:Q60 wdt:P625 ?NYLoc . 
  SERVICE wikibase:around { 
      ?place wdt:P625 ?location . 
      bd:serviceParam wikibase:center ?NYLoc . 
      bd:serviceParam wikibase:radius "30" . 
  } 
  # Is a city
  ?place wdt:P31 wd:Q515 .
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr" . 
  }
}

Try it!

You can also request everything aroudn New York :

#Cities within 30km of New York
#defaultView:Map
SELECT ?place ?placeLabel ?location
WHERE
{
  # New York coordinates
  wd:Q60 wdt:P625 ?NYLoc . 
  SERVICE wikibase:around { 
      ?place wdt:P625 ?location . 
      bd:serviceParam wikibase:center ?NYLoc . 
      bd:serviceParam wikibase:radius "30" . 
  }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "fr" . 
  }
}

Try it!

Cdlt, VIGNERON (talk) 12:17, 24 March 2017 (UTC)

Thank you @Vigneron:, that's a very good start! I actually would like a list of towns/cities (I didn't realize it was treated separatly) with their population and distance from NYC, I am looking for a new place of work :) I will make my own researches but I'm pretty sure you will have figured it out far faster than I -_-  – The preceding unsigned comment was added by Noux (talk • contribs) at 24. 3. 2017, 13:24‎ (UTC).

SELECT DISTINCT ?place ?placeLabel ?location ?population ?distance WHERE {
  wd:Q60 wdt:P625 ?NYLoc .
  ?place wdt:P625 ?location; wdt:P17 wd:Q30 .
  BIND( geof:distance( ?NYLoc, ?location ) AS ?distance ) .
  FILTER( ?distance < 30 ) .
  ?place wdt:P1082 ?population; wdt:P31/wdt:P279* wd:Q486972 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en" } .
} ORDER BY ?distance

Try it!

Matěj Suchánek (talk) 20:53, 24 March 2017 (UTC)

Excellent, thank you very much!

Noux (talk) 13:19, 27 March 2017 (UTC)

wdt:P31/wdt:P279*

Could someone please explain the syntax of wdt:P31/wdt:P279* - what is the * ? And why does this find zero results if it is as I understand - instance of or sub-class of):

SELECT ?item (COUNT(?item) AS ?count)
WHERE {
  ?item wdt:P31/wdt:P279* wd:Q8066 .
    ?item wdt:P702 ?uuu .
  ?uuu wdt:P31 wd:Q165052 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,nl". }
  }
group by ?item

Try it!

when this does find results.

SELECT ?item (COUNT(?item) AS ?count)
WHERE {
  ?item wdt:P279 wd:Q8066 .
    ?item wdt:P702 ?uuu .
  ?uuu wdt:P31 wd:Q165052 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,nl". }
  }
group by ?item

Try it!

thanks --Tagishsimon (talk) 01:18, 27 March 2017 (UTC)

As far as I understand, ?item wdt:P31/wdt:P279* wd:Q8066 . means that either ?item has P31:Q8066 OR P31 value is subclass (at some level) of Q8066. Somebody could clarify, if I'm right :) --Edgars2007 (talk) 06:53, 27 March 2017 (UTC)

You can use the slash / to simplify a query:

?item wdt:P31 ?class .
?class wdt:P279* wd:Q8066 .

Now imagine both ?classes being crossed out by a single line (the slash) and you'll get:

?item wdt:P31/wdt:P279* wd:Q8066 .

The same way, you could drop ?uuu using ?item wdt:P702/wdt:P31 wd:Q165052.

Asterisks * and pluses + are useful for transitive properties like subclass of (P279), located in the administrative territorial entity (P131), child (P40) etc. wdt:P279+ means subclass of or subclass of a subclass of and so on... When you use *, you will also match the upper class itself, so ?class wdt:P279* wd:Q8066 will also match wd:Q8066 but ?class wdt:P279+ wd:Q8066 won't.

Why the query doesn't return any results? Perhaps because there are no instances of a amino acid (Q8066), just subclasses. Matěj Suchánek (talk) 13:21, 27 March 2017 (UTC)

Non-humans with ORCID iDs

This query:

#top 50 for P31 and P279 of items using P496
SELECT ?class ?classLabel ?count ?use_as_Label
{	{	SELECT ?class (COUNT(*) AS ?count) (wd:P31 as ?use_as_)
		{	?a	wdt:P496	?p	; wdt:P31	?class}
        GROUP BY ?class ORDER BY DESC(?count) LIMIT 50
  	}
  	UNION
	{	SELECT ?class (COUNT(*) AS ?count) (wd:P279 as ?use_as_)
		{	?a	wdt:P496	?p	; wdt:P279	?class}
        GROUP BY ?class ORDER BY DESC(?count) LIMIT 50
  	}
  	SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en" }
}
ORDER BY DESC(?count) ?class

Try it!

says, at the time of writing, that we have items with a ORCID iD (P496), that are instances of lake, a mountain range, or software (Q7397).

However, this query, for example, finds no results.

#software with an ORCID iD
SELECT ?item ?itemLabel
WHERE
{
  ?item wdt:P496 wd:Q7397.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

Is the bug in the later, or elsewhere? Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 10:41, 27 March 2017 (UTC)

#software with an ORCID iD
SELECT ?item ?itemLabel
WHERE
{
  ?item wdt:P496 [] ; wdt:P31 wd:Q7397.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

You were checking if its ID was software, not if it had an ID and it was a software. – Máté (talk) 10:54, 27 March 2017 (UTC)

Got it, thank you. (There are no results now, because I've fixed the error on the item concerned.) Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 12:31, 27 March 2017 (UTC)

displaying name instead of Qid

I managed to get this list:

#Triplets with their encodement
SELECT ?itemLabel ?codingLabel  WHERE {
  ?item wdt:P31 wd:Q165052.
  Optional{?item wdt:P688 ?codingLabel.}
  	FILTER NOT EXISTS{?item wdt:P31 wd:Q28354.}
  	FILTER NOT EXISTS{?item wdt:P31 wd:Q1854186.} 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,nl". }
  }

Try it!

but I failed to get the label, now it shows only the Qid, but I want to get the label of that item. How to fix this? Q.Zanden questions? 22:42, 26 March 2017 (UTC)

#Triplets with their encodement
SELECT ?itemLabel ?codingLabel  WHERE {
  ?item wdt:P31 wd:Q165052.
  Optional{?item wdt:P688 ?coding.}
  	FILTER NOT EXISTS{?item wdt:P31 wd:Q28354.}
  	FILTER NOT EXISTS{?item wdt:P31 wd:Q1854186.} 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,nl". }
  }

Try it!

 ;) --Tagishsimon (talk) 23:26, 26 March 2017 (UTC)

Thank you! Is there also a possibility to show how many triplets are coding for one aminoacid? Q.Zanden questions? 23:28, 26 March 2017 (UTC)

Not without understanding your question. POint to data and tell us what you're after. (May still be above my pay-grade. --Tagishsimon (talk) 23:33, 26 March 2017 (UTC)

Appearing in a list like:

• phenylalanine (Q170545) has 2 triplets coding for it: UUC and UUU

And that for all aminoacids. Q.Zanden questions? 23:51, 26 March 2017 (UTC)

So to check, we want a count within items which are am instance or subclass of amino acid (Q8066), of the encoded by (P702) values which which have a instance of (P31) of codon (Q165052)? --Tagishsimon (talk) 00:13, 27 March 2017 (UTC)

This may come close. Fair warning, though; I'm more blundering through SPARQL than someone to be relied on. (I note that substituting P31 for P279 finds now records ... I should be able to combine them but it didn't seem to work well.)

SELECT ?item (COUNT(?item) AS ?count)
WHERE {
  ?item wdt:P279 wd:Q8066 .
    ?item wdt:P702 ?uuu .
  ?uuu wdt:P31 wd:Q165052 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,nl". }
  }
group by ?item

Try it!

--Tagishsimon (talk) 01:11, 27 March 2017 (UTC)

Thanks a lot, but one more question: is it also possible to display the triplets that are coding for that aminoacid? Q.Zanden questions? 14:17, 27 March 2017 (UTC)

I can't do that and count them at the same time; for that you'd need a grown-up. But without counting, this:

SELECT ?item ?itemLabel ?uuuLabel
WHERE {
  ?item wdt:P279 wd:Q8066 .
    ?item wdt:P702 ?uuu .
  ?uuu wdt:P31 wd:Q165052 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,nl". }
  }

Try it!

--Tagishsimon (talk) 18:22, 27 March 2017 (UTC)

Thanks a lot for your help! These different queries made me also a bit more confident with the QueryService. Q.Zanden questions? 20:55, 27 March 2017 (UTC)

All peoples NOT born or dead a specific month and year

Hi !
I tried to adapt queries like http://stackoverflow.com/questions/41841969/how-to-find-people-who-were-born-in-summer-from-wikidata and Wikidata:SPARQL_query_service/queries#Male_Americans_born_after_1875_and_before_1930.2C_that_have_a_day-specific_date_of_death.2C_but_only_a_year-specific_date_of_birth. to find peoples NOT born or dead a specific month and year, without success.
The goal is to conjugate this query with the French subcategories of fr:Catégorie:Chronologie des naissances classified by month and year in PetScan to find the articles in French Wikipedia having a different date of birth or death in Wikidata. Simon Villeneuve (talk) 20:30, 28 March 2017 (UTC)

I think I can also do it with a query giving all peoples born or dead a specific month and year (it seem less difficult to do), but if someone can do the first query, it will be better. --Simon Villeneuve (talk) 21:50, 28 March 2017 (UTC)

This Petscan, I think, does what you want, but at the year level not the month level - this example is for 1936. It looks at 'Category:Naissance en 1936' to 3 levels deep, and has a SPARQL query in the Other Sources tab; and 'Articles with Wikidata Items' checked in the Wikidata tab. Gets 69 results; as normal, you might need to try more than once before Petscan completes.

What I'm not sure about (and don't have time to play with right now) is how to deal with wikidata items that have only a year for the DoB, if we are trying to filter at the month-level. The SPARQL right now is below - perhaps someone more savvy with dates can extend it?

SELECT ?item ?birth WHERE {
  ?item wdt:P31 wd:Q5 .
  ?item wdt:P569 ?birth .
  FILTER(!(year(?birth) =1936))
}

Try it!

--Tagishsimon (talk) 22:30, 28 March 2017 (UTC)

Thank you for your work ! Unfortunately, PetScan didn't make it everytime I run it.
I tried this, with the same year than you, and it seems to do what I had imagined : petscan:844526. Simon Villeneuve (talk) 23:49, 28 March 2017 (UTC)

My problem is that it seems to only have worked once. I tried to adapt to other year, or to substract other categories (petscan:844546 et petscan:844565), and it don't work. Simon Villeneuve (talk) 10:47, 29 March 2017 (UTC)

Petscan does seem to struggle. I tried your petscan:844526 and iirc got 39 results back. Like you, I'm scratching my head at the result for petscan:844546.

Meanwhile I have two different queries on the go:

• petscan:844419 which uses FILTER(!(year(?birth) =1936)), and gets 69 results
• petscan:845498 which uses FILTER(!(?birth >= "1936-01-01T00:00:00Z"^^xsd:dateTime) && (?birth <= "1936-12-31T00:00:00Z"^^xsd:dateTime)), and which gets 39 results.

An example record found in the first set and not in the second is La Lupe (Q450849) which specifies a DoB of 23 December 1939 ... but a record common to both sets, such as Jackie Mason (Q75021), has a similar date format - 9 June 1931. I don't have enough time to figure out what the difference between the two is. I've tried both my petscans with 1937 dates, and they work - petscan:845559 (21 results) and petscan:845560 (45 results). So. You do have something to get your teeth into, if petscan will play nicely for you. Meanwhile we still need an adult to give us some guidance on date handling in queries, following the lines of the discussion in this thread. --Tagishsimon (talk) 20:02, 29 March 2017 (UTC)

Female tennis players who have won a Grand Slam singles title

Hi, I am looking for an overview of female tennis players (P106: Q10833314) who have won (P1346) a singles title (P31: Q16893403) at a Grand Slam tournament (Australian Open (Q60874) or French Open (Q43605) or Wimbledon Championships (Q41520) or US Open (Q123577)) and would like to display the tournamen name, player name, handedness (P552), year of birth and country of citizenship. Thanks! --Wolbo (talk) 16:14, 12 March 2017 (UTC)

Like this? I’m not sure if the connection from winner statement to tournament is correct – I tried to imply it from some example items, but in most cases there didn’t seem to be any connection from the event to the tournament.

SELECT ?tournament ?tournamentLabel ? player ?playerLabel ?handednessLabel (YEAR(?dateOfBirth) AS ?yearOfBirth) ?countryLabel WHERE {
  VALUES ?tournament { wd:Q60874 wd:Q43605 wd:Q41520 wd:Q123577 }
  ?player wdt:P31 wd:Q5;
          wdt:P21 wd:Q6581072;
          wdt:P106 wd:Q10833314;
          p:P1346/ps:P1346 [
            wdt:P31 wd:Q16893403;
            wdt:P361 [ wdt:P31 ?tournament ]
          ].
  OPTIONAL { ?player wdt:P552 ?handedness. }
  OPTIONAL { ?player wdt:P569 ?dateOfBirth. }
  OPTIONAL { ?player wdt:P27 ?country. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

--TweetsFactsAndQueries (talk) 20:01, 12 March 2017 (UTC)

Thx User:TweetsFactsAndQueries ! Looks very good. It displays far fewer records than expected but that is probably due to missing data, i.e. either 'part of' or 'instance of' (=women's singles), which links the items. The link from tournament to winner involves three levels, e.g. French Open (tournament) > 1972 French Open (tournament edition) > 1972 French Open – Women's Singles (tournament edition event). The latter has the winner as a property. I added the tournament and player id to the query for linking purposes. Could you update the query to also display the tournament edition name (e.g. 1972 French Open) and id (Q738118)? --Wolbo (talk) 20:50, 12 March 2017 (UTC)

@Wolbo: I think I see another reason for the lack of results – I misunderstood the winner (P1346) link. I thought it should go from the player to the tournament, but it looks like it’s more common to link from the tournament to the player. This query accounts for both possibilities:

SELECT ?tournament ?tournamentLabel ?tournamentEdition ?tournamentEditionLabel ?player ?playerLabel ?handednessLabel (YEAR(?dateOfBirth) AS ?yearOfBirth) ?placeOfBirthLabel ?countryLabel WHERE {
  VALUES ?tournament { wd:Q60874 wd:Q43605 wd:Q41520 wd:Q123577 }
  ?player wdt:P31 wd:Q5;
          wdt:P21 wd:Q6581072;
          wdt:P106 wd:Q10833314.
  { ?player p:P1346/ps:P1346 ?event. } UNION
  { ?event wdt:P1346 ?player. }
  ?event wdt:P31 wd:Q16893403;
         wdt:P361 ?tournamentEdition.
  ?tournamentEdition wdt:P31 ?tournament.
  OPTIONAL { ?player wdt:P552 ?handedness. }
  OPTIONAL { ?player wdt:P569 ?dateOfBirth. }
  OPTIONAL { ?player wdt:P19 ?placeOfBirth. }
  OPTIONAL { ?player wdt:P27 ?country. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY ?tournamentLabel ?tournamentEditionLabel

Try it!

--TweetsFactsAndQueries (talk) 20:54, 12 March 2017 (UTC)

@TweetsFactsAndQueries: That produces a seemingly complete set of results. Excellent! Currently all of the Grand Slam tournament editions have a winner (P1346) for the women's singles events but, vice versa, very few player items show which events they have won. Roger Federer is one of the exceptions. Can wikidata not automatically make certain statements reciprocal, e.g. when Federer is added as winner to the 2017 Australian Open – Men's Singles item (Q22956224) then it automatically adds this event to his winner (P1346) property? --Wolbo (talk) 21:32, 12 March 2017 (UTC)

@Wolbo: No, and in fact, I think that should be considered incorrect in this case – winner (P1346) is not a symmetric property, so as far as I understand it doesn’t actually belong on the person item – that should be an inverse property: victory (P2522). (To add victory (P2522) statements, you would probably use some kind of bot.) --TweetsFactsAndQueries (talk) 21:35, 12 March 2017 (UTC)

So the Roger Federer item is incorrect in this regard? Will have a look around for some bot expertise (once I'm certain what the mechanism should be). Thanks again for the help.--Wolbo (talk) 21:49, 12 March 2017 (UTC)

@TweetsFactsAndQueries: if you don't mind I have two more questions. I added the place of birth in the above query in the expectation that this would enable the map display but that option is grayed out. How can it be enabled? Also I noticed several duplicate results in the second query. Is this because it accounts for both options, from player to tournament as well as from tournament to player? If so can you update the query to show only the option from the tournament to the player? --Wolbo (talk) 00:10, 14 March 2017 (UTC)

@Wolbo: For the map view, you need coordinates coordinate location (P625) in the results, not just the label of a location.

SELECT DISTINCT ?tournament ?tournamentLabel ?tournamentEdition ?tournamentEditionLabel ?player ?playerLabel ?handednessLabel (YEAR(?dateOfBirth) AS ?yearOfBirth) ?placeOfBirthLabel ?placeOfBirthCoordinates ?countryLabel ?dateOfBirth WHERE {
  ?player wdt:P31 wd:Q5;
          wdt:P21 wd:Q6581072;
          wdt:P106 wd:Q10833314.
  ?event wdt:P1346 ?player.
  ?event wdt:P31 wd:Q16893403;
         wdt:P361 ?tournamentEdition.
  ?tournamentEdition wdt:P31 ?tournament.
  ?tournament wdt:P361 wd:Q102113.
  OPTIONAL { ?player wdt:P552 ?handedness. }
  OPTIONAL { ?player wdt:P569 ?dateOfBirth. }
  OPTIONAL {
    ?player wdt:P19 ?placeOfBirth.
    OPTIONAL { ?placeOfBirth wdt:P625 ?placeOfBirthCoordinates. }
  }
  OPTIONAL { ?player wdt:P27 ?country. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY ?tournamentLabel ?tournamentEditionLabel

Try it!

Another, unrelated thing that I noticed: it would be nice to get rid of the VALUES at the beginning of the query and instead retrieve the Grand Slam tournaments in the query as well, but currently two of them are part of (P361) Grand Slam of tennis (Q102113) and two are subclass of (P279). I assume they should all be part of (P361)? --TweetsFactsAndQueries (talk) 00:44, 14 March 2017 (UTC)

@TweetsFactsAndQueries: done, updated all Grand Slam tournaments to part of (P361), seems the approriate property per Help:Basic membership properties. Noticed that the timeline option does not active with (birth)years but only with full dates, is that correct? --Wolbo (talk) 20:51, 14 March 2017 (UTC)

@Wolbo: Correct, though there’s nothing stopping you from selecting both year and full date. I’ve updated the above query to use the part of (P361) relation and to add a ?dateOfBirth column at the very end. (But be warned: trying to actually display that timeline crashed my browser tab the first time :D ) --TweetsFactsAndQueries (talk) 20:55, 14 March 2017 (UTC)

@TweetsFactsAndQueries: I added the #defaultView:Map option to the above query which makes it possible to create a tinyurl link pointing to just the map. So far, so good. Now I would like to differentiate the color of the dots on the map based on the handedness (wdt:P552) of the player. Is that possible, and if so can you explain me how? --Wolbo (talk) 22:14, 30 March 2017 (UTC)

┌────────────────────────────────────────────────────────────────────────────────────────────────────┘ @Wolbo: Sure! The map dot color is determined by the variable ?layer if it exists, so you just need to include ?handednessLabel AS ?layer (I added it at the end of the SELECT clause).

#defaultView:Map
SELECT DISTINCT ?tournament ?tournamentLabel ?tournamentEdition ?tournamentEditionLabel ?player ?playerLabel ?handednessLabel (YEAR(?dateOfBirth) AS ?yearOfBirth) ?placeOfBirthLabel ?placeOfBirthCoordinates ?countryLabel ?dateOfBirth (?handednessLabel AS ?layer) WHERE {
  ?player wdt:P31 wd:Q5;
          wdt:P21 wd:Q6581072;
          wdt:P106 wd:Q10833314.
  ?event wdt:P1346 ?player.
  ?event wdt:P31 wd:Q16893403;
         wdt:P361 ?tournamentEdition.
  ?tournamentEdition wdt:P31 ?tournament.
  ?tournament wdt:P361 wd:Q102113.
  OPTIONAL { ?player wdt:P552 ?handedness. }
  OPTIONAL { ?player wdt:P569 ?dateOfBirth. }
  OPTIONAL {
    ?player wdt:P19 ?placeOfBirth.
    OPTIONAL { ?placeOfBirth wdt:P625 ?placeOfBirthCoordinates. }
  }
  OPTIONAL { ?player wdt:P27 ?country. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY ?tournamentLabel ?tournamentEditionLabel

Try it!

By the way, you can also link to embed.html to show the map immediately without having to click “Run”, like this. --TweetsFactsAndQueries (talk) 06:41, 31 March 2017 (UTC)

Wikidata items with a specific image

Hello, how do you query for a specific image? I'd like to remove placeholders mistakenly imported to Wikidata such as this one: File:Chybí obrázek-botanika (druh)02.png. Thank you, --Vojtěch Dostál (talk) 14:34, 23 March 2017 (UTC)

See Wikidata:Request a query/Archive/2016/07#Items with specific string in image filename.--Jklamo (talk) 15:10, 23 March 2017 (UTC)

File:Chybí obrázek-botanika (druh)02.png#File usage. Matěj Suchánek (talk) 16:09, 23 March 2017 (UTC)

I forgot to thank you last week. Thank you both, guys. --Vojtěch Dostál (talk) 13:39, 31 March 2017 (UTC)