Wikidata:Request a query/Archive/2022/11

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

Finding disambiguation pages and equally-labelled occupations

Sometimes, disambiguation page items (such as Coach (Q404866)) are erroneously used as property values, e.g. for occupation (P106). I wanted to find such disambiguation page items as well as valid occupations with the same label. Following Wikidata:SPARQL query service/query optimization#Searching labels this is what I have so far, but unfortunately the query times out:

SELECT ?pseudoOccupation ?realOccupation
WHERE
{
  {
    SELECT DISTINCT ?pseudoOccupation
    WHERE
    {
      ?someone wdt:P106 ?pseudoOccupation.
      ?pseudoOccupation wdt:P31 wd:Q4167410.
    }
  }
  ?pseudoOccupation rdfs:label ?label.
  BIND(CONCAT('inlabel:', ?label) AS ?search)
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint 'www.wikidata.org';
                    wikibase:api 'Generator';
                    mwapi:generator 'search';
                    mwapi:gsrsearch ?search;
                    mwapi:gsrlimit 'max'.
    ?realOccupation wikibase:apiOutputItem mwapi:title.
  }
  ?realOccupation rdfs:label ?label.
  FILTER EXISTS
  {
    VALUES ?occupation { wd:Q12737077 wd:Q28640 wd:Q4164871 }
    ?realOccupation wdt:P31 ?occupation.
  }
}

Try it!

--2A02:8108:50BF:C694:7062:2BB6:D365:3A7E 17:51, 31 October 2022 (UTC)

Yeah, a search using the API is expensive and your query is trying to do quite a few of them. I can think of two approaches to this task, one is using exact matching which is fast but unfortunately yields far fewer matches than we would like, but hey they are free so why not. The other approach is generating links to a targeted search on Wikidata.

SELECT ?pseudoOccupation ?realOccupation ?label
WHERE {
  {
    SELECT DISTINCT ?pseudoOccupation
    WHERE {
      ?someone wdt:P106 ?pseudoOccupation.
      ?pseudoOccupation wdt:P31 wd:Q4167410.
    }
  }
  ?pseudoOccupation rdfs:label ?label. FILTER (LANG(?label)="en")

  VALUES ?occupation { wd:Q12737077 wd:Q28640 wd:Q4164871 }
  ?realOccupation wdt:P31 ?occupation.
  ?realOccupation rdfs:label ?label .
}

Try it!

SELECT
  ?pseudoOccupation ?pseudoOccupationLabel
  (URI(CONCAT("https://www.wikidata.org/w/index.php?title=Special:Search&fulltext=1&ns0=1&search=inlabel:\"", STR(?pseudoOccupationLabel), "\" haswbstatement:P31=Q12737077|P31=Q28640|P31=Q4164871")) AS ?search)
WHERE {
  {
    SELECT DISTINCT ?pseudoOccupation
    WHERE {
      ?someone wdt:P106 ?pseudoOccupation.
      ?pseudoOccupation wdt:P31 wd:Q4167410.
    }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

HTH. Infrastruktur (talk) 06:42, 1 November 2022 (UTC)

Hmm… Both approaches have their pros and cons. I’ll see how far they get me. At the worst, I’d have to split the query and do the searches manually. Thanks, anyway! --2A02:8108:50BF:C694:658F:7048:504A:F96A 09:41, 1 November 2022 (UTC)

Land Borders of Current Countries

I've created a query for current (i.e., non-historic) countries' land borders and want to sort them in descending order of length. The query kind of works.

1. The sorting works because I've changed the only border that was defined in metres to kilometres: Slovakia–Ukraine border (Q2630766), like the rest. I hope nobody minds, but (I guess) anyone is in their good right to define it in something other than km. I'm printing out the unit. Is there a way to unify these lengths so that the sorting desc(?len) works with arbitrary units?
2. One border appears twice in the result: Austria–Slovakia border (Q1991275). I guess that's because it contains two length values, one with a deprecated rank. How can the query be changed so that only the one non-deprecated length is being used?
3. Please comment on anything in the query that appears strange or false to you, or any suggestion that might make the query more stable.
4. From a data correctness point of view, I've found https://lithub.com/the-oldest-the-longest-the-weirdest-a-brief-history-of-land-borders/ to mention 317 land borders in the world, so subtracting one for the duplicate border from the query's current result of 318 seems to work out overall.

# land borders of current countries in descending order of length
SELECT ?item ?itemLabel ?c1Label ?c2Label ?len ?lenUnitLabel WHERE {
  ?item wdt:P31 wd:Q12413618 . # international border
  ?item wdt:P31 wd:Q15104814 . # land border

  # countries
  ?item wdt:P17 ?c1.
  ?item wdt:P17 ?c2.

  # show each border only once
  BIND (xsd:integer(substr(str(?c1), 33)) as ?c1Num).
  BIND (xsd:integer(substr(str(?c2), 33)) as ?c2Num).
  filter(?c1Num < ?c2Num )

  # border length and unit
  ?item wdt:P2043 ?len.
  ?item p:P2043 ?lenStat .
  ?lenStat psv:P2043 ?valuenode.
  optional {?valuenode wikibase:quantityUnit ?lenUnit}

  # lack of end time means current
  optional {?item wdt:P582 ?endTime}.
  filter(?c1 != ?c2 && !bound(?endTime) )
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} order by desc(?len)

Try it!

176.199.18.181 13:16, 1 November 2022 (UTC)

For #1, there's an example query here which uses normalised heights - the syntax is a little tricky but it looks like using psn: will give you a normalised length in metres regardless of what the unit in ?valuenode is.

For #2, you are getting the "best value" only using wdt:P2043 ?len, but then also getting all the statements using the p:P2043 ?lenStat approach. Two ways around this - one to explicitly filter for the rank, the other is nice and quick and I've used it here. Essentially, "the best available statement has value ?len, so make sure we are only checking for the details of the statement with value ?len". I've also added a DISTINCT to the SELECT - it's not strictly needed here since we get the same # of results without, but it's good to have it just for tidiness.

However, for point #4, the fix above actually brings us down to 314 - this may need a bit of investigating. Looking at the results, we have a triple entry for Belgium–Netherlands border (Q3088843) (the data is confused because of the way the Netherlands are modelled), and Ceuta border fence (Q2339765) seems to be a part of Morocco–Spain border (Q2748217) that has been modelled as a distinct border in its own right. So that suggests we may only have 311?

There are 22 international borders that do not have a length or an end date (query and some of these may reflect the gaps. These are a bit of a mix - ones like Central African Republic–South Sudan border (Q3088973) probably just need a figure added, ones like Polish-Czechoslovak border (Q9275831) need an end date as they're historic, and some of the others like Algeria–Western Sahara border (Q3088690) start getting into "not everyone recognises one of the countries". There's also border between Cyprus and Northern Cyprus (Q91090289) which is missing the "land border" statement as well as not having a length and the recognition question - there may be others but I haven't spotted them. I suspect that based on the recognition issue, there may not be a single clear answer. Andrew Gray (talk) 23:45, 1 November 2022 (UTC)

# land borders of current countries in descending order of length
SELECT DISTINCT ?item ?itemLabel ?c1Label ?c2Label ?len ?lenUnitLabel ?normalised WHERE {
  ?item wdt:P31 wd:Q12413618 . # international border
  ?item wdt:P31 wd:Q15104814 . # land border

  # countries
  ?item wdt:P17 ?c1.
  ?item wdt:P17 ?c2.

  # show each border only once
  BIND (xsd:integer(substr(str(?c1), 33)) as ?c1Num).
  BIND (xsd:integer(substr(str(?c2), 33)) as ?c2Num).
  filter(?c1Num < ?c2Num )

  # border length and unit
  ?item wdt:P2043 ?len.
  ?item p:P2043 ?lenStat .
  ?lenStat psv:P2043 ?valuenode.
  optional {?valuenode wikibase:quantityUnit ?lenUnit}
  
  ?lenStat psn:P2043 ?lenNorm.
  ?lenNorm wikibase:quantityAmount ?normalised .
  # get normalised length value
  
  ?lenStat ps:P2043 ?len .
  # make sure the statement we are using has the same value as the one returned by wdt, ie is best available

  # lack of end time means current
  optional {?item wdt:P582 ?endTime}.
  filter(?c1 != ?c2 && !bound(?endTime) )
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} order by desc(?len)

Try it!

Please merge this two items: Q7412155 & Q7317507

I wasn't able to merge this two items: Q7412155 & Q7317507 because they both have the default description for the categories. How! El Mono Español (talk) 20:10, 26 October 2022 (UTC)

@El Mono Español You can't merge two items with sitelinks to the same project, since an item can only have one sitelink to any given site. In this case, many projects (ar, az, cy, en, fa, ko, my, ro, tr, ur) have both a Category:Dutch art (Q7412155) and a Category:Arts in the Netherlands (Q7317507), so we need to have two separate items. Andrew Gray (talk) 23:54, 1 November 2022 (UTC)

I thought they meant the same thing 😳 Okay thany kou El Mono Español (talk) 08:46, 2 November 2022 (UTC)

Trails with some values and www with some values

• Trails are added with "some value" for P10467 --> I can filter them
• Some trails also has some values for P856. Question how do I just show www when it has a value

My test

#title: P10467 find some value
SELECT distinct ?item ?itemLabel  (sample(?www) AS ?www)
WHERE
{
  ?item wdt:P10467 ?value .      
  OPTIONAL{?item wdt:P856 ?www} # need to just show when it has a value
  FILTER (wikibase:isSomeValue(?value))
         
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,sv" }
} group by ?item ?itemLabel 
order by ?itemLabel

P10467 find some value

This query return P856 as <http://www.wikidata.org/.well-known/genid/cf5ce5379f37d2ee5b06c024efdc939b>

Question how do I avoid showing them? - Salgo60 (talk) 17:04, 2 November 2022 (UTC)

@Salgo60: The query, as I understand it, is limited to those items that have an 'unknown value' set in the P10467 statement. The URI you cite - <http://www.wikidata.org/.well-known/genid/cf5ce5379f37d2ee5b06c024efdc939b> - is the WD representation of the P10467 'unknown value', and is not the P856 value. So, not clear where you want to take this query, but I've modified it to show all such trails, making ?www optional; and I show the ?value to make it clear it is distinct from the P856 value.

#title: P10467 find some value
SELECT distinct ?item ?itemLabel ?value  (sample(?www_) AS ?www)
WHERE
{
  ?item wdt:P10467 ?value .
  FILTER (wikibase:isSomeValue(?value))

  OPTIONAL {?item wdt:P856 ?www_ .} # need to just show when it has a value
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,sv" }
} group by ?item ?itemLabel ?value order by ?itemLabel

P10467 find some value

--Tagishsimon (talk) 17:18, 2 November 2022 (UTC)

Thanks sorry being unclear. Your query returns https://www.wikidata.org/.well-known/genid/cb2952968bb653e6e3a1b21569e771f5 for Q113861565#P856 I would like not to display wikibase:isSomeValue for P856 - Salgo60 (talk) 17:32, 2 November 2022 (UTC)

@Salgo60: I think I'm with you now.

#title: P10467 find some value
SELECT distinct ?item ?itemLabel (sample(?www_) AS ?www)
WHERE
{
  ?item wdt:P10467 ?value .
  FILTER (wikibase:isSomeValue(?value))
  OPTIONAL {?item wdt:P856 ?www_ .   FILTER (!wikibase:isSomeValue(?www_)) } # need to just show when it has a value
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,sv" }
} group by ?item ?itemLabel  order by ?itemLabel

P10467 find some value

--Tagishsimon (talk) 17:43, 2 November 2022 (UTC)

Graph of databases links

Hello hello,

I want to visualise the linkage (using maintains linking to (P10568)) between video game databases and highlight the ones we link to. I got as far as the following, but the highlighting does not actually work.

Also, I’m not sure about the BIND(IF(?toggle, ?item1, ?item2) AS ?item) − when I have done such graphs before I just SELECTed both ?item1 and ?item2 ; I saw that construct in the queries example and thought I would follow.

#defaultView:Graph
SELECT DISTINCT ?item ?itemLabel ?link ?rgb 
WITH {
  SELECT DISTINCT ?item ?itemLabel ?link
  WHERE {
    VALUES ?toggle {
      "true"^^xsd:boolean
      "false"^^xsd:boolean
    }
    ?item1 (wdt:P31/(wdt:P279*)) wd:Q55341040;
      wdt:P10568 ?item2;
      rdfs:label ?item1Label.
    ?item2 rdfs:label ?item2Label.
    FILTER((LANG(?itemLabel)) = "en")
    FILTER((LANG(?item2Label)) = "en")
    # I saw that in the examples, no idea whether that’s really necessary instead of SELECTing ?item1 and ?item2
    BIND(IF(?toggle, ?item1, ?item2) AS ?item)
    BIND(IF(?toggle, ?item1Label, ?item2Label) AS ?itemLabel)
    BIND(IF(?toggle, "", ?item1) AS ?link)
  }
} AS %items
WHERE {
  INCLUDE %items
  OPTIONAL {
    ?item wdt:P1687 ?property.
  }
  # The idea is to highlight DBs linked from Wikidata, but that does not seem to work
  BIND(IF(BOUND(?property),"FFA500","") AS ?rgb).
}

Try it!

Jean-Fred (talk) 13:47, 31 October 2022 (UTC)

First, and with all due respect, @Jean-Frédéric:, a pox on the 'graph' visualisation in WDQS. It's horrible & unbearable. YMMV.

With that out of the way, I am seeing highlighting on what seem to be appropriate nodes in the graph. I've tweaked the query a little. It seems to be using an ingeneous ?toggle construction to take pairs of subject-object and swap them around, so that there is one row for the subject as ?item, one for the object as ?item. It then checks whether the ?item has a WD property associated with it, and if so, highlights it. All of that seems to be happening.

So, back to you: what are you expecting to be different; can you provide an example ?item, ?item1 r ?item2 that fails?

I've rendered my version as a table & added another label to help me understand what was going on.

SELECT DISTINCT ?item ?itemLabel ?link ?linkLabel ?rgb ?toggle
WITH {
  SELECT DISTINCT ?item ?itemLabel ?link ?toggle
  WHERE {
    VALUES ?toggle {
      "true"^^xsd:boolean
      "false"^^xsd:boolean
    }
    ?item1 wdt:P31/wdt:P279* wd:Q55341040;
      wdt:P10568 ?item2.

    BIND(IF(?toggle, ?item1, ?item2) AS ?item)
    BIND(IF(?toggle, "", ?item1) AS ?link)
  }
} AS %items
WHERE {
  INCLUDE %items
  OPTIONAL {
    ?item wdt:P1687 ?property.
  }
  # The idea is to highlight DBs linked from Wikidata, but that does not seem to work
  BIND(IF(BOUND(?property),"FFA500","") AS ?rgb)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 17:22, 31 October 2022 (UTC)

I sort of suspect the original query was trying to get around the issue that maintains linking to (P10568) is not symmetric and so A might link to B, but not the other way around. That makes it harder to create a graph. Infrastruktur (talk) 18:06, 31 October 2022 (UTC)

@Tagishsimon: Thanks for the answer!

• Thanks for the word of caution :) I don’t expect too much of that graph so I’ll take my chances ;)
• Right, I can just use the label SERVICE (not sure anymore why I ended up using rdfs:label so much)
• I completely neglected to mention *what* I was expecting Facepalm. So for example, there is little island c64games.de (Q81539510) → C64-Wiki (Q114419873) − only the latter is colored while both have a Wikidata property (P1687) statement.

  That said, I realise I have bigger problems:

  • The arrow goes c64games.de (Q81539510) → C64-Wiki (Q114419873), but the actual relationship is the other way around
  • C64-Wiki (Q114419873) is not supposed to be a dead-end − it has a maintains linking to (P10568) towards 6 other databases.

Jean-Fred (talk) 18:29, 31 October 2022 (UTC)

Well, to clear up just one thing at once, it seems the placement of the ?rgb in the SELECT is critical; it acts on "the item just before the rgb variable" [1], and with the below we see both c64games.de (Q81539510) and C64-Wiki (Q114419873) coloured. I have had to squint at a 'graph' to determine this. I did not enjoy it. The arrow direction and lack of onwards links we can deal with later, very likely dispensing with ?toggle.

SELECT DISTINCT ?item ?itemLabel ?rgb ?link ?linkLabel 
WITH {
  SELECT DISTINCT ?item ?itemLabel ?link ?toggle
  WHERE {
    VALUES ?toggle {
      "true"^^xsd:boolean
      "false"^^xsd:boolean
    }
    ?item1 wdt:P31/wdt:P279* wd:Q55341040;
      wdt:P10568 ?item2.

    BIND(IF(?toggle, ?item1, ?item2) AS ?item)
    BIND(IF(?toggle, "", ?item1) AS ?link)
  }
} AS %items
WHERE {
  INCLUDE %items
  OPTIONAL {
    ?item wdt:P1687 ?property.
  }
  # The idea is to highlight DBs linked from Wikidata, but that does not seem to work
  BIND(IF(BOUND(?property),"FFA500","") AS ?rgb)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 20:13, 31 October 2022 (UTC)

@Jean-Frédéric: Does this get us further:

SELECT DISTINCT ?item ?itemLabel ?rgb ?link ?linkLabel WHERE {
  {
    ?item wdt:P31/wdt:P279* wd:Q55341040; # ?item is a video games database
      wdt:P10568 ?link.                    # which is linked to ?link
  }
  UNION
  {
    ?item ^wdt:P10568 ?vgd.                 # item is linked to from ?vgd
    ?vgd wdt:P31/wdt:P279* wd:Q55341040;    # ?vgd is a video games database
    OPTIONAL {
               ?item wdt:P10568 ?link.      # item might be linked to ?link
             }
  }
    OPTIONAL {
    ?item wdt:P1687 ?property.
  }
  BIND(IF(BOUND(?property),"FFA500","") AS ?rgb)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 20:22, 31 October 2022 (UTC)

@Tagishsimon: That is definitely much farther − it looks really good. I spotted one little thing though: C64.COM (Q81447966) is not coloured, even though it does have a Wikidata property. Looking through your table, it seems that it’s because it’s only on the right-most columns (?link) and not the left-most columns (?item), as it does not have a link with P10568.

But that’s really good as it is, probably good enough for my use-case. :)

Jean-Fred (talk) 15:25, 2 November 2022 (UTC)

@Jean-Frédéric: Possibly this next hack sorts that out? It makes anything which turns up as a ?link in the second UNIONed set of statements, turn up as an ?item by dint of the third UNION, so that it can then be tested for P1687 & coloured in. You'll be better placed to tell me whether or not it has unfortunate side-effects.

SELECT DISTINCT ?item ?itemLabel ?rgb ?link ?linkLabel ?rgb2 WHERE {
  {
    ?item wdt:P31/wdt:P279* wd:Q55341040; # ?item is a video games database
      wdt:P10568 ?link.                    # which is linked to ?link
  }
  UNION
  {
    ?item ^wdt:P10568 ?vgd.                 # item is linked to from ?vgd
    ?vgd wdt:P31/wdt:P279* wd:Q55341040.    # ?vgd is a video games database
    OPTIONAL {
               ?item wdt:P10568 ?link.      # item might be linked to ?link
             }
  }
  UNION
  {
    ?item99 ^wdt:P10568 ?vgd.                 # item99 is linked to from ?vgd
    ?vgd wdt:P31/wdt:P279* wd:Q55341040.    # ?vgd is a video games database
    ?item99 wdt:P10568 ?item.      # item99 is linked to ?item
  }
  OPTIONAL {
    ?item wdt:P1687 ?property.
  }
  BIND(IF(BOUND(?property),"FFA500","") AS ?rgb)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 17:34, 2 November 2022 (UTC)

That looks perfect. Thanks Tagishsimon! Jean-Fred (talk) 09:09, 3 November 2022 (UTC)

Percentage of out of school children (P2573) in population of a country

I want to make a query like this ?outOfSchoolChildrenInACountry / ?populationOfThisCountry * 100%
Is it possible? Thank you in advance! Hopejesus55 (talk) 07:52, 3 November 2022 (UTC)

Yes, it's, You can query for values and use them for counting:

SELECT ?country ?countryLabel ?OOS {
  ?country wdt:P31 wd:Q6256;
           wdt:P1082 ?pop;
           wdt:P2573 ?outOfSchool.
  BIND(ROUND( (?outOfSchool/?pop*100) *100)/100 AS ?OOS). # *100)/100 is workaruound for two places after comma, `round` doesn't do that (?)
#   BIND((?outOfSchool/?pop*100) AS ?OOS). # no rounding 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE], en". }
}

Try it!

regards, Piastu (talk) 11:43, 3 November 2022 (UTC)

Thank you very much! Hopejesus55 (talk) 16:12, 3 November 2022 (UTC)

List all predicates available for entities of Q5

How can one create a query to get all predicates (optionally with human readable labels in English) available for all entities which are instance of Q5 (Human). I come up with the following, but time out is an issue. Thanks!

SELECT DISTINCT ?p ?propLabel WHERE {

 ?person wdt:P31 wd:Q5.
 ?person ?p ?o.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } 
 ?prop wikibase:directClaim ?p .

}

WDGO5IT (talk) 16:55, 3 November 2022 (UTC)

I come up with another approach to address this. This time I also used Person (Q215627) to include more properties. If you have an opinion, highly appreciated. Thanks!

```

prefix s: <http://www.wikidata.org/entity/statement/>

prefix p: <http://www.wikidata.org/prop/>

select ?property ?propertyLabel

where

{

?property p:P2302 ?o .

{?o pq:P2308 wd:Q5} UNION {?o pq:P2308 wd:Q215627}.

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

}

ORDER BY ?propertyLabel

``` WDGO5IT (talk) 17:29, 4 November 2022 (UTC)

@WDGO5IT: I don't think WDQS will be able to include all Q5 items, and so you're going to have to be content to look at a subset, perhaps using the slice service, as below. Next, it's not clear if you're after the incidence of properties, or of predicates. Your report asks for ?prop wikibase:directClaim ?p . and so includes only wdt: predicates.

SELECT ?property ?propertyLabel ?count WITH { 
  SELECT ?property (count(distinct ?item) as ?count)  WHERE {
    SERVICE bd:slice {
        ?item wdt:P31 wd:Q5.
    bd:serviceParam bd:slice.offset 0 . # Start at item number (not to be confused with QID)
    bd:serviceParam bd:slice.limit 80000 . # List this many items
  }
    hint:Prior hint:runFirst true .
    ?item ?predicate ?value .
    ?property wikibase:directClaim ?predicate .
  } group by ?property  } as %i
WHERE
{
  INCLUDE %i
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
  
} order by desc(?count)

Try it!

--Tagishsimon (talk) 17:54, 4 November 2022 (UTC)

A lexeme form that has exactly the given features

Lexeme forms have grammatical features. I can ask for all genitive plural forms of Croatian words like this:

SELECT ?form ?representation WHERE {
  ?lexemeId dct:language wd:Q6654;
    ontolex:lexicalForm ?form.
  ?form ontolex:representation ?representation ;
    wikibase:grammaticalFeature wd:Q146786 ;
    wikibase:grammaticalFeature wd:Q146233 .
}

Try it!

But let's say I want to ask for all forms that only have a specific set of grammatical features, and nothing else. E.g. that only have exactly genitive and plural. Or that have only genitive. This one doesn't work, as it returns me all that have genitive and something else too:

SELECT ?form ?representation WHERE {
  ?lexemeId dct:language wd:Q6654;
    ontolex:lexicalForm ?form.
  ?form ontolex:representation ?representation ;
    wikibase:grammaticalFeature wd:Q146233 .
}

Try it!

So I want to get back mene (L184852-F2) (which I do) but without getting back grozdova (L684493-F9).

Any ideas? --Denny (talk) 22:10, 3 November 2022 (UTC)

@Denny: Think the normal approach is along these lines:

SELECT ?form ?representation WHERE {
  ?lexemeId dct:language wd:Q6654;
    ontolex:lexicalForm ?form.
  ?form ontolex:representation ?representation ;
    wikibase:grammaticalFeature wd:Q146233 .
  
  filter not exists {
   ?form ontolex:representation ?representation ;
    wikibase:grammaticalFeature ?unwanted.
    filter(?unwanted !=wd:Q146233 )
            }
}

Try it!

--Tagishsimon (talk) 22:23, 3 November 2022 (UTC)

That is awesome! How would I do it if I want exactly two (or more) values, i.e. how would I state the filter to be something like ?unwanted not in [wd:Q145233,wd:Q145786]? --Denny (talk) 22:28, 3 November 2022 (UTC)

@Denny: Yes, that; or just add another entire filter not exists {} for a second unwated value. --Tagishsimon (talk) 22:41, 3 November 2022 (UTC)

You must think me silly for my question, but I was literally just writing some Python-esque pseudo syntax and didn't expect it to actually work (and it doesn't, the square brackets have to be round brackets, but other than that, it works fine!). Thank you so much for your quick and comprehensive help!

For completeness, here's the query:

SELECT ?form ?representation WHERE {
::::  ?lexemeId dct:language wd:Q6654;
::::    ontolex:lexicalForm ?form.
::::  ?form ontolex:representation ?representation ;
::::    wikibase:grammaticalFeature wd:Q145786 ;
::::    wikibase:grammaticalFeature wd:Q146233 .
::::  
::::  filter not exists {
::::   ?form ontolex:representation ?representation ;
::::    wikibase:grammaticalFeature ?unwanted.
::::    filter(?unwanted not in (wd:Q145233,wd:Q145786) )
::::            }
::::}
::::

Try it!

--Denny (talk) 22:49, 3 November 2022 (UTC)

A query to find items that link to a Wikipedia project that is missing either a label or a description in one specific language

Hi! I have a request that I thought I could complete through PetScan, but no such luck!

I want to find items that are linking to nowiki, but are lacking either a description or a label in Norwegian bokmål (nb).

Is that possible? (I also want nnwiki/nn, but I'm sure I can tweak the query when I see one for Norwegian bokmål!) EdoAug (talk) 04:33, 5 November 2022 (UTC)

@EdoAug: Try this:

# items with nowiki sitelinks but no nb labels
SELECT distinct ?item 
WHERE {
  ?article schema:about ?item .
  ?article schema:isPartOf <https://no.wikipedia.org/>.
  FILTER NOT EXISTS { ?item rdfs:label ?l . filter ( lang(?l) = "nb" ) }
} limit 10

Try it!

Change rdfs:label to schema:description to get descriptions not labels. It times out without the LIMIT function. You can also combine both to eg find items that have a label but not a description:

# items with nowiki sitelinks, nb labels, no nb description
SELECT distinct ?item 
WHERE {
  ?article schema:about ?item .
  ?article schema:isPartOf <https://no.wikipedia.org/>.
  ?item rdfs:label ?l . filter ( lang(?l) = "nb" ) .
  FILTER NOT EXISTS { ?item schema:description ?l2 . filter ( lang(?l2) = "nb" ) }
} limit 10

Try it!

They all time out without the LIMIT function but it should at least give you some to work with. Andrew Gray (talk) 13:38, 5 November 2022 (UTC)

@Andrew Gray: Thank you so much! This is splendid. EdoAug (talk) 16:25, 5 November 2022 (UTC)

Gender for 541 en.wikipedia articles

Not sure if this is the correct location to request this or not, but would it be possible to get the gender (value of sex or gender (P21)) associated with the 541 people listed at w:user:Thryduulf/Sandbox? Ideally, the results would be added to the table there but if not some other format is fine as long as I can easily match to a row in that table (row numbers are fine). If the entry doesn't have a value for P21 just indicate that in some distinct way (e.g. empty cell). I don't think there are any redirects in the list, but if there are I'm interested in the target article. Semi-separately, these should all have a value for date of death (P570), so a list of any that don't would be useful for Wikidata's purposes (I should be able to add them if nobody else does before me) - putting that list wherever is convenient for you (but not my en.wp sandbox please) is fine. Thanks. Thryduulf (talk) 02:10, 6 November 2022 (UTC)

@Thryduulf This list link will give you the gender for 532 of the 541 in a format that should hopefully be really easy to line back up with the table.

(Method: generate a list of WD IDs for all linked pages using petscan, feed that into the SPARQL query using VALUES, tweak the output to get a clean page name)

Of the remaining pages, I think two explicitly have no linked WD items yet (w:Roberto Rojas and w:Burt Gustafson), and the others seem to be redirects? They may need to be checked manually - I couldn't quite figure out how to easily tweak it to find the right target items for those. Andrew Gray (talk) 10:51, 6 November 2022 (UTC)

@Thryduulf And now added to the sandbox - I filled in the missing ones manually but have tagged all the problem cases. Looks like we have six disambiguation pages (will leave these for you to confirm which pages you wanted, though I'm assuming the 2022 deaths?), 106 female, 422 male, and six animals (2F, 4M) - total 540. Looking at the table more closely it seems to have 540 lines (there is no #1) so looks like it all adds up. Andrew Gray (talk) 11:34, 6 November 2022 (UTC)

@Andrew Gray thank you, I'd forgotten to check for disambiguation pages but that is actually very useful. Thryduulf (talk) 11:40, 6 November 2022 (UTC)

@Thryduulf Glad to help. I think this method would break down a bit for a much longer list of names (I'm not quite sure where the limits to WD's VALUES clause are) but a very quick and dirty approach if you want to try it in future would be to use the petscan approach and say "return only pages whose items link to the value for 'male'" or ditto "female" (this is 'the item links to that value somewhere' - not absolutely guaranteed those values are being used for the 'gender' statement, but I would be confident that almost always they are). It won't catch redirected pages or anyone not marked as M/F, but in general should get you an overview count & list of page titles pretty easily. Andrew Gray (talk) 11:57, 6 November 2022 (UTC)

Order of results

Hi! I am trying to get countries with this query:

select ?item ?itemLabel (GROUP_CONCAT(?CountryLabel; separator=", ") AS ?Countrys) where {
  ?item wdt:P1440 "900206".
  ?item p:P1532 [ps:P1532 ?Country].

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en" .
    ?item rdfs:label ?itemLabel .
    ?Country rdfs:label ?CountryLabel           
  }
}
GROUP BY ?item ?itemLabel

Try it!

But the countries are not listed in the order they appear in the wikidata, but first with the preferred rank, then with the normal rank. Please help me make a request for the output in the order they appear on the wikidata. With gratitude Игорь Темиров (talk) 22:08, 5 November 2022 (UTC)

@Игорь Темиров: It is an interesting problem. I'm not sure to what extent we can control the way in which GROUP_CONCAT does its thing. If we can, the approach is probably as below. First, let's check what "the order they appear on the wikidata" is:

SELECT ?item ?itemLabel ?CountryLabel ?Country ?sort where {
  ?item wdt:P1440 "900206".
  ?item p:P1532 [ps:P1532 ?Country].
    BIND(xsd:integer(STRAFTER(STR(?Country),"Q")) as ?sort)
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en" .
    ?item rdfs:label ?itemLabel .
    ?Country rdfs:label ?CountryLabel           
  }
} order by ?sort

Try it!

which would seem to me to be Serbia, Yugoslavia, Serbia and Montenegro.

Next, and after some experimentation, use that in your query, using a named subquery to find and sort the data which is grouped in the final query:

select ?item ?itemLabel (GROUP_CONCAT(?CountryLabel; separator=", ") AS ?Countrys) WITH {
  SELECT ?item ?itemLabel ?CountryLabel ?sort where {
  ?item wdt:P1440 "900206".
  ?item p:P1532 [ps:P1532 ?Country].
  BIND(xsd:integer(STRAFTER(STR(?Country),"Q")) as ?sort)
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en" .
    ?item rdfs:label ?itemLabel .
    ?Country rdfs:label ?CountryLabel           
  }
} order by desc(?sort) } as %i
WHERE
{
  INCLUDE %i
}          
GROUP BY ?item ?itemLabel

Try it!

As I say, whether this approach *really* works as opposed to sometimes works, I'm unsure. --Tagishsimon (talk) 22:36, 5 November 2022 (UTC)

@Tagishsimon I think you are right that the subquery can sort the order of things in the GROUP_CONCAT - I tested alphabetical order of the labels ("Serbia, Serbia and Montenegro, Yugoslavia), or date qualifiers (using the country of citizenship (P27) countries instead, since they have start/end - "Yugoslavia, Serbia and Montenegro, Serbia"). It seems pretty reliable.

However, @Игорь Темиров asked for 'the order they appear in the wikidata', and I assumed that was the main website? The website statements aren't meaningfully ordered in any way - they just show up with the oldest at the top and the most recently added one at the bottom. The query service doesn't have access to that information so it can't sort them in the same way. (Statement IDs are random, not sequentially ordered, unfortunately). I think this is the one kind of sorting we can't do... Andrew Gray (talk) 22:53, 5 November 2022 (UTC)

(reading over that last message it might have come across a little blunt - apologies if so!)

Here's a little test of a complex GROUP_CONCAT sorting: seats represented by a couple of politicians and the times they were elected for each constituency (link), and with the party labels added into the sorted concatenated elements (link). It looks like the ordering within the CONCAT is quite reliable, even with several items involved. Andrew Gray (talk) 00:03, 6 November 2022 (UTC)

The order in https://www.wikidata.org/wiki/Q487109#P1532 ... agreed, we have no handle on that. https://www.wikidata.org/wiki/Q487109#P17 we could have used the qualifying start or end dates. --Tagishsimon (talk) 00:20, 6 November 2022 (UTC)

The ordering of multisets from subqueries is not guaranteed to be preserved, and so if you run this on a query engine other than Blazegraph it may very well not work. (ANGLES, Renzo; GUTIERREZ, Claudio. The multiset semantics of SPARQL patterns. In: International semantic web conference. Springer, Cham, 2016. p. 24.) Also «18.5.1.7 GroupConcat is a set function which performs a string concatenation across the values of an expression with a group. The order of the strings is not specified.» One of the principal authors of the spec. points out that sorting is also done after aggregation is performed, which throws another spanner in the works for a portable solution. «A: However, looking at the SPARQL 1.1 query spec, I think this is not a guaranteed result: as far as I can tell the solution modifier ORDER BY should be applied to the solution sequence _after_ grouping and aggregation, so it can not influence the order of the input for the GROUP_CONCAT. ... B: That's correct.» https://lists.w3.org/Archives/Public/public-sparql-dev/2011AprJun/0006.html Infrastruktur (talk) 01:43, 6 November 2022 (UTC)

Thanks - useful to know how that officially works! Andrew Gray (talk) 21:44, 6 November 2022 (UTC)

In some place the order of displaying parameter values on the screen must be stored, but it is not available to us, it seems. At the same time, I learned a lot of useful things for myself. Many thanks for the responses! Игорь Темиров (talk) 06:17, 6 November 2022 (UTC)

Lakes in Austria with no article at hewiki

Hi. I need a query about Lakes in Austria which doesn't have an article at hewiki. Thanks. YoavR (talk) 05:00, 6 November 2022 (UTC)

@YoavR: This should do the job:

select distinct ?lake where
{
  { ?lake wdt:P31 wd:Q23397 } union
  { ?lake wdt:P31 ?type . ?type wdt:P279+ wd:Q23397 }
  # item is a lake, or something that is a subclass of lake
  ?lake wdt:P17 wd:Q40 . 
  # lake is in Austria
  FILTER NOT EXISTS { ?article schema:about ?lake . ?article schema:isPartOf <https://he.wikipedia.org/> }
  # no hewiki article
}

Try it!

I make it 659 lakes not on hewiki (574x if you just go with "instance of : lake"). The slightly odd syntax is because "?lake wdt:P31 ?type . ?type wdt:P279* wd:Q23397" times out. Andrew Gray (talk) 12:23, 6 November 2022 (UTC)

There is another way to get rid of timeout and stick to noSoOdd syntax, do not use optimizer:

SELECT DISTINCT ?item ?itemLabel {
  hint:Query hint:optimizer "None" .
  ?item wdt:P17 wd:Q40;
        wdt:P31/wdt:P279* wd:Q23397.
  FILTER NOT EXISTS {
    ?article schema:about ?item;
             schema:isPartOf <https://he.wikipedia.org/>.
  }
  SERVICE wikibase:label {bd:serviceParam wikibase:language "[AUTO_LANGUAGE], en" .}
}

Try it!

regards, Piastu (talk) 12:50, 6 November 2022 (UTC)

Thank you both. Can you add a condition - presence of an article at enwiki? YoavR (talk) 17:28, 6 November 2022 (UTC)

@YoavR:

SELECT DISTINCT ?item ?itemLabel {
  ?item wdt:P17 wd:Q40. hint:Prior hint:runFirst true.
  ?item wdt:P31/wdt:P279* wd:Q23397. hint:Prior hint:gearing "forward".
  FILTER NOT EXISTS {
    ?article schema:about ?item;
             schema:isPartOf <https://he.wikipedia.org/>.
  }
    ?article2 schema:about ?item;
             schema:isPartOf <https://en.wikipedia.org/>.
  SERVICE wikibase:label {bd:serviceParam wikibase:language "[AUTO_LANGUAGE], en" .}
}

Try it!

--Tagishsimon (talk) 17:50, 6 November 2022 (UTC)

Museums in the historic county of Kent

I want to generate a complete list of museums (and other historical things) across the UK, and a convenient partioning is the historic county (P7959). Rather than assume every museum has that defined, I try to infer it from administrative area, but hit the problem that London has lots of museums, and London includes part of Kent, so not only do I get London museums, I get them many times. How can I modify this query to pick up historical county if present, and only deduce it if not, and filter down to one value?

SELECT ?organisation ?organisationLabel ?location ?locationLabel ?county ?countyLabel WHERE {
  ?organisation wdt:P131 ?location;
    wdt:P31 wd:Q33506.
  ?location wdt:P7959 ?county;
    wdt:P7959 wd:Q67479626.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
      MINUS {
        ?organisation p:P576 ?statement_2.
        ?statement_2 psv:P576 ?statementValue_2.
        ?statementValue_2 wikibase:timeValue ?P576_2.
      }
      MINUS {
        ?organisation p:P3999 ?statement_3.
        ?statement_3 psv:P3999 ?statementValue_3.
        ?statementValue_3 wikibase:timeValue ?P3999_2.
      }
}

Try it!

Vicarage (talk) 11:41, 7 November 2022 (UTC)

@Vicarage: Maybe this. Not all UK museums have a P131 nor a P7959 right now - legacy of the Mapping Museums import, and so the query will not pick these up. Still. BIND(COALESCE()) is your SPARQL friend.

SELECT ?organisation ?organisationLabel  ?county ?countyLabel WHERE {
   ?organisation wdt:P31 wd:Q33506.
   ?organisation wdt:P17 wd:Q145 .
   OPTIONAL {?organisation wdt:P7959 ?county1. }
   OPTIONAL {?organisation wdt:P131/wdt:P7959 ?county2. }
   BIND(COALESCE(?county1,?county2) as ?county)
   FILTER(?county IN (wd:Q67479626))
   MINUS {
   ?organisation p:P576 ?statement_2.
   ?statement_2 psv:P576 ?statementValue_2.
   ?statementValue_2 wikibase:timeValue ?P576_2.
   }
   MINUS {
   ?organisation p:P3999 ?statement_3.
   ?statement_3 psv:P3999 ?statementValue_3.
   ?statementValue_3 wikibase:timeValue ?P3999_2.
   } 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 13:30, 7 November 2022 (UTC)

Bleak (map of no P131 and no P7959). I thought there was a bot dealing with historic county. AFAIK, I do have data which would ameliorate the P131 lack; must get around to it.

#defaultView:Map
SELECT ?organisation ?organisationLabel  ?coords WHERE {
  ?organisation wdt:P31 wd:Q33506.
  ?organisation wdt:P17 wd:Q145 .
  OPTIONAL {?organisation wdt:P131 ?county1. }
  OPTIONAL {?organisation wdt:P7959 ?county2. }
  filter(!BOUND(?county1) && !BOUND(?county2))
  ?organisation wdt:P625 ?coords.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 13:39, 7 November 2022 (UTC)

Thanks for that, just what I wanted. I sighed when I saw the 1300+ omissions, and did wonder if I could use the located in the administrative territorial entity (P131) to populate them, but realising that London had multiple counties (and Bristol has 2) dissuaded me. Vicarage (talk) 13:57, 7 November 2022 (UTC)

I've added P131s for (modern day) Kent museums; will try to sort out all of them later today. --Tagishsimon (talk) 14:19, 7 November 2022 (UTC)

Ta. There is a problem with your query though, it omits Dover Museum and Bronze Age Boat Gallery (Q5302509) which is properly speced through either route, and I can't see why Vicarage (talk) 15:32, 7 November 2022 (UTC)

@Vicarage: wdt:P31/wdt:P279* cures that. More later.

SELECT DISTINCT ?organisation ?organisationLabel  ?county ?countyLabel WHERE {
   ?organisation wdt:P31/wdt:P279* wd:Q33506.
   ?organisation wdt:P17 wd:Q145 .
   OPTIONAL {?organisation wdt:P7959 ?county1. }
   OPTIONAL {?organisation wdt:P131/wdt:P7959 ?county2. }
   BIND(COALESCE(?county1,?county2) as ?county)
   FILTER(?county IN (wd:Q67479626))
   MINUS {
   ?organisation p:P576 ?statement_2.
   ?statement_2 psv:P576 ?statementValue_2.
   ?statementValue_2 wikibase:timeValue ?P576_2.
   }
   MINUS {
   ?organisation p:P3999 ?statement_3.
   ?statement_3 psv:P3999 ?statementValue_3.
   ?statementValue_3 wikibase:timeValue ?P3999_2.
   } 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 15:43, 7 November 2022 (UTC)

Lots of good work adding historic counties across the UK and categories, even with items like fortifications I was going to work on. Thanks very much Vicarage (talk) 10:07, 8 November 2022 (UTC)

@Vicarage: @Tagishsimon: I took a less elegant approach (no COALESCE) but it might be useful anyway, so posting it out of completeness - this version lets you keep the London ones but mark them up as potential problems.

SELECT distinct ?organisation ?organisationLabel ?location ?locationLabel ?tag
WHERE {
  { ?organisation wdt:P31/wdt:P279* wd:Q33506 .
    ?organisation wdt:P131 ?location .
    ?organisation wdt:P7959 wd:Q67479626 . 
    bind("Kent (stated on item)" as ?tag) .
  } # option A, it's in the historic county
  union
  { ?organisation wdt:P31/wdt:P279* wd:Q33506 .
    ?organisation wdt:P131 ?location . ?location wdt:P7959 wd:Q67479626 . 
    filter not exists { ?organisation wdt:P7959 wd:Q67479626 } . # not option A
    filter not exists { ?location wdt:P7959 ?elsewhere . filter (?elsewhere != wd:Q67479626) } # not option C
    bind("Kent (deduced via location)" as ?tag) .
  } # option B, it's in somewhere in historic Kent but not tagged as Kent itself, and no other historic county
  union
  { ?organisation wdt:P31/wdt:P279* wd:Q33506 .
    ?organisation wdt:P131 ?location . ?location wdt:P7959 wd:Q67479626 . 
    ?location wdt:P7959 ?elsewhere . filter (?elsewhere != wd:Q67479626) .
    filter not exists { ?organisation wdt:P7959 wd:Q67479626 } . # not option A
    bind("Maybe (location is somewhere which was partly in Kent)" as ?tag) 
  } # option C, it's in somewhere in historic Kent and another historic county
  
  filter not exists { ?organisation wdt:P576 ?closed }
  filter not exists { ?organisation wdt:P3999 ?closed }

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

} order by ?organisationLabel

Try it!

Andrew Gray (talk) 21:32, 7 November 2022 (UTC)

Its good to compare the 2 approaches, as it revealed a mistake in one entry. Thanks for doing that. Vicarage (talk) 10:39, 8 November 2022 (UTC)

Endemic species in NZ (Wikipedia categories vs. Wikidata items)

Hi all! I'm wanting to run a query of English language Wikipedia pages with endemic species categories, and compare this to Wikidata items (items with P183=Q664), to see if there are any species with Wikipedia categories but not Wikidata items stating that they're endemic. Then I want to do the reverse, and see which Wikidata items have P183=Q664, but don't have an endemic species category on their English-language Wikipedia pages. I'd want to do two queries for this:

• Part one: en.wikipedia.org pages containing category/text string 'Category:Endemic fauna of New Zealand', 'Category:Endemic flora of New Zealand' or 'Category:Endemic orchids of New Zealand', without P183=Q664 in their Wikidata items
• Part two: a list of wikidata items where P183=Q664, which have en.wikipedia pages, but don't have the text string 'Category:Endemic' anywhere on the page (or have any of the three categories listed above).

Is it possible to do queries looking for Wikipedia main-space categories and/or text strings in main space? If it is, I'd love to find out how to do it. --Prosperosity (talk) 05:36, 8 November 2022 (UTC)

To obtain lists of wiki pages matching complex criterias, good tools are PetScan or Pywikibot's listpages script. For your first problem here is a Petscan search for it: https://petscan.wmflabs.org/?psid=23225663 . Your second problem is not easy to solve as there is no "Endemic" category, and so finding all the relevant categories and generating a list of pages for that is going to be a PITA. You could start with this incomplete Petscan search (https://petscan.wmflabs.org/?psid=23225726) and add categories you want excluded to it. IIRC "fulltext" searches are generally too expensive to do, and the regular indexed searches doesn't have the high specificity you'd want. Infrastruktur (talk) 10:32, 8 November 2022 (UTC)

List of all current countries

As a newbie I have many questions, I'll start with a simple one. I want to have a list of all countries and start very plainly:

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

Try it!

In this, the Beiyang Government (Q814959) also comes along. This is somewhat understandable, because that too is a Sovereign (albeit historical) State {wd|Q3624078}. Why then is the Holy Roman Empire {wd|Q12548} not also included? In my opinion, the data situation is the same there. My goal, however, is a list of the current sovereign states. --Ladon (talk) 13:18, 8 November 2022 (UTC)

'All countries' is the poster-child for 'WD's data is inconsistent & difficult to work with'. In this instance, Holy Roman Empire (Q12548) has another P31 statement set to preferred rank, and so sovereign state does not respond to a wdt:P31 predicate; it would respond to p:P31/ps:P31. See https://www.mediawiki.org/wiki/Wikibase/Indexing/RDF_Dump_Format#Statement_types . There are a lot of countries, and there are a lot of bright sparks who don't appreciate rank and take it into their heads that promoting to preferred rank some or other P31 value on their country item is a good idea. My best suggestion for 'all current countries' is to look at, for instance, current members of the United Nations. --Tagishsimon (talk) 13:55, 8 November 2022 (UTC)

Thanks for help. My goal is to insert some indexes for each country --Ladon (talk) 06:43, 9 November 2022 (UTC)

Object has role

I would like to know what (and how many) items are using GND ID (P227) with the qualifier object of statement has role (P3831): cross-reference (Q1302249). Example: Q1276062#P227. Thanks in advance. --Kolja21 (talk) 04:30, 9 November 2022 (UTC)

@Kolja21: 13 out of about 1.45 million.

SELECT ?item ?itemLabel
WHERE 
{
  ?item p:P227/pq:P3831 wd:Q1302249. 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--04:41, 9 November 2022 (UTC)

Fenlands of National Importance in Switzerland

Hi, I need a query about Fenlands of National Importance in Switzerland. I wish to know the Q-number, the label and if possible the coordinates. Thank you. --Matutinho 06:03, 7 November 2022 (UTC)

@Matutinho: Doesn't seem to me WD has data supporting such a query. I find things like Federal Inventory of Fenlands of National Importance (Q108060384), Düdingermoos (Q108204717), Riedboden (Q114971756) which responded to a search for Fenlands of National Importance, but they look like thin pickings. --Tagishsimon (talk) 13:43, 7 November 2022 (UTC)

@Tagishsimon: Thank you for your answer. Yes, there are not many entries yet. A query for the Federal Inventory of Amphibian Spawning Areas of National Importance is better. There should be about 800 - 900 entries, I thhink. Can you code this query for me? If possible with Q-number, label, coordinates and image (if available). Matutinho 13:56, 7 November 2022 (UTC)

@Matutinho: Can you find an item or two which are typical of the class of items you'd like. Once again, I run into sand ... there are items on lists of Amphibian Spawning Areas, but nothing I've found that identifies a territorial item as being a Amphibian Spawning Areas. See, for instance:

• https://www.wikidata.org/wiki/Special:WhatLinksHere/Q108060386
• https://www.wikidata.org/wiki/Special:WhatLinksHere/Q109802386

--Tagishsimon (talk) 14:01, 7 November 2022 (UTC)

@Tagishsimon: Here some examples: Q108204980, Q108205085, Q108204958, Q108089861, Q108089835 and Q108086080. Perhaps there are still deficiencies in these items in Wikidata. Matutinho 14:19, 7 November 2022 (UTC)

@Matutinho: Apologies for the delay. I'm not seeing any statements in the first three of your examples which points to a Fenlands of National Importance concept. We perhaps get somewhere if we look at the P31 and P17 values - e.g. amphibian spawning area of national importance (Q109802386) and position held (P39). I also have in my head that the code CH05 in the description may point to Fenlands. Can't remember where I saw something that suggested that to me, & perhaps I'm wrong; it might be a NUTS code, who knows. Just in case:

SELECT DISTINCT ?item ?itemLabel ?coords
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q473972 . 
  ?item wdt:P17 wd:Q39.
  ?item wdt:P625 ?coords .
  ?item schema:description ?itemDescription . filter(lang(?itemDescription)="en")
  FILTER(CONTAINS(?itemDescription,"CH05"))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

So, right now, I think the possibility exists that there is a catalogue of Fenlands, but that WD items are not marked up with pointers to the catalogue code. I have zero domain knowledge here, but happy to help in whatever way I can to improve the situation. --Tagishsimon (talk) 13:08, 9 November 2022 (UTC)

@Tagishsimon: Thank you very much for your help. I think you are right. Not all elements of the Federal Inventory of Amphibian Spawning Areas of National Importance have CH05 in the label description. There is a catalogue of amphibian spawning areas. There are even two objects in Wikidata (duplicates?): Q5440266 and Q108060386. And as you say, something is missing here. Unfortunately, I lack the necessary expertise. I am trying to get helpful information on the Federal Inventory of Amphibian Spawning Areas. I'll report back here as soon as I know more. -- Matutinho 16:55, 9 November 2022 (UTC)

List of mastodon instances just by looking at existing addresses?

Is it possible to to query domains of mastodon instances just by looking at Mastodon address (P4033)? For instance gretathunberg@mastodon.nu → mastodon.nu – Loominade (talk) 10:09, 9 November 2022 (UTC)

I think I got it:

SELECT distinct ?domain WHERE {
  ?item wdt:P4033 ?address.
  BIND (replace(?address, "^@?[^@]+@", "") AS ?domain)
}

Try it!

is it good? -- Loominade (talk) 10:14, 9 November 2022 (UTC)

Survey of bibcode (P1300) items

Greetings. I'm hoping to determine where priority of work should be for adding ADS bibcode (P819). May I get a list publications with bibcode (P1300) and for each line, how many articles published in that journal, and how many published in that journal have ADS bibcode (P819). I'll start adding ADS bibcode (P819) accordingly. Many thanks for this. Trilotat (talk) 14:15, 8 November 2022 (UTC)

@Trilotat: Need to give you this as two queries; first, the eight or so largest (by number of papers) journals, and then the rest, b/c timeouts.

SELECT ?item ?itemLabel ?bibcode (count(?paper) as ?papercount) (count(?P819) as ?P819count) (?papercount - ?P819count as ?diff) WITH { 
  SELECT ?item ?itemLabel ?bibcode WHERE {
  ?item wdt:P1300  ?bibcode .
  VALUES ?item {wd:Q180445 wd:Q192864 wd:Q2261792 wd:Q2018386 wd:Q3470990 wd:Q2284414 wd:Q2128181}
  OPTIONAL{ ?item rdfs:label ?itemLabel . filter(lang(?itemLabel)="en") }
  } } as %i
WHERE
{
  INCLUDE %i
  ?paper wdt:P1433 ?item.
  optional {?paper wdt:P819 ?P819.}
} group by ?item ?itemLabel ?bibcode order by desc(?papercount)

Try it!

SELECT ?item ?itemLabel ?bibcode (count(?paper) as ?papercount) (count(?P819) as ?P819count)  (?papercount - ?P819count as ?diff) WITH { 
  SELECT ?item ?itemLabel ?bibcode WHERE {
  ?item wdt:P1300  ?bibcode .
  FILTER (?item NOT IN ( wd:Q180445, wd:Q192864, wd:Q2261792, wd:Q2018386, wd:Q3470990, wd:Q2284414, wd:Q2128181))
  OPTIONAL{ ?item rdfs:label ?itemLabel . filter(lang(?itemLabel)="en") }
  } } as %i
WHERE
{
  INCLUDE %i
  ?paper wdt:P1433 ?item.
  optional {?paper wdt:P819 ?P819.}
} group by ?item ?itemLabel ?bibcode order by desc(?papercount)

Try it!

--Tagishsimon (talk) 17:20, 8 November 2022 (UTC)

These are wonderful! Thank you so much! Trilotat (talk) 13:45, 10 November 2022 (UTC)

List of cities named after elected officials by population

Hi, I am trying to find the largest city that's named after an elected official, and I have a preliminary query that seems to work (I am very new at this):

SELECT ?cityLabel ?countryLabel ?population ?namedLabel ?officeLabel
WHERE
{
    ?city wdt:P31 wd:Q515 .
    ?city wdt:P138 ?named .
    ?city wdt:P1082 ?population .
    ?city wdt:P17 ?country .
    ?named wdt:P39 ?office .
    ?office wdt:P31 wd:Q294414
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
ORDER BY DESC(?population)

Try it!

I'm using the the property "public office" (Q294414), but I notice that Pittsburg is not on the list even though it is named after a British Prime Minister (the wikidata entries are correct). Is there something wrong with my query? Underbar dk (talk) 00:50, 10 November 2022 (UTC)

@Underbar dk: So on the Pittburgh point, the item does not have a P31=Q515 statement/value. Pittsburgh is various sorts of things, some of which are a subclass of a city. So wdt:P31/wdt:P279* - and a DISTINCT in the select - is indicated - see the SPARQL spec on property paths. The report still has duplicate rows in it b/c you are fetching P39 values for the person after whom the city was named, and some of those people have multiple P39s which are public offices; so you might want to reach for some aggregation cf. https://en.wikibooks.org/wiki/SPARQL/Aggregate_functions ?

SELECT DISTINCT ?cityLabel ?countryLabel ?population ?namedLabel ?officeLabel
WHERE
{
    ?city wdt:P31/wdt:P279* wd:Q515 .
    ?city wdt:P138 ?named .
    ?city wdt:P1082 ?population .
    ?city wdt:P17 ?country .
    ?named wdt:P39 ?office .
    ?office wdt:P31 wd:Q294414
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
ORDER BY DESC(?population)

Try it!

--Tagishsimon (talk) 01:22, 10 November 2022 (UTC)

@Tagishsimon Thanks! I think I am very close to what I need with this

SELECT ?cityLabel ?countryLabel (MAX(?pop) AS ?population) ?namedLabel (GROUP_CONCAT(distinct ?officeLabel; SEPARATOR=", ") AS ?offices)
WHERE
{
    ?city wdt:P31/wdt:P279* wd:Q515 .
    ?city wdt:P138 ?named .
    ?city wdt:P1082 ?pop .
    ?city wdt:P17 ?country .
    ?named wdt:P39 ?office .
    ?office wdt:P31/wdt:P279* wd:Q294414.
    MINUS{ ?office wdt:P31/wdt:P279* wd:Q112806846. }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en"}
  
}
GROUP BY ?cityLabel ?countryLabel ?namedLabel
ORDER BY DESC(?population)

Try it!

The only problem I have remaining is my GROUP_CONCAT returning empty results. I can't figure out why since ?officeLabel returns data. Underbar dk (talk) 04:35, 10 November 2022 (UTC)

@Underbar dk: Ah, that, yes. When aggregating label values, the report needs either to fetch the label manually (e.g. ?named rdfs:label ?namedLabel. filter(lang(?namedLabel)="en")), or needs to poke the label service into doing so, as in the example below ... and then for reasons not so clear to me, in this second approach, the label service needs to be primed for all the labels required, not merely the values that are aggregated. Fun times.

SELECT ?cityLabel ?countryLabel (MAX(?pop) AS ?population) ?namedLabel (GROUP_CONCAT(distinct ?officeLabel; SEPARATOR=", ") AS ?offices)
WHERE
{
    ?city wdt:P31/wdt:P279* wd:Q515 .
    ?city wdt:P138 ?named .
    ?city wdt:P1082 ?pop .
    ?city wdt:P17 ?country .
    ?named wdt:P39 ?office .
    ?office wdt:P31/wdt:P279* wd:Q294414.
    MINUS{ ?office wdt:P31/wdt:P279* wd:Q112806846. }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en".
                           ?office rdfs:label ?officeLabel.
                           ?city rdfs:label ?cityLabel.
                           ?country rdfs:label ?countryLabel.
                           ?named rdfs:label ?namedLabel.}
}
GROUP BY ?cityLabel ?countryLabel ?namedLabel
ORDER BY DESC(?population)

Try it!

--Tagishsimon (talk) 08:29, 10 November 2022 (UTC)

List of Countries and their population and land area in square kilometers

Hi all, I'm trying to gather land area data to use in conjunction with the NaturalEarthData geodata. Here's what I have so far:

SELECT ?country ?countryLabel ?countryArea
 WITH {
   SELECT DISTINCT *
   WHERE {
     # Instances (P31) of country (Q3624078), sovereign state (Q183366), or disputed territory (Q15634554).
     VALUES ?countryclass {wd:Q3624078 wd:Q183366 wd:Q15634554 }
     ?country wdt:P31 ?countryclass .
     # the psn: prefix to normalize the values to a common unit of area (meters, in this case)
     ?country p:P2046/psn:P2046/wikibase:quantityAmount ?countryArea .
   }
 } AS %i
 WHERE {
   INCLUDE %i
   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
 } ORDER BY ASC(?countryLabel)

Try it!

At the moment I'm happy in how I've defined a country, and am happy with the Area that it retrieves, but I've noticed some duplicate results. For example Australia has 4 rows with different areas depending on whether you're looking at the mainland or economic zone.

How can I filter the area query so that it returns the main area definition? Also one of the countries has no label. How do I filter for countries with defined labels? Mtekman (talk) 16:10, 8 November 2022 (UTC)

@Mtekman: Some of this may get you on your way. Unclear what you mean by 'main area definition', but if we presume you might want to look at the 'applies to part' qualifiers, I've amended your report so that we find the ?statement for p:P2046, and then added a commented-out clause which would ask if there was an applies to part, aspect, or form (P518) qualifier of a certain value. But I'm not convinced this is the way to go; we'd need to work out how to make that work with all the other countries that do not have that qualifier ... which is possible. So instead I've added ?statement a wikibase:BestRank. since for Australia there is one 'truthy' area value, and then a bunch of qualified values. All things being equal, it may be that the truthy value is the appropriate thing. If not, we can go back to manipulating P518, e.g. to FILTER NOT EXIST {} any P518's that were not wanted.

You can access the label of an item using ?item rdfs:label ?itemLabel. though you might want to restrict that to a single language, so ?item rdfs:label ?itemLabel. filter(lang(?itemLabel)="en") ... and then do stuff based on whether and/or what is returned by that - e.g. FILTER(BOUND(?itemLabel)) will require that the item has a label. hth.

SELECT ?country ?countryLabel ?countryArea
 WITH {
   SELECT DISTINCT *
   WHERE {
     # Instances (P31) of country (Q3624078), sovereign state (Q183366), or disputed territory (Q15634554).
     VALUES ?countryclass {wd:Q3624078 wd:Q183366 wd:Q15634554 }
     ?country wdt:P31 ?countryclass .
     # the psn: prefix to normalize the values to a common unit of area (meters, in this case)
     ?country p:P2046 ?statement . 
     ?statement psn:P2046/wikibase:quantityAmount ?countryArea .
     #?statement pq:P518 wd:Q2872203.
     ?statement a wikibase:BestRank.
   }
 } AS %i
 WHERE {
   INCLUDE %i
   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
 } ORDER BY ASC(?countryLabel)

Try it!

--Tagishsimon (talk) 17:35, 8 November 2022 (UTC)

Hi @Tagishsimon, thanks for your pointers -- they definitely got me in the right direction. So I've gotten the area that I need using your wikibase:BestRank suggestion, but now I've added the requirement of having population values as well.

I've tried the following:

SELECT DISTINCT ?country ?area_sqkm ?population {
     ## Define Country
     #- Sovereign state, territory, state with limited recognition, and country (respectively)
     VALUES ?countryclass {wd:Q3624078 wd:Q183366 wd:Q15634554 wd:Q6256} .
     ?countrystat wdt:P31 ?countryclass .
     ## Define Area
     #- and convert m² to km² with 2 decimal places
     ?countrystat p:P2046 [ a wikibase:BestRank; 
                              psn:P2046/wikibase:quantityAmount ?area_sqm ] 
                  FILTER(?area_sqm > 0) .
     BIND( ROUND(?area_sqm/10000)/100 as ?area_sqkm)
     ## Define Population
     ?countrystat p:P1082 [ a wikibase:BestRank; 
                              ps:P1082 ?population ] 
                  FILTER(?population >= 0 ) .
     ## Define the Country label
     ?countrystat rdfs:label ?country FILTER(lang(?country)="en").
     SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
ORDER BY ASC(?country)

Try it!

This appears to work well except for Costa Rica where it produces two population values. Any idea why I'm not retrieving the "best" population value here? Thanks again Mtekman (talk) 09:57, 9 November 2022 (UTC)

@Mtekman: Good progress! For Costa Rica, two population statements had preferred rank, and so both are bestrank. I've demoted one - 2017 - to normal. I guess we could as easily have looked at the point in time (P585) qualifiers for the population statement, and taken the most recent; or aggregated and used SAMPLE() to choose one of them. Unruly data is always fun. --Tagishsimon (talk) 12:53, 9 November 2022 (UTC)

@Tagishsimon: Works perfectly, thank you for changing the ranking as you did!

I have a very related query that I'm currently trying to debug related to the NaturalEarthData source I'm trying to modify, but unable to get anywhere:

SELECT ?e ?i ?iLabel ?population ?area_sqkm ?areastatement WHERE {
  {
    SELECT DISTINCT ?e ?i ?r WHERE {
      VALUES ?i {
        ## List of land areas, just three for now.
        wd:Q1781 wd:Q43296 wd:Q183
      }
      OPTIONAL { ?i owl:sameAs ?r. }
      BIND(COALESCE(?r, ?i) AS ?e)
    }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  OPTIONAL { ?e wdt:P1082 ?population. }     ## Population, if it exists
  OPTIONAL {                                 ## Area, if it exists
    ?e p:P2046 ?areastatement .
    ?areastatement a wikibase:BestRank .     ## No effect.
    ##MINUS { ?e wdt:P361 wd:Q11081619 . }   ## Filter out lagoons?
    ?areastatement ps:P2046 ?area_sqkm . 
  }
}

Try it!

The Wake Island (Q43296) entity produces two area values, one for land and one for lagoon. It looks like these properties can be accessed via the "applies to part" (Property:P518) property, but as of yet I've been unable to filter using these properties. I've stared at the RDF format image and countless examples, but unable to understand where I'm going wrong. How can one filter a property? Thanks for all your help so far Mtekman (talk) 05:44, 10 November 2022 (UTC)

@Mtekman: Good that you're staring at the RDF format image; it's exactly where you need to be. What the diagram is seeking to show is that an item can have a statement, the node of which is accessible using the p:Pxxx predicate; and then the node is linked to all sorts of stuff, such as the statement value, via ps:Pxxx, or to qualifiers, via pq:Pxxx, or to the reference node via prov:wasDerivedFrom. That being the case, ?e p:P2046 ?areastatement . makes ?areastatement the node value for the statement, and so the lagoon qualifier is ?areastatement pq:P518 wd:Q187223.. :)

SELECT ?e ?i ?iLabel ?population ?area_sqkm ?areastatement WHERE {
  {
    SELECT DISTINCT ?e ?i ?r WHERE {
      VALUES ?i {
        ## List of land areas, just three for now.
        wd:Q1781 wd:Q43296 wd:Q183
      }
      OPTIONAL { ?i owl:sameAs ?r. }
      BIND(COALESCE(?r, ?i) AS ?e)
    }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  OPTIONAL { ?e wdt:P1082 ?population. }     ## Population, if it exists
  OPTIONAL {                                 ## Area, if it exists
    ?e p:P2046 ?areastatement .
    ?areastatement a wikibase:BestRank .     ## No effect.
    MINUS { ?areastatement pq:P518 wd:Q187223 . }   ## Filter out lagoons?
    ?areastatement ps:P2046 ?area_sqkm . 
  }
}

Try it!

--Tagishsimon (talk) 08:45, 10 November 2022 (UTC)

@Tagishsimon It's working beautifully -- should I add these examples to the main example page? How are these snippets migrated normally? Mtekman (talk) 09:05, 11 November 2022 (UTC)

@Mtekman: The examples page is poorly maintained. By all means, if you wish, add this query; or not. If so, you'll need to deal with the language translation tags, a facet of WD multilingual help pages which I have yet fully to understand. (And so there is no normal, or if there is, it is that queries discussed here rarely get added to the examples page ... there's no real connection between the two pages.) I look forward to your next questions ... don't be a stranger :) --Tagishsimon (talk) 14:58, 11 November 2022 (UTC)

Will also point you to https://wdqs-tutorial.toolforge.org/index.php/simple-queries/qualifiers/statements-with-qualifiers/ ... the diagrams here seem quite good. --Tagishsimon (talk) 19:37, 10 November 2022 (UTC)

An incredible resource, thank you so much! Mtekman (talk) 09:05, 11 November 2022 (UTC)

Maxdate

@Tagishsimon Haha, I see -- well then, let me inundate you with my latest related query: I keep getting lots of population values for the same entity (due to different years), but I'm not sure how to filter for the latest Year:

SELECT DISTINCT ?i ?iLabel ?population ?area_sqkm ?popstatement ?date WHERE {
{
    SELECT DISTINCT ?e ?i ?r WHERE {
      VALUES ?i {
        ## Problematic entities with multiple population values
        wd:Q884 wd:Q31057 wd:Q35555 wd:Q220982 wd:Q31063 wd:Q475038
      }
      OPTIONAL { ?i owl:sameAs ?r. }
      BIND(COALESCE(?r, ?i) AS ?e)
    }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  OPTIONAL {                  ## Population, if it exists
      ?i p:P1082 ?popstatement. 
      ?popstatement a wikibase:BestRank .
      ?popstatement ps:P1082 ?population .
      ?popstatement pq:P585 ?date .
  }
  OPTIONAL {                  ## Area, if it exists
    ?i p:P2046 ?areastatement .
    ?areastatement a wikibase:BestRank .     
    MINUS { ?areastatement pq:P518 wd:Q187223. }     ## Filter out lagoons
    MINUS { ?areastatement pq:P518 wd:Q9259. }       ## Filter out UNESCO sites
    MINUS { ?areastatement pq:P518 wd:Q64364418. }   ## Filter out buffer zones
    MINUS { ?areastatement pq:P1012 wd:Q389717. }    ## Filter out contested Islands
    ?areastatement psn:P2046/wikibase:quantityAmount ?area_norm .
    BIND( ROUND(?area_norm/1000000) as ?area_sqkm) . 
  }
} ORDER BY ASC(?iLabel)

Try it!

Ideally I'd change the "?date" statement at the top to "(MAX(?date) as ?date)", but it doesn't seem to like that. Any thoughts? Cheers as always ;-) Mtekman (talk) 17:02, 11 November 2022 (UTC)

@Mtekman: Yes, this, too, is a common query pattern - get value X for maxdate Y ... which is to say the problem is not so much a problem of selecting MAX(?date) which depends mainly on understanding aggregation rules in SPARQL (which I understand to be, GROUP BY any variables that are not AGGREGATED in the SELECT [2]) as it is about selecting the maxdate and then finding another attached value - in this case population.

Suggested approach is to find the max date in an initial query, and then find the population value for that date in a second query. The WDQS SPARQL engine is Blazegraph, which offers so-called named subqueries facilitating this sort of approach. So perhaps start somewhere around the below. btw, I'm not sure what the OPTIONAL { ?i owl:sameAs ?r. } BIND(COALESCE(?r, ?i) AS ?e) is all about, since a) it's optional and b) you don't use its result; nor the whole SELECT DISTINCT ?e ?i ?r WHERE { ... I've done a second version, below, without these, fwiw. The Wikidata:SPARQL query service/query optimization page, which describes named subqueries, is worth bookmarking; some gems if information in it.

These sorts of questions are exactly why we're here: a sounding board for the puzzled SPARQL user; and the history of questions, discussions and solutions on this board are v.useful for those learning SPARQL, as I know from personal experience. So you are doing a public good by asking them :)

SELECT DISTINCT ?i ?iLabel ?population ?area_sqkm ?popstatement ?date 
WITH { SELECT ?i (MAX(?date_) as ?date) WHERE
  {
    {
      SELECT DISTINCT ?e ?i ?r WHERE {
        VALUES ?i {
          ## Problematic entities with multiple population values
          wd:Q884 wd:Q31057 wd:Q35555 wd:Q220982 wd:Q31063 wd:Q475038
        }
        OPTIONAL { ?i owl:sameAs ?r. }
        BIND(COALESCE(?r, ?i) AS ?e)
      } }
      OPTIONAL {                  ## Population, if it exists
      ?i p:P1082 ?popstatement. 
      ?popstatement a wikibase:BestRank .
#      ?popstatement ps:P1082 ?population .
      ?popstatement pq:P585 ?date_ .
      }
   } GROUP BY ?i } as %i
WHERE
{
  INCLUDE %i
  OPTIONAL {                  ## Population, if it exists
      ?i p:P1082 ?popstatement. 
      ?popstatement a wikibase:BestRank .
      ?popstatement ps:P1082 ?population .
      ?popstatement pq:P585 ?date .  ## <<<<< The ?date must be the same as the ?date found by MAX(?date) in the first select <<<
  }
  OPTIONAL {                  ## Area, if it exists
    ?i p:P2046 ?areastatement .
    ?areastatement a wikibase:BestRank .     
    MINUS { ?areastatement pq:P518 wd:Q187223. }     ## Filter out lagoons
    MINUS { ?areastatement pq:P518 wd:Q9259. }       ## Filter out UNESCO sites
    MINUS { ?areastatement pq:P518 wd:Q64364418. }   ## Filter out buffer zones
    MINUS { ?areastatement pq:P1012 wd:Q389717. }    ## Filter out contested Islands
    ?areastatement psn:P2046/wikibase:quantityAmount ?area_norm .
    BIND( ROUND(?area_norm/1000000) as ?area_sqkm) . 
  }
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} ORDER BY ASC(?iLabel)

Try it!

SELECT DISTINCT ?i ?iLabel ?population ?area_sqkm ?popstatement ?date 
WITH { SELECT ?i (MAX(?date_) as ?date) WHERE
  {
   VALUES ?i {wd:Q884 wd:Q31057 wd:Q35555 wd:Q220982 wd:Q31063 wd:Q475038}
              ## Problematic entities with multiple population values
      OPTIONAL {                  ## Get population dates for maxing purposes
      ?i p:P1082 ?popstatement. 
      ?popstatement a wikibase:BestRank .
#      ?popstatement ps:P1082 ?population .
      ?popstatement pq:P585 ?date_ .
      }
   } GROUP BY ?i } as %i
WHERE
{
  INCLUDE %i
  OPTIONAL {                  ## Population, if it exists
      ?i p:P1082 ?popstatement. 
      ?popstatement a wikibase:BestRank .
      ?popstatement ps:P1082 ?population .
      ?popstatement pq:P585 ?date .
  }
  OPTIONAL {                  ## Area, if it exists
    ?i p:P2046 ?areastatement .
    ?areastatement a wikibase:BestRank .     
    MINUS { ?areastatement pq:P518 wd:Q187223. }     ## Filter out lagoons
    MINUS { ?areastatement pq:P518 wd:Q9259. }       ## Filter out UNESCO sites
    MINUS { ?areastatement pq:P518 wd:Q64364418. }   ## Filter out buffer zones
    MINUS { ?areastatement pq:P1012 wd:Q389717. }    ## Filter out contested Islands
    ?areastatement psn:P2046/wikibase:quantityAmount ?area_norm .
    BIND( ROUND(?area_norm/1000000) as ?area_sqkm) . 
  }
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} ORDER BY ASC(?iLabel)

Try it!

--Tagishsimon (talk) 17:34, 11 November 2022 (UTC)

With regards to the subquery that touched redirects. It's a clever way to replace manually entered items that may be redirects with their real items. I confirmed experimentally that a query can't get values from a redirect item that belong to the real item. Redirects might also materialize as a result of a query, but there are two bots that fixes statements that points to a redirect, so that they will point to the real item instead. In other words the graph may temporarily contain links to redirect items, but you can as a query user for practical purposes ignore this. I also suspect, but can't say for sure that statements obtained through other means than queries will have special handling for redirects. Infrastruktur (talk) 07:19, 12 November 2022 (UTC)

* is not working in REGEX

Hi there !

Is anyone can tell me why this query :

SELECT DISTINCT ?Test

{

VALUES (?Test)

{ ("abc def")

("Abc Def") }.

FILTER REGEX(?Test,"A*D").

}

is working perfecty (Responding : "Abc Def" only - Normal, it's case sensitive)

But with real data :

SELECT DISTINCT ?NameTest

{

?Test wdt:P1340 wd:Q17122834.

?Test rdfs:label ?NameTest

FILTER (LANG(?NameTest) = "en").

FILTER REGEX(?NameTest,"A*D").

}

The result is completely false (Contains "Gilles Duceppe" by example), The "D" is always Correct but "A" is completely forgiven

Thanks in advance Klymandre (talk) 13:29, 11 November 2022 (UTC)

@Klymandre: IMO your regex is asking for zero or more A followed by a D, all case sensitive. Try "A.*D" --Tagishsimon (talk) 14:46, 11 November 2022 (UTC)

Oh my God ! you're magic !!! if you didn't exist, you would have to be invented ! Thaaaaaaaanks ! Klymandre (talk) 16:04, 11 November 2022 (UTC)

Two entries that denote the same person

https://www.wikidata.org/wiki/Q59601793

and

https://www.wikidata.org/wiki/Q61086727

Both entries introduce the very same person, Rudolf Lassel. I just discovered this and I'm not sure what to do with it. Komitsuki (talk) 10:04, 12 November 2022 (UTC)

I would encourage you to merge the two items using the Gadget as described in Help:Merge. It's a safe way to perform a merge, and if it fails you can ask someone else to do it. Just make sure that the two items are indeed the same thing first, for instance the word bread is not the same as the item bread. Once you are sure it's the same thing, just go ahead and merge them. Infrastruktur (talk) 10:33, 12 November 2022 (UTC)

Already did. Thank you. Komitsuki (talk) 10:49, 12 November 2022 (UTC)

Query to return Wikipedia West African film articles with/without an infobox

The query below is meant to return some Wikipedia film article from West Africa with an infobox, but I noticed articles like 40 Looks Good on You and A Stab in the Dark (film), which do not contain an infobox is included in the results. Also, if I wanted to negate the infobox requirement, thereby returning articles that do not contain an infobox, what keyword can be used in this regard. Tried using FILTER NOT EXISTS and then ! in the regex to no avail. This query is a fork of the map illustration on the Wikidata sample query page.

SELECT DISTINCT ?item ?itemLabel ?article WHERE {

?item wdt:P31 wd:Q11424 .
?item wdt:P495 ?place .
?place wdt:P361 wd:Q4412 .
   SERVICE wikibase:mwapi {
     bd:serviceParam wikibase:endpoint "en.wikipedia.org" .
     bd:serviceParam wikibase:api "Generator" .
     bd:serviceParam mwapi:generator "search" .
     bd:serviceParam mwapi:gsrsearch "hastemplate:\"infobox film\"" .
     bd:serviceParam mwapi:gsrlimit "max" .
     ?item wikibase:apiOutputItem mwapi:item .
     ?pageid wikibase:apiOutput "@pageid" .     
 } 
   
 OPTIONAL {
     ?article schema:about ?item .
     ?article schema:inLanguage "en" .
     FILTER (SUBSTR(str(?article), 1, 25) = "https://en.wikipedia.org/")
 }

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

}

HandsomeBoy (talk) 23:24, 12 November 2022 (UTC)

@HandsomeBoy: The MWAPI service didn't seem to be contributing to the query in any way. Not sure why. Try:

SELECT DISTINCT ?item ?itemLabel ?article WHERE {
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:endpoint "en.wikipedia.org" .
    bd:serviceParam wikibase:api "Generator" .
    bd:serviceParam mwapi:generator "search" .
    bd:serviceParam mwapi:gsrsearch "hastemplate:\"infobox film\"" .
    bd:serviceParam mwapi:gsrlimit "max" .
    ?item wikibase:apiOutputItem mwapi:item .   
 } 
  FILTER(BOUND(?item))
  ?item wdt:P31 wd:Q11424 .
  ?item wdt:P495 ?place .
  ?place wdt:P361 wd:Q4412 .
  OPTIONAL {
     ?article schema:about ?item .
     ?article schema:inLanguage "en" .
     FILTER (SUBSTR(str(?article), 1, 25) = "https://en.wikipedia.org/")
 }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

As to the second request: feels more like Petscan territory. Here's an example for Cameroon - https://petscan.wmflabs.org/?psid=23260121 - which you could flesh out by adding more West Africa country film categories. --Tagishsimon (talk) 00:38, 13 November 2022 (UTC)

Should also say the normal incantation for EN wiki articles is below; filters are too expensive when a triple will do the job:

?article schema:about ?item ;
  schema:isPartOf <https://en.wikipedia.org/> .

Try it!

--Tagishsimon (talk) 00:41, 13 November 2022 (UTC)

Thanks so much for the prompt response. While the results for the SPARQL query is still not correct (gives a result of about 3 films, when there are hundreds/thousands of such films with infobox), the Petscan platform is highly helpful. Actually didn't recall such as platform existed. HandsomeBoy (talk) 02:23, 13 November 2022 (UTC)

Can someone replace the word "Barker" with "North Street Postern" on every section of this page and any related pages?

Barker Tower is now called the North Street Postern Tower

Q17530640

[3]https://her.york.gov.uk/Monument/MYO4234

[4]https://www.british-history.ac.uk/rchme/york/vol2/plate-26 92.21.59.237 09:59, 13 November 2022 (UTC)

No. First, this is the wrong forum for such a request. Second, the tower is known by both names and so to the extent there is anything to do here, it is to ensure that both names are reflected. --Tagishsimon (talk) 11:31, 13 November 2022 (UTC)

Tagishsimon As the following link shows, the tower is "Formerly known as Barker Tower"

Formerly = In the past

[5]https://her.york.gov.uk/Designation/DYO1294

The original link also says "now the North Street Postern Tower"

Now = In the present 92.21.59.237 19:04, 13 November 2022 (UTC)

Still no. Come back a) when you've learnt a little civility and b) when you have a clue. [6] --Tagishsimon (talk) 19:53, 13 November 2022 (UTC)

How to reference a Wikimedia commons resource from a query

See "Media in category 'Hector Villa-Lobos'" at https://commons.wikimedia.org/wiki/Category:Heitor_Villa-Lobos.

I have tried but not worked out a triple pattern that would reference the https://commons.wikimedia.org/wiki/File:Heitor_Villa-Lobos_-_Trio_for_Oboe,_Clarinet_and_Bassoon_-_1._Anime.ogg media resource on that page. Queries on https://commons-query.wikimedia.org/ for triple patterns where that (as a string or as a URI) is the object or subject of a triple have not turned up anything. Should I be rearranging the parts of the URI? Bobdc (talk) 22:42, 13 November 2022 (UTC)

@Bobdc: This is an approach, starting from a category. Not absolutely clear what you're after.

SELECT ?file ?title ?P180
WITH
{
  SELECT ?file ?title
  WHERE
  {
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:api "Generator" .
      bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
      bd:serviceParam mwapi:gcmtitle "Category:Heitor Villa-Lobos" .
      bd:serviceParam mwapi:generator "categorymembers" .
      bd:serviceParam mwapi:gcmtype "file" .
      bd:serviceParam mwapi:gcmlimit "max" .
      ?title wikibase:apiOutput mwapi:title .
      ?pageid wikibase:apiOutput "@pageid" .
    }
    BIND (URI(CONCAT('https://commons.wikimedia.org/entity/M', ?pageid)) AS ?file)
  }
} AS %get_files
WHERE
{
  INCLUDE %get_files
  filter not exists {?file wdt:P180 ?P180 .}
}

Try it!

--Tagishsimon (talk) 03:42, 14 November 2022 (UTC)

Looking into it, the URIs that's being used in the Commons graph use a slightly different prefix than usual. PREFIX commons: <http://commons.wikimedia.org/wiki/Special:FilePath/>. Meaning URIs in the form https://commons.wikimedia.org/wiki/File:Heitor_Villa-Lobos_-_Trio_for_Oboe,_Clarinet_and_Bassoon_-_1._Anime.ogg will have to be rewritten as http://commons.wikimedia.org/wiki/Special:FilePath/Heitor_Villa-Lobos_-_Trio_for_Oboe,_Clarinet_and_Bassoon_-_1._Anime.ogg . Alternatively you could grab the URI from the entity's schema:url property, but not all the images have structured data associated with them. Very cool seing you here btw. Infrastruktur (talk) 10:57, 14 November 2022 (UTC)

and schema:url seems to be the pertinent predicate (oh, as you said):

SELECT ?file ?title ?image ?imgstr
WITH
{
  SELECT ?file ?title
  WHERE
  {
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:api "Generator" .
      bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
      bd:serviceParam mwapi:gcmtitle "Category:Heitor Villa-Lobos" .
      bd:serviceParam mwapi:generator "categorymembers" .
      bd:serviceParam mwapi:gcmtype "file" .
      bd:serviceParam mwapi:gcmlimit "max" .
      ?title wikibase:apiOutput mwapi:title .
      ?pageid wikibase:apiOutput "@pageid" .
    }
    BIND (URI(CONCAT('https://commons.wikimedia.org/entity/M', ?pageid)) AS ?file)
  }
} AS %get_files
WHERE
{
  INCLUDE %get_files
  ?file schema:url ?image
  BIND(STR(?image) as ?imgstr)
}

Try it!

SELECT ?file WHERE 
{  
 ?file schema:url <http://commons.wikimedia.org/wiki/Special:FilePath/Heitor%20Villa-Lobos%20-%20Trio%20for%20Oboe%2C%20Clarinet%20and%20Bassoon%20-%201.%20Anime.ogg> 
}

Try it!

--Tagishsimon (talk) 17:14, 14 November 2022 (UTC)

Thanks so much for the quick response! This gives me a lot to play with, and there are a lot of other cool things on this page that I will be digging into. Bobdc (talk) 17:47, 14 November 2022 (UTC)

extracting covid-19 pandemic most recent data per country using a Wikidata SPARQL query

I'm trying to extract some of the most recents data about covid-19 pandemic statistics (number of cases, recoveries, point in time) per country from Wikidata using this SPARQL query:

SELECT DISTINCT ?COVID19_loc ?COVID19_locLabel ?countryLabel ?cases ?timeC ?recoveries ?timeR {

   ?pandemic wdt:P1269 wd:Q81068910;
   FILTER ( ?pandemic in ( wd:Q83741704)).
   ?pandemic wdt:P527 ?COVID19_loc.
   ?COVID19_loc wdt:P17 ?country.
   ?COVID19_loc wdt:P1603 ?cases.
   FILTER NOT EXISTS {?COVID19_loc wdt:P1603/wdt:P585 ?timeC_Other FILTER ( ?timeC_Other > ?timeC)}.
   ?COVID19_loc wdt:P8010 ?recoveries.
   FILTER NOT EXISTS {?COVID19_loc wdt:P8010/wdt:P585 ?timeR_Other FILTER ( ?timeR_Other > ?timeR)}.

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

The thing is I'm getting multiples cases and recoveries results instead of the most recents only, also timeC and timeR don't return a thing! Aminov92 (talk) 17:34, 12 November 2022 (UTC)

@Aminov92: Sorry for the delay. Three main things: 1) need to use p:P1603/pq:P1603 type property paths to get to qualifiers, not wdt:/wdt: 2) the query does not define ?timeC and ?timeR, so the filters using them will not work 3) using filter to try to get to the most recent time is probably expensive.

Perhaps the approach below is better? Sadly it times out if done for the world, and so I've added a continent selection clause; the query first finds the qualifying items and from them aggregates the MAX dates; then goes off and finds values matching the dates; then adds labels.

SELECT DISTINCT ?COVID19_loc ?COVID19_locLabel ?countryLabel ?cases ?timeC ?recoveries ?timeR 
WITH { SELECT ?COVID19_loc ?country (MAX(?timeC_) as ?timeC) (MAX(?timeR_) as ?timeR) WHERE 
{
   wd:Q83741704 wdt:P527 ?COVID19_loc.
   ?COVID19_loc wdt:P17 ?country.
   ?country wdt:P30 wd:Q48 .
   ?COVID19_loc p:P1603/pq:P585 ?timeC_ .
   ?COVID19_loc p:P8010/pq:P585 ?timeR_ .
} group by ?COVID19_loc ?country} as %i
WITH { SELECT ?COVID19_loc ?country ?cases ?timeC ?recoveries ?timeR 
WHERE
{
  INCLUDE %i
   ?COVID19_loc p:P1603 ?statC . 
   ?statC ps:P1603 ?cases . 
   ?statC pq:P585 ?timeC.
  
   ?COVID19_loc p:P8010 ?statR . 
   ?statR ps:P8010 ?recoveries . 
   ?statR pq:P585 ?timeR.
  } } as %r
WHERE
{
  INCLUDE %r
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } 
}

Try it!

--Tagishsimon (talk) 09:07, 15 November 2022 (UTC)

Find multiple people by name

Hi! I want to find multiple people by name. Example: "Wolfgang Amadeus Mozart" and "George Frideric Handel". What should I do?

SELECT ?file ?title ?P180
WHERE { 
  ?id rdfs:label ?name .
  FILTER regex(?name, "Wolfgang Amadeus Mozart", "George Frideric Handel") 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de". } 
}|

Try it!

2003:C5:FF1E:4376:ED21:29CC:E7E7:EF44 19:13, 15 November 2022 (UTC)

Probably, reach for MWAPI search, and then filter on the returned items' P31 and rdfs:label:

SELECT DISTINCT ?item ?itemLabel 
WHERE {
  hint:Query hint:optimizer "None".
  VALUES ?name {"Wolfgang Amadeus Mozart" "George Frideric Handel"} 
  BIND(?name as ?name2)
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:srsearch ?name.
    ?item wikibase:apiOutputItem mwapi:title .
  }
  ?item wdt:P31 wd:Q5.
  ?item rdfs:label ?label.
  FILTER regex(str(?label), ?name2) 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de". }
}

Try it!

--Tagishsimon (talk) 23:20, 15 November 2022 (UTC)

Just one distinct value

Hi guys !

In this query, I only want distinct ?item (and not distinct ?itemLabel or ?natureLabel). Can you show me how to adapt it ?

#tous les éléments avec une référence spécifique
SELECT DISTINCT ?item ?itemLabel ?natureLabel {
  ?item ?prop ?statement .
  ?statement prov:wasDerivedFrom ?ref.
  ?ref pr:P248 wd:Q112601887 .
  ?item p:P31 ?value .
  ?value ps:P31 ?nature .
 ?item wdt:P463 wd:Q3423698 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Simon Villeneuve (talk) 14:03, 16 November 2022 (UTC)

@Simon Villeneuve: Probably, group by ?item ?itemLabel, and then GROUP_CONCAT ?natureLabel. As I understand your ask (one row per item), ?natureLabel is your problem; there will only be a single ?itemLabel. Things to note: a) need to prompt the label service to provide labels early enough for them to be aggregated & as a consequence, need also to specify all labels within the service call b) the value aggregated - ?natureLabel_ - needs to have a variable name different from the column label, ?natureLabel. I tend to use a trailing _ for this purpose c) I still cannot spell separator reliably without consulting a dictionary; it's very sad.

#tous les éléments avec une référence spécifique
SELECT ?item ?itemLabel (GROUP_CONCAT(DISTINCT ?natureLabel_;separator="; ") as ?natureLabel) {
  ?item ?prop ?statement .
  ?statement prov:wasDerivedFrom ?ref.
  ?ref pr:P248 wd:Q112601887 .
  ?item p:P31 ?value .
  ?value ps:P31 ?nature .
 ?item wdt:P463 wd:Q3423698 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
                         ?item rdfs:label ?itemLabel .
                         ?nature rdfs:label ?natureLabel_ .}
} group by ?item ?itemLabel

Try it!

--Tagishsimon (talk) 14:13, 16 November 2022 (UTC)

Thank you Tagishsimon !

a) ok. Not sure if I'll be able to know in the future when this must be precised
b) ok, I'll do that too
c) I don't understand. ; is good for separating the labels no ?

Thank you very much again ! Simon Villeneuve (talk) 14:31, 16 November 2022 (UTC)

Separator v.good for separating things. I think English pronounciation tends towards seperator. At least in my head. --Tagishsimon (talk) 14:56, 16 November 2022 (UTC)

Filtering sitelinks to only Wikipedia articles

I have a query for the count of sitelinks marked 'featured' per item (ie: sitelink has featured article badge (Q17437796)), derived from one of the examples:

#Featured articles of all Wikimedia projects
# Get all Featured Articles (Q17437796)
SELECT distinct ?item ?itemLabel (count(distinct ?sitelink) as ?fa_count) WHERE {
  ?sitelink schema:name ?name;
    schema:inLanguage ?lang;
    schema:about ?item;
    # Sitelink is badged as a Featured Article
    wikibase:badge wd:Q17437796.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?item ?itemLabel order by desc(?fa_count)

Try it!

(It's quite a fun query: see if you can guess what our most popular FAs are before running it.)

However, I'd like to count only the Wikipedia articles for each - so eg Michelangelo (Q5592) would give 11 not 12 (he has one FA wikiquote page). I tried adding in "?sitelink schema:isPartOf [wikibase:wikiGroup "wikipedia"]" but this seemed to time out, even with the labels service turned off. Is there a trick to get this more efficiently that I might be missing, or is it just going to break down trying to filter so many links? Andrew Gray (talk) 18:07, 16 November 2022 (UTC)

@Andrew Gray: This seems to be the thing.

#Featured articles of all Wikimedia projects
# Get all Featured Articles (Q17437796)
SELECT distinct ?item ?itemLabel (count(distinct ?sitelink) as ?fa_count) WHERE {
  ?sitelink schema:name ?name;
    schema:inLanguage ?lang;
    schema:about ?item.
    # Sitelink is badged as a Featured Article
    ?sitelink wikibase:badge wd:Q17437796. hint:Prior hint:runFirst true.
    ?sitelink schema:isPartOf ?partOf.
    ?partOf wikibase:wikiGroup "wikipedia" . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?item ?itemLabel order by desc(?fa_count)

Try it!

--Tagishsimon (talk) 18:18, 16 November 2022 (UTC)

@Tagishsimon amazing, thankyou! That works perfectly. So it's only runFirst I need to enable - that makes the whole thing a lot smoother. Andrew Gray (talk) 18:33, 16 November 2022 (UTC)

It's horses for courses; in this instance, runFirst seemed like the place to go, b/c there cannot be that many FA badges. Sometimes runLast stops the optimiser running off with a pattern matching too many triples. Sometimes hint:Query hint:optimizer "None". (aka just do what I say) works best. Also worth noting that most things - i.e. multi-clause sparql - held within braces will take hint:Prior hint:runFirst true. - see, for instance this answer which causes a nested query to be run first. --Tagishsimon (talk) 18:46, 16 November 2022 (UTC)

@Tagishsimon All very elegant. One day I will understand how to use the optimizer!

Here is an unexpected mystery that this threw up: it turned out enwiki had about ten FAs less than we thought. Fine, easy to fix. But... there are five disambiguation pages there, that WD seems to think have FA badges (https://w.wiki/5yRA). All of them seem to have had recent page moves and at some point pointed to a FA, but the items all seem to have had the link added after the title no longer redirected to the FA and there is no edit in the WD item's history to indicate a badge being added/removed. Absolutely no idea what might be causing this. Andrew Gray (talk) 19:26, 16 November 2022 (UTC)

@Andrew Gray: Most odd. The rdf shown in page information, and the edit histories for Combe Hill (Q112621799) and Eric Brewer (Q108861497), both suggest the items do not have, and have never had, the FA badge. Both of the featured EN wiki articles were moved before their prior titles were usurped by disambiguation pages. The badge is attached to the sitelink, not to the item. Seems that whilst WD is dealing properly with badges when updating the sitelink after a page move (we see no FA badge on the disambiguation item, do see it on the ice hockey player item), Blazegraph has not been informed? Feels like phab territory. (I note Eric Brewer (Q1342539) is reported to have an FA badge, so the badge is being associated with the new sitelink, but not disassociated from the old?) --Tagishsimon (talk) 20:05, 16 November 2022 (UTC)

@Tagishsimon Now up at phab:T323239. I think it is successfully updating the new sitelink with badge (it also works fine for a more mundane move). I wonder if it is somehow not deleting the badge from the sitelink record, even though the sitelink doesn't exist any more, and then when you add the new item it goes, oh hello, I know that link should have a badge. Or something of that ilk. Andrew Gray (talk) 20:38, 16 November 2022 (UTC)

Please Merge these two items Q64728405 & Q88469760

They are the same woman, Caroline de Broutelles. Thank you! (I am a real user who can't get into their account becuase I haven't used it in over a decade... ) 2600:1700:9758:7340:3C1E:AE49:FF26:3CA7 20:55, 16 November 2022 (UTC)

Done. --Tagishsimon (talk) 21:13, 16 November 2022 (UTC)

Listing countries where languages have status of an official language

Hello!

Is it possible to build, using some SPARQL query, following list:

for each 639-1 language code (or maybe also 639-2 and 639-3?) list a;; counties, where given language is the official language.

Preferably both wikidata id of a country and country name in English would be listed.

For example part of the list would be pl language code listing Q36 Poland

I searched for such query here using [7] and [8]

Mateusz Konieczny (talk) 16:04, 16 November 2022 (UTC)

@Mateusz Konieczny: This sort of thing?

SELECT ?item ?itemLabel ?ISO639_1 ?ISO639_2 ?ISO639_3 ?country ?countryLabel
WHERE 
{
  { SELECT ?item (SAMPLE(?ISO639_1_) as ?ISO639_1) (SAMPLE(?ISO639_2_) as ?ISO639_2) (SAMPLE(?ISO639_3_) as ?ISO639_3) WHERE
    {
      {?item wdt:P218 ?ISO639_1_ .}
      UNION 
      {?item wdt:P219 ?ISO639_2_ .}
      UNION
      {?item wdt:P220 ?ISO639_3_ .}
      } group by ?item }
  ?country wdt:P463 wd:Q1065. 
  ?country wdt:P37 ?item.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 19:09, 16 November 2022 (UTC)

@Tagishsimon: - yes, exactly! Would you agree to license it under MIT so I can use it within https://github.com/matkoniecz/wikibrain without having licensing headaches? Mateusz Konieczny (talk) 19:25, 16 November 2022 (UTC)

@Tagishsimon: how can I get rid of https://www.wikidata.org/wiki/Q713750 and https://www.wikidata.org/wiki/Q193619 and other defunct countries from results? Mateusz Konieczny (talk) 08:30, 18 November 2022 (UTC)

Oh, this time examples seem useful - https://w.wiki/5yxa worked Mateusz Konieczny (talk) 08:34, 18 November 2022 (UTC)

SELECT ?item ?itemLabel ?ISO639_1 ?ISO639_2 ?ISO639_3 ?country ?countryLabel
WHERE 
{
  { SELECT ?item (SAMPLE(?ISO639_1_) as ?ISO639_1) (SAMPLE(?ISO639_2_) as ?ISO639_2) (SAMPLE(?ISO639_3_) as ?ISO639_3) WHERE
    {
      {?item wdt:P218 ?ISO639_1_ .}
      UNION 
      {?item wdt:P219 ?ISO639_2_ .}
      UNION
      {?item wdt:P220 ?ISO639_3_ .}
      } group by ?item }
  ?country wdt:P463 wd:Q1065. 
  ?country wdt:P37 ?item.
  #not a former country
  FILTER NOT EXISTS {?country wdt:P31 wd:Q3024240}
  #and no an ancient civilisation (needed to exclude ancient Egypt)
  FILTER NOT EXISTS {?country wdt:P31 wd:Q28171280}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } 
}

Try it!

Mateusz Konieczny (talk) 08:36, 18 November 2022 (UTC)

Hmmm. https://www.wikidata.org/wiki/Q504081 is listed, both https://www.wikidata.org/wiki/Q756617 and https://www.wikidata.org/wiki/Q35 also both https://www.wikidata.org/wiki/Q974 and https://www.wikidata.org/wiki/Q971 - I will need to look into it what is going on Mateusz Konieczny (talk) 08:41, 18 November 2022 (UTC)

All "COVID-19 pandemic in <COUNTRY>" articles with most recent data

Hello, I want a query to get all COVID-19 pandemic in <COUNTRY> articles with most recent data for coordinate location, number of deaths, cases and recoveries. Thank you in advance! Aram (talk) 22:49, 17 November 2022 (UTC)

@Aram: Something like this?

SELECT ?COVID19_loc ?COVID19_locLabel ?countryLabel ?coord ?cases ?dateC ?recoveries ?dateR ?deaths ?dateD
WITH { SELECT ?COVID19_loc ?country WHERE 
{
   wd:Q83741704 wdt:P527 ?COVID19_loc.
   ?COVID19_loc wdt:P17 ?country.
} } as %i
WITH { SELECT ?COVID19_loc ?country (MAX(?timeR_) as ?timeR_max) WHERE 
{
  INCLUDE %i
  ?COVID19_loc p:P8010/pq:P585 ?timeR_ .
} group by ?COVID19_loc ?country } as %Rdate
WITH { SELECT ?COVID19_loc ?country (MAX(?timeC_) as ?timeC_max) WHERE 
{
  INCLUDE %i
  ?COVID19_loc p:P1603/pq:P585 ?timeC_ .
} group by ?COVID19_loc ?country } as %Cdate
WITH { SELECT ?COVID19_loc ?country (MAX(?timeD_) as ?timeD_max) WHERE 
{
  INCLUDE %i
  ?COVID19_loc p:P1120/pq:P585 ?timeD_ .
} group by ?COVID19_loc ?country } as %Ddate
WITH { SELECT ?COVID19_loc ?country (SAMPLE(?timeD_max) as ?timeD) (SAMPLE(?timeC_max) as ?timeC) (SAMPLE(?timeR_max) as ?timeR) WHERE
{
  {INCLUDE %Rdate}
  UNION
  {INCLUDE %Cdate}
  UNION
  {INCLUDE %Ddate}
}  group by ?COVID19_loc ?country } as %dates
WITH { SELECT ?COVID19_loc ?country ?recoveries_ ?timeR WHERE
{
  INCLUDE %dates
  ?COVID19_loc p:P8010 ?statR . 
  ?statR ps:P8010 ?recoveries_ . 
  ?statR pq:P585 ?timeR .
} } as %Rstats
WITH { SELECT ?COVID19_loc ?country ?cases_ ?timeC WHERE
{
  INCLUDE %dates
  ?COVID19_loc p:P1603 ?statC . 
  ?statC ps:P1603 ?cases_ . 
  ?statC pq:P585 ?timeC .
} } as %Cstats
WITH { SELECT ?COVID19_loc ?country ?deaths_ ?timeD WHERE
{
  INCLUDE %dates
  ?COVID19_loc p:P1120 ?statD . 
  ?statD ps:P1120 ?deaths_ . 
  ?statD pq:P585 ?timeD .
} } as %Dstats
WITH { SELECT ?COVID19_loc ?country
              (SAMPLE(?cases_) as ?cases) (SAMPLE(?timeC) as ?dateC) 
              (SAMPLE(?recoveries_) as ?recoveries) (SAMPLE(?timeR) as ?dateR) 
              (SAMPLE(?deaths_) as ?deaths) (SAMPLE(?timeD) as ?dateD) WHERE
{
  {INCLUDE %Rstats}
  UNION
  {INCLUDE %Cstats}
  UNION
  {INCLUDE %Dstats}
} group by ?COVID19_loc ?country } as %results 
WHERE
{
  INCLUDE %results
  OPTIONAL {?country wdt:P625 ?coord. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } 
}

Try it!

--Tagishsimon (talk) 00:20, 18 November 2022 (UTC)

Tagishsimon Yes, that’s exactly the statistics we wanted including some more columns! We (me and two other friends working on a project) really thank you so much for saving our lives! We really appreciate your reply! Aram (talk) 17:31, 18 November 2022 (UTC)

Optional is not working with schema

Hi there !

Is anyone can explain to me why this query :

SELECT ?Cat ?Article

WHERE {

    ?Cat wdt:P31 wd:Q146.

    ?Article schema:about ?Cat.

    ?Article schema:isPartOf <https://fr.wikipedia.org/>.

}

is working perfectly, but if I want to add "OPTIONAL" clause, a JSon error is generate ?

 OPTIONAL {?Article schema:about ?Cat}.

OPTIONAL {?Article schema:isPartOf <https://fr.wikipedia.org/>}.

Thanks in advance ! Klymandre (talk) 22:03, 19 November 2022 (UTC)

@Klymandre: WDQS is mainly whinging about the fullstops being on the wrong side of the closing brace in your two optionals. THat said, I think what you were looking for is:

SELECT ?Cat ?Article WHERE
{
    ?Cat wdt:P31 wd:Q146.
    OPTIONAL {
      ?Article schema:about ?Cat.
      ?Article schema:isPartOf <https://fr.wikipedia.org/>.
    }
}

Try it!

--Tagishsimon (talk) 22:23, 19 November 2022 (UTC)

Oh thank you ! I didn't know there is a good or bad place for the dots with the brackets.

And now I see the two instructions in the same "OPTIONAL", it appears logical to me !

Thanks again !!! Klymandre (talk) 17:14, 20 November 2022 (UTC)

Get Landkreis and Bundesland for German place

I want to get the current values for Landkreis (district of Germany (Q106658))/Kreisfreie Stadt (independent city of Germany (Q22865)) and Bundesland (federated state of Germany (Q1221156)) for a German place. E.g. Cordshagen (Q19697835) has located in the administrative territorial entity (P131) of Welle (Q676944) and that again has located in the administrative territorial entity (P131) of Harburg (Q5907) and that again located in the administrative territorial entity (P131) of Lower Saxony (Q1197). But I don't know the number of levels down. In the case of Cordshagen (Q19697835) it's three levels down but there could be cases where it's four levels down or zero levels down (Hamburg (Q1055) is a place, a Kreisfreie Stadt and a Bundesland at the same time).

How would I model that? Slomox (talk) 15:21, 20 November 2022 (UTC)

@Slomox: The short answer is, it'll likely involve a ZeroOrMorePath or OneOrMorePath property path - https://www.w3.org/TR/sparql11-query/#propertypaths - which handles the issue of uncertainty about the path length between a subject and an object. Quite how you approach it depends on what you want, but here's one approach for Landkreis. I've not attempted Kreisfreie Stadt, in part because there seem to be very few things attached to that. Bundesland is very problematic; more than 3 million items have country=germany, and I suspect very many of them have a P131 path leading to a Bundesland item ... more items than the query service is able to deal with without timeouts - at least based on my SPARQL.

In the query below, a first query finds items with a P131 statement; traces a path from the statement value to a ?Landkrei variable, and constrains ?Landkreis to be a district of Germany (Q106658), and then as a bonus finds out the P131 authority value in the subject item as a variable which can be used in the SELECT. A second query deals with getting labels for everything. So we get columns for the item, its immediate P131, and the district of Germany (Q106658) which that maps to. district of Germany (Q106658) seems, from a quick look, to be found as a normal rank statement on items, and items seem to have other P31s set to preferred, so the query uses p:P131/ps:P131 to get to the items, rather than wdt:P131 (which will not retrieve normal rank items where preferred rank items exist).

SELECT ?item ?itemLabel ?authority ?authorityLabel ?Landkreis ?LandkreisLabel 
WITH { SELECT ?item ?Landkreis ?authority
  WHERE 
  {
    ?item p:P131 ?stat. 
    ?stat ps:P131/wdt:P131* ?Landkreis . hint:Prior hint:gearing "forward".
    ?Landkreis p:P31/ps:P31  wd:Q106658. 
    ?stat ps:P131 ?authority.  
  } } as %i
WHERE
{
  INCLUDE %i     
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de, en". } 
}

Try it!

--Tagishsimon (talk) 10:16, 21 November 2022 (UTC)

@Tagishsimon: Thank you very much! I'll probably need some time to grasp what all of that means but I think this will help me to solve my problem and to get a better understanding of SPARQL. --Slomox (talk) 15:32, 21 November 2022 (UTC)

US Companies and Subsidiaries

Hi - I'm looking to pull a list of active, US-based companies and their associated subsidiaries (e.g. for J2 (https://www.wikidata.org/wiki/Q16997523), return J2 in one column and IGN, Mashable, Pc Magazine, AskMen, geek.com, etc in another column as individual records) 24.193.169.191 18:40, 20 November 2022 (UTC)

Started building this out but not returning any subsidiaries or revenue

SELECT DISTINCT ?item ?itemLabel ?hasSubsidiary ?revenue WHERE {

 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
 {
   SELECT DISTINCT ?item WHERE {
     ?item p:P17 ?statement0.
     ?statement0 (ps:P17/(wdt:P279*)) wd:Q30.
     ?item p:P31 ?statement1.
     ?statement1 (ps:P31/(wdt:P279*)) wd:Q4830453.
     ?item p:P355 ?statement2.
     ?statement2 (ps:P355/(wdt:P279*)) _:anyValueP355.
   }
   LIMIT 100
 }

}

The value for the subsidiary is to be found in the variable _:anyValueP355, which is not specified in the SELECT for the inner query, and is thus not available to the outer query. There are other issues; ps:P17/wdt:P279* is valid, but probably won't find anything more than wdt:P31 would have, b/c countries are not arranged using subclass of. Same for ps:P355/wdt:P279*.

Here is something nearer what you wanted, but even in this a) we'd probably need units for the revenue figures b) and a date c) and dedupication for those subsidiaries that have more than one BestRank revenue statement. Plus ?item wdt:P355 ?subsidiary is only one approach to the model; it would probably be more productive to look at items having parent organization (P749) since generally it is saner to get child items to point to their parent, than parents to point to their children, b/c companies can have hundreds of subsidiaries, which rather gums up a single item. So, there's much scope for improvement of the report, should you wish. Please come back & talk about it if you do; it's what this board is here for.

SELECT DISTINCT ?item ?itemLabel ?subsidiary ?subsidiaryLabel  ?revenue WHERE {

     ?item wdt:P17 wd:Q30.
     ?item wdt:P31/wdt:P279* wd:Q4830453.
     ?item wdt:P355 ?subsidiary.
  OPTIONAL {?subsidiary wdt:P2139 ?revenue.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 12:56, 21 November 2022 (UTC)

Thank you! This is helpful. If I were to improve it to to look at items having parent organization, would I just need to replace P355 with P749 and ?parentOrganization in the select statement? 24.193.169.191 14:25, 21 November 2022 (UTC)

On thinking about it, the issue can be handled by using an inverse property path, as below.

SELECT DISTINCT ?item ?itemLabel ?subsidiary ?subsidiaryLabel  ?date ?revenue ?unitLabel WHERE {

  ?item wdt:P17 wd:Q30.
  ?item wdt:P31/wdt:P279* wd:Q4830453.
  ?item wdt:P355|^wdt:P749 ?subsidiary.
  OPTIONAL {?subsidiary p:P2139 ?stat . 
            ?stat psv:P2139 [ wikibase:quantityAmount ?revenue ; wikibase:quantityUnit ?unit ]  .
            ?stat a wikibase:BestRank.
            OPTIONAL {?stat pq:P585 ?date} }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

Another way would be an additional ?subsidiary wdt:P749 ?item clause:

SELECT DISTINCT ?item ?itemLabel ?subsidiary ?subsidiaryLabel  ?date ?revenue ?unitLabel WHERE {

  ?item wdt:P17 wd:Q30.
  ?item wdt:P31/wdt:P279* wd:Q4830453.
  {?item wdt:P355 ?subsidiary.}
  UNION
  {?subsidiary wdt:P749 ?item.}
  OPTIONAL {?subsidiary p:P2139 ?stat . 
            ?stat psv:P2139 [ wikibase:quantityAmount ?revenue ; wikibase:quantityUnit ?unit ]  .
            ?stat a wikibase:BestRank.
            OPTIONAL {?stat pq:P585 ?date} }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

The query now provides units for the revenue, but does not deal with subsidiaries having multiple revenue statement none of which is preferred. Easiest way to sort that out, with the relatively few instances of problem, is to mark the statement for the most recent revenue as preferred in the item. I also spotted some bogus units, and hence values, in the rows returned by the report. Ideally someone'll fix them :( --Tagishsimon (talk) 16:38, 21 November 2022 (UTC)

Thank you - going through the results now but seems to have done the trick. Appreciate the help! 24.193.169.191 00:29, 22 November 2022 (UTC)

I cannot add information on the identifiers

https://www.wikidata.org/wiki/Q115315621

I need to add an IMSLP-related information for it and I cannot access to the bare edit mode. Thank you in advance for helping me with this concern. Komitsuki (talk) 03:56, 21 November 2022 (UTC)

Bear edit mode

I am not entirely sure what you mean. I see you have some familiarity with Wikidata, so I am going to assume you know the data has to be "structured data", meaning structure has to be imposed. If you need to enter a lot of information you could use QuickStatements. Infrastruktur (talk) 16:39, 21 November 2022 (UTC)

OSM objects tagged with wikimedia_commons=* while wikidata have no P18

I saw there's a tool to query both OSM and Wikidata. My idea is to query for OSM objects tagged with wikidata=*+wikimedia_commons=* when the wikidata entry has no P18 (I found some while mapping). So we could add the commons image in the wikidata entry after a check. Ivanbranco (talk) 09:36, 21 November 2022 (UTC)

@Ivanbranco: note that such systematic copying may be problematic (while database rights are not existing in USA it would further increase problems for users of Wikidata in jurisdictions where database rights are a thing) Mateusz Konieczny (talk) 12:09, 22 November 2022 (UTC)

Is this what you had in mind? This finds OSM objects of any type that have both a wikidata tag and a commons tag and filters out matches that doesn't have a P18 statement on wikidata. By the way, I was told by someone here that OpenStreetMap's triplestore lags 10 months behind their live data.

PREFIX osmm: <https://www.openstreetmap.org/meta/>
PREFIX osmt: <https://wiki.openstreetmap.org/wiki/Key:>

select ?wd ?s ?commons
where {
  SERVICE <https://sophox.org/sparql> {
    select ?s ?wd ?commons
    where {
      ?s osmt:wikimedia_commons ?commons_ ;
         osmt:wikidata ?wd .
      BIND(URI(CONCAT("https://commons.wikimedia.org/wiki/", ?commons_)) AS ?commons)
    }
  }
  FILTER NOT EXISTS { ?wd wdt:P18 [] . }
}

Try it!

Infrastruktur (talk) 13:20, 22 November 2022 (UTC)

Some more context &, for laughs, a column of commons sitelinks for found items.

PREFIX osmm: <https://www.openstreetmap.org/meta/>
PREFIX osmt: <https://wiki.openstreetmap.org/wiki/Key:>

select ?wd ?wdLabel ?countryLabel ?s ?commons ?sitelink WITH {
  select ?wd ?wdLabel ?countryLabel ?s ?commons 
where {
  SERVICE <https://sophox.org/sparql> {
    select ?s ?wd ?commons
    where {
      ?s osmt:wikimedia_commons ?commons ;
         osmt:wikidata ?wd .
    }
  }
  FILTER NOT EXISTS { ?wd wdt:P18 [] . }
 } } as %i
WHERE
{
  INCLUDE %i
  OPTIONAL {?wd wdt:P17 ?country.}
  OPTIONAL {  ?url schema:about ?wd ;
  schema:isPartOf <https://commons.wikimedia.org/> ; 
  schema:name ?sitelink .
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

@Mateusz Konieczny: that's why I added "after a check"! @Infrastruktur: seems so! Thank you! --Ivanbranco (talk) 14:34, 22 November 2022 (UTC)

@Ivanbranco: Adding additional check does not change that as result Wikidata becomes (as far as database rights are concerned) a derivative work of OSM Mateusz Konieczny (talk) 16:32, 22 November 2022 (UTC)

removing VALUES from result set

This must be a FAQ. Using VALUES to collect parts of the result is straightforward:

SELECT DISTINCT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P31 wd:Q13442814.
  ?item wdt:P921 wd:Q164778.
  VALUES ?prot { wd:Q83133549 wd:Q24745872 wd:Q24770641 }
  ?item wdt:P921 ?prot.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

This gives 57 out of 634 articles. But how do I remove items using VALUES? This naive attempt does not work:

SELECT DISTINCT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P31 wd:Q13442814.
  ?item wdt:P921 wd:Q164778.
  VALUES ?prot { wd:Q83133549 wd:Q24745872 wd:Q24770641 }
  MINUS { ?item wdt:P921 ?prot. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

This gives 631 articles. The correct answer would be 577, like we get from this query:

SELECT DISTINCT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P31 wd:Q13442814.
  ?item wdt:P921 wd:Q164778.
  MINUS { ?item wdt:P921 wd:Q83133549. }
  MINUS { ?item wdt:P921 wd:Q24745872. }
  MINUS { ?item wdt:P921 wd:Q24770641. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}

Try it!

Is it possible to do this without x explicit MINUS lines? Thanks. --SCIdude (talk) 16:20, 22 November 2022 (UTC)

@SCIdude: Yup. I'm not v.clear on the rules for when a clause within braces can & cannot see stuff which is outside the brace; but that was what was going on here.

SELECT DISTINCT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P31 wd:Q13442814.
  ?item wdt:P921 wd:Q164778.
  MINUS {
    VALUES ?prot { wd:Q83133549 wd:Q24745872 wd:Q24770641 }
    ?item wdt:P921 ?prot. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 18:41, 22 November 2022 (UTC)

Thanks. Really counter-intuitive for any developer. SCIdude (talk) 08:18, 23 November 2022 (UTC)

P2860 with qualifier

I am interested in knowing all items with at least one cites work (P2860) statement with at least one qualifier, any qualifier. Harej (talk) 23:27, 23 November 2022 (UTC)

@Harej:

I think you'll be out of luck; the property is currently used 287,516,594 times as a main statement. We can look at a sample of ~2.5 million items with cites work (P2860). Any more, and timeout. That suggests (at least) four qualifiers are used: has goal (P3712), has characteristic (P1552), object named as (P1932) & series ordinal (P1545). However the last two of those do not seem amenable to being listed when tackled on their own, let alone in union with the rest. There is a laborious way in which all of the items could be found, using the SLICE service, but you'd need to run 100 or more individual reports. It's possible I'm missing some sort of go-faster trick, but...

#title:Sample 2.5m P2860s for qualifiers
SELECT ?property WITH { 
  SELECT ?item ?stat 
  WHERE 
  {
    ?item p:P2860 ?stat . 
  }  limit 2500000 } as %i
WHERE
{
  INCLUDE %i
  ?stat ?predicate [] . 
  ?property wikibase:qualifier ?predicate.
} group by ?property

Sample 2.5m P2860s for qualifiers

#title:Find all items ***TIMEOUT***
SELECT ?item
WHERE 
{
  {?item p:P2860/pq:P3712 ?value .}
  UNION
  {?item p:P2860/pq:P1932 ?value .}
  UNION
  {?item p:P2860/pq:P1552 ?value .}
  UNION
  {?item p:P2860/pq:P1545 ?value .}
}

Find all items ***TIMEOUT***

#title:Find P1932 qualifier items ***TIMEOUT***
SELECT ?item
WHERE 
{
  ?stat pq:P1932 [] .
  ?item p:P2860 ?stat .
}

Find P1932 qualifier items ***TIMEOUT***

--Tagishsimon (talk) 00:19, 24 November 2022 (UTC)

Extraordinarily helpful, thank you. Helped me reduce the number of items I had to look through from millions to hundreds. (I was able to run the full query on another copy of the query service.) Harej (talk) 02:34, 24 November 2022 (UTC)

Items that are subclass of (P279) anatomical entity (Q27043950) with sitelink to redirect (Q70893996)

I'd love to have a query that shows all subclass of (P279) anatomical entity (Q27043950) that have sidelinks with sitelink to redirect (Q70893996) but not with intentional sitelink to redirect (Q70894304) ChristianKl ❪✉❫ 13:10, 25 November 2022 (UTC)

Items that are instance of (P31) human (Q5) with sitelink to redirect (Q70893996) in enwiki

I'd love to have a query that shows all instance of (P31) human (Q5) that have sidelinks with sitelink to redirect (Q70893996) into enwiki but not with intentional sitelink to redirect (Q70894304) into enwiki. ChristianKl ❪✉❫ 13:11, 25 November 2022 (UTC)

SELECT ?item ?itemLabel ?name ?sitelink WHERE {
  ?sitelink schema:about ?item; schema:isPartOf <https://en.wikipedia.org/>; schema:name ?name; wikibase:badge wd:Q70893996 .
  ?item wdt:P31 wd:Q5 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}

Try it!

Mind that ~50% of sitelinks to enwiki redirects do not have a badge yet. The bot is running 24/7, but there is quite a lot of work to do. —MisterSynergy (talk) 13:44, 25 November 2022 (UTC)

Filter out spesific items from a property

This query is going to be used in a Wikidata list. What I want is a query that generates a list of ships registered in Norway, and fetches data from significant event (P793), but only shipwrecking (Q906512), scuttling (Q1786766) and ship breaking (Q336332). The query must still return ship items without any of those three.

This is the query I made, but I don't know how to filter out the spesific entries:

SELECT ?item ?itemLabel ?IMO_number ?event ?eventLabel  WHERE {
  ?item wdt:P8047 wd:Q20.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "nb". }
  OPTIONAL { ?item wdt:P793 ?event. }
  OPTIONAL { ?item wdt:P458 ?IMO_number. }
}

Try it!

Cavernia (talk) 10:41, 22 November 2022 (UTC)

@Cavernia: We meet again. This gets only the three events of interest to you. Let's keep going with this until you get exactly what you're after ... maybe share the test Listeria page URL so I can see how it looks.

SELECT ?item ?itemLabel ?IMO_number ?event ?eventLabel  WHERE {
  ?item wdt:P8047 wd:Q20.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "nb". }
  OPTIONAL { ?item wdt:P793 ?event. FILTER(?event IN (wd:Q906512, wd:Q1786766, wd:Q336332))}
  OPTIONAL { ?item wdt:P458 ?IMO_number. }
}

Try it!

--Tagishsimon (talk) 13:10, 22 November 2022 (UTC)

Excellent, thanks a lot! --Cavernia (talk) 21:55, 22 November 2022 (UTC)

@Tagishsimon Now I've got another problem. I also want to show the year/date of the events which is added as qualifier, and the place. How can I return only the date/place for the actual event, and not dates/places for all events? The best solution would be to concatenate the event, date and place in the same coloumn. --Cavernia (talk) 18:57, 25 November 2022 (UTC)

@Cavernia: So this sort of direction. There is considerable scope for fiddling with the format of the dates, including just a SUBSTR(str(?date_),1,8), or translating months into strings - 07 -> July &c using a formulation such as BIND(IF(?month="7","July",IF(?month="8","August,"")) as ?monthLabel). Right now I've used some code with evaluates date precision and the length of day and month strings, to give a consistent yyyy:mm:dd output, providing days or months only when justified by the date precision. You say "concatenate the event, date and place", but I'm not sure where we're getting the "place" from. You can probably see how to work it to the SPARQL below, but come back here if not and/or for any other tweaks.

SELECT ?item ?itemLabel ?IMO_number ?event ?eventLabel ?eventdate WHERE {
  ?item wdt:P8047 wd:Q20.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "nb". }
  OPTIONAL { ?item p:P793 ?stat .
             ?stat ps:P793 ?event. 
             OPTIONAL {?stat pqv:P585 [
                wikibase:timePrecision ?precision ;
                wikibase:timeValue ?date_ ].
                BIND(IF(strlen(str(month(?date_)))=2,str(month(?date_)),concat("0",str(month(?date_)))) as ?month) 
                BIND(IF(strlen(str(day(?date_)))=2,str(day(?date_)),concat("0",str(day(?date_)))) as ?day) 
                BIND(IF(?precision=9,year(?date_),
                       IF(?precision=10,concat(str(year(?date_)),":",?month),
                         IF(?precision=11,concat(str(year(?date_)),":",?month,":",?day),""))) as ?date)                      
             }
             ?event rdfs:label ?eventLabel . filter(lang(?eventLabel)="nb")
             BIND(CONCAT(?eventLabel," ",str(?date)) as ?eventdate) 
            
            FILTER(?event IN (wd:Q906512, wd:Q1786766, wd:Q336332))
           }
  OPTIONAL { ?item wdt:P458 ?IMO_number. }
}

Try it!

--Tagishsimon (talk) 19:35, 25 November 2022 (UTC)

Here's an added hack to get the monthLabel, fwiw.

SELECT ?item ?itemLabel ?IMO_number ?event ?eventLabel ?eventdate ?monthitemLabel WHERE {
  ?item wdt:P8047 wd:Q20.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "nb". }
  OPTIONAL { ?item p:P793 ?stat .
             ?stat ps:P793 ?event. 
             OPTIONAL {?stat pqv:P585 [
                wikibase:timePrecision ?precision ;
                wikibase:timeValue ?date_ ].
                BIND(IF(strlen(str(month(?date_)))=2,str(month(?date_)),concat("0",str(month(?date_)))) as ?month) 
                BIND(IF(strlen(str(day(?date_)))=2,str(day(?date_)),concat("0",str(day(?date_)))) as ?day) 
                BIND(IF(?precision=9,year(?date_),
                       IF(?precision=10,concat(str(year(?date_)),":",?month),
                         IF(?precision=11,concat(str(year(?date_)),":",?month,":",?day),""))) as ?date) 
       
                BIND(month(?date_) as ?monthdate)
                ?monthitem p:P279 ?statm . 
                ?statm ps:P279 wd:Q18602249.
                ?statm pq:P1545 ?monthdate2.
                FILTER(str(?monthdate2)=str(?monthdate))             
             }
             ?event rdfs:label ?eventLabel . filter(lang(?eventLabel)="nb")
             BIND(CONCAT(?eventLabel," ",str(?date)) as ?eventdate) 
            
            FILTER(?event IN (wd:Q906512, wd:Q1786766, wd:Q336332))
           }
  OPTIONAL { ?item wdt:P458 ?IMO_number. }
}

Try it!

--Tagishsimon (talk) 19:59, 25 November 2022 (UTC)

Class tree

I'm looking to have something like this be made, I don't know if it is possible with queries. Based on subclasses.

user account (Q3604202)

social media account (Q102345381)

verified account or profile (Q28378282)

X Premium account (Q115148082)

premium account (Q2108670)

private account (Q58370623)

protected Twitter account (Q78680253)

Just an organized way to see the subclasses of an item. Is this possible? Thanks! -wd-Ryan (Talk/Edits) 20:58, 25 November 2022 (UTC)

@Wd-Ryan: Seems so, using the Tree view

#defaultView:Tree
SELECT ?level0 ?level0Label ?level1 ?level1Label ?level2 ?level2Label ?level3 ?level3Label ?level4 ?level4Label
WHERE 
{
  BIND(wd:Q3604202 as ?level0)
  ?level1 wdt:P279 ?level0. 
  OPTIONAL {?level2 wdt:P279 ?level1.
    OPTIONAL {?level3 wdt:P279 ?level2.
      OPTIONAL {?level4 wdt:P279 ?level3.} } }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
} order by ?level1Label ?level2Label ?level3Label ?level4Label

Try it!

--Tagishsimon (talk) 21:19, 25 November 2022 (UTC)

Oh this is amazing, queries can do anything! -wd-Ryan (Talk/Edits) 21:33, 25 November 2022 (UTC)

List of place names in Cornwall, in both English and Cornish language

I've tried getting this Wikidata List (Listeria powered) to work, with no avail. If someone has a minute PLEASE can you correct it? I'm also trying to get the pronounciation (in both languages), but it's above me, as they're language specific!

Much of the data is on Wikidata, but I will try and get a Mix N Match project to get this database on Commons of Cornish names onto WD asap. Any help would be appreciated! THANKS! Meur ras! Llywelyn2000 (talk) 16:45, 24 November 2022 (UTC)

@Llywelyn2000: Probably need to tighten up the spec for the Listeria version of this. I get 15k rows of things in Cornwall (council area), and Listeria creaks north of 3000, has a hard limit of 5000. What direction do you want to take this query?

SELECT ?item ?itemLabel_en ?itemLabel_kw
WHERE
{
  ?item wdt:P131/wdt:P131* wd:Q22338583.
  OPTIONAL {?item rdfs:label ?itemLabel_en. filter(lang(?itemLabel_en)="en") }
  OPTIONAL {?item rdfs:label ?itemLabel_kw. filter(lang(?itemLabel_kw)="kw") }
}

Try it!

Oh. Perhaps restriced to

SELECT ?item ?itemLabel_en ?itemLabel_kw ?dedicated_toLabel_en ?dedicated_toLabel_kw
WHERE
{
  {?item wdt:P131/wdt:P131* wd:Q22338583.} hint:Prior hint:runFirst true.
  ?item wdt:P825 ?dedicated_to. 
  OPTIONAL {?item rdfs:label ?itemLabel_en. filter(lang(?itemLabel_en)="en") }
  OPTIONAL {?item rdfs:label ?itemLabel_kw. filter(lang(?itemLabel_kw)="kw") }
  OPTIONAL {?dedicated_to rdfs:label ?dedicated_toLabel_en. filter(lang(?dedicated_toLabel_en)="en") }
  OPTIONAL {?dedicated_to rdfs:label ?dedicated_toLabel_kw. filter(lang(?dedicated_toLabel_kw)="kw") }
}

Try it!

--Tagishsimon (talk) 02:08, 25 November 2022 (UTC)

Many thanks @Tagishsimon:, but the list should include towns or villages only, ordered by parish. Thanks! I'm sorry I didn't make that clear. Llywelyn2000 (talk) 10:05, 26 November 2022 (UTC)

@Tagishsimon: - Your first example is best, but needs to be limited (for WD Lists) to c. 3,000. Can you suggest a way to do this eg using the starting letter A-D then I could create a second list E-J etc? Thanks! Llywelyn2000 (talk) 11:07, 29 November 2022 (UTC)

Filtering on novalue

I want to list battleships where vessel class (P289) is missing, but am getting those where it is set to <no value>. I was hoping a query like

SELECT DISTINCT ?item ?itemLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
  {
    SELECT DISTINCT ?item WHERE {
      ?item p:P31 ?statement0.
      ?statement0 (ps:P31/(wdt:P279*)) wd:Q182531.
      MINUS {
        ?item p:P289 ?statement2.
        ?statement2 (ps:P289/(wdt:P279*)) _:noValueP289_1.
      }
      MINUS {
        ?item p:P289 ?statement1.
        ?statement1 (ps:P289/(wdt:P279*)) _:anyValueP289_1.
      }
    }
  }
}

Try it!

but that doesn't work, and I'm struggling to find documentation on the anyValue logic to see whether noValue was the correct variant Vicarage (talk) 11:17, 27 November 2022 (UTC)

@Vicarage: Documentation: https://www.mediawiki.org/wiki/Wikibase/Indexing/RDF_Dump_Format#Novalue

SELECT ?item ?itemLabel 
WHERE 
{
  ?item p:P31/ps:P31/wdt:P279* wd:Q182531.
  filter not exists {?item a wdno:P289.}
  filter not exists {?item p:P289 [].}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 11:59, 27 November 2022 (UTC)

Exactly what I wanted. Thanks! Vicarage (talk) 12:09, 27 November 2022 (UTC)

It can, fwiw, be thought of best as 'member of the class of items which have <no value> for whichever property' ... 'a' being shorthand for rdf:type. --Tagishsimon (talk) 12:14, 27 November 2022 (UTC)

Units in plural form using lexemes?

Hi, how i can get formatted unit in plural in swedish form using lexemes? Below is the example query.

SELECT ?place ?placeLabel ?elev ?unit ?unitLabel
WHERE
{
  ?place p:P2044/psv:P2044 ?placeElev.
  ?placeElev wikibase:quantityAmount ?elev.
  ?placeElev wikibase:quantityUnit ?unit.


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

Try it!

Br, -- Zache (talk) 10:20, 29 November 2022 (UTC)

This should work I think. This isn't a very efficient way of doing things by the way. You can get normalized values in meters and simply ignore looking up the words for the units, and instead just manually set the unit words.

SELECT ?place ?placeLabel ?elev ?ulabel #?unit ?prepr ?ulabel_ #?sense ?lexeme ?form
WITH {
  SELECT ?place (SAMPLE(?elev_st_) AS ?elev_st)
  WHERE {
    #?place wdt:P17 wd:Q34 . # Reduce the working set.
    #?place wdt:P31 wd:Q8502 .

    ?place p:P2044 ?elev_st_ .
    ?elev_st_ a wikibase:BestRank .
  }
  GROUP BY ?place
  LIMIT 100
} AS %i
WITH {
  SELECT ?place ?elev_st ?elev ?unit ?ulabel_ (SAMPLE(?prepr_) AS ?prepr) #?sense ?form ?lexeme
  WHERE {
    INCLUDE %i

    ?elev_st psv:P2044 [ wikibase:quantityAmount ?elev; wikibase:quantityUnit ?unit ] .
    ?unit rdfs:label ?ulabel_. FILTER(LANG(?ulabel_)="sv")

    OPTIONAL { 
      ?sense wdt:P5137 ?unit . 
      ?lexeme ontolex:sense ?sense . 
      ?lexeme ontolex:lexicalForm ?form. 
      ?lexeme dct:language wd:Q9027 .
      ?form wikibase:grammaticalFeature wd:Q146786 .
      ?form ontolex:representation ?prepr_ .
    }
  }
  GROUP BY ?place ?elev_st ?elev ?unit ?ulabel_ #?sense ?form ?lexeme
} AS %f
WHERE {
  INCLUDE %f
  BIND(IF(ABS(?elev) >= 2, ?prepr, ?ulabel_) AS ?ulabel)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en" }
}

Try it!

Infrastruktur (talk) 14:39, 29 November 2022 (UTC)

Here's an example of using normalized values (always using metres). English has two plural forms, whilst swedish seems to use the same spelling for singular as plural form.

SELECT ?place ?placeLabel ?elev ?ulabel #?unit
WITH {
  SELECT ?place (SAMPLE(?elev_st_) AS ?elev_st)
  WHERE {
    #?place wdt:P17 wd:Q34 . # Reduce the working set.
    #?place wdt:P31 wd:Q8502 .

    ?place p:P2044 ?elev_st_ .
    ?elev_st_ a wikibase:BestRank .
  }
  GROUP BY ?place
  LIMIT 100
} AS %i
WHERE {
  INCLUDE %i
  ?elev_st psn:P2044 [ wikibase:quantityAmount ?elev; wikibase:quantityUnit ?unit ] .
  BIND(IF(ABS(?elev) >= 2, "meter", "meter") AS ?ulabel)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en" }
}

Try it!

Infrastruktur (talk) 15:23, 29 November 2022 (UTC)

I was trying to figure out how to do this universally. Ie. I get some random unit from SPARQL or in the Lua module and then I should try to render the value. It would be also nice if I could change the language if needed between Finnish and Swedish.

However, I needed to get some push in the right direction on how to do this. This is what i came up based on your example.

SELECT DISTINCT ?item ?itemLabel ?elev ?unit_label #?unit ?sense ?lexeme ?form_1 ?prepr_1 ?form_2 ?prepr_2
WHERE 
{
  BIND(wd:Q13428 as ?item)
  ?item p:P2044/psv:P2044 ?placeElev.
  ?placeElev wikibase:quantityAmount ?elev.
  ?placeElev wikibase:quantityUnit ?unit.
  ?sense wdt:P5137 ?unit . 
  ?lexeme ontolex:sense ?sense .
  ?lexeme dct:language wd:Q1412 . # Finnish
  ?lexeme ontolex:lexicalForm ?form_1. 
  ?form_1 wikibase:grammaticalFeature wd:Q110786 .
  ?form_1 wikibase:grammaticalFeature wd:Q131105 .
  ?form_1 ontolex:representation ?prepr_1 .
  ?lexeme ontolex:lexicalForm ?form_2. 
  ?form_2 wikibase:grammaticalFeature wd:Q146786 .
  ?form_2 wikibase:grammaticalFeature wd:Q131105 .
  ?form_2 ontolex:representation ?prepr_2 .
  BIND(IF(?elev = 1,  ?prepr_1, ?prepr_2) AS ?unit_label)

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}

Try it!

Zache (talk) 16:32, 29 November 2022 (UTC)

This should grab both finnish and swedish labels at the same time. By the way, your check on elevation won't work if elevation is negative or has decimals.

SELECT DISTINCT ?item ?itemLabel ?elev ?unit_label #?dlangLabel ?unit ?sense ?lexeme ?form_1 ?repr_1 ?form_2 ?repr_2
WHERE {
  BIND(wd:Q13428 as ?item)
  ?item p:P2044 ?place_st .
  ?place_st a wikibase:BestRank .
  ?place_st psv:P2044 ?placeElev.
  ?placeElev wikibase:quantityAmount ?elev.
  ?placeElev wikibase:quantityUnit ?unit.
  ?sense wdt:P5137 ?unit . 
  ?lexeme ontolex:sense ?sense .

  ?lexeme dct:language ?dlang . FILTER (?dlang IN (wd:Q1412, wd:Q9027))

  ?lexeme ontolex:lexicalForm ?form_1. 
  ?form_1 wikibase:grammaticalFeature wd:Q110786 .
  ?form_1 wikibase:grammaticalFeature wd:Q131105 .
  OPTIONAL { ?form_1 wikibase:grammaticalFeature ?indef_1 . FILTER (?indef_1 IN (wd:Q53997857)) }
  ?form_1 ontolex:representation ?repr_1 .
  FILTER((?dlang = wd:Q9027 && BOUND(?indef_1)) || (?dlang = wd:Q1412))

  ?lexeme ontolex:lexicalForm ?form_2. 
  ?form_2 wikibase:grammaticalFeature wd:Q146786 .
  ?form_2 wikibase:grammaticalFeature wd:Q131105 .
  OPTIONAL { ?form_2 wikibase:grammaticalFeature ?indef_2 . FILTER (?indef_2 IN (wd:Q53997857)) }
  ?form_2 ontolex:representation ?repr_2 .
  FILTER((?dlang = wd:Q9027 && BOUND(?indef_2)) || (?dlang = wd:Q1412))

  BIND(IF(ABS(?elev) >= 2,  ?repr_2, ?repr_1) AS ?unit_label)

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

Try it!

Infrastruktur (talk) 20:02, 29 November 2022 (UTC)

adding language+script for query output

How may I update the query, https://w.wiki/63ms, that returns zh to include script code? For instance, Chinese traditional script, zh-Hant? Thank you. jshieh (talk) 17:59, 30 November 2022 (UTC)

Is this what you had in mind? Or did you mean to match objects of any kind of chinese and report which variant?

SELECT ?s ?label (LANG(?label) AS ?lc)
Where {
    ?s ?p "Smithsonian Institution"@zh .
    ?s rdfs:label ?label . FILTER (langmatches(lang(?label), "zh"))
}
LIMIT 100

Try it!

Infrastruktur (talk) 18:24, 30 November 2022 (UTC)

In case you meant the latter, here's a query for that too.

SELECT ?s ?o (LANG(?o) AS ?lc)
Where {
  {
    SELECT DISTINCT ?lcs WHERE { 
      [] wdt:P424 ?lcs . FILTER(SUBSTR(?lcs, 1, 2) = "zh")
    }
  }
  BIND ( STRLANG("Smithsonian Institution", ?lcs) AS ?o)
  ?s ?p ?o . hint:Prior hint:runLast true .
}
LIMIT 100

Try it!

Infrastruktur (talk) 18:55, 30 November 2022 (UTC)