Wikidata:Request a query/Archive/2021/10

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

Theses, which institution has most?

I don't know how to count stuff, can I get a query to find which institutions have the most theses (Q1266946 and subclasses) submitted to them (P4101)? Thanks.-DrThneed (talk) 04:01, 30 September 2021 (UTC)

@DrThneed: This.

SELECT ?institution ?institutionLabel (count(distinct ?item) as ?count)
WHERE 
{
  ?item wdt:P4101 ?institution. 
  hint:Prior hint:runFirst true.
  ?item wdt:P31/wdt:P279* wd:Q1266946 .
  hint:Prior hint:gearing "forward".
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
} group by ?institution ?institutionLabel order by desc(?count)

Try it!

--Tagishsimon (talk) 09:57, 30 September 2021 (UTC)

@DrThneed, Tagishsimon:The use of a property path in ?item wdt:P31/wdt:P279* wd:Q1266946 gives duplicate results and thus a too high count. I added the distinct keyword to the argument for the count function to avoid that. --Dipsacus fullonum (talk) 19:43, 30 September 2021 (UTC)

@Dipsacus fullonum, Tagishsimon: Thank you both, that's exactly what I needed, you're marvellous, as always! DrThneed (talk) 20:41, 30 September 2021 (UTC)

Two threads up, I'm explaining exactly that duplicate snafu. This thread I'm making it. Oh well. :) --Tagishsimon (talk) 23:30, 30 September 2021 (UTC)

That's alright, I wasn't actually interested in absolute numbers anyway, just in finding out which institutions have got a decent amount of data uploaded so I can look at how it's been done. Ta! DrThneed (talk) 02:14, 1 October 2021 (UTC)

Extracting population data from two given years

Hello! I'm stuck here. I would like to extract population data for the last available statement (let's make simple: 2020) and from 10 years ago (2010). I don't know if I could automatically get the "last" and "last-10". I know how to extract the 2020 by hand, but don't know how to get both data. I have tried with ps: and pq: but... I don't know how to specify a year at the pq: level. So I'm here:

SELECT ?item ?itemLabel ?population ?year WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item wdt:P31 wd:Q2074737;
    (wdt:P131*) wd:Q93366.
  ?item p:P1082 ?pop.
  ?pop ps:P1082 ?population.
  ?pop pq:P585 ?date.
  BIND(YEAR(?date) as ?year).
  FILTER CONTAINS(?year, "2020").
}

Try it!

Thans! -Theklan (talk) 12:55, 1 October 2021 (UTC)

@Theklan: This seems to work?

SELECT ?item ?itemLabel ?population ?year WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item wdt:P31 wd:Q2074737;
    (wdt:P131*) wd:Q93366.
  ?item p:P1082 ?pop.
  ?pop ps:P1082 ?population.
  ?pop pq:P585 ?date.
  BIND(YEAR(?date) as ?year).
  VALUES ?YY {"2010" "2020"}
  FILTER CONTAINS(?year, ?YY).
}

Try it!

--Tagishsimon (talk) 15:44, 1 October 2021 (UTC)

Wouldn't it be possible to have the ?pop2010 and ?pop2020 as columns? In that way doing operations would be possible. I have tried doing BIND(FILTER...) as ?pop2010) but this construction is not possible. Theklan (talk) 15:46, 1 October 2021 (UTC)

: @Theklan: (edit conflict) If you want data for the years 2020 and 2010 you can just change the filter line in your query to

FILTER (?year = 2020 || ?year = 2010)

But what I think you are asking for is this:

SELECT ?item ?itemLabel ?population ?year ?population10 ?year10
WHERE
{
  ?item wdt:P31 wd:Q2074737;
    (wdt:P131*) wd:Q93366.
  ?item p:P1082 ?pop.
  ?pop ps:P1082 ?population.
  ?pop pq:P585 ?date.
  ?pop wikibase:rank wikibase:PreferredRank.
  BIND(YEAR(?date) as ?year).
  BIND (?year - 10 AS ?years_minus_10)
  OPTIONAL
  {
    ?item p:P1082 ?pop10.
    ?pop10 ps:P1082 ?population10.
    ?pop10 pq:P585 ?date10.
    BIND(YEAR(?date10) as ?year10).
    FILTER (?year10 = ?years_minus_10)
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Note that this query depends the latest population data having preferred rank, as it search for rank rather looking for the newest data. --Dipsacus fullonum (talk) 15:50, 1 October 2021 (UTC)

MAGIC! -Theklan (talk) 15:56, 1 October 2021 (UTC)

Que la consulta presente todos los campos requeridos, incluidos la fecha y el genero

SELECT ?pintura ?creador ?creadorLabel ?imagen ?museoLabel ?museoLocation ?museoLocationLabel ?coordenadas ?sexo_o_g_nero ?sexo_o_g_neroLabel ?fecha_de_publicaci_n WHERE {

 ?pintura (wdt:P31/(wdt:P279*)) wd:Q3305213;
   wdt:P170 ?creador.
 ?creador wdt:P27 wd:Q38.
 ?pintura wdt:P276 ?museo.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,es". }
 OPTIONAL { ?museo wdt:P625 ?coordenadas. }
 OPTIONAL { ?museo wdt:P131 ?museoLocation. }
 OPTIONAL { ?pintura wdt:P18 ?imagen. }
 OPTIONAL { ?pintura wdt:P21 ?sexo_o_g_nero. }
 OPTIONAL { ?pintura wdt:P577 ?fecha_de_publicaci_n. }

} LIMIT 100

I assume you mean the gender of the painter instead of the painting, so change ?pintura wdt:P21 ?sexo_o_g_nero to ?creador wdt:P21 ?sexo_o_g_nero. Also paintings usually don't have publication date (P577). Maybe you meant inception (P571). --Dipsacus fullonum (talk) 06:19, 2 October 2021 (UTC)

Items that are "instance of" two things at the same time

I think I might be going mad, but should this not show me everything that instance of (P31) both volume (Q1238720) and scientific journal (Q5633421)?

# things that are both somehow "volumes" and "scientific journals"
SELECT ?volume ?volumeLabel WHERE {
   ?volume wdt:P31 wd:Q1238720 .
   ?volume wdt:P31 wd:Q5633421 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

And yet it does not find The Orchid Album (Q104551298), but it does find Popular Science Monthly, Volume 2 (Q105369310).

This does work:

SELECT DISTINCT ?item ?itemLabel WHERE {
  ?item p:P31 [ ps:P31 wd:Q1238720 ]. 
  ?item p:P31 [ ps:P31 wd:Q5633421 ].
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
}

Try it!

Which makes sense, but why does the top query only work for that one item? Inductiveload (talk) 12:54, 5 October 2021 (UTC)

@Inductiveload: It's rank. In Q104551298 one of the two values is set to preferred, so wdt: only sees it, and not the other value. p:/ps: sees all.

Stick this in your vector.css - much easier to spot rank issues if they're in red or green:

.wb-deprecated { /* deprecated claims with red-ish background */

background-color:#FFE0E0;

}

.wb-preferred { /* preferred claims with green-ish background */

background-color:#E0FFE0;

}

--Tagishsimon (talk) 13:21, 5 October 2021 (UTC)

I see. IDK what the rank is supposed to mean in that context! Is there a way to make wdt: immune to rank, or that a feature, and p:/ps: is the correct way to ignore it? Thanks for the CSS :-) Inductiveload (talk) 13:26, 5 October 2021 (UTC)

wdt: is doing what it's designed to do, so p:/ps: is the cure. Once down that path, you can look at, for instance, the actual rank (e.g. to remove deprecated values) or, much the same thing, look out for wikibase:bestRank (iirc) statements.

As to what the rank is seeking to convey in that instance, I've no clue, either, but for your report, it's the garbage in which results in garbage out. Ideally a change from normal rank should be qualified with a reason but... --Tagishsimon (talk) 13:36, 5 October 2021 (UTC)

Got it, thank you! Inductiveload (talk) 14:08, 5 October 2021 (UTC)

Q-ID for known string of item label

Hi,

I try to get the ID-Q-Number of persons where the item label is the persons name. I tried this:

SELECT ?person
      WHERE { ?person wdt:P734 ?familyname .
                            
       Values ?familyname  {'Bertha Krupp'}.  
       SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de".}

Try it!

Properly it's super easy... However I don't manage it right now. Thank you for help or hint! – The preceding unsigned comment was added by EvaSeidlmayer (talk • contribs) at 15:38:33 (UTC).

You should probably just use the item for "Krupp" as a family name: Krupp (Q37069298):

SELECT ?person ?personLabel WHERE {
  VALUES ?familyNameItem { wd:Q37069298 }
  ?person wdt:P734 ?familyNameItem .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de" }
}

Try it!

However, if you had to use a string, you can do it be selecting the family name(s) with a suitable native label (P1705):

SELECT ?person ?personLabel WHERE {
  ?person wdt:P734 ?familyName .
  ?familyName wdt:P1705 "Krupp"@mul
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de" }
}

Try it!

~~I am unsure if it is always a safe bet to use the mul there, however.~~ Wikidata:WikiProject_Names/Properties#Family_name indicates that family names should always have a mul. However, if you have the QID for "Krupp as a family name" already, I'd use that anyway.

Also I see I misread the question and though you were looking for the family name only!

Inductiveload (talk) 15:42, 5 October 2021 (UTC)

I would use the actual labels and query like this:

SELECT ?item WHERE { ?item rdfs:label 'Bertha Krupp'@en }

Try it!

This query yields all items with the exact English label "Bertha Krupp". It works with other languages as well, just change the language code after the @ sign. —MisterSynergy (talk) 15:50, 5 October 2021 (UTC)

ha! @MisterSynergy great this is what I was looking for! Thank you a lot @Inductiveload this @mult was new to me!

EvaSeidlmayer (talk) 15:54, 5 October 2021 (UTC)

Note that this will not find "Bertha B. Krupp" or "Bertha Krupp von Bohlen und Halbach", but one of these will:

# People with family name "Krupp" and given name "Bertha"
SELECT DISTINCT ?person ?personLabel WHERE {
  ?person wdt:P734 wd:Q37069298 .
  ?person wdt:P735 wd:Q16420820 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de" }
}

Try it!

or

# People with family name "Krupp" and "Bertha" somewhere in the label
SELECT DISTINCT ?person ?personLabel WHERE {
  ?person wdt:P734 wd:Q37069298 .
  ?person rdfs:label ?label .
  FILTER REGEX(?label, "Bertha")
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de" }
}

Try it!

Whatever works! Inductiveload (talk) 16:07, 5 October 2021 (UTC)

optional WHERE statement?

Hi, using an f-String for replacement in Python, I loop through a list of historical persons trying to get their family information. Unfortunately, my query only gives me information if the person has an entry for her/his father AND an entry for her/his mother AND an entry for one or more child(ren). Since many persons do not have entries for one or the other, I don't receive any information from the Wikidata.

How can I make the WHERE statements facultative/optional? I mean like "try; except: continue" . I would like to get something like an empty string, if there is no information in the database.

In the example, Tilo Wilmowsky (Q2433711) does not have an entry for his mother.

SELECT ?personLabel ?motherLabel ?mother ?fatherLabel ?father ?childLabel ?child 
            WHERE { VALUES ?person { wd:Q2433711 } 
            ?person wdt:P22 ?father . 
            ?person wdt:P25 ?mother . 
            ?person wdt:P40 ?child .
            SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de" }
            }

Try it!

Right now I use a workaround with three separate queries. However, you might know a more elegant way... Thank you in advance!!

EvaSeidlmayer (talk) 11:44, 6 October 2021 (UTC)

@EvaSeidlmayer: I think this is what you're after. It's now optional if the person has any relatives; you'll get the person's label back if they have one. Presumably you get a blank row back if there is not label; if that's a problem, maybe add ?person to your select since there is always a value for that.

SELECT ?personLabel ?motherLabel ?mother ?fatherLabel ?father ?childLabel ?child 
            WHERE { VALUES ?person { wd:Q2433711 } 
            OPTIONAL { ?person wdt:P22 ?father . } 
            OPTIONAL { ?person wdt:P25 ?mother . }
            OPTIONAL { ?person wdt:P40 ?child . }
            SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de" }
            }

Try it!

hth --Tagishsimon (talk) 11:39, 6 October 2021 (UTC)

fantastic! ...and much more elegant!! EvaSeidlmayer (talk) 13:10, 6 October 2021 (UTC)

Tagishsimon wrote "Presumably you get a blank row back if there is not label". No, the label service will always return a value. If no label exist in any of the selected languages, it will return the item title (i.e. the Q number). --Dipsacus fullonum (talk) 20:33, 7 October 2021 (UTC)

astronautas

Quiero una tabla con todos los astronautas y algunos datos. Pero no soy capaz...

gracias SELECT ?item ?itemLabel ?nombre ?pa_s_de_nacionalidad ?pa_s_de_nacionalidadLabel ?nombreLabel ?misi_n_del_astronauta ?misi_n_del_astronautaLabel ?premio_recibido ?premio_recibidoLabel ?fecha_de_nacimiento ?inicio_del_periodo_de_actividad WHERE {

 ?item wdt:P31 wd:Q5.
 OPTIONAL {  }
 ?item wdt:P106 wd:Q11631.
 OPTIONAL { ?item wdt:P2561 ?nombre. }
 OPTIONAL { ?item wdt:P27 ?pa_s_de_nacionalidad. }
 OPTIONAL { ?item wdt:P450 ?misi_n_del_astronauta. }
 OPTIONAL { ?item wdt:P2873 ?premio_recibido. }
 OPTIONAL { ?item wdt:P569 ?fecha_de_nacimiento. }
 OPTIONAL { ?item wdt:P2031 ?inicio_del_periodo_de_actividad. }

}

@Quimper200: If I understand correctsly, you are missing a call to the label service to get labels:

SELECT ?item ?itemLabel ?nombre ?pa_s_de_nacionalidad ?pa_s_de_nacionalidadLabel ?nombreLabel ?misi_n_del_astronauta ?misi_n_del_astronautaLabel ?premio_recibido ?premio_recibidoLabel ?fecha_de_nacimiento ?inicio_del_periodo_de_actividad WHERE {

 ?item wdt:P31 wd:Q5.
 OPTIONAL {  }
 ?item wdt:P106 wd:Q11631.
 OPTIONAL { ?item wdt:P2561 ?nombre. }
 OPTIONAL { ?item wdt:P27 ?pa_s_de_nacionalidad. }
 OPTIONAL { ?item wdt:P450 ?misi_n_del_astronauta. }
 OPTIONAL { ?item wdt:P2873 ?premio_recibido. }
 OPTIONAL { ?item wdt:P569 ?fecha_de_nacimiento. }
 OPTIONAL { ?item wdt:P2031 ?inicio_del_periodo_de_actividad. }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],es,en". } # Helps get the label in your language, if not, then en language
}

Try it!

--Tagishsimon (talk) 11:22, 9 October 2021 (UTC)

Finding items with articles in any Wikipedia but no in other projects (Documentation about schema:X usage)

Hi there,

I'm trying to get people from a given country with sitelinks to Wikipedia, but not to other Wikimedia projects.

With this query I can get all people from Q298, with sitelinks;

SELECT DISTINCT ?item ?itemLabel ?itemDescription  ?article 
WHERE {
  ?item wdt:P31 wd:Q5;
    wdt:P27 wd:Q298.
    ?article schema:about ?item
    SERVICE wikibase:label { bd:serviceParam wikibase:language "es,en". }

}

Try it!

And with this I can get all ppl with sitelinks on English Wikipedia:

SELECT DISTINCT ?item ?itemLabel ?itemDescription  ?article 
WHERE {
  ?item wdt:P31 wd:Q5;
    wdt:P27 wd:Q298.
    ?article schema:about ?item.
     ?article schema:isPartOf <https://en.wikipedia.org/>.
    SERVICE wikibase:label { bd:serviceParam wikibase:language "es,en". }

}

Try it!

My questions really are two:

How can I use wild cards on these queries to do something *.wikipedia.org
Where can I find documentation about the "schema:" syntaxis? I've just found examples, but not a explanation how to use it.

Thanks in advance! Diego (WMF) (talk) 02:43, 28 September 2021 (UTC)

@Diego (WMF): As to documentation, we don't go overboard on that sort of thing, but there's a definition of the structure in the RDF dump format page; and the data model diagram helps a little bit, at least by showing ?article in relation to ?item.

Here are a couple of answers for you, dealing mainly with the difference between any sitelinks, and sitelinks to language wikipedias.

case: has en wiki article but no other sitelinks to any wikimedia property. In this query we cheat and use the wikibase:sitelinks predicate to check there is only one sitelink.

SELECT DISTINCT ?item ?itemLabel ?itemDescription  ?article WHERE 
{
  ?item wdt:P27 wd:Q298.
  ?item wdt:P31 wd:Q5.
  ?article schema:about ?item.
  ?article schema:isPartOf <https://en.wikipedia.org/>.
  ?item wikibase:sitelinks "1"^^xsd:integer.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "es,en". }
} limit 10

Try it!

case: has en wiki article but no other language wiki article (but it might have e.g. commons, wikisource). The main trick here is to find a way to exclude items that have sitelinks to other language wikipedias than EN wiki, without reaching for a FILTER(), which would be hideously expensive at runtime. Queries restricted to triple patterns will always be orders of magnitude quicker. This is, in effect, your wildcard; here's my devious suggestion:

SELECT DISTINCT ?item ?itemLabel ?itemDescription  ?article WHERE 
{
  ?item wdt:P27 wd:Q298.
  ?item wdt:P31 wd:Q5.
  ?article schema:about ?item.
  ?article schema:isPartOf <https://en.wikipedia.org/>. # there is an en wiki article
  FILTER NOT EXISTS                            # exclude items if
  {
    ?article1 schema:about ?item.              # there may be another article
    ?article1 schema:inLanguage ?lang .        # in a language
    filter not exists {wd:Q328 wdt:P424 ?lang} # which language (long story short) is not "en"
    ?article1 schema:isPartOf ?thing.          # which article is part of something
    ?thing wikibase:wikiGroup "wikipedia" .    # and that thing is a language wiki
  }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "es,en". }
} limit 10

Try it!

hth --Tagishsimon (talk) 03:51, 28 September 2021 (UTC)

Thanks, all this is very helpful. Diego (WMF) (talk) 22:07, 30 September 2021 (UTC)

But I'm still curious where that "wikiGroup" comes from. I don't see it in the RDF page you provided. There are more keys similar to that one? Thanks in advance Diego (WMF) (talk) 22:14, 30 September 2021 (UTC)

@Diego (WMF): It seems to be the only wikiBase: predicate attached to the site_url <en.wikipedia.org> - SPARQL. There seem to be 17 different groups into which wikimedia websites are divided - SPARQL.

"wikiGroup" is documented in the Sitelinks section of the RDF dump page, at the foot of the first block of code, but as I inferred earlier, datamodel documentation is v.terse.

There are a heaps of other wikibase: predicates - it seems to be the domain (?) for WD predicates for concepts not well served by pre-existing domains such as rdfs: owl:, skos:, &c &c. All the wikibase: predicates are documented on the dump page, but per the query above, none serve as predicates of the site_url. At risk of going wider than your interest, some I regularly reach for are wikibase:statements and wikibase:sitelinks, which provide counts of statements & sitelinks on items. And the wikibase: predicates of a property [1], useful for interrogating predicates used in items; example. --Tagishsimon (talk) 23:28, 30 September 2021 (UTC)

Thank you very much @Tagishsimon, Diego (WMF) (talk) 10:16, 12 October 2021 (UTC)

National flags ordered by population

I don’t know why this simple query always times out. Any idea why? It should return a list of countries, sorted by decreasing population, with their national flags.

SELECT ?country ?countryLabel ?flag ?population

WHERE {
    ?country wdt:P31 wd:Q6256;
             wdt:P1082 ?population.

    ?flag wdt:P31 wd:Q186516;
          wdt:P1001 ?country.
  
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

ORDER BY DESC(?population)
LIMIT 10

Try it!

@Jollywatt: It does seem a little slow. Putting it into a named subquery form seems to solve it, but without any real insight into the problem. In other news, ?country wdt:P31 wd:Q6256 may be a poor way of selecting countries; I suspect some countries have sovereign state (Q3624078) with a preferred rank, which will mean there is no wdt:P31 wd:Q6256 for that item. And both country and sovereign state may well return defunct countries, which may not be what you want. My quick & dirty method for getting a list of countries, avoiding these issues, is to select members of the United Nations, fwiw.

SELECT ?country ?countryLabel ?flag ?population with { 
  SELECT ?country ?flag ?population
  WHERE {
    ?country wdt:P31 wd:Q6256; 
    wdt:P1082 ?population. 

    ?flag wdt:P31 wd:Q186516;
          wdt:P1001 ?country.
  } } as %i
WHERE
{
  INCLUDE %i
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 11:23, 9 October 2021 (UTC)

@Jollywatt: I cannot say exactly why your query times out, but queries involving country items often are slow due to the very large size of these items (many statements). The query will be much faster (about 1 second) if you use flag (P163) instead of instance of (P31) national flag (Q186516) and applies to jurisdiction (P1001), and will also return more results at the same time:

SELECT ?country ?countryLabel ?flag ?population

WHERE {
    ?country wdt:P31 wd:Q6256;
             wdt:P1082 ?population;
             wdt:P163 ?flag.

    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?population)

Try it!

--Dipsacus fullonum (talk) 16:14, 9 October 2021 (UTC)

Thanks guys! It still remains mysterious, because the query itself doesn’t seem very complex to me (and there aren’t that many present day countries — about 200). Anyway, the following is what I’ve gone with (it grabs images of the countries' flags). You can then use the Wikimedia commons API to grab the `.svg` files themselves, as described [here](https://stackoverflow.com/a/46441957/1937384).

This is useful for people wanting to learn some geography and memorize national flags in the most effective order!

# Countries by descending population with images of their flags
SELECT ?country ?countryLabel ?flagImage ?population
WHERE {
    ?country wdt:P31 wd:Q6256;
             wdt:P1082 ?population;
             wdt:P163 [ wdt:P18 ?flagImage ].
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?population)

Try it!

Jollywatt (talk) 03:09, 11 October 2021 (UTC)

Bio pages in ES WP, not in LAD WP

Wikidata item Q8888860 (Category:Uruguayan Jews) has 48 member pages in the Spanish (ES) WP. How can I check whether those (biography) pages have an article in the Ladino (LAD) WP? Have I provided enough information to formulate a query? Thank you! -- Deborahjay (talk) 10:54, 12 October 2021 (UTC)

@Deborahjay: This query does more than you asked for. It finds all items in the category Category:Uruguayan Jews (Q8888860) in any Wikimedia project, and for each of them gives the name of the Ladino aricle if it exists.

SELECT ?item ?itemLabel ?lad_article ?lad_title ?found_in
WITH
{
  SELECT 
    ?item
    (GROUP_CONCAT(?endpoint; SEPARATOR=", ") AS ?found_in)
  WHERE
  {
    ?category schema:name "Categoría:Judíos de Uruguay"@es .
    ?category schema:isPartOf <https://es.wikipedia.org/> .
    ?category schema:about ?category_item .
    ?other_cat schema:about ?category_item .
    ?other_cat schema:isPartOf ?wikimedia_site .
    ?other_cat schema:name ?category_title .
    BIND (STRBEFORE(STRAFTER(STR(?wikimedia_site), "https://"), "/") AS ?endpoint)
    OPTIONAL
    {
      SERVICE wikibase:mwapi
      {
        bd:serviceParam wikibase:endpoint ?endpoint .
        bd:serviceParam wikibase:api "Generator" .
        bd:serviceParam mwapi:generator "categorymembers" .
        bd:serviceParam mwapi:gcmtitle ?category_title .
        bd:serviceParam mwapi:gcmlimit "max" .
        ?item wikibase:apiOutputItem mwapi:item .
      }
    }
    FILTER BOUND (?item)
  }
  GROUP BY ?item
} AS %get_items
WHERE
{
  hint:Query hint:optimizer "None" .
  INCLUDE %get_items
  OPTIONAL
  {
    ?lad_article schema:about ?item .
    ?lad_article schema:isPartOf <https://lad.wikipedia.org/> .
    ?lad_article schema:name ?lad_title .
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Dipsacus fullonum (talk) 13:15, 12 October 2021 (UTC)

The "added value" of other WP language projects does expand the content in a meaningful way, including a focus on improving the WD items with some missing labels. I started with the ES WP category because it had more pages than the EN WP. Also the similarity between Spanish and Ladino will make those pages more likely sources for translation. Looking forward to progress! -- Deborahjay (talk) 13:27, 12 October 2021 (UTC)

I made it that because I used as model a query I sometimes use on dawiki to find existing articles not in some category, but where the corresponding article in some other Wikipedia are in that Wikipedia's corresponding category. That is usually a sign of missing categorization. And it was easiest to leave in searching in all categories. --Dipsacus fullonum (talk) 15:06, 12 October 2021 (UTC)

How to get a list of humans, including some of their properties, who died on a particular date?

I am so happy I found this page! I've been improving and adding to the list of notable deaths for some years now and I only now realized that the Wikidata db could save me oceans of time.
I already created some software to semi-automate the creation of the older monthly deaths pages. I still need a query that will return a list of humans whose date of death matches the input (date) variable.

Per human next properties should be returned:

item
itemLabel
itemDescription
sitelinks
item date of birth
item cause of death (including references)
item manner of death (including references)

Preferably I'd like to sort on surname but I have not found that data item property.

This is how for I got myself (input death date is 25 Aug. 2001):

SELECT ?item ?itemLabel ?itemDescription ?sitelinks where
{
  ?item wdt:P31 wd:Q5;
  wikibase:sitelinks ?sitelinks.
  ?item p:P570 ?statement_0.
  ?statement_0 psv:P570 ?statementValue_0.
  ?statementValue_0 wikibase:timePrecision ?precision_0.
  FILTER(?precision_0 >= 11 )
  ?statementValue_0 wikibase:timeValue ?P570_0.
  BIND("+2001-08-25T00:00:00Z"^^xsd:dateTime AS ?P570_0)
  SERVICE wikibase:label {bd:serviceParam wikibase:language "en"}
 }
ORDER BY DESC(?sitelinks)

Try it!

So I don't know how to include the subjects's birth date, manner of death and cause of death, including references (if any).

Thank a lot in advance! Regards, Mill 1 (talk) 13:50, 12 October 2021 (UTC)

@Mill 1: It is complicated to include references for two properties because each item can have multiple statements for cause of death and manner of death, and each of these statements can have multiple references each consisting of multiple reference statements. The simple solution is get a result for each reference statement, but you will get a lot of results for the same items when they have many references with many reference statements. That solution is like this:

SELECT
  ?item ?itemLabel ?itemDescription ?sitelinks ?date_of_birth
  ?cause_of_deathLabel ?cod_ref_propLabel ?cod_ref_valueLabel
  ?manner_of_deathLabel ?mod_ref_propLabel ?mod_ref_valueLabel
WHERE
{
  ?item wdt:P31 wd:Q5 .
  ?item wdt:P570 "+2001-08-25T00:00:00Z"^^xsd:dateTime .
  ?item wikibase:sitelinks ?sitelinks .
  OPTIONAL { ?item wdt:P569 ?date_of_birth . }
  OPTIONAL
  {
    ?item p:P509 ?cod_stm .
    ?cod_stm ps:P509 ?cause_of_death .
    OPTIONAL
    {
      ?cod_stm prov:wasDerivedFrom ?cod_ref .
      ?cod_ref ?cod_ref_pr ?cod_ref_value .
      ?cod_ref_prop wikibase:reference ?cod_ref_pr .
    }
  }
  OPTIONAL
  {
    ?item p:P1196 ?mod_stm .
    ?mod_stm ps:P1196 ?manner_of_death .
    OPTIONAL
    {
      ?mod_stm prov:wasDerivedFrom ?mod_ref .
      ?mod_ref ?mod_ref_pr ?mod_ref_value .
      ?mod_ref_prop wikibase:reference ?mod_ref_pr .
    }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
}
ORDER BY DESC(?sitelinks)

Try it!

To get a better overview you can group by e.g. each statement for cause of death and then by each statement for manner of death. It can be done like this:

SELECT
  ?item ?itemLabel ?itemDescription ?sitelinks ?date_of_birth
  ?cod ?cod_refs ?mod ?mod_refs
WITH
{
  SELECT
    ?item ?cod_stm (SAMPLE(?codLabel) AS ?cod)
    (GROUP_CONCAT(CONCAT(?cod_ref_propLabel, ": ", ?cod_ref_valueLabel); SEPARATOR=", ") AS ?cod_refs)
  WHERE
  {
    ?item wdt:P31 wd:Q5 .
    ?item wdt:P570 "+2001-08-25T00:00:00Z"^^xsd:dateTime .
    OPTIONAL
    {
      ?item p:P509 ?cod_stm .
      ?cod_stm ps:P509 ?cod .
      OPTIONAL
      {
        ?cod_stm prov:wasDerivedFrom ?cod_ref .
        ?cod_ref ?cod_ref_pr ?cod_ref_value .
        ?cod_ref_prop wikibase:reference ?cod_ref_pr .
      }
    }
    SERVICE wikibase:label
    {
      bd:serviceParam wikibase:language "en" .
      ?cod rdfs:label ?codLabel .
      ?cod_ref_prop rdfs:label ?cod_ref_propLabel .
      ?cod_ref_value rdfs:label ?cod_ref_valueLabel .
    }
  }
  GROUP BY ?item ?cod_stm
} AS %get_cod_refs
WITH
{
  SELECT
    ?item ?cod_stm ?cod ?cod_refs (SAMPLE(?modLabel) AS ?mod)
    (GROUP_CONCAT(CONCAT(?mod_ref_propLabel, ": ", ?mod_ref_valueLabel); SEPARATOR=", ") AS ?mod_refs)
  WHERE
  {
    INCLUDE %get_cod_refs
    OPTIONAL
    {
      ?item p:P1196 ?mod_stm .
      ?mod_stm ps:P1196 ?mod .
      OPTIONAL
      {
        ?mod_stm prov:wasDerivedFrom ?mod_ref .
        ?mod_ref ?mod_ref_pr ?mod_ref_value .
        ?mod_ref_prop wikibase:reference ?mod_ref_pr .
      }
    }
    SERVICE wikibase:label
    {
      bd:serviceParam wikibase:language "en" .
      ?mod rdfs:label ?modLabel .
      ?mod_ref_prop rdfs:label ?mod_ref_propLabel .
      ?mod_ref_value rdfs:label ?mod_ref_valueLabel .
    }
  }
  GROUP BY ?item ?mod_stm ?cod_stm ?cod ?cod_refs
} AS %get_mod_refs
WHERE
{
  INCLUDE %get_mod_refs
  ?item wikibase:sitelinks ?sitelinks .
  OPTIONAL { ?item wdt:P569 ?date_of_birth . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
}
ORDER BY DESC(?sitelinks)

Try it!

This query still have problems. It will aggregate reference statements from different references to the same statements together, and still give multiple results when an item has multiple statements for cause and/or manner of deaths. To solve this you will need additional levels of grouping. But this query will probably be good enough for most cases. --Dipsacus fullonum (talk) 07:02, 13 October 2021 (UTC)

Get all the possible values of "instance of" property

Hello, What is the query to get all the possible values of "instance of" property?

Thanks,

This seems to work:

SELECT ?P31 ?P31Label WITH {
  SELECT DISTINCT ?P31 WHERE
{
  ?item wdt:P31 ?P31. 
  } } as %i
WHERE
{
  INCLUDE %i
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}

Try it!

--Tagishsimon (talk) 15:30, 16 October 2021 (UTC)

I should say, the basic query is as shown below, and all of the rest is about getting labels for the P31 values whilst avoiding a timeout.

SELECT DISTINCT ?P31 WHERE
{
  ?item wdt:P31 ?P31. 
}

Try it!

--Tagishsimon (talk) 15:32, 16 October 2021 (UTC)

Finding Wikisource pages that are not connected to a Wikipedia page

I'm trying to figure out a way to see if we can detect Wikisource pages that don't have a Wikipedia page. I have the following naive query, but this does not consider the fact that many WS pages connect to the edition, and the WP/Commons pages connect to the work.

# Wikisource-linked items without Wikipedia links
SELECT ?work ?page_titleWS WHERE {

  ?work wdt:P31/wdt:P279* wd:Q17537576 .

  ?wsarticle schema:about ?work;
             schema:isPartOf <https://en.wikisource.org/>;
             schema:name ?page_titleWS.
  
  MINUS {
    ?wparticle schema:about ?work;
      schema:isPartOf <https://en.wikipedia.org/>;
      schema:name ?page_titleWP.
  }
}
LIMIT 50

Try it!

As is my new tradition, I have run head-first into query timeouts when trying to do anything involving edition or translation of (P629). Is there any way to figure out a list of Wikisource pages that are not connected to Wikipedia, either directly, or via a parent work through a edition or translation of (P629) relation? Inductiveload (talk) 09:47, 14 October 2021 (UTC)

@Inductiveload: Looks like the normal optimiser fail; uncountable millions of items are a subclass of a creative work and, presumably, the report engine chooses to look for all of these first. The named subquery is your friend, presenting only the results of the schema.about enquiries to the P31/P279 bottom check.

# Wikisource-linked items without Wikipedia links
SELECT ?work ?page_titleWS WITH {
  SELECT ?work ?page_titleWS WHERE {
    ?wsarticle schema:about ?work;
               schema:isPartOf <https://en.wikisource.org/>;
               schema:name ?page_titleWS.
    MINUS { ?wparticle schema:about ?work;
                       schema:isPartOf <https://en.wikipedia.org/>. }
  } } as %i
WHERE {
  INCLUDE %i
  ?work wdt:P31/wdt:P279* wd:Q17537576 . hint:Prior hint:gearing "forward" .   
  }

Try it!

--Tagishsimon (talk) 13:42, 14 October 2021 (UTC)

Very nice! But is there any practical way to query via the edition or translation of (P629) relationship. I.e. (WS page)→Q(edition)–P629→Q(work)→(missing WP article)? I can manage with an actual script if needed, but a SPARQL query would be more efficient, if a bit mind-bending! Inductiveload (talk) 21:41, 15 October 2021 (UTC)

@Inductiveload: Sure it's possible. Less sure what 'it' is, exactly. Below I look for the P629 of the ?wsarticle ?work and knock out any P629s that have an EN wiki article. So the ?wsarticle ?work has no EN article, does have a P629 relationship, and its P629 has no EN wiki article.

# Wikisource-linked items without Wikipedia links
SELECT ?work ?page_titleWS WITH {
  SELECT ?work ?page_titleWS WHERE {
    ?wsarticle schema:about ?work;
               schema:isPartOf <https://en.wikisource.org/>;
               schema:name ?page_titleWS.
    MINUS { ?wparticle schema:about ?work;
                       schema:isPartOf <https://en.wikipedia.org/>. }
  } } as %i
WHERE {
  INCLUDE %i
  ?work wdt:P31/wdt:P279* wd:Q17537576 . hint:Prior hint:gearing "forward" .   
  ?work wdt:P629 ?P629 . 
  MINUS { ?wparticle_P629 schema:about ?P629;
                       schema:isPartOf <https://en.wikipedia.org/>. }
  } group by ?work ?page_titleWS

Try it!

and then in this one, we include all ?wsarticle / ?work items that have no EN wiki, and if they have a P629 with an EN wiki article, we remove them

# Wikisource-linked items without Wikipedia links
SELECT ?work ?page_titleWS  WITH {
  SELECT ?work ?page_titleWS WHERE {
    ?wsarticle schema:about ?work;
               schema:isPartOf <https://en.wikisource.org/>;
               schema:name ?page_titleWS.
    MINUS { ?wparticle schema:about ?work;
                       schema:isPartOf <https://en.wikipedia.org/>. }
  } } as %i
WHERE {
  INCLUDE %i
  ?work wdt:P31/wdt:P279* wd:Q17537576 . hint:Prior hint:gearing "forward" .   
  OPTIONAL {?work wdt:P629 ?P629 . 
  MINUS { ?wparticle_P629 schema:about ?P629;
                       schema:isPartOf <https://en.wikipedia.org/>. } }
  } group by ?work ?page_titleWS

Try it!

(I've used group by ?work ?page_titleWS, btw, b/c I think some ?work have multiple P629s, which throws the row count off when trying to compare the 178,263 rows from the first query, with the 177,569 from that last query.) Not sure if either of these are the directions you wanted to go in. --Tagishsimon (talk) 22:59, 16 October 2021 (UTC)

Wikimedia categories in bnwiki

I need a query contains instance of (P31): Wikimedia category (Q4167836) and

has only one sitelink to Bangla Wikipedia (Q427715);
has sitelink to Bangla Wikipedia (Q427715) but not in English Wikipedia (Q328). – Afeef (talk) 03:49, 16 October 2021 (UTC)

@Afeef: With luck, these are what you are after.

SELECT ?item ?itemLabel ?category_titleBN
WHERE 
{
  ?item wdt:P31 wd:Q4167836. 
  ?bn_category schema:about ?item;
                       schema:isPartOf <https://bn.wikipedia.org/>;
                       schema:name ?category_titleBN. 
  ?item wikibase:sitelinks "1"^^xsd:integer
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],bn,en". }
}

Try it!

SELECT ?item ?itemLabel ?category_titleBN WITH { 
  SELECT ?item ?category_titleBN  WHERE 
  {
    ?item wdt:P31 wd:Q4167836. 
    ?bn_category schema:about ?item ;
                 schema:isPartOf <https://bn.wikipedia.org/> ;
                 schema:name ?category_titleBN . 
  } } as %i
WHERE
{
  INCLUDE %i
  FILTER NOT EXISTS { ?en_category schema:about ?item;#
                       schema:isPartOf <https://en.wikipedia.org/>. }          
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],bn,en". }
}

Try it!

--Tagishsimon (talk) 23:12, 16 October 2021 (UTC)

@Tagishsimon: Thank you!

– Afeef (talk) 03:01, 17 October 2021 (UTC)

Count only Wikipedia links for list of articles

Hi. I'm trying to create list like en:Wikipedia:Articles in many other languages but not on English Wikipedia/Update (English) for Japanese Wikipedia.

First, I've tried:

SELECT  ?item ?label ?w ?linkcount WHERE{
  ?item wikibase:sitelinks ?linkcount .
  values ?linkcount {90 91 92 93 94 95 96}
  ?item rdfs:label ?label. 
  FILTER(lang(?label)="en")
  FILTER(!strstarts(?label, "Category:"))
  FILTER(!strstarts(?label, "Wikipedia:"))
  FILTER(!strstarts(?label, "Template:"))
  FILTER(!strstarts(?label, "Module:"))
  FILTER NOT EXISTS{?sc schema:about ?item; schema:isPartOf <https://ja.wikipedia.org/> . }
    
  OPTIONAL{?item wdt:P31 ?what.?what rdfs:label ?w. FILTER(lang(?w)="en")}
}

Try it!

It works well, except linkcount is not wikipedia language links, but wikimedia sitelinks.

How can I cange it counts or specify only wikipedia links? --Suisui (talk) 05:53, 17 October 2021 (UTC)

@Suisui: Not wholly convinced the above would be my route to what I understand to be your goal; but putting that aside, I've added a column counting the language wikipedia sitelinks (?lw_count) for each item.

SELECT  ?item ?label ?w ?linkcount (count(?article) as ?lw_count) WHERE{
  ?item wikibase:sitelinks ?linkcount . hint:Prior hint:runFirst true.
  values ?linkcount {90 91 92 93 94 95 96}
  ?item rdfs:label ?label. 
  FILTER(lang(?label)="en")
  FILTER(!strstarts(?label, "Category:"))
  FILTER(!strstarts(?label, "Wikipedia:"))
  FILTER(!strstarts(?label, "Template:"))
  FILTER(!strstarts(?label, "Module:"))
  FILTER NOT EXISTS{?sc schema:about ?item; schema:isPartOf <https://ja.wikipedia.org/> . }
    
  ?article schema:about ?item ;
           schema:isPartOf ?site . 
  ?site wikibase:wikiGroup "wikipedia" .
  
  OPTIONAL{?item wdt:P31 ?what.?what rdfs:label ?w. FILTER(lang(?w)="en")}
} group by ?item ?label ?w ?linkcount

Try it!

@Tagishsimon: Wow Perfect! I couldnt find the line

    ?item wikibase:sitelinks ?linkcount . hint:Prior hint:runFirst true.

so I've always timeout it. Thank you!. --Suisui (talk) 12:40, 20 October 2021 (UTC)

Select first item in a list of attributes

I have an existing query that gets information for towns and cities in the united states. The problem is that I am getting duplicate rows for some items. For example, Cordova (Q79607) shows 6 lines. I believe this is because it has 2 listed counties and 3 listed area attributes that all have the same date. In cases like this, I'd like to be able to just select the first item in the list. Is there a way to do this? Thank you so much for any assistance!

--Jmogs (talk) 01:57, 22 October 2021 (UTC)

@Jmogs: There is a range of aggregation functions that can be brought to bear in such a situation, such as MIN or MAX for ?area, or SAMPLE, or GROUP_CONCAT for any of the plural value columns. There is not a way to select "the first in the list" ... the order of rows or values has no meaning for the SPARQL query. If you want to implement some sort of "first in the list" arrangement, you need to construct a concept of "first" out of the data in the rows, not the order of the rows. So MIN, for instance, selects the "first" value if the concept of "first" is the MIN value. (There are convoluted means of numbering rows in a query such that there's an index which can be used to select "first" values, but a) they are complicated and b) they are a construction overlaid on the data much as MIN/"first" is a construction and c) we're not going there today b/c don't think it's either necessary or appropriate for this query. Also d) hard.)

Meanwhile: a rule of aggregation is that any value that is not aggregated in the SELECT, should appear in the GROUP BY at the foot of the query.

So, dealing just with Cordova, the query below aggreates the three values (?state ?county & ?area) causing repeated rows ... but possibly not in a good way.

#City and town info

SELECT 
  ?item 
  ?itemLabel
  ?fips_code
  ?population 
  ?population_date 
  ?population_methodLabel
  (SAMPLE(?countyLabel) as ?countrysample) 
  (SAMPLE(?stateLabel) as ?statesample) 
  ?coordinates 
  ?elevation 
  (MIN(?area) as ?areamin) 
  ?area_date 
  ?website 
  ?climateLabel 
  ?nickname
  ?gmaps_id 
  ?open_street_map_id
  ?open_weather_map_id
  ?facebook_places_id
  ?youtube_id
  ?twitter_user_id
  ?subreddit_id
  ?quora_topic_id
  ?image
  ?flag_image
WHERE 
{
  VALUES ?item {wd:Q79607}
  { ?item p:P31/ps:P31/wdt:P279* wd:Q1093829 .} # cities
  UNION { ?item p:P31/ps:P31/wdt:P279* wd:Q15127012 .} # towns
  UNION { ?item p:P31/ps:P31/wdt:P279* wd:Q4946305 .} # boroughs
#  ?county wdt:P131 wd:Q1261 . # where the county is in colorado

  ?item wdt:P131 ?county . #  county
  ?county wdt:P131 ?state . # state
  
  OPTIONAL {?item wdt:P1449 ?nickname} . # nickname
  OPTIONAL {?item wdt:P571 ?inception} . # inception
  OPTIONAL {?item wdt:P625 ?coordinates} . # coordinates
  OPTIONAL {?item wdt:P2044 ?elevation} . # elevation
  OPTIONAL {?item wdt:P856 ?website} . # website
  OPTIONAL {?item wdt:P2564 ?climate} . # climate
  OPTIONAL {?item wdt:P3749 ?gmaps_id} . # gmaps_id
  OPTIONAL {?item wdt:P402 ?open_street_map_id} . # open_street_map_id
  OPTIONAL {?item wdt:P1997 ?facebook_places_id} . # facebook_places_id
  OPTIONAL {?item wdt:P7197 ?open_weather_map_id} . # open_weather_map_id
  OPTIONAL {?item wdt:P774 ?fips_code} . # fips_code
  OPTIONAL {?item wdt:P2397 ?youtube_id} . # youtube_id
  OPTIONAL {?item wdt:P2002 ?twitter_user_id} . # twitter_user_id
  OPTIONAL {?item wdt:P3984 ?subreddit_id} . # subreddit_id
  OPTIONAL {?item wdt:P3417 ?quora_topic_id} . # quora_topic_id
  OPTIONAL {?item wdt:P18 ?image} . # image
  OPTIONAL {?item wdt:P163 ?flag_image} . # flag_image
  
  OPTIONAL {
    ?item p:P1082 ?population_statement.
    ?population_statement ps:P1082 ?population.
    ?population_statement pq:P585 ?population_date.
    ?population_statement pq:P459 ?population_method.
  }
  
  FILTER NOT EXISTS {
    ?item p:P1082/pq:P585 ?population_date_ .
    FILTER (?population_date_ > ?population_date)
  }
  
  OPTIONAL {
    ?item p:P2046 ?area_statement.
    ?area_statement ps:P2046 ?area.
    ?area_statement pq:P585 ?area_date.
  }
  
  FILTER NOT EXISTS {
    ?item p:P2046/pq:P585 ?area_date_ .
    ?item p:P2046/ps:P2046 ?area_ .
    FILTER (
      ?area_date_ > ?area_date && ?area_ > ?area
    )
  }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". 
                          ?item rdfs:label ?itemLabel .
                          ?population rdfs:label ?population_methodLabel .
                          ?county rdfs:label ?countyLabel .
                          ?state rdfs:label  ?stateLabel .
                          ?climate rdfs:label ?climateLabel . 
                        } 
} group by   ?item 
  ?itemLabel
  ?fips_code
  ?population 
  ?population_date 
  ?population_methodLabel
  ?coordinates 
  ?elevation 
  ?area_date 
  ?website 
  ?climateLabel 
  ?nickname
  ?gmaps_id 
  ?open_street_map_id
  ?open_weather_map_id
  ?facebook_places_id
  ?youtube_id
  ?twitter_user_id
  ?subreddit_id
  ?quora_topic_id
  ?image
  ?flag_image

Try it!

To give a second example, it might be better to show all of the initial P131 values within a GROUP_CONCAT, and then constrain the ?state to values that are actually a US State.

I'll leave you to decide what you want to do about ?area. The Cordova data can be characterised as a hot mess :(

And to note: we've dealt, somewhat, with Cordova's issues, but you have a lot of columns. Plural values might be found for any of the others, so you may have to aggregate other values if your requirement is for single rows per item.

Finally, before I forget. Check out the label service section. In order to aggregate labels, we have to get the label service to produce them before the end of the the query engine's operations, which is done by specifying all of the variables for which we want labels within the label service statement. If we don't specify them here, they do not exist at the time the data is aggregated, and we'd get big fat blanks for all aggregated label values. It really is non-stop fun in SPARQL world.

#City and town info

SELECT 
  ?item 
  ?itemLabel
  ?fips_code
  ?population 
  ?population_date 
  ?population_methodLabel
  (GROUP_CONCAT(DISTINCT ?countyLabel;separator=", ") as ?countrysample) 
  ?stateLabel
  ?coordinates 
  ?elevation 
  (MIN(?area) as ?areamin) 
  ?area_date 
  ?website 
  ?climateLabel 
  ?nickname
  ?gmaps_id 
  ?open_street_map_id
  ?open_weather_map_id
  ?facebook_places_id
  ?youtube_id
  ?twitter_user_id
  ?subreddit_id
  ?quora_topic_id
  ?image
  ?flag_image
WHERE 
{
  VALUES ?item {wd:Q79607}
  { ?item p:P31/ps:P31/wdt:P279* wd:Q1093829 .} # cities
  UNION { ?item p:P31/ps:P31/wdt:P279* wd:Q15127012 .} # towns
  UNION { ?item p:P31/ps:P31/wdt:P279* wd:Q4946305 .} # boroughs
#  ?county wdt:P131 wd:Q1261 . # where the county is in colorado

  ?item wdt:P131 ?county . #  county
  ?county wdt:P131* ?state . # find a state somewhere in the P131 path
  ?state wdt:P31 wd:Q35657. # check the state is a US State
  
  OPTIONAL {?item wdt:P1449 ?nickname} . # nickname
  OPTIONAL {?item wdt:P571 ?inception} . # inception
  OPTIONAL {?item wdt:P625 ?coordinates} . # coordinates
  OPTIONAL {?item wdt:P2044 ?elevation} . # elevation
  OPTIONAL {?item wdt:P856 ?website} . # website
  OPTIONAL {?item wdt:P2564 ?climate} . # climate
  OPTIONAL {?item wdt:P3749 ?gmaps_id} . # gmaps_id
  OPTIONAL {?item wdt:P402 ?open_street_map_id} . # open_street_map_id
  OPTIONAL {?item wdt:P1997 ?facebook_places_id} . # facebook_places_id
  OPTIONAL {?item wdt:P7197 ?open_weather_map_id} . # open_weather_map_id
  OPTIONAL {?item wdt:P774 ?fips_code} . # fips_code
  OPTIONAL {?item wdt:P2397 ?youtube_id} . # youtube_id
  OPTIONAL {?item wdt:P2002 ?twitter_user_id} . # twitter_user_id
  OPTIONAL {?item wdt:P3984 ?subreddit_id} . # subreddit_id
  OPTIONAL {?item wdt:P3417 ?quora_topic_id} . # quora_topic_id
  OPTIONAL {?item wdt:P18 ?image} . # image
  OPTIONAL {?item wdt:P163 ?flag_image} . # flag_image
  
  OPTIONAL {
    ?item p:P1082 ?population_statement.
    ?population_statement ps:P1082 ?population.
    ?population_statement pq:P585 ?population_date.
    ?population_statement pq:P459 ?population_method.
  }
  
  FILTER NOT EXISTS {
    ?item p:P1082/pq:P585 ?population_date_ .
    FILTER (?population_date_ > ?population_date)
  }
  
  OPTIONAL {
    ?item p:P2046 ?area_statement.
    ?area_statement ps:P2046 ?area.
    ?area_statement pq:P585 ?area_date.
  }
  
  FILTER NOT EXISTS {
    ?item p:P2046/pq:P585 ?area_date_ .
    ?item p:P2046/ps:P2046 ?area_ .
    FILTER (
      ?area_date_ > ?area_date && ?area_ > ?area
    )
  }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". 
                          ?item rdfs:label ?itemLabel .
                          ?population rdfs:label ?population_methodLabel .
                          ?county rdfs:label ?countyLabel .
                          ?state rdfs:label  ?stateLabel .
                          ?climate rdfs:label ?climateLabel . 
                        } 
} group by   ?item 
  ?itemLabel
  ?stateLabel
  ?fips_code
  ?population 
  ?population_date 
  ?population_methodLabel
  ?coordinates 
  ?elevation 
  ?area_date 
  ?website 
  ?climateLabel 
  ?nickname
  ?gmaps_id 
  ?open_street_map_id
  ?open_weather_map_id
  ?facebook_places_id
  ?youtube_id
  ?twitter_user_id
  ?subreddit_id
  ?quora_topic_id
  ?image
  ?flag_image

Try it!

--Tagishsimon (talk) 08:04, 22 October 2021 (UTC)

@Tagishsimon thank you so much! I really appreciate your feedback.

"it might be better to show all of the initial P131 values within a GROUP_CONCAT, and then constrain the ?state to values that are actually a US State"

This was a separate problem I've been running into (the parent not being a real state) so this seems like the perfect solution. I'll try it out! Jmogs (talk) 00:15, 23 October 2021 (UTC)

Hi again @Tagishsimon, I have another question for you if you're willing to continue helping. This query filters out Boulder Colorado for some reason, and I can't figure out why. Maybe because one of the points in time is just a year and one is a full date? Anyway this seems like the ideal method to get the "latest population" but it's not working in this case. I'd rather not just take the max(population) because it wouldn't necessarily be the latest if the population is going down over time. Any thoughts would be much appreciated! Jmogs (talk) 01:37, 23 October 2021 (UTC)

@Jmogs: So the problem in this instance in that the 2010 population has a pq:P459 ?population_method, but the 2016 population does not. So if ?population_statement pq:P585 ?population_date. takes the 2010 date, the row is rejected by ?item p:P1082/pq:P585 ?population_date_2 . FILTER (?population_date_2 > ?population_date) taking the 2016 date. And then, I think, in the situation in which ?population_statement pq:P585 ?population_date. does not take the 2016 date (because it has no pq:P459), then there is no ?population_date (?population_date is unbound); ?item p:P1082/pq:P585 ?population_date_2 . will find 2010 and 2016, and (this is the I think but) FILTER (?population_date_2 > ?population_date) will knock both of them out because it returns true (?population_date_2 > null), which triggers the FILTER NOT EXISTS condition.

Probably the simplest method to deal with it is to make ?population_statement pq:P459 ?population_method. optional; or else amend the FILTER NOT EXISTS to check for and require a pq:P459 (so that in effect you get the most recent population with a pq:P459, not merely the most recent population); or else add FILTER(!BOUND(?population_date)) into the FILTER NOT EXISTS, which in effect checks that the OPTIONAL{} clause found something & that we're not comparing ?population_date_2 with null. It's most always a pleasure answering these questions; very happy to see people developing their SPARQL skills (as I still am by trying to answer them). --Tagishsimon (talk) 02:14, 23 October 2021 (UTC)

Hi @Tagishsimon, thank you! I assumed that the OPTIONAL around the population_statement, population, and population_date meant that each of these fields was optional, but I think I see now that it means that it's optional for all of them to exist, not each one individual. Thanks again for your assistance, I really appreciate it. Jmogs (talk) 19:47, 23 October 2021 (UTC)

#City and town info

SELECT 
  ?item 
  ?itemLabel
  ?fips_code
  ?population
  ?population_date 
  ?population_methodLabel

WHERE 
{
  ?item p:P31/ps:P31/wdt:P279* wd:Q1093829 . # cities
  ?county wdt:P131 wd:Q1261 . # where the county is in colorado
  ?item wdt:P131 wd:Q113029 . # and in boulder county

  ?item wdt:P131 ?county . #  county
  ?county wdt:P131 ?state . # state
  
  OPTIONAL {
    ?item p:P1082 ?population_statement.
    ?population_statement ps:P1082 ?population.
    ?population_statement pq:P585 ?population_date.
    OPTIONAL {?population_statement pq:P459 ?population_method.}
  }
  
  FILTER NOT EXISTS {
    ?item p:P1082/pq:P585 ?population_date_2 .
    FILTER (?population_date_2 > ?population_date)
  }
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } # Helps get the label in your language, if not, then en language
}

Try it!

Need help adjusting the query you helped me with

Hi,
The last query you created in this request works great! However, after testing it with several dates my specs changed a little; could you change the query so that it returns the 'date of death' and its references INSTEAD OF the references belonging to 'manner of death' and 'cause of death'?:

item
itemLabel
itemDescription
sitelinks
item date of birth
item date of death (including references)
item cause of death (NO references)
item manner of death (NO references)

And finally: could you limit the results to humans who have a page on the English (en) Wikipedia ?

Although the query has to be simplified I lack the knowledge to do it myself. Thank again, Mill 1 (talk) 08:01, 17 October 2021 (UTC)

@Dipsacus fullonum:, @Tagishsimon: not to put you under any kind of pressure but I am kind of waiting for an answer in order to be able to continue some activities for Wikipedia. Already lost the weekend. Mill 1 (talk) 21:03, 19 October 2021 (UTC)

@Mill 1: I adjusted Dipsacus's query to fit your request. It makes a rough attempt to sort by surname but it won't be perfect. I also added a column that calculates the age of the person at death (it should be immune to any rounding errors and will also alert you if the exact day of birth is unknown or missing). If you don't need it, all you have to do is remove "?age" from the third line of the query.

SELECT
  ?item ?itemLabel ?itemDescription ?sl ?dob
  ?dod ?age ?dod_refs ?cod ?mod
WITH
{
  SELECT
    ?item ?dod_stm ?dod
    (GROUP_CONCAT(CONCAT(?dod_ref_propLabel, ": ", ?dod_ref_valueLabel); SEPARATOR=" ~ ") AS ?dod_refs)
  WHERE
  {
    ?item wdt:P31 wd:Q5 .
    ?item wdt:P570 "+2001-08-25"^^xsd:dateTime .
    OPTIONAL
    {
      ?item p:P570 ?dod_stm .
      ?dod_stm ps:P570 ?dod ;
               psv:P570 [wikibase:timePrecision ?dod_precision].
      FILTER( ?dod_precision >= "11"^^xsd:integer )
      OPTIONAL
      {
        ?dod_stm prov:wasDerivedFrom ?dod_ref .
        ?dod_ref ?dod_ref_pr ?dod_ref_value .
        ?dod_ref_prop wikibase:reference ?dod_ref_pr .
      }
    }
    SERVICE wikibase:label
    {
      bd:serviceParam wikibase:language "en" .
      ?dod_ref_prop rdfs:label ?dod_ref_propLabel .
      ?dod_ref_value rdfs:label ?dod_ref_valueLabel .
    }
  }
  GROUP BY ?item ?dod_stm ?dod
} AS %get_dod_refs
WITH
{
  SELECT
    ?item ?dod_stm ?dod ?dod_refs ?cod_stm (SAMPLE(?codLabel) AS ?cod)
  WHERE
  {
    INCLUDE %get_dod_refs
    OPTIONAL
    {
      ?item p:P509 ?cod_stm .
      ?cod_stm ps:P509 ?cod .
    }
    SERVICE wikibase:label
    {
      bd:serviceParam wikibase:language "en" .
      ?cod rdfs:label ?codLabel .
    }
  }
  GROUP BY ?item ?dod_stm ?dod ?dod_refs ?cod_stm
} AS %get_cod
WITH
{
  SELECT
    ?item ?dod_stm ?dod ?dod_refs ?cod_stm ?cod (SAMPLE(?modLabel) AS ?mod)
  WHERE
  {
    INCLUDE %get_cod
    OPTIONAL
    {
      ?item p:P1196 ?mod_stm .
      ?mod_stm ps:P1196 ?mod .
    }
    SERVICE wikibase:label
    {
      bd:serviceParam wikibase:language "en" .
      ?mod rdfs:label ?modLabel .
    }
  }
  GROUP BY ?item ?dod_stm ?dod ?dod_refs ?cod_stm ?cod
} AS %get_mod
WHERE
{
  INCLUDE %get_mod
  ?item wikibase:sitelinks ?sl .
  ?item ^schema:about [schema:isPartOf <https://en.wikipedia.org/>] .
  OPTIONAL { ?item wdt:P569 ?dob ;
                   p:P569 ?dob_stm .
             ?dob_stm ps:P569 ?dob ;
                      psv:P569 [wikibase:timePrecision ?dob_precision].}
  SERVICE wikibase:label
    {
      bd:serviceParam wikibase:language "en" .
      ?item rdfs:label ?itemLabel .
      ?item schema:description ?itemDescription .
    }
  BIND(REPLACE(?itemLabel, "^.* (?![SJ]r\\.?$)", "") AS ?sortname)
  BIND(IF(BOUND(?dob),IF(?dob_precision>=11,str(if(?dod< strdt(concat(str(year(?dod)),"-",if(strlen(str(month(?dob)))=1,"0",""),str(month(?dob)),"-",if(strlen(str(day(?dob)))=1,"0",""),str(DAY(?dob))),xsd:dateTime),year(?dod)-year(?dob)-1,year(?dod)-year(?dob))),"exact DOB unknown"),"DOB missing") AS ?age)
}
ORDER BY ASC(UCASE(?sortname))

Try it!

Let me know if any other changes are needed and I'll be glad to help!

--Quesotiotyo (talk) 22:26, 20 October 2021 (UTC)

Thanks @Quesotiotyo:, that was what I was looking for! And the sort functionality is a great bonus! Cheers, Mill 1 (talk) 18:14, 21 October 2021 (UTC)

Hi @Quesotiotyo:, Still very happy with the query but after some testing I ran into some minor issues:

For example; take as date of death 2 November 2004 (

?item wdt:P570 "+2004-11-02"^^xsd:dateTime .

in the first WHERE):

Item(label) 'Václav Kotva' appears four times
Item 'Theo van Gogh' appears two times
The query returnS results containing a date of death different from the one in the WHERE clause

I suspect that issue 3. is the cause of issue 1. I also suspect that 'Theo van Gogh' appeared twice because two causes of death exist. Could you or someone else change the query so that:

Only records are returned of which the date of death matches the date of death in the WHERE clause
In case of multiple causes of death, return one record of the human, containing the first cause of death

In the end the query should not contain any duplicate items. BTW, it's great you implemented the sorting Quesotiotyo, otherwise I would not have spotted this ;) Thank you. Mill 1 (talk) 12:24, 23 October 2021 (UTC)

@Mill 1: Here you go! (I'm posting in plaintext because the SPARQL template wants to cut off part of the query)

SELECT
  ?item ?articlename ?itemLabel ?itemDescription ?sl ?dob
  ?dod ?age ?dod_refs ?cod ?mod
WITH
{
  SELECT
    ?item ?dod_stm ?dod
    (GROUP_CONCAT(CONCAT(?dod_ref_propLabel, ": ", ?dod_ref_valueLabel); SEPARATOR=" ~ ") AS ?dod_refs)
  WHERE
  {
    VALUES ?dod {"+2004-11-02"^^xsd:dateTime}
    ?dod ^wdt:P570 ?item .
    ?item wdt:P31 wd:Q5 .
    OPTIONAL
    {
      ?item p:P570 ?dod_stm .
      ?dod_stm ps:P570 ?dod ;
               psv:P570 [wikibase:timePrecision ?dod_precision].
      FILTER( ?dod_precision >= "11"^^xsd:integer )
      OPTIONAL
      {
        ?dod_stm prov:wasDerivedFrom ?dod_ref .
        ?dod_ref ?dod_ref_pr ?dod_ref_value .
        ?dod_ref_prop wikibase:reference ?dod_ref_pr .
      }
    }
    SERVICE wikibase:label
    {
      bd:serviceParam wikibase:language "en" .
      ?dod_ref_prop rdfs:label ?dod_ref_propLabel .
      ?dod_ref_value rdfs:label ?dod_ref_valueLabel .
    }
  }
  GROUP BY ?item ?dod_stm ?dod
} AS %get_dod_refs
WITH
{
  SELECT
    ?item ?dod_stm ?dod ?dod_refs (GROUP_CONCAT(?codLabel; SEPARATOR=", ") AS ?cod)
  WHERE
  {
    INCLUDE %get_dod_refs
    OPTIONAL
    {
      ?item p:P509 ?cod_stm .
      ?cod_stm ps:P509 ?cod .
    }
    SERVICE wikibase:label
    {
      bd:serviceParam wikibase:language "en" .
      ?cod rdfs:label ?codLabel .
    }
  }
  GROUP BY ?item ?dod_stm ?dod ?dod_refs
} AS %get_cod
WITH
{
  SELECT
    ?item ?dod_stm ?dod ?dod_refs ?cod (SAMPLE(?modLabel) AS ?mod)
  WHERE
  {
    INCLUDE %get_cod
    OPTIONAL
    {
      ?item p:P1196 ?mod_stm .
      ?mod_stm ps:P1196 ?mod .
    }
    SERVICE wikibase:label
    {
      bd:serviceParam wikibase:language "en" .
      ?mod rdfs:label ?modLabel .
    }
  }
  GROUP BY ?item ?dod_stm ?dod ?dod_refs ?cod
} AS %get_mod
WHERE
{
  INCLUDE %get_mod
  ?item wikibase:sitelinks ?sl .
  ?item ^schema:about ?article .
  ?article schema:isPartOf <https://en.wikipedia.org/> ;
           schema:name ?articlename .
  OPTIONAL { ?item wdt:P569 ?dob ;
                   p:P569 ?dob_stm .
             ?dob_stm ps:P569 ?dob ;
                      psv:P569 [wikibase:timePrecision ?dob_precision].}
  SERVICE wikibase:label
    {
      bd:serviceParam wikibase:language "en" .
      ?item rdfs:label ?itemLabel .
      ?item schema:description ?itemDescription .
    }
  BIND(REPLACE(?itemLabel, "^.*(?<! [Vv][ao]n| [Dd][aeiu]| [Dd][e][lns]| [Ll][ae]) (?!([SJ]r\\.?|[XVI]+)$)", "") AS ?sortname)
  BIND(IF(BOUND(?dob),IF(?dob_precision>=11,str(if(?dod< strdt(concat(str(year(?dod)),"-",if(strlen(str(month(?dob)))=1,"0",""),str(month(?dob)),"-",if(strlen(str(day(?dob)))=1,"0",""),str(DAY(?dob))),xsd:dateTime),year(?dod)-year(?dob)-1,year(?dod)-year(?dob))),"exact DOB unknown"),"DOB missing") AS ?age)
} ORDER BY ASC(UCASE(?sortname)) ASC(UCASE(?itemLabel))

I made a few minor adjustments, so now you will only get Theo Van Gogh once and he will be sorted properly :). You will still see two Václav Kotva results though, since he has two possible dates of birth and so his age at death could be one of two values (he also has two possible dates of death, which is why his name was previously listed four times). If someone were to, say, thoroughly research him (or any individual where this is the case) to determine the correct dates and use ranking to give preference to those dates, then re-running the query would fix that ;).

Happy to be of service!

--Quesotiotyo (talk) 16:14, 23 October 2021 (UTC)

Thx @Quesotiotyo: for the swift reply. I'll try it out tomorrow! Mill 1 (talk) 17:01, 23 October 2021 (UTC)

@Quesotiotyo: The improved query works great! You even listed the multiple causes of death regarding Theo Van Gogh! One very last request: as it turns out, I also need the name of the corresponding English Wikipedia article (enwiki). So 'Theo van Gogh (film director)' in case of label 'Theo van Gogh'. I think it has to do with

?article schema:about ?item.

but I can't make it work Mill 1 (talk) 20:20, 25 October 2021 (UTC)

@Mill 1: I edited the above query to include the article titles. Enjoy! --Quesotiotyo (talk) 22:45, 26 October 2021 (UTC)

@Quesotiotyo: for President! Thanks so much. Will post an update in the future how this query is applied in my finished application. Cheers Mill 1 (talk) 21:44, 27 October 2021 (UTC)

Counting ID properties

Hello. I want to be able to count the number of identifier properties per item for a given subset of Wikidata items but I cannot figure out how to isolate just ID properties. Any help would be greatly appreciated. Thanks Jason.nlw (talk) 16:34, 19 October 2021 (UTC)

@Jason.nlw: This, probably. Find all the predicates of the items, check they're claims / property statements, check their type is ExternalID. You can use ?property wikibase:directClaim ?predicate. if you want to count just truthy claims; and (COUNT(DISTINCT ?property) as ?count) if you want to know how many differet types of ID are used, rather than how many IDs. There's also a bit of fiddling about with grouping arising from single items having multiple P2966s.

SELECT ?item ?itemLabel ?count WITH 
{ SELECT ?item ?value (COUNT(?property) AS ?count) WHERE 
  {
    ?item wdt:P2966 ?value . hint:Prior hint:runFirst true .
    ?item ?predicate [] .
    ?property wikibase:claim ?predicate .
    ?property wikibase:propertyType wikibase:ExternalId .
} GROUP BY ?item ?value } as %i
WHERE
{
  INCLUDE %i
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} GROUP BY ?item ?itemLabel ?count ORDER BY DESC(?count)

Try it!

--Tagishsimon (talk) 17:14, 19 October 2021 (UTC)

Tagishsimon - Thanks so much! I would never have figured that out! This will be really useful for a little case study i'm working on. Thanks again, this is much appreciated. Jason.nlw (talk) 19:37, 19 October 2021 (UTC)

@Jason.nlw: Turns out there's a much simpler way of doing this; number of identifiers is stored as a triple in the RDF with the predicate wikibase:identifiers. So now that we know that & know we need to knock out duplicate rows arising from items that have more than one P2966 value, we get the below. You'd have to stick with the previous query to get counts of types of IDs, and counts of only truthy IDs.

(All of which might not actually be of much interest to you, but I feel Request a query is also a how-to / cookbook for a bunch of people interested in developing SPARQL/WDQS skills & so it helps to cover all the bases. WD stores three counts as triples using the predicates: wikibase:sitelinks, wikibase:statements, and wikibase:identifiers, each of which does what you'd expect - all sitelinks, all statements & IDs irrespective of rank.)

SELECT DISTINCT ?item ?itemLabel ?identifiers WHERE 
{
  ?item wdt:P2966 ?value . hint:Prior hint:runFirst true .
  ?item wikibase:identifiers ?identifiers .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} ORDER BY DESC(?identifiers)

Try it!

--Tagishsimon (talk) 20:27, 27 October 2021 (UTC)

Ah Brilliant! Thank you. I'm hopping to use all this to build a case for the National Library to use Wikidata to help clean up their authority records so that there is only one per entity. Fingers crossed! Jason.nlw (talk) 11:16, 28 October 2021 (UTC)

Wikidata query API to get a list of items with their properties

For me to be able to read a list of items from Wikidata, I am making one HTTP request to get the list of items' IDs, and then for each item, I am making another HTTP request to get all properties and their values for that item. Is there a better way to do this? is there a one HTTP request that I can call that would give me a list of item with all of their properties?

This short discussion might or might not help. It's possible to send a query to WDQS and get the results back as a file. Presuming you can compose suitable queries, this would seem to be a possible mechanism? --Tagishsimon (talk) 18:31, 30 October 2021 (UTC)

Items created by User Pmt

Is it possible to have a list of items created by User:Pmt and dont have IDs, and second who dont have references? Pmt (talk) 21:03, 27 October 2021 (UTC)

Hi. I shamelessly adapted a query from User:Dipsacus fullonum to make it do what you wanted. (So any thanks should go to that guy) It lists a selection of 200 entities which you created, then it discards any items that have identifiers. I also tried to create a similar query to list new items without references, but either I messed something up or there are no items completely without references that you've made. --Infrastruktur wdt:P31 wd:Q5 (T | C) 14:47, 28 October 2021 (UTC)

Edit: Since it doesn't list all your new entities, you would have to either add identifiers to the entities or limit the search to a timeperiod in order to effectively cover them all. The parameters to restrict the list to only last year would look like:

bd:serviceParam mwapi:ucstart "2020-10-28T15:17:35.000Z".
bd:serviceParam mwapi:ucend "2021-10-28T15:17:35.000Z".

#title:New entities by user without identifiers.
SELECT ?item ?itemLabel ?itemDescription ?timestamp ?size
WHERE {
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:endpoint "www.wikidata.org";
                    wikibase:limitContinuations "199";

                    wikibase:api "Generator";
                    mwapi:generator "allpages";
                    mwapi:gaplimit "1";

                    mwapi:list "usercontribs";
                    mwapi:ucuser "Pmt";
                    mwapi:ucshow "new";
                    mwapi:ucnamespace "0";
                    mwapi:uclimit "1";
                    mwapi:ucprop "ids|title|timestamp|size" .
    ?item wikibase:apiOutputItem "//api/query/usercontribs/item/@title" .
    ?timestamp wikibase:apiOutput "//api/query/usercontribs/item/@timestamp" .
    ?size wikibase:apiOutput "//api/query/usercontribs/item/@size" .
  }
  FILTER BOUND(?item)
  ?item wikibase:identifiers "0"^^xsd:integer.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "nb,nn,no,en". }
}

New entities by user without identifiers.

Infrastruktur Thank you. (Added nn, and no in the search) Pmt (talk) 09:22, 29 October 2021 (UTC)

Wikidata list about people of Bangladeshi films

Wikidata:WikiProject Movies/lists/films by country/Bangladesh/people, here, I'm trying to make a list. Help me to fix it. -- — Afeef (talk) 13:04, 30 October 2021 (UTC)

@Afeef: Needed |SPARQL= to be |sparql= --Tagishsimon (talk) 13:37, 30 October 2021 (UTC)

Tournaments won by Ivan Lendl

Hi, I would like to request an overview of the tournaments won by 'tennis player' (Q10833314) Ivan Lendl (Q182736). Wins are registered in the item 'winner' (P1346) on the level of tennis event (Q46190676). Example: 1985 Tokyo Indoor – singles (Q3992338). Overview should list year, tournament name (in this example Q3992339), country (P17), surface played on (P765) , prize money (P2121), number of participants (P1132). Thanks!--Wolbo (talk) 15:37, 30 October 2021 (UTC)

@Wolbo: This is going in the right direction. Not much luck with surface or purse. Query gives you the event and the competition is it part of. If you have suggestions for improvements, happy to amend it again.

SELECT ?item ?itemLabel ?part_ofLabel ?year ?countryLabel ?surfaceLabel ?purse ?participants
WHERE 
{
  ?item wdt:P1346 wd:Q182736 . 
  OPTIONAL { ?item wdt:P361 part_of . }
  OPTIONAL { ?item wdt:P585 ?time . BIND(year(?time) as ?year) }
  OPTIONAL { ?item wdt:P17 ?country . }
  OPTIONAL { ?item wdt:P765 ?surface . }
  OPTIONAL { ?item wdt:P2121 ?purse . }
  OPTIONAL { ?item wdt:P1132 ?participants . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 16:31, 30 October 2021 (UTC)

Tagishsimon thanks! That looks pretty good. The fields surface played on and prize money can be found on the level of the tournament (one level higher than event), in this example 1985 Tokyo Indoor (Q3992339). Also the tournament name and country can best be derived from that level.--Wolbo (talk) 18:31, 30 October 2021 (UTC)

@Wolbo: V2. I've moved surface, purse and country up to the tournament level. Looks slightly better. Amend the SELECT if you do not want ?event or ?eventLabel, add ?tournament if you want its QId. Prod some more here if you want anything else doing.

SELECT ?event ?eventLabel ?tournamentLabel ?year ?countryLabel ?surfaceLabel ?purse ?participants
WHERE 
{
  ?event wdt:P1346 wd:Q182736 . 
  OPTIONAL { ?event wdt:P361 ?tournament . 
             OPTIONAL { ?tournament wdt:P765 ?surface . }
             OPTIONAL { ?tournament wdt:P2121 ?purse . }
             OPTIONAL { ?tournament wdt:P17 ?country . }
           }
  OPTIONAL { ?event wdt:P585 ?time . BIND(year(?time) as ?year) }  
  OPTIONAL { ?event wdt:P1132 ?participants . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 18:50, 30 October 2021 (UTC)

Percentage of biographies per gender per century

Hi! So I read that less than 20% of biographies on the English Wikipedia are about women. This causes a lot of unrest and seems to indicate a strong gender bias. However, I wonder: if women have been systematically oppressed and kept out of politics, science, higher education, etc. throughout history almost everywhere, isn't it to be expected that there're way less encyclopedically relevant women than men throughout history, and therefore on Wikipedia? So today I thought: if we graph the percentage of biographies per gender per century of birth of the person, perhaps we'll see that the further back we go, the more percentage of men and less of women we find, and the closer we come to the present, the greater the equality in the percentages. Or perhaps the inequality persists as strong as ever? Or perhaps so many community efforts to decrease the gap have actually flipped it on recent centuries? Just to clarify further, here's an image illustrating the kind graph I hope to produce (with fictional data):

With the real data I can easily build the real graph myself. However, the Wikidata query syntax is a bit daunting to me so I'd really appreciate some help. Thanks! Sophivorus (talk) 01:44, 31 October 2021 (UTC)

@Sophivorus: You may or may not be aware of https://humaniki.wmcloud.org/gender-by-dob which provides visualisations of the sort you have described. I'm not sure how far down the road we would get were we to try to query all humans in WD for gender and DoB. By & large the query would be something like that below - in this example, it's trying to get a count of per-year-of-birth figures by gender. But it times out. I cannot think of a means of reshaping it such that it'll work. I suspect this is a form of query best done from dump files.

SELECT ?DoBYear ?gender (count(?item) as ?count)
WHERE  
{
  ?item wdt:P31 wd:Q5.
  ?item wdt:P21 ?gender . 
  ?item wdt:P569 ?DoB .
  BIND (year(?DoB) as ?DoBYear)
} group by ?DoBYear ?gender

Try it!

-Tagishsimon (talk) 02:09, 31 October 2021 (UTC)

@Tagishsimon Thanks! I was not aware of https://humaniki.wmcloud.org/gender-by-dob ! I copy-pasted their data, normalized and crunched it manually, and produced my graph. It turned out to be quite similar to my fictional graph. In case you (or anyone) is curious, you can find it here. Some of the data seems suspicious though (see for example years 1794 or 1953) and I ignored other genders and years BC to reduce work, but overall I think the trend is quite clear and the big picture seems reliable. Thanks again for your help! Sophivorus (talk) 03:29, 31 October 2021 (UTC)

First, chapeau! @Sophivorus:. Nice work. And to spot Humaniki's borked data, hitherto unnoticed, afaik. Here's a query looking at births per year (and also requiring at least 1 sitelink, which might or might not be the same setting as the data you downloaded.) 1794 is banged to rights. 1793 looked close, but no cigar. I fear the humaniki team may by now have dispersed, so it might not be possible to find out exactly what's going on.

SELECT ?gender ?genderLabel (count(?item) as ?count)
WHERE  
{
  ?item wdt:P31 wd:Q5.
  ?item wdt:P21 ?gender . 
  ?item wdt:P569 ?DoB . hint:Prior hint:rangeSafe true.
  FILTER("1794-00-00"^^xsd:dateTime <= ?DoB &&
         ?DoB < "1795-00-00"^^xsd:dateTime)
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item wikibase:sitelinks ?sl .
  filter(?sl >0)
} group by ?gender ?genderLabel

Try it!

--Tagishsimon (talk) 03:48, 31 October 2021 (UTC)

Pinged to a wider world here: https://twitter.com/Tagishsimon/status/1454659390150221828 --Tagishsimon (talk) 04:00, 31 October 2021 (UTC)

@Tagishsimon Thanks! I tested your query and compared it with humaniki data and it seems you nailed it. Your numbers are a bit higher than humaniki but that's probably because their numbers are old and many biographies have been created since. And anyway, it's not about the numbers but about the percentages. Soon I'll try to produce a more definitive graph, including years BC and other genders. Your query will help me fix any dubious data, thanks again!! Sophivorus (talk) 12:55, 31 October 2021 (UTC)

instance of (P31) subclass of (P279)

Typical situation: I want found lakes. One lake isn’t instance of (P31)lake (Q23397) but instance of (P31)hypersaline lake (Q2912558). hypersaline lake (Q2912558)subclass of (P279)salt lake (Q188025) and salt lake (Q188025)subclass of (P279)lake (Q23397). How can I find all such lakes? 217.117.125.83 11:19, 31 October 2021 (UTC)

By looking for things that are an instance of a lake, or a subclass of a lake ... the key incantation is wdt:P31/wdt:P279* - see https://www.w3.org/TR/sparql11-property-paths/#path-language . However there are 301833 items which meet that criteria, more than the label service will furnish labels for in a 60 second query. So here's the same thing, constrained to look only at UK lakes ... in short we need to segment the data, e.g. on country, to be able to produce labelled reports. The report also specifies what P31 values the lake has, such that you can see which are reservoirs, salt lakes, &c.

SELECT ?item ?itemLabel ?P31Label
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q23397.
  ?item wdt:P31 ?P31 .
  ?item wdt:P17 wd:Q145 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 13:28, 31 October 2021 (UTC)

Many thanks! I needed only lakes meet specific creteria (i.e. enough area) so their number isn’t problem for me. 217.117.125.83 16:30, 31 October 2021 (UTC)

Wikidata:Request a query/Archive/2021/10

Contents

Theses, which institution has most?

Extracting population data from two given years

Que la consulta presente todos los campos requeridos, incluidos la fecha y el genero

Items that are "instance of" two things at the same time

Q-ID for known string of item label

optional WHERE statement?

astronautas

Finding items with articles in any Wikipedia but no in other projects (Documentation about schema:X usage)

National flags ordered by population

Bio pages in ES WP, not in LAD WP

How to get a list of humans, including some of their properties, who died on a particular date?

Get all the possible values of "instance of" property

Finding Wikisource pages that are not connected to a Wikipedia page

Wikimedia categories in bnwiki

Count only Wikipedia links for list of articles

Select first item in a list of attributes

Need help adjusting the query you helped me with

Counting ID properties

Wikidata query API to get a list of items with their properties

Items created by User Pmt

Wikidata list about people of Bangladeshi films

Tournaments won by Ivan Lendl

Percentage of biographies per gender per century

instance of (P31) subclass of (P279)

Navigation menu

Wikidata:Request a query/Archive/2021/10

Theses, which institution has most?

Extracting population data from two given years

Que la consulta presente todos los campos requeridos, incluidos la fecha y el genero

Items that are "instance of" two things at the same time

Q-ID for known string of item label

optional WHERE statement?

astronautas

Finding items with articles in any Wikipedia but no in other projects (Documentation about schema:X usage)

National flags ordered by population

Bio pages in ES WP, not in LAD WP

How to get a list of humans, including some of their properties, who died on a particular date?

Get all the possible values of "instance of" property

Finding Wikisource pages that are not connected to a Wikipedia page

Wikimedia categories in bnwiki

Count only Wikipedia links for list of articles

Select first item in a list of attributes

Need help adjusting the query you helped me with

Counting ID properties

Wikidata query API to get a list of items with their properties

Items created by User Pmt

Wikidata list about people of Bangladeshi films

Tournaments won by Ivan Lendl

Percentage of biographies per gender per century

instance of (P31) subclass of (P279)

Navigation menu

Search