Wikidata

Wikidata:Request a query/Archive/2020/03

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

Unification of distances in the stars

By running the following query: 

# Stars up to 18 light years away sorted by the nearest
SELECT ?star ?starLabel ?distance
WHERE
{
	?star wdt:P31 wd:Q523 ;
	      wdt:P2583 ?distance;
    FILTER( ?distance < 19 )
		SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}
ORDER BY ?distance
Try it!

The logical thing would be to think that the first result should be Alpha Centauri A (Q2090157) (since it is the closest to Earth), but in reality is COSMOS2015 489026 (Q75031832) which is 0.15 kiloparsec. Is there any way to detect the distances expressed in parsec and kiloparsec to convert them to light years? Otherwise, the distance from Earth (P2583) should be added in each star both in light years and in (kilo)parsecs and choose from the query which to use.--190.139.216.64 02:25, 20 February 2020 (UTC)

There are two possibilities. One is the built-in normalization of units:
# Stars up to 18 light years away sorted by the nearest
SELECT ?star ?starLabel ?distance ?unitLabel ?lightyears
WHERE
{
  {
    SELECT ?star ?distance ?unit ?lightyears
    WHERE
    {
      ?star wdt:P31 wd:Q523.
	  ?star p:P2583/psv:P2583 ?statement.
      ?statement wikibase:quantityUnit ?unit.
      ?statement wikibase:quantityAmount ?distance.
      ?statement wikibase:quantityNormalized/wikibase:quantityAmount ?distanceNormalized. # convert to normalized unit (meter)
      BIND (?distanceNormalized / 9460800000000000  AS ?lightyears)
      FILTER( ?lightyears < 19)
    }
    LIMIT 100
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}
ORDER BY ?lightyears
LIMIT 100
Try it!
You can also explicit convert the units:
# Stars up to 18 light years away sorted by the nearest
SELECT ?star ?starLabel ?distance ?unit ?unitLabel ?lightyears
WHERE
{
  {
    SELECT ?star ?distance ?unit ?lightyears
    WHERE
    {
	  ?star wdt:P31 wd:Q523.
	  ?star p:P2583/psv:P2583 ?d.
      ?d wikibase:quantityUnit ?unit.
      ?d wikibase:quantityAmount ?distance.
      BIND(
        COALESCE(
          IF(?unit = wd:Q531, ?distance, 1/0), # lightyear
          IF(?unit = wd:Q12129, ?distance*3.2616, 1/0), # paresc
          IF(?unit = wd:Q11929860, ?distance*3261.6, 1/0), # kiloparsec
          IF(?unit = wd:Q3773454, ?distance*3261600, 1/0), # megaparsec
          -1
      ) AS ?lightyears)
      FILTER( ?lightyears < 19)
    }
    LIMIT 100
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}
ORDER BY ?lightyears
LIMIT 100
Try it!
I have limited both queries to 100 results before sorting because theywill timeout without a limit. There are 1,542,325 stars in Wikidata. I think it is impossible to search them all and convert the units in 60 seconds using SPARQL. --Dipsacus fullonum (talk) 05:15, 20 February 2020 (UTC)
The most time-consuming part of the queries is the FILTER function (when removed its run almost instantaneously). Ideally, all these distances (light-year (Q531), parsec (Q12129), kiloparsec (Q11929860) and megaparsec (Q3773454)) should be stored in the database instead of being calculated on the fly and that everyone chooses the unit they prefer, I think that would improve the execution times of the query.--190.139.216.64 13:18, 20 February 2020 (UTC)
Probably it's better to use "wdt:P31/wdt:P279* wd:Q523" as not all the stars have preferred "Q523" value. Also do you want e.g. brown dwarf (Q101600) (one is very close to the Sun)? They are even technically not stars... --Infovarius (talk) 10:14, 3 March 2020 (UTC)

Most common kinds of items and what are the most common statements for them? I want to write some instructions for new contributors

Hi all

I'm not sure exactly what query I'm looking for but I'm working on schemas and other instructions for new contributors and I really like some information on common kinds of items so I know what people want to work on. What I would like to know is what are the most common 'kinds' of items are, as these, I guessing, are probably what they will want to create an item for. I guess this could be measured in a couple of different ways

  • Most common statements overall (this will be useful for quick instructions for themed editathons etc)
  • Most common 'instance of'
  • Other very common statements and the most common values for those statements
  • I assume there are a lot of people on Wikidata, to break this down a bit, what are the most common statements for people and what are the most common values?
  • Once there's a list of the most common 'kinds' of items what are the most common statements for them e.g What are the most common statements for scientists? (I assume the professional would be more granular than 'scientist' but would have very similar instructions to create

Thanks very much

--John Cummings (talk) 19:56, 28 February 2020 (UTC)

@John Cummings: There's some statistics info at the bottom of Wikidata:Statistics, and you can get an idea of common properties for kinds of items from the WD:Recoin tool. --Yair rand (talk) 19:51, 2 March 2020 (UTC)

Weird football statistics (2 queries)

I just added full individual statistics for the UEFA Super Cup (Q484028) (example1example2).

Top 10 of the youngest and the oldest goal scorers and players in tournament history

Every player has dates of his first & last game and goal. Given their dates of births, I need to get the maximum and minimum differences with the dates of goals and games. Сидик из ПТУ (talk) 19:01, 1 March 2020 (UTC)

@ Сидик из ПТУ: 10 youngest and 10 oldest goal scorers:
SELECT ?player ?playerLabel ?dob ?goal_date ?goal_age_days ?goal_age_years
WITH
{
  SELECT ?player ?dob ?date_first ?date_latest ?age_first ?age_latest
  WHERE
  {
    ?player p:P6509 ?p6509stm . # goals
    ?p6509stm pq:P7124 ?date_first .
    ?p6509stm pq:P7125 ?date_latest .
    ?p6509stm pq:P642 wd:Q484028 . # for UEFA Super Cup
    ?player wdt:P569 ?dob . # Date of birth
    BIND(?date_first - ?dob AS ?age_first)
    BIND(?date_latest - ?dob AS ?age_latest)
  }
} as %query
WHERE
{
  {
    SELECT ?player ?dob (?date_first AS ?goal_date) (?age_first AS ?goal_age_days)
    {
      INCLUDE %query
    }
    ORDER BY ASC(?goal_age_days)
    LIMIT 10
  }
  UNION
  {
    SELECT ?player ?dob (?date_latest AS ?goal_date) (?age_latest AS ?goal_age_days)
    {
      INCLUDE %query
    }
    ORDER BY DESC(?goal_age_days)
    LIMIT 10
  }
  BIND(?goal_age_days/365.25 AS ?goal_age_years)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ASC(?goal_age_days)
Try it!
--Dipsacus fullonum (talk) 21:00, 1 March 2020 (UTC)
Great! Thanks! Сидик из ПТУ (talk) 21:16, 1 March 2020 (UTC)

Places of birth of soccer players on a world map

Can I mark on the map with circles the places of births of all goal scorers in the tournament history? They must have more than 0 goals scored. Сидик из ПТУ (talk) 19:01, 1 March 2020 (UTC)

@Сидик из ПТУ: Something like this maybe:
#defaultView:Map{"hide":"?coord"}
SELECT ?itemLabel ?birthplaceLabel ?coord ?layer {
  ?item p:P6509 ?p6509stm .
  ?p6509stm ps:P6509 ?layer .
  FILTER(?layer > 0)
  ?p6509stm pq:P642 wd:Q484028 .
  ?item wdt:P19 ?birthplace .
  ?birthplace wdt:P625 ?coord .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Larske (talk) 19:59, 1 March 2020 (UTC)
Great! Thanks! Сидик из ПТУ (talk) 20:03, 1 March 2020 (UTC)

Nigeria women biographies without photos on Wikipedia

Hi

Greetings,

I need a query on Wikidata for the list of Nigerian's women biography on Wikipedia without photos.

Thanks as I await your help.Olaniyan Olushola (talk) 13:21, 2 March 2020 (UTC)

@Olaniyan Olushola: I sorry to say that it is impossible to query on Wikidata the details of articles on a Wikipedia. I can easily write a query for Nigerian women without an image on Wikidata, but not a query for Wikipedia biographies without image. There may be other tools which can do that, but I cannot help with that. Maybe others can. --Dipsacus fullonum (talk) 19:34, 2 March 2020 (UTC)
SELECT DISTINCT ?item ?itemLabel ?itemDescription 
{
    { ?item wdt:P27 wd:Q1033 } UNION { ?item wdt:P19/wdt:P17 wd:Q1033 } UNION { ?item wdt:P20/wdt:P17 wd:Q1033 } 
    ?item wdt:P21 wd:Q6581072 .
    FILTER NOT EXISTS { ?item wdt:P18 [] }
    [] schema:about ?item . 
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Once done, you could re-run it. There might be some that get missed though (images locally at Wikipedia or search not working well) --- Jura 22:19, 2 March 2020 (UTC)

Renaming label variables

Hi, I only need the label of the entity, but I need this variable mapped to a specific variable name (`innerText`). 

SELECT ?innerText  WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?century wdt:P31 wd:Q578.
  ?century wdt:P585 "1600-00-00T00:00:00Z"^^xsd:dateTime.
  BIND(?centuryLabel as ?innerText)
}
LIMIT 1
Try it!

In this case I expect the word 16th century. How do I do that? --Shisma (talk) 18:58, 2 March 2020 (UTC)

@Shisma: You can use the manual mode of the label service:
SELECT ?innerText  WHERE {
  ?century wdt:P31 wd:Q578.
  ?century wdt:P585 "1600-00-00T00:00:00Z"^^xsd:dateTime.
  SERVICE wikibase:label
  { 
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
    ?century rdfs:label ?innerText .
  }
}
LIMIT 1
Try it!
--Dipsacus fullonum (talk) 19:22, 2 March 2020 (UTC)
@Dipsacus fullonum: thats it. thank you 🙂--Shisma (talk) 19:27, 2 March 2020 (UTC)

Project country coverage stats

I'm trying to write a query that would give some statistics on the number of articles on a particular country's citizens/subdivisions/other in each Wikipedia, but it keeps timing out. Even this query, which only deals with citizens and only lists the top 15, times out: 

SELECT ( count( ?q ) as ?i ) ?project WHERE {
  ?q wdt:P17 wd:Q30 ;
     ^schema:about / schema:isPartOf ?project .
  FILTER EXISTS { ?project wikibase:wikiGroup 'wikipedia' . }
} GROUP BY ?project ORDER BY DESC ( ?i ) LIMIT 15
Try it!

Is there any way to make this kind of query run faster? --Yair rand (talk) 20:34, 2 March 2020 (UTC)

@Yair rand: Remove the filter. The majority of sitelinks goes to Wikipedias anyway, so the filter uses time without limiting the results much. There will only be Wikipedias among the 15 first results, but if you increase the limit you can always add a filter after the grouping and counting by using subqueries – or take out unwanted results manually. Without filter and with P17 (country) changed to P27 (citizenship) I could run the query in 32 s without timeout. For items in USA (using P17) I think you have to query more specific subgroups in each query by adding more conditions with e.g. P31. --Dipsacus fullonum (talk) 22:42, 2 March 2020 (UTC)

How to query all places within a place?

How do I query Ancestral Home (P66) so I get all values within Finland (Q33), not just Finland?

With the query below, I get two results that have the exact value Finland - not all places within Finland. 

 SELECT ?item ?itemLabel WHERE {
 ?item wdt:P66 wd:Q33;
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".}
 }

For example, I know that Richard Dean Andersson (Q203047) has a ancester from Munsala in Finland. What do I need to change in the query so he and others would be included in the result?

Thanks for any help!

Hi. You can add P17+ (country) to the property path after P66. You will also need a hint to engine to search the path forward to avoid a timeout from looking at all things in Finland:
SELECT ?item ?itemLabel
WHERE
{
  ?item wdt:P66/wdt:P17+ wd:Q33 . hint:Prior hint:gearing "forward" .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
Try it!
--Dipsacus fullonum (talk) 09:49, 3 March 2020 (UTC)
@Dipsacus: Thank you! Works as I hoped :)

How to show only unique values when using several GROUP_CONCAT?

Hello,

Is there any way to leave out duplicates when using GROUP_CONCAT on multiple columns? The following query will duplicate values within each cell. I'd like to just have unique ones. 

 SELECT ?place ?placeLabel 
 (GROUP_CONCAT(?p31label;SEPARATOR=", ") AS ?instances) 
 (GROUP_CONCAT(?p361label;SEPARATOR=", ") AS ?part_ofs) 
 WHERE {
   ?place wdt:P37 wd:Q9027.
   OPTIONAL { ?place wdt:P31 ?p31 . ?p31 rdfs:label ?p31label . FILTER(lang(?p31label)='sv') }
   OPTIONAL { ?place wdt:P361 ?p361 . ?p361 rdfs:label ?p361label . FILTER(lang(?p361label)='sv') }
   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
 }
 GROUP BY ?place ?placeLabel
 ORDER BY STR(?placeLabel)

Thank you for help!

@Robertsilen: You can add the word DISTINCT as the first word in GROUP_CONCAT arguments, like GROUP_CONCAT(DISTINCT ?p31label;SEPARATOR=", "). --Dipsacus fullonum (talk) 10:13, 3 March 2020 (UTC)
PS. I would recommend using the label service in manual mode with language fallback for the labels you concatenate in case some items don't have Swedish labels. --Dipsacus fullonum (talk) 10:19, 3 March 2020 (UTC)
@Dipsacus: Thank you, adding DISTINCT did the trick! Regarding the label service - I picked up the above way of doing it from an example I was given - so I'm not totally sure of how it works. Please do tell me how to do "language fallback for the labels you concanate". It seems that simply changing FILTER(lang(?p31label)='sv') to FILTER(lang(?p31label)='[AUTO_LANGUAGE],sv') does not work. I'd like the result to be in Swedish, and fallback to anything else if Swedish is not available.
@Robertsilen: Here is a version using the label service for the labels. It tries first for a Swedish label, then a Finnish and English label if none is found. Last resort will be the Q-number instead of a label. You can of course change the language codes to your preferences. It is described in the user manual.
SELECT ?place ?placeLabel 
 (GROUP_CONCAT(DISTINCT ?p31label;SEPARATOR=", ") AS ?instances) 
 (GROUP_CONCAT(DISTINCT ?p361label;SEPARATOR=", ") AS ?part_ofs) 
 WHERE {
   ?place wdt:P37 wd:Q9027.
   OPTIONAL { ?place wdt:P31 ?p31 . }
   OPTIONAL { ?place wdt:P361 ?p361 . }
   SERVICE wikibase:label { 
     bd:serviceParam wikibase:language "sv,fi,en".
     ?place rdfs:label ?placeLabel .
     ?p31 rdfs:label ?p31label .
     ?p361 rdfs:label ?p361label .
   }
 }
 GROUP BY ?place ?placeLabel
 ORDER BY STR(?placeLabel)
Try it!
--Dipsacus fullonum (talk) 21:55, 3 March 2020 (UTC)

List of IATA airport codes with geo coords, city, country, continent

Hi all, I'm new to SPARQL, I'm trying to create a query to have:

  • IATA airport code
  • Geographical coordinates
  • City
  • Country
  • Continent

What I did with the query editor: 

SELECT ?codice_aeroportuale_IATA ?coordinate_geografiche ?PaeseLabel ?continenteLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  OPTIONAL { ?item wdt:P238 ?codice_aeroportuale_IATA. }
  OPTIONAL { ?item wdt:P625 ?coordinate_geografiche. }
  OPTIONAL { ?item wdt:P17 ?Paese. }
  OPTIONAL { ?item wdt:P30 ?continente. }
}
LIMIT 10
Try it!

But all I have are IATA codes, geo coords and country. No continent (blanks). Moreover, I wasn't able to add city to the query as a property.

And i receive lots of cases of duplicated IATA code because of different geo coords, for example:

  • ROD Point(19.899918 -33.812355)
  • ROD Point(19.903697222 -33.811775)

Is there anyone that can help me obtain the result I need without duplicates?

Thank you!--195.32.95.24 13:21, 3 March 2020 (UTC)

Continent are blank because there are no values for airport items. continent (P30) are normally only used for countries, and in case of countries in more than one continent also in the largest subdivisions. So you would need to use a chain of located in the administrative territorial entity (P131) values until you found an item giving the continent. It can be a little tricky to do that and only select the correct values for countries in multiple continents. To avoid duplicates of coordinates the results can grouped by IATA code. I don't have time to write code for this now, but can do it later if no one else has done it. --Dipsacus fullonum (talk) 14:17, 3 March 2020 (UTC)
Hi Dipsacus, thank you for your feedback. I don't know actually how to "chain" located in the administrative territorial entity (P131) values; I also tried to group by ?codice_aeroportuale_IATA but I received an error. I guess I'll wait your kind support ;)--195.32.95.24 15:21, 3 March 2020 (UTC)
Here is a "simple" version without continent, but with the results grouped by IATA code to avoid duplicate coordinates. I will return with a query to include continent later.
SELECT ?codice_aeroportuale_IATA (SAMPLE(?coord) AS ?coordinate_geografiche) ?PaeseLabel
WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item wdt:P238 ?codice_aeroportuale_IATA.
  OPTIONAL { ?item wdt:P625 ?coord. }
  OPTIONAL { ?item wdt:P17 ?Paese. }
}
GROUP BY ?codice_aeroportuale_IATA ?PaeseLabel
LIMIT 10
Try it!
--Dipsacus fullonum (talk) 21:35, 3 March 2020 (UTC)
Hello again. Here is a query which gives continent. However there are problems. First, there isn't data about continent for all places in Wikidata! When a country has parts in more than one continent, the code will try to get the continent from the largest administrative unit that the item is in instead of from the country. But that isn't always possible, so for some airports there will more than one value for continent, even though only one is true.
Another problem is that there isn't time enough to handle all 8.745 airports with IATA code on Wikidata. So if you remove the limit on results, you have to also remove either all the code for continent or the code for labels (the SERVICE wikibase:label line) to avoid that the query times out.
SELECT DISTINCT ?item ?itemLabel ?codice_aeroportuale_IATA ?Paese ?PaeseLabel ?coordinate_geografiche ?continent ?continentLabel
WHERE
{
  {
    SELECT ?item ?codice_aeroportuale_IATA (SAMPLE(?coord) AS ?coordinate_geografiche) ?Paese
    WHERE
    {
      ?item wdt:P238 ?codice_aeroportuale_IATA.
      OPTIONAL { ?item wdt:P625 ?coord. }
      OPTIONAL { ?item wdt:P17 ?Paese. }
    }
    GROUP BY ?item ?codice_aeroportuale_IATA ?Paese
    LIMIT 10
  }
  OPTIONAL
  {
    # Find continent for the country
    ?Paese wdt:P30 ?continent_country. 
  }
  OPTIONAL
  {
    # Find continent for the largest subunit in the country
    ?item wdt:P131+ ?subunit. hint:Prior hint:gearing "forward".
    ?subunit wdt:P131 ?Paese.
    FILTER(?subunit != ?Paese)
    ?subunit wdt:P30 ?continent_subunit.
  }
  # Use continent for subunit if present, otherwise for country
  BIND(COALESCE(?continent_subunit,?continent_country) AS ?continent)
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Dipsacus fullonum (talk) 19:46, 4 March 2020 (UTC)
Thank you! Your first query fits perfect for what I actually need; I can always add the continent from a reference list in excel, that's not a problem. Again, thank you!--195.32.95.24 09:07, 5 March 2020 (UTC)

Get a list of lexemes with sense

Hello! Some months ago I made this request and @TweetsFactsAndQueries: gave me this query that seems to be broken. 

SELECT ?lexeme (GROUP_CONCAT(?lemma; separator = "/") AS ?lemmata) ?sampleSense (SAMPLE(?gloss) AS ?anyGloss)
WITH {
  SELECT ?lexeme (SAMPLE(?sense) AS ?sampleSense) WHERE {
    SERVICE bd:sample {
      ?lexeme dct:language wd:Q8752.
      bd:serviceParam bd:sample.limit 20 .
    }
    OPTIONAL { ?lexeme ontolex:sense ?sense. }
  }
  GROUP BY ?lexeme
} AS %lexemesWithOneSense
WHERE {
  INCLUDE %lexemesWithOneSense.
  ?lexeme wikibase:lemma ?lemma.
  OPTIONAL { ?sampleSense skos:definition ?gloss. }
}
GROUP BY ?lexeme ?sampleSense
Try it!

I don't know exactly where it breaks. Can someone help me figure out? Thanks! -Theklan (talk) 11:52, 4 March 2020 (UTC)

@Theklan: Can you clarify how it’s broken? The potential problem I’m seeing is that it might be returning too many results (I’m getting 222284) – is that also your problem? --TweetsFactsAndQueries (talk) 19:46, 4 March 2020 (UTC)
@TweetsFactsAndQueries: Instead of giving a list of 20 lexemes and their definition (it made that) now it gives thousands of repeated results with linked glosses that are not in the same language, and are not evident why they are there. For example I get:
wd:L224884 independizatu wd:L22370-S1 stát v Evropě
And it doesn't make sense, because independizatu (L224884) is not Suède (L22370-S1) and the word isn't a stát v Evropě. -Theklan (talk) 19:53, 4 March 2020 (UTC)
@Theklan: Yeah, I think that’s due to the “produce all glosses that exist” I mentioned below (it would also, in reverse, produce the corresponding senses). I thought I had time to write the second part of my response before you’d see the first one, but clearly not, sorry :D --TweetsFactsAndQueries (talk) 20:00, 4 March 2020 (UTC)
And as for the too many results, I think I just made a mistake in the query logic and it didn’t surface before because all the results had senses (I think). But if the inner query doesn’t find any senses for a lexeme, and ?sampleSense is unbound for some result, then I think the OPTIONAL in the outer query will produce all glosses that exist. I can’t think of a correct way to fix that, actually (the skos:definition triple needs to move inside the ontolex:sense OPTIONAL block, but then you’d need to do both SAMPLE(?sense) and SAMPLE(?gloss) in the same aggregation, and I think the two might select a sense and gloss that don’t match up), but maybe the sense isn’t actually necessary as a query output, in which case this should work: 
SELECT ?lexeme (GROUP_CONCAT(DISTINCT ?lemma; separator = "/") AS ?lemmata) (SAMPLE(?gloss) AS ?anyGloss) WHERE {
  SERVICE bd:sample {
    ?lexeme dct:language wd:Q8752.
    bd:serviceParam bd:sample.limit 20 .
  }
  ?lexeme wikibase:lemma ?lemma.
  OPTIONAL { ?lexeme ontolex:sense/skos:definition ?gloss. }
}
GROUP BY ?lexeme
Try it!
--TweetsFactsAndQueries (talk) 19:52, 4 March 2020 (UTC)
No, wait, I found a solution that gives you the ?sampleSense and corresponding ?anyGloss without exploding the results. It’s a bit hacky, and a lot slower, though, so I don’t recommend it unless you really need both. 
SELECT ?lexeme (GROUP_CONCAT(DISTINCT ?lemma; separator = "/") AS ?lemmata) ?sampleSense (SAMPLE(?gloss) AS ?anyGloss)
WITH {
  SELECT ?lexeme (SAMPLE(?sense) AS ?sampleSense) WHERE {
    SERVICE bd:sample {
      ?lexeme dct:language wd:Q8752.
      bd:serviceParam bd:sample.limit 20 .
    }
    OPTIONAL { ?lexeme ontolex:sense ?sense. }
  }
  GROUP BY ?lexeme
} AS %lexemesWithOneSense
WHERE {
  INCLUDE %lexemesWithOneSense.
  ?lexeme wikibase:lemma ?lemma.
  BIND(BOUND(?sampleSense) AS ?haveSampleSense) # if ?sampleSense is unbound, the result of the below OPTIONAL is meaningless
  BIND(?sampleSense AS ?sampleSense_) # copy of ?sampleSense that can be bound without harm (not included in GROUP BY / SELECT)
  OPTIONAL { ?sampleSense_ skos:definition ?gloss_. } # if ?sampleSense_ was unbound, it is now bound to any sense of any gloss
  BIND(IF(?haveSampleSense, ?gloss_, ?unbound) AS ?gloss) # use the gloss only if it belongs to the already known ?sampleSense
}
GROUP BY ?lexeme ?sampleSense
Try it!
--TweetsFactsAndQueries (talk) 20:12, 4 March 2020 (UTC)

Timeline US news

#defaultView:Timeline
SELECT ?candidate ?candidateLabel ?candidateDescription ?start (COALESCE(?end, NOW()) as ?to) ?img 
{
    BIND( wd:Q22923830 as ?el ) 
    ?el p:P726 ?st . 
    ?st ps:P726 ?candidate .
    OPTIONAL { ?st pq:P582 ?end }
    ?el wdt:P155 / wdt:P585 ?start . 
    OPTIONAL { ?candidate wdt:P18 ?img }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}  
ORDER BY BOUND(?end) DESC(?to)

Try it!

Is there a way to improve the timeline? --- Jura 23:46, 4 March 2020 (UTC)

List of 1000 female with the highest number of interwikis, but without a be:interwiki

Hello. Is it possible to create a query for a list of 1000 articles about female with the highest number of interwikis, but without a Belarusian (be:) interwiki? Thanks. --Maksim L. (talk) 07:09, 6 March 2020 (UTC) 

# Biełarusian women covered in other Wikipedias but not in Biełarusan
SELECT ?person ?personLabel ?personDescription (COUNT(DISTINCT ?sitelink) as ?linkcount) WHERE {
  ?person wdt:P31 wd:Q5 . # human
  ?person wdt:P21 wd:Q6581072 . # woman
  ?person wdt:P27 wd:Q184 . # Biełarusan
  ?sitelink schema:about ?person . # get Wikimedia pages about the person
  FILTER NOT EXISTS {
    ?links schema:about ?person . # get Wikimedia pages about the person
    ?links schema:isPartOf <https://be.wikipedia.org/> . # but exclude Biełarusan Wikipedia.
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],be-tarask,en,uk,pl,ru". }
} GROUP BY ?person ?personLabel ?personDescription
ORDER BY DESC(?linkcount) 
LIMIT 1000
Try it!

--Taravyvan Adijene (talk) 07:42, 6 March 2020 (UTC)

@Maksim L.: The following query gives you a list of objects that don't have a link to bewp. It is not limited to country of citizenship (P27) equal to Belarus (Q184).
SELECT ?item ?itemLabel ?itemDescription ?sitelinks WITH {
SELECT ?item ?sitelinks {
  ?item wdt:P31 wd:Q5 .
  ?item wdt:P21 wd:Q6581072 .
  ?item wikibase:sitelinks ?sitelinks .
  FILTER(?sitelinks>40)
  OPTIONAL { ?bewp schema:about ?item; schema:isPartOf <be.wikipedia.org/> }
  FILTER(!BOUND(?bewp))
}
ORDER BY DESC(?sitelinks)
LIMIT 1000
} AS %i WHERE {
  include %i
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?sitelinks) ?itemLabel
Try it!
Note:Links to all wikimedia project, not just Wikipedias, are included in the "sitelinks".
--Larske (talk) 07:51, 6 March 2020 (UTC)
The strategy is correct, but your query was not fully correct. This one works as desired: 
SELECT ?item ?itemLabel ?itemDescription ?sitelinks WITH {
  SELECT ?item ?sitelinks WHERE {
    ?item wdt:P21 wd:Q6581072; wikibase:sitelinks ?sitelinks .
    FILTER(?sitelinks > 30) .
    MINUS { ?item ^schema:about/schema:isPartOf <https://be.wikipedia.org/> }
  }
} AS %subquery WHERE {
  INCLUDE %subquery .
  ?item wdt:P31 wd:Q5 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} ORDER BY DESC(?sitelinks) LIMIT 1000
Try it!
 —MisterSynergy (talk) 08:11, 6 March 2020 (UTC)
Thanks for the correction! --Larske (talk) 08:21, 6 March 2020 (UTC)
And one more: if the ranking should be sorted according to Wikipedia sitelink counts only, this query would be the way to go. —MisterSynergy (talk) 08:23, 6 March 2020 (UTC)

Query no longer working

Until recently, the below query was working fine. Now, I have a java error. Is this issue linked to the query or to WQS? 

SELECT ?instance ?instanceLabel (COALESCE(?instanceLabel, " autres") AS ?itemLabel) ?count {{
    SELECT ?instance (COUNT(DISTINCT ?item) as ?count) {
          ?item  wdt:P17 wd:Q142. 
          ?item (wdt:P31/wdt:P279*) wd:Q16970 ; wdt:P31 ?instance.}
    GROUP BY ?instance
    HAVING (?count > 50)}
  UNION {
    SELECT (SUM(?count) AS ?count) {{
        SELECT (COUNT(DISTINCT ?item) as ?count) {
          ?item wdt:P17 wd:Q142. 
          ?item (wdt:P31/wdt:P279*) wd:Q16970 ; wdt:P31 ?instance.}
        GROUP BY ?instance
        HAVING (?count <=50)}}}
 SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }}
ORDER BY DESC(?count)
Try it!

Thanks. Ayack (talk) 19:08, 6 March 2020 (UTC)

You need to change "(SUM(?count) AS ?count)" to something like (SUM(?count) AS ?count1) --- Jura 19:12, 6 March 2020 (UTC)
By doing that, I no longer have an error, but I've only the result of the first part of the UNION... Ayack (talk) 19:27, 6 March 2020 (UTC)
SELECT ?type ?typeLabel (SUM(?count) as ?count_types) 
{
    {  SELECT ?instance (COUNT(DISTINCT ?item) as ?count)
       WHERE
       {
          ?item  wdt:P17 wd:Q142. 
          ?item (wdt:P31/wdt:P279*) wd:Q16970 ; wdt:P31 ?instance
       }
       GROUP BY ?instance
    }             
    BIND( If(?count < 51 , wd:Q55107540 , ?instance) as ?type) 
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr,en". }
}
GROUP BY ?type ?typeLabel 
ORDER BY (?type = wd:Q55107540 ) DESC(?count_types)

Try it!

There seems to be another issue with the aggregate. I re-wrote it slightly differently above. I suppose that's what you are looking for. --- Jura 19:41, 6 March 2020 (UTC) 


SELECT ?instance ?instanceLabel (COALESCE(?instanceLabel, " autres") AS ?itemLabel) ?count {{
    SELECT ?instance (COUNT(DISTINCT ?item) as ?count) {
          ?item  wdt:P17 wd:Q142. 
          ?item (wdt:P31/wdt:P279*) wd:Q16970 ; wdt:P31 ?instance.}
    GROUP BY ?instance
    HAVING (?count > 50)}
  UNION {
    SELECT (SUM(?count0) AS ?count) {{
        SELECT ?instance (COUNT(DISTINCT ?item) as ?count0) {
          ?item wdt:P17 wd:Q142. 
          ?item (wdt:P31/wdt:P279*) wd:Q16970 ; wdt:P31 ?instance.}
        GROUP BY ?instance
        HAVING (?count0 <=50)}}}
 SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }}
ORDER BY DESC(?count)

Try it! Original query edited. --- Jura 19:51, 6 March 2020 (UTC)

Thanks, it's what I expected. However it's strange that the original query has stop working... Ayack (talk) 20:02, 6 March 2020 (UTC)
See https://phabricator.wikimedia.org/T235540 --Tagishsimon (talk) 20:06, 6 March 2020 (UTC)
Note that reusing a name in an aggregate function is not allowed in SPARQL (see https://www.w3.org/TR/sparql11-query/#aggregateExample), so it should not be expected to work. However it would be nice to have a better error message. --Dipsacus fullonum (talk) 22:09, 6 March 2020 (UTC)
If so, I suppose the bug should be closed as "wont fix". The other day, I fixed a few queries that failed for that reason since the change in October. Some are probably still left on https://tools.wmflabs.org/listeria/botstatus.php . --- Jura 02:00, 7 March 2020 (UTC)
I would have closed it with "Invalid", as it isn't a bug. --Dipsacus fullonum (talk) 02:45, 7 March 2020 (UTC)

Simple request (Participants to the 2016 Olympics)

Hi, Id like a request that would list all the athletes participating to the 2016 Olympics (Q8613) Thanks--Kimdime (talk) 19:59, 7 March 2020 (UTC)

@ Kimdime: Answer 1 with 1,540 results, atheletes who participated using participant in (P1344) 2016 Summer Olympics (Q8613):
SELECT ?item ?itemLabel
WHERE
{
  ?item wdt:P31 wd:Q5.
  ?item wdt:P1344 wd:Q8613.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
And answer 2 with 4,582 results, athetles and teams who are in a subcategory of Category:Competitors at the 2016 Summer Olympics (Q24335239) at English Wikipedia:
SELECT ?title
WHERE 
{
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:api "Search" .
    bd:serviceParam wikibase:endpoint "en.wikipedia.org" .
    bd:serviceParam mwapi:srnamespace "0" .
    bd:serviceParam mwapi:srsearch 'deepcat:"Competitors at the 2016 Summer Olympics"' .
    ?title wikibase:apiOutput mwapi:title .
  }
}
Try it!
--Dipsacus fullonum (talk) 20:32, 7 March 2020 (UTC)
Dipsacus fullonum Thank you for showing me how to interrogate Wikipedia categories.--Kimdime (talk) 09:31, 8 March 2020 (UTC)
With this PetScan query we get 10,737 articles in the enwp category tree en:Category:Competitors at the 2016 Summer Olympics, and all of them seem to be connected to a Wikidata object. As an example, only 3 of the first 20 items in the "PetScan query" result are present in the "Search query" result.
@Dipsacus fullonum: Do you know the reason for this big discrepancy?
--Larske (talk) 09:40, 8 March 2020 (UTC)
@Larske: I don't know for sure but the search may have hit the limits of the "deepcat" search method in Wikipedia. See en:Help:Searching#deepcategory:. The help page says: "The depth of the tree is currently limited to 5 levels, and the overall number of categories is limited to 256." --Dipsacus fullonum (talk) 10:34, 8 March 2020 (UTC)
@Dipsacus fullonum: I think there must be some other problem with the deepcat search. The number of categories are just 40, well below 256, and the depth i just 2 levels (excluding the top level), well below 5. And if the limit(s) were hit, I think there should be a warning message on this search result page, but there isn't.
I noticed that PetScan misses the 474 articles on the subcategories to Sailors (379 articles i 8 subcategories) and Volleyball players (95 articles in one subcategory). The total number of articles in the category tree is 11,211.
--Larske (talk) 14:25, 8 March 2020 (UTC)
@Larske: The deepcat search only gives 4.582 results (see https://en.wikipedia.org/w/index.php?title=Special:Search&limit=5000&offset=0&ns0=1&search=deepcat%3A%22Competitors+at+the+2016+Summer+Olympics%22&advancedSearch-current={}) so the result isn truncated by the MWAPI service. Maybe there also is a limit on the total number of search results? --Dipsacus fullonum (talk) 14:45, 8 March 2020 (UTC)

Getting the lexeme for a given form

I'm trying to check whether the strings in titles of a set of publications already exist as lexemes and am stuck at the 

?lexeme ontolex:lexicalForm ?form

point. So here is what I have so far, with the problematic part commented out.

The following query uses these:

  • Properties: main subject (P921)     , title (P1476)     , image (P18)     
    ################
# Checking whether strings from the titles of publications already exist as lexemes
# The query has three parts:
#   I - get a list of publications on a given topic
#  II - extract strings from the titles
# III - check whether these strings exist as Wikidata lexemes
################

SELECT DISTINCT
  ?word ?wordUrl
  ?form ?formLabel
  ?lexeme ?lexemeLabel
  ?lexical_category ?lexical_categoryLabel
  (GROUP_CONCAT(DISTINCT ?featureLabel; separator=" // ") AS ?features)
  ?sense ?senseLabel
  (IRI(CONCAT("https://commons.wikimedia.org/w/index.php?title=Special:Redirect/file&width=100&wpvalue=", 
          SUBSTR(STR(SAMPLE(?images)), 52))) AS ?sense_image)
WHERE {

#   I - get a list of publications on a given topic
  
  {
    SELECT DISTINCT ?x ?title WHERE {
      ?x wdt:P921 wd:Q202864 ;  # Zika virus
         wdt:P1476 ?title.
      FILTER(STRLEN(?title) >= 6)
    }
    LIMIT 10
  }
  
#  II - extract strings from the titles
  
  BIND(LCASE(?title) AS ?ltitle)
  BIND(REPLACE(?ltitle, "^.*?(\\b\\w{6,}\\b).*$", "$1") AS ?w1)
  BIND(REPLACE(STRAFTER(?ltitle, ?w1), "^.*?(\\b\\w{6,}\\b).*$", "$1") AS ?w2)
  BIND(REPLACE(STRAFTER(?ltitle, ?w2), "^.*?(\\b\\w{6,}\\b).*$", "$1") AS ?w3)
  VALUES ?w_ { 1 2 3 }
  BIND(IF(?w_ = 1, ?w1, IF(?w_ = 2, ?w2, ?w3)) AS ?word)
  FILTER(REGEX(?word, "^\\w+$")) # since ?w may evaluate to an empty string, e.g. for one-word titles

  FILTER (LANG(?word) = "en")
  
# III - check whether these strings exist as Wikidata lexemes
# This part is taken from https://tools.wmflabs.org/ordia/text-to-lexemes
  
  OPTIONAL {
    ?form ontolex:representation ?word . 
    OPTIONAL {
      ?form wikibase:grammaticalFeature ?feature .
      BIND(STR(?feature) AS ?default_featureLabel)
      OPTIONAL {
        ?feature rdfs:label ?featureLabel_ .
        FILTER (LANG(?featureLabel_) = "en")
      }
      BIND(COALESCE(?featureLabel_, ?default_featureLabel) AS ?featureLabel)
    }
    ?form ontolex:representation ?formLabel .
  
# START OF PROBLEMATIC SECTION    
#    ?lexeme ontolex:lexicalForm ?form .
#
#    ?lexeme wikibase:lexicalCategory ?lexical_category .
#    BIND(STR(?lexical_category) AS ?default_lexical_categoryLabel)
#    OPTIONAL {
#      ?lexical_category rdfs:label ?lexical_categoryLabel_ .
#      FILTER (LANG(?lexical_categoryLabel_) = 'en')
#    }
#    BIND(COALESCE(?lexical_categoryLabel_, ?default_lexical_categoryLabel) AS
#	 ?lexical_categoryLabel)
#      
#	  
#    ?lexeme wikibase:lemma ?lexemeLabel .
#
#    OPTIONAL {
#      ?lexeme ontolex:sense ?sense .
#      BIND(SUBSTR(STR(?sense), 32) AS ?senseLabel)
#      OPTIONAL {
#        ?sense wdt:P18 ?images .
#      }
#    }
# END OF PROBLEMATIC SECTION    
    
  }
  BIND(IF(BOUND(?form), "", CONCAT("search?language=en&q=", ?word)) AS ?wordUrl)
  
}
GROUP BY
  ?word ?wordUrl ?form ?formLabel
  ?lexeme ?lexemeLabel ?lexical_category ?lexical_categoryLabel
  ?sense ?senseLabel
ORDER BY ?word
Try it!

Thanks for any pointers! --Daniel Mietchen (talk) 06:25, 7 March 2020 (UTC) 

SELECT (GROUP_CONCAT(DISTINCT ?title) as ?input_for_ordia)
WHERE
{
      [] wdt:P921 wd:Q202864 ; wdt:P1476 ?title .
      FILTER(lang(?title) = "en" )
}

Try it!

I guess that's not what you are looking for, but you could just paste the result of above into https://tools.wmflabs.org/ordia/text-to-lexemes
Ordia then does the single string to array conversion --- Jura 12:58, 7 March 2020 (UTC)
.. you could place the query in a Listeria list and then use LUA to generate the query or link to Ordia. --- Jura 13:08, 7 March 2020 (UTC)
@Jura1: Thanks, Jura — your suggestions are not precisely what I wanted, but still a massive inspiration! Pasting into Ordia's text to lexemes is what I have done so far, and I am using Listeria queries a lot for testing such things (example) but here specifically, I was wondering how this could be streamlined and perhaps triggered by some missing page on Scholia, as per this ticket. Thanks again! --Daniel Mietchen (talk) 22:44, 7 March 2020 (UTC)
@Daniel Mietchen: I think the last one below does what you had asked for ("to check whether the strings in titles of a set of publications already exist as lexemes"). It can run on all Zika article that currently exist. It shouldn't be too complicated to invert it (list the words that exist). --- Jura 23:04, 7 March 2020 (UTC)
@Jura1: That last query is indeed very close to what I wanted (which is not entirely spelled out above), but it differs in some details, e.g. it does not list the corresponding lexeme's identifier. But this set of queries of yours has inspired me for several days, and as soon as I got the draft query above to work by adding some more OPTIONAL clauses to the problematic part, I started experimenting with incorporating some of your solutions. Thanks again! --Daniel Mietchen (talk) 01:37, 10 March 2020 (UTC)
@Daniel Mietchen: I added another query that lists the lexical categories of corresponding lexemes (and one of these lexemes). If no lexeme is found, placeholder (L254535) is used. Maybe this could work for Scholia.
I think we still need to find a good way to handle taxon names (see Property talk:P225#IETF tag for values and its parts e.g.).
I revised the other queries slightly. --- Jura 13:29, 11 March 2020 (UTC)
  • If it's not available yet, a service that does that on WQS would be good to have.
Maybe with a federation query, one could try to do the conversion on another SPARQL server that has a corresponding extension installed (if there is).
There are probably a few complicated ways of solving this with existing functions, e.g. count word delimiters, extract each word, combine that .. --- Jura 15:15, 7 March 2020 (UTC)


How many words to check

#How many words are in these titles:
SELECT ?wordcount (COUNT(?title) as ?titles_count)
{
      [] wdt:P921 wd:Q202864 ; wdt:P1476 ?title .
      FILTER(lang(?title) = "en" )
      BIND(REPLACE(str(?title), " ", "") as ?test)
      BIND(strlen(str(?title)) - strlen(?test)+1 as ?wordcount)
}
GROUP BY ?wordcount
ORDER BY DESC(?wordcount)

Try it! 

SELECT ?wordcount (COUNT(?title) as ?titles_count)
{
      { ?a wdt:P921 wd:Q84263196 } UNION { ?a wdt:P921 wd:Q81068910 }  UNION { ?a wdt:P921 wd:Q1134583 }  
      ?a wdt:P1476 ?title .
      BIND(REPLACE(str(?title), " ", "") as ?test)
      BIND(strlen(str(?title)) - strlen(?test)+1 as ?wordcount)
}
GROUP BY ?wordcount
ORDER BY DESC(?wordcount)

Try it!

List words

#defaultView:BubbleChart
#Bubblechart and list of words in titles of articles about Zika. Limited to English titles, first 42 words, some words filtered.
#by Jura1, 7 March 2020

SELECT ?word (COUNT(DISTINCT ?title) as ?count)
WITH
{  SELECT ?title (REPLACE(CONCAT(REPLACE(REPLACE(REPLACE(str(?title),'[\\.:,\\? %";\\]\\[]',""),"[\\(\\)–“—”]"," "),"’","'"), 
  " Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z")
  ,"[ ]{2,100}"," ") as ?input)
   {  [] wdt:P921 wd:Q202864 ; wdt:P1476 ?title . FILTER(lang(?title)="en") } #  LIMIT 100
} as %titles
WITH
{    SELECT ?re ?nv { ?n wdt:P5176 []; wdt:P1181 ?nv . FILTER( ?nv < 43) 
     BIND( CONCAT( "^([^ ]+ ){", str( ?nv - 1 ),"}([^ ]+) .*") as ?re) }
} as %positionalregexes                                                             
WITH
{    SELECT * { INCLUDE %titles  INCLUDE %positionalregexes  BIND( REPLACE(?input, ?re, "$2") as ?word) }
} as %b 
WHERE
{
    INCLUDE %b
    FILTER ( ?word != "Z" && ?word != "Zika" && ?word != "Corona" 
            && strlen(?word) > 3 
            && ?word != "pandemic" && ?word != "virus" && lcase(?word) != "infection" && ?word != "mortality"
           )               
}
GROUP BY ?word

Try it!

As a starter .. --- Jura 16:40, 7 March 2020 (UTC)

Cross-check against existing lexemes

PREFIX lhasform: <http://www.w3.org/ns/lemon/ontolex#representation>
PREFIX llang: <http://purl.org/dc/terms/language>
PREFIX lforms:   <http://www.w3.org/ns/lemon/ontolex#lexicalForm>

# List words of some titles that don't exists as lexeme form 
#  limited to the 42 first words, English language titles
#  by Jura1, 7-11 March 2020

SELECT ?word ?count ?sample
WITH
{  SELECT ?a ?title (REPLACE(CONCAT(REPLACE(REPLACE(REPLACE(str(?title),'[\\.:,\\? %";\\]\\[]',""),"[\\(\\)–“—”]"," "),"’","'"), 
  " Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z")
  ,"[ ]{2,100}"," ") as ?input)
  #skip some characters in title
  #pad with " Z" in case the title is shorter than 42 words. "Z" is filtered out later. 
  WHERE
   {
      { ?a wdt:P921 wd:Q84263196 } UNION { ?a wdt:P921 wd:Q81068910 }    UNION { ?a wdt:P921 wd:Q57751738 } 
     UNION { ?a wdt:P921 wd:Q1134583 }  UNION { ?a wdt:P921 wd:Q82069695 }    UNION { ?a wdt:P921 wd:Q290805 }
      # above for COVID-19, use the following for ZIKA instead, add other items as needed
      # ?a wdt:P921 wd:Q202864 .
      ?a wdt:P1476 ?title . FILTER(lang(?title)="en") }
} as %titles
WITH
{    SELECT ?re ?nv WHERE { ?n wdt:P5176 []; wdt:P1181 ?nv . FILTER( ?nv < 43) 
     BIND( CONCAT( "^([^ ]+ ){", str( ?nv - 1 ),"}([^ ]+) .*") as ?re) }
} as %positionalregexes                                                            
WITH
{    SELECT * { INCLUDE %titles  INCLUDE %positionalregexes  BIND( strlang(REPLACE(?input, ?re, "$2"),"en") as ?word) }
} as %getwords
WITH
{  SELECT ?word (COUNT(DISTINCT ?title) as ?count) (SAMPLE(?title) as ?sample) WHERE { INCLUDE %getwords FILTER ( ?word != "Z"@en ) } GROUP BY ?word
} as %countedwords
WHERE
{
    INCLUDE %countedwords
    hint:Query hint:optimizer "None".
  
    # check against English forms of lexemes ("en")
    FILTER NOT EXISTS { ?lf lhasform: ?word 
      # ?lexeme lforms: ?lf ; llang: wd:Q1860 .
    }

    # check against lowercase forms 
    BIND( Lcase(?word) as ?lword)
    FILTER NOT EXISTS { ?lf lhasform: ?lword }
  
    # check against British English lowercase forms ("en-gb")
    BIND( strlang(Lcase(str(?word)),"en-gb") as ?lword_engb)
    FILTER NOT EXISTS { ?lf lhasform: ?lword_engb }  

    # exclude digits-only strings and 1-letter strings
    FILTER ( !REGEX( str(?word), "^\\d+$" )  )
    FILTER ( strlen( str(?word)) != 1  ) 
}

Try it! Another approach, checking against lexemes. --- Jura 19:03, 7 March 2020 (UTC)

Article titles and corresponding lexemes (or "placeholder")

PREFIX llemma:   <http://wikiba.se/ontology#lemma>
PREFIX lcat:     <http://wikiba.se/ontology#lexicalCategory>
PREFIX llang:    <http://purl.org/dc/terms/language>
PREFIX lforms:   <http://www.w3.org/ns/lemon/ontolex#lexicalForm>
PREFIX lhasform: <http://www.w3.org/ns/lemon/ontolex#representation>
PREFIX lfeature: <http://wikiba.se/ontology#grammaticalFeature>
PREFIX lsenses:  <http://www.w3.org/ns/lemon/ontolex#sense>
PREFIX lglos:    <http://www.w3.org/2004/02/skos/core#definition>

# List words of in some titles and corresponding lexemes (or "placeholder") 
#  limited to the 42 first words & English language titles
#  includes glos for any of the senses already defined on one of the lexemes (more generally needed)
#  by Jura1, 10 March 2020

SELECT
  ?a ?title ?nv ?word (GROUP_CONCAT(DISTINCT ?lemma; separator="/") as ?all_lemma) 
  (SAMPLE(?lexeme) as ?a_lexeme) 
  (GROUP_CONCAT(DISTINCT ?lexcatLabel; separator=", ") as ?all_lexcat) 
  (COUNT(DISTINCT ?sense) as ?senses)
  (GROUP_CONCAT(DISTINCT ?glos_en; separator=" [other sense: ]_") as ?all_glos_en) 
WITH
{  SELECT ?a ?title (REPLACE(CONCAT(REPLACE(REPLACE(REPLACE(str(?title),'[\\.:,\\? %";\\]\\[]',""),"[\\(\\)–“—”]"," "),"’","'"), 
  " Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z Z")
  ,"[ ]{2,100}"," ") as ?input)
  #skip some characters in title
  #pad with " Z" in case the title is shorter than 42 words. "Z" is filtered out later. 
  WHERE
   {
      # { ?a wdt:P921 wd:Q84263196 } UNION { ?a wdt:P921 wd:Q81068910 }    UNION { ?a wdt:P921 wd:Q57751738 } 
      # UNION { ?a wdt:P921 wd:Q1134583 }  UNION { ?a wdt:P921 wd:Q82069695 }    UNION { ?a wdt:P921 wd:Q290805 }
      # above for COVID-19, use the following for ZIKA instead, add other items as needed
      ?a wdt:P921 wd:Q202864 .
      ?a wdt:P1476 ?title . FILTER(lang(?title)="en") } OFFSET 1000 LIMIT 200
} as %titles
WITH
{    SELECT ?re ?nv WHERE { ?n wdt:P5176 []; wdt:P1181 ?nv . FILTER( ?nv < 43) 
     BIND( CONCAT( "^([^ ]+ ){", str( ?nv - 1 ),"}([^ ]+) .*") as ?re) }
} as %positionalregexes                                                            
WITH
{    SELECT * { INCLUDE %titles  INCLUDE %positionalregexes  BIND( strlang(REPLACE(?input, ?re, "$2"),"en") as ?word) }
} as %getwords
WHERE
{   INCLUDE %getwords
    hint:Query hint:optimizer "None".
    FILTER ( ?word != "Z"@en )
    # check against English forms of lexemes ("en")
    OPTIONAL { ?lf lhasform: ?word . ?lexeme1 lforms: ?lf ; llang: wd:Q1860 }

    # check against lowercase forms 
    BIND( Lcase(?word) as ?lword)
    OPTIONAL { ?lf lhasform: ?lword . ?lexeme2 lforms: ?lf ; llang: wd:Q1860 }

    # check against noun forms with 's (incomplete, not en-gb, not different cases)
    BIND( strlang(REPLACE(str(?word),"^(.+)'s$", "$1"),"en") as ?noun)
    OPTIONAL { ?lf lhasform: ?noun . ?lexeme5 lforms: ?lf ; llang: wd:Q1860 ; lcat: wd:Q1084 }
 
    # check against British-English lowercase forms ("en-gb")
    BIND( strlang(Lcase(str(?word)),"en-gb") as ?lword_engb)
    OPTIONAL { ?lf lhasform: ?lword_engb . ?lexeme3 lforms: ?lf ; llang: wd:Q1860 }
  
    # exclude digits-only strings and 1-letter strings
    FILTER ( !REGEX( str(?word), "^\\d+$" )  )
    FILTER ( strlen( stR(?word)) != 1  ) 
 
    BIND( COALESCE( ?lexeme1, ?lexeme2, ?lexeme5, ?lexeme3, wd:L254535 ) as ?lexeme)
    ?lexeme llemma: ?lemma ; lcat: ?lexcat .
    ?lexcat rdfs:label ?lexcatLabel . FILTER(lang(?lexcatLabel) = "en") .
    OPTIONAL { ?lexeme lsenses: ?sense .
               OPTIONAL { ?sense lglos: ?glos_en . FILTER(lang(?glos_en) = "en")   }
             }
}
GROUP BY ?a ?title ?nv ?word
ORDER BY ?a ?nv

Try it!

"has part/part of" tree

Is there any way to query a multilevel "has part/part of" tree (show me all of the parts of A + all of the parts of parts of A etc.). I'd like to generate a Listeria list of all of the tapestries in the series Grotesques (Q84956427) (I have more to add). - PKM (talk) 20:45, 10 March 2020 (UTC)

@PKM: easy one :
select ?part {
  ?part wdt:P361* wd:Q84956427 .
}
Try it!
If you need something more sophisticated like a sorting according to the imbrication of the parts, it may be possible. author  TomT0m / talk page 20:50, 10 March 2020 (UTC)
It might also be valuable to display this result as a graph (external tool result). —MisterSynergy (talk) 21:01, 10 March 2020 (UTC)
@TomT0m, MisterSynergy: Thank you both! This is just what I needed. (I have soooo many of these to work on...) I am pleased to see "has part" works the same way as subclasses. :-) - PKM (talk) 21:12, 10 March 2020 (UTC)
Also as a graph, query service : 
#defaultView:Graph
select ?part ?partLabel ?partParent{
  ?part wdt:P361* wd:Q84956427 .
  ?part wdt:P361 ?partParent .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
author  TomT0m / talk page 21:21, 10 March 2020 (UTC)

Every 'instance of' X which has more than one 'instance of' statement and a list of values of these 'instance of' stataments

Don't know if it's even possible, but maybe it is:

  1. Every instance of (P31)chemical compound (Q11173)
  2. that has another instance of (P31) statement
  3. and the result of the query should be a list of such values from #2
  4. optionally: with a number of items that has such statement

As an example: D-methamphetamine (Q191924) has (beside instance of (P31)chemical compound (Q11173)):

And I need a list in which I would have:

I want to know which other 'instance of' values are present in items classified as chemical compound (Q11173). Wostr (talk) 22:37, 10 March 2020 (UTC)

@Wostr:

First one, the list of compound with other instance of value : 

select ?item {
  ?item wdt:P31 wd:Q11173 filter exists {?item wdt:P31 ?class filter (?class != wd:Q11173) }
}
Try it!

Second one, the list of those values : 

select distinct ?class {
  ?item wdt:P31 wd:Q11173, ?class filter (?class != wd:Q11173) .
}
Try it!

author  TomT0m / talk page 10:36, 11 March 2020 (UTC)

Thanks, didn't know it is so simple :) Wostr (talk) 14:18, 11 March 2020 (UTC)

Catalan label and Roman people

Hi! I need a query listing all items

Thanks, --Epìdosis 09:53, 11 March 2020 (UTC) 

SELECT ?item ?roman_praenomen ?enlabel WHERE {
  ?item wdt:P2358 ?roman_praenomen .
  ?item rdfs:label ?enlabel .
  FILTER(LANG(?enlabel) = 'en') .
  ?item rdfs:label ?calabel .
  FILTER(LANG(?calabel) = 'ca') .
  FILTER(STR(?enlabel) = STR(?calabel)) .
  OPTIONAL {
    ?item rdfs:label ?lalabel .
    FILTER(LANG(?lalabel) = 'la') .
  }
  FILTER(!BOUND(?lalabel)) .
}
Try it!

 —MisterSynergy (talk) 10:01, 11 March 2020 (UTC)

Timeout, but has been running two weeks ago every day.

select ?sitelinks ?itemLabel ?itemDescription ?item
         with {
            select distinct $item
            where {
               values ?item_class {wd:Q33999 wd:Q191796 wd:Q281525 wd:Q465501 wd:Q622807 wd:Q658371 wd:Q674067 wd:Q850978 wd:Q851903 wd:Q895818 wd:Q948329 wd:Q1148872 wd:Q1314140 wd:Q1381612 wd:Q1416106 wd:Q1425448 wd:Q1707847 wd:Q1765879 wd:Q2405480 wd:Q2479076 wd:Q2782358 wd:Q2833480 wd:Q3178518 wd:Q3437937 wd:Q3446340 wd:Q3625363 wd:Q3775957 wd:Q4388180 wd:Q4677709 wd:Q4789398 wd:Q4881007 wd:Q4961740 wd:Q5160063 wd:Q6508764 wd:Q7244915 wd:Q10798782 wd:Q11481802 wd:Q14786283 wd:Q16078681 wd:Q16830339 wd:Q18086706 wd:Q18706995 wd:Q19708268 wd:Q20856740 wd:Q21280562 wd:Q22343421 wd:Q24641989 wd:Q25229942 wd:Q25970041 wd:Q28806161 wd:Q28806402 wd:Q28806737 wd:Q30184815 wd:Q42273048 wd:Q50236774}
               ?item wdt:P106 ?item_class; wdt:P21 wd:Q6581072; wdt:P31 wd:Q5.
               minus {
                  ?article schema:about ?item; schema:isPartOf <https://de.wikipedia.org/>.
               }
					minus {?item wdt:P106 wd:Q488111.}
					minus {?item wdt:P106 wd:Q852857.}
            }
         } as %subquery
         where {
            include %subquery
            bind(xsd:integer(substr(str(?item), 33)) as ?num).
            ?item wikibase:sitelinks ?sitelinks
            service wikibase:label {bd:serviceParam wikibase:language 'de'.}
         }
         order by desc(?sitelinks) asc(?num)
Try it!

Hi! This runs into timeout since about 15 days. Before I got good results. How have I to optimize this query to get results? Thank you, Doc Taxon (talk) 13:48, 11 March 2020 (UTC)

@Doc Taxon: I suggest you move "?item wikibase:sitelinks ?sitelinks" to the bottom of the subquery and add e.g. "filter(?sitelinks >= 5)". Without the filter you get 87.124 results. I doubt very much that you can use use so many results anyway and with a limit you avoid the timeout. I would also drop "bind(xsd:integer(substr(str(?item), 33)) as ?num)." and order by "asc(num)". Why does the ordering by QID matter? --Dipsacus fullonum (talk) 23:22, 11 March 2020 (UTC)
filter of sitelinks is a good way, thank you Doc Taxon (talk) 22:59, 12 March 2020 (UTC)

  OK Doc Taxon (talk) 22:59, 12 March 2020 (UTC)

Revenue: Newest Number and Qualifier

I have a quite simple query to find all German Nonprofits (Vereine) and their total revenue: https://w.wiki/KFz 

#German Vereine (Nonprofit Organisations) by Revenue
SELECT ?OrgaLabel ?Revenue
WHERE 
{
  ?Orga wdt:P1454 wd:Q9299236 . # Select German Nonprofit Organisations
  ?Orga wdt:P2139 ?Revenue . # Select Revenue
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en".}
}

ORDER BY DESC(?Revenue)
LIMIT 500
Try it!

Now I want to ensure, that only the latest Revenue is shown, even so sometimes old entries have a higher Rank. And I would like to add a column with the Year (Qualifier point in time P585 of the Revenue P2139. Thanks for your help! --Newt713 (talk) 13:56, 12 March 2020 (UTC)

@Newt713: This query only shows the latest revenue independent of rank. I added columns for year, valuta and statement rank. If it is too much information, the extra columns are easy to remove again.
#German Vereine (Nonprofit Organisations) by Revenue
SELECT ?OrgaLabel ?Revenue (YEAR(?Renevue_time) AS ?year) ?ValutaLabel ?rank
WHERE 
{
  ?Orga wdt:P1454 wd:Q9299236 . # Select German Nonprofit Organisations
  ?Orga p:P2139 ?Revenue_statement .
  ?Revenue_statement psv:P2139 ?Renevue_value .
  ?Renevue_value wikibase:quantityAmount ?Revenue .
  ?Renevue_value wikibase:quantityUnit ?Valuta .
  ?Revenue_statement pq:P585 ?Renevue_time .
  ?Revenue_statement wikibase:rank ?rank .
  FILTER NOT EXISTS
  {
    ?Orga p:P2139/pq:P585 ?Renevue_time2 .
    FILTER(?Renevue_time2 > ?Renevue_time)
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en".}
}

ORDER BY DESC(?Revenue)
LIMIT 500
Try it!
--Dipsacus fullonum (talk) 14:56, 12 March 2020 (UTC)
Perfect. With the filter even more complicated than I was hoping for. Thank you! --Newt713 (talk) 16:32, 12 March 2020 (UTC)
Df's query is also instructive, in that instead of reaching for some sort of MAX-based solution - which might seem the obvious direction of travel - the query instead asks, somewhat nonchalantly, whether there's a possibility that there's a ?Renevue_time2 greater than ?Renevue_time in which case would ?Renevue_time & all that sails with it kindly step down. Lovely. --Tagishsimon (talk) 16:43, 12 March 2020 (UTC)
Nice query indeed. Wonder why I would have tried with MAX(). --- Jura 23:43, 12 March 2020 (UTC)
@Tagishsimon, Jura1: Thank you for the nice words. I never really considered using the MAX aggregation function because the revenue from the found time would have to be fetched afterwards, forcing the aggregation into a subquery. Besides a filter (albeit simpler) would be needed in the outer query anyway. Here is code with MAX() for comparison. The code with MAX() is a little bit slower than my original query above (1.7 s vs. 1.5 s). If the revenue for the time wasn't needed, it of course was a different matter.
#Alternative version of the query using MAX()
#German Vereine (Nonprofit Organisations) by Revenue
SELECT ?OrgaLabel ?Revenue (YEAR(?Renevue_time) AS ?year) ?ValutaLabel ?rank
WHERE 
{
  {
    SELECT ?Orga (MAX(?time) AS ?Latest_time)
    WHERE
    {
      ?Orga wdt:P1454 wd:Q9299236 . # Select German Nonprofit Organisations
      ?Orga p:P2139/pq:P585 ?time .
    }
    GROUP BY ?Orga
  }
  ?Orga p:P2139 ?Revenue_statement .
  ?Revenue_statement pq:P585 ?Renevue_time .
  FILTER(?Renevue_time = ?Latest_time)
  ?Revenue_statement psv:P2139 ?Renevue_value .
  ?Renevue_value wikibase:quantityAmount ?Revenue .
  ?Renevue_value wikibase:quantityUnit ?Valuta .
  ?Revenue_statement wikibase:rank ?rank .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en".}
}

ORDER BY DESC(?Revenue)
LIMIT 500
Try it!
--Dipsacus fullonum (talk) 10:27, 13 March 2020 (UTC)
For films, queries I wrote look for MIN() on publication dates. The above approach seems more efficient. I think I should rewrite many queries I did for WikiProject Movies. --- Jura 10:44, 13 March 2020 (UTC)

Items with more than 30 sitelinks

Hello. I want to find all items that:

1) have article in en.wikipedia

2) don't have article in el.wikipedia

3) have more than 30 sitelinks.

Xaris333 (talk) 13:53, 13 March 2020 (UTC)

I haven't been able to make a query as described that wouldn't timeout. There are way too many results. I suggest more restraints to the items to limit the number of results. E.g. higher value for sitelinks, items with certain properties, allow all kind of pages on Wikipedia instead of only articles (so no testing for namespace is required) or other things. --Dipsacus fullonum (talk) 18:13, 13 March 2020 (UTC)
# Items in ENWP, not in ELWP, with >30 sitelinks
SELECT ?x ?count WHERE {
   <https://en.wikipedia.org/> ^schema:isPartOf/schema:about ?x .
  MINUS { <https://el.wikipedia.org/> ^schema:isPartOf/schema:about ?x . } 
  ?x wikibase:sitelinks ?count .
  FILTER(?count > 30)
} LIMIT 10000
Try it!

I can't get you all of them, but I can get you many of them. Bovlb (talk) 00:20, 14 March 2020 (UTC)

Well, it is some of them and some not of them as the query by Bovlb also includes items with with pages on enwiki that isn't articles (categories, templates, disambiguation pages etc.) --Dipsacus fullonum (talk) 05:35, 14 March 2020 (UTC)

Good point. 

# Items in ENWP, not in ELWP, with >30 sitelinks
SELECT ?x ?count WHERE {
   <https://en.wikipedia.org/> ^schema:isPartOf/schema:about ?x .
  MINUS { <https://el.wikipedia.org/> ^schema:isPartOf/schema:about ?x . } 
  MINUS { 
    VALUES ?type { wd:Q4167836 wd:Q11266439 wd:Q4167410 wd:Q17442446 }
    ?x wdt:P31 ?type .
  }
  ?x wikibase:sitelinks ?count .
  FILTER(?count > 30)
} LIMIT 10000
Try it!

This one attempts to exclude some of those, but it doesn't do a complete job. Unfortunately, our taxonomic hierarchy is too polluted for useful filtering. Bovlb (talk) 06:42, 14 March 2020 (UTC)

Yeah, I gave up trying to covince people to use just one for templates and categories ..
Maybe Quarry could do this. --- Jura 08:45, 14 March 2020 (UTC)
What surprises me is that we can get 177997 results in 3086 ms for the basic linkcount >30 element, but can't sort out the EL / EN sitelink requirements in the remaining 57 seconds, e.g. by way of a named subquery.
select ?item WHERE 
{
  ?item wikibase:sitelinks ?linkcount .
  values ?linkcount {
31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 
61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 
91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 
116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 
138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 
160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 
182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 
204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 
226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 
248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 
270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 
292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 
314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 
336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 
358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 
380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 
402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 
424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 
446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 
468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 
490 491 492 493 494 495 496 497 498 499 
  }
}
Try it!
--Tagishsimon (talk) 11:12, 14 March 2020 (UTC)

Pageviews of a given item in a given endpoint

Is it possible, via MWAPI, to get the average pageviews in the last pvipdays of a given item article in a given endpoint?

No, it is only a few things like title, item, pageid, lastrevid, timestamp and category you can get via MWAPI. Please see the manual at mw:Wikidata Query Service/User Manual/MWAPI#Supported services for details. --Dipsacus fullonum (talk) 17:51, 13 March 2020 (UTC)
See also phab:T174981. Bovlb (talk) 23:44, 13 March 2020 (UTC)
@Dipsacus fullonum, Bovlb: You can get some pageviews data – you can (ab)use the allpages generator to “generate” exactly one page (gapfrom=title + gaplimit=1), and then specify custom props to get whatever information you want from the result for that page, including pageviews, as long as you can write the right XPath to extract the number out of the full result: 
SELECT ?pageviews WHERE {
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Generator";
                    wikibase:endpoint "en.wikipedia.org";
                    wikibase:limit "once";
                    mwapi:generator "allpages";
                    mwapi:gapfrom "Coronavirus";
                    mwapi:gaplimit "1";
                    mwapi:prop "pageviews";
                    mwapi:pvipdays "2".
    ?pageviews wikibase:apiOutput "pageviews/pvip/text()".
  }
  FILTER(?pageviews != "1") # no idea where the "1" result comes from :/
}
Try it!
Unfortunately, this only gives you one pageviews number (for the previous day, I believe) – I couldn’t figure out how to get more results with a higher pvipdays parameter, it always just seems to return the first one. --TweetsFactsAndQueries (talk) 18:38, 15 March 2020 (UTC)

Playing with references

Could you help me displaying stated in (P248) (when there is one) in the query below, please? 

SELECT ?item ?coord ?reference
WHERE
{
	?item	wdt:P31/wdt:P279* wd:Q484170;
				wdt:P17 wd:Q142; wdt:P625 ?coord.
  OPTIONAL { ?item p:P625 [ ps:P625 ?coord; prov:wasDerivedFrom [ pr:P248 ?reference ] ] }.
}
Try it!

Thanks, Ayack (talk) 13:15, 16 March 2020 (UTC)

@Ayack: The error was that ps:p625 should be ps:P625 with upper case P. I have corrected it in the query. --Dipsacus fullonum (talk) 15:12, 16 March 2020 (UTC)
Thanks Dipsacus fullonum, I was looking at something much more complicated. Ayack (talk) 15:58, 16 March 2020 (UTC)

List of city residents, with street names

How to generate a list of residents of a city, with the name of the street each person lived on in that city? For example, Fontane (Q76632) lived on Große Hamburger Strasse (Q2704118) in Berlin. How to include street name in a list such as this one: < https://w.wiki/KiD >? Thanks -- M2545 (talk) 16:16, 18 March 2020 (UTC)

Pretty sure we don't normally include that data at all. --Yair rand (talk) 18:19, 18 March 2020 (UTC)
@M2545: This query find street names for people living in Berlin. There is only 13 results as there is very few data for people living in streets as pointed out by Yair rand. Besides it seems to me that located on street (P669) is used in a way not according the property description giving too many results.
SELECT DISTINCT ?person ?personLabel ?street ?streetLabel
WHERE
{
  {
    SELECT ?person ?street
    WHERE
    {
      ?person wdt:P31 wd:Q5. # Is a human
      ?person wdt:P551/wdt:P669? ?street. # Lives/lived in this place
      ?street wdt:P17 wd:Q183. # in Germany
      ?street wdt:P131+ wd:Q64. # ... located in Berlin
        hint:Prior hint:gearing "forward". 
      ?street wdt:P31/wdt:P279* wd:Q34442. # ... which must be a street
        hint:Prior hint:gearing "forward".
    }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de,en".}
}
Try it!
--Dipsacus fullonum (talk) 22:48, 18 March 2020 (UTC)
@Dipsacus fullonum: Thanks very much! -- M2545 (talk) 23:07, 18 March 2020 (UTC)

All Encompassing Query

Is it possible to make a query that will return a list of all an items properties and values? For example, I would like to make a query that returns all cats, but not just their names and q numbers, but rather every property and value associated with them. And just to be a little clearer, obviously not all cats will have the same properties, therefore I would only like to receive the properties that each specific cat has. Thank You. 

SELECT ?cat ?catLabel ?property ?propertyLabel ?value ?valueLabel
WHERE
{
  ?cat wdt:P31 wd:Q146. # Cats
  ?cat ?truthy_claim ?value. # Get a truthy claim and the value
  ?property wikibase:directClaim ?truthy_claim. # Get property item for a truthy claim 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".}
}
Try it!
--Dipsacus fullonum (talk) 06:02, 20 March 2020 (UTC)

Wikipedia categories and sub-categories

Hello,

I would like to be able to query a Wikipedia category so that the result would be all articles within the category and any subcategories.

This example queries the the category Pargas (kommun) (a muncipality in Finland): now it returns all articles and sub-categories in that category, but I would like it to return all articles from it's sub-categories. How would that be possible?

The Wikidata Query Service User Manual MWAPI describes some of the query, but not enough for me to figure out the above.

Hi. Please see my answer in the section #Simple request (Participants to the 2016 Olympics) above. It uses the deeepcat search function in a Wikipedia. As discussed the result there isn't complete, but as long as the category is relatively small the method should work fine. --Dipsacus fullonum (talk) 11:16, 20 March 2020 (UTC)

Instance of (P31) Disease Outbreak (Q3241045) of (P642) COVID-19 (Q84263196)

Hi all,

I'm trying to get these two queries:

  • All Items that has Instance of (P31) Disease Outbreak (Q3241045) of (P642) COVID-19 (Q84263196). Results should include items like: Q81068910, Q84104992, etc.
  • More general, I would like to get all Wikidata Items that has some type of connection with COVID-19 (Q84263196), and get the type of connection. Results should include all items appearing in the "What links here", but adding the information about the information about the relationship between the items found and Q84263196. I'm trying something like this, but is not the excepted results. The result that I'm looking for for should be something like:
      • 2020 coronavirus pandemic in Italy (Q84104992) Instance of (P31) Disease Outbreak (Q3241045) of (P642) COVID-19 (Q84263196)
      • Tom Hanks (Q2263) Has Medical Condition (P1050) COVID-19 (Q84263196)
      • etc

Thanks in Advance Diego (WMF) (talk) 15:04, 20 March 2020 (UTC)

@Diego (WMF): Are you thinking about something like this?
# All truthy statements with COVID-19 (Q84263196) as value.
SELECT ?item ?itemLabel ?property ?propertyLabel
WHERE
{
  ?item ?claim wd:Q84263196.
  ?property wikibase:directClaim ?claim.
   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Dipsacus fullonum (talk) 16:32, 20 March 2020 (UTC)
@Diego (WMF): The previous query was for all truthy statements with COVID-19 as value. Here is a query for all statements with item, property, value, rank and qualifier which have COVID-19 as a qualifier value:
# All statements with item, property, value and rank with COVID-19 (Q84263196) as value for qualifier.
SELECT ?item ?itemLabel ?property ?propertyLabel ?value ?valueLabel ?rank ?qualifier ?qualifierLabel
WHERE
{
  ?item ?claim ?statement.
  ?property wikibase:claim ?claim.
  ?property wikibase:statementProperty ?sprop.
  ?statement ?sprop ?value.
  ?statement wikibase:rank ?rank.
  ?statement ?qprop wd:Q84263196.
  ?qualifier wikibase:qualifier ?qprop.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Dipsacus fullonum (talk) 16:59, 20 March 2020 (UTC)
@Dipsacus fullonum: A-M-A-Z-I-N-G!!! Thank you very much Diego (WMF) (talk) 17:05, 20 March 2020 (UTC)
@Dipsacus fullonum: abusing from your knowledge, do you if is possible to get the timestamp when each statements with item, property, value and rank with COVID-19 was added? I mean, get the timestamp when the relationship was created.
@Diego (WMF): That is not possible with Wikidata Query Service. You can get the time an item was last modified (use ?item schema:dateModified ?dateModified.) so any statements for the item cannot have been added after that date. For more detailed information you will have to analyze the item's history. You can get the raw data for an analysis from either the database directly (using SQL), database dumps or the MediaWiki API. I have no knowledge of any tools doing that. --Dipsacus fullonum (talk) 20:03, 20 March 2020 (UTC)

List of the websites of all cities in Germany

I want to retrieve a list of all the websites of german cities. With this I want to find links to all Corona related information. Example: For https://de.wikipedia.org/wiki/Altdorf_bei_N%C3%BCrnberg I want to get the URL/Website link "www.altdorf.de". Also included should be the city name, the coordinates and the state ("Bundesland"). Thanks it advance. 

SELECT ?item ?itemLabel ?website ?coordinates ?BundeslandLabel
WHERE
{
  ?item wdt:P31 wd:Q42744322. # Item is German city
  ?item wdt:P856 ?website.
  OPTIONAL { ?item wdt:P625 ?coordinates. }
  ?item wdt:P131+ ?Bundesland.
  ?Bundesland wdt:P31 wd:Q1221156.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de,en". }
}
Try it!
--Dipsacus fullonum (talk) 23:29, 20 March 2020 (UTC)

Thank you very much! This works like a charm!

Abfrage der Bürgermeister ein Stadt in zeitlicher Reihenfolge

Hi, ich versuche die Bürgermeister einer Stadt in zeitlicher Reihenfolge abzufragen. Leider funktioniert es in Fällen bei denen ein Bm mehrere Amtszeiten nicht und ich bekomme ein totales durcheinander. Ich verstehe aber die Abfragesprache zu wenig um herauszufinden wo der Fehler liegt. Ich habe folgende zwei Abfragen probiert: 

SELECT ?item ?itemLabel ?start ?end ?Bild WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de". }
?item wdt:P39 wd:Q61742486.
OPTIONAL { ?item p:P39 [ pq:P580 ?start ] . }
OPTIONAL { ?item p:P39 [ pq:P582 ?end ] . }
}
ORDER BY ?start
Try it!

und 

SELECT ?item ?itemLabel ?start ?end ?Bild WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de". }
wd:Q61742486 wdt:P1308 ?item.
OPTIONAL { ?item p:P39 [ pq:P580 ?start ] . }
OPTIONAL { ?item p:P39 [ pq:P582 ?end ] . }
}
ORDER BY ?start
Try it!
@Ogmios: Hi, I cannot answer in German. You need to make sure the value and qualifier is for the same statement. Here is your two queries rewritten to do that. I also added filters to test for "unknown value" for ?start and ?end.
SELECT ?item ?itemLabel ?start ?end ?Bild
WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de". }
  ?item p:P39 ?statement.
  ?statement ps:P39 wd:Q61742486.
  OPTIONAL { ?statement pq:P580 ?start . FILTER (! isBLANK(?start)) }
  OPTIONAL { ?statement pq:P582 ?end . FILTER (! isBLANK(?end)) }
}
ORDER BY ?start
Try it! 
SELECT ?item ?itemLabel ?start ?end ?Bild
WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de". }
  wd:Q61742486 p:P1308 ?statement.
  ?statement ps:P1308 ?item.
  OPTIONAL { ?statement pq:P580 ?start . FILTER (! isBLANK(?start)) }
  OPTIONAL { ?statement pq:P582 ?end . FILTER (! isBLANK(?end)) }
}
ORDER BY ?start
Try it!
--Dipsacus fullonum (talk) 15:07, 22 March 2020 (UTC)
@Dipsacus fullonum: Hi, thanks a lot. I don not fully understand it but it delivers exactly what I am aiming for. Ogmios (Tratsch) 21:24, 22 March 2020 (UTC)

List of Oscar Best Movies

Hi, I was working to create a query to get a list of Oscar Best Movies and their relevant IMDB ID's, so I build this query: 

SELECT ?item ?itemLabel ?IMDb_ID WHERE {
  ?item wdt:P31 wd:Q11424;
    wdt:P166 wd:Q102427.
  OPTIONAL { ?item wdt:P345 ?IMDb_ID. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
Try it!

The issue is that I need the time (or year) this award was provided using qualifier point in time (P585) within a column. If I can add a condition (for example all awards given after 2010) it will be great.

Thank you in advance.--Aladdin (talk) 11:11, 22 March 2020 (UTC)

@Aladdin:
SELECT ?item ?itemLabel (YEAR(?time) as ?year) ?IMDb_ID
WHERE {
  ?item wdt:P31 wd:Q11424;
    p:P166 ?oscar_statement.
  ?oscar_statement ps:P166 wd:Q102427;
    pq:P585 ?time.
  OPTIONAL { ?item wdt:P345 ?IMDb_ID. }

  # Use only truthy statements - not really needed here, but for good measure
  ?oscar_statement wikibase:rank ?rank.
  FILTER (?rank = wikibase:PreferredRank ||
          (?rank = wikibase:NormalRank && NOT EXISTS { ?item p:P166/wikibase:rank wikibase:PreferredRank. } )
         )
  
  # Add condition for year
  FILTER (YEAR(?time) > 2010)

  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
Try it!
--Dipsacus fullonum (talk) 14:26, 22 March 2020 (UTC)
Thank you very much @Dipsacus fullonum--Aladdin (talk) 14:43, 22 March 2020 (UTC)
Dear @Dipsacus fullonum, I made a minor change, this time I'm looking for movies with best directer award, I changed Academy Award for Best Picture (Q102427) to be Academy Award for Best Director (Q103360) and I got only 3 results. For example The Artist (Q171861) won the two Oscars, it appears on the first one but not the second. My query is:
SELECT ?item ?itemLabel (YEAR(?time) as ?year) ?IMDb_ID
WHERE {
  ?item wdt:P31 wd:Q11424;
    p:P166 ?oscar_statement.
  ?oscar_statement ps:P166 wd:Q103360;
    pq:P585 ?time.
  OPTIONAL { ?item wdt:P345 ?IMDb_ID. }

  # Use only truthy statements - not really needed here, but for good measure
  ?oscar_statement wikibase:rank ?rank.
  FILTER (?rank = wikibase:PreferredRank ||
          (?rank = wikibase:NormalRank && NOT EXISTS { ?item p:P166/wikibase:rank wikibase:PreferredRank. } )
         )
  
  # Add condition for year
  FILTER (YEAR(?time) > 2010)

  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
Try it!

I also did the same for other four Oscars Academy Award for Best Actor (Q103916), Academy Award for Best Actress (Q103618), Academy Award for Best Supporting Actor (Q106291) and Academy Award for Best Supporting Actress (Q106301). but, also I didn't get the full results, have I did anything wrong? --Aladdin (talk) 15:17, 22 March 2020 (UTC)

Hi Aladdin. The results are missing where point in time (P585) isn't used as a qualifier. See Q18002795#P166 as an example. There are instead the qualifier statement is subject of (P805) with the value 88th Academy Awards (Q20022969). That makes the query more complicated as you have to go to that item to get a point of time. I don't have time for that query now, but I plan to return in 4 or 5 hours. --Dipsacus fullonum (talk) 15:58, 22 March 2020 (UTC)
Hi again Aladdin. I had time to make a query already. The changes are relatively small. I changed pq:P585 to pq:P585 | (pq:P805/wdt:P585) and added DISTINCT in the SELECT clause as both branches in the extended property path may have results:
SELECT DISTINCT ?item ?itemLabel (YEAR(?time) as ?year) ?IMDb_ID
WHERE {
  ?item wdt:P31 wd:Q11424;
    p:P166 ?oscar_statement.
  ?oscar_statement ps:P166 wd:Q103360;
    pq:P585 | (pq:P805/wdt:P585) ?time.
  OPTIONAL { ?item wdt:P345 ?IMDb_ID. }

  # Use only truthy statements - not really needed here, but for good measure
  ?oscar_statement wikibase:rank ?rank.
  FILTER (?rank = wikibase:PreferredRank ||
          (?rank = wikibase:NormalRank && NOT EXISTS { ?item p:P166/wikibase:rank wikibase:PreferredRank. } )
         )
  
  # Add condition for year
  FILTER (YEAR(?time) > 2010)

  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
Try it!
--Dipsacus fullonum (talk) 16:46, 22 March 2020 (UTC)
Thank you very much @Dipsacus fullonum--Aladdin (talk) 09:12, 23 March 2020 (UTC)

Thousands separator

Can en:Template:Wikidata list/Listeria show thousands separator in quantitative values? For example, 1000000 should be shown as 1.000.000 in most languages, 1,000,000 in english and 1 000 000 (using no-break spaces) in Swedish. Correct sorting based on the numerical value should still be ensured.

One option would be some fancy SPARQL query code - can you help me with that? Another option to change the listeria bot code. Tomastvivlaren (talk) 23:46, 14 March 2020 (UTC)

@Tomastvivlaren: I think the best way to do that would be to apply the formatnum parser function ({{formatnum:1000000}} → 1,000,000) on the wiki end – I think Listeria has options for that (wrapping result cells in certain wikitext). Do you have a particular page in mind where we can try it out? --TweetsFactsAndQueries (talk) 18:03, 15 March 2020 (UTC)
@Tomastvivlaren: I tried it out at User:TweetsFactsAndQueries/sandbox, based on w:sv:Pandemi#Lista över pandemier i urval – it looks like the trick is to use a custom row_template, such as the one at User:TweetsFactsAndQueries/sandbox/row template, which is based on the Wikitext that Listeria generates by default but wraps the p1120 argument in {{formatnum:}}. --TweetsFactsAndQueries (talk) 18:16, 15 March 2020 (UTC)
Thankyou @TweetsFactsAndQueries: for a professional solution. Nowadays when the listeria bot allows several SPARQL variables in the template, not only ?item, it should also be possible to output numbers formatted as strings using SPARQL code, but I have not succeeded yet. Neither the Query service, nor listeria, seems to allow all functions mentioned in the SPARQL documentation. Tomastvivlaren (talk) 10:30, 16 March 2020 (UTC)
I think it is possible to do it in SPARQL but I think it is better to do complicated formatting of data like this with other tools after retrival as WDQS is a limited resource. But I wonder which SPARQL functions you are missing. I have never experienced correct SPARQL code not working (expect for timeouts) at WDQS. --Dipsacus fullonum (talk) 12:23, 16 March 2020 (UTC)
Here is code to insert thousand separators in positive integers with up to 15 digits (e.g. population numbers). It could be expanded to decimal numbers, negative numbers or more digits, but I will leave that as an exercise for interested persons. I don't really recommend using this code.
SELECT ?n ?formatted
WHERE
{
  BIND ("," AS ?sep)
  VALUES ?n { 1 12 123 1234 12345 123456 1234567 12345678 123456789 1234567890 12345678901
          123456789012 1234567890123 12345678901234 123456789012345 }
  BIND (STR(?n) AS ?str)
  BIND (STRLEN(?str) AS ?len)
  BIND (?len - CEIL(?len/3)*3 + 3 AS ?length_first)
  BIND (SUBSTR(?str, 1, ?length_first) AS ?group1)
  BIND (SUBSTR(?str, ?length_first + 1, 3) AS ?group2)
  BIND (SUBSTR(?str, ?length_first + 4, 3) AS ?group3)
  BIND (SUBSTR(?str, ?length_first + 7, 3) AS ?group4)
  BIND (SUBSTR(?str, ?length_first + 10, 3) AS ?group5)
  BIND (COALESCE(
    IF(?len > 12, CONCAT(?group1, ?sep, ?group2, ?sep, ?group3, ?sep, ?group4, ?sep, ?group5) , 1/0),
    IF(?len > 9, CONCAT(?group1, ?sep, ?group2, ?sep, ?group3, ?sep, ?group4), 1/0),
    IF(?len > 6, CONCAT(?group1, ?sep, ?group2, ?sep, ?group3), 1/0),
    IF(?len > 3, CONCAT(?group1, ?sep, ?group2), 1/0),
    ?group1
  ) AS ?formatted)
}
Try it!
--Dipsacus fullonum (talk) 14:21, 16 March 2020 (UTC)
SELECT * { wd:Q2 wdt:P1082 ?p . BIND( CONCAT("{","{formatnum:",str(?p),"}","}") as ?pop)  }

Try it!

should work in Listeria --- Jura 18:54, 26 March 2020 (UTC)

Searching for a specific property from a list of items

Hello, I'm not the most familiar with queries, so any help would be appreciated. I have a long list of items, and I would like to find all the items from this list which have the property UEFA player ID (P2276), including deprecated rank, and if so what the value is. For example, if I had the items João Domingos Pinto (Q1654093), George Best (Q164521), Eusébio (Q162043) and Cristiano Ronaldo (Q11571), I would like to return the items that have the property and the corresponding value (1705 for João Domingos Pinto (Q1654093), 63706 for Cristiano Ronaldo (Q11571)). Thanks, S.A. Julio (talk) 09:12, 24 March 2020 (UTC)

@S.A. Julio: Here is a query. I added a column for rank.
SELECT ?item ?itemLabel ?UEFA_player_ID ?rank
WHERE
{
  VALUES ?item
  {
    # Place items here with the wd: prefix:
    wd:Q1654093 wd:Q164521 wd:Q162043 wd:Q11571
  }
  ?item p:P2276 ?statement.
  ?statement ps:P2276 ?UEFA_player_ID.
  ?statement wikibase:rank ?rank.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
Try it!
--Dipsacus fullonum (talk) 10:48, 24 March 2020 (UTC)
Great, thank you very much! S.A. Julio (talk) 16:53, 24 March 2020 (UTC)

Upper case titles

Can we query for scholarly papers whose title (P1476) are in all upper case? I realise this may time out, so limiting by, say, subject, venue, or year of publication, would be OK. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 13:44, 26 March 2020 (UTC)

@Pigsonthewing: You are right about the timeout if there isn't some other limit, I added one for year of publication. It could also have been something else.
SELECT ?item ?title
WHERE
{
  ?item wdt:P31 wd:Q13442814 . # Scientific article
  ?item wdt:P1476 ?title .
  FILTER (?title = UCASE(?title) &&  # Title doesn't change if converted to uppercase
          ?title != LCASE(?title))   # Title does change if converted to lowercase (to sort out scripts which don't distinguish like e.g. Chinese)
  
  ?item wdt:P577 ?publication_date. hint:Prior hint:rangeSafe true.
  FILTER (?publication_date >= "2019-00-00"^^xsd:dateTime && # Published in 2019
          ?publication_date < "2020-00-00"^^xsd:dateTime)
}
Try it!
--Dipsacus fullonum (talk) 18:16, 26 March 2020 (UTC)

Article titles about persons that contain a certain string

I would like to get a list of Wikipedia articles about people with the title containing the string "Vale". Also the same search but with the title starting with "Vale" instead of containing that string. I apologize if the request is dumb. —  Ark25  (talk) 19:09, 27 March 2020 (UTC)

@Ark25: These queries cannot run without timeout, so I added a limit of 100 articles. The limit could also be about something else.
SELECT ?title ?article ?Wikipedia
WHERE
{
  ?article schema:about ?item.
  ?item wdt:P31 wd:Q5.
  ?article schema:name ?title.
  ?article schema:isPartOf ?Wikipedia.
  ?Wikipedia wikibase:wikiGroup "wikipedia".
  FILTER CONTAINS(?title, "Vale")
}
LIMIT 100
Try it! 
SELECT ?title ?article ?Wikipedia
WHERE
{
  ?article schema:about ?item.
  ?item wdt:P31 wd:Q5.
  ?article schema:name ?title.
  ?article schema:isPartOf ?Wikipedia.
  ?Wikipedia wikibase:wikiGroup "wikipedia".
  FILTER STRSTARTS(?title, "Vale")
}
LIMIT 100
Try it!
--Dipsacus fullonum (talk) 19:38, 27 March 2020 (UTC)
Correction. I overlooked that the articles should be about persons in the first version. That is corrected now. The queries still cannot run without more limits. --Dipsacus fullonum (talk) 19:45, 27 March 2020 (UTC)
@Dipsacus fullonum: Thank you very much, but I forgot to mention that I only need the results from English Wikipedia (not from all Wikipedias) and also with "Vale" as an exact (whole) word - in other words, the titles containing string "Vale" or " Vale". Can you please re-write the second query to provide such results? —  Ark25  (talk) 19:52, 27 March 2020 (UTC)
@Ark25: There are very few articles on English Wikipedia about persons containing the word "Vale" in the title. That combined with that searching for words is a relatively slow operation means that this query can only find one article with a title containing the word Vale before it times out:
SELECT ?title ?article
WHERE
{
  ?article schema:about [wdt:P31 wd:Q5].
  ?article schema:name ?title.
  ?article schema:isPartOf <https://en.wikipedia.org/>
  FILTER REGEX(?title, "\\bVale\\b")
}
LIMIT 1
Try it!
I cannot make a query for English Wikipedia articles starting with the word "Vale" that doesn't timeout. I think it will much more effective to make a search directly in the English Wikipedia since you only are interested in articles on that Wikipedia anyway. --Dipsacus fullonum (talk) 20:45, 27 March 2020 (UTC)
@Dipsacus fullonum: Many thanks for your time and patience! In the hope I am not abusing (too much), I would like to ask the following questions:
  • Is it possible to simply search for "Vale " (Vale followed by a space) instead of searching for "\\bVale\\b" ? Indeed the \\b seems do make the search much slower. And I don't know how to escape the space character.
  • Is there any way to change the value of the timeout? I have patience to wait for results. Or is it possible to leave a query on the server and to come back later and check for it's results?
  • How can make such a search directly on the English Wikipedia? I've managed to produce this search but I don't know how to search for "Vale " instead of "Vale" (again I can't escape spaces) —  Ark25  (talk) 21:42, 27 March 2020 (UTC)
@Ark25: You don't need to escape a space character as it has no special meaning i SPARQL strings:
FILTER CONTAINS(?title, "Vale ") and
FILTER STRSTARTS(?title, "Vale ")
are both correct SPARQL code, but it is impossible search millions of titles in 60 seconds and it is not possible to change the value for the timeout. But the Wikipedias have indexes of all words so a search for words there will be almost instantaneously. Just search for intitle:Vale in the search field on the English Wikipedia. --Dipsacus fullonum (talk) 22:08, 27 March 2020 (UTC)
@Dipsacus fullonum: Thank you very much for all your help! —  Ark25  (talk) 23:15, 27 March 2020 (UTC)

Mayors

Hi! I have this query for women mayors in Catalonia. I would like to have only the ones who served between 13.06.2015 and 15.06.2019. They could start from 2007 or 2018, or they could finish in 2017, but they should have served in some moment between these two dates. I don't know if it's possible... Thanks! --Davidpar (talk) 15:01, 28 March 2020 (UTC) 

SELECT ?alcaldessa ?alcaldessaLabel ?carrec ?carrecLabel ?municipiLabel ?comarcaLabel ?inici ?fi
    WHERE {
      ?alcaldessa wdt:P31 wd:Q5.
      ?alcaldessa wdt:P21 wd:Q6581072.
      ?alcaldessa p:P39 ?st . ?st ps:P39 ?carrec.
      ?carrec wdt:P279 wd:Q5663900.
      ?carrec wdt:P1001 ?municipi.
      ?municipi wdt:P31 wd:Q33146843.
      ?municipi wdt:P131 ?comarca.
      VALUES ?comarquesiaran {wd:Q937876 wd:Q19920968}
      ?comarca wdt:P31 ?comarquesiaran.
      OPTIONAL {?st pq:P580 ?inici.}
      OPTIONAL {?st pq:P582 ?fi.}
      FILTER ( !BOUND(?inici)   \u007C\u007C   ?inici < "2019-06-15"^^xsd:dateTime  )  
      FILTER ( !BOUND(?fi)      \u007C\u007C   ?fi > "2015-06-13"^^xsd:dateTime ) 
    SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],ca,en" .
    }
    }
ORDER BY ?comarcaLabel ?municipiLabel

Try it!

\u007C\u007C is for ||

@Davidpar: I tried to fix the date part of above. It also includes those without dates --- Jura 15:13, 28 March 2020 (UTC)

@Davidpar: Here is a version which take the precision of the dates into account. There are two entries with end dates with precision year in 2015 which may or may not be after 13 June that are shown here but not in the query by Jura1.
SELECT ?alcaldessa ?alcaldessaLabel ?carrec ?carrecLabel ?municipiLabel ?comarcaLabel ?inici ?fi ?inici_precision ?fi_precision
    WHERE {
      ?alcaldessa wdt:P31 wd:Q5.
      ?alcaldessa wdt:P21 wd:Q6581072.
      ?alcaldessa p:P39 ?st . ?st ps:P39 ?carrec.
      ?carrec wdt:P279 wd:Q5663900.
      ?carrec wdt:P1001 ?municipi.
      ?municipi wdt:P31 wd:Q33146843.
      ?municipi wdt:P131 ?comarca.
      VALUES ?comarquesiaran {wd:Q937876 wd:Q19920968}
      ?comarca wdt:P31 ?comarquesiaran.
      OPTIONAL {
        ?st pqv:P580 ?inici_value.
        ?inici_value wikibase:timeValue ?inici.
        ?inici_value  wikibase:timePrecision ?inici_precision.
      }
      OPTIONAL {
        ?st pqv:P582 ?fi_value.
        ?fi_value wikibase:timeValue ?fi.
        ?fi_value  wikibase:timePrecision ?fi_precision.
      }
      FILTER ( !BOUND(?inici) ||
               ?inici_precision = 11 && ?inici < "2019-06-15"^^xsd:dateTime ||
               ?inici_precision = 9  && ?inici < "2020-00-00"^^xsd:dateTime)
      FILTER ( !BOUND(?fi) ||
               ?fi_precision = 11 && ?fi >  "2015-06-13"^^xsd:dateTime ||
               ?fi_precision = 9  && ?fi >= "2015-00-00"^^xsd:dateTime)
      SERVICE wikibase:label {
        bd:serviceParam wikibase:language "[AUTO_LANGUAGE],ca,en" .
      }
    }
ORDER BY ?comarcaLabel ?municipiLabel
Try it!
--Dipsacus fullonum (talk) 16:57, 28 March 2020 (UTC)
Ok! @Dipsacus fullonum, Jura1: Thank you so much!! --Davidpar (talk) 17:02, 28 March 2020 (UTC)

Part of string with P973

Hello everybody,

could you help me to write this query : 

#movie with P973 start with "https://www.artfilm.ch"
SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P31 wd:Q2431196;
        wdt:P973 "https://www.artfilm.ch"
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

One result must be A Road Not Taken (Q88872037)

Thanks in advance --2le2im-bdc (talk) 15:50, 28 March 2020 (UTC) 

#movie with P973 start with "https://www.artfilm.ch"
SELECT ?item ?itemLabel ?value ?title 
WHERE 
{
  ?item wdt:P31 wd:Q2431196;
        wdt:P973 ?value . 
  FILTER( REGEX(str(?value),"^https://www\\.artfilm\\.ch" ) )
  OPTIONAL { ?item wdt:P1476 ?title }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr,it,de,rm,en". }
}

Try it! @2le2im-bdc: --- Jura 16:20, 28 March 2020 (UTC)

@2le2im-bdc: And here is my version without regular expression.
#movie with P973 start with "https://www.artfilm.ch"
SELECT DISTINCT ?item ?itemLabel
WHERE 
{
  ?item wdt:P31 wd:Q2431196;
        wdt:P973 ?describes.
  FILTER STRSTARTS(STR(?describes), "https://www.artfilm.ch")
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Dipsacus fullonum (talk) 16:24, 28 March 2020 (UTC)
Good point: more user friendly. --- Jura 16:29, 28 March 2020 (UTC)
Thanks a lot @Jura1, Dipsacus fullonum:!!
User:Jura1, I have take notice of not use audiovisual work (Q2431196) for movie. I will be careful in the futur. --2le2im-bdc (talk) 19:35, 28 March 2020 (UTC)

Spelling out the language of a Wikimedia page from sitelink

I'm working on a list of all languages that have a version of Template:COVID-19 pandemic (Q83761248). Here is what I got so far:

The following query uses these:

  • Properties: Wikimedia language code (P424)     
    SELECT ?TemplateURL ?TemplateTitle  ?lang ?Wikigroup 
#?Wiki ?WikiLabel ?WikiURL 
WHERE {
  VALUES ?Template {
    wd:Q83761248
  }
  ?TemplateURL schema:about ?Template;
               schema:inLanguage ?lang ;
#               schema:isPartOf ?WikiURL ;
               schema:isPartOf [ wikibase:wikiGroup ?Wikigroup ] ;
               schema:name ?TemplateTitle.
#  ?Wiki wdt:P424 ?lang .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

So the functionality is there, but in addition to displaying ?lang (e.g. "zh") and ?Wikigroup (e.g. "wikinews"), which is useful for sorting, I would like to display the corresponding full name (in a specific language) of that wiki, i.e. ?Wiki would be "Chinese Wikinews". I tried to use Wikimedia language code (P424) for that and also to infer it somehow based on ?WikiURL but got stuck and would appreciate pointers. --Daniel Mietchen (talk) 04:59, 29 March 2020 (UTC)

@Daniel Mietchen: Does this do what you want?
The following query uses these:
  • Properties: Wikimedia database name (P1800)     
    SELECT ?TemplateURL ?TemplateTitle  ?lang ?Wikigroup ?wikiLabel
#?Wiki ?WikiLabel ?WikiURL 
WHERE {
  VALUES ?Template {
    wd:Q83761248
  }
  ?TemplateURL schema:about ?Template;
               schema:inLanguage ?lang ;
#               schema:isPartOf ?WikiURL ;
               schema:isPartOf [ wikibase:wikiGroup ?Wikigroup ] ; 
               schema:name ?TemplateTitle.
  BIND(IF(?Wikigroup in ("wikipedia","commons","species","wikidata","meta","incubator"),CONCAT(?lang,"wiki"),CONCAT(?lang,?Wikigroup)) as ?wikilang)
  ?wiki wdt:P1800 ?wikilang.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

Note lines 12 and 13 in the above query. Let me know if you need this to work with "commonswiki" or "specieswiki" or "wikidatawiki". Mahir256 (talk) 05:16, 29 March 2020 (UTC)

@Mahir256: That's great! Yes, it does what I wanted, and yes, I'd like for it to work across all Wikimedia sites. --Daniel Mietchen (talk) 05:24, 29 March 2020 (UTC)
@Daniel Mietchen: I was kidding with that part (those are the very few exceptions to the rule I'm exploiting), but I modified the query above anyways. I'll be damned if you find some other wiki with such a template that the query does not still account for. Mahir256 (talk) 05:38, 29 March 2020 (UTC)
@Mahir256: That seems to cover it all — thanks again! I tested it by throwing a few more templates into the mix:
The following query uses these:
  • Properties: Wikimedia database name (P1800)     
    SELECT ?Template ?TemplateURL ?TemplateTitle ?LanguageCode ?Wikigroup ?WikiLabel
WHERE {
  VALUES ?Template {
    wd:Q83761248 wd:Q5637226 wd:Q5312535 wd:Q5625141
  }
  ?TemplateURL schema:about ?Template;
               schema:inLanguage ?LanguageCode ;
               schema:isPartOf [ wikibase:wikiGroup ?Wikigroup ] ; 
               schema:name ?TemplateTitle.
  BIND(IF(?Wikigroup in ("wikipedia","commons","species","wikidata","meta","incubator"),CONCAT(?LanguageCode,"wiki"),CONCAT(?LanguageCode,?Wikigroup)) as ?wikilang)
  ?Wiki wdt:P1800 ?wikilang.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
While this has all the functionality I need, I am now curious as to whether the string values in line 10 could be inferred in some way rather than hardcoded. At least in theory, things like "sdcommons" or "wikibase" or "wikispore" might eventually fit in there at some point. --Daniel Mietchen (talk) 06:01, 29 March 2020 (UTC)

The direct way is 

SELECT ?Template ?TemplateURL ?TemplateTitle ?LanguageCode ?WikiLabel
WHERE {
  VALUES ?Template {
    wd:Q83761248 wd:Q5637226 wd:Q5312535 wd:Q5625141
  }
  ?TemplateURL schema:about ?Template;
               schema:inLanguage ?LanguageCode ;
               schema:isPartOf ?wikilang ; 
               schema:name ?TemplateTitle .
  ?Wiki wdt:P856 ?wikilang.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!--- Jura 06:48, 29 March 2020 (UTC)

Short and sweet. Thanks, Jura1! --Daniel Mietchen (talk) 07:42, 29 March 2020 (UTC)

Query on qualifier and display it on results

Hello,

I need your help to find what wrong with my query.

I try to find all the item who are movie and have a statement described at URL (P973) with qualifier language of work or name (P407) with the value French (Q150). I want also to display in the results the value with French (Q150) qualifier's value.

One example of result must be : Q88874436 

#movie with P973 start with "https://www.artfilm.ch" and in french
SELECT DISTINCT ?item ?itemLabel ?réal ?réalLabel ?famLabel
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q2431196;
        wdt:P973 ?describes;
        wdt:P57 ?réal
  FILTER STRSTARTS(STR(?describes), "https://www.artfilm.ch").
  ?item p:P973 ?describes.
  ?describes ps:P973 ?decrit.
  ?decrit pq:P407 wd:Q150.
  ?réal wdt:P734 ?fam.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,it,de".
    ?item rdfs:label ?itemLabel.
    ?réal rdfs:label ?réalLabel.
    ?fam rdfs:label ?famLabel.
}
}
ORDER BY STR(?famLabel)
Try it!

Thanks in advance ! --2le2im-bdc (talk) 19:35, 29 March 2020 (UTC)

@2le2im-bdc: Check that you're not reusing variable names next time: 
#movie with P973 start with "https://www.artfilm.ch" and in french
SELECT DISTINCT ?item ?itemLabel ?réal ?réalLabel ?famLabel
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q2431196;
        wdt:P57 ?réal.
  ?item p:P973 ?describestmt.
  ?describestmt ps:P973 ?decrit.
  ?describestmt pq:P407 wd:Q150.
  FILTER STRSTARTS(STR(?decrit), "https://www.artfilm.ch").
  ?réal wdt:P734 ?fam.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,it,de".
    ?item rdfs:label ?itemLabel.
    ?réal rdfs:label ?réalLabel.
    ?fam rdfs:label ?famLabel.
}
}
ORDER BY STR(?famLabel)
Try it!
Mahir256 (talk) 19:45, 29 March 2020 (UTC)
Great! Thanks a lot @Mahir256:. I will be more carful next time.--2le2im-bdc (talk) 19:57, 29 March 2020 (UTC)
@2le2im-bdc: Also remember when using p: prefix that you get all statements including them with deprecated rank, and also normal rank even if another statement with preferred rank exists. To only access truthy statements as before you will need a filter like:
?describestmt wikibase:rank ?rank.
FILTER (?rank = wikibase:PreferredRank || ?rank = wikibase:NormalRank && NOT EXISTS { ?item p:P973/wikibase:rank wikibase:PreferredRank. })
added to query by Mahir256. --Dipsacus fullonum (talk) 20:00, 29 March 2020 (UTC)

ORDER BY Family name

Hello,

How write this query to have the results ORDER BY alphabethic name of families (without french accent), from A to Z? 

#movie with P973 start with "https://www.artfilm.ch"
SELECT DISTINCT ?item ?itemLabel ?réal ?réalLabel ?fam ?famLabel
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q2431196;
        wdt:P973 ?describes;
        wdt:P57 ?réal
  FILTER STRSTARTS(STR(?describes), "https://www.artfilm.ch").
  ?réal wdt:P734 ?fam.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,it,de". }
}
ORDER BY ?famLabel
Try it!

Thanks in advance --2le2im-bdc (talk) 21:43, 28 March 2020 (UTC)

That's strange, in theory I think it should work. I would like to know exactly why it doesn't. @Lucas Werkmeister: any idea?
That said, here a simple solution with adding a STR() on the label :
#movie with P973 start with "https://www.artfilm.ch"
SELECT DISTINCT ?item ?itemLabel ?réal ?réalLabel ?fam ?famLabel
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q2431196;
        wdt:P973 ?describes;
        wdt:P57 ?réal
  FILTER STRSTARTS(STR(?describes), "https://www.artfilm.ch").
  ?réal wdt:P734 ?fam.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,it,de". }
}
ORDER BY STR(?famLabel)
Try it!
Cdlt, VIGNERON (talk) 12:13, 29 March 2020 (UTC)
@2le2im-bdc, VIGNERON: The label service is only documented to work on variables in the SELECT clause, so I see no requirement for it to work correctly for variables used for ordering. A test shows that it does work with ORDER BY ?famLabel if the manual mode of label service is used:
SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,it,de".
    ?item rdfs:label ?itemLabel.
    ?réal rdfs:label ?réalLabel.
    ?fam rdfs:label ?famLabel.
}
--Dipsacus fullonum (talk) 12:41, 29 March 2020 (UTC)
@Dipsacus fullonum: ah yes, obviously. I knew it but somehow forgot it, thanks. Cdlt, VIGNERON (talk) 13:21, 29 March 2020 (UTC)
Thanks @Dipsacus fullonum, VIGNERON: but I see no change if I add label declarations in the SERVICE wikibase (without STR(...)). I am certainly missing something...
#movie with P973 start with "https://www.artfilm.ch"
SELECT DISTINCT ?item ?itemLabel ?réal ?réalLabel ?fam ?famLabel
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q2431196;
        wdt:P973 ?describes;
        wdt:P57 ?réal
  FILTER STRSTARTS(STR(?describes), "https://www.artfilm.ch").
  ?réal wdt:P734 ?fam.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,it,de".
    ?item rdfs:label ?itemLabel.
    ?réal rdfs:label ?réalLabel.
    ?fam rdfs:label ?famLabel.
}
}
ORDER BY ?famLabel
Try it!
Pour info @VIGNERON:, je trouverais bien si tu pouvais expliquer dans un de tes prochains live sur Twitch ce qui se joue ici au niveau du SERVICE etc. Je ne suis pas sûr d'avoir vraiment saisie! --2le2im-bdc (talk) 19:25, 30 March 2020 (UTC)
@2le2im-bdc: oui bonne idée, je me le note. Le service est souvent un peu mystérieux pour les débutants. Cdlt, VIGNERON (talk) 20:04, 30 March 2020 (UTC)
#movie with P973 start with "https://www.artfilm.ch"
SELECT DISTINCT ?item ?itemLabel ?réal ?réalLabel ?fam ?famLabel
WHERE 
{
  VALUES ?réal { wd:Q88692719 wd:Q21215043 wd:Q88691725 wd:Q79961261 }
  ?item wdt:P31/wdt:P279* wd:Q2431196;
        wdt:P973 ?describes;
        wdt:P57 ?réal
  FILTER STRSTARTS(STR(?describes), "https://www.artfilm.ch").
  ?réal wdt:P734 ?fam.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
}
ORDER BY STR(?famLabel)

Try it!

It still doesn't sort in the way I'd expect though. --- Jura 14:19, 29 March 2020 (UTC)

FILTER NOT EXISTS

Hello,

I don't understand why I got no result with this query : 

#director of movie with P973 "artfilm.ch"
SELECT DISTINCT ?réal ?réalLabel ?famLabel
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q2431196;
        wdt:P973 ?describes;
        wdt:P57 ?réal
  FILTER STRSTARTS(STR(?describes), "https://www.artfilm.ch").
  FILTER NOT EXISTS {?réal wdt:P734 ?fam.}.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,it,de".
    ?item rdfs:label ?itemLabel.
    ?réal rdfs:label ?réalLabel.
    ?fam rdfs:label ?famLabel.
}
}
Try it!

When I know with OPTIONAL that there is many ?réal without family name (P734) : 

#director of movie with P973 "artfilm.ch"
SELECT DISTINCT ?réal ?réalLabel ?famLabel
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q2431196;
        wdt:P973 ?describes;
        wdt:P57 ?réal
  FILTER STRSTARTS(STR(?describes), "https://www.artfilm.ch").
  OPTIONAL {?réal wdt:P734 ?fam.}.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,it,de".
    ?item rdfs:label ?itemLabel.
    ?réal rdfs:label ?réalLabel.
    ?fam rdfs:label ?famLabel.
}
}
Try it!

Thanks in advance! --2le2im-bdc (talk) 20:03, 29 March 2020 (UTC)

@2le2im-bdc: Delete the line "?fam rdfs:label ?famLabel" from your original query. Leaving it in there tries to obtain a label for something that does not exist. Mahir256 (talk) 20:19, 29 March 2020 (UTC)
@2le2im-bdc: You get no results in the first query because a filter doesn't bind any variables, so ?fam is unbound in triplet ?fam rdfs:label ?famLabel causing all results to be discarded at that point. --Dipsacus fullonum (talk) 20:27, 29 March 2020 (UTC)
Thanks a lot for your help @Mahir256, Dipsacus fullonum: but I got a Timeout with my new query:
#director of movie with P973 "artfilm.ch"
SELECT DISTINCT ?réal ?réalLabel
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q2431196;
        wdt:P973 ?describes;
        wdt:P57 ?réal
  FILTER STRSTARTS(STR(?describes), "https://www.artfilm.ch").
  FILTER NOT EXISTS {?réal wdt:P734 ?fam.}.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,it,de".}
}
Try it!
--2le2im-bdc (talk) 20:46, 29 March 2020 (UTC)
@2le2im-bdc: This is one of those instances where changing "FILTER NOT EXISTS" to "MINUS" worked. Mahir256 (talk) 23:44, 29 March 2020 (UTC)
Great!Thanks a lot @Mahir256:. --2le2im-bdc (talk) 19:21, 30 March 2020 (UTC)
Pour info @VIGNERON:, je trouverais bien si tu pouvais parler dans un de tes prochains live de la différence entre FILTER NOT EXISTS et MINUS. Je ne suis pas sûr d'avoir vraiment saisie la différence! --2le2im-bdc (talk) 19:21, 30 March 2020 (UTC)
@2le2im-bdc: ahah, là non, désolé moi aussi je suis souvent un peu perdu sur cette différence aussi rare que subtile (contrairement au SERVICE wikibase:label que je comprends bien et que j'expliquerais volontiers). Cdlt, VIGNERON (talk) 20:08, 30 March 2020 (UTC)

Better query for P301 inverse constraint?

category's main topic (P301) has a 'Inverse property of “topic's main category (P910)”' constraint, of which there are currently 27k according to Wikidata:Database_reports/Constraint_violations/P301#Inverse (but that page only lists the first few thousand). The new-style sparql query: 

SELECT DISTINCT ?item ?itemLabel WHERE {
	?statement wikibase:hasViolationForConstraint wds:P301-0EC7969D-436B-4365-B6B5-59454795403E .
	?item ?p ?statement .
	FILTER( ?item NOT IN ( wd:Q4115189, wd:Q13406268, wd:Q15397819 ) ) .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}
Try it!

only returns ~900, and most of those seem to be out of date (due to the way the constraint violation is updated). The old-style query 

SELECT ?item ?itemLabel ?should_link_via_P910_to ?should_link_via_P910_toLabel 
WHERE
{
	?should_link_via_P910_to wdt:P301 ?item .
	FILTER NOT EXISTS { ?item wdt:P910 ?should_link_via_P910_to } .
	SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}
LIMIT 100
Try it!

returns 'Query timeout limit reached', even for a low LIMIT. Is there a better query that will run than this one, please? Thanks. Mike Peel (talk) 08:02, 30 March 2020 (UTC)

@Mike Peel: If you omit the label service, the last query run can without timeout and gives 24438 results. --Dipsacus fullonum (talk) 10:28, 30 March 2020 (UTC)
@Dipsacus fullonum: That's perfect, thanks! Mike Peel (talk) 10:37, 30 March 2020 (UTC)

electoral district of a political position held?

How to query Wikidata to create a list of local elected representatives, along with the particular electoral district represented, and start/end date for each person? Here is my first attempt: < https://w.wiki/LcC >. How to include the electoral district (P768), start time (P580), and end time (P582)? Thank you. -- M2545 (talk) 16:28, 30 March 2020 (UTC)

@M2545: You include qualifiers by using the p: prefix to get full statements and then pq: prefixes for simple qualifier values. Be aware that the dates may have precision greater than day (e.g. month or year) even if an exact date is always shown. To get precision you will need to get full qualifier values with pqv:, see mw:Wikibase/Indexing/RDF Dump Format for details.
# members of Massachusetts House of Representatives

SELECT ?item ?itemLabel 
?date_of_birth ?image ?electoral_district ?electoral_districtLabel ?start ?end
WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item p:P39 ?position_statement.
  ?position_statement ps:P39 wd:Q17342542.
  ?position_statement wikibase:rank ?rank.
  FILTER (?rank = wikibase:PreferredRank ||
          ?rank = wikibase:NormalRank && NOT EXISTS { ?item p:P39/wikibase:rank wikibase:PreferredRank. })
  OPTIONAL { ?item wdt:P569 ?date_of_birth. }
  OPTIONAL { ?item wdt:P18 ?image. }
  OPTIONAL { ?position_statement pq:P768 ?electoral_district. }
  OPTIONAL { ?position_statement pq:P580 ?start. }
  OPTIONAL { ?position_statement pq:P582 ?end. }
}
ORDER BY DESC(?date_of_birth)
LIMIT 5000
Try it!

--Dipsacus fullonum (talk) 18:45, 30 March 2020 (UTC)

@Dipsacus fullonum: Wonderful. Thank you!! -- M2545 (talk) 19:03, 30 March 2020 (UTC)

Many wikiquote entries, but few wikipedia entries

Find a person about whom there are articles in the Wikiquote in the largest number of languages, despite the fact that there are no more than n articles on Wikipedia

Persons with more Wikiquote articles than Wikipedia articles. I have limited the search to persons with occupation (P106) author (Q482980) to make the query possible without timeout.
SELECT ?item ?itemLabel ?wikipedia_articles ?wikiquote_articles
WITH
{
  SELECT ?item (COUNT(DISTINCT ?wikipedia_article) AS ?wikipedia_articles)
        (COUNT(DISTINCT ?wikiquote_article) AS ?wikiquote_articles)
  WHERE
  {
    ?item wdt:P31 wd:Q5.
    ?item wdt:P106 wd:Q482980.
    OPTIONAL {
      ?wikipedia_article schema:about ?item.
      ?wikipedia_article schema:isPartOf/wikibase:wikiGroup "wikipedia".
    }
    ?wikiquote_article schema:about ?item.
    ?wikiquote_article schema:isPartOf/wikibase:wikiGroup "wikiquote".
  }
  GROUP BY ?item
  HAVING (?wikiquote_articles > ?wikipedia_articles)
} AS %sub
WHERE
{
  INCLUDE %sub
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}
ORDER BY DESC(?wikiquote_articles)
Try it!
--Dipsacus fullonum (talk) 03:07, 31 March 2020 (UTC)

List of artists in City of Pori art collection

Can someone help me get a list of artists (I think all of them don't have an artist as an occupation, but they all should be instances of human P31, Q5) who have artworks in (P6379) City of Pori art collection (Q86443703), with their date of birth (P569) and possible date of death (P570). I tried, but I'm not too good with these queries. I tried this, but didn't get any results:

SELECT ?itemLabel

WHERE

{ 

 ?itemLabel wd:P31 wd:Q5.
 ?item wdt:P6379 wd:Q86443703.
 ?item wdt:P569 wdt:P570

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

}


Thank you if someone can help me:) 

SO 08:53, 31 March 2020 (UTC)
@SannaOikkis: Here is a query. Remember that some of the dates of birth and death may only have precision month or year.
SELECT ?item ?itemLabel ?date_of_birth ?date_of_death
WHERE
{
  ?item wdt:P31 wd:Q5.
  ?item wdt:P6379 wd:Q86443703.
  OPTIONAL { ?item wdt:P569 ?date_of_birth. }
  OPTIONAL { ?item wdt:P570 ?date_of_death. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Dipsacus fullonum (talk) 09:16, 31 March 2020 (UTC)
@Dipsacus fullonum: Thank you so much:)!

SO 10:01, 31 March 2020 (UTC)

Select Australian women who have an infobox with the parameter "image =" (and possibly therefore an image) where there is no image in wikidata

# select Australian women have an enwiki article with an image
# but have no image on wikidata
SELECT ?item ?itemLabel  ?article ?sitelink
WHERE 
{
  {
    ?item wdt:P21 wd:Q6581072. # is a women
    ?item wdt:P27 wd:Q408.     # Is Australian
    ?sitelink ^schema:name ?article .
    ?article schema:about ?item ;
             schema:isPartOf <https://en.wikipedia.org/> .
  }
MINUS    { ?item wdt:P18 ?image.}
  {
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:api "Search" .
      bd:serviceParam wikibase:endpoint "en.wikipedia.org" .
      bd:serviceParam mwapi:srnamespace "0" .
      bd:serviceParam mwapi:srlimit "max" .
      bd:serviceParam mwapi:srwhat "text" .
      bd:serviceParam mwapi:srsearch 'insource:"image = "' .
      ?title wikibase:apiOutput mwapi:title .
    }
    BIND (STRLANG(?title, "en") as ?sitelink)
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 100
Try it!

Hoping someone can help me with this? Cheers, MargaretRDonald (talk) 22:52, 29 March 2020 (UTC)

I just tried to search for insource:"image = " in English Wikipedia. It says that there is 3,345,677 results for that query, so I am not surprised that this query times out. The first part (Australian women without image on Wikidata) has 6,531 results but I cannot see a way to search only those articles in English Wikipedia without creating a new specialized tool for that. --Dipsacus fullonum (talk) 10:48, 30 March 2020 (UTC)
@MargaretRDonald: A different approach might be to look to see which have Commons categories/galleries:
SELECT ?item ?commonscat WHERE {
  ?item wdt:P21 wd:Q6581072 .
    ?item wdt:P27 wd:Q408.     # Is Australian
  ?commonscat schema:about ?item;
  schema:isPartOf <https://commons.wikimedia.org/>.
  FILTER NOT EXISTS {
               ?item wdt:P18 ?image . }
}
Try it!
Thanks. Mike Peel (talk) 10:24, 31 March 2020 (UTC)

Thanks, @Mike Peel:. It's a good try but the proportion of successes seems to be a bit low: many of the categories do not have images of the women, but of their work and some are empty all together. I had been hoping to find english wikipedia articles about australian women which had an image in the infobox but no image in wikdata. Would it be possible to add a further constraint about citizenship. (I tried to insert this into search within articles, but I couln' add the second constraint. MargaretRDonald (talk) 15:13, 31 March 2020 (UTC)

And thank you also @Dipsacus fullonum: for the explanation of failure. MargaretRDonald (talk) 15:13, 31 March 2020 (UTC)
Retrieved from "https://www.wikidata.org/w/index.php?title=Wikidata:Request_a_query/Archive/2020/03&oldid=1614118659"