Wikidata

Wikidata:Request a query/Archive/2019/05

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

Number of countries in the time

Hello, I saw this

 

and I wonder if there would be a sparql query reflecting this graph ? Thanks ! Bouzinac (talk) 17:05, 24 April 2019 (UTC)

@Bouzinac: A little spoiler of what I have for now : 

select ?bound (count(?country) as ?count)

with {

  select ?country {
     ?country wdt:P31/wdt:P279* wd:Q3624078 # sovereign states, I don’t know if it’s the right item
  } 
}as %countries
with
{
  select distinct ?bound {
    ?country (wdt:P571|wdt:P576) ?bound. 
    include %countries .
    filter (year(?bound) > 1800)
   }
} as %bounds

where {
  include %bounds .
  include %countries .
  ?country wdt:P571 ?creation .
  optional { ?country wdt:P576 ?dest_maybe . }
  bind (coalesce(?dest_maybe, now()) as ?destruction)
  
  filter ( ?creation <= ?bound  && ?destruction > ?bound )
} group by ?bound order by ?bound
Try it!

I don’t know if this is correct and there is no visualization. There is no check of recognition by the UN or something, anything counted as a sovereign state in WD is counted, and the result may be incorrect if the countries lack a dissolution date — they will be always counted after their creation time. If they have no creation date they will be missed by the query. I have at least one other version with the endpoint of the time interval as another column. author  TomT0m / talk page 20:28, 24 April 2019 (UTC)

There is a visualisation if you select the line graph option ... the graph has many more datapoints, but shows the same essential curve as Bouzinac's image. Top SPARQL. --Tagishsimon (talk) 20:53, 24 April 2019 (UTC)
It’s better using the "graph builder" one but it’s harder to embedlink (actually embedding is eassy, it’s a matter of copy/pasting the " export " of the graph builder into a <graph>...</graph>-tag, but linking is hard) :
Sympa! Tu pourrais synthétiser par année civile ?Bouzinac (talk) 20:44, 24 April 2019 (UTC)
Je crois pas que ce soit nécessaire, cf. la figure précédente qui n’en a pas besoin, l’outil alignant les dates comme il faut sur les y. Mais si tu y tiens vraiment je peux. author  TomT0m / talk page 21:48, 24 April 2019 (UTC)
C'est déjà pas mal et ça corrobore plus ou moins l'image (qui n'est pas de moi, que j'avais trouvée sur cet article https://fr.wikipedia.org/wiki/Liste_des_pays_du_monde#Évolution_du_nombre_d'États_dans_le_monde) ==> tu aurais l'équivalent en liste détaillée, voir s'il y a pas d'erreurs manifestes ? Merci à toi, Bouzinac (talk) 22:20, 24 April 2019 (UTC)
Je suis en train de construire la liste que j’essaye d’augmenter des changements à chaque date dans une requête, c’est pas fini mais au préalable il y a déjà un truc à faire/vérifier/nettoyer, c’est la liste des états souverains connue de WD : 
select ?country (coalesce(?countryLabelFr, ?countryLabelEn, ?country) as ?countryLabel) ?creationDate ?dissolutionDate{
     ?country wdt:P31/wdt:P279* wd:Q3624078 # sovereign states, I don’t know if it’s the right item
     optional { ?country rdfs:label ?countryLabelFr filter(lang(?countryLabelFr)= "fr")} .
     optional { ?country rdfs:label ?countryLabelEn filter(lang(?countryLabelEn)= "en")}
     optional { ?country wdt:P571 ?creationDate }
    optional { ?country wdt:P576 ?dissolutionDate }
    
  } order by ?countryLabel
Try it!
On y voit quelques trucs comme des états avec des dates dans les libellées qui sont pas encore retranscrites en déclarations (Kingdom of Norway (Q2196956) ou de trucs comme Q2926400 qui ont des dates sur l’élément de la classe (taifa (Q217177) - années 1030 ) qui ne sont pas reflétées dans les instances. author  TomT0m / talk page 08:48, 25 April 2019 (UTC)
@Bouzinac: voilà. Une requête avec la liste des pays à une date en colonne de gauche, et dans les autres colonnes la liste des nouveaux et anciens pays à cette date. Ça devrait être pratique pour vérifier. author  TomT0m / talk page 09:35, 25 April 2019 (UTC)
Une anomalie marrante, il y avait l’Empire Romain dans la liste. Un qualificatif de date de fin sur sa condition d’Empire, mais pas reflétée en date de dissolution avant que je le rajoute. author  TomT0m / talk page 09:46, 25 April 2019 (UTC)
C'est impressionant, le nombre d'états qui ont existé et dont une personne lambda (j'ai des connaissances historiques mais pas de niveau universitaire) n'a probablement jamais entendu parler. OK, c'est parti dans le nettoyage, vérifications. Bouzinac (talk) 09:54, 25 April 2019 (UTC)
Tu pourrais m'aider dans la correction de cette requête? J'aimerais éliminer certaines choses qui ne sont manifestement pas des états souverains (exemple Autriche coloniale qui n'a jamais eu d'existence dans les faits)
select ?country (sample(?NatureLabelFr) as ?NatureLabelFr) (coalesce(?countryLabelEn,?countryLabelFr,  ?country) as ?countryLabel) ?creationDate ?dissolutionDate{
     ?country wdt:P31/wdt:P279* wd:Q3624078 # sovereign states, I don’t know if it’s the right item
     optional{wdt:P31 rdfs:label ?NatureLabelFr filter(lang(?NatureLabelFr)= "fr")} .
              optional { ?country rdfs:label ?countryLabelFr filter(lang(?countryLabelFr)= "fr")} .
     optional { ?country rdfs:label ?countryLabelEn filter(lang(?countryLabelEn)= "en")}
     optional { ?country wdt:P571 ?creationDate }
    optional { ?country wdt:P576 ?dissolutionDate }
    
  } 
group by ?country ?countryLabel ?creationDate ?dissolutionDate
order by ?countryLabel
Try it!

Bouzinac (talk) 10:52, 25 April 2019 (UTC)

Il faudrait éliminer de la liste les états non souverains de type vassal/comté etc, exemple Royal Lordship of Molina (Q3571951)
C’est pas nécessairement simple parce qu’il peut y avoir des historiques : une entité peut être vassale à un moment puis gagner une souverainneté. Si il y a pas 2 éléments pour les distinguer on est obligé de faire cohabiter les 2 infos… J’ai pas trop le temps làe crois, mais j que c’est ce que tu voulais plus ou moins faire ? 
tr Je crois select ?country (group_concat(?NatureLabelFr;separator=" ,") as ?NatureLabelFr) ?countryLabel  ?creationDate ?dissolutionDate

with {
  select ?country (coalesce(?countryLabelEn, ?countryLabelFr,  ?country) as ?countryLabel) ?creationDate ?dissolutionDate{
     ?country wdt:P31/wdt:P279* wd:Q3624078 # sovereign states, I don’t know if it’s the right item

     optional { ?country rdfs:label ?countryLabelFr filter(lang(?countryLabelFr)= "fr")} .
     optional { ?country rdfs:label ?countryLabelEn filter(lang(?countryLabelEn)= "en")}
     optional { ?country wdt:P571 ?creationDate }
     optional { ?country wdt:P576 ?dissolutionDate }
    
  } order by ?countryLabel
} as %datas

where {
  include %datas .
  optional{
      ?country wdt:P31/rdfs:label ?NatureLabelFr filter(lang(?NatureLabelFr)= "fr") .
  }
} group by ?country ?countryLabel  ?creationDate ?dissolutionDate
Try it!
Hello again, I'd like to eliminate Colonial empires, Vassal states and other stuff that are not pertinent : how should I redact the MINUS clause ?
select ?country (group_concat(?NatureLabelFr;separator=" ,") as ?NatureLabelFr) ?countryLabel  ?creationDate ?dissolutionDate
with {
select ?country (coalesce(?countryLabelFr, ?countryLabelEn,  ?country) as ?countryLabel) ?creationDate ?dissolutionDate{
?country wdt:P31/wdt:P279* wd:Q3624078 # sovereign states, I don’t know if it’s the right item
MINUS wd:Q1790360 #empires coloniaux
MINUS wd:Q1371288 #états vassals
optional { ?country rdfs:label ?countryLabelFr filter(lang(?countryLabelFr)= "fr")} .
optional { ?country rdfs:label ?countryLabelEn filter(lang(?countryLabelEn)= "en")}
optional { ?country wdt:P571 ?creationDate }
optional { ?country wdt:P576 ?dissolutionDate }
} order by ?countryLabel
} as %datas
where {
include %datas .
optional{
?country wdt:P31/rdfs:label ?NatureLabelFr filter(lang(?NatureLabelFr)= "fr") .
}
} group by ?country ?countryLabel  ?creationDate ?dissolutionDate
Try it!
Ok, la bonne rédaction est MINUS { ?country (wdt:P31/wdt:P279*) wd:Q1790360. }#empires coloniaux

MINUS { ?country (wdt:P31/wdt:P279*) wd:Q1371288. } #états vassals Bouzinac (talk) 07:59, 1 May 2019 (UTC)

Fix a query that generates duplicates args in template?

I've an example of a query that currently generates duplicates args in template, automatically putting the page in the corresponding tracking category, in frwiki: fr:Utilisateur:Popolon/Brouillon/Liste de mosquées de Chine. The duplicate args problem is fixed in the generated wikicode by my bot WikiCleanerBot, but of course each time ListeriaBot updates the list it recreates the problem. How can the query be modified to prevent this duplication of args in the template? The current request is 

SELECT DISTINCT ?item ?div1 ?div2
WHERE
{ 
  ?item wdt:P17 wd:Q148 . 
  ?item wdt:P31/wdt:P279* wd:Q32815 .
  OPTIONAL {
	  ?item wdt:P131* ?div1.
	  ?div1 wdt:P31/wdt:P279* wd:Q9316670.
  }
  OPTIONAL {
	  ?item wdt:P131* ?div2.
	  ?div2 wdt:P31/wdt:P279* wd:Q13396669.
  }
}
Try it!

which results in duplicate "string=" args on some templates (when both div1 and div2 are empty). Any idea on how to fix this? --NicoV (talk) 15:09, 30 April 2019 (UTC)

@NicoV: It depends how you want it to behave. What the query needs is an aggregation function, and the obvious two choices are 1) GROUP_CONCAT, which where there are two values for e.g. ?div1, will give you both values in a single row & column. Or else SAMPLE, which will select just one of the two values. Or, for all I know, yu might be able to use MAX or MIN. Some discussion at https://en.wikibooks.org/wiki/SPARQL/Aggregate_functions
The general form is, an aggregation function in the SELECT, and a GROUP BY at the end of the query, where you group by and values that are not aggregated - ?item in this case. So here are two examples. The first is probelematic, in that it converts results from a WD:Qnnnn form into a http://www.wikidata.org/entity/Qnnn form ... tbh, I'm not sure why and don't have the time to look at it right now. The second is slightly better behaved, rendering a sample as WD:Qnnnn.
Hope this gets you slightly further forward. Someone else may come along and explain what's going on in my first query, and how to deal with it.
SELECT DISTINCT ?item (GROUP_CONCAT(DISTINCT ?div1;separator=", ") as ?div1) (GROUP_CONCAT(DISTINCT ?div2;separator=", ") as ?div2)
WHERE
{ 
  ?item wdt:P17 wd:Q148 . 
  ?item wdt:P31/wdt:P279* wd:Q32815 .
  OPTIONAL {
	  ?item wdt:P131* ?div1.
	  ?div1 wdt:P31/wdt:P279* wd:Q9316670.
  }
  OPTIONAL {
	  ?item wdt:P131* ?div2.
	  ?div2 wdt:P31/wdt:P279* wd:Q13396669.
  }
} GROUP BY ?item
Try it! 
SELECT DISTINCT ?item (SAMPLE(?div1) as ?div1) (SAMPLE(?div2) as ?div2)
WHERE
{ 
  ?item wdt:P17 wd:Q148 . 
  ?item wdt:P31/wdt:P279* wd:Q32815 .
  OPTIONAL {
	  ?item wdt:P131* ?div1.
	  ?div1 wdt:P31/wdt:P279* wd:Q9316670.
  }
  OPTIONAL {
	  ?item wdt:P131* ?div2.
	  ?div2 wdt:P31/wdt:P279* wd:Q13396669.
  }
} GROUP BY ?item
Try it!
--Tagishsimon (talk) 15:30, 30 April 2019 (UTC)
Hi Tagishsimon. I'm not sure I understand your answer. My understanding is that the problem is visible when there are no values for both div1 and div2, not when there are several values. It seems that ListeriaBot creates a call to {{Wikidata list/Monument}} for each element found in WikiData, but depending on the existence of div1 (or div2), it creates a parameter named "div1" (or div2) if it exists or "string" if it doesn't exist (I don't know why, it would seem more logical to me to create an empty div1 parameter...). When both div1 and div2 do not exist, 2 parameters named "string" are then created...
The full code embedding the SPARQL query is recopied below. --NicoV (talk) 16:08, 30 April 2019 (UTC)
{{Wikidata list
|kml = true
|sparql=
SELECT DISTINCT ?item ?div1 ?div2
WHERE
{ 
  ?item wdt:P17 wd:Q148 . 
  ?item wdt:P31/wdt:P279* wd:Q32815 .
  OPTIONAL {
	  ?item wdt:P131* ?div1.
	  ?div1 wdt:P31/wdt:P279* wd:Q9316670.
  }
  OPTIONAL {
	  ?item wdt:P131* ?div2.
	  ?div2 wdt:P31/wdt:P279* wd:Q13396669.
  }
}
|sort=label
|columns= qid, col2:Nom local, ?div2:Province, ?div1:Préfecture, col5:Adressse, col6:Coordonnées, col7:Statut patrimonial, col8:Image
|row_template=Wikidata list/Monument
}}
@NicoV: When I try the wikidata list at User:Tagishsimon/junk I just get a big mess. Let's play there, or at https://en.wikipedia.org/wiki/User:Tagishsimon/junk2 if this is for a language wiki.
Things: Looking just at the SPARQL, all of the dupes are on either two ?div1 or two ?div2. There are only two items that have neither a ?div1 nor a ?div2, http://www.wikidata.org/entity/Q1425141 and http://www.wikidata.org/entity/Q28125747, and we get just one row each for each of those. Your template example has |kml = true ... I'm not familiar with that as a wikidata list parameter & can't at a glance see it in the template documentation on wikidata nor en.wiki, so colour me puzzled & in need of a steer.
And/or point me at wherever you've been experimenting with the template. thx. --Tagishsimon (talk) 18:25, 30 April 2019 (UTC)
@Tagishsimon: The query is used on frwiki, the documentation for Wikidata list says that kml is used to put links to a map of all the points mentioned (a sentence is added just before the table with links to various maps with all the points). I don't know how it works, but we can remove it, because it's probably unrelated to the problem at hand.
The template for each row seems only to be used for formatting the row. The only dupes that trigger the error are the two items that have neither a ?div1 nor a ?div2: the generated template call then uses twice the parameter "string=". What I don't understand is why it's using the parameter "string=" and not "div1=" and "div2=": is there any reason to change the parameter name when there's no value? --NicoV (talk) 16:36, 1 May 2019 (UTC)
@NicoV: Okay; KML seems to be something fr.wiki has which en & wikidata do not, in that template. I've made a table at https://fr.wikipedia.org/wiki/Utilisateur%3ATagishsimon%2FBrouillon ... I'm not seeing any duplicates. Are you? --Tagishsimon (talk) 16:50, 1 May 2019 (UTC)
@Tagishsimon: Yes, I'm seeing 2 duplicate args in your table, for Q1425141 and Q28125747. Both have | string = | string =. --NicoV (talk) 17:07, 1 May 2019 (UTC)
@NicoV: Okay, I can see we have a single row entry for women's mosque (Q1425141), and that instead of having div1= and div2=, it has string= and string= ... and so I think I'm now on the same page as you as to what the problem is.
I am certain the problem does not arise out of the SPARQL. Per the above discussion, if we run it within WDQS we get the results we expect.
Two of the results have neither a ?div1 nor a ?div2 value, and so I must make the presumption that one of the fr implementation of wikidata list, or https://fr.wikipedia.org/wiki/Mod%C3%A8le:Wikidata_list/Monument or https://fr.wikipedia.org/w/index.php?title=Mod%C3%A8le:M (or some other module on which any of these has a dependency) is causing the issue by failing to handle the no ?div1 & no ?div2 case.
So the solution will not come from this page (unless there happens to be a template markup expert coincidently here). A patch for the issue on the China Mosques page would be to add P131 data to the two troublesome records, but clearly that's a sticking plaster for an underlying fault in - I assert - the fr wiki template & module sausage machine. Sorry I cannot be of any more help than this. --Tagishsimon (talk) 17:49, 1 May 2019 (UTC)
Thanks for your analysis Tagishsimon, but I think the problem is more with what is generated by ListeriaBot: instead of keeping the name "div1" and "div2" for the templates args, it replaces them both with "string" (your own test page User:Tagishsimon/junk shows the same problem in the generated wikicode: {{Wikidata list/Monument| qid = Q1425141 | pcol2 = | string = | string = | pcol5 = | pcol6 = | pcol7 = | pcol8 = }}). Maybe Magnus Manske can help and tell us if this behavior is normal... --NicoV (talk) 18:24, 1 May 2019 (UTC)

List of items without instance of (P31)

Hello, I'd like a query listing the items without instance of (P31) (or without pertinent instance of (P31)), but with "realm" / "emirate" / "kindgom" etc in their fr-en labels ? Thanks !Bouzinac (talk) 07:40, 1 May 2019 (UTC)

@Bouzinac:, just FYI, « nature » is the french label of the property. The english one is « instance of », and no non-french speaker will understand if you use « nature » (and maybe it’s not a good word for this notion) author  TomT0m / talk page 09:15, 1 May 2019 (UTC)
Ok, corrected :)
@Bouzinac: This might be a good direction of travel. You might, though, want to reject items that have a P279 ... you can copy the P31 filter to do that. Your search term needs to be amended in two places in the query; in this example it is searching for Émirats. You talk about the fr-en labels ... this query is looking at fr labels; for now, sadly, run another for the en sitation.
SELECT DISTINCT ?item ?itemLabel 
WHERE {
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:srsearch "Émirats".                   # search term
    ?title wikibase:apiOutput mwapi:title.
  }
  BIND(IRI(CONCAT(STR(wd:), ?title)) AS ?item)
  FILTER NOT EXISTS {?item wdt:P31 [] . }
  ?item rdfs:label ?itemLabel . filter(lang(?itemLabel)="fr")
  FILTER(CONTAINS(?itemLabel,"Émirats"))                        # search term
}
Try it!
--Tagishsimon (talk) 11:54, 1 May 2019 (UTC)

Same date of birth and death

How to obtain people who has death the same day of the month as they have born? It is the case os the poet Kavafis (Q216980) --Tsaorin (talk) 09:17, 1 May 2019 (UTC) @Tsoarin: 

select ?person ?personLabel 
with { 
  select ?person {
    ?person wdt:P31 wd:Q5 
            ; p:P569/psv:P569 ?birth 
            ; p:P570/psv:P570 ?death 
            .
    ?birth wikibase:timePrecision ?bprec 
           ; wikibase:timeValue ?bval 
           filter (?bprec >= 11)
          .
  ?death wikibase:timePrecision ?dprec 
         ; wikibase:timeValue ?dval 
          filter (?dprec >= 11)
        .
                                   
   filter (day(?bval) = day(?dval) && month(?bval) = month(?dval)) .
  } limit 4000
} as %dataset
where {
  include %dataset .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

This query works, although I limited the number of person founds because there is too many of them to find then in less than the time limit of queries. author  TomT0m / talk page 11:06, 1 May 2019 (UTC)

How to mix wdt:P31/wdt:P279* ?

Hello, I'd like in this query to mix/unionize 2 classes of historical country (Q3024240)+sovereign state (Q3624078), how to write this ? Thanks! Bouzinac (talk) 19:50, 1 May 2019 (UTC) 

select distinct ?country (group_concat(?NatureLabelFr;separator=" ,") as ?NatureLabelFr) ?countryLabel  ?creationDate ?dissolutionDate
with {
select ?country (coalesce(?countryLabelFr, ?countryLabelEn,  ?country) as ?countryLabel) ?creationDate ?dissolutionDate{
?country wdt:P31/wdt:P279* wd:Q3624078 # sovereign states, I don’t know if it’s the right item
?country wdt:P31/wdt:P279* wd:Q3024240 # état historique
MINUS { ?country (wdt:P31/wdt:P279*) wd:Q1790360. } #empires coloniaux
MINUS { ?country (wdt:P31/wdt:P279*) wd:Q1371288. } #états vassals
optional { ?country rdfs:label ?countryLabelFr filter(lang(?countryLabelFr)= "fr")} .
optional { ?country rdfs:label ?countryLabelEn filter(lang(?countryLabelEn)= "en")}
optional { ?country wdt:P571 ?creationDate }
optional { ?country wdt:P576 ?dissolutionDate }
} order by ?countryLabel
} as %datas
where {
include %datas .
optional{
?country wdt:P31/rdfs:label ?NatureLabelFr filter(lang(?NatureLabelFr)= "fr") .
}
} group by ?country ?countryLabel  ?creationDate ?dissolutionDate
Try it!
@Bouzinac: Try this modified query
select distinct ?country (group_concat(?NatureLabelFr;separator=" ,") as ?NatureLabelFr) ?countryLabel  ?creationDate ?dissolutionDate
with {
select ?country (coalesce(?countryLabelFr, ?countryLabelEn,  ?country) as ?countryLabel) ?creationDate ?dissolutionDate{
VALUES ?what { 
               wd:Q3624078 # sovereign states, I don’t know if it’s the right item
               wd:Q3024240 # états vassals
             }
?country wdt:P31/wdt:P279* ?what . 
MINUS { ?country (wdt:P31/wdt:P279*) wd:Q1790360. } #empires coloniaux
MINUS { ?country (wdt:P31/wdt:P279*) wd:Q1371288. } #états vassals
optional { ?country rdfs:label ?countryLabelFr filter(lang(?countryLabelFr)= "fr")} .
optional { ?country rdfs:label ?countryLabelEn filter(lang(?countryLabelEn)= "en")}
optional { ?country wdt:P571 ?creationDate }
optional { ?country wdt:P576 ?dissolutionDate }
} order by ?countryLabel
} as %datas
where {
include %datas .
optional{
?country wdt:P31/rdfs:label ?NatureLabelFr filter(lang(?NatureLabelFr)= "fr") .
}
} group by ?country ?countryLabel  ?creationDate ?dissolutionDate
Try it!
--Larske (talk) 11:25, 2 May 2019 (UTC)

Add Wikipedia Title to Query result

Hi again, I managed to create a query of female rock musicians grouped with their respective genres, have a look: 

#Female rock musicians
SELECT  ?person 
        ?personLabel 
        ?page_titleDE # breaks
 (GROUP_CONCAT(DISTINCT(?genreLabel); separator=", ") as ?genres)

 WHERE {
  ?genre wdt:P31 wd:Q188451.
  ?genre wdt:P279* wd:Q11399.
  
  ?person wdt:P31 wd:Q5;
    wdt:P21 wd:Q6581072;
    wdt:P106 wd:Q639669;
    wdt:P136 ?genre.
?article schema:about ?person ; schema:isPartOf <https://de.wikipedia.org/> ;  schema:name ?page_titleDE .
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en".
                          ?person rdfs:label ?personLabel.
                          ?genre  rdfs:label ?genreLabel.}
}
GROUP BY ?person ?personLabel ?page_titleDE 
LIMIT 1000
Try it!

As you can see, I try to add the de.wp article titles in the list, with the goal to have a listeria page with red and blue links (if I'd managed to concatenate with [[…]], this would be beautiful).

However, I'm failing already with the additional column, which seems incompatible with the GROUP_CONCAT foo. I tried different orders with no success. Any idea? Thanks … --Elya (talk) 20:42, 2 May 2019 (UTC)

@Elya: Classical one, I corrected the « group by ». As the title is the same for any person, you had to add it to the « group by », same as ?person and ?personLabel (the hint is, if you don’t use a variable in any group operation such as group_concat, it should probably be listed after the « group by »). It’s a performance to have been able to write this query without knowing that. You should be able to find out how to use the « concat » function to achieve what you want. author  TomT0m / talk page 20:51, 2 May 2019 (UTC)
ahhh … thanks! A bit tired, I could have figured that out by myself. But thanks for the compliment, fiddled about with this the whole evening. --Elya (talk) 21:06, 2 May 2019 (UTC)

Extracting a list of photographers

Hello! I need a list of living people who are photographers or environmentalists‎.

The following properties in the query are needed:

Can someone help with that? Thanks in advance! --Acedtalk 14:42, 3 May 2019 (UTC)

Combining Wikidata query and Wikipedia category tree search isn't simple. Better only search categories using PetScan. Matěj Suchánek (talk) 08:25, 4 May 2019 (UTC)
@Matěj Suchánek: Thanks! --Acedtalk 11:45, 5 May 2019 (UTC)

Querying for all external identifiers for an item

Hello!

I am trying to query wikidata to retrieve all of the external identifiers for a wikidata item. For example, I would like all of the external identifiers for Ferdinand Hodler (wd:Q214564). I am asking for all properties and their associated values of Ferdinand Hodler, and then I am specifying that I only want properties that are of the property type "External ID". When I run this query, the results read "No matching records found." 

SELECT ?property ?value ?propertyLabel ?propertyDescription WHERE {
  wd:Q214564 ?property ?value .
  ?property wikibase:propertyType wikibase:ExternalId .
	SERVICE wikibase:label {
		bd:serviceParam wikibase:language "en" .
	}          
} ORDER BY ?propertyLabel
Try it!

Thank you kindly for any assistance you may be able to provide - S.ann.adams 16:18, 4 May 2019 (EST) 

SELECT ?property ?value ?propertyLabel ?propertyDescription WHERE {
  ?property wikibase:propertyType wikibase:ExternalId; wikibase:directClaim ?wdt .
  wd:Q214564 ?wdt ?value .
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }          
} ORDER BY ?propertyLabel
Try it!
 —MisterSynergy (talk) 20:25, 4 May 2019 (UTC)

Execution methods

Hello, I am surprised not to see execution by firing squad (Q216169) in this query, was I mistaken trying to list "all" methods of killing ? Bouzinac (talk) 07:34, 5 May 2019 (UTC) 

SELECT ?class ?classLabel (SAMPLE(?item) AS ?sample) WHERE {
?class wdt:P279+ wd:Q15169167.
OPTIONAL { ?item wdt:P31 ?class. }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?class ?classLabel
Try it!
Replace + with *, this will include Q15169167 in ?class and make it listed. Matěj Suchánek (talk) 12:17, 6 May 2019 (UTC)
Hm, it doesn't. Perhaps I just don't understand what's going on. Matěj Suchánek (talk) 12:21, 6 May 2019 (UTC)

Query for replacing references on statements?

How do I replace all references in statements of items to European Environment Agency (Q632988) (an institution) with Nationally designated areas inventory (Q1116062) (the institution catalog)?

I've already built a query: https://w.wiki/3eV

But I'm a bit lost now...--DarwIn (talk) 18:00, 5 May 2019 (UTC)

An somewhat more useful version of the query is here: [1]. A direct replacement works with bot code only. Are you sure that you added all of the 1002 references by yourself? —MisterSynergy (talk) 18:34, 5 May 2019 (UTC)
@MisterSynergy: Thanks! :D You're just great. Almost all must have been added by me. The others I'll let as they are, unless there's something obvious there that should be replaced as well.--DarwIn (talk) 19:02, 5 May 2019 (UTC)
I figured out that there were 8 references on instance of (P31) claims by another user. I left those ones untouched (see previous query), and updated all your references to the catalog value. Sample diff: Special:Diff/934173873. —MisterSynergy (talk) 20:07, 5 May 2019 (UTC)
@MisterSynergy: Thanks! Can you explain how you have done it, so that I may try it in the future, if a similar situation appears?--DarwIn (talk) 20:17, 5 May 2019 (UTC)
This code, executed with the PAWS tool. —MisterSynergy (talk) 20:38, 5 May 2019 (UTC)
@MisterSynergy: Wonderful, thank you very much for all your help, and the patience to explain it.--DarwIn (talk) 20:44, 5 May 2019 (UTC)

Statistics

Among all human (Q5) how many do have the property place of burial (P119), and how many have signature (P109) and how many have doctoral advisor (P184). Breg Pmt (talk) 16:28, 6 May 2019 (UTC)

—MisterSynergy (talk) 16:51, 6 May 2019 (UTC)

Thank you very much this one w.wiki/3gt didnt made my PC happy. Breg Pmt (talk) 16:59, 6 May 2019 (UTC)

Author and author name string

I am trying to get a query that gives me duplicates of author (P50) and author name string (P2093) at a given position of the author list, but limited to those duplicates where the P2093-statement P50-statement has a P1932-statement. I have the first part, but I could not add the italic part. 

select ?item ?interval where {
  ?item p:P2093/pq:P1545 ?interval ;
        p:P50/pq:P1545 ?interval .
  }
  LIMIT 1000
Try it!

Can somebody help? 92.74.30.62 09:47, 27 April 2019 (UTC)

First, there seem only to be 17 items where there is a object named as (P1932) qualifier for author name string (P2093)
select ?item ?interval ?namestring where
  {
    ?item p:P2093 [pq:P1545 ?interval ; ps:P2093 ?namestring; pq:P1932 [] ] .
  }
Try it!
Putting those very few through the sausage machine gets us no hits, if we're also trying to match the namestrings - which would seem to be a good thing. We can match on the P2093 value:
select ?item ?interval with { 
  select ?item ?interval ?namestring where
  {
    ?item p:P2093 [pq:P1545 ?interval ; ps:P2093 ?namestring; pq:P1932 [] ] .
  } } as %i
where
{
  include %i
  ?item p:P50 [pq:P1545 ?interval ; ps:P50 ?name ] . 
  ?name rdfs:label ?namestring. 
}
Try it!
or we can match on the P1932 value. Still no hits.
select ?item ?interval with { 
  select ?item ?interval ?namestring where
  {
    ?item p:P2093 [pq:P1545 ?interval ; pq:P1932 ?namestring ] .
  } } as %i
where
{
  include %i
  ?item p:P50 [pq:P1545 ?interval ; ps:P50 ?name ] . 
  ?name rdfs:label ?namestring. 
}
Try it!
Does that help? --Tagishsimon (talk) 11:23, 27 April 2019 (UTC)
And then, rethinking: if your purpose is to spot duplicates by position only, irrespective of the namesting - and I can see why you would do that, then ... still no hits:
select ?item ?interval with { 
  select ?item ?interval  where
  {
    ?item p:P2093 [pq:P1545 ?interval ; pq:P1932 [] ] .
  } } as %i
where
{
  include %i
  ?item p:P50 [pq:P1545 ?interval ] . 
}
Try it!
--Tagishsimon (talk) 11:28, 27 April 2019 (UTC)
Thank you for the help. I have to admit that I made a silly mistake in the request. Not the author name string (P2093)-statement shall have the object named as (P1932)-statement, but the author (P50)-statement (I have corrected it above in the request). I think was able to adapt your queries to fit it to my intented request, however, it times out:
select ?item ?interval with { 
  select ?item ?interval ?namestring where
  {
    ?item p:P50 [pq:P1545 ?interval ; ps:P50 ?namestring; pq:P1932 [] ] .
  } LIMIT 50000 } as %i
where
{
  include %i
  ?item p:P2093 [pq:P1545 ?interval ; ps:P2093 ?name ] . 
  ?name rdfs:label ?namestring. 
}
Try it!

Can it be optimized? 92.74.30.62 11:52, 27 April 2019 (UTC)

I'm not sure how much useful optimisation can be done. I've moved your limit statement in the above query to limit the set of results from the first subquery, but I can't get it to work with a value much higher than 50k, nor with e.g. OFFSET 50000 LIMIT 50000 (and as I've amended it, we're back to 0 results). Nor have I managed to get a count of P50s with pr:P1932s, which would at least tell us how many increments of the first query we need to go through to exhaust the set of possible results. (I should stress, I'm not a SPARQL expert, more a dangerous person with a little knowledge; others on this page have more of a clue than me.) I note - and presumbly this is deliberate on your part - that you elected to amend the query which is doing a namestring match, rather than the one doing only an interval number match. We start to get some results if we go back to the 'on interval number only' version, albeit OFFSET 50000 LIMIT 50000 (to let us look at the next set of 50k candidates) once again takes is to a timeout . --Tagishsimon (talk) 12:23, 27 April 2019 (UTC)
select ?item ?interval with { 
  select ?item ?interval  where
  {
    ?item p:P50 [pq:P1545 ?interval ; pq:P1932 [] ] .
  } LIMIT 50000 } as %i
where
{
  include %i
  ?item p:P2093 [pq:P1545 ?interval] . 
}
Try it!
No, I do not want a namestring match. If I included it in my query, then because I am quite a sqarql noob ;) Let's give an example: In Elementary gates for quantum computation (Q21709020), I would like that the series ordinal numbers "2" and "3" statements are found by the query (and some of the others also, but those two illustrate that it shall not matter if the namestring is the same as "author" or not). 92.74.30.62 13:41, 27 April 2019 (UTC)
Okay, then we have that in the bottommost query, albeit it would not find the 2 & 3 in Q21709020 because the P50s there for 2 & 3 lack a P1932. So now - presuming you do still have a dependency on P1932, we 'just' need to figure out how to get it to cover all papers rather than the first few tens of thousands. And if you don;t want P1932, then your original query is a perfectly good solution, and we could all have stayed in bed :) --Tagishsimon (talk) 14:08, 27 April 2019 (UTC)
Yeah right, 2 & 3 would not be found. This was a bad example. Ok, I see that the bottommost query gives what I want. I want to use it as a complex constraint, so maybe the limit is there not relevant. Thank you! 92.74.30.62 14:42, 27 April 2019 (UTC)
Okay. If we're not concerned with getting all matches, then we can probably go back to a simpler form of the query, such as below; and it might not need ?interval in the select, for all I know. The VALUES statement is there just for test purposes - remove before use as a constraint:
select ?item ?interval  where
{
  values ?item {wd:Q43768915}                     # remove before use as a complex contraint!
  ?item p:P50 [pq:P1545 ?interval ; pq:P1932 [] ] .
  ?item p:P2093 [pq:P1545 ?interval] . 
}
Try it!
--Tagishsimon (talk) 14:53, 27 April 2019 (UTC)

@Tagishsimon: There seems to be an error in the query, Wikidata:Database reports/Complex constraint violations/P2093 was not filled, and running the query gives a server error. 129.13.72.197 13:42, 29 April 2019 (UTC)

Noted. I'm not familiar with complex constraints ... I /think/ they might be used in two ways: 1) to warn the user on the item when a constraint is violated ... that would (probably) require a search against only the singe value. And then 2) use within the report which you pointed to, which is giving us an error. That might be because we're back to getting a timeout of the sort we were seeing in some of the earlier versions in this thread. I'll return to this problem sometime in the next 24 hours, but by all means ping me again if I don't get around to it. thx --Tagishsimon (talk) 13:51, 29 April 2019 (UTC)
@Tagishsimon: I am not sure if these complex constraints trigger edit warnings, but they definitely are used for the report pages. 129.13.72.197 12:30, 30 April 2019 (UTC)
I did a little bit of reading last night - https://www.wikidata.org/wiki/Template:Complex_constraint - but I've not got around to testing whether they generate trigger warnings. Still more later ... have some IRL stuff to do. --Tagishsimon (talk) 12:36, 30 April 2019 (UTC)

@Tagishsimon: Can you maybe have a look again at this issue? 129.13.72.197 08:37, 8 May 2019 (UTC)

Question about BLPs

I don't know whether this is the right place for this question. I'm trying to find out how many biographies of living persons exist on the English Wikipedia, and what kind of data we have on them. In particular, I'm looking for the gender breakdown. I'd also like to know when they were created; average length; and whether they've been nominated for deletion. Can anyone advise? Sarah (talk) 21:38, 4 May 2019 (UTC)

@RexxS: do you have any idea where I could find this, or where best to ask about it? Sarah (talk) 01:06, 5 May 2019 (UTC)

SlimVirgin Oooh, interesting question! The first part is relatively straightforward - Category:Living people on enwiki has 906,720 members, of which 697,402 are identified as male by Wikidata, 205,117 as female. 1220 have no gender statement (often where the article is about a group), 517 have no Wikidata item yet, and (by inference) 2464 have some other gender statement than male/female. (I've used Category:LP to define if someone is a living person or not - Wikidata doesn't explicitly state this, so you would have to infer it from birth/death dates, and it's a lot easier to use the Wikipedia category where there's a clear and unambiguous statement.)
The queries to do this are from Petscan - male LP; female LP; no-statement LP; no-Wikidata LP. These take ages to run, caveat emptor.
For the metadata, go back to Petscan, set the last tab to "format: TSV" and run the query. Eventually(!) you will get a file which includes pageID and length. Length is exactly what it sounds like, pageID is probably an approximation to age, as it tends to go up in sequence as new pages are created. I don't know how reliable it is for pages which have been moved, though, or deleted/recreated. Merges and articles converted from redirects will also confuse the issue. But it's a start. You could download the lists for both men & women, take a deep breath, hope Excel doesn't crash, and see what the distributions look like for each group. (I'd love to see the analysis)
For the third part - have they been nominated for deletion? - I don't think you can do this easily with any tools which are available. You'll never be able to trace CSD/PROD nominations without analysing the full text histories from a dump. AFD nominations are a bit easier - you can use "does this page have an AFD subpage with exactly the same name" as a proxy - but I think you'd have to write something to do this analysis offline, using the article lists from Petscan, rather than use an existing tool. Andrew Gray (talk) 10:47, 5 May 2019 (UTC)
Update: annoyingly the second part does not quite work as suggested, since the Petscan TSVs default to returning Wikidata items not Wikipedia pages.
You can get the useful data (pageID/size) in a downloadable format for enwiki pages by:
a) selecting "TSV" in the first line of the "output" tab
b) selecting "From manual list" in the bottom ('use wiki') section of the "other sources" tab.
c) hit "do it!" and go make a cup of tea
This will (after much thought) give you a file looking something like:
     number   title           pageid      namespace     length     touched
     1        Alain_Connes    340                       7555       20190504023855
     ...
     697402   Chen_Fahu       60665469                  3226       20190505083445
w:Alain Connes was created before Sept 2001, hence it has a very low pageID; the length pretty much matches bytesize if I copy/paste the wikitext, so that all checks out. Conversely, w:Chen_Fahu was created a few hours ago, and length matches.
Hope this helps! Let me know if you have trouble getting the TSVs - I can put them up online somewhere since I've extracted them now. Andrew Gray (talk) 11:28, 5 May 2019 (UTC)
And a quick answer from looking at the files - average length for men is 6611, for women 6689. For the 70,000 men and 20,000 women with highest pageIDs, (ie approximately the most recent ~10%), it's 4151 for men and 4955 for women. So overall they're reasonably similar, but for recently created pages, women's articles tend to be a bit more substantial. For the oldest 10%, the gap is the other way around - men are 14044 on average, women 13159. Andrew Gray (talk) 11:50, 5 May 2019 (UTC)
@SlimVirgin, Andrew Gray: Nice work , Andrew, and it really is an interesting question. We could probably use some means of automating the steps you used to create a list with given criterion with Petscan, then doing analysis on it via WikiDataQueryService. The "whether they've been nominated for deletion" question is a tough one. Do we know if the templates like {Old VfD}}, {Oldafdfull}, {Old XfD mult} etc. are likely to have been applied consistently? The presence of one of those on the talk page might be a reasonable indication, and PetScan can handle intersection of categories and templates in a single run. --RexxS (talk) 12:45, 5 May 2019 (UTC)
So I think doing subsequent analysis on the list with WDQS is probably challenging - AIUI you can't import a big list of IDs into WDQS, you have to generate it with a query. There is some functionality within WDQS to identify articles based on WP categories, but I think it has trouble with very large ones (it was telling me there were only a few thousand in Cat:LP). What you can do, though, is write your analysis query (eg "women who are politicians, men who are French"), drop that into the petscan box, and run to generate the overall numbers.
On the nominated for deletion question, I tried looking at AFD subpages. This is going to be very inexact, but... 20453 male BLPs (2.9%) and 7377 female BLPs (3.6%) have page titles which exactly match "Wikipedia:Articles for deletion/$PAGENAME" as of the most recent dump. (It ignores any suffixes like (nth nomination) and will not report a match for those, but I think mostly the first nomination does not have a suffix). "Wikipedia:Votes for deletion//$PAGENAME" matches 798 men (0.11%), 222 women (0.11%) - I haven't added this to the total above as I'm not sure if these are redirects (and so we'd get some double-counting). This does not take account of proposed and declined PRODs or CSDs, nor does it take account of the fact that those AFDs may be of a different article which has the same title as the current one (false positives), or of the fact that those articles may have been AFDed under an old title (and thus are false negatives). An article which was a BLP at the time, but the subject has now died, will not be identified. But with all those caveats, it does tend to suggest that women do indeed get nominated for deletion at a disproportionately higher rate than men. Andrew Gray (talk) 13:23, 5 May 2019 (UTC)
On quick reflection, clarifying that last conclusion - it suggests a higher proportion of articles which are nominated for deletion but survive are women; we don't know anything about the population which is actually deleted. Andrew Gray (talk) 13:42, 5 May 2019 (UTC)
Summary table
BLP gender Number of articles Share of M/F
gendered articles		 Average length
(bytes)		 # matching AFD subpage
Male 697402 77.3% 6611 20453 (2.93%)
Female 205117 22.7% 6689 7377 (3.60%)
Male
(since 1/5/18)		 39557 71.4% 3876 775 (1.96%)
Female
(since 1/5/18)		 15845 28.6% 4946 334 (2.11%)
Male
(before 1/5/18)		 657845 77.7% 6776 19678 (2.99%)
Female
(before 1/5/18)		 189272 22.3% 6836 7043 (3.72%)

I've done some very quick calculations for a "recent" and "old" subset as well (assuming that PageID is strictly sequential; the divider is pageID #5729000. which would have been created on 1/5/18). This suggests that for articles created in the past year, deletion nominations are about equally common for men and women (perhaps very slightly more common for women), but that women's articles are also substantially longer (nb this is byte count not prose size). For articles created more than a year ago, there are more pronounced disparities in nomination for deletion, with women's articles perhaps 25% more likely to have an associated AFD page, but the size disparities are much less marked. Andrew Gray (talk) 14:31, 5 May 2019 (UTC)

@Andrew Gray, RexxS: thank you so much for this. The summary table is extremely helpful. Andrew, do you mind if I copy the table elsewhere, attributed to you? Also, I'm sorry if this is a stupid question, but how does Wikidata identify whether someone is male or female? Sarah (talk) 22:06, 5 May 2019 (UTC)
@SlimVirgin: by all means! I might also try and blog it at some point (since it seems worth documenting the process) but I probably won't get around to that for a while :-)
Gender is identified on Wikidata with a single property, sex or gender (P21), which takes one of various different values; see eg this example for how it sits on an item (item = entity; a person, in this case). Conceptually, this is much the same way as we would identify "person has occupation: musician" or "person has nationality: French". Andrew Gray (talk) 22:26, 5 May 2019 (UTC)
@Andrew Gray: a blog post would be great, because then we'd have a source too. I've always thought that the BLP WikiProject banner should include sex, which would make it easy to get the data. Can you clarify again what you said about deletion, even if the estimates are rough? Women are how much more likely to be nominated? Did that change in 2018? And are also more likely to be kept? Sarah (talk) 22:34, 5 May 2019 (UTC)
@SlimVirgin: you've got me wondering now if we could get the banners to draw gender from WD... but that's a different problem :)
At the moment, I would say that overall, BLPs of women appear to have a ~25% higher chance than average of having previously been nominated at (and passing) AFD. With the most recent group of articles, however, the odds of having previously been through an AFD are much closer to that of men.
I don't think from this data we can say anything for sure about the rate of articles being kept/deleted, though - we have no way to identify deleted BLPs so can't say if those are disproportionately female or not.
Using 2018 was an arbitrary cut to look at "recent" articles rather than a specific changeover point. I am of to bed now but I'll try doing some checks tomorrow with say six-month bands and see if there is any point where we see a noticeable shift. Andrew Gray (talk) 22:54, 5 May 2019 (UTC)
@Andrew Gray: thank you, this has been extremely helpful. Sarah (talk) 23:16, 5 May 2019 (UTC)
@SlimVirgin: Post and graphs to follow, but I went back to 2009 in six-month chunks and some conclusions:
  • The relative share of female vs male BLPs was ~20% in 2009. They continued to be created in the same proportions until ~2012, when the rate of creation of female BLPs kicked up a gear, and since 2013, 25-30% of new BLPs have been female (almost hitting 33% in late 2017!). This has driven the overall share steadily and continually upwards, now at 22.7% (as noted above) - so it's increasing by something like half a percent per year.
  • Female BLPs created at any time since 2009 are currently slightly longer on average than male ones of similar age; the gap may be widening over the past year but it's not quite certain yet. Average difference is about 500 bytes or a little under 10% (which is not trivial!)
  • Female BLPs created through 2009-16, in general, have a significantly higher chance of having been to and survived AFD than male BLPs of an equivalent age. It is most marked for the oldest biographies (pre-2012). For 2017 and onwards, the two groups are much closer. (There is also a substantial drop in both groups in this period, suggesting either it takes a while for articles to get sent to AFD - or, more optimistically, that thanks to drafts & AFC we're getting better at making sure they're good quality before hitting mainspace)
Definitely some questions we can't answer, but there's still a lot to chew on here. Andrew Gray (talk) 14:59, 6 May 2019 (UTC)
@Andrew Gray:, this is great stuff. I look forward to any graphs. I'd be particularly interested in seeing a breakdown of female BLPs having (a) significantly higher chance of having been to AfD; and (b) significantly higher chance of having survived AfD, and (c) what is (roughly) "significantly higher". It's interesting that it has levelled out. Sarah (talk) 17:19, 6 May 2019 (UTC)
@SlimVirgin: Posted!. The graphs are on commons, and feel free to quote/extract as needed.
We can't say for sure if female BLPs survive AFDs or are nominated more often than male BLPs - we don't know the far side of the equation, the numbers of deleted articles and their gender breakdown. But we can confidently say, for articles created before 2016, that there is some kind of gender-specific difference in their involvement with the deletion process. Systematically more female BLPs than male BLPs appear to have previously experienced a deletion debate. Exact numbers vary but the rate is something like perhaps 15-25% more chance of having experienced a deletion debate than an equivalent male BLP of similar age. However, for articles created in 2017 and onwards, the gender differences mostly disappear, and fewer of these articles overall have been through and survived AFD than the long-run average would suggest. Andrew Gray (talk) 20:49, 6 May 2019 (UTC)
@Andrew Gray: thank you so much for this! It's incredibly helpful for testing our assumptions. I've started a discussion about it at Women in red, and I've pinged you and @RexxS: there. Sarah (talk) 05:19, 7 May 2019 (UTC)

Query to extract several values for the same property

Some properties have several values or even several statements. How can we extract all the values with a query ? Thank you. Snipre (talk) 11:13, 8 May 2019 (UTC)

Items with Commons Category but no image

I am looking for items with a Commons Wikilink but without a image (P18) statement. --GPSLeo (talk) 19:26, 7 May 2019 (UTC)

Here you are but it needs some work to be useful:
SELECT ?item WHERE {
  { ?item wdt:P373 [] } UNION { ?item ^schema:about/schema:isPartOf <https://commons.wikimedia.org/> } .
  MINUS { ?item wdt:P18 [] } .
  MINUS { ?item wdt:P31 wd:Q4167836 } .
} LIMIT 2000
Try it!
Matěj Suchánek (talk) 07:55, 9 May 2019 (UTC)

Actual politicians in Italian parliament

I'd like to have two lists of all politician in Italian parliament in order to add more data on them, one for position held (P39) set to member of the Chamber of Deputies of the Italian Republic (Q18558478) and one with same property set to member of the Italian Senate (Q13653224). These statements have some qualifiers in order to define that they have been elected in latest election: some have just parliamentary term (P2937) set to 18th Legislature of Italy (Q48799610), some have just start time (P580) set to a date since 04-03-2019, some both. If somebody have been substitute with somebody else, please create a third list of just these people, so that I can check if the first two lists have respectively 630 and 315 items. Thank you very much! --★ → Airon 90 07:55, 8 May 2019 (UTC)

@Airon90: Here some results. It's not perfekt because I am beginner in SPARQL but it's a start point.
#politicians in italian parlement
SELECT ?item ?itemLabel ?date
WHERE 
{
  ?item wdt:P39 wd:Q18558478;
        p:P39 [ps:P39 wd:Q18558478;pq:P580 ?date].
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC (?date)
Try it! 
#politicians in italian senat
SELECT ?item ?itemLabel ?date
WHERE 
{
  ?item wdt:P39 wd:Q13653224;
        p:P39 [ps:P39 wd:Q18558478;pq:P580 ?date].
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC (?date)
Try it!

--2le2im-bdc (talk) 06:07, 9 May 2019 (UTC)

Thank you but these query are not useful to me. I want all politicians in parliament actually, not also those in the past. I saw in results some people which have been not reelected. --★ → Airon 90 17:11, 9 May 2019 (UTC)

Display qualifier P1545 and Wikisource

Hello,

I need some help to display the series ordinal (P1545) of the poems from this anthology. 

#Liste des poèmes compris dans l'anthologie de Stéphane Hessel
#Sont affichés l'ordre d'apparition dans la série.
SELECT ?item ?itemLabel ?ordre
WHERE 
{ 
  wd:Q63372286 wdt:P527 ?item.
  OPTIONAL {wd:Q63372286 p:P527 ?statement.           
    ?statement pq:P1545 ?ordre.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

If it possible I would like also to display the Wikisource (in any language) if it's existing.

Thanks in advance --2le2im-bdc (talk) 19:46, 8 May 2019 (UTC) 

SELECT ?item ?itemLabel ?ordre ?wikisource
WHERE
{
  wd:Q63372286 p:P527 ?statement .
  ?statement ps:P527 ?item .
  OPTIONAL { ?statement pq:P1545 ?ordre } .
  OPTIONAL {
    ?item ^schema:about ?wikisource .
    ?wikisource schema:isPartOf/wikibase:wikiGroup "wikisource" .
  } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } .
} ORDER BY DESC(BOUND(?ordre)) xsd:integer(?ordre)
Try it!
Matěj Suchánek (talk) 07:51, 9 May 2019 (UTC)
Thanks a lot @Matěj Suchánek:!--2le2im-bdc (talk) 11:34, 9 May 2019 (UTC)

Display geoshapes with color scale based on a calculated percentage

The following query returns (when there is no time out) the percentage of geolocated churches by department in France. With geoshape (P3896), I'd like to display a map with a color scale based on this percentage. I tried to adapt this query but it is definitely above my competencies. Could someone help me please? 

SELECT ?dep ?INSEE ?depLabel ?itemCount(ROUND((IF(?_withCoord > ?itemCount, ?itemCount, ?_withCoord) / ?itemCount) * 100 * 100) / 100 AS ?withCoord) 
WHERE {
  {
    SELECT ?dep (COUNT(DISTINCT ?item) AS ?itemCount) (COUNT(DISTINCT ?coord) AS ?_withCoord) WHERE {
      hint:Query hint:optimizer "None".
      ?item wdt:P131+ wd:Q142;
             wdt:P31/wdt:P279* wd:Q16970;
             wdt:P131 ?commune .
      ?commune p:P131 ?stmt .
      ?stmt ps:P131 ?dep .
      MINUS { ?stmt pq:P582 [] } .
      ?dep wdt:P2586 [] .
      OPTIONAL { ?item wdt:P625 ?coord } .
    } GROUP BY ?dep
  } .
  ?dep wdt:P2586 ?INSEE .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr" } .
}
Try it!

Thanks. Ayack (talk) 12:48, 7 May 2019 (UTC)

Hopefully you will be satisfied with this:
#defaultView:Map{"hide": ["?rgb", "?shape"]}
SELECT ?dep ?INSEE ?depLabel ?itemCount ?withCoord ?shape ?rgb
WITH {
  SELECT DISTINCT ?dep ?item WHERE {
    hint:Query hint:optimizer "None".
    ?item wdt:P131+ wd:Q142;
          wdt:P31/wdt:P279* wd:Q16970;
          wdt:P131 ?commune .
    ?commune p:P131 ?stmt .
    ?stmt ps:P131 ?dep .
    MINUS { ?stmt pq:P582 [] } .
    ?dep wdt:P2586 [] .
  }
} AS %churches WITH {
  SELECT ?dep (COUNT(DISTINCT ?item) AS ?itemCount) {
    INCLUDE %churches .
  } GROUP BY ?dep
} AS %counts WITH {
  SELECT ?dep (COUNT(DISTINCT ?item) AS ?_withCoord) {
    INCLUDE %churches .
    ?item wdt:P625 [] .
  } GROUP BY ?dep
} AS %coords WHERE {
  INCLUDE %counts .
  INCLUDE %coords .
  ?dep wdt:P2586 ?INSEE .
  OPTIONAL { ?dep wdt:P3896 ?shape } .
  BIND( ROUND((?_withCoord / ?itemCount) * 100 * 100) / 100 AS ?withCoord ) .
  BIND( IF( ?withCoord > 50, IF( ?withCoord > 75, '0000FF', '00FF00' ), 'FF0000' ) AS ?rgb ) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr" } .
}
Try it!
Matěj Suchánek (talk) 08:25, 9 May 2019 (UTC)
It's perfect. Thanks a lot Matěj Suchánek! Ayack (talk) 09:23, 10 May 2019 (UTC)

Requesting for the list of Indian actors

Hello, can anyone help me to create a query for occupation is actor born between 1960 and 1970 who speaks Hindi language? --Gopala Krishna A (talk) 17:33, 10 May 2019 (UTC)

Hello @Gopala Krishna A:, I am not the best in SPARQL but you will find here a first result.
#people with occupation actor born between 1960 and 1970 who are born oder death in India
SELECT DISTINCT ?item ?itemLabel ?born
WHERE 
{
  ?item wdt:P106 wd:Q33999;#occupation = actor
        wdt:P569 ?born;#
  FILTER (?born >= "1960-01-01T00:00:00Z"^^xsd:dateTime) .#after 1960
  FILTER (?born <= "1970-12-31T00:00:00Z"^^xsd:dateTime) .#befor 31.12.1970
  ?item wdt:P19/wdt:P131*|wdt:P20/wdt:P131* wd:Q668.#place of birth or death is India
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?born
Try it!
--2le2im-bdc (talk) 18:49, 10 May 2019 (UTC)
Thank you @2le2im-bdc:. Really appreciate your quick reply. --Gopala Krishna A (talk) 06:38, 11 May 2019 (UTC)

List of items with a column "province"

Good day, I'm trying to build a query to have the list of Q3423162, but I would like in the result table to have a column "province". By using P131, I get the most local administrative level (city or municipality, etc.), how do I get it to display the higher level (i.e. Q2879)? Thanks, Amqui (talk) 14:47, 10 May 2019 (UTC)

One possibility: ?item wdt:P131+ ?province . ?province wdt:P31 wd:Q2879 (note that queries with */+ modifiers tend to time out). Matěj Suchánek (talk) 16:33, 10 May 2019 (UTC)
Thank you, it worked. Amqui (talk) 16:56, 11 May 2019 (UTC)

Storing results from brackets on Wikidata

Probably a really difficult task: There are a lot ot results in brackets (for instance: de:Badminton-Europameisterschaft 1968/Herreneinzel). Is it somehow possible, to import these results into Wikidata? One way would be, to add the ranking to Q799298 (i.e. 1. 2. 3. 5. 9. 17.), but there will be still missing the results of the matches. A second way would be, to add the respective rounds to Wikidata in the form as part of Q799298: First round of Q799298: Elo Hansen vs. Danče Pohar 15:2, 15:2 etc., Second round, etc. The best way would be to add the whole bracket. More complicated it will be on tournaments like de:All England Super Series 2013/Herrendoppel. There are more brackets on one page and 4 participants in every match. The overall goal is, to store the data here and export it into every other language. Florentyna (talk) 15:38, 12 May 2019 (UTC)

Adding missing BWF-ID from an external database

There are a lot of badminton players (wdt:P106 wd:Q13141064, around 3000 players) having already added the BWF.tournamentsoftware.com player ID P2729, but with a missing BWFbadminton.com player ID P3620, see Wikidata:WikiProject Badminton/BWF. This ID stands right beside the name of the BWF.tournamentsoftware.com player ID P2729 on bwf.tournamentsoftware.com. Is it somehow possible to extract this number from the page and add it to Wikidata? Florentyna (talk) 15:23, 12 May 2019 (UTC)

Solved. Thanks a lot. Florentyna (talk) 14:30, 13 May 2019 (UTC)

Non-human citizens

I am trying to get a list of all non-humans having some citizenship. I can do this for any particular country but query for all countries timeout: 

SELECT ?stateLabel (COUNT(?item) AS ?cnt) WHERE {
  ?item wdt:P27 ?state.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  
MINUS { ?item wdt:P31 wd:Q5. }
MINUS { ?item wdt:P31/wdt:P279* wd:Q95074.}
MINUS { ?item wdt:P31/wdt:P279* wd:Q8436.}
}
GROUP BY ?state ?stateLabel
Try it!

Is there a way to optimize the query? --Infovarius (talk) 16:33, 13 May 2019 (UTC)

Soccer players

I would need a query with soccer players that played at least once in 1964–65 Fußball-Bundesliga (Q704091). I know this is not directly possible with the currenty data grain size. But an approximate workaround should be possible: Find all clubs that played in the 1964–65 Fußball-Bundesliga (Q704091), and find all players that played in Bundesliga (Q82595) and were a member of those clubs during the years 1964 and 1965. 92.75.203.164 17:53, 5 May 2019 (UTC)

Anyone please? 129.13.72.197 07:52, 15 May 2019 (UTC)

Help needed with query reaching timeout limit

Hello, I have a query for detecting items with copyright status (P6216) set to public domain (Q19652) which are missing required applies to jurisdiction (P1001). My original query recently stopped to work, always giving "Query timeout limit reached" error. Can some figure out a way to optimize it so it is able to complete? --Jarekt (talk) 12:59, 13 May 2019 (UTC) 

SELECT ?item  {
	?item p:P6216 ?statement .
	?statement ps:P6216 wd:Q19652 .              # P6216 = "Public Domain"
  	FILTER NOT EXISTS { ?statement pq:P1001 [] } # exclude if P1001 qualifier present
  	hint:Prior hint:rangeSafe true .
} LIMIT 100
Try it!
By just saying the same with different words:
SELECT ?item  {
  ?item p:P6216 ?statement .
  ?statement ps:P6216 wd:Q19652 . # P6216 = "Public Domain"
  OPTIONAL { ?statement pq:P1001 ?q } .
  FILTER( !BOUND( ?q ) ) . # exclude if P1001 qualifier present
} LIMIT 100
Try it!
Matěj Suchánek (talk) 08:46, 14 May 2019 (UTC)

Column item without "wd:" and image without "commons:"

Good day, I wonder how to get the result table from a query to display the Q# of the item without "wd:" in front of it. Same thing for the image column. I would like to have only the file name without "commons:" in front of it. How can I achieve that? Thanks, Amqui (talk) 15:24, 14 May 2019 (UTC)

@Amqui: See below example for one way of doing it. There are probably other ways.
SELECT ?itemLabel ?item_qnumber ?image_filename {
  ?item wdt:P31 wd:Q35657 .
  ?item wdt:P163 [ wdt:P18 ?image ] .
  BIND(STRAFTER(STR(?item),'entity/') AS ?item_qnumber)
  BIND(STRAFTER(wikibase:decodeUri(STR(?image)),'FilePath/') AS ?image_filename)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?itemLabel
Try it!
--Larske (talk) 15:26, 15 May 2019 (UTC)

Import a parameter from Wikipedia-Infobox and insert it into Wikidata

Probably similar tasks already exist, but I can't find the solution so far. My problem is: I want to import from dewiki de:Vorlage:Infobox Badmintonspieler the property BWFID and from zhwiki zh:Template:Infobox badminton player the property bwf_id both as Property:P3620 to Wikidata. A solution for the extraction from dewiki and zhwiki and the connection to the corresponding Wikidata-ID is requested (the addition to Wikidata via quickstatements I should manage then by myself). Best regards Florentyna (talk) 11:36, 8 May 2019 (UTC)

That's not something for which you'd use a SPARQL query; but you can use HarvestTemplates; see Wikidata:Tools/Edit items. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 11:39, 8 May 2019 (UTC)
Thank you very much. I used HarvestTemplates already a long time ago, but I did not remember this tool. Florentyna (talk) 12:51, 8 May 2019 (UTC)
I think that this discussion is resolved and can be archived. If you disagree, don't hesitate to replace this template with your comment. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 22:28, 15 May 2019 (UTC)

Songs about birds, by family

Songs, whose main subject is a bird of a given family, for example Muscicapidae (Q200989); please. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 22:27, 15 May 2019 (UTC)

@Pigsonthewing: The following query lists objects that are instance of (P31) song (Q7366) or subclass of (P279) thereof with a main subject (P921) that is instance of (P31) a taxon where the chain of parent taxon (P171) eventually ends up in the requested family (Q35409).
SELECT ?song ?songLabel ?main_subject ?main_subjectLabel ?family ?familyLabel {
  BIND(wd:Q200989 AS ?family)
  ?song wdt:P31/wdt:P279* wd:Q7366 .  # also subclasses of song included
  ?song wdt:P921 ?main_subject .
  ?main_subject wdt:P171* ?family .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Larske (talk) 04:50, 16 May 2019 (UTC)
That's a fun one to show how limited our coverage is. Here are all the songs by family, not limited to Muscicapidae (Q200989), nor even to birds!
SELECT DISTINCT ?song ?songLabel ?main_subject ?main_subjectLabel ?family ?familyLabel {
  ?family wdt:P105 wd:Q35409 .
  ?song wdt:P31/wdt:P279* wd:Q7366 .  # also subclasses of song included
  ?song wdt:P921 ?main_subject .
  ?main_subject wdt:P171* ?family .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--99of9 (talk) 05:41, 16 May 2019 (UTC)

All items with label in small language

I am trying to get all labels in some small language (e.g. I know there are ~30 items with labels in Tokipona) but I get timeout: 

SELECT ?item ?lang_label ?itemLabel ?itemDescription WHERE {
# ?item wdt:P279* wd:Q35120.
#  ?item wdt:P31 wd:Q55983715.
# SERVICE wikibase:label { bd:serviceParam wikibase:language "ru,ase,nl,fr,en,de,it,es,no,pt". }
 FILTER(EXISTS {
   ?item rdfs:label ?lang_label.
   FILTER(LANG(?lang_label) = "tokipona") #with missing Italian label
 })
}
ORDER BY ?itemLabel
Try it!

Is it possible at all? Infovarius (talk) 17:08, 4 May 2019 (UTC)

This is not possible with WDQS, I'm afraid. I would have recommended to use the wb_terms SQL table, but it is about to be replaced with something much more complex in very near future (phab:T221764; "as it melts the servers" is what one of the WMF database engineers recently told me). Anyways, what's the language code for "tokipona"? I could give it a try before they shut down the old table. —MisterSynergy (talk) 20:33, 4 May 2019 (UTC)
@MisterSynergy: It's a pity. Code is "tokipona" itself I think. And how is User:Pasleim building his language statistics? --Infovarius (talk) 10:34, 6 May 2019 (UTC)
Since wb_terms is still available, you can find the items with a "tokipona" term (label, description, or alias) in quarry:query/35748. Starting in end of May, there will be a transition period where data is distributed over two schemas, and the old one will be phased out subsequently. As far as I understand, such kind of queries should be possible in the new schema as well, but the query code will likely be much more complex. If Pasleim uses the old table, he would have to update his code. —MisterSynergy (talk) 06:11, 7 May 2019 (UTC)
Thanks! Is it possible to reuse the result in SPARQL? For example, for getting some links between them and to show a graph. --Infovarius (talk) 21:38, 8 May 2019 (UTC)
The only way known to me at the moment is to hardcode the terms into the input, as here. Now you have items bound to ?item for whatever you want to do :-) —MisterSynergy (talk) 05:08, 9 May 2019 (UTC)
Thanks! I've learned something from your query. --Infovarius (talk) 19:55, 14 May 2019 (UTC)
P.S. I am curious why all tokipona labels were deleted? --Infovarius (talk) 19:55, 14 May 2019 (UTC)
The quarry link from above still has the old results, as I did not re-execute it. After looking into some of the item histories, it appears that User:Dexbot removed all the tokipona terms, with edit summaries linking to phab:T200432. Ping @Ladsgroup as bot operator. —MisterSynergy (talk) 20:05, 14 May 2019 (UTC)
Hey, I did it with my bot but did the rest using maintenance script. The language isn't supported anymore and thus needs to be removed. Anything wrong? Amir (talk) 12:51, 16 May 2019 (UTC)

How to use wikidata property paths?

Given a term/concept, I want to decide if it belongs to computer science. For that, I am checking if the concept has a property path to computer science node. In other words, I am checking whether computer science is an ancestral concept.

For this purpose, I am using the following wikidata query. 

SELECT DISTINCT ?item {
VALUES ?searchTerm { "word2vec"}
SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "EntitySearch".
    bd:serviceParam wikibase:endpoint "www.wikidata.org".
    bd:serviceParam wikibase:limit 3 .
    bd:serviceParam mwapi:search ?searchTerm.
    bd:serviceParam mwapi:language "en".
    ?item wikibase:apiOutputItem mwapi:item.
    ?num wikibase:apiOrdinal true.
}
?item (wdt:P279|wdt:P31) ?type
filter exists {?type (wdt:P279|wdt:P361)* wd:Q21198}
}
ORDER BY ?searchTerm ?num
Try it!

However, the query gets timeout.

Therefore, I would like to know if there is another way of solving my approach.

I am only interested in concepts that reaches computer science node using 8 or less hops. For example, is it possible to get all the nodes related to computer science node in 8 or less hops before, and then use this node list as a vocabulary to validate the concepts.

Please let me know if my explaination is not clear.

I am happy to provide more details if needed.

Emijenne (talk) 15:56, 17 May 2019 (UTC)

Getting a company's hierarchy

Hi,

Is there a way to query for a complete organization's hierarchy traversing through parent Organization property or any other property?  – The preceding unsigned comment was added by Srini6teen (talk • contribs) at 12:08, 27 February 2019‎ (UTC).

The question is, what stays as a companys hierarchy for you. What about this query?
# List mother organisation of an organisations set in wd
# for each level, create a separate line as shown in the commented line 3
# for avoyding mother organisation, of an organisation, you just skipi ?PO1stlevelLabel on the SELECT line
SELECT  ?PO2ndlevelLabel # ?PO3rdlevelLabel
WHERE
{
   ?PO1stlevel wdt:P355 wd:Q1318441. 
  ?PO2ndlevel wdt:P355 ?PO1stlevel.
# ?PO3rdlevel wdt:P355 ?PO2ndlevel.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
}
Try it!

--Juandev (talk) 16:44, 18 May 2019 (UTC)

QIDs of Wikipedia article

I have a list of several thousand Wikipedia articles and want to have the corresponding qids. How can I do this? --Sebastian Wallroth (talk) 06:49, 17 May 2019 (UTC)

There are several options, with WDQS, quarry, or (depending on your input) maybe even Petscan. With the Query Service, you could do something like this, which should work with "several thousand" articles as well. Is that already enough? —MisterSynergy (talk) 07:51, 17 May 2019 (UTC)
Super! Thanks, MisterSynergy. --Sebastian Wallroth (talk) 08:04, 17 May 2019 (UTC)

Similar question: I need a list of all lexeme ID for a given list of words (i.e. Schirm,Schirms,Schirme) where the words can be the value of several lexemes. MisterSynergy? --Sebastian Wallroth (talk) 16:44, 17 May 2019 (UTC)

No idea, honestly. Lexemes can be queried with WDQS meanwhile, but I have never done that. —MisterSynergy (talk) 20:22, 17 May 2019 (UTC)

Finding mismatched P373 and commons category sitelinks, following P910

Hi all. Pi bot currently uses this query to look for cases where Commons category (P373) links to a redirect that points to the category linked to through the commons sitelink: 

SELECT ?item ?commonscat ?sitelink ?name WHERE {  ?item wdt:P373 ?commonscat.  ?sitelink schema:about ?item; schema:isPartOf <https://commons.wikimedia.org/>; schema:name ?name .  FILTER( CONTAINS(STR(?sitelink), 'Category:') = true ) .  FILTER( ?commonscat != SUBSTR(STR(?name), 10) ) .} LIMIT 500 OFFSET 0
Try it!

(It then steps through offset values to get the full set). However, this misses cases where the commons category sitelink is in a separate item linked to through topic's main category (P910). Is there a good way of modifying the query to catch those cases too, please? Thanks. Mike Peel (talk) 16:22, 18 May 2019 (UTC)

List items that have certain property

How can I list "railroad lines" (Q728937) that have "OSM relation ID" property (P402)? Like for example this one - Q802995. Possibly limit to country: CZ too. How can I show results to map (in case the item contains coordtinates)? Thanks.--Kozuch (talk) 16:17, 18 May 2019 (UTC) 

SELECT ?item ?osm ?itemLabel WHERE {
  ?item wdt:P31/wdt:P279* wd:Q728937 .
  ?item wdt:P402 ?osm .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
To limit by country, try:
SELECT ?item ?osm ?itemLabel WHERE {
  ?item wdt:P31/wdt:P279* wd:Q728937 .
  ?item wdt:P402 ?osm .
  ?item wdt:P17 wd:Q213 .  # country = Czech Republic
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
To plot items with coordinates on a map:
#defaultView:Map
SELECT ?item ?osm ?itemLabel ?coords WHERE {
  ?item wdt:P31/wdt:P279* wd:Q728937 .
  ?item wdt:P402 ?osm .
  ?item wdt:P17 wd:Q213 .  # country = Czech Republic
  OPTIONAL {?item wdt:P625 ?coords}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
(But it's slightly deprecated to give point co-ordinates to an extended linear feature, which may explain why so few of the items have this). Jheald (talk) 16:42, 18 May 2019 (UTC)
@Kozuch: There are a few railway line (Q728937) items that have terminus location (P609) statements. Then one can write a query like this:
#defaultView:Map{"hide":["?line"]}
SELECT ?item ?osm ?itemLabel ?term1 ?term1Label ?term2 ?term2Label ?line WHERE {
  ?item wdt:P31/wdt:P279* wd:Q728937 .
  ?item wdt:P402 ?osm .
#  ?item wdt:P17 wd:Q213 .  # country = Czech Republic
  ?item wdt:P609 ?term1 .
  ?item wdt:P609 ?term2
  FILTER(str(?term1) < str(?term2))      
  
  ?term1 p:P625/psv:P625/wikibase:geoLatitude ?lat1 .
  ?term1 p:P625/psv:P625/wikibase:geoLongitude ?lon1 .
  ?term2 p:P625/psv:P625/wikibase:geoLatitude ?lat2 .
  ?term2 p:P625/psv:P625/wikibase:geoLongitude ?lon2 .
      
  BIND(CONCAT('LINESTRING (', STR(?lon1), ' ', STR(?lat1), ',', STR(?lon2), ' ', STR(?lat2), ')') AS ?str) .
  BIND(STRDT(?str, geo:wktLiteral) AS ?line) 
      
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
Jheald (talk) 16:58, 18 May 2019 (UTC)

Find all URLs for a domain

The website "BBC Your Paintaings" has moved. The new URL is at artuk.org - I have changed all the URLs on Enwiki and Commons and now looking to do the same at Wikidata. The BBC URLs take the form: www.bbc.co.uk/arts/yourpaintings/<whatever> where <whatever> can be a wide variety of things. The conversion to artuk.org is complex that requires external look-up and searches. I have a bot for that.

Was thinking if there was a query to find all the URLs, I can upload them to userspace on Enwiki and run my bot on them. The bot is designed to work on wiki. Once the links are converted to artuk.org, reupload to Wikidata with Help:QuickStatements a manual copy-paste via the import window. Example QuickStatements.

I would like help to design a query to find all the URLs, and also an example QuickStatements for re-upload. -- GreenC (talk) 14:22, 3 May 2019 (UTC)

WDQS is probably not the best tool for that task. There is either Special:LinkSearch (which I don't get to output the desired results), or this quarry which you could fork and adapt if necessary. —MisterSynergy (talk) 15:58, 3 May 2019 (UTC)

User:MisterSynergy, thanks for the query! If not WDQS how would you recommend automating (or semi) the task of updating Wikidata? -- GreenC (talk) 13:37, 4 May 2019 (UTC)

Now that you have the URLs from quarry, you could use WDQS to evaluate how exactly those URLs are used (namespace 0 results from quarry only). Roughly like this. You still need to transform this to a tool input. I would leave existing references untouched, and add new reference with the new URL instead. —MisterSynergy (talk) 14:06, 4 May 2019 (UTC)

User:MisterSynergy: Ah I see, thanks for putting that together! Now I guess the final step is creating a QuickStatements believe it looks like this:

Q21453973 P570 "" S854 "https://artuk.org/discover/artists/tuer-herbert-c-16001686"

Notice the missing value "" for P570 (should be 1686). Is there a way for the query to include a column for the value of the mainproperty? -- GreenC (talk) 16:10, 4 May 2019 (UTC)

This does not look like a job for QuickStatements, to be honest. Which kind of data do you have? Maybe a spreasheet, or TSV file or so with "old URL, new URL, new URL page title, new URL retrieval date"? If so, the new references could best be added with a short bot script. —MisterSynergy (talk) 20:37, 4 May 2019 (UTC)
User:MisterSynergy, You did not say what is the problem with QuickStatements. I would prefer it. Bots require time, permissions and learning APIs. For 130 changes. -- GreenC (talk) 04:43, 6 May 2019 (UTC)
QuickStatements is not really made for such changes. You can try to add a new reference to an existing claim, but if the provided claim value in your QS input slightly differs from the actual value in the item, it will create a second main value and add the reference to that one. Things like date precision matter as well.
It is much cleaner to retrieve Statement-IDs from WDQS, and add new references with a bot. I can offer to do this for you, in case you can provide input as requested by me on May 4. Such a job is not overly comlicated to code, and one does not need special permissions for small batches like this one. —MisterSynergy (talk) 12:08, 7 May 2019 (UTC)
Ok I see thank you very much. The data is now here User_talk:GreenC#Data_for_Art_UK_URLs. This is the TSV output from the query above with an added field for the new arturk.org URL -- GreenC (talk) 14:52, 7 May 2019 (UTC)
@MisterSynergy: -- GreenC (talk) 14:53, 7 May 2019 (UTC)
Actually I am working on adding a title field.. -- GreenC (talk) 14:59, 7 May 2019 (UTC)
Titles added. Access date is today. -- GreenC (talk) 15:20, 7 May 2019 (UTC)
I have just added those new references. Sorry for the delay! —MisterSynergy (talk) 13:11, 14 May 2019 (UTC)
@MisterSynergy: Great, thanks! One thing for example at this page on commons there are now two URLs, one to BBC and one to artuk. Ideally there wouldn't be a dead URL. What is the rationale for keeping a dead URL on Wikidata and/or is there a way to mark it dead? -- GreenC (talk) 15:45, 14 May 2019 (UTC)
I have no idea whether the Commons template supports those approaches. It should definitely not display deprecated data. —MisterSynergy (talk) 15:52, 14 May 2019 (UTC)
MisterSynergy, to clarify. The BBC hosted the "Your Paintings" website. At some point they transferred hosting and ownership to Art UK. The site moved. It is the same reference and website but with a new URL. Normally on Wikipedia we would not maintain old URLs they would be replaced if there is a new working URL. What is the reason on Wikidata for maintaining old URLs in the database? -- GreenC (talk) 05:43, 16 May 2019 (UTC)
Historical data may be interesting for some data users, thus we do things a little different than Wikipedias sometimes do. The mentioned "ranks" are a low-level functionality that helps controlling visibility of statements, including management of historical data, and there is also the possibility to complement archive URLs to dead links (as long as they are available). That said, I have to mention that there is no policy which tells us what to do, i.e. decisions such as this one are made on a case-by-case basis. —MisterSynergy (talk) 07:29, 16 May 2019 (UTC)
MisterSynergy, Ok thanks for the info. A URL works for about 7 years on average before it stops working. And everything on Wikidata should ideally have a reference thus 10s if not 100s of millions of external links will grow with time, needing archive, moves to new URLs, replacements to new sites, marked dead, etc.. to give a sense. I know that IABot has recently been converted to Wikidata (or in process) so it will help with archiving some dead links. -- GreenC (talk) 12:56, 20 May 2019 (UTC)

Limit coordinates and link to Wikipedia

Hello, I have this query and I need to limit the amount of coordinates per result to one. Also I would need to get link to Polish Wikipedia for each line please. Thx. --Juandev (talk) 17:56, 19 May 2019 (UTC) 


#Display villages in the specific radius from a spacific place

SELECT ?place ?placeLabel ?location WHERE {
  wd:Q7264829 wdt:P625 ?polvillLoc.
  SERVICE wikibase:around {
    ?place wdt:P625 ?location.
    bd:serviceParam wikibase:center ?polvillLoc;
      wikibase:radius "27".
  }
  FILTER(EXISTS { ?place wdt:P31 wd:Q3558970. })
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
@Juandev: Try this modified query.
#Display villages within the specific radius from a specific place

SELECT ?place ?placeLabel ?plwp ?location WITH {
  SELECT ?place (SAMPLE(?location) AS ?location) {
  wd:Q7264829 wdt:P625 ?polvillLoc .
  SERVICE wikibase:around {
    ?place wdt:P625 ?location.
    bd:serviceParam wikibase:center ?polvillLoc;
      wikibase:radius "27".
  }
  ?place wdt:P31 wd:Q3558970 .
 }
 GROUP BY ?place
 } AS %i WHERE {
  include %i
  OPTIONAL { ?plwp schema:about ?place; schema:isPartOf <https://pl.wikipedia.org/> . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Larske (talk) 19:06, 19 May 2019 (UTC)

Decoding sitelink and removing prefix

Hi! Would it be possible adding an extra column in this query with the url-commons-sitelink decoded? Removing the <https://commons.wikimedia.org/wiki/Category: (...) > would be nice too. Thanks in advance! strakhov (talk) 14:51, 20 May 2019 (UTC)

?article schema:name ?_title and (STRAFTER(?_title, 'Category:') AS ?title). Matěj Suchánek (talk) 17:21, 20 May 2019 (UTC)

Adding missing birth date from date of participation

There are a lot of badminton players (wdt:P106 wd:Q13141064) without a birth date (more than 4000), see Wikidata:WikiProject Badminton/Lists. I hope it is possible to do the same what I do in my brain with a query: If the player is participant of a junior tournament (participant of (P1344) contains "junior"), then add the birth date from the year of the participation minus 19 years with sourcing circumstances (P1480) circa (Q5727902). If the player is not participant of a junior tournament, then add the birth date from the year of the participation minus 25 years with sourcing circumstances (P1480) circa (Q5727902) (or the resulting decade like 1960s, 1970s etc). Florentyna (talk) 15:15, 12 May 2019 (UTC)

Solved. Extract year from participation, then in Excel year minus 19 resp. 25 years, Pivot table with max-min year, and add it to Wikidata. Florentyna (talk) 07:30, 21 May 2019 (UTC)

People born in Valle del Cauca but not in Cali

Can someone please make a query for list the people born in Valle del Cauca (Q13990) but not in its capital city Cali (Q51103). Thanks --Sahaquiel9102 (talk) 05:30, 22 May 2019 (UTC)

@Sahaquiel9102: Try this query:
SELECT ?birthplace ?birthplaceLabel ?item ?itemLabel {
  { ?item wdt:P31 wd:Q5 . ?item wdt:P19/wdt:P131* wd:Q13990 . }
  MINUS 
  { ?item wdt:P19 wd:Q51103 . }
  ?item wdt:P19 ?birthplace .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?birthplaceLabel ?itemLabel
Try it!
--Larske (talk) 23:59, 22 May 2019 (UTC)
Thanks! --01:04, 23 May 2019 (UTC)

Transfer of category members in dewiki to location on Wikidata

I have often seen the summary added to Property:xyz to Wikidata because included in Category:abc. Could this be done for all subcategories of de:Kategorie:Badmintonwettbewerb nach Ort, too? Means for example: Add location (P276) = Amersfoort (Q992) to 2007 Europe Cup in Badminton (Q799371) because this event is included in de:Kategorie:Badmintonwettbewerb in Amersfoort (Q26245327). This for all events with P279 (subclass) = Q13357858 (badminton tournament) from the category de:Kategorie:Badmintonwettbewerb nach Ort (Q21192307). Additionally, if the event has a statement has part (P527), also for all entities mentioned in the has-part-section except for tournaments with P2094 (competition class) = Q63874701 (tournament series); or except for tournaments with the statement qualifies for event (P3085) . Florentyna (talk) 06:02, 23 May 2019 (UTC)

@Ghuron: as the source of that specific sort of edit summary. Mahir256 (talk) 06:12, 23 May 2019 (UTC)
@Florentyna: Please verify whenever mentioned categories are connected to the correct location: 
SELECT ?cat ?catLabel ?location ?locationLabel {
  ?cat p:P4224[ps:P4224 wd:Q13357858; pq:P276 ?location]
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de" }
}
Try it!
--Ghuron (talk) 12:09, 23 May 2019 (UTC)
Looks good. I was a little bit confused about Zgornja Kungota (Q3434908), but in this part of Kungota (Q998566) the tournaments take place, means: For the location of the tournament it is the correct entity, for the category it should be later on changed to Q998566 or the category must be renamed. Florentyna (talk) 12:55, 23 May 2019 (UTC)
@Florentyna: Fixed Kungota. Apparently you know how to use quickstatements, so here is a query for all items, that belongs to those categories, has subclass of (P279)badminton tournament (Q13357858), but no location (P276) specified: 
PREFIX mw: <http://tools.wmflabs.org/mw2sparql/ontology#>
SELECT DISTINCT ?tournament ?tournamentLabel ?location ?locationLabel {
  hint:Query hint:optimizer "None" .
  ?cat p:P4224[ps:P4224 wd:Q13357858; pq:P276 ?location] .
  ?catArticle schema:about ?cat
  SERVICE <http://tools.wmflabs.org/mw2sparql/sparql> {
    ?page mw:inCategory ?catArticle
  }
  ?page schema:about ?tournament . ?tournament wdt:P279 wd:Q13357858
  FILTER NOT EXISTS { ?tournament wdt:P276 [] } 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,de" }
}
Try it!
--Ghuron (talk) 07:47, 24 May 2019 (UTC)

Thanks a lot, just started it. Florentyna (talk) 10:34, 24 May 2019 (UTC)

@Florentyna: I can see that my query return no data now, so I assume you've uploaded the data. Now we can move to the third part (P527). Please verify that I've expressed your proposed filtering correctly: 
SELECT ?parts ?partsLabel ?location ?locationLabel {
  ?tournament wdt:P279 wd:Q13357858; wdt:P276 ?location; wdt:P527 ?parts .
  ?parts wdt:P279 wd:Q13357858
  FILTER NOT EXISTS { ?parts wdt:P276 [] }
  FILTER NOT EXISTS { ?parts wdt:P2094 wd:Q63874701 }
  FILTER NOT EXISTS { ?parts wdt:P3085 [] }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,de" }
}
Try it!
--Ghuron (talk) 10:18, 25 May 2019 (UTC)

Girl groups with genre kpop

Hello, I would like to request a query with the following parameters: Instance of girl group (Q641066) with genre = kpop (Q213665). Thanks, --Redalert2fan (talk) 19:47, 24 May 2019 (UTC)

@Redalert2fan: Try this query:
SELECT ?item ?itemLabel (lang(?itemLabel) AS ?lang) WHERE {
  ?item wdt:P31 wd:Q641066 .
  ?item wdt:P136 wd:Q213665 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?itemLabel
Try it!
--Larske (talk) 06:19, 25 May 2019 (UTC)
Thanks! --Redalert2fan (talk) 09:12, 25 May 2019 (UTC)

Most western city or town in Alabama?

I just saw this asked on Quora, and wondered whether we could answer it? Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 20:52, 24 May 2019 (UTC)

@Pigsonthewing: Could be Semmes (Q79476); though truth of answer may turn on definitions; and/or completeness of our data; and/or whether one defines the 'western-ness' by a representative point near the centre, or by furthest west point within the settlement boundary.
SELECT DISTINCT ?item ?itemLabel ?long (SAMPLE(?coords) AS ?coords) WHERE {
   VALUES ?town_city {wd:Q3957 wd:Q515} .
   ?item wdt:P31/wdt:P279* ?town_city .
   ?item wdt:P131* wd:Q173 .
   ?item p:P625/psv:P625/wikibase:geoLongitude ?long .
   ?item wdt:P625 ?coords
   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} GROUP BY ?item ?itemLabel ?long
ORDER BY ?long LIMIT 40
Try it!
Jheald (talk) 21:22, 24 May 2019 (UTC)

build a company page in wikidata

hello, i want to build company page in wikidata but i don't know how to do this i try 2 times build a page but 2 times my page was deleted


I guess it's just not allowed, review the terms of service. Nikolay Komarov (talk) 03:11, 27 May 2019 (UTC)

People who were boen in the 1920s' and more

Hi. I need a query about enwiki articles for people who were born from 1920 to 1929 + for which there is no existing article at hewiki. Thanks. YoavR (talk) 12:17, 25 May 2019 (UTC)

@YoavR: This should do it:
SELECT ?item ?linkcount ?_articleEN ?born
WHERE
{
  ?item wdt:P31 wd:Q5 ; wdt:P569 ?born .
  # person with birthdate
  FILTER (?born >= "1920-01-01T00:00:00Z"^^xsd:dateTime) .
  # birthdate on or after 1/1/1920
  FILTER (?born < "1930-01-01T00:00:00Z"^^xsd:dateTime) .
  # birthdate before 1/1/1930
  ?_articleEN schema:about ?item. ?_articleEN schema:isPartOf <https://en.wikipedia.org/>.
  # has an enwiki article
  filter not exists { ?_articleHE schema:about ?item. ?_articleHE schema:isPartOf <https://he.wikipedia.org/>. }
  # does not have hewiki article
  ?item wikibase:sitelinks ?linkcount .
  # how many sitelinks has it got?
} order by desc(?linkcount)
Try it!
The ?linkcount element is to help give a sense of how "prominent" the item is, as otherwise you have 76k results to look through! Andrew Gray (talk) 19:18, 26 May 2019 (UTC)
Thank you very much. YoavR (talk) 19:38, 26 May 2019 (UTC)

Query bounderies

Dear all,
with the following query I produce a working list for species year of descriptions (species with older description date than the genus to include brackets / recombination (Q14594740)): Query

I would like to exclude all species that are not Metazoa (Q151542) or directly only include this taxon/tree. Anyone can help me with this? -- Achim Raschka (talk) 07:40, 28 May 2019 (UTC)

Countries without OSM relation ID

I'm looking for a simple query: instances of country without OpenStreetMap tag or key (P1282). NMaia (talk) 06:53, 29 May 2019 (UTC) 

SELECT ?country ?label ?relation WHERE {
  ?country wdt:P31 wd:Q6256 .
  
  OPTIONAL {  
    ?country wdt:P402 ?relation .
  }
  
  FILTER (!BOUND(?relation))
  
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en" . 
    ?country rdfs:label ?label
  }
}
LIMIT 1000
Try it!
@NMaia: I assume you meant OpenStreetMap relation ID (P402) as in your title, because countries don't have OpenStreetMap tag or key (P1282). --99of9 (talk) 07:05, 29 May 2019 (UTC)

climate change imagery

Dear all,


For a research project, we are looking into the visual representation of climate change in the web. In this context, the following questions came up:

- What would be the query for: show all images from wikimedia commons, which are related to 'climate change' (and/or 'global warming'? Not only with the property P921 (depicts), but every possible relationship.

- How can I also include items that mention 'climate change / global warming' in the file name?


Thanks in advance!

Simon

Hi Simon. At the moment most images on Commons are not linked to Wikidata structured data. For the moment I think you would be better off using the category tree in Commons. --99of9 (talk) 07:51, 31 May 2019 (UTC)

Is there a better way to find all "writers" and any of its subclasses?

I'm trying to find all the living writers of any kind in Massachusetts. Here's what I have so far, but it seems rather clunky. I imagine there must be some way of returning all the writers and any of its subclasses more elegantly (i.e. I'm talking about all the UNIONs I had to use). Thanks for any insight.

see: https://w.wiki/4Sm  – The preceding unsigned comment was added by MJSfoto1956 (talk • contribs) at 29 maj 2019 kl. 21.11 (UTC).

@MJSfoto1956: This query will give the same result:
SELECT ?person ?personLabel ?personDescription ?birthplaceLabel ?dob ?dod (SAMPLE(?img) AS ?image)
WHERE {
  VALUES ?p106 { wd:Q36180 wd:Q753110 wd:Q6625963 wd:Q1607826 wd:Q482980 wd:Q49757 wd:Q1086863 wd:Q1930187 wd:Q214917 wd:Q28389 }
  ?person wdt:P27 wd:Q30 ;
          wdt:P19/wdt:P131* wd:Q771 ;
          wdt:P19 ?birthplace ;
          wdt:P569 ?dob ;
  OPTIONAL {?person wdt:P18 ?img .}
  OPTIONAL {?person wdt:P570 ?dod .}
  { ?person wdt:P106 ?p106 }
  MINUS {?person wdt:P570 [] .}
  FILTER (YEAR(?dob) > 1919)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en"}
} GROUP BY ?person ?personLabel ?personDescription ?dod ?dob ?birthplaceLabel
ORDER BY ASC (?dob)
Try it!
The result is limited to the classes listed in the VALUE clause.
If you want all subclasses to author (Q482980) this could be achieved by using "wdt:P106/wdt:P279* wd:Q482980.
SELECT DISTINCT ?person ?personLabel ?personDescription ?birthplaceLabel ?dob ?dod ?image WITH {
  SELECT ?person ?birthplace ?dob ?dod (SAMPLE(?img) AS ?image) WHERE {
  ?person wdt:P27 wd:Q30 ;
          wdt:P19/wdt:P131* wd:Q771 ;
          wdt:P19 ?birthplace ;
          wdt:P569 ?dob ;
  OPTIONAL {?person wdt:P18 ?img .}
  OPTIONAL {?person wdt:P570 ?dod .}
  MINUS {?person wdt:P570 [] .}
  FILTER (YEAR(?dob) > 1919)
} GROUP BY ?person ?birthplace ?dod ?dob
} AS %i WHERE {
    include %i 
    ?person wdt:P106/wdt:P279* wd:Q482980 .
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en"}
} 
ORDER BY ASC (?dob)
Try it!
The 968 results include persons with occupation (P106) in any of the subclasses of author (Q482980) also subclasses that are not listed in the first query above that will give 671 results.
--Larske (talk) 08:02, 31 May 2019 (UTC)


@MJSfoto1956: So is the reason for all this unusual "nesting" is to make the query more performant? If I have this right, you first get a list of all persons born in Massachusetts then intersect it with persons who are authors + all subclasses of authors. Presumably, trying to fetch both at the same time results in a timeout?

Danish Mosques without coordinate locations

Can anyone help me making such a list? --Trade (talk) 18:19, 30 May 2019 (UTC)

@Trade: Assuming you mean mosque (Q32815) with country (P17) equal to Denmark (Q35), try this query:
SELECT ?item ?itemLabel {
  ?item wdt:P31 wd:Q32815 .
  ?item wdt:P17 wd:Q35 .
  OPTIONAL { ?item wdt:P625 ?coord }
  FILTER(!BOUND(?coord))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,ar". }
}
Try it!
--Larske (talk) 07:02, 31 May 2019 (UTC)
Retrieved from "https://www.wikidata.org/w/index.php?title=Wikidata:Request_a_query/Archive/2019/05&oldid=962517419"