Shortcuts: WD:RAQ, w.wiki/LX

Wikidata:Request a query

From Wikidata
Jump to navigation Jump to search

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/07.

Show bubble chart by place, gender of printer and/or language

Hi everyone,

Last month I requested a bubble chart to show results for printed matter (Q1261026) and publication (Q732577) items by year for the period 1700 to 1799. Now I upgraded my attempt to a somewhat more complex level. I attempted the easy way to get "places of publication" bubbles instead of years, by replacing years with places, but that obviously did not work.

So my goal would be for the same period to show a count of printed matter (Q1261026) and publication (Q732577) by place of publication, by gender of the printer (Q175151 or) and language of printed work (P407 > Q?), three different graphs really, if that is possible. I add below the base to get year bubbles (proportion of printed matter or publications published each year) in the places of publication specified, which may help. Thank you in advance for your time 

#title:Publications by year (Basque Country)
#defaultView:BubbleChart
SELECT (str(?year_) as ?year) (count(*) as ?count)
WHERE
{
  VALUES ?place {wd:Q8692 wd:Q10313 wd:Q497801 wd:Q14318 wd:Q10282 wd:Q134674}
  ?item wdt:P291 ?place.
  ?item wdt:P577 ?publishing_date . 
  FILTER(?publishing_date >= "1700-01-01"^^xsd:dateTime && ?publishing_date <= "1799-12-31"^^xsd:dateTime)
  BIND(YEAR(?publishing_date) as ?year_)
}
GROUP BY ?year_ ORDER BY DESC(?count)
Publications by year (Basque Country)

Inaki LL (talk) 08:27, 13 September 2022 (UTC)[reply] 

#defaultView:BubbleChart
SELECT ?clabel ?count
WITH {
  SELECT DISTINCT ?item ?place ?placeLabel ?publishing_date ?gender ?language
  WHERE {
    ?item wdt:P291 ?place ;
      wdt:P577 ?publishing_date; 
      wdt:P31 ?inst. 
    FILTER(?inst IN (wd:Q1261026, wd:Q732577))
    FILTER(?place IN (wd:Q8692, wd:Q10313, wd:Q497801, wd:Q14318, wd:Q10282, wd:Q134674))
    FILTER(?publishing_date >= "1700-01-01"^^xsd:dateTime && ?publishing_date < "1800-01-01"^^xsd:dateTime)
    ?place rdfs:label ?placeLabel. FILTER(LANG(?placeLabel)="en")
    OPTIONAL { ?item wdt:P872/wdt:P21/rdfs:label ?gender_ . FILTER(LANG(?gender_)="en") }
    OPTIONAL { ?item wdt:P407/rdfs:label ?language_ . FILTER(LANG(?language_)="en") }
    BIND(COALESCE(?gender_, "unspecified") AS ?gender)
    BIND(COALESCE(?language_, "unspecified") AS ?language)
  }
} AS %i
WHERE {
  {
    SELECT ?placeLabel (count(?item) as ?count)
    WHERE { INCLUDE %i }
    GROUP BY ?placeLabel
  }
  BIND(CONCAT(?placeLabel, " ", STR(?count)) AS ?clabel)
}
ORDER BY DESC(?count)
Try it! 
#defaultView:BubbleChart
SELECT ?clabel ?count
WITH {
  SELECT DISTINCT ?item ?place ?placeLabel ?publishing_date ?gender ?language
  WHERE {
    ?item wdt:P291 ?place ;
      wdt:P577 ?publishing_date; 
      wdt:P31 ?inst. 
    FILTER(?inst IN (wd:Q1261026, wd:Q732577))
    FILTER(?place IN (wd:Q8692, wd:Q10313, wd:Q497801, wd:Q14318, wd:Q10282, wd:Q134674))
    FILTER(?publishing_date >= "1700-01-01"^^xsd:dateTime && ?publishing_date < "1800-01-01"^^xsd:dateTime)
    ?place rdfs:label ?placeLabel. FILTER(LANG(?placeLabel)="en")
    OPTIONAL { ?item wdt:P872/wdt:P21/rdfs:label ?gender_ . FILTER(LANG(?gender_)="en") }
    OPTIONAL { ?item wdt:P407/rdfs:label ?language_ . FILTER(LANG(?language_)="en") }
    BIND(COALESCE(?gender_, "unspecified") AS ?gender)
    BIND(COALESCE(?language_, "unspecified") AS ?language)
  }
} AS %i
WHERE {
  {
    SELECT ?gender (count(?item) as ?count)
    WHERE { INCLUDE %i }
    GROUP BY ?gender
  }
  BIND(CONCAT(?gender, " ", STR(?count)) AS ?clabel)
}
ORDER BY DESC(?count)
Try it! 
#defaultView:BubbleChart
SELECT ?clabel ?count
WITH {
  SELECT DISTINCT ?item ?place ?placeLabel ?publishing_date ?gender ?language
  WHERE {
    ?item wdt:P291 ?place ;
      wdt:P577 ?publishing_date; 
      wdt:P31 ?inst. 
    FILTER(?inst IN (wd:Q1261026, wd:Q732577))
    FILTER(?place IN (wd:Q8692, wd:Q10313, wd:Q497801, wd:Q14318, wd:Q10282, wd:Q134674))
    FILTER(?publishing_date >= "1700-01-01"^^xsd:dateTime && ?publishing_date < "1800-01-01"^^xsd:dateTime)
    ?place rdfs:label ?placeLabel. FILTER(LANG(?placeLabel)="en")
    OPTIONAL { ?item wdt:P872/wdt:P21/rdfs:label ?gender_ . FILTER(LANG(?gender_)="en") }
    OPTIONAL { ?item wdt:P407/rdfs:label ?language_ . FILTER(LANG(?language_)="en") }
    BIND(COALESCE(?gender_, "unspecified") AS ?gender)
    BIND(COALESCE(?language_, "unspecified") AS ?language)
  }
} AS %i
WHERE {
  {
    SELECT ?language (count(?item) as ?count)
    WHERE { INCLUDE %i }
    GROUP BY ?language
  }
  BIND(CONCAT(?language, " ", STR(?count)) AS ?clabel)
}
ORDER BY DESC(?count)
Try it!
Something like this? Infrastruktur (talk) 13:38, 13 September 2022 (UTC)[reply]
Sorry for the delay and thanks Infrastruktur! The query on gender shows three bubbles: men, women and another (parasitic) one in between. Could we remove that? Also, would it be possible to show figures inside the bubbles? Inaki LL (talk) 14:41, 13 September 2022 (UTC)[reply]
The language query also shows a parasitic column (only) in the bar chart view (second to Spanish in size and prior to Latin). Could this be removed?
Additionally, could the place label query show languages of publications/printed matter for each place of publication, with different colours? And another chart with count of publications/printed matter by decades (10...) and different colour for each language? I think presentation for this query is best in bar chart view... Great if you could give me a clue on this (I hope I start to get the mechanics of it all...). Regards Inaki LL (talk) 15:15, 13 September 2022 (UTC)[reply]
The "parasitic column" may be due to undefined language of work or title (no P407 defined) Inaki LL (talk) 16:09, 13 September 2022 (UTC)[reply]
One could leave out the unspecified values, but this will give a statistically wrong picture. Rather than leave it out I made it explicit. Also changed the queries to show the amount if there is space for it. I don't think the graphing framework that is used supports barcharts with groups, but you can paste the output from this query into LibreOffice Calc or some other tool to produce the graph. The graphing framework also seems to want to plot values in descending order, so you might have to use a spreadsheet to make the decades graph as well.
SELECT ?placeLabel ?language ?count
WITH {
  SELECT DISTINCT ?item ?place ?placeLabel ?publishing_date ?decade ?gender ?language
  WHERE {
    ?item wdt:P291 ?place ;
      wdt:P577 ?publishing_date; 
      wdt:P31 ?inst. 
    FILTER(?inst IN (wd:Q1261026, wd:Q732577))
    FILTER(?place IN (wd:Q8692, wd:Q10313, wd:Q497801, wd:Q14318, wd:Q10282, wd:Q134674))
    FILTER(?publishing_date >= "1700-01-01"^^xsd:dateTime && ?publishing_date < "1800-01-01"^^xsd:dateTime)
    ?place rdfs:label ?placeLabel. FILTER(LANG(?placeLabel)="en")
    OPTIONAL { ?item wdt:P872/wdt:P21/rdfs:label ?gender_ . FILTER(LANG(?gender_)="en") }
    OPTIONAL { ?item wdt:P407/rdfs:label ?language_ . FILTER(LANG(?language_)="en") }
    BIND(COALESCE(?gender_, "unspecified") AS ?gender)
    BIND(COALESCE(?language_, "unspecified") AS ?language)
    BIND(CONCAT(SUBSTR(STR(?publishing_date), 1, 3), "0") AS ?decade)
  }
} AS %i
WHERE {
  {
    SELECT ?placeLabel ?language (COUNT(?item) AS ?count)
    WHERE { INCLUDE %i }
    GROUP BY ?placeLabel ?language 
  }
}
ORDER BY ?placeLabel DESC(?count)
Try it! 
SELECT ?decade ?count
WITH {
  SELECT DISTINCT ?item ?place ?placeLabel ?publishing_date ?decade ?gender ?language
  WHERE {
    ?item wdt:P291 ?place ;
      wdt:P577 ?publishing_date; 
      wdt:P31 ?inst. 
    FILTER(?inst IN (wd:Q1261026, wd:Q732577))
    FILTER(?place IN (wd:Q8692, wd:Q10313, wd:Q497801, wd:Q14318, wd:Q10282, wd:Q134674))
    FILTER(?publishing_date >= "1700-01-01"^^xsd:dateTime && ?publishing_date < "1800-01-01"^^xsd:dateTime)
    ?place rdfs:label ?placeLabel. FILTER(LANG(?placeLabel)="en")
    OPTIONAL { ?item wdt:P872/wdt:P21/rdfs:label ?gender_ . FILTER(LANG(?gender_)="en") }
    OPTIONAL { ?item wdt:P407/rdfs:label ?language_ . FILTER(LANG(?language_)="en") }
    BIND(COALESCE(?gender_, "unspecified") AS ?gender)
    BIND(COALESCE(?language_, "unspecified") AS ?language)
    BIND(CONCAT(SUBSTR(STR(?publishing_date), 1, 3), "0") AS ?decade)
  }
} AS %i
WHERE {
  {
    SELECT ?decade (COUNT(?item) AS ?count)
    WHERE { INCLUDE %i }
    GROUP BY ?decade 
  }
}
ORDER BY ?decade
Try it!
Infrastruktur (talk) 19:00, 13 September 2022 (UTC)[reply]
Thanks again Infrastruktur,
You are right, the undefined (language) column shown is fine, else the result would no be representative of the real data. Inaki LL (talk) 16:45, 14 September 2022 (UTC)[reply]
@Tagishsimon, Dipsacus fullonum: As a PSA sort of thing for the people who likes to answer questions; I have reported in the past that Blazegraph has known bugs with its VALUES implementation, at least when in comes to handling duplicates. You might think that using the FILTER IN construction might be less effective than a basic graph pattern (BGP), but IRL it seems most SPARQL engines combines a BGP with a filter so they should be functionally equivalent and as far as I can tell they perform pretty well too, but you might want to run your own benchmarks. Infrastruktur (talk) 19:07, 15 September 2022 (UTC)[reply]
@Infrastruktur: What are the known bugs regarding VALUES? I haven't seen your report, and I couldnẗ find anything on this in Phabicator. (PS: I know PSA as "prostate-specific antigen", but what is it here?) --Dipsacus fullonum (talk) 20:04, 15 September 2022 (UTC)[reply]
This was what I reported at the time: https://www.wikidata.org/w/index.php?title=Wikidata:Report_a_technical_problem&diff=1635095890&oldid=1634386263 I trust you would agree this is a rather fundamental and egregious problem? Infrastruktur (talk) 20:23, 15 September 2022 (UTC)[reply]
@Infrastruktur: To me, it seems like a bug in the label service implementation, and not in the "values" implementation. I wouldn't call it a fundamental and egregious problem because it doesn't seem to be a big problem in practice. When would you or anyone use queries like the reported? It certainly isn't an good argument against using "values" instead of "filter" in the queries in this thread. --Dipsacus fullonum (talk) 21:03, 15 September 2022 (UTC)[reply]
It is a good argument against VALUES that the database engine does not properly support it, and its implementation does not follow the specification. So shame on Blazegraph. *sad face*. As far as the argument maybe it's just the label service goes, yeah, but AFAICT it seems to be based on joins which is a graph engine thing. Post scriptum. PSA is short for 'Public Service Annoucement'. Infrastruktur (talk) 23:39, 15 September 2022 (UTC)[reply]

Search for person by name

I found this document on commons about a "Arthur Goldschmidt" (1902-1970) . So I search in Wikidata for this person. 

#defaultView:Table
SELECT distinct ?item ?itemLabel ?itemDescription ?date_of_birth ?date_of_death

WITH {
  SELECT ?item
  WHERE
  {
    ?item rdfs:label "Arthur Goldschmidt"@en .
    ?item wdt:P31 ?instance_of .
  }
} AS %person

WHERE {
  INCLUDE %person.
 
  #OPTIONAL { ?item wdt:P19 ?place_of_birth }. 
  #filter (?place_of_birth = wd:Q64)          # Berlin
  #OPTIONAL { ?item wdt:P18 ?image. }        # image
  OPTIONAL { ?item wdt:P569 ?date_of_birth }.
  #OPTIONAL { ?item wdt:P570 ?date_of_death }.
  #FILTER (?date_of_birth >= "1902-02-13T00:00:00Z"^^xsd:dateTime)  # born after
  #FILTER (?date_of_birth <= "1902-02-13T00:00:00Z"^^xsd:dateTime)  # born before
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en". }
}

order by ?date_of_birth
Try it!

With this first query I get only all items with a name exact "Arthur Goldschmidt". I need more a LIKE query with someone like Georges-Arthur Goldschmidt (Q88551). 

#defaultView:Table
SELECT ?item ?itemLabel ?itemDescription
# ?image ?date_of_birth 
WHERE {
  ?item wdt:P31 wd:Q5 .  # human
  ?item rdfs:label ?name . 
  # FILTER(REGEX(STR(?name), "Arthur Goldschmidt"))             
  FILTER (CONTAINS(?name, 'Arthur Goldschmidt')) .        
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de,en". }
}
LIMIT 25
Try it!

With the secound query I try to find all items with contain "Arthur Goldschmidt", but I get every time a timeout. Any ideas, how to make the query better? -- sk (talk) 15:09, 14 September 2022 (UTC)[reply]

@Stefan Kühn: Using WikibaseCirrusSearch (mw:Help:Extension:WikibaseCirrusSearch) via the MWAPI interface is better at fuzzy label search than SPARQL:
SELECT ?item ?itemLabel ?itemDescription ?place_of_birthLabel ?date_of_birth ?date_of_death
WHERE
{
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "www.wikidata.org" .
    bd:serviceParam wikibase:api "Generator" .
    bd:serviceParam mwapi:generator "search" .
    bd:serviceParam mwapi:gsrsearch "inlabel:'Arthur Goldschmidt'" .
    bd:serviceParam mwapi:gsrlimit "max" .
    ?item wikibase:apiOutputItem mwapi:title.
  }
  ?item wdt:P31 wd:Q5 . # Only humans
  OPTIONAL { ?item wdt:P19 ?place_of_birth }
  OPTIONAL { ?item wdt:P569 ?date_of_birth }.
  OPTIONAL { ?item wdt:P570 ?date_of_death }.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de,en". }
}
Try it!
--Dipsacus fullonum (talk) 16:36, 14 September 2022 (UTC)[reply]
@Dipsacus fullonum Many thanks. This is interesting. In the future I will use this CirusSearch. --sk (talk) 18:35, 14 September 2022 (UTC)[reply]

SUBSTR is not working in filter

Hi there !

Beginner's question : I have a very simple query : I just want only the cats with an "a" on second letter of their names. This query shows effectively some cats with "a" on second position  :

SELECT ?NameCat (SUBSTR(?NameCat,2,1) AS ?Letter)

WHERE

{

  ?Cat wdt:P31 wd:Q146.

  ?Cat rdfs:label ?NameCat.

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

  #FILTER(SUBSTR(?NameCat,2,1) = "a")

}

But if I remove the # on the line with "FILTER", there is no records displaying... Why ?????????

Thanks in advance Klymandre (talk) 20:52, 15 September 2022 (UTC)[reply]

?NameCat is a literal with language tag. If you compare against "a"@en, it works. —MisterSynergy (talk) 21:00, 15 September 2022 (UTC)[reply]
Oh, Thanks !!! ... Now you're tell me why, it appears like an evidence ! Klymandre (talk) 21:48, 16 September 2022 (UTC)[reply]
It's a lovely query, Klymandre. Another option achieving the same end as MisterSynergy's, is to stringify ?NameCat within the FILTER, as below. tbh the "a"@en within a FILTER solution would not have occurred to me, although I've used the same formulation in VALUES statements, so, things I learn.
SELECT ?NameCat (SUBSTR(?NameCat,2,1) AS ?Letter)
WHERE
{
  ?Cat wdt:P31 wd:Q146.
  ?Cat rdfs:label ?NameCat.
  FILTER(LANG(?NameCat) = "en").
  FILTER(SUBSTR(STR(?NameCat),2,1) = "a")
}
Try it!
--Tagishsimon (talk) 22:06, 16 September 2022 (UTC)[reply]

Fixing up the details: Timeline

Hi, I would like to show all items (publications & printed matter Q732577 Q1261026) in a timeline, but would like to round up to year (no YYYYMMDD format) and show the labels, not the QIDs as they appear now. I tried some solutions adding itemLabel and placeLabel at different positions in the expression, but not my forte... it did not work out. Additionally, would it possible to expand horizontally the view within the window for a wider year span? 

# tool: scholia
#defaultView:Timeline
SELECT ?item ?label ?place ?argitaratze_data WHERE {
  VALUES ?type {
    wd:Q732577 wd:Q1261026
  }
  ?item wdt:P31 ?type;
    wdt:P577 ?argitaratze_data.
  FILTER((?argitaratze_data > "1700-01-01"^^xsd:dateTime) && (?argitaratze_data < "1799-12-31"^^xsd:dateTime))
  BIND(YEAR(?argitaratze_data) as ?year_)
  VALUES ?place {wd:Q8692 wd:Q10313 wd:Q497801 wd:Q14318 wd:Q10282 wd:Q134674}  
  ?item wdt:P291 ?place.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en, eu". }
}
Try it!

If I exported the results to Scholia, for something like this, what codes or extension should I use? Thank you Inaki LL (talk) 15:44, 16 September 2022 (UTC)[reply]

@Inaki LL: (reply after edit conflict)
1. In order to show labels, you have to select the variables for the labels in the select clause (see mw:Wikidata Query Service/User Manual#Label service)
2. The timeline view needs a variable of the DateTime datatype, so you cannot only give it the year. You can however hide the the DateTime variable (?argitaratze_data) with the hide option, and show the year only (see Wikidata:SPARQL query service/Wikidata Query Help/Result Views#Timeline)
3. Over the timeline there is among others "+" in a circle icon you can use to expand the timeline.
4. Note that I changed ">" and "<" to ">=" and "<=" as I assume you want all dates in the 1700 years.
# tool: scholia
#defaultView:Timeline{"hide":["?argitaratze_data"]}
SELECT ?argitaratze_data ?itemLabel ?placeLabel ?year WHERE {
  VALUES ?type {
    wd:Q732577 wd:Q1261026
  }
  ?item wdt:P31 ?type;
    wdt:P577 ?argitaratze_data.
  FILTER((?argitaratze_data >= "1700-01-01"^^xsd:dateTime) && (?argitaratze_data <= "1799-12-31"^^xsd:dateTime))
  BIND(YEAR(?argitaratze_data) as ?year)
  VALUES ?place {wd:Q8692 wd:Q10313 wd:Q497801 wd:Q14318 wd:Q10282 wd:Q134674}  
  ?item wdt:P291 ?place.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,eu". }
}
Try it!
--Dipsacus fullonum (talk) 16:18, 16 September 2022 (UTC)[reply]
@Dipsacus fullonum Wow, many thanks for mending my attempt and the explanations provided! I am trying to understand the points you outlined.
2. I would understand that the date of publication (argitaratze_data) refers to the whole date, so if I hide argitaratze_data, as instructed to SPARQL in line 2, why is it not hiding the date altogether? I see BIND as ?year, but that appears to me unrelated to showing or not showing, right?
3. Yes, there is, but the area for the results in the Timeline view is divided into two columns. One is blank on the right, and the other on the left holds the timeline, so if I press +, the timeline expands horizontally within the left column, but it offers little room. Also, the boxes and their print are quite large, so no room to show the results. Can sth be done about that?
4. You are right, and this mend appeared before, as corrected by Infrastruktur, but I used the wrong template variant. Inaki LL (talk) 07:37, 17 September 2022 (UTC)[reply]
The volume of entries for each year is quite large anyway, and I am not sure if I will be able to accommodate in some way all of them. Inaki LL (talk) 07:42, 17 September 2022 (UTC)[reply]
@Inaki LL: ?argitaratze_data is used to construct the timeline, but the hide option means that the value isn't displayed in the boxes. It is hidden al together. The year in the boxes comes from the variable ?year. Both ?argitaratze_data and ?year are in SELECT clause. The former is hidden, and the latter is displayed in the timeline boxes. --Dipsacus fullonum (talk) 09:26, 17 September 2022 (UTC)[reply]
Yes, the boxes are large when they contain long labels. You asked for the labels the be shown instead of the QID, and the consequence is large boxes. You can't have your cake and eat it too! --Dipsacus fullonum (talk) 09:34, 17 September 2022 (UTC)[reply]
Thanks, Dipsacus fullonum. Would it be possible to constrain the titleLabel on show to, say, 50 characters, instead of the 249 allowed in WD? Inaki LL (talk) 11:11, 17 September 2022 (UTC)[reply]
@Inaki LL: Sure. I also added an ellipsis to truncated labels. You will notice that I switched the label service from automatic mode to manual mode. That is necessary in order to use the labels as arguments in string functions like SUBSTR (substring). This is explained in the user manual for the label service (link above).
# tool: scholia
#defaultView:Timeline{"hide":["?argitaratze_data"]}
SELECT ?argitaratze_data ?itemLabelStart ?placeLabel ?year WHERE {
  VALUES ?type {
    wd:Q732577 wd:Q1261026
  }
  ?item wdt:P31 ?type;
    wdt:P577 ?argitaratze_data.
  FILTER((?argitaratze_data >= "1700-01-01"^^xsd:dateTime) && (?argitaratze_data <= "1799-12-31"^^xsd:dateTime))
  BIND(YEAR(?argitaratze_data) as ?year)
  VALUES ?place {wd:Q8692 wd:Q10313 wd:Q497801 wd:Q14318 wd:Q10282 wd:Q134674}  
  ?item wdt:P291 ?place.
  BIND(IF(STRLEN(?itemLabel) > 50, CONCAT(SUBSTR(?itemLabel, 1, 50),"…"), ?itemLabel) AS ?itemLabelStart)
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,eu".
    ?item rdfs:label ?itemLabel .
    ?place rdfs:label ?placeLabel .
  }
}
Try it!
--Dipsacus fullonum (talk) 14:22, 17 September 2022 (UTC)[reply]
It worked out! Still it shows the view as half the screen, a column, so there is little room for horizontal display. The print remains pretty large and boxes are large. I know, that is tricky, but would there be any way out of those constraints? Inaki LL (talk) 17:27, 17 September 2022 (UTC)[reply]
Why not a normal table sorted after publication date? --Dipsacus fullonum (talk) 18:22, 17 September 2022 (UTC)[reply]
Not as visual, but may be the solution... Inaki LL (talk) 04:22, 19 September 2022 (UTC)[reply]

Properties of Country

Can I get all the available properties of a country (Q6256)? Can't find the full list. Hopejesus55 (talk) 15:17, 18 September 2022 (UTC)[reply]

@Hopejesus55: What properties you should use, is for discussion elsewhere (and probably depends on the country of the county), but I can make a query saying which properties are used in county items: 
SELECT ?property ?propertyLabel ?count
WITH
{
  SELECT DISTINCT ?country
  {
    ?country wdt:P31 / wdt:P279 * wd:Q6256 . # ?country is a country or a subclass of it
  }
} AS %countries
WITH
{
  SELECT ?property (COUNT(*) AS ?count)
  {
    INCLUDE %countries
    ?country ?truthy_claim [] .
    ?property wikibase:directClaim ?truthy_claim
  }
  GROUP BY ?property
} AS %count
{
  INCLUDE %count
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}
ORDER BY DESC(?count)
Try it!
--Dipsacus fullonum (talk) 16:24, 18 September 2022 (UTC)[reply]
Thank you, that's exactly what I was looking for! Hopejesus55 (talk) 17:47, 18 September 2022 (UTC)[reply]
Some are REALLY incorrect, like IPA transcription (P898). --Infovarius (talk) 20:37, 18 September 2022 (UTC)[reply]


Another view of country properties, counting the number of items that have particular properties, rather than the number of property statements; and separating external-ID from non-external-ID properties, fwiw:
SELECT ?property ?propertyLabel ?count ?propertyType
WITH
{
  SELECT DISTINCT ?country
  {
    ?country wdt:P31 / wdt:P279 * wd:Q6256 . # ?country is a country or a subclass of it
  }
} AS %countries
WITH
{
  SELECT ?property (COUNT(DISTINCT ?country) AS ?count)
  {
    INCLUDE %countries
    ?country ?truthy_claim [] .
    ?property wikibase:directClaim ?truthy_claim
  }
  GROUP BY ?property
} AS %count
{
  INCLUDE %count
  OPTIONAL { ?property wikibase:propertyType wikibase:ExternalId.
             BIND("External-ID" as ?propertyType) }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}
ORDER BY ?propertyType DESC(?count)
Try it!
--Tagishsimon (talk) 21:11, 18 September 2022 (UTC)[reply]
I agree that the latter query may give a better overview. I just corrected "county" to "country" in the SPARQL code. For some unknown reason I was erroneously thinking of counties when I wrote the code. --Dipsacus fullonum (talk) 05:42, 19 September 2022 (UTC)[reply]

Different query result?

I'm trying to make a query that allows me to have a list of items related to space missions

example : 

SELECT ?item ?itemLabel 
WHERE 
{ 
{?item wdt:P31/wdt:P279*  wd:Q40218 .} # spacecraft
  UNION {?item wdt:P31/wdt:P279* wd:Q62832 .} # observatory
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} 
GROUP BY ?item ?itemLabel
Try it!

The result of the query gives 10228 elements.

On the other hand, if I separate this request into 2 : 

SELECT ?item ?itemLabel
WHERE 
{  
{?item wdt:P31/wdt:P279*  wd:Q40218 .} # spacecraft

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?item ?itemLabel
Try it!

And 

SELECT ?item ?itemLabel 
WHERE 
{  
  {?item wdt:P31/wdt:P279* wd:Q62832 .} # observatory 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} 
GROUP BY ?item ?itemLabel
Try it!

The first gives 8463 results and the second gives 1925 results. So we have a total of 10388.

Could someone help me to understand? LDEBI (talk) 09:22, 19 September 2022 (UTC)[reply]

@LDEBI: 160 items are an instance or instance of a subclass of both main types of thing you're looking for. So these appear in each of the two smaller reports, but the larger report gives only one row for each of these (because of the GROUP BY) and so there is a 160 difference between the number of rows in report 1, and the sum of the number of rows in reports 2 & 3. The items are:
SELECT ?item ?itemLabel WHERE 
{
  ?item wdt:P31/wdt:P279*  wd:Q40218 . # spacecraft
  ?item wdt:P31/wdt:P279* wd:Q62832 . # observatory
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} GROUP BY ?item ?itemLabel
Try it!
--Tagishsimon (talk) 09:56, 19 September 2022 (UTC)[reply]
Ok, thank you for your help ! It seems clearer now LDEBI (talk) 12:16, 19 September 2022 (UTC)[reply]

Show the figures next to language labels

Hi, I am persuaded there must be a way out in this query to show next to the language label the amounts of items in a given language for each place of publication (Tree map). I used ?clabel on the first line, and ?clabel in SELECT. Still I saw no changes. (I removed clabel for the example below, since it did not work) 

SELECT ?placeLabel ?language ?count
WITH {
  SELECT DISTINCT ?item ?place ?placeLabel ?publishing_date ?decade ?gender ?language
  WHERE {
    ?item wdt:P291 ?place ;
      wdt:P577 ?publishing_date; 
      wdt:P31 ?inst. 
    FILTER(?inst IN (wd:Q1261026, wd:Q732577))
    FILTER(?place IN (wd:Q8692, wd:Q10313, wd:Q497801, wd:Q14318, wd:Q10282, wd:Q134674))
    FILTER(?publishing_date >= "1700-01-01"^^xsd:dateTime && ?publishing_date < "1800-01-01"^^xsd:dateTime)
    ?place rdfs:label ?placeLabel. FILTER(LANG(?placeLabel)="eu")
    OPTIONAL { ?item wdt:P872/wdt:P21/rdfs:label ?gender_ . FILTER(LANG(?gender_)="en") }
    OPTIONAL { ?item wdt:P407/rdfs:label ?language_ . FILTER(LANG(?language_)="eu") }
    BIND(COALESCE(?gender_, "unspecified") AS ?gender)
    BIND(COALESCE(?language_, "unspecified") AS ?language)
    BIND(CONCAT(SUBSTR(STR(?publishing_date), 1, 3), "0") AS ?decade)
  }
} AS %i
WHERE {
  {
    SELECT ?placeLabel ?language (COUNT(?item) AS ?count)
    WHERE { INCLUDE %i }
    GROUP BY ?placeLabel ?language 
  }
}
ORDER BY ?placeLabel DESC(?count)
Try it!

The attempted result for the figures should be something like this (this is a variant by place of publication of the query above): 

SELECT ?language ?languageLabel ?count ?clabel
WITH {
  SELECT DISTINCT ?item ?place ?placeLabel ?publishing_date ?decade ?gender ?language
  WHERE {
    ?item wdt:P291 ?place ;
      wdt:P577 ?publishing_date; 
      wdt:P31 ?inst. 
    FILTER(?inst IN (wd:Q1261026, wd:Q732577))
    FILTER(?place IN (wd:Q8692, wd:Q10313, wd:Q497801, wd:Q14318, wd:Q10282, wd:Q134674))
    FILTER(?publishing_date >= "1700-01-01"^^xsd:dateTime && ?publishing_date < "1800-01-01"^^xsd:dateTime)
    ?place rdfs:label ?placeLabel. FILTER(LANG(?placeLabel)="eu")
    OPTIONAL { ?item wdt:P872/wdt:P21/rdfs:label ?gender_ . FILTER(LANG(?gender_)="en") }
    OPTIONAL { ?item wdt:P407/rdfs:label ?language_ . FILTER(LANG(?language_)="eu") }
    BIND(COALESCE(?gender_, "unspecified") AS ?gender)
    BIND(COALESCE(?language_, "unspecified") AS ?language)
    BIND(CONCAT(SUBSTR(STR(?publishing_date), 1, 3), "0") AS ?decade)
  }
} AS %i
WHERE {
  {
    SELECT ?placeLabel ?language (COUNT(?item) AS ?count)
    WHERE { INCLUDE %i }
    GROUP BY ?placeLabel ?language 
  }
BIND(CONCAT(?placeLabel, " ", STR(?count)) AS ?clabel)
}
ORDER BY ?placeLabel DESC(?count)
Try it!

Thank you again Inaki LL (talk) 09:25, 19 September 2022 (UTC)[reply]

@Inaki LL: In the SELECT clause, you can replace
?language ?count
with
(CONCAT(?language, " ", STR(?count)) AS ?clabel)
like in the your second query above (there the CONCAT function was in a BIND clause instead of the SELECT clause. Both ways are possible and are essentially the same.) If you already tried something similar and it didn't work for you, maybe you forget to use STR() to change the ?count values from numbers to strings which is necessary as CONCAT() only works with string type arguments. --Dipsacus fullonum (talk) 21:22, 19 September 2022 (UTC)[reply]
Wow, yes, looks great now! Thanks Dipsacus Inaki LL (talk) 10:31, 20 September 2022 (UTC)[reply]

Problem with "sub-properties" PQ

Hi, there !

Beginners's question  : here's a query with Georges Favon, place of burial : "Cemetery of kings".

Why, in this query, the line

?Person p:P39 [pq:P580 ?PosHeldBeginDate].

Works perfectly, but :

?Person p:P119 [pq:P985 ?BurialPlot].

Display "No matching records found" if I remove the "#" ????

How can I do to make it works correctly ?

SELECT ?Person ?BurialPlot ?PosHeldBeginDate WHERE

{

VALUES ?Person{wd:Q3102701}.

  ?Person p:P39 [pq:P580 ?PosHeldBeginDate].

  #?Person p:P119 [pq:P985 ?BurialPlot].

}

Thanky in advance ! Klymandre (talk) 15:53, 19 September 2022 (UTC)[reply]

@Klymandre: pq:P985 versus pq:P965.
SELECT ?Person ?BurialPlot ?PosHeldBeginDate WHERE
{
VALUES ?Person {wd:Q3102701}.
  ?Person p:P39 [pq:P580 ?PosHeldBeginDate].
  ?Person p:P119 [pq:P965 ?BurialPlot].
}
Try it!
--Tagishsimon (talk) 16:19, 19 September 2022 (UTC)[reply]
tbh, the most interesting thing about this sort of 'do my eyes deceive me (yes)' sort of probem, is the diagnosis, where the query can be built up piece by piece from the working base, so perhaps step 1, does p:P119 work?
#title:Does p:P119 on its own work?
SELECT ?Person ?BurialPlot ?PosHeldBeginDate WHERE
{
VALUES ?Person {wd:Q3102701}.
  ?Person p:P39 [pq:P580 ?PosHeldBeginDate].
  ?Person p:P119 ?statement .
  #[pq:P985 ?BurialPlot].
}
Does p:P119 on its own work?
And presuming so, let's look at the predicates and values of p:P119
#title:What predicates and values does p:P119 have?
SELECT ?Person ?BurialPlot ?PosHeldBeginDate ?predicate ?value WHERE
{
VALUES ?Person {wd:Q3102701}.
  ?Person p:P39 [pq:P580 ?PosHeldBeginDate].
  ?Person p:P119 ?statement .
  ?statement ?predicate ?value .
}
What predicates and values does p:P119 have?
and in the list of rows returned, we see the pq:P965 which we cut & paste into the query
#title:Will pq:P965 cooperate?
SELECT ?Person ?BurialPlot ?PosHeldBeginDate  WHERE
{
VALUES ?Person {wd:Q3102701}.
  ?Person p:P39 [pq:P580 ?PosHeldBeginDate].
  ?Person p:P119 ?statement .
  ?statement pq:P965 ?BurialPlot .
}
Will pq:P965 cooperate?
after which we can probably remove the use of ?statement and stitch the query back up again to get the top answer. --Tagishsimon (talk) 17:01, 19 September 2022 (UTC)[reply]
And thanks a lot for your very useful query for a better understanding of "sub-properties" ! (#title:What predicates and values does p:P119 have?) Klymandre (talk) 20:04, 19 September 2022 (UTC)[reply]
AAAAAAAAARRRRRRRRRRRGGGGGGGGHHHHHHHHH !!!!! ... [BANG BANG] (Head against walls !) ... I was sooo much sure this problem was due to the simple text and not object !!! Klymandre (talk) 20:02, 19 September 2022 (UTC)[reply]

show Properties of P18 images

The query below will show images of paintings which can be found in Witkacy: Drawing (Q113870515) book 

SELECT ?item ?page ?img WHERE { 
  ?item p:P1433 [ ps:P1433 wd:Q113870515; pq:P304 ?page ] .
  ?item wdt:P18 ?img
  BIND(xsd:float(?page) as ?pageNum) .
} order by ?pageNum
Try it!

I am looking for a way to look up number of rows and columns of those images, so I can find images which I might want to scan. Jarekt (talk) 01:28, 21 September 2022 (UTC)[reply]

Please explain where these numbers can be found by a query. --Dipsacus fullonum (talk) 05:56, 21 September 2022 (UTC)[reply]
Indeed. @Jarekt: What rows and columns? Jheald (talk) 08:04, 21 September 2022 (UTC)[reply]

Extension:WikibaseMediaInfo/RDF mapping describes schema:height and schema:width and c:Commons:SPARQL_query_service/queries/examples#Scatter_chart_showing_height/width_of_a_sample_of_10,000_images has a sample query showing how to use them. But those are properties accessible for Commmons SPARQL queries. I am trying to figure out how to combine them. --Jarekt (talk) 10:40, 21 September 2022 (UTC)[reply]

@Jarekt: It is possible to get to size (height and width) measured in pixels for images at Commons using either WCQS or MWAPI. Is it these numbers you are asking about when you say rows and columns? --Dipsacus fullonum (talk) 12:22, 21 September 2022 (UTC)[reply]
IIRC the wikidata query endpoint blocks requests to the commons endpoint, but the opposite is allowed, which means we can utilize federation. In other words this query will only work if you run it from https://commons-query.wikimedia.org/ (!).
# Federation with correlation - Get height and width for Commons images
# Run this from WCQS - https://commons-query.wikimedia.org/
SELECT ?item ?page ?image ?file ?width ?height
WHERE {
  hint:Query hint:optimizer "None" .
  SERVICE <https://query.wikidata.org/sparql> {
    ?item p:P1433 [ ps:P1433 wd:Q113870515; pq:P304 ?page_ ] ;
      wdt:P18 ?image .
    BIND(xsd:integer(?page_) as ?page) .
  }
  ?file schema:url ?image ;
    schema:height ?height ;
    schema:width ?width .
}
ORDER BY (?width * ?height)
Try it!
Infrastruktur (talk) 19:42, 21 September 2022 (UTC)[reply]
@Infrastruktur:, Thanks This is what I was looking for. --Jarekt (talk) 22:16, 21 September 2022 (UTC)[reply]

Collapsing query

Here's a query to compare how apart stations are from a particular station according to their coordinates, compared to how far they are apart according to a linear reference (P6710) mileage measured along the track. It can be quite helpful in finding stations with the wrong coordinates. (As also is the query's big brother https://w.wiki/5jC5 , especially when used with a map-plotting query like https://w.wiki/5jBW ). 

SELECT  ?ratio  ?item2 ?item2Label ?dist ?mileage_diff ?section ?lin ?lin2 ?sectionLabel 

WITH {
  SELECT ?item ?section ?elr ?lin ?itemLoc WHERE {
    ?item p:P795 ?stmt .
    ?stmt ps:P795 ?section . 
    ?section wdt:P10271 ?elr .
    ?stmt pq:P6710 ?lin .
    ?item wdt:P625 ?itemLoc .
  }
} AS %items

WHERE { 
  VALUES ?item {wd:Q2525068} .
  INCLUDE %items .
  ?item2 wdt:P795 ?section .
  FILTER (?item != ?item2) .
  {
    SELECT (?item AS ?item2) ?section (?lin AS ?lin2) (?itemLoc AS ?item2Loc) WHERE {
      INCLUDE %items .
    }
  }
  BIND ((geof:distance(?itemLoc, ?item2Loc) / 1.609) AS ?dist) .
  BIND (abs(?lin - ?lin2) AS ?mileage_diff) . 
  BIND (?dist / ?mileage_diff AS ?ratio) . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } .
} 
ORDER BY ?lin2
Try it!

However, I'm stumped by one curiosity. If I comment out the line

FILTER (?item != ?item2) .

then I might expect to get one more item in the results. Instead the row with ?item = ?item2 becomes the only row in the results: the query has collapsed.

Can anybody explain (i) why this happens; and (ii) if there is a good way round it? (ie to include the target item in its appropriate place in the table) -- I just can't see it. Thanks, Jheald (talk) 23:26, 21 September 2022 (UTC)[reply]

@Jheald: I looked at the explained queries to see what happens. When you remove the filter, VALUES ?item {wd:Q2525068} is applied globally as a static binding everywhere in the query including in the named subquery (%items), with the consequence that only results with ?item = ?item2 exist. I cannot say exactly why that happens, but the filter restricts the optimizer from doing some operations.
You can fix it by turning off the optimizer (with hint:Query hint:optimizer "None"), but that will also result in ineffective and slow execution. Instead I suggest to make a filter that ostensibly depends on ?item but always evaluates to true. I used FILTER (?item || true) with the correct results. --Dipsacus fullonum (talk) 08:04, 22 September 2022 (UTC)[reply]
JHeald, creatively breaking SPARQL since I don't know when. @Dipsacus fullonum: what are you seeing in the explained queries (with filter, without) that points to the conclusion. Afraid I cannot make head nor tail of the 370k lines of RDF. --Tagishsimon (talk) 08:35, 22 September 2022 (UTC)[reply]
@Tagishsimon: You forgot to include "query=" in the links just before the query code. I corrected the two links and they now return readable HTML code. In the explained query without filter, look at the bottom of the section "Optimized AST":
with static (exogeneous) bindings defined as follows: 
{
  { item=Vocab(2)[http://www.wikidata.org/entity/Q]:XSDUnsignedInt(2525068) }
}
and also note the values for estimatedCardinality in the optimized AST (Abstract syntax tree). --Dipsacus fullonum (talk) 08:58, 22 September 2022 (UTC)[reply]
Before optimization (in the section "Original AST") the values data wasn't static bindings outside the query, but correctly represented as
SELECT VarNode(ratio) VarNode(item2) VarNode(item2Label) VarNode(dist) VarNode(mileage_diff) VarNode(section) VarNode(lin) VarNode(lin2) VarNode(sectionLabel)
  JoinGroupNode {
    BindingsClause ?item
    {
      { item=Vocab(2)[http://www.wikidata.org/entity/Q]:XSDUnsignedInt(2525068) }
    }
    INCLUDE %items
    ...
--Dipsacus fullonum (talk) 09:07, 22 September 2022 (UTC)[reply]
Thank you; that's a fascinating insight. First time user of explain query, but I see what you're pointing to. --Tagishsimon (talk) 09:31, 22 September 2022 (UTC)[reply]
@Dipsacus fullonum: Thanks. That's really interesting (and how you diagnosed it), and a really neat fix. Thank you so much. Jheald (talk) 09:39, 22 September 2022 (UTC)[reply]

Remove publication year

Hello, I'm trying to add a reference URL for the publication years of several publications with QuickStatements. I presume I need to delete the entry, and then re-add it with the reference details.

For example for Q113518919 I have tried running the following, but all of them lead to errors.

-Q113518919 TAB P577 TAB +1920-01-01T00:00:00Z/9

-Q113518919 TAB P577 TAB +1920-00-00T00:00:00Z/9

-Q113518919 TAB P577 TAB 1920

What would be the right way to do this? Thank you! Robertsilen (talk) 09:18, 22 September 2022 (UTC)[reply]

@Robertsilen: I don't recall ever getting QS to delete a date, other than by specifying the statement ID, despite the documentation's promise. But in better news, I tested adding a date, and then adding a reference to the date in the two edits in this diff and it worked fine. Maybe give these two a try with a different date. --Tagishsimon (talk) 09:45, 22 September 2022 (UTC)[reply]
  • Q4115189 TAB P577 TAB +1920-01-01T00:00:00Z/9
  • Q4115189 TAB P577 TAB +1920-01-01T00:00:00Z/9 TAB S248 TAB Q54898890
@Robertsilen: Did you remember to URL encode the characters "+", ":" and "/" in the QuickStatements code, like this https://quickstatements.toolforge.org/#/v1=Q4115189%7CP577%7C%2B1920-00-00T00%3A00%3A00Z%2F9 (for adding a publication date to the sandbox item)? --Dipsacus fullonum (talk) 10:06, 22 September 2022 (UTC)[reply]

Outdoor gyms in Berlin (Q64)

This query times out any solution? 

#title: Berlin outdoor gyms on a map
#defaultView:Map{"hide":["?coord"], "layer": "?adminLabel"}
SELECT ?id ?item ?itemLabel ?itemDescription ?img ?coord ?www ?adminLabel WHERE {
  ?item wdt:P6104 wd:Q107186275;
    wdt:P17 wd:Q183;
    (wdt:P131*) wd:Q64.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en". }
  OPTIONAL { ?item wdt:P18 ?img. }
  OPTIONAL { ?item wdt:P131 ?admin. }
  OPTIONAL { ?item wdt:P856 ?www. }
  OPTIONAL { ?item wdt:P625 ?coord. }
  OPTIONAL { ?item wdt:P217 ?id. }
}
Berlin outdoor gyms on a map

Salgo60 (talk) 11:10, 22 September 2022 (UTC)[reply]

@Salgo60: A hint. Some few notes here
#title: Berlin outdoor gyms on a map
#defaultView:Map{"hide":["?coord"], "layer": "?adminLabel"}
SELECT ?id ?item ?itemLabel ?itemDescription ?img ?coord ?www ?adminLabel WHERE {
  ?item wdt:P6104 wd:Q107186275;
    wdt:P17 wd:Q183.
  ?item wdt:P131* wd:Q64. hint:Prior hint:gearing "forward".
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en". }
  OPTIONAL { ?item wdt:P18 ?img. }
  OPTIONAL { ?item wdt:P131 ?admin. }
  OPTIONAL { ?item wdt:P856 ?www. }
  OPTIONAL { ?item wdt:P625 ?coord. }
  OPTIONAL { ?item wdt:P217 ?id. }
}
Berlin outdoor gyms on a map
--Tagishsimon (talk) 11:22, 22 September 2022 (UTC)[reply]

Causes of Death

I’m trying to obtain a list of all causes of death (Property:P509) in humans, together with their respective number of occurrences (items that link to this cause of death via P509).

This works, but only gives me distinct causes of death without their number of occurrences: 

SELECT DISTINCT ?causeOfDeath ?causeOfDeathLabel
WHERE 
{
  ?item wdt:P31 wd:Q5;
        wdt:P509 ?causeOfDeath.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

The following query, which is this adapted for the above, produces an error (Bad aggregate): 

SELECT ?causeOfDeath ?causeOfDeathLabel (COUNT(?item) as ?count)
WHERE 
{
  ?item wdt:P31 wd:Q5;
        wdt:P509 ?causeOfDeath.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?causeOfDeath
Try it!

How can I obtain the desired list? --2A02:8108:50BF:C694:2976:C1E3:FFCD:5CC4 12:30, 22 September 2022 (UTC)[reply]

Need to group everything that is not aggregated in the select - in this case, adding ?causeOfDeathLabel
SELECT ?causeOfDeath ?causeOfDeathLabel (COUNT(?item) as ?count)
WHERE 
{
  ?item wdt:P31 wd:Q5;
        wdt:P509 ?causeOfDeath.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?causeOfDeath ?causeOfDeathLabel
Try it!
--Tagishsimon (talk) 12:33, 22 September 2022 (UTC)[reply]
Oh. That’s surprisingly easy. Thank you! --2A02:8108:50BF:C694:2976:C1E3:FFCD:5CC4 12:58, 22 September 2022 (UTC)[reply]
The query will use a lot of time looking up over 100,000 labels before grouping. If you group by ?causeOfDeath in a subquery before finding labels, the query will be much faster (going from 28 to 3 seconds for me): 
SELECT ?causeOfDeath ?causeOfDeathLabel ?count
WHERE 
{
  {
    SELECT ?causeOfDeath (COUNT(?item) as ?count)
    WHERE
    {
      ?item wdt:P31 wd:Q5;
            wdt:P509 ?causeOfDeath
    }
    GROUP BY ?causeOfDeath
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
Another thing is that each case of "somevalue" is represented by a different Skolem IRI. To group them also, use something like: (or maybe remove them with a filter if you don't care about the number of "somevalue") 
SELECT ?causeOfDeath ?causeOfDeathLabel ?count
WHERE 
{
  {
    SELECT ?causeOfDeath (COUNT(?item) as ?count)
    WHERE
    {
      ?item wdt:P31 wd:Q5;
            wdt:P509 ?causeOfDeath_.
      BIND (IF(wikibase:isSomeValue(?causeOfDeath_), "Some value", ?causeOfDeath_) AS ?causeOfDeath)
    }
    GROUP BY ?causeOfDeath
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
--Dipsacus fullonum (talk) 13:08, 22 September 2022 (UTC)[reply]

Map with labels

Hi, I'm using the following query 

#defaultView:Map
SELECT ?item (SAMPLE(?coord) as ?coord)
WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item wdt:P31 wd:Q12323.
  ?item wdt:P131/wdt:P131* wd:Q130300.
  ?item wdt:P625 ?coord.
}
GROUP BY ?item
Try it!

I would like to know how to produce English labels on each point. I am no sure how to use the SAMPLE syntax (I just adapted this from another query I saw somewhere.) Also is the GROUP BY needed here? Thanks — Martin (MSGJ · talk) 13:34, 22 September 2022 (UTC)[reply]

@MSGJ: Probably the main point here is that you need to specify to the label service that it needs to deliver labels in time for them to be aggregated - see ?item rdfs:label ?itemLabel_. Sampling for the label works as sampling for the coordinate did. The GROUP BY is required b/c the query aggregates some stuff, does not aggregate ?item. In general, GROUP BY everything that is not aggregated in the SELECT. (Addendum: In the select, (Sample(X) as X) syntax is wrong, it needs to be (SAMPLE(X) as Y). I've changed that in the coords sample.)
#defaultView:Map
SELECT ?item (SAMPLE(?itemLabel_) as ?itemLabel) (SAMPLE(?coordinate) as ?coord)
WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
                         ?item rdfs:label ?itemLabel_}
  ?item wdt:P31 wd:Q12323.
  ?item wdt:P131/wdt:P131* wd:Q130300.
  ?item wdt:P625 ?coordinate.
}
GROUP BY ?item
Try it!
--Tagishsimon (talk) 13:57, 22 September 2022 (UTC)[reply]
The label service produces exactly one label for each item, so sampling is not needed for the labels. So it is simpler to add ?itemLabel to the GROUP BY clause, and keep the automatic mode of the label service without explicit naming the variables for labels. 
#defaultView:Map
SELECT ?item ?itemLabel (SAMPLE(?coordinate) as ?coord)
WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item wdt:P31 wd:Q12323.
  ?item wdt:P131/wdt:P131* wd:Q130300.
  ?item wdt:P625 ?coordinate.
}
GROUP BY ?item ?itemLabel
Try it!
--Dipsacus fullonum (talk) 14:09, 22 September 2022 (UTC)[reply]
Not actually sure what I was thinking. On autopilot. --Tagishsimon (talk) 14:11, 22 September 2022 (UTC)[reply]
Thanks both for the quick reply! — Martin (MSGJ · talk) 15:24, 22 September 2022 (UTC)[reply]

Follow-up question: it wasn't quite bringing up all the results I expected because some are subclasses of Q12323 instead of direct instances. So I added /wdt:P279* to the query as below: 

#defaultView:Map
SELECT ?item ?itemLabel (SAMPLE(?coordinate) as ?coord)
WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item wdt:P31/wdt:P279* wd:Q12323.
  ?item wdt:P131/wdt:P131* wd:Q127513.
  ?item wdt:P625 ?coordinate.
}
GROUP BY ?item ?itemLabel
Try it!

But this query reaches timeout without producing any results. What did I do wrong? — Martin (MSGJ · talk) 15:37, 22 September 2022 (UTC)[reply]

@MSGJ: Nothing wrong, in particular. The query optimizer sometimes chooses a poor solution. In this instance, a couple of approaches; first, a hint:
#defaultView:Map
SELECT ?item ?itemLabel (SAMPLE(?coordinate) as ?coord)
WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item wdt:P31/wdt:P279* wd:Q12323.
  ?item wdt:P131/wdt:P131* wd:Q127513. hint:Prior hint:gearing "forward".
  ?item wdt:P625 ?coordinate.
}
GROUP BY ?item ?itemLabel
Try it!
and second, splitting the query by use of a named subquery.
#defaultView:Map
SELECT ?item ?itemLabel (SAMPLE(?coordinate) as ?coord) WITH {
  SELECT ?item WHERE {
    ?item wdt:P131/wdt:P131* wd:Q127513.
  } } as %i
WHERE
{
  INCLUDE %i
  ?item wdt:P625 ?coordinate.
  ?item wdt:P31/wdt:P279* wd:Q12323. hint:Prior hint:gearing "forward".
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?item ?itemLabel
Try it!
These things are discussed some more at Wikidata:SPARQL query service/query optimization --Tagishsimon (talk) 19:33, 22 September 2022 (UTC)[reply]

Song titles that are girl names (or city names! or something else!)

Hi there! This is not something I need, but I thought it might be fun as I've never seen it done before.

Would it be possible to query for all P31 = musical work/composition where the title matches the native label of any item with P31 = female given name (and return the Qitem of both) ? Moebeus (talk) 20:39, 22 September 2022 (UTC)[reply]

@Moebeus: I think this query gives too few results because the languages must match for two monolingual strings to compare equal, but some titles and names use the language code "mul" (for multiple languages) while others use one or more real languages.
SELECT ?song ?female_name ?title (LANG(?title) AS ?language)
{
  ?song wdt:P31 / wdt:P279 * wd:Q105543609.
  ?song wdt:P1476 ?title .
  ?female_name wdt:P31 wd:Q11879590 .
  ?female_name wdt:P1705 ?title .
}
Try it!
--Dipsacus fullonum (talk) 22:21, 22 September 2022 (UTC)[reply]
Yeah, it's missing a lot but this is still pretty cool! Thank you so much :D 🙏🙏 Moebeus (talk) 22:30, 22 September 2022 (UTC)[reply]
@Moebeus: For instance Michelle (Q1353749) (Beatles song) has the title "Michelle (English)", while Michelle (Q15621047) (female given name) has native label "Michelle (multiple languages)". This query will catch cases like this, inceasing the number of results from 42 to 539: 
SELECT ?song ?female_name ?title (LANG(?title) AS ?title_language) ?note
WITH
{
  SELECT DISTINCT ?song ?title
  {
    ?song wdt:P31 / wdt:P279 * wd:Q105543609 .
    ?song wdt:P1476 ?title .
  }
} AS %songs
{
  {
    INCLUDE %songs
    ?female_name wdt:P1705 ?title .
    ?female_name wdt:P31 wd:Q11879590 .
  }
  UNION
  {
    INCLUDE %songs
    FILTER (LANG(?title) != "mul")
    BIND (STRLANG(STR(?title), "mul") AS ?title_mul)
    ?female_name wdt:P1705 ?title_mul .
    ?female_name wdt:P31 wd:Q11879590 . hint:Prior hint:runLast true. 
    BIND ("match one to multiple" AS ?note)
  }
}
Try it!
--Dipsacus fullonum (talk) 07:32, 23 September 2022 (UTC)[reply]
Now we're cooking with gas, many thanks once again! Moebeus (talk) 09:49, 23 September 2022 (UTC)[reply]
@Moebeus: With dates and performers, for added interest: https://w.wiki/5jdS -- Jheald (talk) 14:20, 23 September 2022 (UTC)[reply]

Norwegian names on taxons

I would like to use QuickStatements to add taxon common name (P1843) in Bokmål (Q25167), and therefore need a query returning instance of (P31) equals taxon (Q16521), having the identifier NBIC taxon ID (P8707), but not having taxon common name (P1843) in Bokmål (Q25167). The last requirement stops me from being able to build the query myself... 4ing (talk) 18:25, 23 September 2022 (UTC)[reply]

@4ing: presuming the language code is 'no' this seems to produce the data you require. (And if it is not, change the langauge code in the filter.)
SELECT ?item ?itemLabel ?NBIC WITH {
  SELECT ?item WHERE 
{
  ?item wdt:P8707 ?NBIC.
} } as %i
WITH { 
  SELECT ?item ?NBIC WHERE
{
  INCLUDE %i
  ?item wdt:P31 wd:Q16521. 
  FILTER NOT EXISTS {?item wdt:P1843 ?tcn . FILTER(LANG(?tcn)="no") }  
} } as %j
WHERE
{
  INCLUDE %j
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}
Try it!
--Tagishsimon (talk) 18:36, 23 September 2022 (UTC)[reply]
TBH even the above struggles with timeouts, depending on server load. I think there are ~130k P8707 in WD, so lets use the Slice Service to deliver the first 65k taxons. You can then run the report again with both parameters set to 65k, to get the rest of the data.
SELECT ?item ?itemLabel ?NBIC WITH {
  SELECT ?item ?NBIC WHERE 
{
  SERVICE bd:slice {
    ?item wdt:P8707 ?NBIC.
    bd:serviceParam bd:slice.offset 0 . # Start at item number (not to be confused with QID)
    bd:serviceParam bd:slice.limit 65000 . # List this many items
  }
} } as %i
WITH { 
  SELECT ?item ?NBIC WHERE
{
  INCLUDE %i
  ?item wdt:P31 wd:Q16521. 
  FILTER NOT EXISTS {?item wdt:P1843 ?tcn . FILTER(LANG(?tcn)="no") }  
} } as %j
WHERE
{
  INCLUDE %j
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}
Try it!
--Tagishsimon (talk) 18:41, 23 September 2022 (UTC)[reply]
@Tagishsimon: Thanks a lot! Actually had to split it in 5 to avoid timeouts. - 4ing (talk) 20:32, 23 September 2022 (UTC)[reply]
@4ing: I noticed the entire name database is available as tabular data, with a little bit of programming it should be possible to import all of those common names just by correlating the NBIC IDs. Infrastruktur (talk) 04:48, 24 September 2022 (UTC)[reply]
@Infrastruktur: Yes, that's my plan. - 4ing (talk) 15:01, 24 September 2022 (UTC)[reply]

Query article sitelink title, if present

Adapting an example from Wikidata:SPARQL query service/queries#Working with sitelinks, I can easily query sitelinks to a specific language version (e.g. enwiki): 

SELECT ?article
WHERE 
{
  ?article schema:about wd:Q446728.
  ?article schema:isPartOf <https://en.wikipedia.org/>.
}
Try it!

which returns the sitelink https://en.wikipedia.org/wiki/Old_World_vulture. Is it possible to obtain the article title (i.e. Old World vulture) instead? Further, when there is no sitelink for the requested language version, the query returns no result: 

SELECT ?article
WHERE 
{
  ?article schema:about wd:Q446728.
  ?article schema:isPartOf <https://es.wikipedia.org/>.
}
Try it!

When querying sitelinks as additional info about items, this causes some items to be excluded from the result. Instead, the information that there is no sitelink for that specific item would be more useful. Is it possible to obtain this information via SPARQL? --2A02:8108:50BF:C694:C9C9:AC32:C0A4:28C7 09:24, 24 September 2022 (UTC)[reply]

Okay, for the latter I can use OPTIONAL. Only the first part of my question remains. --2A02:8108:50BF:C694:C9C9:AC32:C0A4:28C7 09:45, 24 September 2022 (UTC)[reply]
(EC) To get the article title: 
SELECT ?article ?title
WHERE 
{
  ?article schema:about wd:Q446728.
  ?article schema:isPartOf <https://en.wikipedia.org/>.
  ?article schema:name ?title.
}
Try it!
You can place some triplets in an "optional" clause to ensure that items with no result for the these triplets are no removed from the result set: 
SELECT ?item ?article ?title
WHERE 
{
  VALUES ?item { wd:Q446728 }
  # Other code using ?item can be placed here ...
  OPTIONAL
  {
    ?article schema:about ?item.
    ?article schema:isPartOf <https://es.wikipedia.org/>.
    ?article schema:name ?title.
  }
}
Try it!
--Dipsacus fullonum (talk) 09:47, 24 September 2022 (UTC)[reply]
Thanks! I was just about to delete my question since I have just found schema:name myself. --2A02:8108:50BF:C694:C9C9:AC32:C0A4:28C7 09:51, 24 September 2022 (UTC)[reply]

Changed coordinates on en-wiki

Something I'm finding with items I've been looking at recently is coordinates here that are sourced just to imported from Wikimedia project (P143) = English Wikipedia (Q328), where incorrrect coordinates have been fixed since import on en-wiki but not changed here.

It would be nice to query for coordinates for items in a particular group (eg UK railway stations), with this source for coordinates, but where the coordinates are not now within (say) 100m of the current coordinates on en-wiki. I think en-wiki coordinates may be available by an API. (Is that how eg WikiShootMe gets them?) But I am not sure whether that would in turn be accessible from SPARQL, or alternatively what wauld be a useful workflow to do such a comparison. Jheald (talk) 20:36, 26 September 2022 (UTC)[reply]

@Jheald: Yes, Wikipedia coordinates are available from the MediaWiki API and they can be fetched by a query. It is rather straightforward, but can timeout when handling many items. This query gives finds Wikidata coordinates and English Wikipedia coordinates for UK railway stations where Wikidata has enwiki as source. It report cases there the coordinates are different (distance > 0) or where enwiki coordinates are not found (usually because the sitelink is to a redirect). It is restricted to items whose item number ends in "0" to avoid timeout, so 10 runs are necessary to get all cases. 
SELECT ?item ?title ?wd_coord ?wiki_coord ?dist
WITH
{
  SELECT DISTINCT ?item
  {
    ?item wdt:P31 / wdt:P279 * wd:Q55488 .
    ?item wdt:P17 wd:Q145 .
    FILTER STRENDS(STR(?item), "0")
  }
} AS %get_items
{
  INCLUDE %get_items
  ?item p:P625 ?s .
  ?s ps:P625 ?wd_coord .
  ?s prov:wasDerivedFrom ?ref .
  ?ref pr:P143 wd:Q328 .
  ?sitelink schema:about ?item .
  ?sitelink schema:isPartOf <https://en.wikipedia.org/> .
  ?sitelink schema:name ?title .
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "en.wikipedia.org" .
    bd:serviceParam wikibase:api "Generator" .
    bd:serviceParam mwapi:generator "allpages" .
    bd:serviceParam mwapi:gapfrom ?title .
    bd:serviceParam mwapi:gapto ?title .
    bd:serviceParam mwapi:prop "coordinates" .
    ?lat wikibase:apiOutput "coordinates/co/@lat" .
    ?lon wikibase:apiOutput "coordinates/co/@lon" .
  }
  BIND (STRDT(CONCAT("Point(", ?lon, " ", ?lat, ")"), geo:wktLiteral) AS ?wiki_coord)
  BIND (geof:distance(?wd_coord, ?wiki_coord) AS ?dist)
  FILTER (! BOUND(?dist) || ?dist > 0.0)
}
Try it!
--Dipsacus fullonum (talk) 00:04, 27 September 2022 (UTC)[reply]
Jheald: This is just to say, I already fixed Marston Magna railway station (Q30621710) - 262 km - which you were probably saving for breakfast. So sweet and so cold.
Dipsacus fullonum, your straightforward is still my magic. I quickly run into the following sort of sand: search for MWAPI on google, get pointed to https://www.mediawiki.org/wiki/Wikidata_Query_Service/User_Manual/MWAPI ... find the generator link on that page at https://www.mediawiki.org/wiki/Wikidata_Query_Service/User_Manual/MWAPI#Supported_services - and traverse to https://www.mediawiki.org/wiki/API:Query#Generators ... and ... nothing much. What documents should I be looking at to understand (presumably) the mwapi:prop options and the corresponding wikibase:apiOutput strings? --Tagishsimon (talk) 00:32, 27 September 2022 (UTC)[reply]
Tagishsimon: the coordinates magic doesn't belong to the MediaWiki core but comes from the MediaWiki extension GeoData (https://www.mediawiki.org/wiki/Extension:GeoData). The extension isn't installed on mediawiki.org, which I suppose is why its two API functions isn't listed on https://www.mediawiki.org/wiki/API:Query (though they are mentioned but not fully documented at https://www.mediawiki.org/wiki/API:Geosearch). You can however find the autogenerated API documentation pages on wikis where GeoData is installed like the Wikipedias, e.g. on enwiki at https://en.wikipedia.org/w/api.php?action=help&modules=query+coordinates and https://en.wikipedia.org/w/api.php?action=help&modules=query+geosearch. --Dipsacus fullonum (talk) 06:03, 27 September 2022 (UTC)[reply]
@Dipsacus fullonum: Thank you so much, that's working like a dream. So many thanks to you for knowing about the GeoData API -- and a neat trick to split the query on the last digit of the item Q-number. Lots to work on! So far I've now cleared '0' and '1' down to 300m (EDIT: and the rest now down to 1 km); but going quite slowly, checking each side to see which is really best. A very useful query I think, thank you so much again! Jheald (talk) 10:48, 27 September 2022 (UTC)[reply]
Retrieved from "https://www.wikidata.org/w/index.php?title=Wikidata:Request_a_query&oldid=1737608437"