Wikidata:Request a query/Archive/2020/06

Coordinates by SIMC place ID edit

Hello, I'm from Poland and for my wiki I need a query that gets exact coordinates of (mainly) villages by providing SIMC place IDs and these places' names. I wonder if you could help me, please :) Montanit (talk) 07:12, 1 June 2020 (UTC)[reply]

This query makes list of all items with SIMC place ID with names and coordinates. It can also show the result as a map view.

SELECT ?item ?itemLabel ?SIMC_place_ID ?coord
WHERE
{
  ?item wdt:P4046 ?SIMC_place_ID.
  OPTIONAL { ?item wdt:P625 ?coord. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],pl". }
}

Try it!

--Dipsacus fullonum (talk) 08:01, 1 June 2020 (UTC)[reply]

Thanks a lot! :) Montanit (talk) 08:14, 1 June 2020 (UTC)[reply]

Idref Ids extraction edit

Hello, I'm failing with this extremly simple query aimed to extract the Idref Id of an author from Wikidata. Could you help me?

SELECT ?idref WHERE { ?idref wdt:P269 wd:Q318391. }

Hi, I think you exchanged object and subject. Try

SELECT ?idref WHERE { wd:Q318391 wdt:P269 ?idref. }

Try it!

--Dipsacus fullonum (talk) 11:06, 1 June 2020 (UTC)[reply]

Thanks a lot!

Coordinates by SIMC place ID edit

Hello, I'm from Poland and for my wiki I need a query that gets exact coordinates of (mainly) villages by providing SIMC place IDs and these places' names. I wonder if you could help me, please :) Montanit (talk) 07:12, 1 June 2020 (UTC)[reply]

This query makes list of all items with SIMC place ID with names and coordinates. It can also show the result as a map view.

SELECT ?item ?itemLabel ?SIMC_place_ID ?coord
WHERE
{
  ?item wdt:P4046 ?SIMC_place_ID.
  OPTIONAL { ?item wdt:P625 ?coord. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],pl". }
}

Try it!

--Dipsacus fullonum (talk) 08:01, 1 June 2020 (UTC)[reply]

Thanks a lot! :) Montanit (talk) 08:14, 1 June 2020 (UTC)[reply]

Idref Ids extraction edit

Hello, I'm failing with this extremly simple query aimed to extract the Idref Id of an author from Wikidata. Could you help me?

SELECT ?idref WHERE { ?idref wdt:P269 wd:Q318391. }

Hi, I think you exchanged object and subject. Try

SELECT ?idref WHERE { wd:Q318391 wdt:P269 ?idref. }

Try it!

--Dipsacus fullonum (talk) 11:06, 1 June 2020 (UTC)[reply]

Thanks a lot!

Music band label edit

Hi, I try to query all the music bands publishing their work on the "Sarah records" label (Q3473424). I find bands but my query does not seems to work.

A band which match : https://www.wikidata.org/wiki/Q536403 I try :

SELECT DISTINCT ?band ?bandLabel
WHERE
{
	?band  wdt:P31 wd:Q5741069 .
        ?band rdfs:label ?bandLabel .
	?band wdt:P264 wd:Q3473424 .
}

Try it!

--Le-sens-commun (talk) 16:19, 1 June 2020 (UTC) Finally I got it :[reply]

SELECT DISTINCT ?band ?bandLabel WHERE {
  ?band rdfs:label ?bandLabel;
    wdt:P264 wd:Q3473424.
} LIMIT 206

Try it!

@Le-sens-commun: You may want to have each band only once in the result instead of once for each label. "DISTINCT" doesn't help for this as the labels are treated as being different because they have different language tags. I suggest this:

SELECT ?band ?bandLabel WHERE {
  ?band  wdt:P264 wd:Q3473424.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Dipsacus fullonum (talk) 17:36, 1 June 2020 (UTC)[reply]

With entities selected, how to show all values of a list of properties edit

I am choosing all the films that have an award: SELECT ?film ?filmLabel ?award ?awardLabel WHERE {

 ?film wdt:P31 wd:Q11424.
 ?film wdt:P166 ?award.
 SERVICE wikibase:label {
     bd:serviceParam wikibase:language "en" .
    }

}

But I wish to also see what language(s) the films are in, what language(s) the films support etc.

I have a list of such desirable properties involving languages: http://www.wikidata.org/entity/P407 http://www.wikidata.org/entity/P1412 ... ...

How do I select all of these properties as well as the films?

I tried with the first two properties in the list:

SELECT ?film ?filmLabel ?award ?awardLabel ?lang ?langLabel WHERE {

 ?film wdt:P31 wd:Q11424.
 ?film wdt:P166 ?award.
 ?film wdt:P407 ?lang.
 ?film wdt:P1412 ?lang.
 SERVICE wikibase:label {
     bd:serviceParam wikibase:language "en" .
    }

}

But even with that, I am not returning anything.

Hi. 3 comments of what is wrong:

When you use the same variable ?lang for two different properties, it means that you require the two properties to have the same value which is hardly what you intended. I have changed them to ?lang1 and ?lang2.
When you want to see a value for a property if it present but still have a result for the film if the property isn't used for the film, you have to use the OPTIONAL keyword.
languages spoken, written or signed (P1412) is intended for persons and should not be used for film. Therefore the column for P1412 is empty in the query below.

That gives this rewritten query:

SELECT ?film ?filmLabel ?award ?awardLabel ?lang1 ?lang1Label ?lang2 ?lang2Label WHERE {

 ?film wdt:P31 wd:Q11424.
 ?film wdt:P166 ?award.
 OPTIONAL { ?film wdt:P407 ?lang1. }
 OPTIONAL { ?film wdt:P1412 ?lang2. }
 SERVICE wikibase:label {
     bd:serviceParam wikibase:language "en" .
    }
}

Try it!

--Dipsacus fullonum (talk) 08:54, 2 June 2020 (UTC)[reply]

Wikipedia:WikiProject_Women_in_Red/Missing_articles_by_occupation/Cartoonists edit

is a long-standing query that several editors worked together to create in 2018. It has worked in the past but it currently times out regardless of how I tweak it. Basically it needs to capture the following people who don't have an en.wp article:

All women who have the occupation Q1114448 or Q715301 and all subclasses like Q191633.
All women who have an entry in the Lambiek Comiclopedia (Property:P5035) but might not have one of the above occupations on their items.

Thank you. Gamaliel (talk) 16:45, 2 June 2020 (UTC)[reply]

@Gamaliel: It seems that when either part of the UNION in that query is excluded, the query runs. So I split both criteria into separate subqueries (with some other modifications) and it seems to work:

SELECT DISTINCT ?item ?linkcount ?lambiekurl
with {
  select ?occ WHERE {
    ?list wdt:P31 wd:Q65181907 .                     # find the WD item for the list
    ?article schema:name ?sitelink_list ; schema:about ?list ; schema:isPartOf <https://en.wikipedia.org/> .
    filter(str(?sitelink_list)="Wikipedia:WikiProject Women in Red/Missing articles by occupation/Cartoonists")
    ?list p:P360 [ pq:P106 ?occ1 ].                    # get its occupations
    ?occ wdt:P279* ?occ1 .
  }
} as %i
with { SELECT ?item ?linkcount { 
  include %i.
    {?item wdt:P106 ?occ .}                            # item has this occupation
  MINUS { ?item wdt:P5035 ?lambiek } # those in the Comiclopedia omitted here, since it will be returned in the other subquery
  MINUS {?wen schema:about ?item ; schema:isPartOf <https://en.wikipedia.org/> . } # has no en.wikipedia sitelink
  ?item wdt:P21 wd:Q6581072 .                      # gender is female
  ?item wdt:P31 wd:Q5 .                            # instance is human
  ?item wikibase:sitelinks ?linkcount .            # number of sitelinks for the item
  }
} as %a
with { SELECT ?item ?linkcount ?lambiekurl { 
  include %i.
  ?item wdt:P5035 ?lambiek.  BIND ((CONCAT ("[https://www.lambiek.net/artists/", ?lambiek, ".htm entry]")) as ?lambiekurl) . # is in the Lambiek Comiclopedia
  MINUS {?wen schema:about ?item ; schema:isPartOf <https://en.wikipedia.org/> . } # has no en.wikipedia sitelink
  ?item wdt:P21 wd:Q6581072 .                      # gender is female
  ?item wdt:P31 wd:Q5 .                            # instance is human
  ?item wikibase:sitelinks ?linkcount .            # number of sitelinks for the item
  }
} as %b
where
{
  {include %a} UNION {include %b}
}

Try it!

Mahir256 (talk) 17:21, 2 June 2020 (UTC)[reply]

@Mahir256: The query works, but it only returns 797 items, while the last time Listeria successfully ran the query, it returned 3331 items. Gamaliel (talk) 17:28, 2 June 2020 (UTC)[reply]

@Gamaliel: It seems, then, that some occupations previously returned in the "%i" subquery got lost (that part I did not change, and all that subquery returns are cartoonist (Q1114448), editorial cartoonist (Q3024627), and art director (Q24204895)). Would you happen to know how such de-linkages might have occurred? Mahir256 (talk) 17:34, 2 June 2020 (UTC)[reply]

I don't know unfortunately. Maybe Tagishsimon might have some insight. Gamaliel (talk) 17:36, 2 June 2020 (UTC)[reply]

@Mahir256: It looks like the list is pulling the occupation from Wikipedia:WikiProject Women in Red/Missing articles by occupation/Cartoonists (Q65183699). I added comics artist (Q715301) to that item but it didn't change the results. Is there a way to get the query to pull multiple occupations from that qualifier? Gamaliel (talk) 17:44, 2 June 2020 (UTC)[reply]

@Gamaliel: It is easy to forget that query service lag exists. Mahir256 (talk) 17:51, 2 June 2020 (UTC)[reply]

SELECT DISTINCT ?item ?linkcount ?lambiekurl
with {
  select ?occ WHERE { VALUES ?occ {
    wd:Q715301  # comics artists
    wd:Q1114448 # cartoonists 
  }
  }
} as %i
with { SELECT ?item ?linkcount { 
  include %i.
    {?item wdt:P106/wdt:P279* ?occ .}                            # item has this occupation
  MINUS { ?item wdt:P5035 ?lambiek } # those in the Comiclopedia omitted here, since it will be returned in the other subquery
  MINUS {?wen schema:about ?item ; schema:isPartOf <https://en.wikipedia.org/> . } # has no en.wikipedia sitelink
  ?item wdt:P21 wd:Q6581072 .                      # gender is female
  ?item wdt:P31 wd:Q5 .                            # instance is human
  ?item wikibase:sitelinks ?linkcount .            # number of sitelinks for the item
  }
} as %a
with { SELECT ?item ?linkcount ?lambiekurl { 
  include %i.
  ?item wdt:P5035 ?lambiek.  BIND ((CONCAT ("[https://www.lambiek.net/artists/", ?lambiek, ".htm entry]")) as ?lambiekurl) . # is in the Lambiek Comiclopedia
  MINUS {?wen schema:about ?item ; schema:isPartOf <https://en.wikipedia.org/> . } # has no en.wikipedia sitelink
  ?item wdt:P21 wd:Q6581072 .                      # gender is female
  ?item wdt:P31 wd:Q5 .                            # instance is human
  ?item wikibase:sitelinks ?linkcount .            # number of sitelinks for the item
  }
} as %b
where
{
  {include %a} UNION {include %b}
}

Try it!

@Mahir256: Since the problem seemed to be with Wikipedia:WikiProject Women in Red/Missing articles by occupation/Cartoonists (Q65183699), I removed that entirely and put the occupations directly in the query you constructed. Listeria has returned 3496 items. Thank you for your help! Gamaliel (talk) 18:16, 2 June 2020 (UTC)[reply]

@Gamaliel: (The comment about query lag was merely intended to suggest that if you just waited until all the query servers caught up, the occupations that were subclasses of "comics artist" would get returned, since "comics artist" was now a qualifier of the is a list of (P360) statement. Indeed, if you try my original query again, those 3496 (3497?) items would get returned.) Mahir256 (talk) 18:20, 2 June 2020 (UTC)[reply]

List of article sizes edit

Hi! I did not find anything helpful in the archives. My request: I need a list of the sizes of all articles sitelinked with Nibal Thawabteh (Q18207978). The item has (with commons) 11 sitelinks. And I need a list of all the 11 sitelinks. A feature but not necessary were the result in kByte instead of Byte. Thank you a lot, Doc Taxon (talk) 20:29, 2 June 2020 (UTC)[reply]

@Doc Taxon: I added some extra variables to select to help me debug. Feel free to remove what you don't need.

SELECT ?sitelink ?site ?endpoint ?title ?size ?size_kbyte
WHERE
{
  ?sitelink schema:about wd:Q18207978.
  ?sitelink schema:isPartOf ?site.
  ?sitelink schema:name ?title.
  BIND (STRBEFORE(STRAFTER(STR(?site), "https://"), "/") AS ?endpoint)
  BIND (STR(?title) AS ?title_nolang)
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint ?endpoint.
    bd:serviceParam wikibase:api "Generator".
    bd:serviceParam mwapi:generator "revisions".
    bd:serviceParam mwapi:titles ?title_nolang.
    bd:serviceParam mwapi:prop "revisions".
    bd:serviceParam mwapi:rvprop "size".
    bd:serviceParam mwapi:grvdir "older".
    bd:serviceParam mwapi:grvlimit "max".
    ?size wikibase:apiOutput "revisions/rev[last()]/@size".
  }
  BIND (ROUND(xsd:integer(?size)/1024) AS ?size_kbyte)
}

Try it!

--Dipsacus fullonum (talk) 22:18, 2 June 2020 (UTC)[reply]

Wow, cool! And is it possible to order it size descending? Doc Taxon (talk) 04:11, 3 June 2020 (UTC)[reply]

@Doc Taxon: Yes, add ORDER BY DESC(?size) as the last line after the closing } of the WHERE clasue. --Dipsacus fullonum (talk) 06:12, 3 June 2020 (UTC)[reply]

PS: You may get wrong or truncated results or maybe timeouts if you try this query with items that have sitelinks with many revisions. The reason for this is discussed at Wikidata:Contact the development team/Query Service and search#Wish for MW API access from WDQS: bd:serviceParam wikibase:limit value to not use continuations but still make more API calls. --Dipsacus fullonum (talk) 07:50, 3 June 2020 (UTC)[reply]

Cities in germany that are at least 40,000 and at most 60,000 in population and those in at most 10 km to them edit

I am looking for

Cities
in germany
that are at least 40,000 and at most 60,000 in population and
those cities and villages that are at most 10 km away from them
shown as map and list of city/village names with distance to the city that is at least 40,000 and at most 60,000 in population

Which Query does that?

I found 261 German cities with 40,000-60,000 inhabitants, and 175,990 items located at most 10 km from these cities. Most of the nearby items are not other cities but other things like streets and houses. However I cannot make a query to find which of the 175,990 nearby items are cities that can run in less 60 seconds so it doesn't timeout. There are simply too many to handle. This query list the 175,990 items:

SELECT ?city ?citizens ?city_coord ?place ?place_coord ?distance_from_city 
WHERE
{
  ?city wdt:P31 / wdt:P279 * wd:Q42744322 .
  ?city wdt:P1082 ?citizens .
  FILTER (?citizens >= 40000 && ?citizens <= 60000)
  ?city wdt:P625 ?city_coord .
  SERVICE wikibase:around
  { 
    ?place wdt:P625 ?place_coord . 
    bd:serviceParam wikibase:center ?city_coord . 
    bd:serviceParam wikibase:radius "10" . 
    bd:serviceParam wikibase:distance ?distance_from_city.
  }
}

Try it!

Your wanted query would probably be possible if you reduced the area, for instance to only one Bundesland per query. --Dipsacus fullonum (talk) 14:39, 3 June 2020 (UTC)[reply]

Nice. I tried with municipalities and got some 1000. Obviously, it times out even more quickly. --- Jura 14:45, 3 June 2020 (UTC)[reply]

Extract all the products with labels from Wikidata edit

Hello, I tried to extract all the products embedded in Wikidata, but I faced an exception that is TimeOut Exception. So, as I understand from this exception, I think that I have to use some strategies of SPARQL Optimization in order to successfully execute this query. My query is just a simple triple pattern but as I said it needs applying some Optimisation services: SELECT ?Products where {?Products wdt:P279* wd:Q2424752.}

The optimization is this case is a GAS program (GAS stands for Gather-Apply-Scatter, documentation is at https://github.com/blazegraph/database/wiki/RDF_GAS_API). It finds 972,898 products with a breath-first search with the last found product at search depth 19. I could not get it to always run. Try lower values for gas:maxIterations if it times out. There is no hope at all to find labels for nearly a million items in a query.

SELECT ?product ?depth
WHERE
{
  SERVICE gas:service
  {
    gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.BFS" .
    gas:program gas:linkType wdt:P279 .
    gas:program gas:traversalDirection "Reverse" .
    gas:program gas:in wd:Q2424752 .
    gas:program gas:out ?product .
    gas:program gas:out1 ?depth .
    gas:program gas:maxIterations 20 .
  }
}
ORDER BY ASC(?depth)

Try it!

--Dipsacus fullonum (talk) 16:27, 3 June 2020 (UTC)[reply]

}} --Thanks a lot Dipsacus fullonum

Things that are situated in problematic countries edit

Hello, I'd like a query list for the current things (things that aren't dissolved/destroyed) being said in country X (country (P17) with only one country X and not matching that country X from located in the administrative territorial entity (P131) such as this problem Bački Breg (Q779764). 13:45, 3 June 2020 (UTC)Bouzinac (talk)

The query will timeout if I try to search for all countries at once. This query will find items located in Serbia (Q403) but also located in an administrative entity which aren't in the country.

SELECT ?item ?itemLabel ?administrative_entity ?administrative_entityLabel ?administrative_entity_country ?administrative_entity_countryLabel
WHERE
{
  VALUES ?country { wd:Q403 }
  ?item wdt:P17 ?country .
  OPTIONAL { ?item wdt:P576 ?dissolved . }
  FILTER (! BOUND(?dissolved))
  ?item wdt:P131 ?administrative_entity .
  MINUS
  {
    ?administrative_entity wdt:P17 ?country .
  }
  OPTIONAL { ?administrative_entity wdt:P17 ?administrative_entity_country . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Dipsacus fullonum (talk) 17:21, 3 June 2020 (UTC)[reply]

Nice try Dipsacus fullonum (talk • contribs • logs), I think it should be filtered out from things that a related to any border (which can be multiple countries). It should also filter out things that are abstracts/studies/etc and not concrete as buildings, churches, cities, etc (like this one Filgotinib Alone and in Combination With Methotrexate (MTX) in Adults With Moderately to Severely Active Rheumatoid Arthritis Who Are Naive to MTX Therapy (Q63813246)) Bouzinac (talk) 19:26, 3 June 2020 (UTC)[reply]

Combining multiple "any subclass of" "instance of"s into a single search? edit

Hi folks!

In the search below I'm looking for anything that is a subclass of cultural institution (Q5193377) on the line: (?library (wdt:P31/(wdt:P279*)) wd:Q5193377.)

Unfortunately, cultural institution (Q5193377) by itself doesn't return all the items I'm looking for, so I need to also look for any subclass of cultural heritage (Q210272), tourist attraction (Q570116), and performing arts building (Q57660343). Is there any way to combine these in one search so that I'm searching for any subclass of these four items?

SELECT DISTINCT ?library ?libraryLabel ?located_at_street_address ?coordinate_location ?phone_number ?e_mail_address ?countyLabel
WHERE {
  VALUES (?located_in_the_administrative_territorial_entity ?countyLabel)
  {
    (wd:Q491547 "Union County") (wd:Q503538 "Towns County") (wd:Q503546 "Rabun County")
    (wd:Q492040 "Lumpkin County") (wd:Q389365 "White County") (wd:Q501096 "Habersham County")
    (wd:Q498362 "Stephens County") (wd:Q492012 "Hall County") (wd:Q488201 "Banks County")
    (wd:Q385931 "Franklin County") (wd:Q491301 "Hart County") (wd:Q486838 "Barrow County")
    (wd:Q486137 "Jackson County") (wd:Q156387 "Madison County") (wd:Q492016 "Elbert County")
    (wd:Q498312 "Walton County") (wd:Q492026 "Oconee County") (wd:Q112061 "Clarke County")
    (wd:Q491525 "Oglethorpe County") (wd:Q491759 "Wilkes County") (wd:Q491519 "Lincoln County")
    (wd:Q501101 "Newton County") (wd:Q493083 "Morgan County") (wd:Q486765 "Greene County")
  }
  FILTER NOT EXISTS { ?library wdt:P576 [] }
  ?library (wdt:P31/(wdt:P279*)) wd:Q5193377.
  ?library wdt:P131 ?located_in_the_administrative_territorial_entity. hint:Prior hint:runFirst true.
  OPTIONAL { ?library wdt:P6375 ?located_at_street_address. }
  OPTIONAL { ?library wdt:P625 ?coordinate_location. }
  OPTIONAL { ?library wdt:P1329 ?phone_number. }
  OPTIONAL { ?library wdt:P968 ?e_mail_address. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY ?library
LIMIT 10000

Try it!

Thanks in advance! Clifflandis (talk) 19:53, 3 June 2020 (UTC)[reply]

@Clifflandis: It seems that adding cultural heritage (Q210272) gives 8 more results to your query, but neither adding tourist attraction (Q570116) or performing arts building (Q57660343) gives any more results. You can add them using VALUES. Instead of

  ?library (wdt:P31/(wdt:P279*)) wd:Q5193377.

use

  VALUES ?class { wd:Q5193377 wd:Q210272 wd:Q570116 wd:Q57660343 }
  ?library (wdt:P31/(wdt:P279*)) ?class.

--Dipsacus fullonum (talk) 06:38, 4 June 2020 (UTC)[reply]

Different colors on map edit

Hi all! Starting from the following query

#Biblioteche, musei e chiese a Roma
#defaultView:Map
SELECT DISTINCT ?luogo ?luogoLabel ?coordinate ?layer
WHERE {
   BIND(wd:Q7075 AS ?biblioteca).
   BIND(wd:Q33506 AS ?museo).
   BIND(wd:Q16970 AS ?chiesa).
  ?luogo wdt:P131 wd:Q220 .
  OPTIONAL { ?luogo wdt:P625 ?coordinate . }
  { ?luogo wdt:P31/wdt:P279* ?biblioteca . }
  UNION
  { ?luogo wdt:P31/wdt:P279* ?museo . }
  UNION
  { ?luogo wdt:P31/wdt:P279* ?chiesa . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en". }
}

Try it!

would it be possible to show in three different colors "biblioteca", "museo" and "chiesa" in the map? Thank you very much, --Epìdosis 10:04, 4 June 2020 (UTC)[reply]

@Epìdosis: Sure. You already selected the variable ?layer, so I just added 3 different bindings for it:

#Biblioteche, musei e chiese a Roma
#defaultView:Map
SELECT DISTINCT ?luogo ?luogoLabel ?coordinate ?layer
WHERE {
   BIND(wd:Q7075 AS ?biblioteca).
   BIND(wd:Q33506 AS ?museo).
   BIND(wd:Q16970 AS ?chiesa).
  ?luogo wdt:P131 wd:Q220 .
  OPTIONAL { ?luogo wdt:P625 ?coordinate . }
  { ?luogo wdt:P31/wdt:P279* ?biblioteca . BIND(1 AS ?layer) }
  UNION
  { ?luogo wdt:P31/wdt:P279* ?museo . BIND(2 AS ?layer) }
  UNION
  { ?luogo wdt:P31/wdt:P279* ?chiesa . BIND(3 AS ?layer) }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en". }
}

Try it!

--Dipsacus fullonum (talk) 10:10, 4 June 2020 (UTC)[reply]

@Epìdosis: Here is a simplified version where one VALUES definition replaces the UNIONs:

#Biblioteche, musei e chiese a Roma
#defaultView:Map
SELECT DISTINCT ?luogo ?luogoLabel ?coordinate ?layer
WHERE {
  VALUES ?layer { wd:Q7075 wd:Q33506 wd:Q16970 } # biblioteca, museo e chiesa
  ?luogo wdt:P131 wd:Q220 .
  OPTIONAL { ?luogo wdt:P625 ?coordinate . }
  ?luogo wdt:P31/wdt:P279* ?layer.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en". }
}

Try it!

--Dipsacus fullonum (talk) 10:22, 4 June 2020 (UTC)[reply]

You can use ?layerLabel in SELECT, instead of ?layer to get a more readable one. --- Jura 12:39, 4 June 2020 (UTC)[reply]

Correct and a good idea! --Dipsacus fullonum (talk) 12:48, 4 June 2020 (UTC)[reply]

#Biblioteche, musei e chiese a Roma
#defaultView:Map{"hide":["?coordinate"], "layer": "?typeLabel"}
SELECT DISTINCT 
  ?luogo ?luogoLabel ?luogoDescription 
  ?coordinate ?typeLabel ?img
WHERE 
{
  VALUES ?type { wd:Q7075 wd:Q33506 wd:Q16970 } # biblioteca, museo e chiesa
  ?luogo wdt:P131/wdt:P131* wd:Q220 .
  ?luogo wdt:P31/wdt:P279* ?type.
  OPTIONAL { ?luogo wdt:P625 ?coordinate . }
  OPTIONAL { ?luogo wdt:P18 ?img }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en". }
}

Try it!

Above with a few other tweaks. --- Jura 12:43, 4 June 2020 (UTC)[reply]

Currently active rock metal bands and their hometowns edit

Hello everyone I am trying to get a list of currently active bands in the genres stoner rock/metal(Q617240), sludge(Q720959), doom(Q186170), alternative rock(Q11366) and progressive rock(Q49451), along with their location of formation/origin(P740) and accompanying coordinates. Thanks a lot!

Here is a query. The results can both be shown as a table and on a map.

SELECT ?band ?bandLabel ?genre ?genreLabel ?location_of_formation ?location_of_formationLabel ?coordinates
{
  ?band wdt:P31 wd:Q215380. # ?band is a band
  VALUES ?genre { wd:Q617240 wd:Q720959 wd:Q186170 wd:Q11366 wd:Q49451 }
  ?band wdt:P136 ?genre. # ?band has one of genres in ?genre
  OPTIONAL { ?band wdt:P576 ?dissolved }
  FILTER (! BOUND(?dissolved)) # Band has no dissolve date
  OPTIONAL {
    ?band wdt:P740 ?location_of_formation.
    OPTIONAL { ?location_of_formation wdt:P625 ?coordinates. }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Dipsacus fullonum (talk) 09:07, 4 June 2020 (UTC)[reply]

Mange tak Dipsacus fullonum! How would you go about also selecting the year of latest release?

@Andrekotze69:

SELECT ?band ?bandLabel ?genre ?genreLabel ?location_of_formation ?location_of_formationLabel ?coordinates
  (MAX(?release_date) AS ?latest_release)
{
  ?band wdt:P31 wd:Q215380. # ?band is a band
  VALUES ?genre { wd:Q617240 wd:Q720959 wd:Q186170 wd:Q11366 wd:Q49451 }
  ?band wdt:P136 ?genre. # ?band has one of genres in ?genre
  OPTIONAL { ?band wdt:P576 ?dissolved }
  FILTER (! BOUND(?dissolved)) # Band has no dissolve date
  OPTIONAL {
    ?band wdt:P740 ?location_of_formation.
    OPTIONAL { ?location_of_formation wdt:P625 ?coordinates. }
  }
  OPTIONAL {
    ?album wdt:P175 ?band.
    ?album wdt:P577 ?release_date.
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?band ?bandLabel ?genre ?genreLabel ?location_of_formation ?location_of_formationLabel ?coordinates

Try it!

--Dipsacus fullonum (talk) 15:55, 4 June 2020 (UTC)[reply]

Many thanks Dipsacus fullonum! I have tried it with genre "heavy metal" (Q38848) and am wondering why it doesn't return the bands Danzig, AC/DC or Ghost? Any help is much appreciated!

I figured it out, it was because some artists are "rock bands" (Q5741069) and not "musical groups". Also some e.g. 1000mods don't have P740 "location of formation" but rather P131 "located in the administrative territorial entity" or P495 "country of origin"

@Andrekotze69: You can change ?band wdt:P31 wd:Q215380. to ?band wdt:P31 / wdt:P279* wd:Q215380. to include all subclasses of bands. That will get more bands, but not AC/DC (Q27593) or Danzig (Q649175) because their use of genre (P136) don't include any of the genres in your list. You could get located in the administrative territorial entity (P131) but that will show where they are located now instead of their origin. I would want that in another column as they are different things. I think use of country of origin (P495) for a band is plainly wrong. The property is intended for creative works like films and television series. --Dipsacus fullonum (talk) 19:01, 4 June 2020 (UTC)[reply]

Getting number of cases/deaths/recoveries to mark most recent value as preferred edit

Following up from here, could number of cases (P1603), number of deaths (P1120), and number of recoveries (P8010) be made so that they automatically mark the most recent value as preferred? Sdkb (talk) 09:55, 4 June 2020 (UTC)[reply]

@Sdkb: You cannot make a query that will edit Wikidata. You can however use the results from a query to find out what you want to change by using other tools. --Dipsacus fullonum (talk) 16:02, 4 June 2020 (UTC)[reply]

@Dipsacus fullonum: Thanks for the reply. I'm new to Wikidata, so I'm just posting here since that's where I was directed. If this isn't the spot to achieve what I'm trying to do, it'd be helpful to have guidance on where to go/what to do. My goal here is to get this page updating with Wikidata data (initial efforts here). Sdkb (talk) 17:24, 4 June 2020 (UTC)[reply]

Query Optimization for bypassing the TimeOut Exception edit

Hello, I wrote the following Query that consists of extracting the products and their brands, but the result that I received is not efficient and generates the TimeOut Exception. Therefore, I need to optimize it and please provide me some relevant references for SPARQL Query Optimization.

select DISTINCT ?brand ?brandLabel (GROUP_CONCAT(?itemName; SEPARATOR=", ") AS ?ProductsItemName) 
where {
    ?CompanyID wdt:P31 wd:Q4830453.
    OPTIONAL{?CompanyID wdt:P1056 ?MaterialProduced.}
    OPTIONAL {?item wdt:P279* ?MaterialProduced.
             ?item wdt:P1716 ?brand.}
  SERVICE wikibase:label{
            bd:serviceParam wikibase:language "en".
            ?item rdfs:label ?itemName.
           ?brand rdfs:label ?brandLabel.
        }
}
GROUP BY ?brand ?brandLabel

Try it!

An obvious optimization is to remove the OPTIONAL keywords. You need results from the OPTIONAL clauses to get values for ?brand and ?item, so the aren't really optional. That being said I don't think it will be possible to avoid timeout because there are far too many results. My counting gave 171,070 companies, 11,760 products and over a million items. You will need to limit the query to get a smaller result by adding more restrictive criteria. An general search for all types of products at once is impossible in 60 seconds. --Dipsacus fullonum (talk) 09:34, 6 June 2020 (UTC)[reply]

@Dipsacus fullonum, Dipsacus fullonum:: Thank you very much. In fact, I removed the OPTIONAL KeyWord. But I still faced a problem of redundancy. In the following Query, the field ?ProductsItemName contains redundant data and when i used Distinct KeyWord, the query generates an Exception. Help me in eliminating the redundancy.

select  ?brand ?brandLabel (GROUP_CONCAT(?itemName; SEPARATOR=", ") AS ?ProductsItemName) 
where {
  
    ?CompanyID wdt:P31   wd:Q4830453.
    ?CompanyID wdt:P1056 ?MaterialProduced.
     ?item      wdt:P279*  ?MaterialProduced.
    ?item      wdt:P1716 ?brand.
  
  SERVICE wikibase:label{
            bd:serviceParam wikibase:language "en".
            ?item rdfs:label ?itemName.
           ?brand rdfs:label ?brandLabel.
        }
}
GROUP BY ?brand ?brandLabel

Try it!

--Bilalox01 (talk) 07:14, 7 June 2020 (UTC)[reply]

@Dipsacus fullonum: I found the solution, and I want to share it. It is simple, Just we have to add Distinct KeyWord inside Template:(GROUP CONCAT(?itemName; SEPARATOR=", ") AS ?ProductsItemName)

select  ?brand ?brandLabel (GROUP_CONCAT( Distinct ?itemName; SEPARATOR=", ") AS ?ProductsItemName) 
where {
  
    ?CompanyID wdt:P31   wd:Q4830453.
    ?CompanyID wdt:P1056 ?MaterialProduced.
     ?item      wdt:P279*  ?MaterialProduced.
    ?item      wdt:P1716 ?brand.
  
  SERVICE wikibase:label{
            bd:serviceParam wikibase:language "en".
            ?item rdfs:label ?itemName.
           ?brand rdfs:label ?brandLabel.
        }
}
GROUP BY ?brand ?brandLabel

Try it!

--Bilalox01 (talk) 07:36, 7 June 2020 (UTC) Bilalox01 (talk) 07:36, 7 June 2020 (UTC)Bilalox01[reply]

Interlaced queries, e.g. works of a certain author; editions of those works; the publisher of those editions etc. edit

1. I created this table. There are listed of 8 volumes of a dictionary.

Each of the volume (Q1238720) 2 to 8 has a (digital) edition (Property:P747). All these version, edition or translation (Q3331189) have a value for full work available at URL (P953).

volume (Q1238720) 1 has a second printed edition and this second edition (in turn) has a digital edition (as volumen 2 to 7).

How can I get the values for full work available at URL (P953) into last column of the table (of course, by other means than copy & paste)?

2. Similar problem:

I created this two tables:

G. Sprigath als Herausgeberin

and

G. Sprigath als Buch-Autorin

This two tables are (more or less) okay for me. I created also a further table - this one:

G. Sprigath als Übersetzerin.

This is a list of essays, which where translated by Gabriele Sprigath. This essays were published within two editions of a certain book. Among other information I would like to mention the publisher (P123) and the place of publication (P291) of this editions within the table (column 6 and 7) as well. What would I have to add to the code of the SPARQL-query?

3. Similar queries would be:

car manufactures in a certain town / quantity of workers of each of the manufactures (within that town).

political parties within a certains country / the chairpersons of each of this parties / the start time (P580), when this chairpersons came into office.

etc.

--Nstrc (talk) 10:58, 6 June 2020 (UTC)[reply]

Online edition / series of creative works edit

1. It could be something likke this:

SELECT ?volume ?edition ?available_at ?derivative_work ?derivative_work_edition ?derivative_work_available_at
WHERE
{
  ?volume wdt:P179 wd:Q96024609.
  OPTIONAL
  {
    ?volume wdt:P747 ?edition.
    ?edition wdt:P953 ?available_at.
  }
  OPTIONAL
  {
    ?volume wdt:P4969 ?derivative_work.
    ?derivative_work wdt:P747 ?derivative_work_edition.
    ?derivative_work_edition wdt:P953 ?derivative_work_available_at.
  }
}

Try it!

It would also be possible to reuse the columns for the two cases, but then you wouldn't know when the edition is a derivative_work.

--Dipsacus fullonum (talk) 13:45, 6 June 2020 (UTC)[reply]

I reworked it in this way.

SELECT ?Titel (year(?Erscheinungsdatum) as ?Erscheinungsjahr) ?ISBN ?Umfang ?DigitalisatURL ?Datensatz WHERE {
  { ?Datensatz wdt:P179 wd:Q96024609. }
  OPTIONAL {
    ?Datensatz wdt:P1476 ?Titel.
  }
  OPTIONAL {
    ?Datensatz wdt:P577 ?Erscheinungsdatum.
  }
  OPTIONAL {
    ?Datensatz wdt:P957 ?ISBN. 
  }
    OPTIONAL {
    ?Datensatz wdt:P1104 ?Umfang. 
  }
        OPTIONAL {
    ?Datensatz wdt:P1292 ?DNB. 
  }
        OPTIONAL {
    ?Datensatz wdt:P6721 ?K10plus. 
  }  
  OPTIONAL
  {
    ?Datensatz wdt:P747 ?Ausgabe.
    ?Ausgabe wdt:P953 ?DigitalisatURL.
  }
  OPTIONAL
  {
    ?Datensatz wdt:P4969 ?Bearbeitung.
    ?Bearbeitung wdt:P747 ?derivative_work_edition.
    ?derivative_work_edition wdt:P953 ?DigitalisatURL.
  }
}

Try it!

Volume 1 is displayed only in one line; volume 2 to 8 are displayed each in two lines. Would be it possible, that also volume 2 to 8 would need only one line as well?

--Nstrc (talk) 15:53, 6 June 2020 (UTC)[reply]

@Nstrc: Volume 2 to 8 are displayed in two lines because they each have two ISBN numbers. That can be fixed grouping the results by everything else than ISBN:

SELECT ?Titel (year(?Erscheinungsdatum) as ?Erscheinungsjahr) (GROUP_CONCAT(?ISBN_) AS ?ISBN) ?Umfang ?DigitalisatURL ?Datensatz WHERE {
  ?Datensatz wdt:P179 wd:Q96024609.
  OPTIONAL {
    ?Datensatz wdt:P1476 ?Titel.
  }
  OPTIONAL {
    ?Datensatz wdt:P577 ?Erscheinungsdatum.
  }
  OPTIONAL {
    ?Datensatz wdt:P957 ?ISBN_.
  }
  OPTIONAL {
    ?Datensatz wdt:P1104 ?Umfang. 
  }
  OPTIONAL {
    ?Datensatz wdt:P1292 ?DNB. 
  }
  OPTIONAL {
    ?Datensatz wdt:P6721 ?K10plus. 
  }  
  OPTIONAL
  {
    ?Datensatz wdt:P747 ?Ausgabe.
    ?Ausgabe wdt:P953 ?DigitalisatURL.
  }
  OPTIONAL
  {
    ?Datensatz wdt:P4969 ?Bearbeitung.
    ?Bearbeitung wdt:P747 ?derivative_work_edition.
    ?derivative_work_edition wdt:P953 ?DigitalisatURL.
  }
}
GROUP BY ?Titel ?Erscheinungsdatum ?Umfang ?DigitalisatURL ?Datensatz

Try it!

--Dipsacus fullonum (talk) 16:19, 6 June 2020 (UTC)[reply]

Thank your very much.

--Nstrc (talk) 16:25, 6 June 2020 (UTC)[reply]

Hyperlinked output (ISBN, OPAC) edit

The output there: https://w.wiki/TH3 is as follows:

Titel	Erscheinungsjahr	Umfang	ISBN	DNB	K10plus	DigitalisatURL	Datensatz
Band 1 Abhängigkeit bis Bund	1983	205	3-88619-033-1	831087498	125332025	<http://www.inkrit.de/neuinkrit/mediadaten/archivkwm/KWM01.pdf>	wd:Q96026743
Band 2 Cäsarismus bis Funktionär	1984	197	3-88619-052-8 3-88619-062-5	840398360	125332106	http://www.inkrit.de/neuinkrit/mediadaten/archivkwm/KWM02.pdf>	wd:Q96031980
Band 3 Gattung bis Judenfrage	1985	184	3-88619-053-6 3-88619-063-3	850368103	125332157	<http://www.inkrit.de/neuinkrit/mediadaten/archivkwm/KWM03.pdf>	wd:Q96032581
Band 4 Kadetten bis Lyssenkismus	1986	220	3-88619-054-4 3-88619-064-1	860515966	125332211	<http://www.inkrit.de/neuinkrit/mediadaten/archivkwm/KWM04.pdf>	wd:Q96033164
Band 5 Machismus bis Owenismus	1986	174	3-88619-055-2 3-88619-065-X	861000641	125332254	<http://www.inkrit.de/neuinkrit/mediadaten/archivkwm/KWM05.pdf>	wd:Q96033337
Band 6 Pariser Kommune bis Romantik	1987	174	3-88619-056-0 3-88619-066-8	870865528	125332327	<http://www.inkrit.de/neuinkrit/mediadaten/archivkwm/KWM06.pdf>	wd:Q96034314
Band 7 Säuberung bis Trotzkismus	1988	168	3-88619-057-9 3-88619-067-6	880402016	12533236X	<http://www.inkrit.de/neuinkrit/mediadaten/archivkwm/KWM07.pdf>	wd:Q96035295
Band 8 Überbau bis Zusammenbruchstheorie	1989	214	3-88619-058-7 3-88619-068-4	881428779	125332416	<http://www.inkrit.de/neuinkrit/mediadaten/archivkwm/KWM08.pdf>	wd:Q96035325

Would it be possible to get the output as well in this way:

Titel	Erscheinungsjahr	Umfang (Seiten)	ISBN-10	DNB	K10plus	Digitalisat-URL	Datensatz
Band 1 Abhängigkeit bis Bund	1983	205	ISBN 3-88619-033-1	831087498	125332025	KWM01.pdf	Q96026743
Band 2 Cäsarismus bis Funktionär	1984	197	ISBN 3-88619-052-8 ISBN 3-88619-062-5	840398360	125332106	KWM02.pdf	Q96031980
Band 3 Gattung bis Judenfrage	1985	184	ISBN 3-88619-053-6 ISBN 3-88619-063-3	850368103	125332157	KWM03.pdf	Q96032581
Band 4 Kadetten bis Lyssenkismus	1986	220	ISBN 3-88619-054-4 ISBN 3-88619-064-1	860515966	125332211	KWM04.pdf	Q96033164
Band 5 Machismus bis Owenismus	1986	174	ISBN 3-88619-055-2 ISBN 3-88619-065-X	861000641	125332254	KWM05.pdf	Q96033337
Band 6 Pariser Kommune bis Romantik	1987	174	ISBN 3-88619-056-0 ISBN 3-88619-066-8	870865528	125332327	KWM06.pdf	Q96034314
Band 7 Säuberung bis Trotzkismus	1988	168	ISBN 3-88619-057-9 ISBN 3-88619-067-6	880402016	12533236X	KWM07.pdf	Q96035295
Band 8 Überbau bis Zusammenbruchstheorie	1989	214	ISBN 3-88619-058-7 ISBN 3-88619-068-4	880402016	125332416	KWM08.pdf	Q96035325

En passant: Abhängigkeit, Bund, Cäsarismus, Funktionär, Gattung, Judenfrage, Kadetten, Lyssenkismus, Machismus, Owenismus, Pariser Kommune, Romantik, Säuberung, Trotzkismus, Überbau, Zusammenbruchstheorie.

DNB = https://d-nb.info/...
K10plus = https://kxp.k10plus.de/DB=2.1/PPNSET?PPN...
(Digitalisat-URL = http://www.inkrit.de/neuinkrit/mediadaten/archivkwm/...)
[[File:DNB.svg|35px]]
[[File:Wikidata-logo-en.svg|30px]]

--Nstrc (talk) 08:36, 7 June 2020 (UTC)[reply]

published in (P1433) edit

2.

SELECT ?item ?published_in ?publisher ?place_of_publication
WHERE
{
  ?item wdt:P655 wd:Q92759384.
  ?item (wdt:P361?) / (wdt:P361 | wdt:P1433) ?published_in.
  ?published_in wdt:P123 ?publisher.
  ?published_in wdt:P291 ?place_of_publication.
}

Try it!

Here is three variants combined to one by use of a property path when finding ?published_in. --Dipsacus fullonum (talk) 13:45, 6 June 2020 (UTC)[reply]

I have reworked it in this way:

SELECT ?AutorLabel ?Titel ?Untertitel ?veroeffentlicht_in ?VerlagLabel ?ErscheinungsortLabel (year(?Erscheinungsdatum) as ?Erscheinungsjahr) WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  {
    ?werk wdt:P655 wd:Q92759384.
    ?werk (wdt:P361?) / (wdt:P361| wdt:P1433) ?published_in.
    ?published_in wdt:P1476 ?veroeffentlicht_in.  
    ?published_in wdt:P123 ?Verlag.
    ?published_in wdt:P291 ?ErscheinungsortLabel.
    ?published_in wdt:P577 ?Erscheinungsdatum.}
  OPTIONAL { ?werk wdt:P50 ?Autor. }
  OPTIONAL { ?werk wdt:P2093 ?Autor. }
  OPTIONAL { ?werk wdt:P1476 ?Titel. }
  OPTIONAL { ?werk wdt:P1680 ?Untertitel. }
}

Try it!

I would rather like to get the name of the town / place of publication displayed (not the Q number of the place). What would I have to change?

Would it be possible to determine, that

in column 1 "Autor" instead of "AutorLabel"
in column 4 "veröffentlicht in" instead of "veroffentlicht_in"

and

in column 5 "Verlag" instead of "VerlagLabel"

is displayed? (However, the content of the lines below should not change.)

--Nstrc (talk) 16:38, 6 June 2020 (UTC)[reply]

Listeria bot edit

Thank you very much. Can I use it with listeria bot? Or can't this work, because we need - at least - a second term different than "item", while listeria works only with "item"? (I tried it, and it didn't work. But I'm not sure, whether I made a mistake or it can't work anyway.) --Nstrc (talk) 14:32, 6 June 2020 (UTC)[reply]

I suppose that the queries can used with Listeria bot, but here others have to help. --Dipsacus fullonum (talk) 14:39, 6 June 2020 (UTC)[reply]

I have recognised now, that Wikidata Query Servive offers at "</> code" lisetria code (among other versions). But also using this code, it does not work for me:

using the listeria code from https://w.wiki/TDD results in:
- User:Nstrc/KWM-Tabelle

and

https://tools.wmflabs.org/listeria/index.php?action=update&lang=en&page=User:Nstrc/KWM-Tabelle ("Status: no items").

substituting within that code "?Datensatz" by "?item" results in: en:User:Nstrc/KWM-Tabelle - 2. Versuch.
Finally, using the listeria code from https://w.wiki/TDT results - even worse - in Tabelle.

--Nstrc (talk) 18:56, 6 June 2020 (UTC)[reply]

Query for multiple GND claims edit

Hi, I ask for help for a query, that lists items with multiple GNDs:

Only items with a link to de.wikipedia
at least two GND ID (P227)
the ranks of these GND properties
Qualifiers if any

Raymond (talk) 08:03, 6 June 2020 (UTC)[reply]

This is a bit of a complicated request, so let's start with this simplified one:

SELECT ?item (COUNT(DISTINCT ?gnd) AS ?gnd_cnt) (GROUP_CONCAT(?gnd; SEPARATOR=', ') AS ?gnds) ?dewiki_sitelink WITH {
  SELECT DISTINCT ?item WHERE {
    ?item wdt:P227 ?gnd .
  } GROUP BY ?item HAVING(COUNT(DISTINCT ?gnd) > 1)
} AS %subquery WHERE {
  INCLUDE %subquery .
  ?item wdt:P227 ?gnd .
  ?dewiki_sitelink schema:about ?item; schema:isPartOf <https://de.wikipedia.org/> .
} GROUP BY ?item ?dewiki_sitelink

Try it!

It only considers "best rank" claims, i.e. all "normal rank" claims if no "preferred rank" claims are present, all "preferred rank" claims only in case there are any such claims, and no "deprecated rank" claims at all; in other words: the same data which you see when using the {{#property:P227}} or {{#statements:P227}} parser function in Wikipedia templates. Qualifiers are somewhat difficult to add here, since the GND identifiers are aggregated into a string.
Starting from here, should we modify the query somehow to gain further insight? —MisterSynergy (talk) 11:30, 6 June 2020 (UTC)[reply]

@Raymond, MisterSynergy: Here is a full version with statements of all ranks and a list of qualifiers as wanted:

SELECT ?item ?itemLabel
       ?rank1 (GROUP_CONCAT(DISTINCT ?qual1; SEPARATOR=", ") AS ?qualifiers1)
       ?rank2 (GROUP_CONCAT(DISTINCT ?qual2; SEPARATOR=", ") AS ?qualifiers2)
WITH
{
  SELECT ?item
  WHERE
  {
    ?item p:P227 [].
  }
  GROUP BY ?item
  HAVING (COUNT(?item) > 1)
} AS %multiple_GND1
WITH
{
  SELECT ?item ?GND_statement1 ?GND_statement2
  WHERE
  {
    INCLUDE %multiple_GND1
    ?item p:P227 ?GND_statement1.
    ?item p:P227 ?GND_statement2.
    FILTER (?GND_statement1 != ?GND_statement2)
    FILTER (STR(?GND_statement1) < STR(?GND_statement2))
  }
} AS %multiple_GND2
WHERE
{
  INCLUDE %multiple_GND2
  ?sitelink schema:about ?item.
  ?sitelink schema:isPartOf <https://de.wikipedia.org/>.
  ?GND_statement1 wikibase:rank ?rank1.
  ?GND_statement2 wikibase:rank ?rank2.
  OPTIONAL { ?GND_statement1 ?qualifier1 []. ?qualifierproperty1 wikibase:qualifier ?qualifier1. }
  OPTIONAL { ?GND_statement2 ?qualifier2 []. ?qualifierproperty2 wikibase:qualifier ?qualifier2. }
  SERVICE wikibase:label
  {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de".
    ?item rdfs:label ?itemLabel.
    ?qualifierproperty1 rdfs:label ?qual1.
    ?qualifierproperty2 rdfs:label ?qual2.
  }
}
GROUP BY ?item ?itemLabel ?GND_statement1 ?GND_statement2 ?rank1 ?rank2

Try it!

--Dipsacus fullonum (talk) 11:53, 6 June 2020 (UTC)[reply]

@Dipsacus fullonum: Thank you very much, that is what I need. May I ask for 2 refinements?

1. Only items with human (Q5)

2. Add the value of the qualifier

Raymond (talk) 12:36, 6 June 2020 (UTC)[reply]

@Raymond: No problem. New version is here:

SELECT ?item ?itemLabel
       ?rank1
       (GROUP_CONCAT(DISTINCT CONCAT(?qual1, ": ", IF(BOUND(?qvalue1Label), ?qvalue1Label, STR(?qvalue1))); SEPARATOR="; ") AS ?qualifiers1)
       ?rank2
       (GROUP_CONCAT(DISTINCT CONCAT(?qual2, ": ", IF(BOUND(?qvalue2Label), ?qvalue2Label, STR(?qvalue2))); SEPARATOR="; ") AS ?qualifiers2)
WITH
{
  SELECT ?item
  WHERE
  {
    ?item p:P227 [].
  }
  GROUP BY ?item
  HAVING (COUNT(?item) > 1)
} AS %multiple_GND1
WITH
{
  SELECT ?item ?GND_statement1 ?GND_statement2
  WHERE
  {
    INCLUDE %multiple_GND1
    ?item wdt:P31 wd:Q5. # only humans
    ?item p:P227 ?GND_statement1.
    ?item p:P227 ?GND_statement2.
    FILTER (?GND_statement1 != ?GND_statement2)
    FILTER (STR(?GND_statement1) < STR(?GND_statement2))
  }
} AS %multiple_GND2
WHERE
{
  INCLUDE %multiple_GND2
  ?sitelink schema:about ?item.
  ?sitelink schema:isPartOf <https://de.wikipedia.org/>.
  ?GND_statement1 wikibase:rank ?rank1.
  ?GND_statement2 wikibase:rank ?rank2.
  OPTIONAL { ?GND_statement1 ?qualifier1 ?qvalue1. ?qualifierproperty1 wikibase:qualifier ?qualifier1. }
  OPTIONAL { ?GND_statement2 ?qualifier2 ?qvalue2. ?qualifierproperty2 wikibase:qualifier ?qualifier2. }
  SERVICE wikibase:label
  {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de".
    ?item rdfs:label ?itemLabel.
    ?qualifierproperty1 rdfs:label ?qual1.
    ?qualifierproperty2 rdfs:label ?qual2.
    ?qvalue1 rdfs:label ?qvalue1Label.
    ?qvalue2 rdfs:label ?qvalue2Label.
  }
}
GROUP BY ?item ?itemLabel ?GND_statement1 ?GND_statement2 ?rank1 ?rank2

Try it!

--Dipsacus fullonum (talk) 14:26, 6 June 2020 (UTC)[reply]

Thank you again, Dipsacus fullonum. Works fine. Raymond (talk) 18:42, 7 June 2020 (UTC)[reply]

Norwegian persons on norwegian wikipedia not having a reference for their birth-date or death-date edit

Is it possible to get a list for all norwegians on Norwegian wikipedia not having a reference for their birth or death date? Pmt (talk) 13:59, 7 June 2020 (UTC)[reply]

SELECT DISTINCT ?item ?itemLabel ?no 
{ 
  ?item wdt:P27 wd:Q20 ; wdt:P31 wd:Q5 .
  ?no schema:about ?item ; schema:isPartOf <https://no.wikipedia.org/> .
  ?item p:P569 ?claim . 
  OPTIONAL { ?claim prov:wasDerivedFrom ?source . ?source ?p ?v . 
  FILTER NOT EXISTS { ?v wdt:P31 wd:Q10876391 } } 
  FILTER(!bound(?source)) 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Above one for birth date. --- Jura 14:08, 7 June 2020 (UTC)[reply]

@Jura1: That's a really interesting query! Playing with variants throws up some interesting stats - 41300 items in total, of which 22400 (54%) have no citations for P569 at all, 12600 (31%) have a citation to something other than WP, and the remaining 6300 (15%) presumably are those cited to WP and nothing else. Andrew Gray (talk) 14:34, 7 June 2020 (UTC)[reply]

@Jura1: Thank you very much, an very usefull Query. Also @Andrew Gray:, there have been "Heavy discussions" included a voting on norwegian Wikipedia where it have been decided that references and other info in Infobox:Biography and other infoboxes shall be taken from Wikidata (max 4), so the hope is that Wikidata birth and death dates will be filled references. in addition occupation (Q12737077) (created by Jura1) will be used together with ISCO-88 occupation class (P952) and Template:P8283 to filter down professions to be used in infoboxes. Breg Pmt (talk) 15:14, 7 June 2020 (UTC)[reply]

Strange timeout edit

Hi all! I've written the following query

#URL contains
SELECT ?p ?db
WHERE {
  ?p p:P569 [ps:P569 ?db; prov:wasDerivedFrom [pr:P854 ?site] ].
  MINUS { ?p wdt:P570 ?dd . }
  FILTER(CONTAINS(STR(?site),"viaf.org/viaf/"))
}
LIMIT 10

Try it!

which, despite looking simple to me, always times out. Any idea about how to get some result? Thank you very much, --Epìdosis 12:47, 5 June 2020 (UTC)[reply]

@Epìdosis: It looks simple, but searches in not-indexed texts are slow, so I'm not particularly surprised that it times out. If you start by selecting items which have links to VIAF by using an API call, you can get some results before the query times out:

SELECT ?p ?db
WHERE
{
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "www.wikidata.org" .
    bd:serviceParam wikibase:api "Generator" .
    bd:serviceParam mwapi:generator "exturlusage" .
    bd:serviceParam mwapi:geuprop "title" .
    bd:serviceParam mwapi:geunamespace "0" .
    bd:serviceParam mwapi:geuprotocol "https" .
    bd:serviceParam mwapi:geuquery "viaf.org/viaf/" .
    bd:serviceParam mwapi:geulimit "max" .
    ?p wikibase:apiOutputItem mwapi:title .
  }
  hint:Prior hint:runFirst "true".
  
  ?p p:P569 [ps:P569 ?db; prov:wasDerivedFrom [pr:P854 ?site] ].
  MINUS { ?p wdt:P570 ?dd . }
  FILTER(CONTAINS(STR(?site),"viaf.org/viaf/"))
}

Try it!

The API call finds items which contains external https links containing "viaf.org/viaf/", but it has a built-in limit to max. 10,000 items. Then these 10,000 items are checked for the other conditions, but because of the limit of 10,000 results from the MWAPI call this query will not give a complete list. --Dipsacus fullonum (talk) 15:44, 5 June 2020 (UTC)[reply]

Readaptation edit

@Dipsacus fullonum: I've readapted your excellent query in this:

SELECT ?p ?db
WHERE
{
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "www.wikidata.org" .
    bd:serviceParam wikibase:api "Generator" .
    bd:serviceParam mwapi:generator "exturlusage" .
    bd:serviceParam mwapi:geuprop "title" .
    bd:serviceParam mwapi:geunamespace "0" .
    bd:serviceParam mwapi:geuprotocol "https" .
    bd:serviceParam mwapi:geuquery "viaf.org/viaf/" .
    bd:serviceParam mwapi:geulimit "max" .
    ?p wikibase:apiOutputItem mwapi:title .
  }
  hint:Prior hint:runFirst "true".
  
  ?p p:P569 [ps:P569 ?db ; prov:wasDerivedFrom [pr:P854 ?site] ].
  FILTER("1950-00-00"^^xsd:dateTime = ?db)
  FILTER(CONTAINS(STR(?site),"viaf.org/viaf/"))
}

Try it!

Now I would need two further readapaptations:

include all the dates of birth having at least a reference containing reference URL (P854) - I think it suffices removing the last FILTER
include not only people born in 1950, but all people born in years ending with 50 (e.g. 450 BCE, 50 BCE, 50 AD, 150 AD, 1450 AD ...) - I don't know how to do this, could you help me?

Thank you very much as always, --Epìdosis 08:43, 8 June 2020 (UTC)[reply]

Here is a query for all years ending in 50. I also added the precision for the date (11=date, 10=month, 9=year, 8=decade, 7=century). The list still is a sample because of the limit of results for use of MWAPI.

SELECT ?p ?db ?precision
WHERE
{
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "www.wikidata.org" .
    bd:serviceParam wikibase:api "Generator" .
    bd:serviceParam mwapi:generator "exturlusage" .
    bd:serviceParam mwapi:geuprop "title" .
    bd:serviceParam mwapi:geunamespace "0" .
    bd:serviceParam mwapi:geuprotocol "https" .
    bd:serviceParam mwapi:geuquery "viaf.org/viaf/" .
    bd:serviceParam mwapi:geulimit "max" .
    ?p wikibase:apiOutputItem mwapi:title .
  }
  hint:Prior hint:runFirst "true".
  
  ?p p:P569 [psv:P569 ?dbv ; prov:wasDerivedFrom [pr:P854 ?site] ].
  FILTER CONTAINS(STR(?site),"viaf.org/viaf/")
  ?dbv wikibase:timeValue ?db; wikibase:timePrecision ?precision.
  BIND (YEAR(?db) AS ?year)
  FILTER IF(?year > 0,
            ?year - FLOOR(?year / 100) * 100 = 50, # year is AD
            ?year - FLOOR(?year / 100) * 100 = 51) # year is BC, 1 BC is encoded as "0", 2 BC as "-1" etc.
}

Try it!

--Dipsacus fullonum (talk) 12:25, 8 June 2020 (UTC)[reply]

Graph asked : a line per year with months edit

Hello, with Tokyo International Airport (Q204853), I'd like to see the 2018,2019,2020 patronage (P3872) monthly statistics, all these 12 months being put into one colored line per year. Would it be possible ? Thanks ! Bouzinac (talk) 14:10, 6 June 2020 (UTC)[reply]

Manually update chart

Graphs are temporarily unavailable due to technical issues.

End of automatically generated list.

@Bouzinac: as above? --- Jura 10:02, 7 June 2020 (UTC)[reply]

Salut Jura1, hum, je ne sais pas trop car là tu as montré un graph annuel. Il y a en fait deux soucis:

Les valeurs annuelles devraient correspondre à par exemple 85 millions pour l'année 2017 (comme il y a de multiples sources, il ne faut pas faire de sommes par années)==> https://w.wiki/THi . S'il était possible d'avoir cette dernière requête en graph visuel affichable côté wikipédia:fr, ce serait super!
Les valeurs mensuelles que je voulais dans ma demande étaient trois courbes, une 2018, une 2019, une 2020, avec janvier, février, mars etc. L'idée était de voir visuellement la chute du trafic aérien sur mars 2020. Bouzinac (talk) 12:25, 7 June 2020 (UTC)[reply]

@Bouzinac: j'ai modifié. Pour d'autres aéroports, il faudrait probablement plus de vérifications pour s'assurer que l'on a qu'une donnée par mois.

ça devrait fonctionner également sur frwiki, si on y fait les mêmes modifications que j'ai faites à Module:Graph pour les variables en minuscules (problème de Listeria). --- Jura 13:31, 7 June 2020 (UTC)[reply]

Graphs are temporarily unavailable due to technical issues.

Oh. peut-être plutôt ainsi. --- Jura 14:03, 7 June 2020 (UTC)[reply]

@Bouzinac, Jura1: I suppose it may also be possible to make a similar linechart using a SPARQL query. But the documentation for that at Wikidata:SPARQL query service/Wikidata Query Help/Result Views is so sparse that I haven't been able to do it. My best try is:

#defaultView:LineChart
SELECT DISTINCT ?month ?patronage ?year 
WHERE
{
  wd:Q204853 p:P3872 ?statement.
  ?statement ps:P3872 ?patronage.
  ?statement pqv:P585 ?timevalue.
  ?timevalue wikibase:timeValue ?time.
  ?timevalue wikibase:timePrecision 10 . # Precsion month (10)
  BIND (STR(MONTH(?time)) AS ?month)
  BIND (STR(YEAR(?time)) AS ?year)
}

Try it!

but the graph has the months in a wrong sequence and I haven't been able to find out how to change it. --Dipsacus fullonum (talk) 14:14, 7 June 2020 (UTC)[reply]

I think there is problem if it isn't string or one is using a browser different from the one of the developer. --- Jura 14:16, 7 June 2020 (UTC)[reply]

Yeah, nice try with the hard-coded values, seems to work. However still trouble with direct query. There is a current [task] about this graph problem (they are removing the graphoid (static image) to enable direct Vega rendering graph. Bouzinac (talk) 20:08, 7 June 2020 (UTC)[reply]

https://www.wikidata.org/wiki/Module:Graph#Test_2-values_with_direct_query_at_Wikidata

– The preceding unsigned comment was added by [[User:|?]] ([[User talk:|talk]] • contribs).

Manually update chart

Graphs are temporarily unavailable due to technical issues.

End of automatically generated list.

Here a dynamic version. Template_talk:Graph:Chart has more samples. --- Jura 05:16, 8 June 2020 (UTC)[reply]

Listado de las organizaciones cubanas edit

Cómo obtener un listado de las organizaciones cubanas con los campos principales, incluyendo los identificadores persistentes q esten registrados

This query finds all Cuban organizations in Wikidata which can be tricky to do effectively. I don't know which information you want displayed for each organization so just a label is displayed now. More can be added.

SELECT ?item ?itemLabel
WHERE
{
  {
    SELECT DISTINCT ?item
    WHERE
    {
      ?item wdt:P17 wd:Q241 . # ?item is Cuban
      hint:Prior hint:runFirst "true". # There are much fewer Cuban items than organizations, but the optimizer cannot see that.

      ?item wdt:P31 ?instance.
      ?instance wdt:P279* wd:Q43229 . # ?item is an organisation
      hint:Prior hint:gearing "forward". # Check if Cuban items are organizations rather than 
                                         # finding all organizations and check if they are Cuban
    }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],es,en". }
}

Try it!

--Dipsacus fullonum (talk) 20:23, 8 June 2020 (UTC)[reply]

Image grid based on multiple image properties edit

Items like Nigeria (Q1033) have multiple image-related properties, and I am trying to build an image grid based on that, for which I have the following query:

The following query uses these:

Items: Nigeria (Q1033)   , Wikidata property to link to Commons (Q18610173)   , Wikidata property for linking to a representative image (Q26940804)

Properties: instance of (P31)

Features: ImageGrid (Q24515278)  

#defaultView:ImageGrid
SELECT * WHERE {
  VALUES ?item {wd:Q1033}
  ?property wdt:P31 wd:Q18610173 .
  ?property wdt:P31 wd:Q26940804 .
  
  ?property a wikibase:Property;
              wikibase:statementValue ?psv ;
              wikibase:claim ?p.

  ?item ?p ?Image1 .

#  ?item ?property ?Image2 . # no results
#  ?item [wdt:P31 wd:Q18610173] ?Image3 . # syntax error
#  ?item ?psv ?Image4 . # no results
#  ?Image1 ?psv ?Image5 . # no results
}

Try it!

This does not actually show the image grid, although it gives the expected number of results in Table view (except perhaps for flag image (P41), where I would prefer to get only the current image). Some of the other things I tried are commented out at the bottom. I'd welcome any pointers on what I am missing. --Daniel Mietchen (talk) 21:34, 8 June 2020 (UTC)[reply]

@Daniel Mietchen: ?Image1 is a wrong type. It is a statement where it should have been a Commons Media. Change wikibase:claim to wikibase:directClaim. --Dipsacus fullonum (talk) 22:00, 8 June 2020 (UTC)[reply]

@Dipsacus fullonum: Thanks — yes, that was it! --Daniel Mietchen (talk) 22:28, 8 June 2020 (UTC)[reply]

Town that have changed of name in the past edit

Hello, I'd like to list towns/cities that have changed at least 3 times in the past, with their dates ? See for instance La Roche-sur-Yon (Q190118) with its official name (P1448) Bouzinac (talk) 20:56, 7 June 2020 (UTC)[reply]

@Bouzinac: This query will list cities etc. with more than 3 officiel names. It doesn't distinguish between names at different times and names in different languages at the same time.

SELECT ?item ?name ?language_tag ?rank ?start ?end
WITH
{
  # Select items with more than 3 official names, i.e. it has changed at least 3 times.
  SELECT ?item
  WHERE
  {
    ?item p:P1448 []. # Officiel name
  }
  GROUP BY ?item
  HAVING (COUNT(?item) > 3)
} AS %multiple_names
WHERE
{
  INCLUDE %multiple_names
  ?item wdt:P31 / wdt:P279 wd:Q486972. # ?item is a settelement
  ?item p:P1448 ?name_statement.
  ?name_statement wikibase:rank ?rank.
  ?name_statement ps:P1448 ?name.
  BIND (LANG(?name) AS ?language_tag).
  OPTIONAL { ?name_statement pq:P580 ?start. }
  OPTIONAL { ?name_statement pq:P582 ?end. }
}
ORDER By ?item ?start

Try it!

--Dipsacus fullonum (talk) 07:12, 8 June 2020 (UTC)[reply]

Nice, thanks! How would I filter only those written in lang:fr ? Bouzinac (talk) 08:38, 8 June 2020 (UTC)[reply]

@Bouzinac: Insert FILTER(?language_tag = "fr") in the query for filter for "fr" as language tag. Insert FILTER LANGMATCHES(?language_tag, "fr") to filter for French language. The latter will include language tags with subtags. I don't think French tags with subtags are used on Wikidata, but there are for other languages. --Dipsacus fullonum (talk) 09:48, 8 June 2020 (UTC)[reply]

Well it gives false positives such as Liège (Q3992) which has never changed of name (but does have many international names) https://w.wiki/TNa Bouzinac (talk) 11:16, 8 June 2020 (UTC)[reply]

@Bouzinac: This version only includes cities which have 4 or more French names. It also includes values for the qualifiers point in time (P585) and epoch (P6259).

SELECT ?item ?name ?language_tag ?rank ?epochLabel ?start ?point_of_time ?end
WITH
{
  # Select items with more than 3 official names, i.e. it has changed at least 3 times.
  SELECT ?item
  WHERE
  {
    ?item p:P1448 []. # Official name
  }
  GROUP BY ?item
  HAVING (COUNT(?item) > 3)
} AS %multiple_names
WITH
{
  SELECT ?item
  WHERE
  {
    INCLUDE %multiple_names
    ?item p:P1448 / ps:P1448 ?name.
    FILTER LANGMATCHES(LANG(?name), "fr")
  }
  GROUP BY ?item
  HAVING (COUNT(?item) > 3)
} AS %multiple_French_names
WHERE
{
  INCLUDE %multiple_French_names
  ?item wdt:P31 / wdt:P279 wd:Q486972. # ?item is a settelement
  ?item p:P1448 ?name_statement.
  ?name_statement wikibase:rank ?rank.
  ?name_statement ps:P1448 ?name.
  BIND (LANG(?name) AS ?language_tag).
  OPTIONAL { ?name_statement pq:P6259 ?epoch. }
  OPTIONAL { ?name_statement pq:P580 ?start. }
  OPTIONAL { ?name_statement pq:P585 ?point_of_time. }
  OPTIONAL { ?name_statement pq:P582 ?end. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr,en". }
}
ORDER By ?item ?start ?point_of_time ?end

Try it!

--Dipsacus fullonum (talk) 11:40, 8 June 2020 (UTC)[reply]

Please use common class for towns and cities - city or town (Q7930989). --Infovarius (talk) 19:18, 9 June 2020 (UTC)[reply]

Hi Dipsacus fullonum, I've updated some French old city names, especially those from French 1789 revolution. I've got two questions for you in the having count clauses here, could you answer their specific use ? https://w.wiki/TfP Thanks ! Bouzinac (talk) 19:32, 10 June 2020 (UTC)[reply]
@Bouzinac: "HAVING" is a keyword for a filter that operates over a grouped solution set. The first subquery named "%multiple_names" finds all entities which have more than 3 statements with official name (P1448) as predicate. It works by finding all such statements, group the statements by the their subject, count the number of equal subjects in each group, and then apply the filter so only entities with a count greater than 3 is preserved. The purpose is to drastically reduce the result set, so the next part of the query can work faster by having a much smaller set of entities to work on. It would not alter the result to remove that subquery, but the query would be slower. It would be faster to change the filter condition to "HAVING (COUNT(?item) > 8)" so the result set is reduced even more.

The second subquery named "%multiple_French_names" select the entities which have more than 8 statements with P1448 with a value in French. It works by finding all statements with P1448 and a value in French, group these statements by their subject, count the number of equal subjects in each group and then apply the filter so only entities with a count greater than 8 is preserved. It would do the same if "INCLUDE %multiple_names" and the first subquery were omitted, but work slower because it would have to find the language of all values of P1448 instead of using the limited result set of the first subquery. I hope that answers your questions. --Dipsacus fullonum (talk) 22:29, 11 June 2020 (UTC)[reply]

Show graph elements by property edit

I would like to show all bridges across a river in a graph *in the order in which they appear* on the river (order by upstream/downstream). Right now I have the following query

#All bridges on Thur with upstream/downstream bridge
#defaultView:Graph
PREFIX gas: <http://www.bigdata.com/rdf/gas#>
SELECT ?item ?itemLabel ?linkTo ?pic ?depth
WHERE 
{
    SERVICE gas:service {
    gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.SSSP" ;
                gas:in wd:Q96108174 ; ## This is my work-around
                #gas:in wdt:P177 wd:Q14339 ; ## This is what I would want!
                gas:traversalDirection "Forward" ;
                gas:out ?item ;
                gas:maxVisited 150;
                gas:out1 ?depth ;
                gas:linkType wdt:P2674
  }
  OPTIONAL { ?item wdt:P2674 ?linkTo }
  OPTIONAL { ?item wdt:P18 ?pic }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?depth

Try it!

however I have two issues here:

this requires me to know the starting point (here Chlostobelstrasse-Brücke (Q96108174) but I would much prefer to only write wdt:P177 wd:Q14339 (all bridges that cross my river of interest). This is because I may not know the start/end point in advance. Is that possible or do I *need* to specify the starting point? Eg is it possible to have the engine choose an arbitrary starting point and then walk bi-directionally? [It may be possible to formulate a sub-query to "walk to the end" and then once the end point is found, use that in the real query as start point]
can I make the graph view default to "hierarchical layout top-down" instead of the non-hierarchical layout that is currently chosen by default?
Finally I have some issues to display the images on the graph nodes, often I just see empty nodes, it seems to work sometimes but not reliably - does somebody know what is going on?

let me know if you have any other improvements/ideas

Best --Hannes Röst (talk) 21:02, 9 June 2020 (UTC)[reply]

Hi Hannes Röst. If all bridges which crosses Thur had statements for next crossing upstream (P2673) and next crossing downstream (P2674) you could find the most upstream bridge as the only one with a novalue special value for P2673 (or maybe without any statement for P2673 at all if novalue wasn't used). But that wouldn't help with the GAS program because GAS programs cannot have variables as input, only constants. Another point is that there are 14 bridges which crosses Thur without statements for P2673 and P2674. Where would you place them in the graph? --Dipsacus fullonum (talk) 08:13, 10 June 2020 (UTC)[reply]

Thanks for the suggestion, this may indeed work under some circumstances but not always since the last bridge on the river Thur is still downstream from other bridges of the two river that flow together to make the river Thur (Wildhauser Thur (Q89288705) and Säntisthur (Q69099248)) so this will not always work unless the river is one that goes all the way up to the source. Of course one could argue that the relationship upstream/downstream should not be set since these are rivers with different names but in the general usage of the term the one bridge would still be downstream from the other. --Hannes Röst (talk) 13:27, 10 June 2020 (UTC)[reply]

Maybe linear reference (P6710) could be set? --- Jura 08:24, 10 June 2020 (UTC)[reply]

What’s wrong with

#All bridges on Thur with upstream/downstream bridge
#defaultView:Graph
PREFIX gas: <http://www.bigdata.com/rdf/gas#>
SELECT ?item ?itemLabel ?linkTo ?pic ?depth
WHERE 
{
  OPTIONAL { ?item wdt:P2674 ?linkTo }
  OPTIONAL { ?item wdt:P18 ?pic }
  ?item wdt:P177 wd:Q14339 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

? Does not need a starting point. author TomT0m / talk page 09:30, 10 June 2020 (UTC)[reply]

Is there are way to get it into table form (for Listeria)? (e.g. as when clicking on the icon "hierarchical layout up to down") --- Jura 10:25, 10 June 2020 (UTC)[reply]

#defaultView:Graph
SELECT ?item ?itemLabel ?pic ?count ?coor ?linkTo
WITH
{
  SELECT ?item (COUNT(DISTINCT ?down) as ?count)
  {  ?item wdt:P177 wd:Q14339 ; wdt:P2674* ?down } 
  GROUP BY ?item
  ORDER BY DESC(?count) ?item
} as %ct
WHERE 
{
  INCLUDE %ct
  ?item wdt:P177 wd:Q14339 .
  OPTIONAL { ?item wdt:P18 ?pic }
  OPTIONAL { ?item wdt:P625 ?coor }
  OPTIONAL { ?item wdt:P2674 ?linkTo }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?count) ?item

Try it!

Something like the above maybe --- Jura 11:43, 10 June 2020 (UTC)[reply]

Great! I added the "?linkTo" and now it works both as graph and as table. Now its quite easy to find issues in wikidata (missing bridges, self-links etc). Great, this helps a lot! --Hannes Röst (talk) 14:42, 10 June 2020 (UTC)[reply]

Items used: River Thames (Q19686)   , river source (Q7376362)   , London Bridge (Q1868889)

Properties used: crosses (P177)   , next crossing downstream (P2674)   , image (P18)   , coordinate location (P625)   , applies to part (P518)

SELECT ?item ?itemLabel ?pic ?count ?coor ?dist (ROUND(?dist/10) as ?layer)
WITH
{
  SELECT ?item (COUNT(DISTINCT ?down) as ?count)
  {  ?item wdt:P177 wd:Q19686 ; wdt:P2674* ?down } 
  GROUP BY ?item
  ORDER BY DESC(?count) ?item
} as %ct
WHERE 
{
  INCLUDE %ct
  ?item wdt:P177 wd:Q19686 .
  OPTIONAL { ?item wdt:P18 ?pic }
  wd:Q19686 p:P625 [ ps:P625 ?coor_orig ; pq:P518 wd:Q7376362 ] .
  OPTIONAL { ?item wdt:P625 ?coor }
  FILTER ( ?item != wd:Q1868889 )
  BIND(ROUND(geof:distance(?coor_orig, ?coor)) as ?dist)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?count) DESC(?dist) ?item

Try it! @Hannes Röst: using distance from river source or river mouth could be another option. Used here when P177 doesn't work. --- Jura 16:32, 10 June 2020 (UTC)[reply]

wow, this really looks amazing! I can use this to annotated the order of the bridges on the river, makes it much easier. Thanks a lot. --Hannes Röst (talk) 16:58, 10 June 2020 (UTC)[reply]

Berlin is not appears in the list of capitals edit

Hello. I am trying to get all capitals (Q5119) with the query below but for some reason Berlin (Q64) is not in the results.

SELECT ?city ?cityLabel
WHERE {
  #VALUES ?city { wd:Q64}
  ?city wdt:P31+ wd:Q5119.
  #wd:Q183 wdt:P36 ?city.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

I see that's https://www.wikidata.org/wiki/Q64 has value "capital" (https://www.wikidata.org/wiki/Q5119) in the "instance of" property https://www.wikidata.org/wiki/Property:P31. But even if I limit search with VALUES ?city { wd:Q64} it doesn't help?

What can be reason of this? – The preceding unsigned comment was added by Wdrupal (talk • contribs).

SELECT ?city ?country ?cityLabel ?countryLabel
WHERE {
  ?country wdt:P31 wd:Q6256 ;          # all countries
           wdt:P36           ?city .   # with their capital
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

Something like this is more likely to work. The « capital » class does not seem to be used everywhere. Could be added though. author TomT0m / talk page 14:35, 10 June 2020 (UTC)[reply]

There's also the capital of (P1376) direction:

SELECT ?country ?countryLabel ?city ?cityLabel WHERE {
  ?country wdt:P31 wd:Q3624078 . # all sovereign states
  ?city p:P1376 ?statement .     # get their capital
  ?statement ps:P1376 ?country .
  FILTER NOT EXISTS { ?statement pq:P582 [] } # unless it has an end date
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY ?countryLabel

Try it!

So I'd be more inclined to remove instance of (P31):capital city (Q5119) everywhere, in favour of capital of (P1376) and capital (P36) approach, rather than in adding it to more cities. The instance of (P31) tree for cities is already pretty nasty, and this seems like one that could be eliminated fairly easily with no real negative consequences. --Oravrattas (talk) 16:32, 11 June 2020 (UTC)[reply]

@Oravrattas: Your query is not OK in case of the presence of a deprecated statement. To follow this approach you would have to exclude them explicitely. It’s easier to rely on the Template:Rank rank - note that you also rely on no former state being a direct instance of sovereign state (Q3624078) if you want to exclude them.

In this case the presence of the country (Q6256) class is pretty much harmless, and the dataset is pretty really stable. Could be better if Wikibase could compute its instance by itself. author TomT0m / talk page 17:37, 11 June 2020 (UTC)[reply]

Sure; this wasn't trying to be an exemplar query — I was just noting that we already have a better property than instance of (P31) for use on the city items, and that it would probably be better to tidy the capital of (P1376) data, and remove the existing instance of (P31): capital city (Q5119) examples rather than add more of those. No matter which direction we go at the minute there are problems with the underlying data, and it would be good to get that tidied up. (BTW my use of sovereign state (Q3624078) was just out of habit, rather than trying to say that it's more correct than country (Q6256). In my experience it tends to be a bit more stable, and usually closer to what people want, so I tend to just default to it, but it always depends on what exactly you're looking for.) --Oravrattas (talk) 19:21, 11 June 2020 (UTC)[reply]

@Wdrupal: As for your original question, the reason why the query doesn't work, even though Berlin does have instance of (P31): capital city (Q5119) is that it's only normal rank, and federated state of Germany (Q1221156) is marked as preferred, so is the only value that gets returned from a wdt:P31 query. To get all statements, not only the ones with highest rank, you could use p:P31/ps:P31 instead. --Oravrattas (talk) 21:19, 11 June 2020 (UTC)[reply]

Q207656 not found when querying for some instances edit

The following all don't yield Apache Wave (Q207656) for me, even though they should.

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

Try it!

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

Try it!

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

Try it!

Interestingly the following works:

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

Try it!

--Gittenburg (talk) 11:53, 12 June 2020 (UTC)[reply]

Some values at Q207656#P31 have preferred rank leading to others not appearing when using wdt:P31 instead of p:P31/ps:P31 --- Jura 12:03, 12 June 2020 (UTC)[reply]

Things named after people involved in the slave trade edit

Hi

I'd like to request a query to provide a list of things named after people involved in the slave trade to create a Wikipedia article. A table with this kind of information would be very helpful:

Named after
Their relation to the slave trade e.g plantation owner, slave trader, slave owner, etc
What the thing is e.g building, statue, street etc
Location
Wikipedia article
References

Thanks

--John Cummings (talk) 12:57, 12 June 2020 (UTC)[reply]

Woah. I don't want to say anything but please keep Wiki* from any political aims. That list might be misused... Bouzinac (talk) 14:01, 12 June 2020 (UTC)[reply]

Indeed. I hope it will not use the same definition of slave trader as a representative of WMDE did in Wikidata:Status updates/2020 06 08 (that is any person with the word "slave" in any language in a label, description or alias) when he published a query to find persons described as slave traders. The list contained many living people who are definitely not slave traders. BTW I may help with the requested later. --Dipsacus fullonum (talk) 15:33, 12 June 2020 (UTC)[reply]

Thanks very much @Dipsacus fullonum:, I'm going to post a discussion in project chat about better mapping of different aspects and roles in the slave trade to help improve the query. --John Cummings (talk) 17:46, 12 June 2020 (UTC)[reply]

@John Cummings: Here is a query. It finds items named after persons with a stated occupation as :Q17769800, slave owner (Q10076267) or Bandeirante (Q806283). It lists what the items are, their administrative location and country, who they are named after and that persons relation to slaves, and a link to English Wikipedia. References are hopefully in the linked Wikidata pages.

SELECT
  ?item ?itemLabel (GROUP_CONCAT(?instance_ofLabel; SEPARATOR=", ") AS ?instance_ofLabels)
  ?location ?locationLabel ?country ?countryLabel ?wikipedia_en
  ?person ?personLabel ?occupation ?occupationLabel
WHERE
{
  VALUES ?occupation { wd:Q17769800 wd:Q10076267 wd:Q806283} # slave trader, slave owner, Bandeirantes
  ?person wdt:P31 wd:Q5. # human
  ?person wdt:P106 ?occupation.
  ?item wdt:P138 ?person. # ?item is named after person
  OPTIONAL { ?item wdt:P31 ?instance_of. }
  OPTIONAL { ?item wdt:P131 ?location. }
  OPTIONAL { ?item wdt:P17 ?country. }
  OPTIONAL
  {
    ?wikipedia_en schema:about ?item.
    ?wikipedia_en schema:isPartOf <https://en.wikipedia.org/> .
  }
  SERVICE wikibase:label
  {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
    ?item rdfs:label ?itemLabel.
    ?instance_of rdfs:label ?instance_ofLabel.
    ?location rdfs:label ?locationLabel.
    ?country rdfs:label ?countryLabel.
    ?person rdfs:label ?personLabel.
    ?occupation rdfs:label ?occupationLabel.
  }
}
GROUP BY
  ?item ?itemLabel ?location ?locationLabel ?country ?countryLabel ?wikipedia_en
  ?person ?personLabel ?occupation ?occupationLabel

Try it!

--Dipsacus fullonum (talk) 20:05, 12 June 2020 (UTC)[reply]

@Dipsacus fullonum:, thanks so much, this is great.--John Cummings (talk) 20:59, 12 June 2020 (UTC)[reply]

Find string in description edit

Hi! I've tried the following query

SELECT ?item ?itemDescription
WHERE {
  ?item wdt:P106 wd:Q82955 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "tr" . }
  FILTER(CONTAINS(STR(?itemDescription),"Alman politikacılar"))
}

Try it!

to find politicians having "Alman politikacılar" in Turkish description, but it gives 0 results, so probably I've written it in the wrong way. Any hint? Thanks as always, --Epìdosis 13:28, 14 June 2020 (UTC)[reply]

SELECT ?item 
WHERE
{
  ?item wdt:P106 wd:Q82955 .
  ?item schema:description "Alman politikacılar"@tr 
}

Try it!

If you just want these, the quick way is the above. It's a bit more complicated if you want "CONTAINS". --- Jura 13:32, 14 June 2020 (UTC)[reply]

@Epìdosis: Here is the bit more complicated version finding Turkish descriptions which contains "Alman politikacılar":

SELECT ?item ?itemDescription
WHERE
{
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "www.wikidata.org".
    bd:serviceParam wikibase:api "Generator".
    bd:serviceParam mwapi:generator "search".
    bd:serviceParam mwapi:gsrsearch "Alman politikacılar".
    bd:serviceParam mwapi:gsrlimit "max".
    ?item wikibase:apiOutputItem mwapi:title.
  }
  ?item schema:description ?itemDescription.
  FILTER (LANG(?itemDescription) = "tr")
  FILTER CONTAINS(?itemDescription, "Alman politikacılar")
}

Try it!

--Dipsacus fullonum (talk) 14:59, 14 June 2020 (UTC)[reply]

candidates edit

Hello. Some items have instance of (P31) -> Mayor of Germasogeia Municipality Elections (Q93306595). The same items have some values with candidate (P726). I want to find these values. Xaris333 (talk) 15:09, 14 June 2020 (UTC)[reply]

@Xaris333:

SELECT ?item ?itemLabel ?candidate ?candidateLabel
WHERE
{
  ?item wdt:P31 wd:Q93306595.
  ?item wdt:P726 ?candidate.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],el,en". }
}

Try it!

--Dipsacus fullonum (talk) 15:33, 14 June 2020 (UTC)[reply]

@Dipsacus fullonum: thanks. But I want the results to be the values of candidate (P726) (to use them with wikidata list). Xaris333 (talk) 15:52, 14 June 2020 (UTC)[reply]

@Xaris333: I'm not sure what you mean as the previous query did give the values of P726, but maybe this?

SELECT DISTINCT ?item ?itemLabel 
WHERE
{
  ?election wdt:P31 wd:Q93306595.
  ?election wdt:P726 ?item.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],el,en". }
}

Try it!

Otherwise please explain more precisely what you want. --Dipsacus fullonum (talk) 16:12, 14 June 2020 (UTC)[reply]

Yes, that I wanted. Thanks! Xaris333 (talk) 16:17, 14 June 2020 (UTC)[reply]

Query about international border (Q12413618) and closed border (Q87443571) edit

Hello I'd like the following table within all European international border (Q12413618). Thanks ! Bouzinac (talk) 15:58, 14 June 2020 (UTC)[reply]

Country A	Country B	Their Q about shared border	if they have Q87443571, their start date	if they have Q87443571, their end date
Italy	Slovenia	Italy–Slovenia border (Q572463)	10 March 2020	15 June 2020

@Bouzinac:

SELECT ?countryA ?countryALabel ?countryB ?countryBLabel ?border ?borderLabel ?closed_start ?closed_end
WHERE
{
  ?border wdt:P31 wd:Q12413618.
  ?border wdt:P17 ?countryA.
  ?border wdt:P17 ?countryB.
  FILTER (STR(?countryA) < STR(?countryB))
  ?countryA wdt:P30 wd:Q46. # continent is Europe
  ?countryB wdt:P30 wd:Q46. # continent is Europe
  OPTIONAL
  {
    ?border p:P31 ?instanceOfStatement.
    ?instanceOfStatement ps:P31 wd:Q87443571.
    OPTIONAL { ?instanceOfStatement pq:P580 ?closed_start. }
    OPTIONAL { ?instanceOfStatement pq:P582 ?closed_end. }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Dipsacus fullonum (talk) 16:33, 14 June 2020 (UTC)[reply]

Hi there Dipsacus fullonum, it's a good start :) May I ask you to add this "must be an existing country" filter ? Thanks ! Bouzinac (talk) 18:49, 14 June 2020 (UTC)[reply]

Hi Bouzinac. I hop this is better, although it is disputed if Republic of Abkhazia (Q31354462) is an existing country.

SELECT ?countryA ?countryALabel ?countryB ?countryBLabel ?border ?borderLabel ?closed_start ?closed_end
WHERE
{
  ?border wdt:P31 wd:Q12413618.
  ?border wdt:P17 ?countryA.
  ?border wdt:P17 ?countryB.
  FILTER (STR(?countryA) < STR(?countryB))
  ?countryA wdt:P30 wd:Q46. # continent is Europe
  ?countryB wdt:P30 wd:Q46. # continent is Europe
  FILTER NOT EXISTS { ?countryA wdt:P576 ?dissolved. } # No date for dissolved
  FILTER NOT EXISTS { ?countryB wdt:P576 ?dissolved. } # No date for dissolved
  OPTIONAL
  {
    ?border p:P31 ?instanceOfStatement.
    ?instanceOfStatement ps:P31 wd:Q87443571.
    OPTIONAL { ?instanceOfStatement pq:P580 ?closed_start. }
    OPTIONAL { ?instanceOfStatement pq:P582 ?closed_end. }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Dipsacus fullonum (talk) 19:08, 14 June 2020 (UTC)[reply]

By the way, closed border (Q87443571) is hardly not used, and I think for a good reason as it cannot properly model the openness of a border. The border may be open to freight and/or to persons crossing. It may open in one direction, but not the other. It may be open for citizens of a country to leave and enter that country, but not for citizens of other countries to enter or only for citizens with a commendable purpose etc. It would probably be necessary with a special property to describe how a border may be crossed. --Dipsacus fullonum (talk) 20:12, 14 June 2020 (UTC)[reply]

You get a point, but how would you model that? For instance, the most closed current border is the one between the two Koreas and perhaps the one Armenia/Turkey. The most open is within European Union... (which has been closed for obvious recent reasons). Bouzinac (talk) 20:57, 14 June 2020 (UTC)[reply]

Perhaps like that test https://www.wikidata.org/wiki/Q1991956#P5817 ? Bouzinac (talk) 21:17, 14 June 2020 (UTC)[reply]

Released film/games with a pending rating edit

I'm looking for a query with the following:

Instance or subclass of video game or film
The item's publication dato have passed
Contains one or multiple of the following properties
ESRB rating (P852) Rating Pending (Q14864333)
Australian Classification (P3156) Check the Classification (Q95469534)
CERO rating (P853) Rating Pending (Q57312857)

--Trade (talk) 09:50, 15 June 2020 (UTC)[reply]

@Trade: Like this?

SELECT DISTINCT ?item ?itemLabel ?instanceOf ?instanceOfLabel
WHERE
{
  OPTIONAL { ?item wdt:P852 wd:Q14864333. BIND(true AS ?pending). }
  OPTIONAL { ?item wdt:P3156 wd:Q95469534. BIND(true AS ?pending). }
  OPTIONAL { ?item wdt:P852 wd:Q57312857. BIND(true AS ?pending). }
  OPTIONAL { ?item wdt:P853 wd:Q14864333. BIND(true AS ?pending). }
  FILTER (?pending)
  OPTIONAL { ?item wdt:P577 ?release_date. }
  FILTER (! BOUND(?release_date) || NOW() > ?release_date)
  ?item wdt:P31 ?instanceOf.
  VALUES ?classes { wd:Q7889 wd:Q11424 } # Video game, film
  ?instanceOf wdt:P279* ?classes.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Dipsacus fullonum (talk) 10:30, 15 June 2020 (UTC)[reply]

BTW The only item removed by the test for video game or film is The Sims 2: Official Trailer (Q65972027). --Dipsacus fullonum (talk) 10:39, 15 June 2020 (UTC)[reply]

Okay, i fixed the request. Hopefully it didn't affected the query too much.--Trade (talk) 11:31, 15 June 2020 (UTC)[reply]

SELECT DISTINCT ?item ?itemLabel ?instanceOf ?instanceOfLabel
WHERE
{
  OPTIONAL { ?item wdt:P852 wd:Q14864333. BIND(true AS ?pending). }
  OPTIONAL { ?item wdt:P3156 wd:Q95469534. BIND(true AS ?pending). }
  OPTIONAL { ?item wdt:P853 wd:Q57312857. BIND(true AS ?pending). }
  FILTER (?pending)
  OPTIONAL { ?item wdt:P577 ?release_date. }
  FILTER (! BOUND(?release_date) || NOW() > ?release_date)
  ?item wdt:P31 ?instanceOf.
  VALUES ?classes { wd:Q7889 wd:Q11424 } # Video game, film
  ?instanceOf wdt:P279* ?classes.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Select all properties with labels (and also knowns) and values of properties edit

I want to add something to the query so I can see values of all objects. For example I want a value of population in all years with the year. I need it as literal.

Thank you.

SELECT ?predicate ?propertyLabel ?propertyAltLabel ?object 
              WHERE {wd:Q15 ?predicate ?object.
              ?property (wikibase:claim| wikibase:directClaim) ?predicate.
              SERVICE wikibase:label { bd:serviceParam wikibase:language "cs". }

Try it!

I am not sure what you mean. The values of the variable ?object is shown in the query. Do you want to show all qualifiers and qualifier values for statements with Q15 as subject? --Dipsacus fullonum (talk) 15:01, 15 June 2020 (UTC)[reply]

@Dipsacus fullonum: Well, I get this: {

           "predicate":{
              "type":"uri",
              "value":"http://www.wikidata.org/prop/P1082"
           },
           "object":{
              "type":"uri",
              "value":"http://www.wikidata.org/entity/statement/Q15-141C4FF9-1A2E-4372-B351-449EA2AFBDBC"
           },
           "propertyLabel":{
              "xml:lang":"cs",
              "type":"literal",
              "value":"počet obyvatel"
           },
           "propertyAltLabel":{
              "xml:lang":"cs",
              "type":"literal",
              "value":"obyvatel, populace"
           }
        },
        {
           "predicate":{
              "type":"uri",
              "value":"http://www.wikidata.org/prop/P1082"
           },
           "object":{
              "type":"uri",
              "value":"http://www.wikidata.org/entity/statement/Q15-1b98bce6-4159-93b1-f2ec-8e4a5e315b57"
           },
           "propertyLabel":{
              "xml:lang":"cs",
              "type":"literal",
              "value":"počet obyvatel"
           },
           "propertyAltLabel":{
              "xml:lang":"cs",
              "type":"literal",
              "value":"obyvatel, populace"
           }
        },

But i want the value of the http://www.wikidata.org/entity/statement/Q15-1b98bce6-4159-93b1-f2ec-8e4a5e315b57 -> the number of people living in Africa nad the year when the population was recorded (Property:P585). --Jan.zhouf (talk) 15:33, 15 June 2020 (UTC)[reply]

@Jan.zhouf: You already have the statement value (the number 1200000000) from result where ?predicate has the value wdt:P1082 (a wikibase:directClaim), but you can get the statement value also when the predicate is p:P22 (a wikibase:claim):

SELECT ?predicate ?propertyLabel ?propertyAltLabel ?object ?value
WHERE
{
  wd:Q15 ?predicate ?object.
  ?property (wikibase:claim| wikibase:directClaim) ?predicate.
  OPTIONAL
  {
    ?property  wikibase:statementProperty ?ps.
    ?object ?ps ?value.
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "cs". }
}

Try it!

--Dipsacus fullonum (talk) 15:50, 15 June 2020 (UTC)[reply]

@Dipsacus fullonum: Thank you! This is awesome. But could i get also point of time (P585) for the objects who has it? Do you know how?

@Jan.zhouf: I already asked if wanted to show qualifiers and qualifier values without an affirmative answer, but now it seems that is what you want anyway, so I have added it here:

SELECT ?predicate ?propertyLabel ?propertyAltLabel ?object ?value ?qualifier ?qualifierLabel ?qualifierValue
WHERE
{
  wd:Q15 ?predicate ?object.
  ?property (wikibase:claim| wikibase:directClaim) ?predicate.
  OPTIONAL
  {
    ?property wikibase:statementProperty ?ps.
    ?object ?ps ?value.
    OPTIONAL
    {
      ?qualifier  wikibase:qualifier ?pq.
      ?object ?pq ?qualifierValue.
    }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "cs". }
}

Try it!

--Dipsacus fullonum (talk) 10:55, 17 June 2020 (UTC)[reply]

Search in Wikipedia category edit

Hi! Some months ago a query like

#Search in category
PREFIX mw: <http://tools.wmflabs.org/mw2sparql/ontology#>
SELECT DISTINCT ?item ?itemLabel {
  hint:Query hint:optimizer "None" .
  ?cat schema:about wd:Q8882780
  SERVICE <http://tools.wmflabs.org/mw2sparql/sparql> {
    ?page mw:inCategory ?cat
  }
  ?page schema:about ?item .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],it". }
}

Try it!

worked well, giving some results, while now it results in an error. Do you know why it happens and if there are alternative ways to obtain a list of items containing sitelinks being in categories which are sitelinks of a certain item? Thank you very much! --Epìdosis 22:44, 17 June 2020 (UTC)[reply]

@Epìdosis: I don't know why mw:MW2SPARQL don't work but I note that the documentation page says: "Caution: This is an experimental project. Anything could change without notice.". Fortunately you can also search categories using MWAPI. The query below will search the category in all sitelinks where MW2SPARQL according to the documentation page only supported English, French and German wikipedias. I added a variable to show in which Wikipedia(s) each item is found.

#Search in category
SELECT DISTINCT ?item ?itemLabel (GROUP_CONCAT(?endpoint; SEPARATOR=", ") AS ?found_in)
{
  ?cat schema:about wd:Q8882780.
  ?cat schema:isPartOf ?wikimedia_site.
  ?cat schema:name ?category_title.
  BIND (STRBEFORE(STRAFTER(STR(?wikimedia_site), "https://"), "/") AS ?endpoint)
  OPTIONAL
  {
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:endpoint ?endpoint.
      bd:serviceParam wikibase:api "Generator".
      bd:serviceParam mwapi:generator "categorymembers".
      bd:serviceParam mwapi:gcmtitle ?category_title.
      bd:serviceParam mwapi:gcmlimit "max".
      ?item wikibase:apiOutputItem mwapi:item.
      ?title wikibase:apiOutput mwapi:title.
    }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],it". }
}
GROUP BY ?item ?itemLabel

Try it!

--Dipsacus fullonum (talk) 06:22, 18 June 2020 (UTC)[reply]

I tried to add OPTIONAL { ?item wdt:P31 ?inst } to something like the above. How can I avoid that it times-out? --- Jura 07:02, 18 June 2020 (UTC)[reply]

@Jura1: When I just tried that I saw no timeout and no noticeable increase in running time. I cannot answer the question without knowing the code causing the timeout. --Dipsacus fullonum (talk) 07:07, 18 June 2020 (UTC)[reply]

@Dipsacus fullonum:

#Search in category
SELECT DISTINCT ?item ?itemLabel ?inst ?instLabel (GROUP_CONCAT(?endpoint; SEPARATOR=", ") AS ?found_in)
{
  ?cat schema:about wd:Q8682545 .
  ?cat schema:isPartOf ?wikimedia_site.
  ?cat schema:name ?category_title.
  BIND (STRBEFORE(STRAFTER(STR(?wikimedia_site), "https://"), "/") AS ?endpoint)
  OPTIONAL
  {
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:endpoint ?endpoint.
      bd:serviceParam wikibase:api "Generator".
      bd:serviceParam mwapi:generator "categorymembers".
      bd:serviceParam mwapi:gcmtitle ?category_title.
      bd:serviceParam mwapi:gcmlimit "max".
      ?item wikibase:apiOutputItem mwapi:item.
      ?title wikibase:apiOutput mwapi:title.
    }
  }
  OPTIONAL { ?item wdt:P31 ?inst }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],it". }
}
GROUP BY ?item ?itemLabel ?inst ?instLabel

Try it!

Indeed. Maybe it's a problem with Category:Rose gardens (Q8682545) I had been using, though it doesn't have that many members either. --- Jura 07:12, 18 June 2020 (UTC)[reply]

There are 4 redirects in these categories without an associated them, e.g. https://en.wikipedia.org/wiki/Mills_Rose_Garden , maybe that throws it off. --- Jura 07:20, 18 June 2020 (UTC)[reply]

FILTER(BOUND(?item) ) fixes it .. (and !BOUND crashed it) --- Jura 07:24, 18 June 2020 (UTC)[reply]

SELECT ?wp ?title ?item ?inst ?instLabel
{
    ?cat wdt:P301 wd:Q291177 .
    [] schema:about ?cat ; schema:isPartOf ?wp ; schema:name ?catname .
    BIND(strbefore(strafter(str(?wp),"//"),"/") as ?wiki) 
    BIND(str(?catname) as ?cstr)
    FILTER(BOUND(?cstr) ) 
    SERVICE wikibase:mwapi
      {
      bd:serviceParam wikibase:endpoint ?wiki ; wikibase:api "Generator".
      bd:serviceParam mwapi:generator "categorymembers".
      bd:serviceParam mwapi:gcmtitle ?cstr ; mwapi:gcmnamespace "0".
      bd:serviceParam mwapi:gcmlimit "max".
      ?item wikibase:apiOutputItem mwapi:item.
      ?title wikibase:apiOutput mwapi:title.    
      }
    FILTER(BOUND(?item) )
    OPTIONAL { ?item wdt:P31 ?inst }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

The above works. --- Jura 07:27, 18 June 2020 (UTC)[reply]

@Jura1: Yes, the problem was caused by ?item being unbound. The were two causes for that: 1. Category members not bound to a Wikidata item: That were a few Wikipedia pages without an item and all the files in the Commons category. 2. MWAPI failed to get any category members at all from fa.wikipedia.org. That may be caused by bad handling of right-to-left script somewhere and should be investigated more. A work-around is as you found FILTER BOUND(?item). I also added a subquery to get the things done in the right order:

#Search in category
SELECT DISTINCT ?item ?itemLabel ?inst ?instLabel ?found_in
WITH
{
  SELECT ?item (GROUP_CONCAT(?endpoint; SEPARATOR=", ") AS ?found_in)
  WHERE
  {
    ?cat schema:about wd:Q8682545 .
    ?cat schema:isPartOf ?wikimedia_site.
    ?cat schema:name ?category_title.
    BIND (STRBEFORE(STRAFTER(STR(?wikimedia_site), "https://"), "/") AS ?endpoint)
    OPTIONAL
    {
      SERVICE wikibase:mwapi
      {
        bd:serviceParam wikibase:endpoint ?endpoint.
        bd:serviceParam wikibase:api "Generator".
        bd:serviceParam mwapi:generator "categorymembers".
        bd:serviceParam mwapi:gcmtitle ?category_title.
        bd:serviceParam mwapi:gcmlimit "max".
        ?item wikibase:apiOutputItem mwapi:item.
      }
    }
    FILTER BOUND (?item)
  }
  GROUP BY ?item
} AS %get_items
WHERE
{
  INCLUDE %get_items
  OPTIONAL { ?item wdt:P31 ?inst }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],it". }
}

Try it!

--Dipsacus fullonum (talk) 08:10, 18 June 2020 (UTC)[reply]

TimeOut Query edit

Hello, I'm experiencing a T.Out with that :

SELECT ?item  ?label_en ?label_fr WHERE {
  ?item wdt:P31/wdt:P279* wd:Q47150325.

 optional {?item rdfs:label ?label_en filter (lang(?label_en) = "en").}
 optional{  ?item rdfs:label ?label_fr filter (lang(?label_fr) = "fr").}
}

Try it!

Any thoughts? Thanks! Bouzinac (talk) 10:04, 18 June 2020 (UTC)[reply]

Not an answer, but I think calendar day of a given year (Q47150325) is useless as is : any concrete instance of calendar date is actually a date with a definite year. It should not be a subclass of « calendar date » imho.

Something that would work conceptually imho, is something like

⟨ September 16, 1907 (Q2546241)  

 ⟩ subclass of (P279) ⟨ 1907 calendar date ⟩

as well as

⟨ September 16, 1907 (Q2546241)  

 ⟩ subclass of (P279) ⟨ September 16, 1907 (Q2546241)  

 ⟩

and recycle calendar day of a given year (Q47150325) as a metaclass for the classes like « 1907 calendar date » and its cousins.

author TomT0m / talk page 10:48, 18 June 2020 (UTC)[reply]

@Bouzinac: The query has 189,281 results and each result has labels in several languages. I doubt that it is possible to extract labels for so many results at once without timeout. I recommend to use more queries with fewer results in each, for instance by filtering on values of point in time (P585). --Dipsacus fullonum (talk) 11:17, 18 June 2020 (UTC)[reply]

Foreign war graveyards in Norway edit

Is it possible to have a query listing all foreign war graveyards in Norway. The graveyard should (at least) have one of the following identifiers Australian War Memorial ID (P6713), Volksbund ID (P6244) or CWGC burial ground ID (P1920). Breg Pmt (talk) 14:18, 18 June 2020 (UTC)[reply]

@Pmt: The following query gives four such items

SELECT ?item ?itemLabel ?p6713 ?p6244 ?p1920 {
  ?item wdt:P31/wdt:P279* wd:Q39614 .
  ?item wdt:P17 wd:Q20 .
  OPTIONAL { ?item wdt:P6713 ?p6713 }
  OPTIONAL { ?item wdt:P6244 ?p6244 }
  OPTIONAL { ?item wdt:P1920 ?p1920 }
  FILTER(BOUND(?p6713)|| BOUND(?p6244) || BOUND(?p1920))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?itemLabel

Try it!

Please note that this query relies on that objects "in Norway" have the property country (P17) with the value Norway (Q20).

--Larske (talk) 17:58, 18 June 2020 (UTC)[reply]

Munzinger person ID but GND missing edit

I would like to know how many items have Munzinger person ID (P1284) but GND ID (P227) is missing. --Kolja21 (talk) 19:33, 18 June 2020 (UTC)[reply]

Can someone correct the following query:

SELECT ?item {

 ?item wdt:P31 wd:Q5.
 ?item wdt:P1284 ?Munzinger_person_ID
 minus {?item P227 ?gnd .}

}

@Kolja21: Try this query:

SELECT (COUNT(?item) AS ?count) {
 ?item wdt:P31 wd:Q5.
 ?item wdt:P1284 [] .
 MINUS {?item wdt:P227 [] .}
}

Try it!

Right now the answer to your question is 3,543.

--Larske (talk) 20:20, 18 June 2020 (UTC)[reply]

Thanks. More than I've thought. --Kolja21 (talk) 20:55, 18 June 2020 (UTC)[reply]

Similar references for the same value edit

Hi! I would be interested in the following two queries:

all items having a value of date of birth (P569) having at least two references, the first containing stated in (P248)BnF authorities (Q19938912) and the second containing stated in (P248)general catalog of BnF (Q15222191)
all items having a value of date of birth (P569) having at least two references, the first containing stated in (P248)BnF authorities (Q19938912) and the second containing reference URL (P854) with a URL containing the string "data.bnf.fr"

If necessary for avoiding timeout, you can place a LIMIT 100. Thank you very much! --Epìdosis 22:50, 18 June 2020 (UTC) P.S. Possibly consider not only best-rank values: I'm interested both in preferred-rank-values and in normal-rank-values; if necessary, you can split both queries in two, one part for preferred-rank-values and the other part for normal-rank-values. Good night, --Epìdosis 23:10, 18 June 2020 (UTC)[reply]

@Epìdosis: Here is the first of the two requested queries. It can run as a single query without a LIMIT.

SELECT ?item ?rank
WHERE
{
  {
    ?reference1 pr:P248 wd:Q19938912.
    ?reference2 pr:P248 wd:Q15222191.
    ?statement prov:wasDerivedFrom ?reference1.
    ?statement prov:wasDerivedFrom ?reference2.
  }
  hint:Prior hint:runFirst "true".

  ?item p:P569 ?statement.
  VALUES ?rank { wikibase:NormalRank wikibase:PreferredRank } # Wanted ranks
  ?statement wikibase:rank ?rank.  
}

Try it!

--07:58, 19 June 2020 (UTC)

@Epìdosis: And here is the second of the two:

SELECT ?item ?rank
WHERE
{
  {
    ?reference1 pr:P248 wd:Q19938912.
    ?reference2 pr:P854 ?URL.
    ?statement prov:wasDerivedFrom ?reference1.
    ?statement prov:wasDerivedFrom ?reference2.
    FILTER (?reference1 != ?reference2) # Requested to be different references
  }
  hint:Prior hint:runFirst "true".
  
  ?item p:P569 ?statement.
  VALUES ?rank { wikibase:NormalRank wikibase:PreferredRank } # Wanted ranks
  ?statement wikibase:rank ?rank.
  FILTER CONTAINS(STR(?URL), "data.bnf.fr")  
}

Try it!

--Dipsacus fullonum (talk) 08:12, 19 June 2020 (UTC)[reply]

@Dipsacus fullonum: Thank you very very much! --Epìdosis 08:18, 19 June 2020 (UTC)[reply]

I have replaced query #1 with a faster version. --Dipsacus fullonum (talk) 08:26, 19 June 2020 (UTC)[reply]

wikidata-qnr property edit

Hello,

I am looking for the correct property to request for an item with a Wikidata-qnr. wdt:4070 [1] does not work unfortunately. However, I cannot find the appropriate one.

In this example I like to know the qnr of and article indicated by an qnr.

SELECT distinct ?author                                                              #please tell me the author
                                     WHERE { ?article wdt:P4070 ?article_qnr .       #an article has a qnr    
                                     Values ?article_qnr { 'Q36369661' }.          #the qnr is Q36369661
                                     { ?article wdt:P50 ?author }.                    #the article has an author
                                     SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE], ar,be,bg,bn,ca,cs,da,de,el,en,es,et,fa,fi, fr,he,hi,hu,hy,id,it,ja,jv,ko,nb,nl,eo,pa,pl,pt,ro,ru,sh,sk,sr,sv,sw,te,th,tr,uk,yue,vec,vi,zh"}

Try it!

Thanks a lot!

[1] identifier shared with (P4070); qualifier, to be used on external identifier IDs, indicating another Wikidata item is also matched to this ID

--Eva (talk)

@EvaSeidlmayer: You don't need to use P4070 for this - it's actually a really simple query. Just take your ?article wdt:P50 ?author line, and replace ?article with a wd: reference for the item you want like so. Andrew Gray (talk) 12:51, 19 June 2020 (UTC)[reply]

select distinct ?author where { wd:Q36369661 wdt:P50 ?author }

Try it!

Great! Thank you!

List of countries in 1754 edit

The list of example queries includes one for List of countries in 1754. The results from it are a bit of a mess though, and it's not clear how much of that is down to poor underlying data (whether missing, duplicated, erroneous, or poorly modelled), and how much to do with this version of the query being quite old and not really reflecting how we would actually want to write it now (e.g. in some cases the relevant dates seem to be on instance of (P31) qualifiers, not top level statements, and many countries have multiple inception (P571) statements to reflect different historic periods). The closest comparative list I can find to aim for is List of state leaders in 1754. Leaving aside for now the added complication of getting the list of rulers, it seems we should at least be able to get closer to replicating the list of countries there. Can anyone suggest an improved version? --Oravrattas (talk) 09:56, 16 June 2020 (UTC)[reply]

One oddity among others : Kingdom of Portugal (Q45670) and Portugal (Q45)...

Copying the end time (P582) into dissolved, abolished or demolished date (P576) among these countries might be a good idea. Bouzinac (talk) 10:50, 16 June 2020 (UTC)[reply]

@Bouzinac: Do you mean places like Beylik of Dulkadir (Q1264808) with a statement-level end time (P582)? In those cases I think we should probably move those to dissolved, abolished or demolished date (P576) rather than just copying them, unless there's some value in keeping the P580/P582 versions as well? Or do you mean copying them out of the qualifiers on instance of (P31) statements? That one seems tricker as there appears to be some confusion as to what the start time (P580) and end time (P582) actually mean on a historical country (Q3024240) claim — does it start being a historic/former country on the date it stops being an actual country? Or do the dates represent when it was a country, even though we now class it as a former country? --Oravrattas (talk) 12:29, 17 June 2020 (UTC)[reply]

I've migrated all start time (P580)/end time (P582) statements on countries to inception (P571)/dissolved, abolished or demolished date (P576) instead (unless those already existed. Items that have both should also be cleaned up at some point, but I'm treating that as a slightly separate issue.) --Oravrattas (talk) 18:36, 20 June 2020 (UTC)[reply]

Query for usage of instances of inverse property label item (Q65932995) as objects in items edit

Instances of inverse property label item (Q65932995) exist as values for inverse label item (P7087). Is there a way to query that shows all usages of the instances of inverse property label item (Q65932995) that are used with a different property then inverse label item (P7087)? ChristianKl ❪✉❫ 11:23, 19 June 2020 (UTC)[reply]

@ChristianKl: This query shows all uses of instances of Q65932995 as a value for a statement, a value for a qualifier or a value for a reference. Cases with P7087 are filtered out. Remove one or more of the three filters to see these cases.

SELECT ?entity ?entityLabel ?property ?propertyLabel ?qualifier ?qualifierLabel
       ?referenceproperty ?referencepropertyLabel ?instance ?instanceLabel
WHERE
{
  ?instance wdt:P31 wd:Q65932995.
  {
    # Uses of ?instance as statement value
    ?statement ?value ?instance.
    ?property wikibase:statementProperty ?value.
    ?property wikibase:claim ?claim.
    ?entity ?claim ?statement.
    FILTER (?property != wd:P7087)
  }
  UNION
  {
    # Uses of ?instance as qualifier value
    ?statement ?qualifiervalue ?instance.
    ?qualifier wikibase:qualifier ?qualifiervalue.
    ?property wikibase:claim ?claim.
    ?entity ?claim ?statement.
    FILTER (?qualifier != wd:P7087)
  }
  UNION
  {
    # Uses of ?instance as reference value
    ?reference ?referencevalue ?instance.
    ?referenceproperty wikibase:reference ?referencevalue.
    ?statement  prov:wasDerivedFrom ?reference.
    ?property wikibase:claim ?claim.
    ?entity ?claim ?statement.
    FILTER (?referenceproperty != wd:P7087)
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Dipsacus fullonum (talk) 12:42, 19 June 2020 (UTC)[reply]

@Dipsacus fullonum: Thanks, I found some wrong uses with that query. I would like to make it into a list to be able to follow changes. Somehow my attempts at https://www.wikidata.org/wiki/User%3AChristianKl%2FOut_of_scope_use_of_labels don't work. ChristianKl ❪✉❫ 12:11, 20 June 2020 (UTC)[reply]

@ChristianKl: Listeria requires the primary column to be named 'item'. If you switch the query to use 'item' instead of 'entity' it should work. --Oravrattas (talk) 15:08, 20 June 2020 (UTC)[reply]

Has "Formation" and "U.S.A." in title, without a subject edit

I'm working on a geology project and am trying to build a list of scholarly article (Q13442814) with the word "formation" and "U.S.A." (or any of the 50 states, and maybe the territories) in the title, but has no main subject (P921). Hoping someone with far better skills than I have would, build this for me. Thanks. Trilotat (talk) 15:09, 19 June 2020 (UTC)[reply]

Hi Trilotat. I think that is impossible. Searching words inside text strings is slow, and with the huge number of article items in Wikidata, such a query will timeout. You need to give more search criteria that are faster to check and can reduce the number of possible results. --Dipsacus fullonum (talk) 16:35, 19 June 2020 (UTC)[reply]

Thank you, Dipsacus fullonum. How about if one filters for a particular publication, e.g. published in (P1433) Geology (Q5535339)? Trilotat (talk) 16:38, 19 June 2020 (UTC)[reply]

@Trilotat: There are no articles in Geology (Q5535339) with the words "formation" and "U.S.A." in the title. I have outcommented the filter for "U.S.A.", leaving titles containing "formation":

SELECT ?article ?title
WHERE
{
  ?article wdt:P31 wd:Q13442814.
  ?article wdt:P1433 wd:Q5535339.
  ?article wdt:P1476 ?title.
  FILTER REGEX(?title, "\\bformation\\b")
#  FILTER REGEX(?title, "\\bU\\.S\\.A\\.\\b")
}

Try it!

--Dipsacus fullonum (talk) 17:01, 19 June 2020 (UTC)[reply]

@Trilotat: If you assume that the title is identical to the item's English label, you can make a faster search using that. This query gives all scholarly article (Q13442814) with the words "formation" in both title and English label. Again there is no with the word "U.S.A." in the titles.

SELECT ?article ?title
WHERE
{
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "www.wikidata.org".
    bd:serviceParam wikibase:api "Generator".
    bd:serviceParam mwapi:generator "search".
    bd:serviceParam mwapi:gsrsearch "haswbstatement:P31=Q13442814 inlabel:formation@en".
    bd:serviceParam mwapi:gsrlimit "max".
    ?article wikibase:apiOutputItem mwapi:title.
  }
  ?article wdt:P31 wd:Q13442814.
  ?article wdt:P1476 ?title.
  FILTER REGEX(?title, "\\bformation\\b")
#  FILTER REGEX(?title, "\\bU\\.S\\.A\\.\\b")
}

Try it!

--Dipsacus fullonum (talk) 17:15, 19 June 2020 (UTC)[reply]

Thanks again, Dipsacus fullonum. If "U.S.A." isn't in the titles in the Geology journal, is it possible to add a state, e.g. "Nevada"? I'll muddle through replacing the term as I search around for other states. I hope I can follow along and make other queries using your example. Trilotat (talk) 17:30, 19 June 2020 (UTC)[reply]

Noticing that capitalization matters, so I'm editing to show "Formation" and I get more useful results. Acknowledged; That wasn't in my original request. Trilotat (talk) 17:43, 19 June 2020 (UTC)[reply]

List of all current municipalities in Switzerland edit

Hi, I would like to create a list of all municipalities in Switzerland (excluding former municipalities). I currently have

SELECT ?item ?itemLabel WHERE {
  ?item p:P31 ?inst .
  ?inst ps:P31 wd:Q70208 . # Swiss municipalities
  FILTER NOT EXISTS {?item wdt:P31 wd:Q685309} # remove former municipalities
  FILTER NOT EXISTS {?inst pq:P582 ?endtime} # remove municipalities with end time
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de"  }  
}

this got me real close, but then I realized that this would also remove instances such as Bözberg (Q1020189) which used to be a municipality, then wasnt and now is again. Basically both these filters would hit (as far as I understand) a case like Bözberg (Q1020189). What I would like to do is obtain all Swiss municipalities that fullfill these criteria:

have instance of (P31) municipality of Switzerland (Q70208) *and not* qualifier end time (P582)
remove all items with instance of (P31) former municipality of Switzerland (Q685309) which do *not* have end time (P582) (they are still "former")

Thanks a lot --Hannes Röst (talk) 21:25, 19 June 2020 (UTC)[reply]

@Hannes Röst:

SELECT ?item ?itemLabel
WHERE
{
  # VALUES ?item { wd:Q1020189 }
  ?item wdt:P31 wd:Q70208 .
  MINUS {?item wdt:P31 wd:Q685309 }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en"  }  
}

Try it!

Normally the above should do. --- Jura 21:34, 19 June 2020 (UTC)[reply]

@Jura1, Hannes Röst: I don't agree the the query above should do. Firstly, it relies on rank of the P31 statements, therefore it misses Pont-en-Ogoz (Q66903) which is both a municipality of Switzerland (Q70208) and former municipality of Switzerland (Q685309) with best rank. Secondly, it includes several former municipalities (Noflen (Q66152), Scherz (Q66721) and several others) with no use of Q685309. Instead I suggest to totally disregard Q685309 (which like all "former something" after my opinion should never be used anyway), and instead only select municipalities with no end date:

SELECT ?item ?itemLabel
WHERE
{
  ?item p:P31 ?instancestatement .
  ?instancestatement ps:P31 wd:Q70208 .
  ?instancestatement a wikibase:BestRank .
  FILTER NOT EXISTS { ?instancestatement pq:P582 [] }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en"  }  
}

Try it!

--Dipsacus fullonum (talk) 05:47, 20 June 2020 (UTC)[reply]

@Dipsacus fullonum, Hannes Röst: It depends if the query should try to use the data as is or if should allow to fix possible problems. I split Pont-en-Ogoz (Q66903) in two items are one shouldn't try to mix these merely because the name happens to be the same. --- Jura 14:31, 21 June 2020 (UTC)[reply]

hmm I am also not sure how to properly represent these cases, the Wikipedia article clearly talks about both the current and former municipality. Also, there is some link between the two and it is not clear to me how to best represent that. Currently different from (P1889) does not quite capture it for me, maybe follows (P155) or replaces (P1365) would be accurate (probably replaces (P1365) since it replaces an entity on the same geographical territory). --Hannes Röst (talk) 13:26, 22 June 2020 (UTC)[reply]

Wikipedia articles tend to include much information beyond the main topic. replaces (P1365) might work, but probably only as a qualifier somewhere. I think gained territory from (P7903) is a nice way to link them. It's just not used that much yet. --- Jura 15:11, 22 June 2020 (UTC)[reply]

List of articles of certain category with interwiki to a certain language edit

In w:en:Category:International development agencies there are 126 articles.

Need a sub-list of the articles in this category wich have interwiki in the Ukrainian (uk) Wikipedia

Please help. --Perohanych (talk) 14:12, 21 June 2020 (UTC)[reply]

@Perohanych: This query almost makes the sublist you wanted. The list only contains articles with interwiki to uk.wikipedia.org generated with Wikidata connections. English artcicles with explicit interwiki links directly in the articles aren't in the list.

SELECT ?article
{
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "en.wikipedia.org".
    bd:serviceParam wikibase:api "Generator".
    bd:serviceParam mwapi:generator "categorymembers".
    bd:serviceParam mwapi:gcmtitle "Category:International development agencies".
    bd:serviceParam mwapi:gcmnamespace "0".
    bd:serviceParam mwapi:gcmlimit "max".
    ?item wikibase:apiOutputItem mwapi:item.
  }
  FILTER BOUND(?item)
  ?article schema:about ?item.
  ?article schema:isPartOf <https://en.wikipedia.org/>.
  FILTER EXISTS
  {
    ?article_uk schema:about ?item.
    ?article_uk schema:isPartOf <https://uk.wikipedia.org/>.
  }
}

Try it!

--Dipsacus fullonum (talk) 11:40, 22 June 2020 (UTC)[reply]

@Perohanych: Here is query which list all articles in the category with language links to Ukrainian Wikipedia. It should – unlike the query above – also work for articles with explicit interwiki links and with no Wikidata item.

SELECT ?title ?article
{
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "en.wikipedia.org".
    bd:serviceParam wikibase:api "Generator".
    bd:serviceParam mwapi:generator "categorymembers".
    bd:serviceParam mwapi:gcmtitle "Category:International development agencies".
    bd:serviceParam mwapi:gcmnamespace "0".
    bd:serviceParam mwapi:gcmlimit "max".
    bd:serviceParam mwapi:prop "langlinks".
    bd:serviceParam mwapi:lllang "uk".
    bd:serviceParam mwapi:lllimit "max".
    ?title wikibase:apiOutput mwapi:title.
    ?uklink wikibase:apiOutput "langlinks/ll/text()".
  }
  FILTER BOUND(?uklink)
  BIND (IRI(CONCAT("https://en.wikipedia.org/wiki/", ENCODE_FOR_URI(?title))) AS ?article)
}

Try it!

--Dipsacus fullonum (talk) 13:03, 22 June 2020 (UTC)[reply]

@Dipsacus fullonum: Great! That is exactly what I need! Thank you! --Perohanych (talk) 13:07, 22 June 2020 (UTC)[reply]

Generating a list of oldest living US Presidents edit

At https://nwhyte.livejournal.com/3409349.html there's an interesting discussion on people who have been both the oldest living ex-President and oldest living ex-Vice President of the United States. I'm pretty sure Wikidata has complete and accurate enough data to be able to generate a list like this for the President (and, mostly also for the Vice President, although it looks like we have a few odd duplicate items, mostly via Malayalam Wikipedia, needing merged), but I'm struggling to work out how to formulate the query to list them. Getting the list of all presidents, with the end of their time in office, and date of death, is fairly trivial:

SELECT ?person ?personLabel ?end ?dod {
  ?person wdt:P31 wd:Q5; p:P39 [ ps:P39 wd:Q11696 ; pq:P582 ?end ] ; wdt:P569 ?dob ; wdt:P570 ?dod .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?end

Try it!

But getting from that to who was the oldest still living at the relevant points is hurting my brain too much. Anyone able to put something together? --Oravrattas (talk) 20:48, 19 June 2020 (UTC)[reply]

@Oravrattas: I can't get there, but here's a start:

SELECT ?day ((count(?pres)+1) as ?n) WHERE 
{
 { SELECT distinct ?day WHERE {
  { ?m wdt:P31 wd:Q5 . ?m p:P39 ?st . ?st ps:P39 wd:Q11696 . ?m wdt:P570 ?day } union
  { ?m wdt:P31 wd:Q5 . ?m p:P39 ?st . ?st ps:P39 wd:Q11696 . ?st pq:P580 ?day } union
  { ?m wdt:P31 wd:Q5 . ?m p:P39 ?st . ?st ps:P39 wd:Q11696 . ?st pq:P582 ?day } } }
  optional { ?pres wdt:P31 wd:Q5 . ?pres p:P39 ?st . ?st ps:P39 wd:Q11696 . ?st pq:P582 ?end .
             optional { ?pres wdt:P570 ?died } . filter (COALESCE(?died, NOW()) > ?day ) .
             filter (?day >= ?end ) . }
} group by ?day order by ?day

Try it!

This basically replicates the table at en:Living presidents of the United States (the +1 is a fudge to make the numbers line up since my query only counts former presidents, that one counts current presidents as well). The first part finds all the days when the count might change - a President starts, ends, or dies.

The second part then figures out for each of those days, how many people had a) a presidential term which had already finished; b) had not yet died.

I haven't yet worked out how to add "and tell me who out of ?pres was oldest on ?day", though... Andrew Gray (talk) 21:29, 19 June 2020 (UTC)[reply]

SELECT ?day ?pres ?presLabel ?age WHERE 
{
 { SELECT distinct ?day WHERE {
  { ?m wdt:P31 wd:Q5 . ?m p:P39 ?st . ?st ps:P39 wd:Q11696 . ?m wdt:P570 ?day } union
  { ?m wdt:P31 wd:Q5 . ?m p:P39 ?st . ?st ps:P39 wd:Q11696 . ?st pq:P580 ?day } union
  { ?m wdt:P31 wd:Q5 . ?m p:P39 ?st . ?st ps:P39 wd:Q11696 . ?st pq:P582 ?day }  
 } }
  optional {
    ?pres wdt:P31 wd:Q5 . ?pres p:P39 ?st . ?st ps:P39 wd:Q11696 . ?st pq:P582 ?end .
    optional { ?pres wdt:P570 ?died } . filter (COALESCE(?died, NOW()) > ?day ) . filter (?day >= ?end ) .
    ?pres wdt:P569 ?born . bind((?day - ?born) as ?age) 
  }
  filter not exists {
    ?pres2 wdt:P31 wd:Q5 . ?pres2 p:P39 ?st2 . ?st2 ps:P39 wd:Q11696 . ?st2 pq:P582 ?end2 . filter (?day >= ?end2 ) .
    ?pres2 wdt:P569 ?born2 . bind((?day - ?born2) as ?age2) . filter( ?age2 < ?age )
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?day

Try it!

Update - this one gets you the youngest living ex-President on every change day. Which is not exactly what we wanted, but we're almost there! Andrew Gray (talk) 21:50, 19 June 2020 (UTC)[reply]

#Former US presidents/vice-presidents, current age or age at death; years alive after last time an office held
#includes incumbents with current date
#by Jura1, 2020-06-20

SELECT ?person ?personLabel (MAX(?end_or_now) as ?last_date)  (MAX(?age_end_or_now) as ?age_at_date)  ?dob ?dod ?when ?age   (?when - YEAR(?last_date)  as ?yearsalivesince)
{
  VALUES ?office {  wd:Q11699 wd:Q11696 }
  ?st ps:P39 ?office .
  ?person wdt:P31 wd:Q5; p:P39 ?st . 
  OPTIONAL { ?st pq:P582 ?e } 
  BIND( COALESCE(?e, NOW() ) as ?end_or_now) 
  ?person wdt:P569 ?dob .
  OPTIONAL { ?person wdt:P570 ?dod } .
  BIND( COALESCE( YEAR( ?dod) , YEAR(NOW()) ) as ?when )  
  BIND( ?when - YEAR(?dob) as ?age) 
  BIND(YEAR(?end_or_now)-YEAR(?dob) as ?age_end_or_now)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?person ?personLabel ?dob ?dod ?age ?when
ORDER BY DESC(?age_at_date)

Try it!

Another variation. --- Jura 22:11, 19 June 2020 (UTC)[reply]

@Oravrattas: Here is a complete list of the oldest living expresidents of USA for each date of potential changes (dates of end of presidency and dates of death). It correctly includes periods with no living expresident.

SELECT DISTINCT ?date ?age ?president ?presidentLabel
WITH
{
  # persons who is/were president of USA
  SELECT ?president ?president_enddate ?president_birthdate ?president_deathdate
  WHERE
  {
    ?president wdt:P31 wd:Q5.
    ?president p:P39 ?president_statement.
    ?president_statement ps:P39 wd:Q11696.
    ?president_statement pq:P582 ?president_enddate.
    ?president wdt:P569 ?president_birthdate.
    OPTIONAL { ?president wdt:P570 ?president_deathdate. }
  }
} AS %presidents
WITH
{
  # Dates where who is oldest living expresident can change
  SELECT DISTINCT ?date
  WHERE
  {
    {
      INCLUDE %presidents
      BIND(?president_enddate + "P1D"^^xsd:duration AS ?date)
    }
    UNION
    {
      INCLUDE %presidents
      BIND(?president_deathdate + "P1D"^^xsd:duration AS ?date)
    }
  }
} AS %dates
WITH
{
  # Find the age for all living expresidents for each value of ?date
  SELECT ?date ?age ?president
  WHERE
  {
    INCLUDE %dates
    INCLUDE %presidents
    FILTER (?president_enddate < ?date) # Must have become expresident
    FILTER IF(BOUND(?president_deathdate), ?president_deathdate >= ?date, true) # Must still be living
    BIND (?date - ?president_birthdate AS ?age)
  }
} AS %ages
WITH
{
  # Find the oldest age for a living exprrsident for each value of ?date
  SELECT ?date (MAX(?age) AS ?oldest)
  WHERE
  {
    INCLUDE %ages
  }
  GROUP BY ?date
} AS %oldest
WHERE
{
  INCLUDE %dates
  OPTIONAL
  {
    INCLUDE %ages
    INCLUDE %oldest
    FILTER (?age = ?oldest)
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?date

Try it!

--Dipsacus fullonum (talk) 22:17, 19 June 2020 (UTC)[reply]

@Dipsacus fullonum: oh wow! That's vaguely the direction I was trying to go in, but I couldn't find any way to actually get even close to there. Thank you! (The "P1D"^^xsd:duration formulation is also a new one to me — that's rather nice, and I can think of other places that could come in handy.) Our results for both President and Vice-President exactly match those from the original blogpost (other than the one day difference everywhere), which is a useful check both for our data and his calculations. And I think this could be an interesting example query for key offices in lots of other countries. But now I'm wondering if this as far as we can get towards replicating a more condensed version for presentational value. For example, is there any way of removing the entries where there's a change in ?date, but not in ?president? I made a vague stab at adding another level of indirection around this to try to get at those, but then realised that a simple GROUP BY / MIN approach won't work because of people who held this distinction more than once (e.g. John Quincy Adams becoming the oldest living ex-president for the second time after the death of Andrew Jackson), and the periods with no living ex-president could also be tricky. So I'm not sure if this is just one of those things that's not really possible from SPARQL, or if I just can't think of a possible approach. --Oravrattas (talk) 07:49, 20 June 2020 (UTC)[reply]

@Oravrattas: It is certainly possible with SPARQL. You just need to figure out a usable way to do it. Here is my way:

SELECT DISTINCT ?date ?age ?president ?presidentLabel
WITH
{
  # Persons who is/were president of USA
  SELECT ?president ?presidentLabel ?president_enddate ?president_birthdate ?president_deathdate
  WHERE
  {
    ?president wdt:P31 wd:Q5.
    ?president p:P39 ?president_statement.
    ?president_statement ps:P39 wd:Q11696.
    ?president_statement pq:P582 ?president_enddate.
    ?president wdt:P569 ?president_birthdate.
    OPTIONAL { ?president wdt:P570 ?president_deathdate. }
    ?president rdfs:label ?presidentLabel.
    FILTER (LANG(?presidentLabel) = "en")
  }
} AS %presidents
WITH
{
  # Dates where who is oldest living expresident can change
  SELECT DISTINCT ?date
  WHERE
  {
    {
      INCLUDE %presidents
      BIND(?president_enddate + "P1D"^^xsd:duration AS ?date)
    }
    UNION
    {
      INCLUDE %presidents
      BIND(?president_deathdate + "P1D"^^xsd:duration AS ?date)
    }
  }
} AS %dates
WITH
{
  # Find the age for all living expresidents for each value of ?date
  SELECT ?date ?age ?president ?presidentLabel ?president_birthdate
  WHERE
  {
    INCLUDE %dates
    INCLUDE %presidents
    FILTER (?president_enddate < ?date) # Must have become expresident
    FILTER IF(BOUND(?president_deathdate), ?president_deathdate >= ?date, true) # Must still be living
    BIND (?date - ?president_birthdate AS ?age)
  }
} AS %ages
WITH
{
  # Find the oldest age for a living expresident for each value of ?date
  SELECT ?date (MAX(?age) AS ?oldest)
  WHERE
  {
    INCLUDE %ages
  }
  GROUP BY ?date
} AS %oldest
WITH
{
  SELECT DISTINCT ?date ?age ?president ?presidentLabel ?president_birthdate
  WHERE
  {
    INCLUDE %dates
    OPTIONAL
    {
      INCLUDE %ages
      INCLUDE %oldest
      FILTER (?age = ?oldest)
    }
  }
} AS %result
WHERE
{
  # A result is a change from the previous date if ?president's presidency ended the day before ?date,
  # or if an older expresident died the day before ?date. Filter out results that don't fulfill this.
  INCLUDE %result
  BIND (?date - "P1D"^^xsd:duration AS ?day_before)
  FILTER
  (
    ! BOUND(?president) ||
    EXISTS
    {
      ?president p:P39 ?president_statement.
      ?president_statement ps:P39 wd:Q11696.
      ?president_statement pq:P582 ?day_before.
    } ||
    EXISTS
    {
      ?other_president wdt:P31 wd:Q5.
      ?other_president p:P39 ?president_statement.
      ?president_statement ps:P39 wd:Q11696.
      ?president_statement pq:P582 ?other_president_end.
      ?other_president wdt:P569 ?other_president_birthdate.
      ?other_president wdt:P570 ?day_before.
      FILTER (?other_president_birthdate < ?president_birthdate) # Other president is oldest
      FILTER (?other_president_end < ?day_before) # other president died as expresident
    }
  )
}
ORDER BY ?date

Try it!

PS. Yes, the xsd:duration type can be handy at times. You can express any duration in using years, months, days, hours, minutes and/or seconds. Your favorite search engine shouls be able to find details. --Dipsacus fullonum (talk) 00:16, 23 June 2020 (UTC)[reply]

@Dipsacus fullonum: That is a spectacular piece of work! Thank you! --Oravrattas (talk) 10:26, 23 June 2020 (UTC)[reply]

Error in Query? edit

I am not sure if this is the right place, but when I execute the following query

SELECT  ?item ?itemLabel
{
  ?item wdt:P31 wd:Q70208 . 
}

Try it!

I got "3306 results in 126 ms " and I found that at least the following items were missing: Essert-Pittet (Q68715) Forel-sur-Lucens (Q52605) Ferlens (Q68729) Sarzens (Q68677) Brenles (Q52284) Mézières (Q68921) Corcelles-sur-Chavornay (Q57105) Cremin (Q57119) Bussy (Q68595) Chesalles-sur-Moudon (Q52747) Villeneuve (Q68583) even though they all clearly are "instance of" municipality of Switzerland (Q70208). What am I doing wrong? Is this an error in the query or an error in the SPARQL endpoint? Is there some heuristics to the SPARQL result and it is expected that I do not get all the results? Best --Hannes Röst (talk) 16:24, 24 June 2020 (UTC)[reply]

@Hannes Röst: the result is exactly what you asked for. The subtelty is that you ask for item with instance of (P31) municipality of Switzerland (Q70208) as the best rank and for your counter-examples, this value is not the best rank. If you want all the value regardless of the rank, you should use the prefix p: and not wdt, like this :

SELECT ?item
{
  ?item p:P31/ps:P31 wd:Q70208 . 
}

Try it!

Cheers, VIGNERON (talk) 17:23, 24 June 2020 (UTC)[reply]

Ah ok, that explains it! Thanks a lot. That makes a difference of 58 items. I think this leaves the question whether it makes sense for Cremin (Q57119) for example to have preferred rank locality (Q3257686) and normal rank municipality of Switzerland (Q70208) - why should one be more important than the other? Is there a consensus regarding the use of "Preferred rank" for instance of (P31)? Should this be used if there is a clear primary meaning of an item? Does this help external tools to reason about the item? Best regards --Hannes Röst (talk) 17:38, 24 June 2020 (UTC)[reply]

@Hannes Röst: I personally don't think it is a good idea to use preferred rank in statements with P31, and it doesn't seem to be supported by the brief help page at Help:Ranking. But that is off topic here. I suggest instead to start a discussing at e.g. Wikidata:Project chat. --Dipsacus fullonum (talk) 17:55, 24 June 2020 (UTC)[reply]

@Hannes Röst, Dipsacus fullonum: this is going off-topic but to clarify, it seems the situation is very close to what is described Help:Ranking, the "current" value has the preferred (AFAIK, all the differences are former communes) and the same is done on many communes in other countries. Cheers, VIGNERON (talk) 19:01, 24 June 2020 (UTC)[reply]

See here for discussion on Project Chat for the very general discussion. However, it clearly doesnt make sense to have some "preferred" statements in former communes and not in others. --Hannes Röst (talk) 19:08, 24 June 2020 (UTC)[reply]

It's not just all values regardles of rank. If there are multiple normal ranked statements and no preferred ranked statement via wdt you still get only one statement (the first). ChristianKl ❪✉❫ 19:15, 24 June 2020 (UTC)[reply]

You mean if there are multiple statements of municipality of Switzerland (Q70208), then wdt would only give me the first one? --Hannes Röst (talk) 19:29, 24 June 2020 (UTC)[reply]

@Hannes Röst, ChristianKl: If ChristianKl means that, then he is mistaken. The meaning of the prefix is described in mw:Wikibase/Indexing/RDF Dump Format#Truthy statements from where I quote: (start quote)

Truthy statements

Truthy statements represent statements that have the best non-deprecated rank for given property. Namely, if there is a preferred statement for property P2, then only preferred statements for P2 will be considered truthy. Otherwise, all normal-rank statements for P2 are considered truthy.

Truthy statement predicates have prefix wdt: with the property name (e.g. wdt:P2) and the object is the simple value (see below) for the statement. The qualifiers are ignored.

(end quote)

Note the use of "statements" in plural form in the description. --Dipsacus fullonum (talk) 01:22, 25 June 2020 (UTC)[reply]

Yes, I misremembered it. At the same time there are usecases where for example Wikipedia Infoboxes expect to get a single value for an answer and it's desireable when there's a clear and predictable answer. ChristianKl ❪✉❫ 10:35, 25 June 2020 (UTC)[reply]

Find any kind of religious building in a region edit

Dear fellows, I don't remember how to find list of any kind of religious building (Q44613, Q44539, Q34627, etc) within a region (Q1443) so not in an specific administrative unit. Thanks for your help. Luckyz (talk) 06:43, 25 June 2020 (UTC)[reply]

@Luckyz:, something like this:

SELECT * WHERE {
  ?q wdt:P31/wdt:P279* wd:Q24398318 .
  ?q wdt:P131*/wdt:P276* wd:Q1443 .
}

Try it!

I'm not sure how the data is structured exactly in this case, some results may or may not be missing.

Cheers, VIGNERON (talk) 08:10, 25 June 2020 (UTC)[reply]

@VIGNERON:This query is super-useful. But would it be possible to do it with coordinate location instead of with located in the administrative entity? Thanks Luckyz (talk) 10:36, 12 July 2020 (UTC)[reply]

Most frequent date "happened-on" edit

Hello, I'd like to query the most common point in time (P585) (xx/xx/xxxx is the most frequent within P585) ? Unuseful but curious about the result. Bouzinac (talk) 16:39, 25 June 2020 (UTC)[reply]

SELECT 	?value (COUNT(DISTINCT(?item)) as ?ct)
WHERE
{
    ?item wdt:P585 ?value
}
GROUP BY ?value
ORDER BY DESC(?ct)
LIMIT 30

Try it!

If you just want main statements, it's 1 January 2014 (=year 2014). --- Jura 16:46, 25 June 2020 (UTC)[reply]

@Bouzinac: If you count all ranks and all time precisions, you can use this query:

SELECT ?time (COUNT(?time) AS ?count)
WHERE
{
  [] ps:P585 ?time.
}
GROUP BY ?time
ORDER BY DESC(?count)
LIMIT 1

Try it!

Result is 1 January 2014

If you only consider time values with date precision but again all ranks, you can use this query:

SELECT ?time (COUNT(?time) AS ?count)
WHERE
{
  [] psv:P585 ?fullvalue.
  ?fullvalue wikibase:timePrecision 11 . # Precision is date
  ?fullvalue wikibase:timeValue ?time.
}
GROUP BY ?time
ORDER BY DESC(?count)
LIMIT 1

Try it!

Result is: 31 December 1890 --Dipsacus fullonum (talk) 16:52, 25 June 2020 (UTC)[reply]

@Bouzinac: My answer above only use P585 used as statement values. To also include use in qualifiers and references use ps:P585 | pq:P585 | pr:P585 or [] psv:P585 | pqv:P585 | prv:P585:

SELECT ?time (COUNT(?time) AS ?count)
WHERE
{
  [] ps:P585 | pq:P585 | pr:P585 ?time.
}
GROUP BY ?time
ORDER BY DESC(?count)
LIMIT 1

Try it!

Result is 1 January 2011

SELECT ?time (COUNT(?time) AS ?count)
WHERE
{
  [] psv:P585 | pqv:P585 | prv:P585 ?fullvalue.
  ?fullvalue wikibase:timePrecision 11 . # Precision is date
  ?fullvalue wikibase:timeValue ?time.
}
GROUP BY ?time
ORDER BY DESC(?count)
LIMIT 1

Try it!

Result is 1 January 2017. --Dipsacus fullonum (talk) 17:02, 25 June 2020 (UTC)[reply]

Hello

Jura1 and

Dipsacus fullonum I picked that query

SELECT ?time (COUNT(?time) AS ?count)
WHERE
{
  [] psv:P585 ?fullvalue.
  ?fullvalue wikibase:timePrecision 11 . # Precision is date
  ?fullvalue wikibase:timeValue ?time.
}
GROUP BY ?time
ORDER BY DESC(?count)

Try it!

without the LIMIT filter and built that viz. Seems the 31 December 1890 date need to be checked, strange that this date is out of the trend. Also, happened too to find strange dates very out of the usual range.

dataviz Bouzinac (talk) 20:24, 25 June 2020 (UTC)[reply]

Apparently, at a quick glance, the 31dec1890 looks to have many population recensement in Austria-Ungarn... https://w.wiki/VD2

Number of uses of a list of properties edit

Hi all!

This

SELECT ?id ?idLabel ?cod
WHERE {
  ?id wdt:P31 wd:Q55586529 ;
      p:P1552 [ ps:P1552 wd:Q26921380; pq:P3295 ?cod ] .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?cod

Try it!

is a list of properties being Wikidata property for authority control by VIAF member (Q55586529), while this

SELECT ?items_with_property ?uses_of_property
WHERE {
  SELECT
 (wikibase:statementProperty as ?use)
 (COUNT(DISTINCT(?item)) as ?items_with_property)
 (COUNT(?value) as ?uses_of_property)
 WHERE { ?item p:P227 ?value }
}

Try it!

is a count of the items having GND ID (P227) and of the number of values of GND ID (P227) as main value with best rank.

How about merging the two queries, apart from the risk of timing out?

I have tried

SELECT ?id ?idLabel ?cod ?items_with_property ?uses_of_property
WHERE {
  ?id wdt:P31 wd:Q55586529 ;
      p:P1552 [ ps:P1552 wd:Q26921380; pq:P3295 ?cod ] .
 { SELECT
 (wikibase:statementProperty as ?use)
 (COUNT(DISTINCT(?item)) as ?items_with_property)
 (COUNT(?value) as ?uses_of_property)
 WHERE { ?item ?id ?value } }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?cod

Try it!

but it obviously doesn't work because (I guess) results of the variable "?id" are in the form "wd:PX" instead of in the required form "p:PX", but I don't know how to do the conversion. Note: I would like having the results distinguished by ID, so one ID per line with items_with_property and uses_of_property, not having the cumulative results of all IDs. Ideas? --Epìdosis 22:58, 29 June 2020 (UTC)[reply]

@Epìdosis: You convert from wd: to p: form with the wikibase:claim predicate. Besides you need to move the finding of ?id values into the inner subquery so the result is in scope there, and add a GROUP BY, giving this query:

SELECT ?id ?idLabel ?cod ?items_with_property ?uses_of_property
WHERE
{
  {
    SELECT ?id ?cod
      (COUNT(DISTINCT(?item)) as ?items_with_property)
      (COUNT(?value) as ?uses_of_property)
    WHERE
    {
      ?id wdt:P31 wd:Q55586529 ;
        p:P1552 [ ps:P1552 wd:Q26921380; pq:P3295 ?cod ] .
      ?id wikibase:claim ?claim.
      ?item ?claim ?value.
    }
    GROUP BY ?id ?cod
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?cod

Try it!

--Dipsacus fullonum (talk) 08:59, 30 June 2020 (UTC)[reply]

@Dipsacus fullonum: Splendid! I've copied it to Wikidata:VIAF/partner, it will be very useful! --Epìdosis 10:24, 30 June 2020 (UTC)[reply]

You could do P227={{Property uses|227}}, etc for P227=2780367

. --- Jura 11:02, 30 June 2020 (UTC)[reply]

What about a count of uses for properties typed Wikidata property to indicate a location (Q18615777)? And Wikidata property with datatype 'time' (Q18636219) ? Bouzinac (talk) 11:25, 30 June 2020 (UTC)[reply]

Query error edit

Does someone know why

#defaultView:Map{"hide":["?geo", "?layer"]}
SELECT DISTINCT ?geo ?xLabel (SAMPLE(?x) AS ?x) (MAX(?population) AS ?population) (SAMPLE(?layer) AS ?layer) WHERE {
  ?x wdt:P31/wdt:P279* wd:Q6256 ;
  #   wdt:P30 wd:Q46 ;
     wdt:P3896 ?geo ;
     wdt:P1082 ?population.
  BIND(
    IF(?population < 100000, "<100,000",
    IF(?population < 500000, "100,000-500,000",
    IF(?population < 1000000, "500,000-1M",
    IF(?population < 2000000, "1M-2M",
    IF(?population < 5000000, "2M-5M",
    IF(?population < 10000000, "5M-10M",
    IF(?population < 20000000, "10M-20M",
    IF(?population < 40000000, "20M-40M",
    IF(?population < 60000000, "40M-60M",   
    IF(?population < 80000000, "60M-80M",
    IF(?population < 100000000, "80M-100M",
    IF(?population < 200000000, "100M-200M",
    IF(?population < 500000000, "200M-500M",
    IF(?population < 1000000000, "500M-1B",
    IF(?population > 1000000000, "1B+",   
    "")))))))))))))))
    AS ?layer).
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?geo ?xLabel
ORDER BY ?population

Try it!

causes a Server error and this one works

#defaultView:Map{"hide":["?geo", "?layer"]}
SELECT DISTINCT ?geo ?xLabel (SAMPLE(?x) AS ?x) (SAMPLE(?population) AS ?population) (SAMPLE(?layer) AS ?layer) WHERE {
  ?x wdt:P31/wdt:P279* wd:Q6256 ;
  #   wdt:P30 wd:Q46 ;
     wdt:P3896 ?geo ;
     wdt:P1082 ?population.
  BIND(
    IF(?population < 100000, "<100,000",
    IF(?population < 500000, "100,000-500,000",
    IF(?population < 1000000, "500,000-1M",
    IF(?population < 2000000, "1M-2M",
    IF(?population < 5000000, "2M-5M",
    IF(?population < 10000000, "5M-10M",
    IF(?population < 20000000, "10M-20M",
    IF(?population < 40000000, "20M-40M",
    IF(?population < 60000000, "40M-60M",   
    IF(?population < 80000000, "60M-80M",
    IF(?population < 100000000, "80M-100M",
    IF(?population < 200000000, "100M-200M",
    IF(?population < 500000000, "200M-500M",
    IF(?population < 1000000000, "500M-1B",
    IF(?population > 1000000000, "1B+",   
    "")))))))))))))))
    AS ?layer).
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?geo ?xLabel
ORDER BY ?population

Try it!

(the difference is MAX vs SAMPLE for population). This was taken from here. --Hannes Röst (talk)

@Hannes Röst: Both queries are invalid SPARQL code so question is why one of them wont give an error. It isn't allowed to choose a variable name after "AS" that is already in scope at that place so (SAMPLE(?x) AS ?x), (MAX(?population) AS ?population), (SAMPLE(?layer) AS ?layer), and (SAMPLE(?population) AS ?population) are all illegal constructs in SPARQL although it seems the engine only gives an error for MAX and not for SAMPLE. I suggest to find new variable names for either the unaggregated variables or the aggregated variables in all the cases to make the queries have correct SPARQL code. --Dipsacus fullonum (talk) 20:53, 30 June 2020 (UTC)[reply]