Shortcut: WD:RAQ

Wikidata:Request a query

From Wikidata
Jump to navigation Jump to search

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

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

For sample queries, see Examples. Property talk pages include also summary queries for these.

For help writing your own queries, or other questions about queries, see Wikidata talk:SPARQL query service/queries.

Help resources about Wikidata Query Service (Q20950365) and SPARQL: Wikidata:SPARQL query service/Wikidata Query Help and Category:SPARQL.

On this page, old discussions are archived. An overview of all archives can be found at this page's archive index. The current archive is located at 2019/01.

Project
chat

Lexicographical
data

Administrators'
noticeboard

Development
team

Translators'
noticeboard

Request
a query

Requests
for deletions

Requests
for comment

Bot
requests

Requests
for permissions

Property
proposal

Properties
for deletion

Partnerships
and imports

Interwiki
conflicts

Bureaucrats'
noticeboard

Slide show with images[edit]

I am trying to make a slideshow with automatic looping over the a set of images returned from a WDQS query. I have made some progress on a page that loops via CCS and Javascript. I was wondering if there is a simpler way. For instance, with SAMPLE and HTML meta refresh. This will not work:

#defaultView:ImageGrid
SELECT (SAMPLE(DISTINCT ?image) AS ?image) WHERE {
  ?image ^wdt:P18 / wdt:P50 / (wdt:P108| wdt:P463 | wdt:P1416/wdt:P361*) wd:Q24283660 .
}

Try it!

 – The preceding unsigned comment was added by Fnielsen (talk • contribs) at 00:51, 21 February 2018‎ (UTC).

Country and city attribution of geolocated item[edit]

I am trying to make use of GeoData API to perform aforementioned task.

I found GeoData API very clean, simple and user friendly in retrieving various data according to geo location of the item. But there are difficulties with retrieving country/city affiliation of the item. While country can theoretically be get in a single request (also not always but only if being specified and not in name format but rather by its alphabetic designation), the city is possible to be get only for items which are cities by themselves. Let's imagine I want to determine in one request providing the coordinates of the Sagrada Familia temple the name of the item and that it is located in Barcelona, Spain. As far as I understood there is no way to do that. From the second hand this information does exist for every geo tagged item and is available for example through WikiData SPARQL query service. But then I'll need to perform a second request to WikiData which I would have liked to avoid by all means.

Looking on advice on the optimal strategy to perform the desired. If not is it possible to add the country/city attributes to GeoData list=geosearch attributes?

 – The preceding unsigned comment was added by Chainastole (talk • contribs) at 14:05, 8 September 2018 (UTC).

@Chainastole: In general, you could rely on the P131 property (if I understand what you need correctly):
select distinct ?city ?cityLabel {
    wd:Q48435 wdt:P131+ ?city . ?city wdt:P31/wdt:P279* wd:Q7930989
    service wikibase:label { bd:serviceParam wikibase:language "en" }
}
Try it! -- Luitzen (talk) 13:07, 10 January 2019 (UTC)

Possible to search to return list of items, as per website search box?[edit]

I was wondering if it is possible to use the query service to replicate the search box found on the website.

This will return results simple enough - but the ordering needs to be there.

SELECT distinct ?item ?itemLabel ?itemDescription WHERE {  
  ?item ?label "Michael Jackson"@en.  
  ?article schema:about ?item .
  ?article schema:inLanguage "en" .
  ?article schema:isPartOf <https://en.wikipedia.org/>.	
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }    
}

Try it!

 – The preceding unsigned comment was added by 185.217.68.243 (talk • contribs) at 22:20, 17 October 2018‎ (UTC).

Which website? You could try MWAPI. -- Luitzen (talk) 12:37, 10 January 2019 (UTC)

sparql[edit]

se desea que los resultados de una consulta federada, empleando Wikidata y Europeana, sean empleados para alimentar un simple catálogo de imágenes asociadas a las obras de pintores impresionistas y/o expresionistas. Para cada obra, será necesario recopilar, como mínimo, la siguiente información: Nombre del autor Título de la obra Enlace a la imagen

Mapping Russian federal subjects[edit]

I'm trying to create a locator map of the administrative divisions of Russian federal subjects by using Mapframe/Maplink. This is what I got so far. The issue is that the query goes too deep in selecting some administrative divisions (hence the different shades of green), while others are not selected at all (like Berezniki (Q105002) and Perm (Q915)).

This is the query:

SELECT ?id
  (if(BOUND(?link),
      concat('[[', substr(str(?link),31,500),  '|', ?idLabel, ']]'),
      ?idLabel)
   as ?title)
WHERE {  
  {  ?id wdt:P31/wdt:P279* wd:Q192287.
     ?id wdt:P131 wd:Q5400. }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language 'en' .
    ?id rdfs:label ?idLabel .
  }
  OPTIONAL {?link schema:about ?id.
  ?link schema:isPartOf <https://en.wikipedia.org/> . }
} GROUP BY ?id ?link ?idLabel ?hexcolor ?idhexcolor

Try it!

--Underlying lk (talk) 11:58, 3 January 2019 (UTC)

  • Maybe replacing "wdt:P31/wdt:P279*" with "p:P31/ps:P31/wdt:P279*" works out. Someone set preferred rank on Q105002 which prevented it from showing up. --- Jura 12:41, 3 January 2019 (UTC)
Even after changing it too many maps look like Swiss cheese. This one for instance.--Underlying lk (talk) 14:11, 3 January 2019 (UTC)
I'm not sure if these maps are entirely defined at Wikidata. Maybe OSM lacks the relevant qid. It's a wmf staff maintained feature, maybe you could ask them. --- Jura 15:01, 3 January 2019 (UTC)

@Underlying lk: There are several reasons why you see overlapping areas and holes:

  • sometimes not only the next higher level for located in the administrative territorial entity (P131) is set but also the one after next. Thus lower-level entites are showing up which overlap the higher-level entities.
  • some items do not have the right value for instance of (P31), e.g. only city/town (Q7930989). Thus they are not returned by the query.
  • some items do not have any located in the administrative territorial entity (P131) set.
  • some towns are the administrative center of a region but itself they are not located in that region. On Wikidata this is sometimes wrongly stated and therefore those towns are not returned by the query but OSM has excluded the city area from the region area. Reverse happens too, Wikidata has the correct hirarchy but not OSM. --Pasleim (talk) 13:52, 4 January 2019 (UTC)

P6216 Statements without qualifiers[edit]

All copyright status (P6216) statements require qualifiers to more precisly specify copyright status, so I tried to write a query to catch statements without any queries. I come up with

SELECT ?item {
  ?item p:P6216 ?statement .
  MINUS {?statement ?pq_qual ?pq_obj }.
} LIMIT 1000

Try it!

but it does not seem to work. Any way to fix it it? --Jarekt (talk) 05:10, 4 January 2019 (UTC)

@Jarekt: This might be a bit inelegant, but it seems to work.:
SELECT ?item  {
  ?item p:P6216 ?statement .
  ?statement ?p ?q .
  bind (if(strstarts(str(?p),"http://www.wikidata.org/prop/qualifier/" ),1,0)as ?test) 
  filter(?test=0)  
} LIMIT 1000
Try it!
The cause of the problem was that, even without a qualifier, {?statement ?pq_qual ?pq_obj }. still finds stuff, so the solution is to test for the absence of the sort of stuff of interest - qualifiers. And there may be a more elegant way to do that than stringification and filtering, but I'm only a pretend SPARQList and haven't a clue what that might be:
SELECT ?item ?p ?q  {
  ?item p:P6216 ?statement .
  ?statement ?p ?q .
} LIMIT 100
Try it! --Tagishsimon (talk) 05:55, 4 January 2019 (UTC)
Jura, I was thinking about this while writing Help:Copyrights and the issue is that we might need different qualifiers for public domain (Q19652) and copyrighted (Q50423863) cases, and we did not start with many aspects of copyrighted works yet. So I was thinking about using separate queries for those 2 cases, like:
SELECT distinct ?item  ?itemLabel {
  ?item wdt:P6216 wd:Q19652 .# copyright status = public domain
  ?item p:P6216 ?statement .
  ?statement ?p ?q .
  FILTER NOT EXISTS { ?statement pq:P1001 ?j }
  FILTER NOT EXISTS { ?statement pq:P459  ?d }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} LIMIT 100
Try it!
Maybe once we figure out how to model copyrighted works, like files on Commons claiming "Own work" we would be able to specify that you always need applies to jurisdiction (P1001) and determination method (P459), but at this point it is unclear. By the way, Tagishsimon, thank you for your help debuging my query. --Jarekt (talk) 19:18, 4 January 2019 (UTC)
  • I think if you list several qualifier-properties in the same constraint statement, any of these will do. This way at least you wont end up without any qualifier at all. --- Jura 19:24, 4 January 2019 (UTC)

Entries of the Dictionary of African Biography[edit]

Hi there,
I already asked for a similar query, but nevertheless I'm not able to write one. I'm sorry, so I'm asking again for a query that

I would like to use the query for a working list to be updated by the ListeriaBot.

Would that be possible? Thank you!

Best regards, --Jcornelius (talk) 15:19, 4 January 2019 (UTC)


Sure, you came to the right place. Here is one:

SELECT ?item ?itemLabel ?itemDescription
  (GROUP_CONCAT(DISTINCT ?genderLabel) as ?genderLabel)
  (GROUP_CONCAT(DISTINCT ?natLabel; separator=", ") as ?natLabels)
WHERE   
{
    ?item wdt:P1343 wd:Q46002746 ; wdt:P31 wd:Q5  
    OPTIONAL { ?item wdt:P21 ?gender }
    OPTIONAL { ?item wdt:P27 ?nat  }              
	SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
        ?item rdfs:label ?itemLabel .                           
        ?item schema:description ?itemDescription .
        ?nat rdfs:label ?natLabel .
        ?gender rdfs:label ?genderLabel .                            
                           }
}
GROUP BY ?item ?itemLabel ?itemDescription
ORDER BY ?itemLabel

Try it!

It would be slightly simpler if you didn't ask for nationality, as that tends to be multi-valued, what would generate several lines for the same person with the below approach:

SELECT ?item ?itemLabel ?itemDescription  ?genderLabel
WHERE   
{
    ?item wdt:P1343 wd:Q46002746 ; wdt:P31 wd:Q5  
    OPTIONAL { ?item wdt:P21 ?gender }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en"}
}
ORDER BY ?itemLabel

Try it!

If you just add it to Listeria, the following could be sufficient, as Listeria allows to define the columns to display.

SELECT DISTINCT ?item 
WHERE   
{
    ?item wdt:P1343 wd:Q46002746 .
    ?item wdt:P31 wd:Q5 
}

Try it!

Hope that helps. --- Jura 16:05, 4 January 2019 (UTC)

Sitelinks query optimization[edit]

Hello. The task is to get a list of river items, which have specific language sitelinks and do not have Russian sitelink. I have made a query for this task, but it takes too much time, leading to timeout with full list on languages ("uk" "be" "pl" "cs" "sk" "bg" "mk" "sh" "hr" "sl" "bs" "hsb" "csb" "rue" "szl"). Is it possible to optimize this query? Now it takes 50 seconds to complete:

SELECT DISTINCT ?item ?itemLabel WHERE {
  ?item wdt:P31 wd:Q4022 .
  OPTIONAL {
    ?sitelink schema:about ?item .
    ?sitelink schema:isPartOf <https://ru.wikipedia.org/>
  }
  FILTER (!BOUND(?sitelink))
  VALUES ?lang { "bg" "mk" "sh" "hr" "sl" "bs" "hsb" "csb" "rue" "szl" } .
  ?sitelink2 schema:about ?item .
  ?sitelink2 schema:inLanguage ?lang .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "ru,uk,be,pl,cs,sk,bg,mk,sh,hr,sl,bs,hsb,csb,rue,szl" }
}

Try it!   — Vort (talk) 12:57, 5 January 2019 (UTC)

There seem to be plenty of items that don't have such a sitelink:

SELECT (COUNT(*) as ?ct)
WHERE 
{
  ?item wdt:P31 wd:Q4022 .
  MINUS { ?sitelink schema:about ?item ; schema:isPartOf <https://ru.wikipedia.org/> }
}

Try it!

Also, I'd guess there are even fewer in the other languages. Maybe doing the reverse could work:

SELECT DISTINCT ?item ?itemLabel 
WHERE 
{
  hint:Query hint:optimizer "None".
  ?item wdt:P31 wd:Q4022 .
  ?sitelink2 schema:about ?item ; schema:inLanguage "bg" .  
  FILTER NOT EXISTS { ?sitelink schema:about ?item ; schema:isPartOf <https://ru.wikipedia.org/> }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "ru,bg,en". }
}

Try it! --- Jura 13:11, 5 January 2019 (UTC)

It works for single language, but task is to check them all at once. — Vort (talk) 13:32, 5 January 2019 (UTC)
I managed to get this to run in 59.5 seconds - no labels though. Whether it succeeds depends on how loaded the report server is. 1327 results, so you could run them through another report to fetch labels.
SELECT DISTINCT ?item ?itemLabel with {
select ?item where
{
  hint:Query hint:optimizer "None".
  ?item wdt:P31 wd:Q4022 .
  VALUES ?lang { "bg" "mk" "sh" "hr" "sl" "bs" "hsb" "csb" "rue" "szl" } .
  ?sitelink2 schema:about ?item .
  ?sitelink2 schema:inLanguage ?lang .
} } as %i
where
{
include %i
minus { ?sitelink schema:about ?item ; schema:isPartOf <https://ru.wikipedia.org/> }
}
Try it! --Tagishsimon (talk) 14:31, 5 January 2019 (UTC)
@Vort: ~40 seconds:
select ?item ?itemLabel {
  hint:Query hint:optimizer "Runtime" .
  ?item wdt:P31 wd:Q4022 .
  filter not exists { 
    ?sitelink schema:about ?item; schema:inLanguage ?lang .              
    filter (?lang = 'ru')
  }
  filter exists {
    ?sitelink schema:about ?item; schema:inLanguage ?lang .             
    filter (?lang in ("uk","be","pl","cs","sk","bg","mk","sh","hr","sl","bs","hsb","csb","rue","szl"))
  }
  service wikibase:label { bd:serviceParam wikibase:language "ru,uk,be,pl,cs,sk,bg,mk,sh,hr,sl,bs,hsb,csb,rue,szl" }
}
Try it! -- Luitzen (talk) 17:58, 6 January 2019 (UTC)
@Luitzen: Thank you! Ping fails without ~~~~. — Vort (talk) 04:33, 12 January 2019 (UTC)

Movie after a work by OR based on work from Jane Austen[edit]

Hello, cloud you please help me to write correctly this query?

#Movie after a work by OR based on works from Jane Austen
SELECT ?item ?itemLabel 
WHERE 
{
    ?item wdt:P31 wd:Q11424;
     OPTIONAL {(wdt:P144) [wdt:P50 wd:Q36322]}.
     OPTIONAL {(wdt:P1877) [wdt:P50 wd:Q36322]}.
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Thanks in advance

--2le2im-bdc (talk) 20:01, 6 January 2019 (UTC)

#Movie after a work by OR based on works from Jane Austen
SELECT ?item ?itemLabel 
WHERE 
{
    ?item wdt:P31 wd:Q11424 . # item is a film
    ?work wdt:P50 wd:Q36322 . # work is by Austen
           {?item wdt:P144* ?work }    # item is based on work
     union {?item wdt:P1877* ?work }.  # OR item is after work
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
@2le2im-bdc: Here you go! The two types of thing (P144 or P1877) are in seperate UNION queries, and the * element means that they also go down the chain - so items based on a work X which is itself based on a work by Austen. (This adds one thing, which is a film based on a parody novel). Andrew Gray (talk) 20:30, 6 January 2019 (UTC)
Great! Thanks a lot @Andrew Gray:.--2le2im-bdc (talk) 09:59, 7 January 2019 (UTC)
SELECT DISTINCT ?item ?itemLabel ?itemDescription
WHERE
{
    hint:Query hint:optimizer "None".
    ?item wdt:P31 wd:Q11424 .
    { ?item wdt:P144*/wdt:P50 wd:Q36322 }
    union { ?item wdt:P1877 wd:Q36322  }
    UNION { ?item wdt:P58 wd:Q36322 }  
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?itemDescription

Try it!

Maybe this one? I also added the filmography template to Talk:Q36322 --- Jura 22:12, 6 January 2019 (UTC)
Thanks a lot @Jura1:!
  1. Is it a way in SPARQL to declare the item (Q36322) one time only and to reuse it with a ?xy ? It would be easier to reuse the query with other author. Could be also possible to insert the model of query in the list of examples?
  2. I have not understand where is the link to the filmography in Talk:Q36322?
Thanks in advance. --2le2im-bdc (talk) 09:59, 7 January 2019 (UTC)
@2le2im-bdc: Answer to Q1 below. Answer to Q2 is, each of the links in the rendered template Jura added to Talk:Q36322 provide SPARQL queries centered around Jane Austin, delivering whatever subset of items are indicated by the report title.
SELECT DISTINCT ?item ?itemLabel ?itemDescription
WHERE
{
    hint:Query hint:optimizer "None".
    VALUES ?author {wd:Q36322}
    ?item wdt:P31 wd:Q11424 .
    { ?item wdt:P144*/wdt:P50 ?author }
    union { ?item wdt:P1877 ?author  }
    UNION { ?item wdt:P58 ?author }  
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?itemDescription
Try it! --Tagishsimon (talk) 10:17, 7 January 2019 (UTC)
Great! Thanks a lot @Tagishsimon:. Cloud I ask : in Q1, what means "hint:Query hint:optimizer "None""? In Q2 : where is the list resulting of this query? --2le2im-bdc (talk) 11:41, 7 January 2019 (UTC)
@2le2im-bdc:"hint:Query hint:optimizer "None"" tells Blazegraph, the report engine, not to try to optimise the SPARQL, but instead to run the query in the order specified. [1]. For any of the queries on this page, or on the talk page that Jura added the template to, you need to select "try it" or on the talk page, hit the any of the links; then you need to hit the blue 'play' button in the middle left side of the report user interface (i.e. white right-pointing arrow with blue square background). The report then runs, results appear. Hope that's what you were asking. --Tagishsimon (talk) 11:46, 7 January 2019 (UTC)
Thanks a lot @Tagishsimon:. I have try now to remplace film (Q11424) with audiovisual work (Q2431196) in the way to catch also the television series, but I have a timeout problem. Is something to be done?
SELECT DISTINCT ?item ?itemLabel ?itemDescription ?director ?directorLabel
WHERE
{
    hint:Query hint:optimizer "None".
    VALUES ?author {wd:Q36322}
    ?item wdt:P31/wdt:P279* wd:Q2431196 .
    ?item wdt:P57 ?director;
    { ?item wdt:P144*/wdt:P50 ?author }
    union { ?item wdt:P1877 ?author  }
    UNION { ?item wdt:P58 ?author }  
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en, [AUTO_LANGUAGE]". }
}
ORDER BY ?itemLabel ?itemDescription
Try it!
--2le2im-bdc (talk) 12:01, 7 January 2019 (UTC)

@2le2im-bdc: Possibly not quickly ... I'll check in later today. Audio-visual works has about 590 subclasses, and so the search-space is now way too big for the 1 minute of processing time allocated to any report ... ?item wdt:P31/wdt:P279* wd:Q2431196 . finds way too many items to look at - about 500,000. As a rule of thumb, the report server starts creaking around the 100k to 200k mark, depending on the nature of the query.

SELECT (count (*) as ?count)
WHERE
{
   ?item wdt:P31/wdt:P279* wd:Q2431196 .
}
Try it! --Tagishsimon (talk) 12:11, 7 January 2019 (UTC)
Thanks a lot @Tagishsimon:. --2le2im-bdc (talk) 12:20, 7 January 2019 (UTC)
@Tagishsimon: Just a idea, I will be perhaps easier to go bottom-up. Examples:
  1. For all the works from Jane Austen (Q36322) display all the value from derivative work (P4969) wich are audiovisual work (Q2431196).
  2. For all the works wich are derivative work (P4969) by a work from Jane Austen (Q36322)(but not audiovisual work (Q2431196)) display all the value from derivative work (P4969) wich are audiovisual work (Q2431196).
  3. For all the works from Jane Austen (Q36322) wich are value from after a work by (P1877) or based on (P144) display the items if it's a audiovisual work (Q2431196).
--2le2im-bdc (talk) 16:13, 7 January 2019 (UTC)
@2le2im-bdc: Possibly ... back in a couple of hours, when I'll be able to think about it. --Tagishsimon (talk) 16:28, 7 January 2019 (UTC)
@Tagishsimon:. There is no emergency for this demand. I give you just the context of it : New York Public Library have publish a post intituled "A Movie for Every Jane Austen Novel Written". I have had the idea that a Query on Wikidata can also do that. The list of the NYPL include :
and the list is order by the inception (P571) of the works of Jane Austen. Thanks a lot for your help --2le2im-bdc (talk) 20:15, 7 January 2019 (UTC)
@2le2im-bdc: This is not quite your suggestion, but gets us (probably) most of the relevant items in the audiovisual tree, by selecting any item which has a JA connection, and then filtering the item on membership of the audiovisual work tree. Right now I run into problems with inception (P571), since the after a work by (P1877) and screenwriter (P58) selects point direct to JA without obviously specifying which of her works is involved. Equally, I'm probably a bit to tired to think straight right now.
SELECT DISTINCT ?item ?itemLabel ?itemDescription
with { select ?item where
{
    hint:Query hint:optimizer "None".
    VALUES ?author {wd:Q36322}
    { ?item wdt:P144*/wdt:P50 ?author }
    union { ?item wdt:P1877 ?author  }
    UNION { ?item wdt:P58 ?author }  
} } as %i
where
{
  include %i
  ?item wdt:P31/wdt:P279* wd:Q2431196 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
Try it! --Tagishsimon (talk) 20:44, 7 January 2019 (UTC)
It's great! Thanks a lot @Tagishsimon:. --2le2im-bdc (talk) 21:05, 7 January 2019 (UTC)
Just a side note: Template:Filmography was designed mainly for actors/directors, but it worked surprisingly well on Talk:Q36322. It doesn't include directly the same queries as mentioned here. I hope eventually it will work just as well for writers. --- Jura 07:14, 8 January 2019 (UTC)

number of publications from before 1800 without copyright statement[edit]

I am looking for number of publications from before 1800 without copyright statement, however below query seems to time out. Any idea what is wrong with it?

SELECT (count(?item) as ?num) where {
  ?item wdt:P577 ?dop . 
  FILTER(?dop < "1800"^^xsd:dateTime).
  MINUS { ?item wdt:P6216 [] } . 
}

Try it!

--Jarekt (talk) 15:46, 7 January 2019 (UTC)

@Jarekt: Mainly, I think, we start with way too many P577s - all films, books, and for all I know academic journal articles. And even if we constrain by adding a ?item wdt:P31/wdt:P279* wd:Q732577 . to constrain it to all types of publications, and comment out the filter and the minus it still times out. So I think you have to segment your queries, by picking off the subclasses of publication you are interested in - see the set at:
SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P279* wd:Q732577.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
and then run, for instance
SELECT (count(?item) as ?num) where {
  ?item wdt:P31 wd:Q571 . # book
  ?item wdt:P577 ?dop . 
  FILTER(?dop < "1800"^^xsd:dateTime).
  MINUS { ?item wdt:P6216 [] } . 
}
Try it!
and since that only takes 3 seconds, maybe think in terms of selecting a set of values to query for, based on the values we got from the ?item wdt:P279* wd:Q732577. list from earlier. I've not looked - it may well be that there's just one subclass of publications - probably academic journal articles - which is killing a query based on ?item wdt:P31/wdt:P279* wd:Q732577 .. So the values statement in the query below can be quite large. You have to experiment. Something like that, anyway. Ah yes. Remember filtering strings is quite inefficient - not that you have a choice in the matter, so far as I can see - but it does further constrain the set of items you can count. And, finally, remember I'm not a SPARQL expert, just doing my best. Others may be able to give better advice.
SELECT (count(?item) as ?num) where {
  VALUES ?things {wd:Q571 wd:Q1002697 wd:Q18536349} #stuff I chose at random
  ?item wdt:P31 ?things . # book
  ?item wdt:P577 ?dop . 
  FILTER(?dop < "1800"^^xsd:dateTime).
  MINUS { ?item wdt:P6216 [] } . 
}
Try it! --Tagishsimon (talk) 16:26, 7 January 2019 (UTC)
Face-smile.svg Thank you --Jarekt (talk) 14:39, 8 January 2019 (UTC)
@Jarekt: Should be 1s or less:
SELECT (COUNT(DISTINCT ?item) AS ?num) WHERE {
  ?item wdt:P577 ?dop . 
  hint:Prior hint:rangeSafe true .
  FILTER (?dop < "1800-01-01T00:00:01"^^xsd:dateTime)
  FILTER NOT EXISTS { ?item wdt:P6216 [] }  
}
Try it! -- Luitzen (talk) 10:40, 10 January 2019 (UTC)

All inhabited places with at least population of 1000 per country[edit]

Since there are different cl;asses for inhabited places (residenial communities, residential areas, inhabited places etc.) and the're maybe more precise population data e.g. in dbpedia, I wonder how to combine those facts into one query.

Your major problem here is, too many settlements, too little processing time. The basic query is below - limiting it to 10 rows takes 28 seconds, and you have an allowance of 60 seconds. So you can push the limit up, but sooner or later you'll get a timeout. You can also constrain it, e.g. by adding ?item wdt:P17 wd:Q145. to pick off a single country, but the basic problem of too many targets still pretty-much remains. The salient feature of the query is wdt:P31/wdt:P279* which reads "is an instance of a human settlement (the P31), or is a subclass of a human settlement (the P279), or a subclass of a subclass to however many levels of depth (the *):
SELECT ?item ?itemLabel ?population
WHERE 
{
  ?item p:P1082 [ps:P1082 ?population]. #item has a population property & ps:P1082 provides the value
  ?item wdt:P31/wdt:P279* wd:Q486972.   #item is a human settlement, or a subclass of a human settlement
  filter (?population>1000)             #population is greater than 1000
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} limit 10
Try it! --Tagishsimon (talk) 10:31, 9 January 2019 (UTC)

Get all the hashtags, facebook page and official websites of every french broadcast content[edit]

Hey there,

I try to get hashtags, facebook pages and official websites of every french broadcast contents (TV and radio). I used this query but it fails: SELECT DISTINCT ?television_programLabel ?hashtag_Twitter ?diffuseur_originalLabel ?site_officiel ?blog_officiel ?page_Facebook_officielle WHERE {

 ?television_program wdt:P495 wd:Q142.
 ?television_program wdt:P279 wd:Q15416.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
 OPTIONAL { ?television_program wdt:P495 ?pays_d_origine. }
 OPTIONAL { ?television_program wdt:P2572 ?hashtag_Twitter. }
 OPTIONAL { ?television_program wdt:P449 ?diffuseur_original. }
 OPTIONAL { ?television_program wdt:P2561 ?titre. }
 OPTIONAL { ?television_program wdt:P856 ?site_officiel. }
 OPTIONAL { ?television_program wdt:P1581 ?blog_officiel. }
 OPTIONAL { ?television_program wdt:P4003 ?page_Facebook_officielle. }

}

@Bblanckemane: This - you needed a P31:
SELECT DISTINCT ?television_programLabel ?hashtag_Twitter ?diffuseur_originalLabel ?site_officiel ?blog_officiel ?page_Facebook_officielle WHERE {
 ?television_program wdt:P495 wd:Q142.
 ?television_program wdt:P31/wdt:P279* wd:Q15416.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
 OPTIONAL { ?television_program wdt:P495 ?pays_d_origine. }
 OPTIONAL { ?television_program wdt:P2572 ?hashtag_Twitter. }
 OPTIONAL { ?television_program wdt:P449 ?diffuseur_original. }
 OPTIONAL { ?television_program wdt:P2561 ?titre. }
 OPTIONAL { ?television_program wdt:P856 ?site_officiel. }
 OPTIONAL { ?television_program wdt:P1581 ?blog_officiel. }
 OPTIONAL { ?television_program wdt:P4003 ?page_Facebook_officielle. }
}
Try it! --Tagishsimon (talk) 10:33, 9 January 2019 (UTC)
And if you want TV and radio, you maybe need to go higher up the class tree - try ?television_program wdt:P31/wdt:P279* wd:Q2431196. --Tagishsimon (talk) 10:36, 9 January 2019 (UTC)


Boris Blk (talk) 10:58, 9 January 2019 (UTC) Thanks a lot. I tried with ?television_program wdt:P31/wdt:P279* wd:Q11578774. instead of ?television_program wdt:P31/wdt:P279* wd:Q2431196. because the latter bringed back some irrelevant results but it seems to me that using wd:Q11578774, some shows are missing (for instance this one https://www.wikidata.org/wiki/Q2941007)

@Bblanckemane: Maybe choose relevant items from this first level of the subclass tree of audiovisual works:
SELECT ?item ?itemLabel WHERE {
  ?item wdt:P279 wd:Q2431196.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?itemLabel
Try it!
and use a query like this:
SELECT DISTINCT ?television_programLabel ?hashtag_Twitter ?diffuseur_originalLabel ?site_officiel ?blog_officiel ?page_Facebook_officielle WHERE {
  ?television_program wdt:P495 wd:Q142.
  VALUES ?type {
    wd:Q11578774 # broadcasting program
    wd:Q11424 # film
    wd:Q23811232 # film and series for children
    wd:Q57608327 # radio series episode
    wd:Q15709880 # serial
    wd:Q15416 # television program
    wd:Q21191270 # television series episode
  }
  ?television_program wdt:P31/wdt:P279* ?type.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
  OPTIONAL { ?television_program wdt:P495 ?pays_d_origine. }
  OPTIONAL { ?television_program wdt:P2572 ?hashtag_Twitter. }
  OPTIONAL { ?television_program wdt:P449 ?diffuseur_original. }
  OPTIONAL { ?television_program wdt:P2561 ?titre. }
  OPTIONAL { ?television_program wdt:P856 ?site_officiel. }
  OPTIONAL { ?television_program wdt:P1581 ?blog_officiel. }
  OPTIONAL { ?television_program wdt:P4003 ?page_Facebook_officielle. }
}
Try it! --Tagishsimon (talk) 12:38, 9 January 2019 (UTC)

195.221.138.38 14:06, 9 January 2019 (UTC) Thanks again! It works perfectly. I just understood how to use "VALUES"!

Query optimization[edit]

Hi, this query:

SELECT DISTINCT ?item ?itemLabel ?souradnice
WHERE
{
?item wdt:P31 wd:Q721747 .
?item wdt:P131* wd:Q1085 .
?item wdt:P625 ?souradnice .
SERVICE wikibase:label { bd:serviceParam wikibase:language "cs"}
}

Try it!

reportedly worked a few days ago, now timeouting. Even "bare":

SELECT ?item
WHERE
{
?item wdt:P31 wd:Q721747 .
?item wdt:P131* wd:Q1085 .
}

Try it!

is timeouting. Any ideas to make it work?--Jklamo (talk) 15:10, 9 January 2019 (UTC)

@Jklamo: Since there are less items located in Prague (Q1085) than commemorative plaque (Q721747) overall, we can tell the optimizer that it should look for those first:
SELECT ?item ?itemLabel ?coordinates WHERE {
  hint:Query hint:optimizer "None".
  ?item wdt:P131* wd:Q1085.
  ?item wdt:P31 wd:Q721747.
  ?item wdt:P625 ?coordinates.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --TweetsFactsAndQueries (talk) 15:27, 9 January 2019 (UTC)
Thanks. At facebook User:Vojtěch Dostál suggested
SELECT DISTINCT ?item ?itemLabel ?souradnice
WHERE
{
?item wdt:P31 wd:Q721747 .
{?item wdt:P131 wd:Q1085 .} union {?item wdt:P131/wdt:P131 wd:Q1085 .} union {?item wdt:P131/wdt:P131/wdt:P131 wd:Q1085 .}
?item wdt:P625 ?souradnice .
SERVICE wikibase:label { bd:serviceParam wikibase:language "cs"}
}
Try it!
which is even faster (there seem to be cycling/cascading error somewhere in wdt:P131* wd:Q1085 tree), but certainly less elegant.
Is there any documentation for "hint:Query hint:optimizer" usage? Unfortunately, there is nothing about it in Wikidata:SPARQL tutorial, meta:Wikidata Query Service/User Manual or even in Wikidata:SPARQL query service/query optimization.--Jklamo (talk) 16:34, 9 January 2019 (UTC)
@Jklamo: How helpful it is is debatable, but yes. The engine underlying WDQS is Blazegraph, and they have a wiki - https://wiki.blazegraph.com/wiki/index.php/QueryHints --Tagishsimon (talk) 16:40, 9 January 2019 (UTC)
I also commend https://wiki.blazegraph.com/wiki/index.php/NamedSubquery to your attention - the idea of using two linked queries, the first of which gathers a minimal set of data, the second of which does more expensive stuff like getting labels, etc. I seem to use it regularly as an alternative to nested queries, for the reason that I can be sure about in which order the engine will do things. If you look back through the archives of this page, perhaps by searching for 'include', you'll find heaps of examples. --Tagishsimon (talk) 16:43, 9 January 2019 (UTC)

SPARQL query result erroneous[edit]

select ?item ?itemLabel ?sitelinks
with {
   select distinct ?item 
   where {
      hint:Query hint:optimizer "None".
      values ?item_class {wd:Q6665249 wd:Q19595175}
      ?item wdt:P106 ?item_class; wdt:P21 wd:Q6581072; wdt:P31 wd:Q5.
      filter not exists {[] 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,en".}
} 
order by desc(?sitelinks) asc(?num)

Try it!

Within the first ten result items there is Kanae Yamabe (Q11471016) but this item has a dewiki sitelink to w:de:Kanae Yamabe. But the query has the exclusion: filter not exists {[] schema:about ?item; schema:isPartOf <https://de.wikipedia.org/>}, what has to exclude items that have a sitelink to dewiki. Why is Kanae Yamabe (Q11471016) part of the result list, it has to be excluded? Doc Taxon (talk) 09:25, 10 January 2019 (UTC)

@Doc Taxon: I forget the Phab ticket number, but there have been issues with data loads to WDQS servers, which in short means that they no longer properly represent the state of wikidata. Although the issues are on the margin, you'll occasionally spot them as you have in this instance. I deleted the de.wiki sitelink and re-added it. The report now does what you'd expect, at least in respect of this item. Grafana shows that there is up to a ~25 million difference in the number of triples on various of the WDQS servers - a clear illustration of the not quite ready for production status of WDQS. [2] It is all most regrettable and has been going on for way too long. --Tagishsimon (talk) 09:50, 10 January 2019 (UTC)
@Tagishsimon: can you find out the phab ticket number? Doc Taxon (talk) 09:52, 10 January 2019 (UTC)
@Doc Taxon: Last discussion I can find is here. I don't know a) which ticket, presuming there is a current ticket, is taking the matter forwards, nor even b) if the matter is actively being attended to by those who provide the infrastructure. The limit of my competence is knowing the issue exists, and that the instant fix is always to remove & reinsert the troubled property. --Tagishsimon (talk) 10:12, 10 January 2019 (UTC)

Find longest substrings in a title[edit]

I am exploring this with the intention to use these substrings as a basis for assisting with the main subject (P921) tagging of works. I got as far as extracting the second word for a given title:

The following query uses these:

  • Properties: title (P1476) View with Reasonator View with SQID, instance of (P31) View with Reasonator View with SQID, main subject (P921) View with Reasonator View with SQID
     1 SELECT * WHERE {
     2   ?work wdt:P1476 ?title.
     3   ?work wdt:P31 wd:Q13442814.
     4   BIND(STRAFTER(?title, " ") AS ?poststring)
     5   BIND(STRBEFORE(?poststring, " ") AS ?prepoststring)
     6   BIND(STRLEN(REPLACE(?prepoststring, " ", "")) AS ?length)
     7   FILTER(NOT EXISTS { ?work wdt:P921 ?topic. })
     8   FILTER(?length > 5)
     9 }
    10 #ORDER BY DESC(?length)
    11 LIMIT 100
    

Those second words of each title can then be ranked manually across titles (the ORDER command times out) but I would actually like to extract the longest three (or so) substrings for each title, and rank the works accordingly. Of course, finding associated lexemes would also be great but I guess that's a bit too much for this query. Thanks for any pointers. --Daniel Mietchen (talk) 06:28, 12 January 2019 (UTC)

I fiddled around a bit more:
SELECT * WHERE {
  {
    VALUES (?title) {
      ("a test string to find a way to find the longest three (or so) substrings in a given string, though I would be fine with something like MAX(?substring) as well")
      ("being able to extract n-grams for n > 1 would be great too")
      (" another test string that starts and ends with space characters ")
      ("writing documentation is useful")
      ("OneWordTitleInCamelCase")
      ("Thanks for your help!")
    }
  }
  UNION
  {
    {
      SELECT ?title WHERE {
        ?work wdt:P1476 ?title. # let's keep the different languages in for the moment and worry about selecting the desired ones later
        ?work wdt:P31 wd:Q13442814 .
        FILTER(REGEX(?title, "\\s+"))   # The workflow below assumes the presence of spaces in the title. Need a way to bring in on-word titles but let's worry about that later too.

      }
      LIMIT 10
    }
  }
  BIND(STRLEN(REPLACE(?title, " ", "")) AS ?titlelength)
  
  BIND(STRBEFORE(?title, " ") AS ?substring1)
  BIND(STRLEN(REPLACE(?substring1, " ", "")) AS ?substring1length)
  BIND(STRAFTER(?title, " ") AS ?postfix)
  BIND(STRLEN(REPLACE(?postfix, " ", "")) AS ?postfixlength)
  BIND(STRBEFORE(?postfix, " ") AS ?substring2)
  BIND(STRLEN(REPLACE(?substring2, " ", "")) AS ?substring2length)
  BIND(STRAFTER(?postfix, ?substring2) AS ?postsubstring2)
  BIND(STRLEN(REPLACE(?substring2, " ", "")) AS ?postsubstring2length)
}
ORDER BY DESC(?substring1length) #order by longest first substring
# ORDER BY DESC(?substring2length) #order by longest second substring

# We still need to bind all those substrings together to be able to 
# identify the (up to) three longest one per title
Try it!
--Daniel Mietchen (talk) 15:39, 12 January 2019 (UTC)
I asked this on StackOverflow as well, where I got a partial solution in Python (thanks to User:Luitzen) and a suggestion that SPARQL alone cannot do it. I also set up a Listeria list here to have something to play with. Now I have the following problems:
  1. I'd like to combine all these substringN variables into a single one, deduplicating their values on the go.
  2. the FILTER command usually times out
Ad 1: that variable (let's call it ?substring) should thus contain all the (up to three) longest substrings for all the titles in the query results, ideally deduplicated and ordered by ?substringlength
Ad 2: I am doing lots of other queries involving main subject (P921), and for a limit of 100 as in the Listeria list, I would not expect a time out. I suspect that this needs some attention in terms of the overall structure of the query and perhaps the query optimizer, but I have not looked into that yet. --Daniel Mietchen (talk) 02:18, 13 January 2019 (UTC)

Qualifers on a Heritage Register Item[edit]

Hi All,

Trying to query the following, probably quite simple although can't quite seem to get it:

Essentially would like a query that returns:

All items that have the heritage designation (P1435); as 'Heritage Act - State Heritage Register' (Q28152854) WHERE there is no 'Inventory Number' (P217) qualifier.

Thanks

@Nickw25: This, by the looks of it:
SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P1435 wd:Q28152854.
  filter not exists {?item p:P1435 [pq:P217 [] ].}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Tagishsimon (talk) 13:03, 12 January 2019 (UTC)

Pefect! Thanks @Tagishsimon:

Wikielements that contains only a redirection[edit]

Hello everyone, I'd like to check any [all // airports] that have only [or contains] a wikipedia redirection page like this one Lviv Stryi Airport (Q6706931). Thanks!

@Bouzinac: You'd need to cross-tabulate the results of the query below with the results of https://quarry.wmflabs.org/query/32587 ... which I'm doing right now; but it might take some time. Results later, probably.
SELECT ?item ?itemLabel ?sitelink ?article
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q62447.
?sitelink ^schema:name ?article .
 ?article schema:about ?item ;
  schema:isPartOf <https://en.wikipedia.org/> .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Tagishsimon (talk) 17:13, 13 January 2019 (UTC)
The link https://quarry.wmflabs.org/query/32587 provides a text search "airport" that does really the job! Thanks Tagishsimon (talkcontribslogs) Bouzinac (talk) 18:58, 13 January 2019 (UTC)
@Bouzinac: Yup, there's that ;). Note, though, that the SQL query is only giving you redirects to en.wikipedia ... we'd better run some more for other languages, sometime. --Tagishsimon (talk) 19:02, 13 January 2019 (UTC)
Yes, I am sometimes surprised a redirection justifies a wikielement… Giving deduplication work, phew! Bouzinac (talk) 19:05, 13 January 2019 (UTC)
I've set up another couple, but they'll take a while to run. I'll list more here as I create them ... you can play the same game by logging into quarry and forking existing reports. Wikidata items should not point to redirects ... it's a shortcoming which isn't addressed by any wikicode. The sitelink pointed to a valid article at the time of its insertion; later the article became a redirect, but the item was not updated (as it is, for instance, when the page title changes). I take it you'll be discovering some more duplicate items as a result of this. --Tagishsimon (talk) 19:09, 13 January 2019 (UTC)

Optimize query[edit]

Hello, is it possible to optimize the above query? I use it to generate graph on this page but it takes too many time so it can be displayed.

SELECT ?type ?count { {
  SELECT ("Avec" as ?type) (COUNT(DISTINCT ?item) as ?count)
  WHERE {
    ?item (wdt:P131/wdt:P131) wd:Q3131.
    ?item (wdt:P31/wdt:P279*) wd:Q16970.
    ?item wdt:P625 ?coor.} }
UNION {
  SELECT ("Sans" as ?type) (COUNT(DISTINCT ?item) as ?count)
  WHERE {
    ?item (wdt:P131/wdt:P131) wd:Q3131.
    ?item (wdt:P31/wdt:P279*) wd:Q16970.
  MINUS { ?item wdt:P625 [].} } }
}
ORDER BY ?type

Try it!

Thanks. Ayack (talk) 11:09, 14 January 2019 (UTC)

@Ayack: It's taking only 5.5s for me. This is another approach, but it seems to take much the same time - 5 to 5.5s. I'd not like to opine on which is the more efficient, but I have a feeling that doing the select only once intuitively is better; depends how much overhead the conditional bind adds.
SELECT ?type (COUNT(DISTINCT ?item) as ?count) where
{
    ?item (wdt:P131/wdt:P131) wd:Q3131.
    ?item (wdt:P31/wdt:P279*) wd:Q16970.
    optional {?item wdt:P625 ?coor.} 
    bind(if(bound(?coor),"avec","sans") as ?type)
  }
GROUP BY ?type
Try it! --Tagishsimon (talk) 12:05, 14 January 2019 (UTC)
@Ayack: Try to add hint:Query hint:optimizer "Runtime". to the Tagishsimon's query. -- Luitzen (talk) 22:07, 14 January 2019 (UTC)
@Tagishsimon, Luitzen: Thanks a lot, it's way faster now! Ayack (talk) 09:26, 15 January 2019 (UTC)
There is another one query I can't success in "converting" to Tagishsimon approach. Could you help me please?
SELECT ?type ?count { {
  SELECT ("Avec" as ?type) (COUNT(DISTINCT ?item) as ?count)
  WHERE {
    ?item (wdt:P131/wdt:P131) wd:Q3131.
    ?item (wdt:P31/wdt:P279*) wd:Q16970.
    ?article schema:about ?item .
    ?article schema:isPartOf <https://fr.wikipedia.org/>.} }
UNION {
  SELECT ("Sans" as ?type) (COUNT(DISTINCT ?item) as ?count)
  WHERE {
    ?item (wdt:P131/wdt:P131) wd:Q3131.
    ?item (wdt:P31/wdt:P279*) wd:Q16970.
    OPTIONAL { ?article schema:about ?item . }
  FILTER NOT EXISTS { ?wfr schema:about ?item ; schema:inLanguage "fr" } } }
}
ORDER BY ?type
Try it!
Thanks. Ayack (talk) 09:39, 15 January 2019 (UTC)
@Ayack:
SELECT ?type (COUNT(DISTINCT ?item) AS ?count) {
    hint:Query hint:optimizer "Runtime" .
    ?item (wdt:P131/wdt:P131) wd:Q3131. 
    ?item (wdt:P31/wdt:P279*) wd:Q16970.
    OPTIONAL {?wfr schema:about ?item ; schema:inLanguage "fr"}
    BIND (IF(BOUND(?wfr), 'Avec', 'Sans') AS ?type)
} GROUP BY ?type
Try it!
Thanks @Luitzen:!! Ayack (talk) 10:21, 15 January 2019 (UTC)
@Ayack: You're welcome! Less magic:
SELECT ?type (COUNT(DISTINCT ?item) AS ?count) {
    hint:Query hint:optimizer "None" .
    ?superitem1 wdt:P131 wd:Q3131 .
    ?item wdt:P131 ?superitem1.
    ?item wdt:P31/wdt:P279* wd:Q16970.      
    OPTIONAL {?wfr schema:about ?item ; schema:inLanguage "fr"}
    BIND (IF(BOUND(?wfr), 'Avec', 'Sans') AS ?type)
} GROUP BY ?type
Try it!
or
SELECT ?type (COUNT(DISTINCT ?item) AS ?count) {
    ?superitem wdt:P131 wd:Q3131 .
    hint:Prior hint:runFirst true .
    ?item wdt:P131 ?superitem .
    ?item wdt:P31/wdt:P279* wd:Q16970 .      
    OPTIONAL {?wfr schema:about ?item ; schema:inLanguage "fr"}
    BIND (IF(BOUND(?wfr), 'Avec', 'Sans') AS ?type)
} GROUP BY ?type
Try it!

Tallest buildings[edit]

Hello, is it possible to have a count of buildings taller than 200 meters ? This source [3] says there would be 1478 buildings higher than 200m. Would wikidata say the same? Bouzinac (talk) 11:37, 14 January 2019 (UTC)

@Bouzinac: Seems not. Spoiler: 391.
SELECT (count(distinct ?item) as ?count) where
{
    ?item wdt:P31/wdt:P279* wd:Q41176.
    ?item p:P2048/psn:P2048/wikibase:quantityAmount ?height.
    filter (?height>200)
  }
Try it! --Tagishsimon (talk) 12:16, 14 January 2019 (UTC)
So it means there is roughly 1000 skyscraper not notorious enough to have a wikidata element :/ Bouzinac (talk) 12:24, 14 January 2019 (UTC)
Not to mention false statements like this one Forte Corno d'Aola (Q3748541) confusing altitude and height lol Well...Bouzinac (talk) 12:30, 14 January 2019 (UTC)
There may be more data down there [4]. Would it be a nice idea to import these inside wikidata ? Or too trash ? Bouzinac (talk) 12:41, 14 January 2019 (UTC)
No, very likely a very good source indeed; but I think we'd need to talk to them about it. I don't know what the copyright position / database protection situation is with the information they hold, but I can imagine it might well not be legal to take it without permission. I've pinged them via twitter to see if I can elicit any interest. --Tagishsimon (talk) 13:03, 14 January 2019 (UTC)
We have most of the items, but usage the of height (P2048) is low. Also at the moment, we are not able to distinguish status of buildings (completed, under construction, planned, vision etc.). But feel free to found Wikidata:WikiProject Skyscrapers.--Jklamo (talk) 09:53, 15 January 2019 (UTC)
Looks like we mostly have the buildings, lack the heights. Specifically we have Skyscraper Center ID (P1305) and Skyscraper Center building complex ID (P2762):
SELECT ?item ?itemLabel ?height where
{
  ?item wdt:P1305 [].
  optional {?item wdt:P2048 ?height}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Tagishsimon (talk) 13:18, 14 January 2019 (UTC)

Teams with colours[edit]

Hello. I want a query to find all items that have:

instance of (P31) -> association football club (Q476028) or instance of (P31) -> sports club (Q847017) or instance of (P31) -> national association football team (Q6979593)

and

color (P462) with any value.

Xaris333 (talk) 16:32, 14 January 2019 (UTC)

@Xaris333:
SELECT distinct ?item ?itemLabel (group_concat( distinct ?colorLabel; separator=", ") as ?colour) where
{
  values ?thing {wd:Q476028 wd:Q847017 wd:Q6979593}
  ?item wdt:P31 ?thing.
  ?item wdt:P462 ?color.
  ?color rdfs:label ?colorLabel. filter(lang(?colorLabel)="en")
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?item ?itemLabel order by ?itemLabel
Try it! --Tagishsimon (talk) 17:40, 14 January 2019 (UTC)

Thanks! Xaris333 (talk) 18:00, 14 January 2019 (UTC)

Bloody @Xaris333:, moving all the data to official color (P6364) and making me think I was going mad when running the above to get zero results. You've taken years off my lifespan. Tell me that you didn't do all that by hand? Totally a job for quickstatements.
SELECT distinct ?item ?itemLabel (group_concat( distinct ?colorLabel; separator=", ") as ?colour) where
{
  values ?thing {wd:Q476028 wd:Q847017 wd:Q6979593}
  ?item wdt:P31 ?thing.
  ?item wdt:P6364 ?color.
  ?color rdfs:label ?colorLabel. filter(lang(?colorLabel)="en")
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?item ?itemLabel order by ?itemLabel
Try it! --Tagishsimon (talk) 21:35, 17 January 2019 (UTC)

Discontinued properties[edit]

Could anyone help me find out what are the properties pointing to archive.org in their formatter url? So far I only know of P3183, but I want to see if we have more like that and tag them properly with Wikidata property for a discontinued website (Q60457486).--Micru (talk) 18:47, 15 January 2019 (UTC)

@Micru: Why do they require Wikidata property for a discontinued website (Q60457486)?
SELECT ?item ?itemLabel ?fURL
WHERE 
{
  ?item wdt:P1630 ?fURL.
  filter(contains(?fURL,"web.archive.org"))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Tagishsimon (talk) 19:01, 15 January 2019 (UTC)
@Tagishsimon: Thanks! They do not "require" it, it is just a way to have them classified.--Micru (talk) 23:05, 15 January 2019 (UTC)
@Micru: Let me put it another way, then. Why do you wish to classify them as discontinued? I'm sure you have sound reasons; in what way are they discontinued? Has archive.org expired? --Tagishsimon (talk) 23:09, 15 January 2019 (UTC)
@Tagishsimon: It is explained on the description of Wikidata property for a discontinued website (Q60457486): "the target website of this property is no longer available. It links now to an archived version. If there is no archived version mark for deletion with "obsolete Wikidata property" (Q18644427)". It makes easier to find which properties have their target discontinued.--Micru (talk) 23:14, 15 January 2019 (UTC)
@Micru: Thank you. It's an unfortunate label, in that it conveys the sense that the wikidata property has been discontinued. --Tagishsimon (talk) 23:38, 15 January 2019 (UTC)
But not anymore [5]. --Tagishsimon (talk) 23:40, 15 January 2019 (UTC)

Usage history for multiple properties in one query[edit]

Usage of Wikidata property P921 (main subject) over time, as of 15 January 2019
Usage history of some key WikiCite properties as of 17 January 2019

On the talk page of each property, there is a "usage history" link that goes to a rather complex query that plots a timeline of the number of items using a given property at a given time. I'd like to combine several properties into one such graph for comparison, e.g. main subject (P921) with author (P50) and author name string (P2093). For performance reasons, I have simplified the query for one property, which now looks as follows:

# Chart of P921 usage
# Note: this chart is based on https://www.wikidata.org/wiki/Template:Property_uses
# which is updated once a day by PLbot

#defaultView:LineChart
SELECT ?day ?count WITH { SELECT (".+\\|921=(\\d+).+" as ?r) ("|921=" as ?p)
  (IF(CONTAINS(?r1, ?p), xsd:integer(REPLACE(?r1, ?r, "$1")), -1) AS ?c1) (xsd:dateTime(?t1) AS ?d1)
  { SERVICE wikibase:mwapi {
      bd:serviceParam wikibase:api "Generator" ; wikibase:endpoint "www.wikidata.org" ; mwapi:generator "allpages" ; 
                      mwapi:gapfrom "Property_uses" ; mwapi:gapto "Property_uses" ; mwapi:gapnamespace "10" ; 
                      mwapi:prop "revisions" ; mwapi:rvprop "content|timestamp" ; mwapi:rvlimit "50" ; mwapi:rvuser "PLbot" .
      ?t1 wikibase:apiOutput "revisions/rev[1]/@timestamp" . ?r1 wikibase:apiOutput "revisions/rev[1]/text()" .
    }
  }
} as %revs {
  {BIND(?c1 AS ?count) BIND(?d1 AS ?day) INCLUDE %revs} 
  FILTER(?count != -1)
}
Try it!

At this point, I am basically stuck, as I don't see a way to turn the 921 into a variable that could cover P50 and P2093 as well, though I suspect including the above query in a higher-order one may help with that. I am also not sure whether all the mwapi calls are actually necessary, or whether they could be optimized somehow for this use case. Pointers would be much appreciated. --Daniel Mietchen (talk) 03:11, 16 January 2019 (UTC)

@Daniel Mietchen:Use VALUES, like this: https://pastebin.com/3DJsnBAg -- Luitzen (talk) 09:53, 16 January 2019 (UTC)
Thanks, Luitzen! I had actually considered VALUES but could not get it to work with CONCAT. Thanks again, --Daniel Mietchen (talk) 10:37, 16 January 2019 (UTC)
The query seems to work fine until about six properties at the moment, and then the time-out kicks in. Adding a pic for some key WikiCite properties. --Daniel Mietchen (talk) 08:15, 17 January 2019 (UTC)

Uruguay-related query[edit]

Dear Wikimedians, I wonder if you could help me construct a query. I wanted to know all the Wikidata items of things Uruguay-related (people born in Urugay, places and things in Uruguay, animals and plants native to Uruguay, etc) that have articles in the English Wikipedia but in in the Spanish Wikipedia. Is this even possible? Thank you!

Very possible, yes. Not quite sure whether you want item has link to EN & ES, or to EN and NOT ES, so here are both:
# has both EN & ES articles
SELECT ?item ?itemLabel (group_concat(?typeLabel; separator=", ") as ?instance_of)
WHERE 
{
  ?item ?property wd:Q77 . # has something to do with Uruguay
  ?item wdt:P31 ?type .    # what sort of thing is it
  ?type rdfs:label ?typeLabel . filter(lang(?typeLabel)="en")
  ?article schema:about ?item ;
          schema:isPartOf <https://en.wikipedia.org/> .
  ?article2 schema:about ?item ;
          schema:isPartOf <https://es.wikipedia.org/> .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,es". }
} group by ?item ?itemLabel order by ?itemLabel
Try it!
# has EN has no ES article
SELECT ?item ?itemLabel (group_concat(?typeLabel; separator=", ") as ?instance_of)
WHERE 
{
  ?item ?property wd:Q77 . # has something to do with Uruguay
  ?item wdt:P31 ?type .    # what sort of thing is it
  ?type rdfs:label ?typeLabel . filter(lang(?typeLabel)="en")
  ?article schema:about ?item ;
          schema:isPartOf <https://en.wikipedia.org/> .
  MINUS {?article2 schema:about ?item ;
          schema:isPartOf <https://es.wikipedia.org/> .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,es". }
} group by ?item ?itemLabel order by ?itemLabel
Try it! --Tagishsimon (talk) 22:49, 16 January 2019 (UTC)

P1342 (members in lower and upper houses)[edit]

Hello I'd like a query returning for the current democracies countries having parliaments, total number of (upper and or lower) houses member (for instance 577 + 348 in France, (435+100 ) in USA and if possible their current population. Thanks! Bouzinac (talk) 22:12, 17 January 2019 (UTC)

@Bouzinac: Here's an initial version...
SELECT ?countryLabel (sum(?seat) as ?seats) ?population
WHERE 
{
  ?item wdt:P1342 ?seat.
  ?item wdt:P1001 ?country.
  ?country wdt:P463 wd:Q1065.
  ?country wdt:P1082 ?population.
  minus {?item wdt:P31/wdt:P279* wd:Q15238777 .} #exclude terms of a legislature
  minus {?item wdt:P31 wd:Q189445 .}             #exclude items for a bicameral legislature
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?countryLabel ?population order by ?countryLabel
Try it!

which is roughly based on this, which lists each item forming the sum in the above. The trick will be to spot rows that should not be here, and concoct a MINUS clause to remove them; and to spot missing legislatures and investigate why they're missing - probably a lack of a number of seats (P1342). I've only done a minimal analysis on the first point, I'm afraid ... getting this exactly right will probably be painstaking.

SELECT ?item ?itemLabel ?appliestoLabel ?seats
WHERE 
{
  ?item wdt:P1342 ?seats.
  ?item wdt:P1001 ?appliesto.
  ?appliesto wdt:P463 wd:Q1065.
  minus {?item wdt:P31/wdt:P279* wd:Q15238777 .}
  minus {?item wdt:P31 wd:Q189445 .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?appliestoLabel
Try it! --Tagishsimon (talk) 00:34, 18 January 2019 (UTC)
Hello, well Wikidata is Wikidata… Primary analysis : should be with MINUS {?item wdt:P31 wd:Q189445 .} MINUS { ?item wdt:P576 [].} # remove items with P576 (dissolved, abolished or demolished) as a main property

Secondly, accordingly to this list [[6]], there would lack :

I'm trying to find why. Bouzinac (talk) 08:01, 18 January 2019 (UTC)

But iirc if you do not use MINUS {?item wdt:P31 wd:Q189445 .} then you get a double-count for Canada. I've also used my go-to 'how to define a country' of ?appliesto wdt:P463 wd:Q1065. - membership of the UN - which is where Palestine drops out. Ditto probably Vatican City. Not sure what Turkey's problem is. So its possible that you'll have to start throwing some UNIONs around to include edge-cases. --Tagishsimon (talk) 08:45, 18 January 2019 (UTC)
For one time, Wikidata was more accurate than the wikipedia-en (austria members 61). There might be other typos… I'm building a short dataviz (work on progress) there https://public.tableau.com/shared/HGCBM3KG4?:display_count=yes CheersBouzinac (talk) 10:41, 18 January 2019 (UTC)
Your URL gave me page-not-found. Publish any revised SPARQL here if you want more input; will be interesting to see how it develops. I look forward to seeing the visualisation. Are you done with airports now? --Tagishsimon (talk) 11:05, 18 January 2019 (UTC)