Wikidata:Request a query

Request a query Fishing in the Wikidata river requires both an idea where to look for fish and a suitable fishing method. If you have the former, this page can help you find the latter. This is a page where SPARQL 1.1 Query Language (Q32146616) queries can be requested. Please provide feedback if a query is written for you. For sample queries, see Examples and Help:Dataset sizing. Property talk pages include also summary queries for these. For help writing your own queries, or other questions about queries, see Wikidata talk:SPARQL query service/queries and Wikidata:SPARQL query service/query optimization. Help resources about Wikidata Query Service (Q20950365) and SPARQL: Wikidata:SPARQL query service/Wikidata Query Help and Category:SPARQL. To report an issue about the Query Service (interface, results views, export...) please see Wikidata:Contact the development team/Query Service and search.

Translate this header box! Start a new discussion

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

Data matching and superset of WikiBio data set

I would like to download the full article text and other metadata of the same pages as the WikiBio data set https://paperswithcode.com/dataset/wikibio both when it was created -- 2016 with 728,000 records and today with however many there are now. the crucial thing that seems to be missing from the Examples is how to specify that I need the full text of the relevant article.

Query about museum's collection

Hello everyone! I'm looking for a query that can identify all the elements of a wikimedia commons category or a museum collection (that can also capture data such as upload date, license, etc)!

How to optimise this query please ?

Hi all, So I have this query https://w.wiki/55Uu that works pretty well. Basically, it shows the number of Qitems with P269 broken down according to different person identifiers (Q36218176) somewhat related to France. I would like my query to be a little wider and to deal with all identifiers (Q19847637) and/or not related to France. The problem is that every time I try I reach a timeout. From the error messages I got I understand that you can't have nested INCLUDE, but I may be wrong. Does anyone have a solution ? Thanks !

Example Query "Recent Events" times out

Maybe it is just me, but today I tried to execute the Recent Events query on the Wikidata Query Service GUI, but I was not able to execute it.

It is labeled as a "Simple Query", but I think that the query is not that simple at all. It uses a lot of SPARQL keywords, date-formatting functions, and also a property path (? - I mean the wdt:P31/wdt:P279* construct).

Is it possible to make the query run faster, simplify it, or (IMHO) put it into a different category in the "Examples" Listing? -- Driller001 2022-05-24T16:52:34(IST)

A version that does not timeout, and internationalized :

#Recent events
#Recent events
#title: Recent events

select ?event ?eventLabel ?date {
  {
    SELECT distinct * 
    {
      # find events
      ?event wdt:P31/wdt:P279* wd:Q1190554.
      # with a point in time or start date
      ?event wdt:P585|wdt:P580 ?date 
      # not including « unknown values » 
      FILTER(DATATYPE(?date) = xsd:dateTime). 
      # not in the future, and not more than 31 days ago
      BIND(NOW() - ?date AS ?distance). 
      # optimisation : all values are dates so we inform the query service of this fact
      hint:Prior hint:rangeSafe true 
      FILTER(0 <= ?distance && ?distance < 31).
    }
  # limit to 100 results so we don't timeout
  LIMIT 100
  }
  # getting the event label in an outer query, to speed up and not getting the label for filtered candidates
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by desc(?date) # sorting result by more recent one first

Recent events

-- TomT0m 2022-05-24T17:23:43(IST)

Query all number plates of a country

Hi,

I want to have all number plates (P395) that where used in a country (eg. Q183). How can I do this?

Cemeteries near churches

Hello, I have two queries that list respectively all the churches and cemeteries in France with their administrative location and coordinates. Now, I'd like to retrieve all the cemeteries and churches that are within 20 meters of each other. Is it possible? Thanks. Ayack (talk) 09:59, 17 June 2022 (UTC)

I think it is possible, but I don't have time to make the query today. I would modify the query for cemeteries by adding a geospatial search for items within 20 meter and filter out what is not a church. I cannot tell for sure if it will work or timeout before I have tried it. --Dipsacus fullonum (talk) 10:35, 17 June 2022 (UTC)

Something what You've described:

SELECT ?cemetery ?cemeteryLabel ?churchLabel ?church ?distance {
  ?cemetery wdt:P31/wdt:P279* wd:Q39614;
            wdt:P17 wd:Q142;
            wdt:P625 ?cem_coords.
  SERVICE wikibase:around {
      ?church wdt:P625 ?chr_coords.
      bd:serviceParam wikibase:center ?cem_coords.
      bd:serviceParam wikibase:radius "0.02".
      bd:serviceParam wikibase:distance ?distance.
  }
  FILTER EXISTS {
    ?church wdt:P31/wdt:P279* wd:Q16970.
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE], en". }
}

Try it!

Piastu (talk) 13:19, 17 June 2022 (UTC)

Wow, great! Thanks a lot! Ayack (talk) 13:38, 17 June 2022 (UTC)

@Ayack: I presume you're thinking these should be joined using connects with (P2789) - cf. https://w.wiki/5JR$ --Tagishsimon (talk) 14:03, 17 June 2022 (UTC)

@Tagishsimon Actually I'm using adjacent building (P3032). Ayack (talk) 14:10, 17 June 2022 (UTC)

@Ayack: Interesting. Is a cemetery a building? --Tagishsimon (talk) 14:12, 17 June 2022 (UTC)

@Tagishsimon Building no, but architectural structure (Q811979) yes (cf. property constraint). Ayack (talk) 14:15, 17 June 2022 (UTC)

@Ayack: Well. TIL. https://w.wiki/5JSG vs https://w.wiki/5JSJ --Tagishsimon (talk) 14:24, 17 June 2022 (UTC)

@Tagishsimon To be honest most of them must have been added by me... Ayack (talk) 14:29, 17 June 2022 (UTC)

@Ayack: It's fine; you win by an order of magnitude & more. I'll change mine :) --Tagishsimon (talk) 14:32, 17 June 2022 (UTC)

Query church buildings in a certain administrative unit, output with image?

Hi, i've failed building a query for churches in a certain administrative unit. My goal is to achieve somethink like the great PetScan tool does (return images for a certain category with subcategories), restricting the output to just one image per wikidata entry - thus getting some sort of gallery of Wikidata items of some sort for a certain administrative unit, with disctinct results, thus just one image per item (PetScan returns all images per Category, giving too huge result sets).

First, i've tried the Query builder, which gave (example: Churches in Normandy Region, France):

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:Q16970.
      ?item p:P131 ?statement1.
      ?statement1 (ps:P131/(wdt:P131*)) wd:Q18677875.
      ?item p:P18 ?statement2.
      ?statement2 (ps:P18) _:anyValueP18.
    }
    LIMIT 10
  }

Try it!

The result doesn't contain the image, and i don't know how to modify the output to get the image. I've tried in vain to adapt the "(Artist) Images of Exoplanets" example to get also the image while keeping the restriction regarding the administrative unit:

#defaultView:ImageGrid
SELECT ?item ?itemLabel ?image WHERE {
  ?item p:P31 ps:P31
        wdt:P279 wd:Q16970. ;
        p:P18 ?image
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 10

Try it!

which fails with an error "Encountered " ";" "; "" at line 4, column 47." What am i doing wrong? Thanks for your help! Fl.schmitt (talk) 10:11, 17 June 2022 (UTC)

@Fl.schmitt Not exactly what you've requested, but you could have a look at this page for example : Wikidata:WikiProject France/Églises/14 Ayack (talk) 10:28, 17 June 2022 (UTC)

And here is your query:

#defaultView:ImageGrid
SELECT DISTINCT ?item ?itemLabel ?lieuLabel ?image WHERE {
  ?item (wdt:P31/(wdt:P279*)) wd:Q16970;
    wdt:P131 ?lieu.
  ?lieu (wdt:P131*) wd:Q18677875.
  ?item wdt:P18 ?image.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Ayack (talk) 10:35, 17 June 2022 (UTC)

@Ayack - whoaa, great, thanks a million! The hint regarding the Wikidata Project France is very useful, too, but i', also happy about the query since i can also apply it to different countries / subdivisions. Fl.schmitt (talk) 10:38, 17 June 2022 (UTC)

Get multivalue as a pack

Hi, Is there any way to get, for each item result, the multi-values from a specific property all together; it is, assembled as one unique string with any kind of separator between values?. Is it possible to do with the help of some kind of edition code ?

In this example, the results from album= Q104028844, Q3285231, Q64168425 have two rows because the "productor" (P162) has 2 values, but not the rest of properties. This is the "standard way", I know, but in some cases is a "not pretty look" to show results:

#title:list of recorded track from one specific composition, and all the releases (single/album, etc.) where using
SELECT distinct ?item ?itemLabel ?tipusLabel ?interpret ?interpretLabel ?productorLabel
 ?album ?albumLabel ?pista ?data_publi WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }

 ?item wdt:P2550 wd:Q1550581.
  OPTIONAL { ?item wdt:P31 ?tipus_trk. }
  OPTIONAL { ?item wdt:P175 ?interpret. }
  OPTIONAL { ?item wdt:P10135 ?data_enreg. }
  OPTIONAL { ?item wdt:P483 ?gravat_a. }
  OPTIONAL { ?item wdt:P162 ?productor. }
  OPTIONAL { ?album p:P658 ?statement.
       ?statement ps:P658 ?item.
       OPTIONAL {?statement pq:P1545 ?pista_ord.}
       OPTIONAL {?statement pq:P3831 ?pista_rol.}
       OPTIONAL { ?album wdt:P31 ?tipus_rls. }
       OPTIONAL { ?album wdt:P577 ?data_publi. }
       OPTIONAL { ?album wdt:P437 ?format_dist. }
           }
  BIND(IF(BOUND(?tipus_rls), ?tipus_rls, ?tipus_trk )
           as ?tipus).
  BIND(IF(BOUND(?pista_ord), ?pista_ord, ?pista_rol)
           as ?pista).
}
ORDER BY ASC(?data_enreg) ASC(?interpretLabel) ASC(?album)

list of recorded track from one specific composition, and all the releases (single/album, etc.) where using

Thanks, Amadalvarez (talk) 10:08, 18 June 2022 (UTC)

@Amadalvarez: – Yes, there is – You can GROUP_CONCAT these labels (and GROUP BY the rest of results; and separator ups to You ;) ):

#title:list of recorded track from one specific composition, and all the releases (single/album, etc.) where using
SELECT ?item ?itemLabel ?tipusLabel ?interpret ?interpretLabel ?album ?albumLabel ?pista ?data_publi (GROUP_CONCAT(DISTINCT ?productorLabel; separator=", ") AS ?productors ) {

SELECT distinct ?item ?itemLabel ?tipusLabel ?interpret ?interpretLabel ?productorLabel
 ?album ?albumLabel ?pista ?data_publi WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }

 ?item wdt:P2550 wd:Q1550581.
  OPTIONAL { ?item wdt:P31 ?tipus_trk. }
  OPTIONAL { ?item wdt:P175 ?interpret. }
  OPTIONAL { ?item wdt:P10135 ?data_enreg. }
  OPTIONAL { ?item wdt:P483 ?gravat_a. }
  OPTIONAL { ?item wdt:P162 ?productor. }
  OPTIONAL { ?album p:P658 ?statement.
       ?statement ps:P658 ?item.
       OPTIONAL {?statement pq:P1545 ?pista_ord.}
       OPTIONAL {?statement pq:P3831 ?pista_rol.}
       OPTIONAL { ?album wdt:P31 ?tipus_rls. }
       OPTIONAL { ?album wdt:P577 ?data_publi. }
       OPTIONAL { ?album wdt:P437 ?format_dist. }
           }
  BIND(IF(BOUND(?tipus_rls), ?tipus_rls, ?tipus_trk )
           as ?tipus).
  BIND(IF(BOUND(?pista_ord), ?pista_ord, ?pista_rol)
           as ?pista).
}
ORDER BY ASC(?data_enreg) ASC(?interpretLabel) ASC(?album)
}

GROUP BY ?item ?itemLabel ?tipusLabel ?interpret ?interpretLabel ?album ?albumLabel ?pista ?data_publi

list of recorded track from one specific composition, and all the releases (single/album, etc.) where using

Piastu (talk) 11:40, 18 June 2022 (UTC)

Thank you !. Each day, one new knowledge. Great. Amadalvarez ([[User talk:Amadalvarez|

Possible to do the same thing without the nested SELECT, albeit at the cost of declaring all variables for which you want labels, in the label service call. --Tagishsimon (talk) 11:49, 18 June 2022 (UTC)

I think it worth the effort of your proposal in favour of clarity of code. I'll use it. Amadalvarez (talk) 15:35, 18 June 2022 (UTC)

talk]]) 15:09, 18 June 2022 (UTC)

#title:list of recorded track from one specific composition, and all the releases (single/album, etc.) where using
SELECT DISTINCT ?item ?itemLabel ?tipusLabel ?interpret ?interpretLabel ?album ?albumLabel ?pista ?data_publi 
                 (GROUP_CONCAT(DISTINCT ?productorLabel; separator=", ") AS ?productors ) WHERE
{
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
                         ?item rdfs:label ?itemLabel . 
                         ?tipus rdfs:label ?tipusLabel .
                         ?interpret rdfs:label ?interpretLabel. 
                         ?album  rdfs:label  ?albumLabel .
                         ?productor rdfs:label ?productorLabel.
                         }
  ?item wdt:P2550 wd:Q1550581.
  OPTIONAL { ?item wdt:P31 ?tipus_trk. }
  OPTIONAL { ?item wdt:P175 ?interpret. }
  OPTIONAL { ?item wdt:P10135 ?data_enreg. }
  OPTIONAL { ?item wdt:P483 ?gravat_a. }
  OPTIONAL { ?item wdt:P162 ?productor. }
  OPTIONAL { ?album p:P658 ?statement.
       ?statement ps:P658 ?item.
       OPTIONAL {?statement pq:P1545 ?pista_ord.}
       OPTIONAL {?statement pq:P3831 ?pista_rol.}
       OPTIONAL { ?album wdt:P31 ?tipus_rls. }
       OPTIONAL { ?album wdt:P577 ?data_publi. }
       OPTIONAL { ?album wdt:P437 ?format_dist. }
           }
  BIND(IF(BOUND(?tipus_rls), ?tipus_rls, ?tipus_trk )
           as ?tipus).
  BIND(IF(BOUND(?pista_ord), ?pista_ord, ?pista_rol)
           as ?pista).
}
GROUP BY ?item ?itemLabel ?tipusLabel ?interpret ?interpretLabel ?album ?albumLabel ?pista ?data_publi

list of recorded track from one specific composition, and all the releases (single/album, etc.) where using

Count contributions per contributor in category

Hello all,

Please I need to know how:

Questions:

- Firstly, I could get the number of contributors on a given category;

- Secondly, how to bring out the names of contributors on a category with the number of their respective contributions.

Example: https://commons.wikimedia.org/w/index.php?title=Category:2021_Africa_Cup_of_Nations

Bile rene (talk) 10:20, 18 June 2022 (UTC)

Hi Bile rene! Would you please define what you mean by contributors on a category? For instance users who created the pages (made the first revision) in the category, or users who made any edits to the pages in the category, or something else? Thank you, --Dipsacus fullonum (talk) 16:29, 18 June 2022 (UTC)

Hi Dipsacus fullonum

Contributors are all users who upload pictures with the category.

Bile rene (talk) 08:15, 19 June 2022 (UTC)

J9U labaels in en and he

I'm trying to create a query of P8189 (j9u) and the labels in English and Hebrew. Simple list doesn't work so try to split it:

• https://w.wiki/5JN9 - P8189 not Q5 (en)
• https://w.wiki/5JNb - P8189 not Q5 (he)
• https://w.wiki/5JPm - P8189 are Q5 not Q6581097 (en)
• https://w.wiki/5JPo - P8189 are Q5 not Q6581097 (he)
• https://w.wiki/5JPs - P8189 are Q5 are Q6581097 (en)
• the same with Hebrew dosen't worked.

My question is, if there is simpler way to create the query. for the last query. I need only the items that have the Hebrew label.

Another question I have is, if there is a possibility to split the outcome to Intervals. From Q1 to Q50000 and another query from Q50000 to Q9999999 (for example). Geagea (talk) 00:12, 19 June 2022 (UTC)

Yeah, there are a few things that can be done. I noticed your queries asked for items with P8189, discard the value, but then later ask for it again. There are times this approach might be better, but more often it would make more sense to just keep the value from the first time. And if you don't need anything particular from a statement (like qualifiers) you can also just use wdt: instead of p:/ps:, this gives you the "simple value" directly.

If I'm understanding you correctly you were just looking for a way to get a whole list without timing out, and had no need for arbitrary subsets, so with that in mind here's a query that gets you the 25 000 first labels. Save the list and add 25 000 to the offset value, run it again and save. Repeat. This will eventually get you the whole list.

SELECT DISTINCT ?item ?label_en ?label_he ?j9u WHERE {
  SERVICE bd:slice {
    ?item wdt:P8189 ?j9u .
    bd:serviceParam bd:slice.offset 0 . # Start at item number (not to be confused with QID)
    bd:serviceParam bd:slice.limit 25000 . # List this many items
  }
  # If you need more properties add them here
  OPTIONAL { ?item rdfs:label ?label_en. FILTER(LANG(?label_en)="en") }
  OPTIONAL { ?item rdfs:label ?label_he. FILTER(LANG(?label_he)="he") }
}

Try it!

With regards to your interval question; Unfortunately SPARQL is notoriously bad at doing string operations due to fundamental design decisions, and also SPARQL doesn't provide things like numeric sequences and arrays, but I've done "hacks" to perform both of these things before. These are all advanced topics which only a geek would be interested in so I'll spare you. HTH. Infrastruktur (talk) 12:19, 19 June 2022 (UTC)

Infrastruktur, thank you very much. This was very helpul. 15:34, 19 June 2022 (UTC)

Fetch Data from Wikisource (Wikidata's Sitelink)

Hello, I know how to query Wikidata. But is it possible to query Wikisource (one of Wikidata's sitelinks)?

For example, if I want to retrieve/read the text from here https://es.wikisource.org/wiki/Relaci%C3%B3n_de_un_viaje_al_R%C3%ADo_de_la_Plata:_Cap%C3%ADtulo_1

1- From Wikidata, https://www.wikidata.org/wiki/Q19486628, I can click on the Wikisource link https://es.wikisource.org/wiki/Relaci%C3%B3n_de_un_viaje_al_R%C3%ADo_de_la_Plata

2- Then click on Viaje desde Buenos Aires hasta Perú

How to fetch the data from Wikisource?

Thanks, Tarfahalrashed (talk) 09:07, 19 June 2022 (UTC)

Fetch which data from wikisource? The document in PDF, Wikitext or other format? And you want to do this starting from Wikidata Q-items? Infrastruktur (talk) 14:18, 21 June 2022 (UTC)

Get Wikidata Sitelinks

Hello, 1) How can we query Wikidata to get the sitelinks (Wikipedia, Wikisource, etc) for a given item (e.g. https://www.wikidata.org/wiki/Q462) 2) How can we specify that we only want Wikipedia and in English?

Thanks! Tarfahalrashed (talk) 11:09, 21 June 2022 (UTC)

• Ask for schema:isPartOf specific project in specific language:

  SELECT ?item ?ensitelink ?slsitelink ?enquotelink ?plquotelink {
    VALUES ?item { wd:Q462 wd:Q25169 wd:Q848517 }
    OPTIONAL { ?ensitelink schema:about ?item ; schema:isPartOf <https://en.wikipedia.org/> . }
    OPTIONAL { ?slsitelink schema:about ?item ; schema:isPartOf <https://sl.wikipedia.org/> . }
    OPTIONAL { ?enquotelink schema:about ?item ; schema:isPartOf <https://en.wikiquote.org/> . }
    OPTIONAL { ?plquotelink schema:about ?item ; schema:isPartOf <https://pl.wikiquote.org/> . }
  }
  

  Try it!

Piastu (talk) 11:55, 21 June 2022 (UTC)

And You'll get all site links if You just omit the isPartOf part – ?link schema:about ?item . – this would be enough instead of optionals ([1]), Piastu (talk) 12:35, 21 June 2022 (UTC)

To flesh this out some more, here's pretty much all the columns WD has on sitelinks for three items, but, constrained to show only sitelinks to websites in the group "wikipedia".

SELECT ?item ?itemLabel ?sitelink ?partof ?article ?badgeLabel WHERE 
{
  VALUES ?item { wd:Q462 wd:Q25169 wd:Q848517 }
  ?article schema:about ?item ;
           schema:isPartOf ?partof ; 
           schema:name ?sitelink ;
           schema:inLanguage ?lang .
  OPTIONAL {?article wikibase:badge ?badge .}
  ?partof wikibase:wikiGroup "wikipedia" . hint:Prior hint:runLast true.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

and the same, restricted to EN wikipedia

SELECT ?item ?itemLabel ?sitelink ?article ?badgeLabel WHERE 
{
  VALUES ?item { wd:Q462 wd:Q25169 wd:Q848517 }
  ?article schema:about ?item ;
           schema:isPartOf <https://en.wikipedia.org/> ; 
           schema:name ?sitelink ;
           schema:inLanguage ?lang .
  OPTIONAL {?article wikibase:badge ?badge .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Documentation on sitelinks is here: https://www.mediawiki.org/wiki/Wikibase/Indexing/RDF_Dump_Format#Sitelinks --Tagishsimon (talk) 23:23, 21 June 2022 (UTC)

Get City/Monument info

Hello, I want to know if there is the possibility to extract info of a city or a historical description for monument? For example Milan I want somethings likes "Milan is a city in northern Italy, capital of Lombardy, and the second-most populous city proper in Italy after Rome. The city proper has a population of about 1.4 million, while its metropolitan city has 3.26 million inhabitants. Its continuously built-up urban area (whose outer suburbs extend well beyond the boundaries of the administrative metropolitan city and even stretch into the nearby country of Switzerland) is the fourth largest in the EU with 5.27 million inhabitants. According to national sources, the population within the wider Milan metropolitan area (also known as Greater Milan), is estimated between 8.2 million and 12.5 million making it by far the largest metropolitan area in Italy and one of the largest in the EU." For monuments the historical description ex. "In 1386, Archbishop Antonio da Saluzzo began construction of the cathedral. Start of the construction coincided with the ascension to power in Milan of the archbishop's cousin Gian Galeazzo Visconti, and was meant as a reward to the noble and working classes, who had suffered under his tyrannical Visconti predecessor Barnabò. [...]". For now, I am been able to to this query that return the geolocalitation of Milan

PREFIX wdt:<http://www.wikidata.org/prop/direct/>
PREFIX wikibase:<http://wikiba.se/ontology#>
PREFIX wd:<http://www.wikidata.org/entity/>
PREFIX rdfs:<http://www.w3.org/2000/01/rdf-schema#>
PREFIX psv:<http://www.wikidata.org/prop/statement/value/>
PREFIX p:<http://www.wikidata.org/prop/>
SELECT DISTINCT ?city ?cityLabel ?lat ?long
WHERE {
?city wdt:P31 wd:Q515 .
?city p:P625 ?statement .
?statement psv:P625 ?coordinatenode .
?coordinatenode wikibase:geoLatitude ?lat .
?coordinatenode wikibase:geoLongitude ?long .
?city rdfs:label ?cityLabel .
FILTER (lang(?cityLabel)='en' )
FILTER(STRSTARTS(?cityLabel, "Milan")).
}


Thanks. Ale 7984 (talk) 14:10, 21 June 2022 (UTC)

Fandom article ID (P6262)

Would it be possible to get a list of all Fandom article ID (P6262) statements with the word 'lgbt', 'gender' or 'mogai' in the subdomain? Some examples would be 'lgbta:Xenogender', 'ezgender:Xenogender' and 'the-mogai-community:Xenogender' . --Trade (talk) 20:46, 21 June 2022 (UTC)

@Trade: Here's a start, but not clear to me if "the subdomain" is that part of the string to the left of the colon. Right now my puny regex is looking at the whole string. It might need a tweak.

SELECT ?item ?itemLabel ?fan
WHERE 
{
  ?item wdt:P6262 ?fan. 
  filter(regex(?fan,"lgbt|gender|mogai"))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

Perhaps this.

SELECT ?item ?itemLabel ?fan
WHERE 
{
  ?item wdt:P6262 ?fan. 
  filter(regex(?fan,"lgbt|gender|mogai/:"))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 21:27, 21 June 2022 (UTC)

The subdomain is the string to the left. @Tagishsimon: --Trade (talk) 21:31, 21 June 2022 (UTC)

@Trade: Then, having rtfm, probably this, for any of those strings in any position in the subdomain.

SELECT ?item ?itemLabel ?fan
WHERE 
{
  ?item wdt:P6262 ?fan. 
  filter(regex(?fan,"(lgbt|gender|mogai).*:"))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 21:54, 21 June 2022 (UTC)

Thanks! --Trade (talk) 22:40, 21 June 2022 (UTC)

Find items that use specific files

Hi, I am trying to find items that include in image (P18) files from c:Category:Graves_without_Wikidata_item. I come up with the following query:

SELECT ?file ?item {
  SERVICE wikibase:mwapi {
	 bd:serviceParam wikibase:api "Generator" .
     bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
     bd:serviceParam mwapi:gcmtitle "Category:Graves_without_Wikidata_item" .
     bd:serviceParam mwapi:generator "categorymembers" .
     bd:serviceParam mwapi:gcmtype "page" .
     bd:serviceParam mwapi:gcmlimit "max" .
     bd:serviceParam mwapi:gcmsort "timestamp" .
     bd:serviceParam mwapi:gcmdir "descending" .
     ?file wikibase:apiOutput mwapi:title  .
  }
  BIND(substr(?file,6) as ?fileName) .
  OPTIONAL { 
    ?item wdt:P18 ?fileName .
   }
#  ?item wdt:P31 wd:Q173387 .
#  ?item wdt:P195 wd:Q311 .
  FILTER ( BOUND(?item) ) .
}

Try it!

But I can not get it to work. I think the issue is with the line " ?item wdt:P18 ?fileName ." as I am not sure what the string used by P18 supposed to look like. Jarekt (talk) 03:25, 22 June 2022 (UTC)

@Jarekt: Seems to be this:

SELECT ?file ?item ?fileName ?mmm {
  SERVICE wikibase:mwapi {
	 bd:serviceParam wikibase:api "Generator" .
     bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
     bd:serviceParam mwapi:gcmtitle "Category:Graves_without_Wikidata_item" .
     bd:serviceParam mwapi:generator "categorymembers" .
     bd:serviceParam mwapi:gcmtype "page" .
     bd:serviceParam mwapi:gcmlimit "max" .
     bd:serviceParam mwapi:gcmsort "timestamp" .
     bd:serviceParam mwapi:gcmdir "descending" .
     ?file wikibase:apiOutput mwapi:title  .
  }
  BIND(IRI(concat("http://commons.wikimedia.org/wiki/Special:FilePath/",ENCODE_FOR_URI(substr(?file,6)))) as ?fileName) .
  OPTIONAL { 
    ?item wdt:P18 ?fileName .
   }
}

Try it!

--Tagishsimon (talk) 04:00, 22 June 2022 (UTC)

@Tagishsimon: thank you. --Jarekt (talk) 04:28, 22 June 2022 (UTC)

erstellen von administrativen karten mit wikidata/osm relationen

in der infobox der Vorlage:Infobox Gemeinde in Deutschland (bsp: https://de.wikipedia.org/wiki/Bassum) soll die .svg datei durch mapframe ersetzt oder ergänzt werden. hier der start, weiter weis ich nicht. es muss aus der infobox der landkreis und die q-nummer rausgezogen werden und als umring dargestellt werden, die untergeordneten gemeinden vom landkreis dargestellt werden, die gemeinde des spezifischen artikels als rote fläche hervorgehoben werden. die mapframe soll genau so aussehen: https://de.wikipedia.org/wiki/Datei:Bassum_in_DH.svg

--Kenji (talk)

GROUP_CONCAT when a query uses WITH clauses?

Hello all! I'm helping write queries for WikiProject Biography and sat in on a hack-a-thon a few weeks ago where someone gave me very insightful feedback on how to make my work more focused and efficient by using WITH clauses. I now have the following query:

The following query uses these:

• Properties: Olympic.org athlete ID (archived) (P3171)   , sex or gender (P21)   , sports discipline competed in (P2416)   , country of citizenship (P27)   

  SELECT DISTINCT
  ?person ?personLabel ?sexgenderLabel ?sport ?sportLabel ?citizenship ?citizenshipLabel
  
  WITH
  { SELECT DISTINCT ?person 
      WHERE { ?person wdt:P3171 ?value . 
              ?person wikibase:statements ?statements .
              FILTER (?statements > 50) . }
  } as %athletes
  
  WITH
  { SELECT DISTINCT ?person ?sexgender ?sport ?citizenship
           WHERE { INCLUDE %athletes 
                   { ?person wdt:P21 ?sexgender . }
                   { ?person wdt:P2416 ?sport . }
                   { ?person wdt:P27 ?citizenship . }
  } } as %athletes2
  
  WHERE
   { INCLUDE %athletes2
     SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
  }
  

Try it!

However, I can't seem to find a way to eliminate duplicate rows for people with multiple sports or citizenships. I had previously used GROUP_CONCAT to fix these redundancies, but I can't seem to figure out the right way to structure that command with the new WITH clauses. I'd be immensely helpful for any help you all can offer on this. Thank you so much! (And thank you to that person who assisted me, too, if you see this!) Tjg6ph (talk) 19:13, 23 June 2022 (UTC)

@Tjg6ph: There are a couple of opportunities to aggregate values; how you approach the solution depends in part on exactly what you want the main SELECT to return; in particular, whether you want to GROUP_CONCAT or SAMPLE multiple values, whether you want a multi-value list of QIDs, &c.

So in the example below, I aggregate the %athletes2 subquery by manually fetching EN citizenship labels and then GROUP_CONCATing them, with a "; " separator. And then I aggregate the main query, this time asking for a SAMPLE of ?sportLabel. Note that in this second aggregation, I've specified within the label service call all of the variables for which I want labels - this is, in essence, so that there are labels to SAMPLE - it forces the label service to operate earlier in the process than it normally would. Failing to specify the variables would mean we'd end up with a blank ?sportLabel column. I've not bothered selecting QIds for ?sport and ?citizenship, b/c in my experience, when you're concatenating labels you tend not to want the QIds. YMMV. Finally, I've not addressed any ?sexgender caused duplicates; unsure if there are any.

SELECT DISTINCT
?person ?personLabel ?sexgenderLabel (SAMPLE(?sportLabel_) as ?sportLabel) ?citizenshipLabel

WITH
{ SELECT DISTINCT ?person 
    WHERE { ?person wdt:P3171 ?value . 
            ?person wikibase:statements ?statements .
            FILTER (?statements > 50) . }
} as %athletes

WITH
{ SELECT DISTINCT ?person ?sexgender ?sport (GROUP_CONCAT(?citizenshipLabel_;separator="; ") as ?citizenshipLabel)
         WHERE { INCLUDE %athletes 
                 { ?person wdt:P21 ?sexgender . }
                 { ?person wdt:P2416 ?sport . }
                 { ?person wdt:P27 ?citizenship . ?citizenship rdfs:label ?citizenshipLabel_ . filter(lang(?citizenshipLabel_)="en") }
} GROUP BY ?person ?sexgender ?sport } as %athletes2

WHERE
 { INCLUDE %athletes2
   SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . 
                          ?person rdfs:label ?personLabel . 
                          ?sexgender rdfs:label ?sexgenderLabel .
                          ?sport rdfs:label ?sportLabel_ . }
} GROUP BY ?person ?personLabel ?sexgenderLabel  ?citizenshipLabel

Try it!

--Tagishsimon (talk) 23:13, 23 June 2022 (UTC)

Thanks so much for this! I think the GROUP_CONCAT is closer to what I'm looking for (though it's very helpful to know about SAMPLE as well), so I tried to do the same for sports. Including a second GROUP_CONCAT in the same subquery led to some lists of duplicates (i.e. "Spain; Spain"), but I attempted to fix that with a DISTINCT STR command.

SELECT DISTINCT
?person ?personLabel ?sexgenderLabel ?sportLabel ?citizenshipLabel
WITH
{ SELECT DISTINCT ?person 
    WHERE { ?person wdt:P3171 ?value . 
            ?person wikibase:statements ?statements .
            FILTER (?statements > 50) . }
} as %athletes
WITH
{ SELECT DISTINCT ?person ?sexgender (GROUP_CONCAT(DISTINCT STR(?sportLabel_);separator="; ") as ?sportLabel) (GROUP_CONCAT(DISTINCT STR(?citizenshipLabel_);separator="; ") as ?citizenshipLabel)
         WHERE { INCLUDE %athletes 
                 { ?person wdt:P21 ?sexgender . }
                 { ?person wdt:P2416 ?sport . ?sport rdfs:label ?sportLabel_ . filter(lang(?sportLabel_)="en") }
                 { ?person wdt:P27 ?citizenship . ?citizenship rdfs:label ?citizenshipLabel_ . filter(lang(?citizenshipLabel_)="en") }
} GROUP BY ?person ?sexgender } as %athletes2
WHERE
 { INCLUDE %athletes2
   SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . 
                          ?person rdfs:label ?personLabel . 
                          ?sexgender rdfs:label ?sexgenderLabel . }
} GROUP BY ?person ?personLabel ?sexgenderLabel ?sportLabel ?citizenshipLabel

Try it!

This seems to work as far as I can tell; do you see any potential issues with this setup? Tjg6ph (talk) 13:13, 24 June 2022 (UTC)

(My apologies; I have no idea why those colons appeared at the start of each line, and I can't seem to remove them.) Tjg6ph (talk) 13:15, 24 June 2022 (UTC)

@Tjg6ph: I've removed some cruft; the grouping on the main select was not needed, and so the need to specify labels in the label service call also not needed; nor was stringifying labels in the %athletes2 query. Equally none of these things was harmful, and the slightly shorter SPARQL gets to exactly the same place as you. hth.

SELECT DISTINCT
?person ?personLabel ?sexgenderLabel ?sportLabel ?citizenshipLabel
WITH
{ SELECT DISTINCT ?person 
    WHERE { ?person wdt:P3171 ?value . 
            ?person wikibase:statements ?statements .
            FILTER (?statements > 50) . }
} as %athletes
WITH
{ SELECT DISTINCT ?person ?sexgender (GROUP_CONCAT(DISTINCT ?sportLabel_;separator="; ") as ?sportLabel) (GROUP_CONCAT(DISTINCT ?citizenshipLabel_;separator="; ") as ?citizenshipLabel)
         WHERE { INCLUDE %athletes 
                 { ?person wdt:P21 ?sexgender . }
                 { ?person wdt:P2416 ?sport . ?sport rdfs:label ?sportLabel_ . filter(lang(?sportLabel_)="en") }
                 { ?person wdt:P27 ?citizenship . ?citizenship rdfs:label ?citizenshipLabel_ . filter(lang(?citizenshipLabel_)="en") }
} GROUP BY ?person ?sexgender } as %athletes2
WHERE
 { INCLUDE %athletes2
   SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
}

Try it!

--Tagishsimon (talk) 13:42, 24 June 2022 (UTC)

Marvelous, thanks for the streamlining! Tjg6ph (talk) 13:59, 24 June 2022 (UTC)

Random items with a 'subclass of' statement but not an 'instance of' statement

This would be helpful to find obscure items that might need help AntisocialRyan (Talk) 03:06, 25 June 2022 (UTC)

Possibly, but there are lots of items which have a P279 and manage perfectly well without a P31. Still. Knock yourself out. Increment the slice offset and limit to get different sets. I'll not vouch for the randomness of the results, but it'll probably do.

SELECT ?item ?itemLabel ?P279 ?P279Label WITH { SELECT ?item ?P279 WHERE
  {
    SERVICE bd:slice {
    ?item wdt:P279 ?P279 .
    bd:serviceParam bd:slice.offset 0 . # Start at item number (not to be confused with QID)
    bd:serviceParam bd:slice.limit 100000 . # List this many items
  }
  filter not exists {?item wdt:P31 [] .} 
  } } as %j
WHERE
{
  INCLUDE %j
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 03:20, 25 June 2022 (UTC)

Make a query more visual

Hello, is there a possibility to show both coulour and logo type in a better way, more visual way from that basic query? Thanks! Bouzinac ^{💬●✒️●💛} 15:20, 27 June 2022 (UTC)

I guess probably the easiest way of doing that is using a quick&dirty method. Save the result in HTML format then run this command on it:

sed -E 's/<td>([a-f0-9]{6})<\/td>/<td><span style="color: #\1">\1<\/span><\/td>/gi; s/(http:\/\/commons\.wikimedia\.org[^<]+)/<img src="\1\?height=50" style="height: 50px" \/>/gi' query.html > query2.html

That will do a couple of search&replace operations on the file "query.html" and save the result as "query2.html" which you can then open in a web-browser. The biggest problem here will be installing sed, lol. I guess the easiest way is to log into PAWS, upload the HTML file there and open a Terminal and run sed from there. Another alternative is Windows Subsystem for Linux. Infrastruktur (talk) 07:43, 28 June 2022 (UTC)

Well, I suppose other way would be to copy-paste into Excel and use macro to colorize from HTML hexacolour into RGB ... but well... https://w.wiki/5MuQ Bouzinac ^{💬●✒️●💛} 08:16, 28 June 2022 (UTC)

I guess a less quick and dirty way would be to add a « color » datatype in Wikidata and make the query service nicely handle it. It’s some work however. A nice collective project however ? author  TomT0m / talk page 08:56, 28 June 2022 (UTC)

Ticket created

Tracked in Phabricator
Task T311483

author  TomT0m / talk page 09:15, 28 June 2022 (UTC)

Thumbs up. 24 bits was AFAIK for some time considered sufficient to represent all colors discernible by humans, but this model is too simplistic. The human eye is more sensitive to changes in luminance than to changes in chrominance something that video compression schemes know to take advantage of. It's a good idea to have a datatype for color, but chances are the best way to natively store this isn't RGB. Maybe a floating point version of YUV or YCbCr is closer to optimal? It certainly gives a more accurate representation using the same amount of bits per component. I don't have a developer account so if someone can copy and paste this into Phabricator that would be great, thanks. Infrastruktur (talk) 12:30, 28 June 2022 (UTC)

I'm thinking a binary encoding using 10 bits for each YCbCr component to fit within 32 bits would be the sweetspot, it should be psychometrically suffient and can be processed efficiently by your grandpa's computer. Most people would probably still enter 24-bit RGB values anyways so there's not much of a point in going any higher in resolution. Infrastruktur (talk) 22:44, 28 June 2022 (UTC)

The query service can handle RGB colour codes as colour codes, but only in certain very specific ways - see Wikidata:SPARQL query service/Wikidata Query Help/Result Views and look for the examples using ?rgb. It can colour items in the graph results view, points/lines/areas on the map results view, and bubbles in the bubble-chart view, but not I think anything else, including the charts or the normal table view.

However, there is a way to hack tables to display it using the Listeria tool, which generates tables on the wiki - see Wikidata:WikiProject British Politicians/colours for an example. It uses a very carefully constructed BIND element to turn the rgb values into coloured table cells. Andrew Gray (talk) 20:55, 28 June 2022 (UTC)

Items with RISM-ID and deWikipedia excluding items which have Template:RISM set in deWiki

the following query should return items, which have the RISM ID (P5504) and sitelink to German Wikipedia and i want to exclude all the items which already have on their German Wikipedia page the template Template:Vorlage:RISM already included. i am totally unsure, if my solution especially with bound() works well. My first idea to build this with two subqueries and substracting them with MINUS run into timeout.

SELECT DISTINCT ?item ?deWP ?rism (CONCAT("[[:Template:RISM]]") AS ?VorlageRISM) WHERE {
  {
    SELECT ?item ?deWP ?rism  WHERE {
      ?item wdt:P5504 ?rism.
      ?deWP schema:about ?item;
        schema:isPartOf <https://de.wikipedia.org/>.
        OPTIONAL {
      SERVICE wikibase:mwapi {
     # in
     bd:serviceParam wikibase:endpoint "de.wikipedia.org";
                     wikibase:api "Generator";
                     mwapi:generator "embeddedin";
                     mwapi:geititle "Vorlage:RISM";
                     mwapi:geinamespace "0".
    
     # out
     ?rismItem wikibase:apiOutputItem mwapi:item.

    } 
    }
     
  }
  }
   FILTER(!bound(?rismItem))
}

Try it!

--Mfchris84 (talk) 10:46, 28 June 2022 (UTC)

popularity of network members on social and web media

I would love to see popularity-use of (past and present) network members on different social and web media.

Ideally the network would be Q96376996 and use and popularity could be displayed service (Twitter, FB, Instagram, Youtube, Vimeo) and each year (from starting an account with 0 followers to fame ;-p)... my gut feeling is that we think that these media are widely used and give results, but in reality only very few network members have positive impact. -- Zblace (talk) 03:43, 30 June 2022 (UTC)