Wikidata:Request a query/Archive/2023/11

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

Query in Wikimedia Commons structured data

Hello, may I ask for a wikidata-query to search for all Wikimedia Files that have collection (P195) with value Swedish Theatre Helsinki Archive (Q123246077), and to return the file's properties depicts (P180) and creator (P170). Thank you! Robert (talk) 11:18, 1 November 2023 (UTC)

Actually, I realized how the query is done (https://w.wiki/7zdt), but how do I get the label from wikidata for the Q-values? Robert (talk) 11:26, 1 November 2023 (UTC)

I managed to figure it out: https://w.wiki/7zeb . The examples have become so much better lately. Thank you! Robert (talk) 11:55, 1 November 2023 (UTC)

Further development of query (narrowing, ref)

Hello! I created a query that was intended to list the elements instance of (P31):town in Hungary (Q13218690), print the value of start time (P580) specified as the qualifier for property Q13218690, and also print what stated in (P248) is in the source provided for it. In which I am asking for help on how to specify NOT to list the additional values given in P31 (so there is no need to list such as million city (Q1637706), city with municipal rights (Q587089), border city (Q902814), spa town (Q4946461), etc.), or the P248 in the source does not show at all, how can this be solved?

This is my query:

Items: town in Hungary (Q13218690)   
Properties: instance of (P31)   , stated in (P248)   , start time (P580)   

SELECT ?item ?itemLabel ?start ?st ?ref ?refLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],hu". }
  ?item wdt:P31 wd:Q13218690.
  ?item p:P31 ?statement.
  ?statement ps:P31 ?start.
  OPTIONAL { ?statement pq:P580 ?st. }
  ?item p:P31 [ prov:wasDerivedFrom ?ref ].
  OPTIONAL { ?ref pr:P248 [] . }                        
}

Try it!

(There are 345 cities in Hungary, that's how many results I want with the source I'm looking for)

Thank you in advance for your help Pallor (talk) 11:52, 3 November 2023 (UTC)

@Pallor: Somthing more like this, probably. Most P31s do not have a start time or a reference. I leave you to work out why 349 rows are returned; duplicate start date or refs, or items that are not towns in the sense you expect having been given the town value, will be the cause.

SELECT distinct ?item ?itemLabel ?start ?ref ?refLabel  WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],hu". }
  ?item p:P31 ?statement .
  ?statement ps:P31 wd:Q13218690.
  OPTIONAL { ?statement pq:P580 ?start. }
  OPTIONAL { ?statement prov:wasDerivedFrom/pr:P248 ?ref . }                        
}

Try it!

--Tagishsimon (talk) 13:42, 3 November 2023 (UTC)

Tagishsimon: very thanks! Pallor (talk) 15:51, 3 November 2023 (UTC)

Randomly (?) working query

This query works fine to research data on people such as Cristiano Ronaldo, Angela Merkel or Alice Cooper, but it doesn't for other people (e.g. finds nothing for Eileen Bennett, finds Serena William when searching for Bob Ross). Can anybody fix it and help me understand the problem?

SELECT DISTINCT ?item ?itemLabel ?genderLabel ?giorno_nascita ?mese_nascita ?anno_nascita ?image
   WHERE {
     ?item ?label "Cristiano Ronaldo";
           wdt:P31 wd:Q5;
           wdt:P21 ?gender;
           wdt:P569 ?dob.
     BIND(day(?dob) as ?giorno_nascita).
     BIND(month(?dob) as ?mese_nascita).
     BIND(year(?dob) as ?anno_nascita).
     
     OPTIONAL {?item wdt:P18 ?image.}
     MINUS {
       ?item p:P570 ?statement_1.
       ?statement_1 psv:P570 ?statementValue_1.
       ?statementValue_1 wikibase:timeValue ?P570_1.
     }
   SERVICE wikibase:label {bd:serviceParam wikibase:language "it"}
 }

Try it!

Aethelfirth (talk) 18:22, 29 October 2023 (UTC)

@Aethelfirth: The query is not doing what you think it's doing. ?item ?label "Cristiano Ronaldo"; is not finding items with a label of "Cristiano Ronaldo", not least since labels are monolingual strings and so in a triple would be addressed as (for an English monolingual string, such as a label) ?item ?label "Cristiano Ronaldo"@en; . The Bob Ross thing was some vandalism in the Serena Williams article - an external ID which existed as a string.

So, this works:

SELECT DISTINCT ?item ?itemLabel ?genderLabel ?giorno_nascita ?mese_nascita ?anno_nascita ?image
   WHERE {
     ?item ?label "Serena Williams"@en;
           wdt:P31 wd:Q5;
           wdt:P21 ?gender;
           wdt:P569 ?dob.
     BIND(day(?dob) as ?giorno_nascita).
     BIND(month(?dob) as ?mese_nascita).
     BIND(year(?dob) as ?anno_nascita).
     
     OPTIONAL {?item wdt:P18 ?image.}
     MINUS {
       ?item p:P570 ?statement_1.
       ?statement_1 psv:P570 ?statementValue_1.
       ?statementValue_1 wikibase:timeValue ?P570_1.
     }
   SERVICE wikibase:label {bd:serviceParam wikibase:language "it"}
 }

Try it!

More generally, ?item rdfs:label "Serena Williams"@en properly targets itemLabels - rdfs:label is the predicate for labels - whereas ?item ?label "Serena Williams" looks for any triple in her item which has a string (not monolingual string) object value of "Serena Williams". Umm. Does that help at all? --Tagishsimon (talk) 18:36, 29 October 2023 (UTC)

It's maybe also worth noting the MWAPI search approach to finding items based on a text string, fwiw ... it'll find more items, and deal with cases where, for instance, the string you're looking for is in the alias list or has an intervening middle name missing from your search argument:

SELECT DISTINCT ?item ?itemLabel 
WHERE {
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:srsearch "Serena Williams".
    ?item wikibase:apiOutputItem mwapi:title .
  }
  ?item wdt:P31 wd:Q5.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 00:43, 30 October 2023 (UTC)

Thanks @Tagishsimon, you've been greatly helpful! :D Aethelfirth (talk) 07:33, 4 November 2023 (UTC)

Schweizerisches Arbeiterhilfswerk und Solidar Suisse in de.wikipedia und Wikidata

Ich schreibe bewusst mal Deutsch: In der deutschsprachigen Wikipedia ging das durcheinander. #1 wurde nicht in #2 umbenannt, sondern beide gibt es noch. Im Artikel ist es jetzt korrekt, #2 wird auf #1 umgeleitet und dort steht im Moment beides. Meine Verschiebung hat in Wikidata Chaos angerichtet? Wenn jemand das sauber trennen kann und in beiden Artikeln (geht ja auch bei WL) die richtige Wikidata-Nummer unterbringt, kann ich das feintunen. Die Webseite hab ich gelöscht, kann aber keine neue richtige einbauen. Es ist ja keine "Weiterentwicklung", sondern war ein Fehler. Danke Wortulo (talk) 11:50, 5 November 2023 (UTC)

Maps with bicycle stand picture in Verona - Italy

I would like to make a query to return the photos containing the subject "bicycle stand", in the municipality of Verona. I would then like to visualize them on a map

Thanks Giubarba (talk) 16:30, 2 November 2023 (UTC)

Not looking very much like Commons has any.

SELECT DISTINCT ?url ?coord WHERE {
  ?file wdt:P180 wd:Q1392526 .
  ?file schema:url ?url .
  ?file wdt:P276 wd:Q2028. # location = Verona
}

Try it!

SELECT DISTINCT ?url ?coord WHERE {
  ?file wdt:P180 wd:Q1392526 .
  ?file schema:url ?url .
  OPTIONAL {?file wdt:P625 ?coord.} # can we use coords? no.
}

Try it!

--Tagishsimon (talk) 16:44, 2 November 2023 (UTC)

Thank you, works!

Does anyone know if there is a service available where you can paste this query and have a map? I found it but I don't remember it. Giubarba (talk) 16:59, 2 November 2023 (UTC)

You can get a map from WDQS and WCQS, but you need a set of data which has coordinates, and the problem here is that the bicycle rack dataset has no coordinates (at least, no P625 coords in its structured data). --Tagishsimon (talk) 17:12, 2 November 2023 (UTC)

I'm taking photos of the bike racks. Each photo has the coordinate in the "exif data". I had found an OSM-based site, where it was possible to paste a query, and all the placeholders matching that query were shown. Giubarba (talk) 17:15, 2 November 2023 (UTC)

SELECT DISTINCT ?url ?coord WHERE {

?file wdt:P180 wd:Q1392526, wd:Q2028 .

?file schema:url ?url .

} Giubarba (talk) 17:16, 2 November 2023 (UTC)

WCQS cannot report on data in EXIF. Put the depicts and coords in structured data and you might get somewhere. There are this sort of site - https://www.pic2map.com/ - but it's not designed to take a SPARQL report as input. --Tagishsimon (talk) 17:24, 2 November 2023 (UTC)

I found the tool again, I'll share it with you: https://wikishootme.toolforge.org/ Giubarba (talk) 09:11, 3 November 2023 (UTC)

Wikishootme is no help in this instance, given the current data. It can show images in Verona, but not limit the images shown to bike racks. It can limit the wikidata items it shows to bike racks, but I doubt WD has any such items. For practical purposes, if you want such a map, the only way I can see you'll get it is by putting the depicts and coords into structured data, image by image. Then WCQS will be able to draw you a map. --Tagishsimon (talk) 03:47, 6 November 2023 (UTC)

Commons structured data is kind of new, so I am not surprised you find few results. Maybe search mw:Help:CirrusSearch. The deepcat feature may be disabled on high traffic sites, I don't know. Infrastruktur (talk) 21:06, 2 November 2023 (UTC)

All dead people on Wikidata, but still are in Living people category in the article on Wikipedia

Hi, I want all dead people on Wikidata that has Property:P570 in their item page, but in the Wikipedia articles they are still listed in en:w:Category:Living people. I specifically wanted for "ckbwiki". Here is a Quarry query for this purpose, but didn't involve Wikidata. Feel free to add more columns to the fetched data. Thanks! Aram (talk) 21:12, 2 November 2023 (UTC)

This should be a trivial query with Petscan, but for the EN wikipedia, at least, the tool is incapable of running the query and exits with an error. It's not clear that anyone feels they have a responsibility to make the tool work, so it'll probably stay broken. It's a sad state of affairs. --Tagishsimon (talk) 01:29, 6 November 2023 (UTC)

@Tagishsimon Thank you for your reply! So in short, are you saying that my request is impossible? Aram (talk) 15:04, 6 November 2023 (UTC)

Statements with no qualifiers

Statements with both a P279 statement and a P2670 statement, where the P2670 statement does not have any qualifiers. Thanks! Swpb (talk) 16:24, 6 November 2023 (UTC)

SELECT DISTINCT ?item ?itemLabel 
WHERE 
{
  ?item p:P2670 ?stat .
  filter not exists {?stat ?predicate ?value . 
                    ?property wikibase:qualifier ?predicate . }
  ?item wdt:P279 []. 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
  }

Try it!

--Tagishsimon (talk) 16:35, 6 November 2023 (UTC)

Thanks! Swpb (talk) 19:31, 6 November 2023 (UTC)

How to assign a variable for property with statement?

The following query shows list of residence of a person, but how can I assign a variable for the properties of statements? I would like to find/query what properties which are associated with a statement (in this case p:P551) are available for an entity (in this case Napoleon (Q517)). Maybe is there a way to FILTER properties? I dont quite understand how to assign a variable when prefix exists in front of P identifier (in this case p: and ps: )

select DISTINCT ?statement ?starttime ?endtime ?city ?cityLabel 
where 
{
wd:Q517 p:P551 ?statement . 
?statement ps:P551 ?city .
OPTIONAL {?statement pq:P580 ?starttime . }
OPTIONAL {?statement pq:P582 ?endtime  . }
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } 
}
ORDER BY ?starttime

I look for syntax or technique something like below:

select DISTINCT ?property ?statement ?starttime ?endtime ?city ?cityLabel 
where
{
wd:Q517 p:?property ?statement . 
?statement ps:?property ?city .
OPTIONAL {?statement pq:P580 ?starttime . }
OPTIONAL {?statement pq:P582 ?endtime  . }
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY ?starttime

82.151.173.12 13:38, 8 November 2023 (UTC)

Not sure if this is what you are after? This shows the main value and qualifiers of a statement, based on the triples described at https://www.mediawiki.org/wiki/Wikibase/Indexing/RDF_Dump_Format#Properties

select DISTINCT ?statement ?predicate ?property ?propertyLabel ?value ?valueLabel
where 
{
  wd:Q517 p:P551 ?statement . 
  ?statement ?predicate ?value .
  {?property wikibase:statementProperty ?predicate .}
  UNION
  {?property wikibase:qualifier ?predicate .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?statement ?predicate

Try it!

--Tagishsimon (talk) 15:05, 8 November 2023 (UTC)

Thank you! More or less, that is what I was looking for. I tried to make it more generic, so that I can list all properties with statement (then filter afterwards whatever I like to focus)

select DISTINCT ?prop ?propLabel ?statement ?predicate ?property ?propertyLabel ?value ?valueLabel

where

{

wd:Q517 ?prop ?statement .

?statement ?predicate ?value .

{?property wikibase:statementProperty ?predicate .}

UNION

{?property wikibase:qualifier ?predicate .}

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

} order by ?statement ?predicate

217.68.56.204 16:59, 8 November 2023 (UTC)

What’s the furthest place you can get from a coordinate location point?

I don’t know if possible. 210.55.76.111 19:46, 5 November 2023 (UTC)

WD Geocoords

It's a good question, but not one which WDQS will be able to answer, I think. --Tagishsimon (talk) 21:39, 5 November 2023 (UTC)

Assuming you mean on the planet Earth and you're talking about the great circle distance, this is basic geometry. Take Sydney, Australia for example which is at 34S 151E. If you draw a straight line from that point straight through the center of the earth you would come out at 34N 29W or some distance outside the coast of Morocco in the North Atlantic. Infrastruktur (talk) 21:50, 5 November 2023 (UTC)

I understood the question to be, what coordinate is furthest from any other coord already on WD; not a q about antipodes. So somewhere in an ocean, probably. --Tagishsimon (talk) 21:57, 5 November 2023 (UTC)

This is what I understand as well, and it's kinda interesting indeed. Not possible with WDQS for sure, but Python with geopandas could be a tool to solve this problem. —MisterSynergy (talk) 22:00, 5 November 2023 (UTC)

I couldn't leave this alone. There is no definite answer yet and I am not sure whether I will find one, but I do already have coordinates which are more than 1000 km away from any P625 coordinate (on Earth) we have here in Wikidata. Which is more than what I had expected. —MisterSynergy (talk) 23:18, 9 November 2023 (UTC)

More than I'd have thought, too; although on examination, the oceans turn out to be quite large. --Tagishsimon (talk) 01:11, 10 November 2023 (UTC)

There seem to be a gotcha in geopandas when it comes to distances: https://stackoverflow.com/questions/63722124/get-distance-between-two-points-in-geopandas . Geopandas is based on shapely which works on carthesian coordinates, which means distance calculations will probably break pretty badly if you attempt to use this on a global scale. And GCD is pretty slow to calculate if you do this in Python, something that will be made worse if you're using a brute-force O(N^2) approach, since there are 10.5 million coordinates. Mandatory xkcd: https://xkcd.com/356/ :-) Infrastruktur (talk) 12:23, 10 November 2023 (UTC)

I am not (yet) relying on geopandas functionality. I just loaded all 10.5M coordinates into a dataframe (easy if one has done this before), and made one plot that bins coordinates. At this point, a regular pandas dataframe is sufficient. Due to the projection of the globe onto a plane this is not super useful to determine distances, but one can easily see where larger areas with no coordinates are located (mostly Eastern Pacific Ocean, Southern Ocean, Southern Atlantic Ocean, to some degree Indian Ocean). I just guessed one coordinate from one of the seemingly largest empty areas and verified with WDQS (wikibase:around) that it is actually empty with 1000 km radius.

At this point I am only interested in the order of magnitude. From here on it is more complicated, but if I continue, I would look for an algorithm based in this value, in order to reduce the number of involved coordinates as quickly as possible. —MisterSynergy (talk) 16:49, 10 November 2023 (UTC)

Even from Point Nemo (Q7886194)? --Infovarius (talk) 21:37, 10 November 2023 (UTC)

3 coordinates within 1000 km form point nemo: https://w.wiki/87R3 —MisterSynergy (talk) 21:45, 10 November 2023 (UTC)

At least two images

Hello, I was not successful in querying items that have at least 2 images (of same normal rank) and no other ranks inside their image (P18)... Bouzinac ^{💬●✒️●💛} 10:50, 12 November 2023 (UTC)

It's normally done something like this; cannot be done on the whole set of items with images (at least not in WDQS) so first select your set of items, then work out if they have at least 2 normal rank images; and exclude other ranks.

SELECT distinct ?item ?itemLabel WHERE {
  
                                           #some means of cutting down the number of items considered
  SERVICE bd:slice {
    ?item wdt:P18 [].
    bd:serviceParam bd:slice.offset 0 . # Start at item number (not to be confused with QID)
    bd:serviceParam bd:slice.limit 10000 . # List this many items
    }
  hint:Prior hint:runFirst true .
  
  ?item p:P18 ?stat .                        # has an image
  ?stat wikibase:rank wikibase:NormalRank .  # normal rank

  ?item p:P18 ?stat2 . 
  ?stat2 wikibase:rank wikibase:NormalRank . 

  filter(str(?stat) > str(?stat2) )          # image statements are different
                                             # no P18s of other ranks
  filter not exists {?item p:P18/wikibase:rank wikibase:PreferredRank . }
  filter not exists {?item p:P18/wikibase:rank wikibase:DeprecatedRank . }

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

Try it!

--Tagishsimon (talk) 21:10, 12 November 2023 (UTC)

Hi, having ?item wdt:P31 wd:Q55488; :: wdt:P17 wd:Q142. in the #some means... line gives no results, whilst I'm pretty sure there are somewhere some 2-images Bouzinac ^{💬●✒️●💛} 12:07, 13 November 2023 (UTC)

SELECT distinct ?item ?itemLabel WHERE {
  
                                           #some means of cutting down the number of items considered
  {
   ?item wdt:P31 wd:Q55488.
   ?item wdt:P17 wd:Q142. 
  }
  hint:Prior hint:runFirst true .
  
  ?item p:P18 ?stat .                        # has an image
  ?stat wikibase:rank wikibase:NormalRank .  # normal rank

  ?item p:P18 ?stat2 . 
  ?stat2 wikibase:rank wikibase:NormalRank . 

  filter(str(?stat) > str(?stat2) )          # image statements are different
                                             # no P18s of other ranks
  filter not exists {?item p:P18/wikibase:rank wikibase:PreferredRank . }
  filter not exists {?item p:P18/wikibase:rank wikibase:DeprecatedRank . }

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

Try it!

--Tagishsimon (talk) 16:22, 13 November 2023 (UTC)

Welsh

I need a query of the 100 most used properties without a label or description in Welsh. I also need the same query but for items--Trade (talk) 16:32, 13 November 2023 (UTC)

@Trade: I think this can only be done based on a sample of items; not enough time evaluate 'most used' across 100m items. So here it is for properties. (Not convinced it's possible for items, at least on WDQS.)

SELECT ?property ?propertyLabel ?count WITH {
  SELECT ?property  (count(?predicate) as ?count) WHERE {
    SERVICE bd:slice {
      ?item wdt:P31 ?w.
      bd:serviceParam bd:slice.offset 0 . # Start at item number (not to be confused with QID)
      bd:serviceParam bd:slice.limit 50000 . # List this many items
    }
  {
  ?item ?predicate ?object .
  ?property wikibase:claim ?predicate. }
  hint:Prior hint:runLast true.
} group by ?property }  as %i
WHERE
{
  INCLUDE %i
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }           
  filter not exists {?property rdfs:label ?propertyLabel_cy . filter(lang(?propertyLabel_cy)="cy") }     
  } ORDER BY DESC(?count) limit 100

Try it!

--Tagishsimon (talk) 19:55, 13 November 2023 (UTC)

Highest point per county in Finland

How do I get the highest point (in meters) per (currently existing) county in Finland?

I tried this, but I can't figure out how to limit it to just one highest point per country. Thank you for help!

SELECT ?point ?pointLabel (MAX(?height) AS ?maxheight) ?countyLabel WHERE {
  ?point wdt:P2044 ?height.
  ?point wdt:P131 ?county.
  ?county wdt:P17 wd:Q33.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fi,en". }
}GROUP BY ?point ?pointLabel ?countyLabel

Try it!

Robert (talk) 20:12, 13 November 2023 (UTC)

@Robertsilen: So the pattern here is to find the maxheight per county, and then go back and identify the point which has that height. This overcomes the difficulty of getting the ?point and the (MAX(?height) AS ?maxheight) to match up. (I've changed the variable names a little)

So. The first query finds a maximum height for each county, and dumps a list of ?county ?height rows into the second query. The second query finds a ?point which matches each ?county ?height row. It's not intuitive that you need to do this, but it turns out that you do. Obvs, problems arise if a couple of points in a ?county have the same height.

Note also that you want to use normalised heights, not compare values that might be feet / fathoms / metres &c. Height gets normalised to metres; p:P2044/psn:P2044/wikibase:quantityAmount is the property path required - see https://www.mediawiki.org/wiki/Wikibase/Indexing/RDF_Dump_Format#Normalised_values

And then finally because we are now using p:P2044 not wdt:P2044, we need to check explicitly for BestRank (i.e. for statements which would be found by wdt:)

SELECT ?point ?pointLabel ?height ?countyLabel WITH  {
  SELECT ?county (MAX(?height_) AS ?height) WHERE {
    ?point p:P2044 ?stat . 
    ?stat psn:P2044/wikibase:quantityAmount ?height_.
    ?stat a wikibase:BestRank.
    ?point wdt:P131 ?county.
    ?county wdt:P17 wd:Q33.
  } GROUP BY ?county } as %i
WHERE
{
  INCLUDE %i
  ?point p:P2044 ?stat2 . 
  ?stat2 psn:P2044/wikibase:quantityAmount ?height.
  ?stat2 a wikibase:BestRank.
  ?point wdt:P131 ?county.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fi,en". }
}

Try it!

--Tagishsimon (talk) 20:26, 13 November 2023 (UTC)

This is also, fwiw, exactly the same pattern as the "List only the highest value" higher up this page. --Tagishsimon (talk) 23:13, 13 November 2023 (UTC)

Thank you! Robert (talk) 19:29, 14 November 2023 (UTC)

Forts without a current country

I'm trying to fix instances of fort (Q1785071) which do not have a current country listed. I can get the valid country list with

 ?country wdt:P31 wd:Q3624078 . FILTER NOT EXISTS {?country wdt:P31 wd:Q3024240}

but I'm not sure how to use the list in a VALUES statement like

MINUS {?item wdt:P17 ?country. FILTER ?country VALUES {wd:Q145}}

Its acceptable for forts to have both historical and current country, but not just a historical one. Vicarage (talk) 08:28, 15 November 2023 (UTC)

@Vicarage:

SELECT ?item ?itemLabel ?country ?countryLabel
WHERE 
{
  ?item wdt:P31 wd:Q1785071 .
  OPTIONAL {?item wdt:P17 ?country.}
  MINUS {?item wdt:P17 ?new_country . ?new_country wdt:P31 wd:Q3624078 . FILTER NOT EXISTS {?new_country wdt:P31 wd:Q3024240} } 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 13:11, 15 November 2023 (UTC)

Thanks, though I had to add MINUS {VALUES ?countries {wd:Q35 wd:Q55 wd:Q1183 wd:Q21203 wd:Q25279 wd:Q31354462 wd:Q25305 wd:Q25230 wd:Q785 wd:Q26273 wd:Q26180 wd:Q25228}. ?item wdt:P17 ?countries} for all those pesky odds and sods. I've given the rest proper countries now Vicarage (talk) 15:24, 15 November 2023 (UTC)

Black-and-white Images

Hello, I don't think it's possible but I try : is it possible to query french current stations that don't have a nowadays/colorized image, such as this one https://www.wikidata.org/wiki/Q2010433#P18 ? Bouzinac ^{💬●✒️●💛} 19:53, 15 November 2023 (UTC)

Plenty of things are possible, just not very practical. If Commons kept this information machine-readable that would make things straightforward, but they don't. With around 5000 stations it should be doable to show the images in a grid and look over them manually. https://w.wiki/8ANy . If the browser starts to struggle try using offset + limit. Infrastruktur (talk) 22:37, 15 November 2023 (UTC)

It is possible for view related recent changes like Q105543609 within a language example Tamil language (Q5885) with query?

It is possible for view related recent changes like musical work/composition (Q105543609) within a language example Tamil (Q5885) with query? We can view like this one in related changes : [1] But I don't know how to include a specific language in related changes. It's possible to view in the query? Sriveenkat (talk) 21:34, 14 November 2023 (UTC)

Probably not. Remote possibility is that the MWAPI can provide something, but it's very far away from the SPARQL domain. --Tagishsimon (talk) 21:56, 14 November 2023 (UTC)

@Tagishsimon Thanks for the answering! Sriveenkat (talk) 18:56, 16 November 2023 (UTC)

Current oldid of an item?

Is it possible to get the current oldid of an item: In order to make a permanent link to a specific revision? – Shisma (talk) 09:50, 16 November 2023 (UTC)

never mind. You found a different way to do that –Shisma (talk) 12:19, 16 November 2023 (UTC)

Querying Wikipedia articles to a WD Query with SPARQL

Hello, I want to add the etymology/toponymic information stored in Wikipedia articles to their respective items mapped on the following query:

#defaultView:Map{"layer":"?linguisticFamily"}
SELECT ?item ?itemLabel ?linguisticFamily ?linguisticFamilyLabel ?coordenadas

WHERE {
  ?item wdt:P31 wd:Q2555896;
    wdt:P407 ?linguisticFamily.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  OPTIONAL { ?item wdt:P625 ?coordenadas. }
}
GROUP BY ?item ?itemLabel ?linguisticFamily ?linguisticFamilyLabel ?coordenadas

Try it!

Is it possible to show this information whenever you click an item?

Thank you! Duityors (talk) 20:52, 15 November 2023 (UTC)

It is possible to provide a link to one or more language wikipedia pages. I'm not clear whether that is what you want. SPARQL certaily cannot deliver "the etymology/toponymic information stored in Wikipedia articles". --Tagishsimon (talk) 13:13, 17 November 2023 (UTC)

Show all linked with Q5885 item except with P407 property

It's possible make a query for Show all linked items of Tamil (Q5885). Except language of work or name (P407) property. And a column show about property. Please ping me Thank you Sriveenkat (talk) 09:14, 17 November 2023 (UTC)

It's not clear to me what you actually want here. --Tagishsimon (talk) 13:14, 17 November 2023 (UTC)

Sorry for not being clear. Show all items linked with Tamil item. Do not display Tamil item associated with language of work or name (P407) (Example: language of work or name (P407): Tamil (Q5885)) Now I hope you understand what I am trying to say. Thank you Sriveenkat (talk) 05:27, 18 November 2023 (UTC)

@Sriveenkat: Thank you; I think I understand now. All items which link to Tamil, except those that link via the qualifier language of work or name. In this query ?property is the property which has a predicate with a Q5885 value. Where that predicate is a qualifier, ?sproperty shows the main statement property and its value. afaics there are no references with value Tamil (having excluded pr:P407). hth

SELECT ?item ?itemLabel ?property ?propertyLabel ?type  ?sproperty ?spropertyLabel ?value ?valueLabel WITH {
  SELECT ?statement ?type ?property ?sproperty ?value WHERE 
  {
    ?statement ?predicate wd:Q5885.
    hint:Prior hint:runFirst true.

    {?property wikibase:statementProperty ?predicate. 
    BIND("statement" as ?type) }
    UNION
    {?property wikibase:qualifier ?predicate. 
    filter not exists {wd:P407 wikibase:qualifier ?predicate. } 
    BIND("qualifier" as ?type)
    ?statement ?predicate3 ?value .
    ?sproperty wikibase:statementProperty ?predicate3 .
    }
    UNION
    {?property wikibase:reference ?predicate. 
    filter not exists {wd:P407 wikibase:reference ?predicate. } 
    BIND("reference" as ?type) 
    ?statement ?predicate3 ?value .
    ?sproperty wikibase:statementProperty ?predicate3 .
    }
  } 
}  as %i
WHERE
{
  INCLUDE %i  
  ?item ?predicate2 ?statement .
  ?property2 wikibase:claim ?predicate2. 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,ta". } 
}

Try it!

--Tagishsimon (talk) 07:53, 18 November 2023 (UTC)

@Tagishsimon exactly right thank you so much :) Sriveenkat (talk) 07:57, 18 November 2023 (UTC)

English Wikipedia articles with no version in [language]?

I'm after a query for articles with a given tag in English wiki but no article in a specified language e.g. articles tagged 'water pollution' on English wiki with no Spanish version.

Hoping @Tagishsimon might happen by soon... OAnick (talk) 17:28, 19 November 2023 (UTC)

@OAnick: This sort of thing? ?property is the predicate which takes the value 'water pollution'.

SELECT ?item ?itemLabel ?propertyLabel ?sitelink ?article
WHERE 
{
  # The set of items of interest
  ?item ?predicate wd:Q183129.
  ?property wikibase:directClaim ?predicate .
  
  # have an EN wiki sitelink
  ?article schema:about ?item ;
  schema:isPartOf <https://en.wikipedia.org/> ; 
  schema:name ?sitelink .
  
  #lack a sitelink to somewhere else
  filter not exists {?article2 schema:about ?item ;
                     schema:isPartOf <https://de.wikipedia.org/> .}
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 17:33, 19 November 2023 (UTC)

There's also this sort of thing, where the query looks at members of an EN wikipedia category for articles which do not have a counterpart on another language:

SELECT ?item ?itemLabel ?sitelink ?article
WHERE 
{
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
     bd:serviceParam wikibase:endpoint "en.wikipedia.org";
                     wikibase:api "Generator";
                     mwapi:generator "categorymembers";
                     mwapi:gcmtitle "Category:Water pollution" ;
                     mwapi:gcmprop "ids|title|type";
                     mwapi:gcmlimit "max".
     # out
     ?name wikibase:apiOutput mwapi:title.        # en-wikipedia article / category name
     ?item wikibase:apiOutputItem mwapi:item.            # wikidata QId for the person's item
    }

  ?article schema:about ?item ;
  schema:isPartOf <https://en.wikipedia.org/> ; 
  schema:name ?sitelink .
  
  filter not exists {?article2 schema:about ?item ;
  schema:isPartOf <https://de.wikipedia.org/> .}
  
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 17:38, 19 November 2023 (UTC)

MWAPI search questions

Following on from OAnick's question, above the third query should be one which free-text searches EN Wiki articles for "water pollution"; something like the below. But two questions:

1. Do I have to BIND (URI(CONCAT('https://en.wikipedia.org/wiki/', ?name)) AS ?article), or am I missing a triple within the SERVICE wikibase:mwapi clause to give me the WD item for the EN WP article?

2. Is there an mwapi:srsearch parameter which indicates I want to find the exact string "water pollution" rather than an article that has those two words, unconnected?

SELECT DISTINCT ?item ?itemLabel ?sitelink ?article
WHERE {
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "en.wikipedia.org";
                    mwapi:srsearch "water pollution".
   ?name wikibase:apiOutput mwapi:title.     
  }
  
  BIND (URI(CONCAT('https://en.wikipedia.org/wiki/', ?name)) AS ?article)

  # have an en wiki article
  ?article schema:about ?item ;
  schema:isPartOf <https://en.wikipedia.org/> ;
  schema:name ?sitelink .         
  
  #lack a sitelink to somewhere else
  filter not exists {?article2 schema:about ?item ;
                     schema:isPartOf <https://de.wikipedia.org/> .}

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

Try it!

--Tagishsimon (talk) 00:49, 20 November 2023 (UTC)

The bind clause won't work properly without URL encoding the page title. You can do BIND(STRLANG(?name, "en") AS ?sitelink) which avoids that altogether. Alternatively you can use Generator/search instead, which will return you the WB item. The latter was slow for some reason when I tried it, so I added the parameter "wikibase:limit 1000".

I seemed to get the most specific results when i put "water pollution" in quotes. Infrastruktur (talk) 23:03, 20 November 2023 (UTC)

MWAPI category question

Then, referring to the category search, below, is there a means of indicating to MWAPI that it should search the indicated category and its child categories?

SELECT ?item ?itemLabel ?sitelink ?article
WHERE 
{
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
     bd:serviceParam wikibase:endpoint "en.wikipedia.org";
                     wikibase:api "Generator";
                     mwapi:generator "categorymembers";
                     mwapi:gcmtitle "Category:Water pollution" ;
                     mwapi:gcmprop "ids|title|type";
                     mwapi:gcmlimit "max".
     # out
     ?name wikibase:apiOutput mwapi:title.        # en-wikipedia article / category name
     ?item wikibase:apiOutputItem mwapi:item.            # wikidata QId for the person's item
    }

  ?article schema:about ?item ;
  schema:isPartOf <https://en.wikipedia.org/> ; 
  schema:name ?sitelink .
  
  filter not exists {?article2 schema:about ?item ;
  schema:isPartOf <https://de.wikipedia.org/> .}
  
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 00:51, 20 November 2023 (UTC)

I have used mw:Wikidata Query Service/Categories in the past, but this is not exactly a finished product. —MisterSynergy (talk) 23:37, 20 November 2023 (UTC)

Map generation no longer working

Hello, The maps shown on Wikidata:WikiProject France/Églises are based on three queries (https://w.wiki/DNP, https://w.wiki/DPg, https://w.wiki/DPn), which have been functioning well for several years. However, they are currently experiencing consistent timeout issues. Could someone please investigate and address this issue? Ayack (talk) 11:34, 24 November 2023 (UTC)

@Ayack: I'd suggest removing the lines ?item wdt:P131+ wd:Q142. hint:Prior hint:gearing "forward". and adding in their place ?item wdt:P17 wd:Q142. By now, anything with a P131 value within France should have a P17 value for France; and if not, that's a gap that could be plugged. --Tagishsimon (talk) 12:49, 24 November 2023 (UTC)

Show All Sanskrit Lexemes forms with ISO 15919 transliteration property

I want to view all sanskrit lexeme forms with ISO 15919 transliteration (P5825). Sriveenkat (talk) 18:36, 25 November 2023 (UTC)

Show all statements of "retrieved" on the property "number of episodes" that is on entries with the property "instance of" "podcasts"

Would like to do some tidying up so would like to do the following but can't work out for to go three levels deep.

• Show the QID of
• every entry with the property "instance of" P31 with the value "podcast"
• that also has the property "number of episodes" P1113 (of any value)
• where the property "number of episodes" P1113 has the statement "retrieved" P813 (of any value)

Thank you :-) Back ache (talk) 00:24, 26 November 2023 (UTC)

@Back ache: Let's start with, I don't know what you mean by three levels deep. Then next, there is podcast (Q20899) and podcast (Q24634210) so the first of those confused me ... not sure if that needs looking into.

Then, on the face of it, the below is probably what you are after; it covers the situation of retrieved being found as a qualifier and/or a reference. I see that some podcast items use point in time (P585) rather than retrieved (P813), so I'm not sure how that figures in your plans.

Let me know if & if so, how you want this query amended.

SELECT ?item ?itemLabel ?number ?retrieved_as_qualifier ?retrieved_as_ref
WHERE 
{
  ?item wdt:P31  wd:Q24634210 . 
  ?item p:P1113 ?stat .
  ?stat ps:P1113 ?number .
  {?stat pq:P813 ?retrieved_as_qualifier.}
  UNION
  {?stat prov:wasDerivedFrom/pr:P813 ?retrieved_as_ref.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 00:50, 26 November 2023 (UTC)

@Tagishsimon: that's perfect, thankyou Back ache (talk) 02:09, 27 November 2023 (UTC)

Specifiying colours for a gender representation of Scottish witches

Hi, is it possible to use ?rgb to specify the colours of layers in a query visualisation so that gender of accused witches in Scotland is shown on a map with red markers for female and blue markers for male? Query Stinglehammer (talk) 20:36, 26 November 2023 (UTC)

@Stinglehammer: Yes. Need to deal with no gender, too:

#defaultView:Map
SELECT ?item ?witch ?ItemLabel ?Residence ?coordinate (?Gender as ?layer) ?rgb
WHERE {
  ?item wdt:P4478 ?witch;
    wdt:P551 ?residence.
  ?residence wdt:P625 ?coordinate.
  OPTIONAL 
  {
    ?item wdt:P21 ?gender .
  }

  BIND(IF(!BOUND(?gender),"00FF00",IF(CONTAINS(STR(?gender),"Q6581097"),"0000FF","FF0000")) as ?rgb)

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
    ?item rdfs:label ?ItemLabel.
    ?residence rdfs:label ?Residence .
    ?gender rdfs:label ?Gender.
  }
}

Try it!

--Tagishsimon (talk) 21:24, 26 November 2023 (UTC)

How to remove duplicate query results (based on the values of the results)?

Sorry, if this is a too basic question of SPARQL, but how to remove the duplicate of Maria Callas (in results) who has two different images. I would like to remove the 2nd one. Many thanks!

select DISTINCT ?entity ?entityLabel ?starttime ?endtime  ?image
            where
            {
            ?entity wdt:P31 wd:Q5 .
            ?entity p:P551 ?statement . 
            ?statement ps:P551 wd:Q90 .
            OPTIONAL {?statement pq:P580 ?starttime . }
            OPTIONAL {?statement pq:P582 ?endtime  . }                 
            OPTIONAL { ?entity wdt:P18 ?image . }
            FILTER((?starttime <= "1977-09-01"^^xsd:dateTime && "1977-10-01"^^xsd:dateTime <= ?endtime) || ("1977-09-01"^^xsd:dateTime <= ?starttime && ?starttime <= "1977-10-01"^^xsd:dateTime) || ("1977-09-01"^^xsd:dateTime <= ?endtime && ?endtime <= "1977-10-01"^^xsd:dateTime))
              SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
            }
            ORDER BY ?starttime

Try it!

2001:610:450:40:0:0:7:19 18:49, 27 November 2023 (UTC)

You'd need to do some sort of aggregation operation on whatever column the duplication is found in; example, use of SAMPLE().

select DISTINCT ?entity ?entityLabel ?starttime ?endtime  (SAMPLE(?image_) as ?image)
            where
            {
            ?entity wdt:P31 wd:Q5 .
            ?entity p:P551 ?statement . 
            ?statement ps:P551 wd:Q90 .
            OPTIONAL {?statement pq:P580 ?starttime . }
            OPTIONAL {?statement pq:P582 ?endtime  . }                 
            OPTIONAL { ?entity wdt:P18 ?image_ . }
            FILTER((?starttime <= "1977-09-01"^^xsd:dateTime && "1977-10-01"^^xsd:dateTime <= ?endtime) || ("1977-09-01"^^xsd:dateTime <= ?starttime && ?starttime <= "1977-10-01"^^xsd:dateTime) || ("1977-09-01"^^xsd:dateTime <= ?endtime && ?endtime <= "1977-10-01"^^xsd:dateTime))
              SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
            } GROUP BY ?entity ?entityLabel ?starttime ?endtime 
            ORDER BY ?starttime

Try it!

--Tagishsimon (talk) 23:05, 27 November 2023 (UTC)

OK, thank you very much for the hint. Did your query work? I tried it out, but got query malformed: Bad aggregate 2001:610:450:40:0:0:0:1C 10:56, 28 November 2023 (UTC)

Try now. --Tagishsimon (talk) 12:50, 28 November 2023 (UTC)