Wikidata:Request a query/Archive/2022/08

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

Extract the definition sentence from Wikipedia using a Wikibase query

I would like to be able to use a query to extract the first descriptive sentence from a specified Wikipedia for the item in question. I get the impression that it is possible, but there are too many moving parts for my experience with combining the Wikibase & mwapi queries. This kind of query might be tweaked, but I don't exactly know the nuts & bolts. I managed to create a suitable mwapi query (with extracts), so I know it should be doable.

The following query uses these:

• Items: Georg Mikael Leinberg (Q17382164)   

  SELECT ?item ?itemLabel WHERE {
    ?link schema:about wd:Q17382164;schema:isPartOf <https://fi.wikipedia.org/>;schema:name ?title .
    SERVICE wikibase:mwapi {
  	 bd:serviceParam wikibase:api "Generator" .
       bd:serviceParam wikibase:endpoint "fi.wikipedia.org" .
       bd:serviceParam mwapi:titles ?title .
       bd:serviceParam mwapi:generator "links" .
      ?item wikibase:apiOutputItem mwapi:item .
      ?ns wikibase:apiOutput "@ns" .
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fi". }
    FILTER (?ns = "0"). #showing only articles
  }
  

Try it!

I would be so happy for help and wonder if it could be further worked on to become a tool to add information from Wikipedias. Or if such a tool exists, I would be very interested in learning about it. Thank you so much! – Susanna Ånäs (Susannaanas) (talk) 08:24, 31 July 2022 (UTC)

@Susannaanas: You can use the prop=extracts (ex) module in API calls. See https://www.mediawiki.org/w/api.php?action=help&modules=query%2Bextracts for documentation. It is possible to specify either a number of sentences or number of characters. The sentence count seems to use periods (.), so with bd:serviceParam mwapi:exsentences "1" in the query, you get this extract: "Georg Mikael Leinberg (24." I increased the value to 3 to get "Georg Mikael Leinberg (24. heinäkuuta 1865 Helsingin pitäjä – 20. toukokuuta 1925 Helsinki) oli suomalainen säästöpankkimies."

SELECT ?item ?itemLabel ?extract
WHERE
{
  ?link schema:about wd:Q17382164; schema:isPartOf <https://fi.wikipedia.org/>; schema:name ?title .
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:api "Generator" .
    bd:serviceParam wikibase:endpoint "fi.wikipedia.org" .
    bd:serviceParam mwapi:generator "allpages" .
    bd:serviceParam mwapi:gapfrom ?title .
    bd:serviceParam mwapi:gapto ?title .
    bd:serviceParam mwapi:prop "pageprops|extracts" .
    bd:serviceParam mwapi:exsentences "3" .     # number of sententences to extract (1-10)
    # bd:serviceParam mwapi:exchars "100" .       # number of characters to extract (1-1200)
    bd:serviceParam mwapi:explaintext "true" .
    ?item wikibase:apiOutputItem mwapi:item .
    ?extract wikibase:apiOutput "extract/text()".
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fi". }
}

Try it!

--Dipsacus fullonum (talk) 09:09, 31 July 2022 (UTC)

Thank you so much! I was unable to put together the correct parts into a query. This is exactly what I was looking for <3 – Susanna Ånäs (Susannaanas) (talk) 04:39, 1 August 2022 (UTC)

I am so excited, and I started imagining further. Unfortunately again there's something I should know about looping or subqueries. Would anyone wish to make this work? 👇

The following query uses these:

• Items: Linda Brava (Q541180)   

  SELECT ?item ?itemLabel ?extract ?link ?wikipedia
  WHERE
  {
    ?link schema:about wd:Q541180; schema:name ?title .
    BIND(CONCAT(SUBSTR(str(?link), 9, 2), ".wikipedia.org") AS ?wikipedia) .
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:api "Generator" .
      bd:serviceParam wikibase:endpoint ?wikipedia .
      bd:serviceParam mwapi:generator "allpages" .
      bd:serviceParam mwapi:gapfrom ?title .
      bd:serviceParam mwapi:gapto ?title .
      bd:serviceParam mwapi:prop "pageprops|extracts" .
      bd:serviceParam mwapi:exsentences "3" .     # number of sententences to extract (1-10)
      # bd:serviceParam mwapi:exchars "100" .       # number of characters to extract (1-1200)
      bd:serviceParam mwapi:explaintext "true" .
      ?item wikibase:apiOutputItem mwapi:item .
      ?extract wikibase:apiOutput "extract/text()".
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fi". }
  }
  

Try it!

– Susanna Ånäs (Susannaanas) (talk) 06:10, 1 August 2022 (UTC)

@Susannaanas: Your query didn't work because ?wikipedia wasn't bound before the MWAPI call. I solved that by using an OPTIONAL clause technically forcing a leftjoin instead of a join. I also changed how ?wikipedia is created so it works for Wikipedias with language codes longer than 2 characters:

SELECT ?item ?itemLabel ?extract ?link ?wikipedia
WHERE
{
  ?link schema:about wd:Q541180 ; schema:name ?title ; schema:isPartOf ?wikimedia_site .
  ?wikimedia_site wikibase:wikiGroup "wikipedia" . # Use only Wikipedias
  BIND (STRBEFORE(STRAFTER(STR(?wikimedia_site), "https://"), "/") AS ?wikipedia)
  OPTIONAL
  # The OPTIONAL keyword is used here to force the SPARQL engine to order the query so ?wikipedia (the endpoint) is bound before calling MWAPI
  {
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:api "Generator" .
      bd:serviceParam wikibase:endpoint ?wikipedia .
      bd:serviceParam mwapi:generator "allpages" .
      bd:serviceParam mwapi:gapfrom ?title .
      bd:serviceParam mwapi:gapto ?title .
      bd:serviceParam mwapi:prop "pageprops|extracts" .
      bd:serviceParam mwapi:exsentences "3" .     # number of sententences to extract (1-10)
      # bd:serviceParam mwapi:exchars "100" .       # number of characters to extract (1-1200)
      bd:serviceParam mwapi:explaintext "true" .
      ?item wikibase:apiOutputItem mwapi:item .
      ?extract wikibase:apiOutput "extract/text()".
    }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fi". }
}

Try it!

--Dipsacus fullonum (talk) 09:25, 1 August 2022 (UTC)

I am sooo happy, thank you! – Susanna Ånäs (Susannaanas) (talk) 16:29, 1 August 2022 (UTC)

That's a neat trick. I've ran out of "normal" material to read, so I started reading some of the more oft-cited scholarly papers. Infrastruktur (talk) 21:29, 3 August 2022 (UTC)

Querying all departments and famous people from a given country

Hello, I'm working on a project that needs me to gather all the data I can about a specific nation, including political parties, political figures, organisations, and influential people. I want to extract all of this data from wikidata. Is it even possible to do that? SanaSaajid (talk) 14:29, 1 August 2022 (UTC)

Category items

Hello. I want to find all items that have:

a) category combines topics (P971) -> association football players who play in this club (Q56465024)

AND

b) category combines topics (P971) -> an item that have instance of (P31) -> association football club (Q476028) and country (P17) -> Cyprus (Q229)

Thanks. Philocypros (talk) 17:07, 3 August 2022 (UTC)

@Philocypros:

SELECT ?item { ?item wdt:P971 wd:Q56465024 , [wdt:P31 wd:Q476028 ; wdt:P17 wd:Q229] }

Try it!

--Dipsacus fullonum (talk) 17:45, 3 August 2022 (UTC)

Same query with item labels:

SELECT ?item ?itemLabel
{
  ?item wdt:P971 wd:Q56465024 , [wdt:P31 wd:Q476028 ; wdt:P17 wd:Q229]
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],el,en" }
}

Try it!

--Dipsacus fullonum (talk) 17:59, 3 August 2022 (UTC)

Same query with triple patterns only and no Syntactic sugar:

SELECT ?item ?itemLabel
{
  ?item wdt:P971 wd:Q56465024 .
  ?item wdt:P971 _:CFC .
  _:CFC wdt:P31 wd:Q476028 .
  _:CFC wdt:P17 wd:Q229 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],el,en" }
}

Try it!

--Dipsacus fullonum (talk) 18:08, 3 August 2022 (UTC)

Thanks! Philocypros (talk) 19:45, 3 August 2022 (UTC)

Show Photo Attribution

Hi, I'm hoping to grab the attribution text (P8264) with a collection of photos. Getting the photos I want from the categories I need works, but not displaying attribution text. Here's one of the examples done before, (Artist) Images of Exoplanets. How could this (or any image query) also display the attribution for the photos? Thank you.

Gorvis (talk) 20:19, 4 August 2022 (UTC)

As far as I can tell, the correct place to save image metadata would be as structured data on Commons, even if you could in theory use P8264 as a qualifier for P18 on Wikidata. There seems to be only 42 images on Commons at the moment that uses that property, so any contributions would be most welcome. Click on the image URL and look for the text "Structured data" somewhere on the page. When you add the property on Commons it should show up in the query below.

To query Commons, you need to be logged in, and this query you will need to be run from the Wikimedia Commons Query service at https://commons-query.wikimedia.org/

# Federation with correlation - Get byline for Commons images
# Run this from WCQS - https://commons-query.wikimedia.org/
SELECT ?exoplanet ?label_en ?image ?file ?abutt
WHERE {
  hint:Query hint:optimizer "None" .
  SERVICE <https://query.wikidata.org/sparql> {
    ?exoplanet wdt:P31 wd:Q44559 ;
      wdt:P18 ?image .
    OPTIONAL { ?exoplanet rdfs:label ?label_en . FILTER(LANG(?label_en)="en") }
  }
  ?file schema:url ?image .
  OPTIONAL { ?file wdt:P8264 ?abutt }
}

Infrastruktur (talk) 05:38, 5 August 2022 (UTC)

Note that image metadata (Exif data, Exchangeable image file format (Q196465)) is automatically stored for all uploaded imaged and can be retrieved via the API including using MWAPI service from queries. This metadata may (or may not) have attribution information too. You can find an example of retrieving Exif data in the archives of this page at Wikidata:Request a query/Archive/2020/05#Querying item with optional images and some info about the image. --Dipsacus fullonum (talk) 06:33, 5 August 2022 (UTC)

Looks like Commons likes to use P170 for this. I'm assuming it was the creator's name you were interested in. Here's a fixed up query:

# Federation with correlation - Get byline for Commons images
# Run this from WCQS - https://commons-query.wikimedia.org/
SELECT ?exoplanet ?label_en ?image ?file ?creator_ns ?creator_wu ?creator_url
WHERE {
  hint:Query hint:optimizer "None" .
  SERVICE <https://query.wikidata.org/sparql> {
    ?exoplanet wdt:P31 wd:Q44559 ;
      wdt:P18 ?image .
    OPTIONAL { ?exoplanet rdfs:label ?label_en . FILTER(LANG(?label_en)="en") }
  }
  ?file schema:url ?image .
  OPTIONAL {
    ?file p:P170 ?cst .
    OPTIONAL { ?cst pq:P2093 ?creator_ns . }
    OPTIONAL { ?cst pq:P4174 ?creator_wu . }
    OPTIONAL { ?cst pq:P2699 ?creator_url . }
  }
}

Infrastruktur (talk) 08:22, 5 August 2022 (UTC)

Thanks to you both! Gorvis (talk) 13:23, 8 August 2022 (UTC)

Wikidata list of films by a certain director

Hi all, once again!

My attempt here at creating a Wikidata list of films fails miserably for some reason! Here's a copy of the code:

{{Wikidata list |sparql=SELECT DISTINCT ?film ?filmLabel ?authorLabel (GROUP_CONCAT(?genre_label; SEPARATOR = " ") AS ?genres) (MIN(?publicationDate) AS ?firstPublication) WHERE { ?director rdfs:label "Rob Minkoff"@en. ?film wdt:P57 ?director. # P57 = film director OPTIONAL { ?film wdt:P136 ?genre. ?genre rdfs:label ?genre_label. FILTER((LANG(?genre_label)) = "en") } OPTIONAL { ?film wdt:P577 ?publicationDate. } SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } } LIMIT 10 |sort=label |columns=label:Ffilm,P18:Delwedd,P136:Genre,P495:Gwlad,P364:Iaith wreiddiol,P577:dyddiad,item:Wicidata |thumb=100 |links=local }} {{Wikidata list end}}

The article is a film. I'm trying to gather a random selection of <10 films by the director. I have both names and Qids of all directors: of the two, I'd prefer the Qid I think. The table will work as a type of 'See also' heading and will be added to quite a few articles on films, and later articles on the directors themselves.

Thanks! Llywelyn2000 (talk) 09:56, 5 August 2022 (UTC)

@Llywelyn2000: I would recommend using the QID of the director instead of name. If more than one one director happens to have the same name, your query will mix them up. The query doesn't work because it uses aggregation functions but has no GROUP BY clause. It also have ?authorLabel in the SELECT clause with no definition anywhere. You could change it this way:

SELECT ?film ?filmLabel ?directorLabel (GROUP_CONCAT(DISTINCT ?genre_label; SEPARATOR = " ") AS ?genres)
  (MIN(?publicationDate) AS ?firstPublication)
WHERE
{
  ?director rdfs:label "Rob Minkoff"@en.
  ?film wdt:P57 ?director.                                                  # P57 =  film director
  OPTIONAL {
    ?film wdt:P136 ?genre.
    ?genre rdfs:label ?genre_label.
    FILTER((LANG(?genre_label)) = "en")
  }
  OPTIONAL { ?film wdt:P577 ?publicationDate. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?director ?directorLabel ?film ?filmLabel 
LIMIT 10

Try it!

Note that I also moved the DISTINCT keyword to the GROUP_CONCAT function. --Dipsacus fullonum (talk) 10:43, 5 August 2022 (UTC)

Another reason to avoid labels to select the director, is that they may change. For example when marrying, or the editors may at some point decide to include middle name in the label, use either initials or spelled out names etc. --Dipsacus fullonum (talk) 10:56, 5 August 2022 (UTC)

Wonderful! Your code works fine in WQ, but as part of the 'Wikidata List' on a WP article as seen here, I've failed to integrate it successfully. I agree with you that the Qid is preferable to name, and will go with that. Therefore I've changed the apropriate line to

?director rdfs:item "Q1151111"@en. 

I hope this is correct. Bt still doesn't work on WP. Thanks for your time and patience! Llywelyn2000 (talk) 11:33, 5 August 2022 (UTC)

@Llywelyn2000: I've tweaked the query in your cy wiki page. You really wanted ?film wdt:P57 wd:Q1151111., and then, Listeria actually requires the main variable to be named ?item, so ?item wdt:P57 wd:Q1151111.. --Tagishsimon (talk) 11:44, 5 August 2022 (UTC)

Brilliant! Thank you VERY much! Diolch yn fawr! PS I've deleted the 'local' constraint, so that more films are seen. Thanks! Llywelyn2000 (talk) 11:53, 5 August 2022 (UTC)

@Llywelyn2000: Query on cy:Lawrence_of_Arabia_(ffilm) tweaked again to provide a single, year, date in the final column. --Tagishsimon (talk) 12:54, 5 August 2022 (UTC)

What am I doing wrong? - scatterplot

Hello, I am trying to plot average weight and height of sportspeople by sport.

#defaultView:ScatterChart
select * with {
    select distinct * where 
     {
    ?item wdt:P1447 [] .
     } 
  } as %sportspeople with {
  select distinct ?sport ((avg(?height)) as ?avg_height) ((avg(?weight)) as ?avg_weight) where {
  include %sportspeople
  ?item wdt:P641 ?sport .
  ?item p:P2048/psv:P2048 [
    wikibase:quantityAmount ?baseHeight;
    wikibase:quantityUnit [
      p:P2370/psv:P2370 [
        wikibase:quantityAmount ?unitHeight;
        wikibase:quantityUnit wd:Q11573
      ]
    ]
  ].     
  BIND(?baseHeight * ?unitHeight AS ?height).
  
  ?item p:P2067/psv:P2067 [
  wikibase:quantityAmount ?baseWeight;
  wikibase:quantityUnit [
      p:P2370/psv:P2370 [
        wikibase:quantityAmount ?unitWeight;
        wikibase:quantityUnit wd:Q11570
      ]
    ]
  ].     
  BIND(?baseWeight * ?unitWeight AS ?weight).
  } group by ?sport } as %results where {

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

Try it!

Why is it not displaying correct units in height? And what's that weird 900-kilogram discipline? It seems to be working correctly when I switch the result view to "table". Vojtěch Dostál (talk) 19:07, 8 August 2022 (UTC)

@Vojtěch Dostál: Seems maybe to want a string height; try: select ?sport (xsd:string(?avg_height) as ?ah) ?avg_weight --Tagishsimon (talk) 19:33, 8 August 2022 (UTC)

@Tagishsimon Thanks, but that doesn't seem to do the trick... Vojtěch Dostál (talk) 20:35, 8 August 2022 (UTC)

@Vojtěch Dostál: Is this not a scatterplot - https://w.wiki/5Yvb - ? Whatever it is, the data in it looks mostly sane. --Tagishsimon (talk) 21:44, 8 August 2022 (UTC)

@Tagishsimon: The results in the scatterplot looks positively insane with average weights going up to 897 kg, and unsorted heights. If you change the view to table form, then the data looks sane. --Dipsacus fullonum (talk) 22:00, 8 August 2022 (UTC)

Big Sumo, Dipsacus fullonum. Or perhaps Phab T168341. Some prior discussion here. --Tagishsimon (talk) 22:30, 8 August 2022 (UTC)

@Tagishsimon It is as I feared. I have not heard of this bug before. Thanks for the link, I'll report my case there. Vojtěch Dostál (talk) 06:03, 9 August 2022 (UTC)

@Vojtěch Dostál: I cannot help with regard to the scatterplot, but you can simplify the query much by using normalized quantity values (psn:) directly instead of calculating them. This code:

  ?item p:P2048/psv:P2048 [
    wikibase:quantityAmount ?baseHeight;
    wikibase:quantityUnit [
      p:P2370/psv:P2370 [
        wikibase:quantityAmount ?unitHeight;
        wikibase:quantityUnit wd:Q11573
      ]
    ]
  ].     
  BIND(?baseHeight * ?unitHeight AS ?height).
  
  ?item p:P2067/psv:P2067 [
  wikibase:quantityAmount ?baseWeight;
  wikibase:quantityUnit [
      p:P2370/psv:P2370 [
        wikibase:quantityAmount ?unitWeight;
        wikibase:quantityUnit wd:Q11570
      ]
    ]
  ].     
  BIND(?baseWeight * ?unitWeight AS ?weight).

can be replaced with just:

  ?item p:P2048/psn:P2048 / wikibase:quantityAmount ?height.  
  ?item p:P2067/psn:P2067 / wikibase:quantityAmount ?weight.

--Dipsacus fullonum (talk) 20:36, 8 August 2022 (UTC)

Thanks, that's definitely simpler. Vojtěch Dostál (talk) 06:03, 9 August 2022 (UTC)

Globes we might want to have coordinates on

Hi, related to phab:T314611, it would be nice to have a query that returns a list of things we are likely to have coordinates on. However, the ontology here seems messy - can anyone see a simple way to query it? Ideally it should return at least Earth (Q2), Mars (Q111), Moon (Q405), 1 Ceres (Q596), Mercury (Q308), Venus (Q313), Phobos (Q7547), Deimos (Q7548), Ganymede (Q3169), Callisto (Q3134), Io (Q3123), Europa (Q3143), Mimas (Q15034), Enceladus (Q3303), Tethys (Q15047), Dione (Q15040), Rhea (Q15050), Titan (Q2565), Hyperion (Q15037), Iapetus (Q17958), Phoebe (Q17975), Miranda (Q3352), Ariel (Q3343), Umbriel (Q3338), Titania (Q3322), Oberon (Q3332), Triton (Q3359), Pluto (Q339) as the manually-identified cases. Thanks. Mike Peel (talk) 18:58, 9 August 2022 (UTC)

@Mike Peel: This query gives all globes which currently have globecoordinates. There are 41.

SELECT ?globe ?globeLabel ?count
WITH
{
  SELECT ?globe (COUNT(?globecoordinate) AS ?count)
  { ?globecoordinate wikibase:geoGlobe ?globe }
  GROUP BY ?globe
} AS %globes
WHERE
{
  INCLUDE %globes
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
ORDER BY DESC(?count)

Try it!

--Dipsacus fullonum (talk) 19:13, 9 August 2022 (UTC)

@Dipsacus fullonum: Perfect, thank you! Mike Peel (talk) 19:18, 9 August 2022 (UTC)

Just to note, [1] is also useful for this, items used by located on astronomical body (P376). Thanks. Mike Peel (talk) 19:37, 9 August 2022 (UTC)

Human settlement and HASC

I want every human settlement in a country and the HASC (P8119). Like

• Pesterwitz (Q6366) --> DE.SN.SS (part of town Freital)
• Olewig (Q885323) --> DE.RP.TI (part of town Trier)
• Trier (Q3138) --> DE.RP.TI (the town Trier)
• Dorfchemnitz near Sayda (Q70946) --> DE.SN.MT

I try different ways, but I get every time a timeout. And I never get all in one query. For example this was the test for Germany to get all settlements over the German municipality key (P439)

#defaultView:Table;Map
SELECT  ?item ?itemLabel ?hasc
WHERE
{
  ?item wdt:P439 ?ags.  # hat amtlichen Gemeindekennschlüssel
  ?item wdt:P131 ?subregion.
  ?subregion wdt:P8119 ?hasc .         # hasc
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de". }
}

Try it!

Thanks for every help. Maybe I don't see the clever way to ask wikidata for this info. ---sk (talk) 07:49, 6 August 2022 (UTC)

Optimizer should help

#defaultView:Table;Map
SELECT  ?item ?itemLabel ?hasc {
  hint:Query hint:optimizer "None".
  ?item wdt:P439 ?ags ;  # hat amtlichen Gemeindekennschlüssel
        wdt:P131 ?subregion .
  ?subregion wdt:P8119 ?hasc .         # hasc
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de". }
}

Try it!

Piastu (talk) 08:56, 6 August 2022 (UTC)

@Stefan Kühn, Piastu: The reason for the timeout is that there are fewer statements with HASC (P8119) (7K) than with German municipality key (P439) (11K), so the optimizer assumes that it is best to start with P8119, which is a mistake because almost all values for P439 will be in final result. However, it is more effective to give hint to the SPARQL engine to start with P439, than to turn the optimizer off entirely:

#defaultView:Table;Map
SELECT  ?item ?itemLabel ?hasc
WHERE
{
  ?item wdt:P439 ?ags.  # hat amtlichen Gemeindekennschlüssel
  hint:Prior hint:runFirst true .
  ?item wdt:P131 ?subregion.
  ?subregion wdt:P8119 ?hasc .         # hasc
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de". }
}

Try it!

--Dipsacus fullonum (talk) 09:21, 6 August 2022 (UTC)

@Dipsacus fullonum, Piastu: First many thanks! - With German municipality key (P439) I get only big settlements in Germany. But I want also the small one. They have no P439. Mostly they are part of a bigger village. Like Pesterwitz (Q6366) it is part of Freital (Q5870). I know the result is a huge list, but this would helpful. --sk (talk) 07:11, 7 August 2022 (UTC)

@Stefan Kühn: I could not make a make query to get all German settlements in one query. But I made this query to select 50 of 419 German hasc values, and give all settlements with these values. You can run the query 9 times, increasing the OFFSET value in the subquery to get hasc values by 50 each time to get all.

SELECT DISTINCT ?item ?region ?hasc
WITH
{
  # Subquery to get all values of hasc in Germany  
  SELECT ?region ?hasc
  WHERE
  {
    ?region wdt:P8119 ?hasc .
    ?region wdt:P17 wd:Q183 . # country is Germany
  }
  ORDER BY ?hasc
  OFFSET 0
  LIMIT 50
} AS %hasc
WHERE
{
  INCLUDE %hasc
  ?item wdt:P131 * ?region .
  ?item wdt:P31 / wdt:P279 * wd:Q486972 . # ?item is a subclass of human settlement
  hint:Prior hint:gearing "forward" .
}

Try it!

--Dipsacus fullonum (talk) 15:10, 7 August 2022 (UTC)

@Dipsacus fullonum Wow, I think this will be helpful. I think a long time about the human settlements in Germany. But I don't found one single attribute, so that we can make one simple query. In your solution are many false data. Like Q111582444. This is a shop. --sk (talk) 19:08, 7 August 2022 (UTC)

@Stefan Kühn: Blame it on errors in Wikidata's ontology. According to the ontology it is a settlement with this property path: Q111582444 > instance of (P31) > plant nursery (Q155511) > subclass of (P279) > plantation (Q188913) > subclass of (P279) > farm (Q131596) > subclass of (P279) > rural settlement (Q10354598) > subclass of (P279) > human settlement (Q486972). You could change the query to look only for selected settlement types like Ortsteil (Q253019) or others. --Dipsacus fullonum (talk) 19:53, 7 August 2022 (UTC)

@Dipsacus fullonum: I create a new version of your query. Now I filter only for Germany-HASC and also filter "instance_of" by a list of useful attributes. The result are over 75000 Items. I only need the label and the HASC. --sk (talk) 09:04, 12 August 2022 (UTC)

SELECT DISTINCT ?itemLabel ?hasc
WITH
{
  # Subquery to get all values of hasc in Germany  
  SELECT ?region ?hasc
  WHERE
  {
    ?region wdt:P8119 ?hasc .
    FILTER(REGEX(STR(?hasc), "^DE.[A-Z]{2}.[A-Z]{2}$","i")) 
    ?region wdt:P17 wd:Q183 . # country is Germany
  }
  #ORDER BY ?hasc
  #OFFSET 0
  #LIMIT 50
} AS %hasc
WHERE
{
  INCLUDE %hasc
  ?item wdt:P131 * ?region .
  #?item wdt:P31 / wdt:P279 * wd:Q486972 . # ?item is a subclass of human settlement
  VALUES ?instance_of { 
    wd:Q253019      # Ortsteil
    wd:Q486972      # Siedlung
    wd:Q262166      # Gemeinde in Deutschland
    wd:Q123705      # Stadtviertel
  } 
  ?item wdt:P31 ?instance_of .  
  #hint:Prior hint:gearing "forward" . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de". }
}

Try it!

can you speed up this query?

Up to now I could handle timeouts by rewriting the query. I don't understand why this times out, it's not recursive, and there are less than 50k items involved. Can you please help?

The following query uses these:

• Properties: Gene Ontology ID (P686)   , subclass of (P279)   

  SELECT DISTINCT ?item ?goid ?stmt1 ?rank ?sgoid WHERE {
    ?item wdt:P686 ?goid.
    ?item wdt:P279 ?P279val.
    ?item p:P279 ?stmt1.
    ?stmt1 ps:P279 ?P279val.
    ?P279val p:P686 ?gostmt.
    ?gostmt ps:P686 ?sgoid.
    ?gostmt wikibase:rank ?rank.
  }
  

Try it!

SCIdude (talk) 16:33, 9 August 2022 (UTC)

@SCIdude: As far as I can see, you are looking for items, named ?item, where

1. ?item has a best rank statement with Gene Ontology ID (P686) that have the value ?goid
2. ?item has a best rank statement with subclass of (P279) that have the value ?P279val
3. ?item has a statement named ?stm1 of any rank with subclass of (P279) that have the value ?P279val
4. There is a statement [?P279val P686 ?sgoid] with the rank ?rank

When condition 2 is fulfilled, condition 3 will automatically also be fulfilled, so we can ignore condition 3 for now, and make this first query to get possible values of ?item, ?goid, ?rank and ?sgoid:

SELECT ?item ?goid ?rank ?sgoid
WHERE {
  ?item wdt:P686 ?goid .
  ?item wdt:P279 ?P279val .
  ?P279val p:P686 ?gostmt .
  ?gostmt ps:P686 ?sgoid .
  ?gostmt wikibase:rank ?rank.
}

Try it!

Now using the first query as a subquery, we can add finding values for ?stm1 (using a RunLast hint):

SELECT DISTINCT ?item ?goid ?stmt1 ?rank ?sgoid
WITH
{
  SELECT ?item ?goid ?rank ?sgoid ?P279val
  WHERE
  {
    ?item wdt:P686 ?goid .
    ?item wdt:P279 ?P279val .
    ?P279val p:P686 ?gostmt .
    ?gostmt ps:P686 ?sgoid .
    ?gostmt wikibase:rank ?rank .
  }
} AS %items
WHERE
{
  INCLUDE %items
  ?item p:P279 ?stmt1 . 
  ?stmt1 ps:P279 ?P279val . hint:Prior hint:runLast true .
}

Try it!

I got 76274 results in 13659 ms. The subquery had 76272 results, so only 2 values of ?stmt1 is for statements that are not best rank. (Addendum: Cannot conclude that) --Dipsacus fullonum (talk) 17:35, 9 August 2022 (UTC)

@SCIdude: (EC) This, perhaps? ~15s. I don't think it changes the query's meaning.

SELECT DISTINCT ?item ?goid ?stmt1 ?rank ?sgoid WHERE {
  ?item wdt:P686 ?goid. hint:Prior hint:runFirst true.
  ?item p:P279 ?stmt1.
  ?stmt1 ps:P279 ?P279val.
  ?stmt1 a wikibase:BestRank .
  ?P279val p:P686 ?gostmt.
  ?gostmt ps:P686 ?sgoid.
  ?gostmt wikibase:rank ?rank.
}

Try it!

--Tagishsimon (talk) 17:36, 9 August 2022 (UTC)

Tagishsimon, your query does in theory change the meaning. It wasn't a condition in the original query that ?stmt1 is best rank, just that there also existed a statement with the same values values of best rank. In practice, it seems to give the same results with the current data in the database. --Dipsacus fullonum (talk) 17:56, 9 August 2022 (UTC)

True. --Tagishsimon (talk) 18:19, 9 August 2022 (UTC)

• Thanks. It looks like it is a matter of "hints" to the optimizer to speed up queries, so I will have to play with these in future. --SCIdude (talk) 06:44, 10 August 2022 (UTC)

@SCIdude: There are many more tools than "hints", see Wikidata:SPARQL query service/query optimization. Your query was rather special by requiring a statement of a certain rank, but including statements of other ranks with the same values. --Dipsacus fullonum (talk) 08:14, 10 August 2022 (UTC)

Labels beginning with case-sensitive words

Is there a straightforward way to query labels and aliases that begin with a given phrase, matching capitalisation? See User_talk:Mike_Peel#Some_proposals_to_uncapitalization_of_labels_and_aliases for the request, I can code up the bot work, but I'm not sure about the query side of things. Thanks. Mike Peel (talk) 19:15, 9 August 2022 (UTC)

There is the "secondary storage for Item and Property terms in SQL" as documented in [2]. I would query those tables and stay away from WDQS since this is not efficient at all in SPARQL. —MisterSynergy (talk) 19:36, 9 August 2022 (UTC)

@Mike Peel: This query suggests string comparisons are case sensitive, so I think I'd be heading in the direction of an MWAPI search for the phrase, and then winnowing the results either through a filter, or using a BIND. There's other stuff to consider on which I do not have info; whether you're concerned about certain languages only, and whether the phrases are a substring or a complete string.

?item rdfs:label ?itemLabel. gves you the item label. filter(lang(?itemLabel)="en") reduced this to EN labels. ?item schema:description gets the description & you can use the same filter technique for language. ItemLabels and Desciptions appear to need stringifying before they'll play nicely, presumably b/c they're monolingual strings. FILTER (CONTAINS(STR(?itemLabel),"grade")) allows inspection of a substring.

SELECT DISTINCT ?item ?itemLabel ?cap
WHERE {
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:srsearch "Grade".
    ?item wikibase:apiOutputItem mwapi:title .
  }
  ?item rdfs:label ?itemLabel.
  BIND(IF(str(?itemLabel) ="grade","lower",IF(str(?itemLabel) ="Grade","upper","")) as ?cap)
  FILTER (lcase(str(?itemLabel)) ="grade")
}

Try it!

--Tagishsimon (talk) 19:46, 9 August 2022 (UTC)

@Mike Peel: I suggest same approach as Tagishsimon, but the API search using the WikibaseCirrusSearch extension (documentation at mw:Help:Extension:WikibaseCirrusSearch) can be language specific. Here is the search for

• cs labels beginning with "Evropská silnice "

SELECT ?item ?label
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:"Evropská silnice@cs"' .
    bd:serviceParam mwapi:gsrlimit "max".
    ?item wikibase:apiOutputItem mwapi:title.
  }
  ?item rdfs:label ?label
  FILTER (LANG(?label) = "cs")
  FILTER STRSTARTS(?label, "Evropská silnice ")
}

Try it!

Note that the "inlabel" search is case insensitive and includes searching in aliases. The language code (@cs) goes inside both sets of apostrophes. The STRSTARTS function (like other text functions) in SPARQL is case sensitive. --Dipsacus fullonum (talk) 20:31, 9 August 2022 (UTC)

@Tagishsimon, Dipsacus fullonum: Thanks! I'm trying out Dipsacus fullonum's approach first, since that seems a little simpler. Is there an equivalent for searching aliases - perhaps an "inalias" search? Thanks. Mike Peel (talk) 16:22, 12 August 2022 (UTC)

@Mike Peel: "inlabel" searches both labels and aliases. There is no WikibaseCirrusSearch keyword to search only one of them. --Dipsacus fullonum (talk) 17:52, 12 August 2022 (UTC)

Help to discard duplicated coordinated

Hi:

I have this query:

SELECT DISTINCT ?place ?placeLabel ?dist ?placeDescription (SAMPLE(?coords) as ?coord) (SAMPLE(?image) AS ?image)
          WHERE {
              SERVICE wikibase:around {
                ?place wdt:P625 ?coords.
                                bd:serviceParam wikibase:center 'Point(-3.2485 37.5873)'^^geo:wktLiteral.
                bd:serviceParam wikibase:radius '70'. }
              { ?place (wdt:P31/(wdt:P279*)) wd:Q386426. }
              UNION
              { ?place (wdt:P31/(wdt:P279*)) wd:Q20671979. }
              UNION
              {
                VALUES ?prop { wdt:P677 wdt:P757 wdt:P809 wdt:P1732 wdt:P1848 wdt:P2467
	            wdt:P2516 wdt:P2520 wdt:P2525 wdt:P2584 wdt:P2621 wdt:P2763
	              wdt:P3009 wdt:P3296 wdt:P3327 wdt:P3401 wdt:P3412 wdt:P3425
	              wdt:P3498 wdt:P3515 wdt:P3516 wdt:P3609 wdt:P3613 wdt:P3810
	              wdt:P3974 wdt:P4001 wdt:P4029 wdt:P4083 wdt:P4154 wdt:P4171
	              wdt:P4170 wdt:P4172 wdt:P4182 wdt:P4190 wdt:P4762 wdt:P4800
	              wdt:P4977 wdt:P5965 wdt:P6070 wdt:P6230 wdt:P6280 wdt:P6478
	              wdt:P6560 wdt:P6602 wdt:P6659 wdt:P6700 wdt:P7129 wdt:P7308
	              wdt:P8114 wdt:P9516 wdt:P9552 wdt:P9755 wdt:P10015 wdt:P10392 wdt:P10513 wdt:P10514 }
                ?place ?prop ?natural.
              }
            SERVICE wikibase:label {
              bd:serviceParam wikibase:language 'es,en'.}
            SERVICE wikibase:label {
              bd:serviceParam wikibase:language "es,en".
             ?place schema:description ?placeDescription.
            }
            OPTIONAL { ?place wdt:P18 ?image. }
            OPTIONAL { ?place wdt:P17 ?country. }
  BIND(geof:distance("Point(-3.2485 37.5873 )"^^geo:wktLiteral, ?coords) AS ?dist)            FILTER NOT EXISTS {?place wdt:P576 ?bar.}
            FILTER NOT EXISTS {?place wdt:P582 ?bar.}
            }
            GROUP BY ?place ?placeLabel ?dist ?placeDescription
            ORDER BY ?dist

Try it!

I understood using SAMPLE I can discard multiple values for a property. In this case is P625. But the query doesn't do and it still lists Q6063113 twice.

What I'm doing wrong? —Ismael Olea (talk) 12:27, 11 August 2022 (UTC)

@Olea: Mainly, overlooking that there are two values for ?dist as well as for the ?coords (which is to say, the SAMPLE of the ?coords is done after two ?dists have been calculated in the body of the query based on the item's two coords). But to solve this, you need to calculate the ?dist from the coord which was SAMPLEd, which tends to point in the direction of a named subquery, as below. Interestingly, and perhaps unexpectedly, the label service calls made in your query led to a row with no ?placeDescription being omitted from the results; it is back in the query below. Hope it's wanted. (Also, please knock one of the duplicate coords on that item on the head when convenient. Neither has a reference.)

SELECT DISTINCT ?place ?placeLabel ((ROUND (100 * ?dist) / 100) AS ?distance)  ?placeDescription ?coord ?image
WITH {SELECT DISTINCT ?place (SAMPLE(?coords) as ?coord) (SAMPLE(?image_) AS ?image)

          WHERE {
              SERVICE wikibase:around {
                ?place wdt:P625 ?coords.
                                bd:serviceParam wikibase:center 'Point(-3.2485 37.5873)'^^geo:wktLiteral.
                bd:serviceParam wikibase:radius '70'. }
              { ?place (wdt:P31/(wdt:P279*)) wd:Q386426. }
              UNION
              { ?place (wdt:P31/(wdt:P279*)) wd:Q20671979. }
              UNION
              {
                VALUES ?prop { wdt:P677 wdt:P757 wdt:P809 wdt:P1732 wdt:P1848 wdt:P2467
	            wdt:P2516 wdt:P2520 wdt:P2525 wdt:P2584 wdt:P2621 wdt:P2763
	              wdt:P3009 wdt:P3296 wdt:P3327 wdt:P3401 wdt:P3412 wdt:P3425
	              wdt:P3498 wdt:P3515 wdt:P3516 wdt:P3609 wdt:P3613 wdt:P3810
	              wdt:P3974 wdt:P4001 wdt:P4029 wdt:P4083 wdt:P4154 wdt:P4171
	              wdt:P4170 wdt:P4172 wdt:P4182 wdt:P4190 wdt:P4762 wdt:P4800
	              wdt:P4977 wdt:P5965 wdt:P6070 wdt:P6230 wdt:P6280 wdt:P6478
	              wdt:P6560 wdt:P6602 wdt:P6659 wdt:P6700 wdt:P7129 wdt:P7308
	              wdt:P8114 wdt:P9516 wdt:P9552 wdt:P9755 wdt:P10015 wdt:P10392 wdt:P10513 wdt:P10514 }
                ?place ?prop ?natural.
              }
            OPTIONAL { ?place wdt:P18 ?image_. }
            OPTIONAL { ?place wdt:P17 ?country. }

            FILTER NOT EXISTS {?place wdt:P582 ?bar.}
            FILTER NOT EXISTS {?place wdt:P576 ?bar.}
            }
            GROUP BY ?place 
} as %i
WHERE
{
  INCLUDE %i
  BIND(geof:distance("Point(-3.2485 37.5873 )"^^geo:wktLiteral, ?coord) AS ?dist)
  SERVICE wikibase:label {  bd:serviceParam wikibase:language 'es,en'.}
  } ORDER BY ?dist

Try it!

--Tagishsimon (talk) 12:52, 11 August 2022 (UTC)

I was in the middle of the lunch when realized about the duplicated dist. Thanks a lot :) —Ismael Olea (talk) 14:14, 11 August 2022 (UTC)

@Olea: When you search for items near a point, you can get the distance to the point directly. There is no need to calculate the distance again later. Just use:

              SERVICE wikibase:around {
                ?place wdt:P625 ?coords.
                bd:serviceParam wikibase:center 'Point(-3.2485 37.5873)'^^geo:wktLiteral.
                bd:serviceParam wikibase:radius '70'. 
                bd:serviceParam wikibase:distance ?dist.
              }

And then use SAMPLE(?dist) (or MIN(?dist) or MAX(?dist) or whatever) in the SELECT clause). That way you don't need the geof:distance function at all. --Dipsacus fullonum (talk) 14:55, 11 August 2022 (UTC)

@Olea: PS. Technically it is incorrect to reuse variable names after "AS" in SPARQL. A server error occurs if you use e.g. (MIN (?dist) AS ?dist), but for some reason (SAMPLE (?dist) AS ?dist) is accepted. However, this is still incorrect SPARQL code. You can see how Tagishsimon has avoided this kind of erroneous code by using e.g. (SAMPLE(?image_) AS ?image). --Dipsacus fullonum (talk) 15:07, 11 August 2022 (UTC)

"The servering class should just do as they are told" Misantributed to Marie Antoinette. :3 Infrastruktur (talk) 19:58, 11 August 2022 (UTC)

Df, Is there not the possibility that if the query SAMPLES both ?coord and ?dist in the same select, the ?dist returned will be for the ?coord that was discarded? If so, handling ?coord and ?dist separately seems more likely to achieve a coherent result? --Tagishsimon (talk) 20:07, 11 August 2022 (UTC)

Yes, that is a possibility. I didn't think inconsistency between ?coord and ?dist would be a problem when a random sample is good enough, but if it is, the method I proposed can't be used. --Dipsacus fullonum (talk) 17:49, 12 August 2022 (UTC)

A very simple query : French cities having a page in fr.wikivoyage.org

Basically this starts with

 SELECT DISTINCT ?item ?itemLabel WHERE {
    SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
    {
        SELECT DISTINCT ?item WHERE {
            ?item p:P131 ?statement0.
        }
        LIMIT 100
    }
}

But then I need to select items in France and having a page in french Wikivoyage (fr.Wikivoyage.org) and this become a bit touchy for me whenever I am used to write big SPARQL requests. 80.215.178.168 11:59, 13 August 2022 (UTC)

Questionable whether it is a simple query, WDs ontology and wild-west approach to statement rank being what they are. I'd be going in this direction:

SELECT DISTINCT ?item ?itemLabel ?article ?sitelink
WHERE 
{
  ?item p:P31/ps:P31/wdt:P279* wd:Q515. # city or subclass of city, irrespective of statement rank
  ?item wdt:P17 wd:Q142. # in France
  ?article schema:about ?item ;  # these three lines are the sitelink incantation
    schema:isPartOf <https://fr.wikivoyage.org/> ; 
    schema:name ?sitelink .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr,en". } 
} ORDER BY ?itemLabel

Try it!

--Tagishsimon (talk) 12:42, 13 August 2022 (UTC)

"?item p:P31", this was a good start. But a tiny one. Working hard I could write the 2nd line about "wdt:P17 wd:Q142" but the "the sitelink incantation" was completly out of my scope.

Thanks, as we say in those cases you save me ! 80.215.178.168 12:55, 13 August 2022 (UTC)

This is, hands down, the best WDQS tutorial: https://wdqs-tutorial.toolforge.org/ ... and this - the whole page, but especially the diagram - the most important documentation. Good luck with your SPARQL & your cities. --Tagishsimon (talk) 13:51, 13 August 2022 (UTC)

Palladian architecture in Ireland

Noting en:Palladian architecture was the featured article on the English Wikipedia for 13 August 2022, and having a marginal interest in the architecture of the Railway Stations in Ireland I decided I would try to try some simple query to get some information from Wikidata. I am thinking Dublin Connolly railway station, Harcourt Street railway station, and The Broadstone all might be, and its a big might, all have elements of Palladian architecture. If I can find references for same then I may add it to Wikidata, I also know for certain Connolly had some but now of its buildings in the polychromatic brick style of Mills. Anyway I am also trying to practice something of the excellent talks from Wikimania @ Bethanl Green and have a simple query to try to locate buildings of Palladian Style in Ireland. It seems to work through not many results are returned, namely four. I am wondering if some form of improved SPARQL query might give wider results. Thankyou. Deirge Ó Dhaoinebeaga (talk) 00:29, 14 August 2022 (UTC)

SELECT ?item ?itemLabel WHERE {
    ?item wdt:P149 wd:Q264649. # architectual style, Palladian architecture
    ?item wdt:P17 wd:Q27.      # country, Republic of Ireland
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Thankyou,

Deirge Ó Dhaoinebeaga (talk) 00:29, 14 August 2022 (UTC) (Djm-leighpark))

@Deirge Ó Dhaoinebeaga: I'm afraid those four records are your lot; in short, given the data, there is nothing that could be improved in your SPARQL. Only four items with country-Ireland have a pointer to Palladian architecture.

Was there more to be found, there are three suggestions for adaptations of the query (and, there are not, so this is all hypothetical): 1) look for items linked to Palladian architecture using properties other than P149, so, ?item ?predicate wd:Q264649., for instance. You don't have to specify the predicate in the query, you can as easily use a variable, which asks the report engine to return any subject predicate object with an object value of Q264649 2) look for items having a P149 pointing to Palladian architecture where the statement is not truthy (e.g. has been deprectaed) by using ?item p:P149/ps:P149 wd:Q264649. and 3) check for additional values in the class tree of Q264649 (there are none so this continues to be a hypothetical) using ?item wdt:P149/wdt:P279* wd:Q264649. hth --Tagishsimon (talk) 02:24, 14 August 2022 (UTC)

Thankyou. I will keep my eye out to improve the data! Though the other tips are food for thought. thankyou. Deirge Ó Dhaoinebeaga (talk) 02:36, 14 August 2022 (UTC)

Unexpected property path non-entailment

I would expect the query

SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P31 wd:Q55983715. hint:Prior hint:runFirst true.
  ?item (wdt:P31|wdt:P279)* wd:Q729. hint:Prior hint:gearing "forward".
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

to give at least all the results of

SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P31 wd:Q55983715. hint:Prior hint:runFirst true.
  ?item wdt:P31?/wdt:P279* wd:Q729. hint:Prior hint:gearing "forward".
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

since any property path that matches wdt:P31?/wdt:P279* should also match (wdt:P31|wdt:P279)*. So, in particular, I would expect the former query to give at least as many results as the latter. However, for the latter query I get 106 results and 105 for the former. What’s going on here? --2A02:8108:50BF:C694:9542:45CE:E09E:3014 09:27, 14 August 2022 (UTC)

The first query has 105 distinct results, while the second has 103 distinct results and 3 duplicates. For example silkworm (Q106513115) has two solutions in the second query, one for larva (Q129270) and another for caterpillar (Q81825), but only one solution to the first query because arbitrary length path matching does not introduce duplicates. See section 9.4 in SPARQL 1.1 specifications. --Dipsacus fullonum (talk) 09:54, 14 August 2022 (UTC)

I see. So if I want the second query I think I need SELECT DISTINCT. Thank you! --2A02:8108:50BF:C694:9542:45CE:E09E:3014 11:53, 14 August 2022 (UTC)

Listing the date in query for persons having both P5871 and P8269

Can Ihave a list of persons who have both Norwegian war refugees register ID (P5871): Norwegian register for refugees at Kjesæter 1940 to 1945 ID and Norwegian prisoner register person ID (P8269): identification number for Norwegians in captivity during World War II 1940 -1945 and also list the qualifier point in time (P585): date something took place, existed or a statement was true; for providing time use the "refine date" property (P4241) Pmt (talk) 21:56, 14 August 2022 (UTC)

@Pmt:

SELECT ?item ?itemLabel ?P5871 ?P5871_P585 ?P8269 ?P8269_P585
WHERE 
{
  ?item p:P5871 ?stat .
  ?stat ps:P5871 ?P5871 .
  OPTIONAL {?stat pq:P585 ?P5871_P585 . }  
        
  ?item p:P8269 ?stat1 .
  ?stat1 ps:P8269 ?P8269 .
  OPTIONAL {?stat1 pq:P585 ?P8269_P585 . }
        
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],nn,no,en". } 
}

Try it!

--Tagishsimon (talk) 22:04, 14 August 2022 (UTC)

Service :) Thanks a lot Pmt (talk) 22:45, 14 August 2022 (UTC)

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?

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)

What is the best way to generate a list of historical events in Japan?

Hello, I wonder what is the best way to list all historical events in Japan. The following query is OK, but it returns a lot of war related events and it seems there are many other important events missing. Many thanks in advance!

select DISTINCT ?event ?eventLabel ?location ?locationLabel ?coord ?d1 ?d2 ?d3 ?date

where {

?event wdt:P31/wdt:P279* wd:Q13418847 .

?event wdt:P276 ?location .

?location wdt:P17 wd:Q17 .

OPTIONAL {?event wdt:P625 ?coord .}

OPTIONAL {?event wdt:P580 ?d1 .}

OPTIONAL {?event wdt:P585 ?d2 .}

OPTIONAL {?event wdt:P582 ?d3 .}

BIND(IF(!BOUND(?d1),(IF(!BOUND(?d2),?d3,?d2)),?d1) as ?date).

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

}

ORDER BY ?date

Note: historical events (wd:Q13418847) includes battle (Q178561) and peace conference (Q7157512). 2001:610:450:40:0:0:0:1035 10:28, 1 August 2022 (UTC)

Please define which historical events you want in the list. On this page we can help with SPARQL queries but you have to describe what they should do. I can't know what you want in the list. --Dipsacus fullonum (talk) 11:27, 1 August 2022 (UTC)

@Dipsacus fullonum Ok, thank you for your prompt reply. I thought it is enough to say I would like to see entities whose type is event in general. My first impression was that historical events (wd:Q13418847) could be enough to see a wide range of events interesting for historical research, but the query above returns a few hundreds events which has date information, which is not enough. In addition, I wonder if this entity focuses too much on war events.

Events could be very broad (sports, concerts, natural disasters, elections, inflation, pandemics, inventions, product releases etc), so I understand it is not easy to list them all in a nice way, but I have a feeling that Wikidata does not define the classification of events very intuitively. Still, my question was what would be a reasonable query which has a nice balance between recall and precision to gain an overview of important historical events for a country (or city), just like the way how Wikidata/Wikipedia lists all important relevant information about a country or city (i.e. wd:Q17). In short, I would say something like the following Wikipage would be very nice: https://en.wikipedia.org/wiki/Timeline_of_Japanese_history 2001:610:450:40:0:0:0:1035 14:02, 1 August 2022 (UTC)

Well, you could simply list all events linked to in the page en:Timeline of Japanese history. Then you know that the events are (hopefully) carefully selected by Wikipedia users:

SELECT ?item ?itemLabel (GROUP_CONCAT(DISTINCT ?instance_ofLabel; separator="; ") AS ?event_types) ?time ?start ?end ?time_precision ?sort_key
WITH
{
  SELECT ?item
  WHERE
  {
    SERVICE wikibase:mwapi
    {
      bd:serviceParam wikibase:api "Generator" .
      bd:serviceParam wikibase:endpoint "en.wikipedia.org" .
      bd:serviceParam mwapi:titles "Timeline of Japanese history" .
      bd:serviceParam mwapi:generator "links" .
      bd:serviceParam mwapi:gpllimit "max" .
      bd:serviceParam mwapi:gplnamespace "0" .
      ?item wikibase:apiOutputItem mwapi:item .
    }
    FILTER BOUND(?item)
  }
} AS %items  
WHERE
{
  INCLUDE %items
  OPTIONAL { ?item wdt:P31 ?instance_of }
  ?item wdt:P31 / wdt:P279* wd:Q1190554 . hint:Prior hint:gearing "forward". # Must be events
  OPTIONAL { ?item p:P585 / psv:P585 [wikibase:timeValue ?time ; wikibase:timePrecision ?time_prec] }
  OPTIONAL { ?item p:P580 / psv:P580 [wikibase:timeValue ?start ; wikibase:timePrecision ?start_prec] }
  OPTIONAL { ?item p:P582 / psv:P582 [wikibase:timeValue ?end ; wikibase:timePrecision ?end_prec] }
  BIND (COALESCE(?time, ?start, ?end) AS ?sort_key)
  FILTER BOUND(?sort_key) # Must have a time
  BIND (COALESCE(?time_prec, ?start_prec, ?end_prec) AS ?prec)
  BIND (IF(?prec = 11, "day", IF(?prec = 10, "month", IF(?prec = 9, "year", "other"))) AS ?time_precision)
  SERVICE wikibase:label
  {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,ja" .
    ?item rdfs:label ?itemLabel .
    ?instance_of rdfs:label ?instance_ofLabel .
  }
}
GROUP BY ?item ?itemLabel ?time ?start ?end ?time_precision ?sort_key
ORDER BY ?sort_key ?end

Try it!

--Dipsacus fullonum (talk) 15:08, 1 August 2022 (UTC)

@Dipsacus fullonum Thank you so much for insightful info. I didn't know that it is possible to interact with Wikipedia pages via API. Just for curiosity. My original question was how to achieve something similar, by using only a relatively simple SPARQL query. I was searching for nice Wikidata properties and classes that I can specify in order to list important historical events for a place. For example, it is possible to get an overview of a city/country history over 3000 years. Even if the query is generic (i.e. "any" events from battles and small festivals to political elections and sport events), the amount of results (say, up to 100.000) might not be a big issue as long as the results can be filtered by date range etc. Do you have any idea for that? 2001:610:450:40:0:0:0:1035 12:19, 8 August 2022 (UTC)

I cannot help with what to query about, only about how to do it. The regulars in this page are mostly experts in SPARQL and technical aspects of the query service, and not so much into the content as such. --Dipsacus fullonum (talk) 14:47, 8 August 2022 (UTC)

@Dipsacus fullonum OK, I understand. I thought my comment is enough to use your imagination to come up with a nice query, but you want to be something specific, right? Then, I would pick one: How to get all (historical) events occurred in Japan between AD 1000 and AD 2000? As said, events are any occurrence of social or personal importance you could think of when reading a history textbook. Id better not to go into philosophy any further. I don't mind if the results have 100000 results, if I can filter them by dates, event types, and provinces/cities. Note that the similar query I used for Japan in the original question did not return the sufficient number and types of results, hence I asked in this wiki:

?event wdt:P31/wdt:P279* wd:Q13418847 .

?event wdt:P276 ?location .

?location wdt:P17 wd:Q17 .

I am hoping you know a better way to compile different kinds of events from the Wikidata ontology (but without using Wikipedia API, because it could produce more noises than ontology, and because the ontology is the whole point of SPARQL, not relying on far less-structured texts like Wikipedia). I look forward to seeing your creative query. Cheers! 2001:610:450:40:0:0:0:1035 17:42, 8 August 2022 (UTC)

@Dipsacus fullonum Just wondering if it is not so easy to list historical events for a country or a city? The Wikimedia API query above is handy, but it looks far more complicated to build than my original query in the question. My only frustration is that the query misses a lot of important events. Doesn't the Wikidata ontology have good mechanism to obtain a reasonable list (which I could filter, if there are too many results)? 2001:610:450:40:0:0:6:1033 13:11, 15 August 2022 (UTC)

Please see my previous answers and please discuss the ontology elsewhere and then return when you can specify exactly what is you want in a reasonable list. I cannot tell what is reasonable. --Dipsacus fullonum (talk) 14:31, 15 August 2022 (UTC)

Well, I try to solve similar problem for city with the following filtering:

SELECT distinct ?event ?eventLabel ?time ?begin ?end ?create ?destroy WHERE {
  VALUES ?city { wd:Q1490 }.
  VALUES ?startTime {"1990-12-31"^^xsd:dateTime}.
  {?event wdt:P131* ?city.} UNION {?event wdt:P276 ?city.}
#  ?event wdt:P31/wdt:P279* wd:Q1190554. (anyway timesout)
  {?event p:P276 ?statement.
    ?statement pqv:P580 ?beginvalue;
               pqv:P582 ?endvalue;
               ps:P276 ?city.
    ?beginvalue wikibase:timeValue ?begin .
    ?endvalue wikibase:timeValue ?end .
    FILTER("1990-12-31"^^xsd:dateTime < ?begin && ?begin < "2020-00-00"^^xsd:dateTime||
           "1990-12-31"^^xsd:dateTime < ?end && ?end < "2020-00-00"^^xsd:dateTime)
 }
 UNION
  { ?event wdt:P585 ?time.
   FILTER("1990-12-31"^^xsd:dateTime < ?time && ?time < "2020-00-00"^^xsd:dateTime)
  }
 UNION
  { ?event wdt:P580 ?begin;
           wdt:P582 ?end.
    FILTER("1990-12-31"^^xsd:dateTime < ?begin && ?begin < "2020-00-00"^^xsd:dateTime ||
           "1990-12-31"^^xsd:dateTime < ?end && ?end < "2020-00-00"^^xsd:dateTime)
  }
 UNION
  { ?event wdt:P571 ?create;
           wdt:P576 ?destroy;
    FILTER("1990-12-31"^^xsd:dateTime < ?create && ?create < "2020-00-00"^^xsd:dateTime ||
           "1990-12-31"^^xsd:dateTime < ?destroy && ?destroy < "2020-00-00"^^xsd:dateTime)
  }         
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Though not everything is working... And I doubt that the task is solvable for the whole country. --Infovarius (talk) 22:24, 15 August 2022 (UTC)

Type of Relationships

Hi There !

I've got this query :

SELECT ?PersonLabel ?RelationShipLabel

WHERE

{

  ?Person (wdt:P22|wdt:P40|wdt:P3373|wdt:P26) wd:Q4616.

  # Code for the RelationShip ???

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

}

In fact, it's simply the list of the direct family of Marilyn Monroe (mother, father, brothers/sisters, children), and it works perfectly.

But I just want to know in the field "RelationShip", what is the ... relationship of each person... Heeeeelp !

Thanks in advance ! Klymandre (talk) 12:37, 16 August 2022 (UTC)

@Klymandre: Probably this sort of thing. (Note that it does not deal well with relation (P2309) where the nature of the relationship is in the qualifier.) The second line in the body of the query is key; it relates a property to the predicate used in the first line, as documented here and is constrained by the VALUES statement.

SELECT DISTINCT ?PersonLabel ?relationshipLabel WHERE
{
  ?Person ?predicate wd:Q4616.
  ?relationship wikibase:directClaim ?predicate .
  VALUES ?relationship {wd:P40 wd:P20 wd:P451 wd:P3373 wd:P26}
  SERVICE wikibase:label{bd:serviceParam wikibase:language "en".}
}

Try it!

--Tagishsimon (talk) 12:47, 16 August 2022 (UTC)

Oh yesss ! It Works ! I'm starting with SPARQL, and it's not easy to know all.. I learn a new option : directClam ! Thanks à lot !!! Klymandre (talk) 13:49, 16 August 2022 (UTC)

Thesaurus of metrology in French

Hello, I would like to generate the Thesaurus in French related to metrology (Q394). Related senses have field of usage (P9488)metrology (Q394). I already am able to list all senses with this property, but I would also like to:

• restrict the request to Lexemes in French
• display the main form of the Lexeme
• group results by item for this sense (P5137)

— Metamorforme42 (talk) 15:12, 16 August 2022 (UTC)

@metamorforme42: Like this?

SELECT
  ?item ?itemLabel
  (GROUP_CONCAT(SUBSTR(STR(?lexeme), 32); separator="; ") AS ?lexemes)
  (GROUP_CONCAT(SUBSTR(STR(?sense), 32); separator="; ") AS ?senses)
  (GROUP_CONCAT(?lemma; separator="; ") AS ?lemmas)
{
  ?sense  wdt:P9488 wd:Q394 .
  ?lexeme ontolex:sense ?sense .
  OPTIONAL { ?sense wdt:P5137 ?item }
  ?lexeme wikibase:lemma ?lemma .
  FILTER (LANG(?lemma) = "fr")
  SERVICE wikibase:label{bd:serviceParam wikibase:language "fr" }
}
GROUP BY ?item ?itemLabel
ORDER BY ?itemLabel

Try it!

--Dipsacus fullonum (talk) 02:09, 17 August 2022 (UTC)

@Dipsacus fullonum Great ! Thank you very much. — Metamorforme42 (talk) 12:09, 17 August 2022 (UTC)

List of items with a second DOI with reference: stated in (P248) Consolidated OpenCitations Corpus – April 2017 (Q30068043)

I've found a number of items with two DOIs, one of which has a reference stated in (P248) Consolidated OpenCitations Corpus – April 2017 (Q30068043). These DOI are redirects to the other DOI on the item. So, is it possible to find items with two DOI statements, one of which has a reference stated in (P248) Consolidated OpenCitations Corpus – April 2017 (Q30068043)? If this DOI is deprecated, I'd be fine if it was left out of the results. Many thanks, Trilotat (talk) 23:00, 12 August 2022 (UTC)

@Trilotat: Here's a possible basic query; ?DOI has pr:P248 wd:Q30068043.

SELECT DISTINCT ?item ?itemLabel ?DOI ?rank ?DOI2 ?rank2 WHERE 
{
  {?item p:P356/prov:wasDerivedFrom/pr:P248 wd:Q30068043 .} hint:Prior hint:runFirst true.
  
  ?stat2 pr:P248 wd:Q30068043 .
  ?stat prov:wasDerivedFrom ?stat2. 
  ?item p:P356 ?stat .
  ?stat ps:P356 ?DOI.
  ?stat wikibase:rank ?rank.
        
  ?item p:P356 ?stat3. 
  ?stat3 ps:P356 ?DOI2.
  filter (str(?stat3) != str (?stat))
  ?stat3 wikibase:rank ?rank2.

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

Try it!

--Tagishsimon (talk) 23:44, 12 August 2022 (UTC)

@Tagishsimon: Thank you so much! This is great! Trilotat (talk) 00:01, 18 August 2022 (UTC)

unicode strlen()

I want to sort two string by their length

SELECT ?short WHERE {
  wd:Q6938433 wdt:P1813 ?short.
} order by asc(strlen(?short))

Try it!

however the result is flipped because CC0 has 3 characters and 3 bytes while 🅭🄍 has 2 characters and 4 bytes. is there an alternative to strlen() that returns the lenght of the string correctly? --Shisma (talk) 15:47, 15 August 2022 (UTC)

This looks like a bug and ought to be reported. The strlen function returns the same as the XPath string-length function which is obligated to count unicode characters. You could export the data you're interested in and run the string length calculations yourself, for example using a spreadsheet application. Infrastruktur (talk) 06:34, 18 August 2022 (UTC)

I agree that it looks like a bug. The W3C document on string-length explicit says: "Unlike some programming languages, a ·codepoint· greater than 65535 counts as one character, not two." It seems that characters with codepoints greater than 65535 are not handled well. If you enter them with standard codepoint escape sequences (according to the SPARQL specification, section 19.2), you get an error message: Query is malformed: Lexical error at line 3, column 11. Encountered: "U" (85), after : "\"\\":

SELECT (STRLEN(?str1) AS ?len1) (STRLEN(?str2) AS ?len2)
{
  BIND ("\U0001F16D" AS ?str1)
  BIND ("\U0001F10D" AS ?str2)
}

Try it!

--Dipsacus fullonum (talk) 08:50, 18 August 2022 (UTC)

Case change seems to produce a different sort of mangling. So \uXXXX works, \UXXXXXXXX fails.

SELECT (STRLEN(?str1) AS ?len1) ?str1 (STRLEN(?str2) AS ?len2) ?str2
{
  BIND ("\u0001F16D" AS ?str1)
  BIND ("\u0001F10D" AS ?str2)
}

Try it!

--Tagishsimon (talk) 08:57, 18 August 2022 (UTC)

With a lowercase u (which works), it is followed by 4 hex characters defining a codepoint in range 0-FFFF. Therefore the latter query have ?len1 and ?len2 as 5. With an uppercase U (which doesn't work), it is followed by 8 characters defining a codepoint in range 0-10FFFF. ?len1 and ?len2 should be 1 in this case. --Dipsacus fullonum (talk) 09:02, 18 August 2022 (UTC)

How many instance_of=human have only Findagrave and Familysearch as an Identifier?

How many instance_of=human have only Findagrave and Familysearch as an Identifier? Currently both Identifiers are marked as not making a person notable. If they are going to be deleted, how many would that be? RAN (talk) 01:04, 17 August 2022 (UTC)

@Richard Arthur Norton (1958- ): I know that it could search for the property numbers myself, but I don't feel like helping when the requester doesn't even the slightest to come up with the necessary information to make a query. --Dipsacus fullonum (talk) 01:35, 17 August 2022 (UTC)

That would be FamilySearch person ID (P2889) and Find a Grave memorial ID (P535), sorry! But I bet it took you longer to write the scathing remark. --RAN (talk) 01:46, 17 August 2022 (UTC)

I wont take the bet. Like I said it is a matter of feeling. You are not inclined to help when you don't feel respected. --Dipsacus fullonum (talk) 02:20, 17 August 2022 (UTC)

SELECT (COUNT(*) AS ?count)
{
  ?entity wdt:P31 wd:Q5 . 
  {
    ?entity wikibase:identifiers "1"^^xsd:integer .
    { ?entity wdt:P2889 _:FS1 . }
    UNION
    { ?entity wdt:P535 _:FG1 . }
  }
  UNION
  {
    ?entity wikibase:identifiers "2"^^xsd:integer .
    ?entity wdt:P2889 _:FS2 .
    ?entity wdt:P535 _:FG2 .
  }
}

Try it!

--Dipsacus fullonum (talk) 02:40, 17 August 2022 (UTC)

• Thanks! It is my first time asking, so was not sure of the procedure. --RAN (talk) 13:40, 18 August 2022 (UTC)

@RAN: You're welcome. And sorry if I was too irritable in my first reply. --Dipsacus fullonum (talk) 09:56, 19 August 2022 (UTC)

• Don't be sorry, you were correct, it was just my first time asking, and you gave your time and effort, which you did not have to do. Thanks again. --RAN (talk) 15:15, 19 August 2022 (UTC)

Query to pull the record of any ORG

I am using spacy to parse some text and the NER type "ORG" shows up a lot. This is usually a company like Apple or Google, or an organization like "Supreme Court". I wonder if I can pull up the wikidata records of such companies/organizations. I have built the following queries, but I'm running into some trouble.

This query gives me Google and Google Canada, which is, well ok -

SELECT DISTINCT ?item ?itemLabel ?desc WHERE {
  ?item wdt:P31 ?typeOfOrg .
  ?typeOfOrg wdt:P279 ?superclasses.
  ?item ?label "Google"@en .
  VALUES ?superclasses { wd:Q17197366 wd:Q43229 wd:Q4830453 wd:Q431289 }
  SERVICE wikibase:label { 
    bd:serviceParam wikibase:language "en". 
    ?item rdfs:label ?itemLabel .
    ?typeOfOrg rdfs:label ?typeOfOrgLabel .
    ?item schema:description ?desc .
  }
}

Try it!

The same pulls up Apple and Apple Records, which I can kind of work with -

SELECT DISTINCT ?item ?itemLabel ?desc WHERE {
  ?item wdt:P31 ?typeOfOrg .
  ?typeOfOrg wdt:P279 ?superclasses.
  ?item ?label "Apple"@en .
  VALUES ?superclasses { wd:Q17197366 wd:Q43229 wd:Q4830453 wd:Q431289 }
  SERVICE wikibase:label { 
    bd:serviceParam wikibase:language "en". 
    ?item rdfs:label ?itemLabel .
    ?typeOfOrg rdfs:label ?typeOfOrgLabel .
    ?item schema:description ?desc .
  }
}

Try it!

The following query precisely gives me Apple Inc, which I'm looking for -

SELECT DISTINCT ?item ?itemLabel ?typeOfOrg ?typeOfOrgLabel ?desc WHERE {
  ?item wdt:P31 ?typeOfOrg .
  ?typeOfOrg wdt:P31/wdt:P279* ?superclasses.
  ?item ?label "Apple"@en .
  VALUES ?superclasses { wd:Q17197366 wd:Q43229 wd:Q4830453 wd:Q431289 }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en". 
    ?item rdfs:label ?itemLabel .
    ?typeOfOrg rdfs:label ?typeOfOrgLabel .
    ?item schema:description ?desc .
  }
}

Try it!

But the same does not work for Google for some reason.

SELECT DISTINCT ?item ?itemLabel ?typeOfOrg ?typeOfOrgLabel ?desc WHERE {
  ?item wdt:P31 ?typeOfOrg .
  ?typeOfOrg wdt:P31/wdt:P279* ?superclasses.
  ?item ?label "Google"@en .
  VALUES ?superclasses { wd:Q17197366 wd:Q43229 wd:Q4830453 wd:Q431289 }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en". 
    ?item rdfs:label ?itemLabel .
    ?typeOfOrg rdfs:label ?typeOfOrgLabel .
    ?item schema:description ?desc .
  }
}

Try it!

So my question is - is there a way to improve the latter query to make it generic enough for all kinds of ORGs? Or is there a completely different way for me to get this result in SPARQL?

BTW the second type of query works for "Supreme Court" too, but gives a LOT of results, which is a nice problem to have. --Nitin Khanna

If the purpose is to find organizations with a specific English name, I would suggest:

1. Test if ?typeOfOrg is a subclass of organization (i.e. ?superclasses should only have value organization (Q43229)) The last two queries with an extra P31 do not make sense in my opinion.
2. Instead of ?label which can match all statements with monolingual text values, I would limit searches to labels and aliases. Otherwise you may catch something where it isn't a name.

So something like:

SELECT DISTINCT ?item ?itemLabel ?desc WHERE {
  ?item wdt:P31 ?typeOfOrg .
  VALUES ?superclasses { wd:Q43229 }
  ?typeOfOrg wdt:P279 * ?superclasses . hint:Prior hint:gearing "forward" .
  VALUES ?label { rdfs:label skos:altLabel }
  ?item ?label "Apple"@en .
  SERVICE wikibase:label { 
    bd:serviceParam wikibase:language "en" .
    ?item rdfs:label ?itemLabel .
    ?typeOfOrg rdfs:label ?typeOfOrgLabel .
    ?item schema:description ?desc .
  }
}

Try it!

(You may want to add ?typeOfOrgLabel to the SELECT clause, maybe aggregated with GROUP_CONCAT and GROUP BY ?item ?itemLabel ?desc) --Dipsacus fullonum (talk) 10:36, 19 August 2022 (UTC)

Oh my! This is perfect! Thank you so much @Dipsacus fullonum you've really helped me out here!

That P279 on a single Q43229 is a very elegant solution. I was unsure of the second P31, as you mentioned. It seemed superfluous. I was just trying to cover all my bases. I thought adding it there meant finding any entity of type business or whose instanceOf subclasses from the other VALUES.

Quick question if you're up for it - I've not found skos:altLabel or even altLabel anywhere in the wikidata JSON results or on any item's page. How do I discover such properties? Nitinkhanna (talk) 17:25, 19 August 2022 (UTC)

@Nitinkhanna: Correction: I really intended to write ?typeOfOrg wdt:P279 * ?superclasses (property chain with zero or more uses of P279) which will also the catch cases where ?typeOfOrg is Q43229 or a deeper subclass. But doing that can slow down the query significantly, unless you also add a hint for the optimizer. I have modified the query above to reflect this. With the change it will also find a rock band named Apple (because ?typeOfOrg is musical group (Q215380) subclass of musical ensemble (Q2088357) subclass of music organization (Q32178211) subclass of organization (Q43229)).

You can find a complete documentation of the used predicates, including skos:altLabel, and the data structure in mw:Wikibase/Indexing/RDF Dump Format which is recommended reading for persons using the query service. --Dipsacus fullonum (talk) 19:09, 19 August 2022 (UTC)

Ah! I tested P279* with "Disney" and it revealed 3 results when I was not SELECTing typeOfOrg. When I added that, the results jumped to 9 :D

That's all without using GROUP. Even with GROUP BY, I've only come to 4 results.

I think I want to stay away from P279* for now, since I don't want Apple the rock band and don't want results like disney.com and disney channel. I'm ok for now with missing out on a few results. Will keep the * in mind for future.

My GROUP BY query -

SELECT DISTINCT ?item ?itemLabel (GROUP_CONCAT(DISTINCT ?typeOfOrg;separator=", ") AS ?orgs) (GROUP_CONCAT(DISTINCT ?typeOfOrgLabel;separator=", ") AS ?orgLabels) ?desc WHERE {
  ?item wdt:P31 ?typeOfOrg .
  VALUES ?superclasses { wd:Q43229 }
  ?typeOfOrg wdt:P279* ?superclasses .
  VALUES ?label { rdfs:label skos:altLabel }
  ?item ?label "Disney"@en .
  SERVICE wikibase:label { 
    bd:serviceParam wikibase:language "en" .
    ?item rdfs:label ?itemLabel .
    ?typeOfOrg rdfs:label ?typeOfOrgLabel.
    ?item schema:description ?desc .
  }
} GROUP BY ?item ?itemLabel ?desc

Try it!

Nitinkhanna (talk) 19:39, 19 August 2022 (UTC)

Oh, and thank you @Dipsacus fullonum for the link to the recommended reading. Thank you also for the excellent help in fixing the query! I wasn't sure how quickly I'd get a response. As with everything wikipedia community related, I'd pleasantly surprised! Nitinkhanna (talk) 19:43, 19 August 2022 (UTC)

all coordinates in an entity

I'd like a map of all coordinates within an entity like Rügen (Q3237) not wanting to enter ids of individual fields. thank you 🙏 -Shisma (talk) 16:38, 15 August 2022 (UTC)

@Shisma:

#defaultView:Map
SELECT ?item ?itemLabel ?property ?propertyLabel ?coord
WHERE 
{ 
  VALUES ?item { wd:Q3237 }
  ?item ?pred1 ?stat1 .
  ?property wikibase:claim ?pred1 .
  ?stat1 ?pred2 ?stat2 .
  ?property wikibase:statementValue ?pred2 .
  ?stat2 a wikibase:GlobecoordinateValue.
  ?stat1 ?pred3 ?coord. 
  ?property wikibase:statementProperty ?pred3 . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 22:00, 15 August 2022 (UTC)

Worth noting this works only for main statement coords, not for coords as qualifiers nor references. --Tagishsimon (talk) 22:08, 15 August 2022 (UTC)

thank you! I must say I don't understand what is going on 😅 – Shisma (talk) 15:28, 16 August 2022 (UTC)

Yeah, it's a bit unhelpful. Here's an explanation, of sorts.

AFAIK, any property which takes coordinates as its main statement value, is indicated as such if we drill down to the p:/psv: node, and ask what rdf:type it is, or better ask it to confirm that its rdf:type is wikibase:GlobecoordinateValue. (And for reasons, rdf:type seems to be abbreviated as 'a'. I don't know why; seems not very helpful.)

And b/c in this query we want to do it blind - not specify the properties of interest - the query uses a variable as a predicate (e.g. in ?item ?pred1 ?stat1 .) and then constrains the predicate to whatever flavour we are interested in, so ?property wikibase:claim ?pred1 . is the triple associated with p: predicates, ?property wikibase:statementValue ?pred2 . is the triple associated with psv: predicates ... each two lines taken together say, 'there is a predicate with this variable name, and the predicate is this type of predicate'. The list of triples which link a property to its predicates is at https://www.mediawiki.org/wiki/Wikibase/Indexing/RDF_Dump_Format#Properties

So we can manipulate the predicates, knowing what type they are, even whilst not knowing (or not caring) what property they are attached to. If I substitute in P625, which we know to be a coordinate, we can perhaps see a little better what was going on. wikibase:blahblahblah triples are commented out b/c we can see what type of predicates are in each position.

#defaultView:Map
SELECT ?item ?itemLabel ?property ?propertyLabel ?coord
WHERE 
{ 
  VALUES ?item { wd:Q3237 }
  ?item p:P625 ?stat1 .
#  ?property wikibase:claim ?pred1 .
  ?stat1 psv:P625 ?stat2 .
#  ?property wikibase:statementValue ?pred2 .
  ?stat2 a wikibase:GlobecoordinateValue.
  ?stat1 ps:P625 ?coord. 
#  ?property wikibase:statementProperty ?pred3 . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 19:22, 16 August 2022 (UTC)

Tagishsimon wrote: (And for reasons, rdf:type seems to be abbreviated as 'a'. I don't know why; seems not very helpful.) – If you don't find a helpful, then just use rdf:type or even <http://www.w3.org/1999/02/22-rdf-syntax-ns#type>, the full IRI without the rdf: prefix. It is neither forced nor necessary to use the keyword a. --Dipsacus fullonum (talk) 20:29, 16 August 2022 (UTC)

No. I'm going to use it and point out that it's a very odd abbreviation, if that's okay with you. --Tagishsimon (talk) 20:38, 16 August 2022 (UTC)

Do as you please. You don't need my permission, of course. a is a strange and quite superfluous keyword, but personally I think it's convenient because it's so short. It's also special in being the only SPARQL keyword that is case-sensitive. --Dipsacus fullonum (talk) 20:51, 16 August 2022 (UTC)

It's quite hard to google search for its meaning, though. --Tagishsimon (talk) 21:02, 16 August 2022 (UTC)

I used this query in Wikidata for Web. Thanks a lot 😊 – Shisma (talk) 07:35, 20 August 2022 (UTC)

equivilant properties and all their subroperties

This query returns all properties equivalent to http://schema.org/actor

SELECT DISTINCT ?prop WHERE {
	{
		?item p:P1628/ps:P1628 <http://schema.org/actor>.
	} UNION {
		?item p:P1628/ps:P1628 <https://schema.org/actor>.
	}
	?item wikibase:propertyType wikibase:WikibaseItem.
	?item p:P1628 [ wikibase:rank ?rank ]. 
	BIND (replace(str(?item), 'http://www.wikidata.org/entity/', '') AS ?prop)
	BIND (IF(?rank = wikibase:PreferredRank, 1, IF(?rank = wikibase:NormalRank, 2, 3)) as ?order) 
} ORDER BY ?order

Try it!

which is only cast member (P161). Now I also need all subproperties (subproperty of (P1647), recrusive) optimally like this:

prop	parent
cast member (P161)
voice actor (P725)	cast member (P161)
presenter (P371)	cast member (P161)

Thank you Shisma (talk) 07:32, 20 August 2022 (UTC)

@Shisma: I renamed the variable ?item to ?property as I found it confusing to call a variable binding to properties for ?item.

SELECT DISTINCT ?prop ?parent

{
  {
	?property p:P1628/ps:P1628 <http://schema.org/actor>
  } UNION {
	?property p:P1628/ps:P1628 <https://schema.org/actor>
  }
  ?property wikibase:propertyType wikibase:WikibaseItem .
  ?subproperty wdt:P1647 * ?property .
  OPTIONAL { ?subproperty p:P1628 [ wikibase:rank ?rank ] }
  BIND (REPLACE(STR(?subproperty), 'http://www.wikidata.org/entity/', '') AS ?prop)
  BIND (IF(?property = ?subproperty, "", REPLACE(STR(?property), 'http://www.wikidata.org/entity/', '')) AS ?parent)  
  BIND (IF(?rank = wikibase:PreferredRank, 1, IF(?rank = wikibase:NormalRank, 2, 3)) AS ?order) 
}
ORDER BY ?order

Try it!

--Dipsacus fullonum (talk) 08:31, 20 August 2022 (UTC)

line 13 is smart 👌 thanks! I wish there was a way to make url querys protocol agnostic 😬 Shisma (talk) 08:36, 20 August 2022 (UTC)

Thoughts on what's going on here?

I had a vague understanding that every non-redirected and not-deleted item will have 1 wikibase:statements triple, and 1 schema:dateModified triple. But if WDQS is asked, it gives 99,005,930 & 102,817,482 as answers. What's going on?

SELECT ?dateCount ?statementCount 
WITH {
  SELECT (count(*) as ?statementCount)
  WHERE 
  {
    ?item wikibase:statements [] . 
  } } as %i
WITH {
  SELECT (count(*) as ?dateCount)
  WHERE 
  {
  ?item schema:dateModified [] . 
  } } as %j
WHERE
{
  INCLUDE %i
  INCLUDE %j
}

Try it!

--Tagishsimon (talk) 20:24, 20 August 2022 (UTC)

• Redirected items have schema:dateModified as well, and there are ~3.8 million of them
• Lexemes and property have wikibase:statements as well
• I don't think that WDQS is particularly helpful to determine item counts. It is much easier to count pages in the MediaWiki database.

—MisterSynergy (talk) 20:57, 20 August 2022 (UTC)

Dam within 1km of another dam

There are a lot of duplicate items on dam (Q12323)s. Is there a query I can use to find dams which are within n kilometres of another dam? It would be useful to restrict to a particular country, e.g. Japan (Q17) Thank you — Martin (MSGJ · talk) 21:06, 21 August 2022 (UTC)

@MSGJ: Japanese dams within n kilometres distance for n=10. To speed up the query and avoid possible timeout, I have limited it to dams with longitudes between 136 and 138 degrees. You can vary the limits.

SELECT ?item ?itemLabel ?coord ?item2 ?item2Label ?coord2 ?dist
WITH
{
  SELECT DISTINCT ?item (SAMPLE(?coord_) AS ?coord)
  {
    ?item wdt:P31 / wdt:P279 * wd:Q12323 . # Instance of Dam or subclass
    ?item wdt:P17 wd:Q17 . # In Japan
    ?item wdt:P625 ?coord_ .
    BIND (geof:longitude(?coord_) AS ?long)
    FILTER (?long > 136)
    FILTER (?long < 138)
  }
  GROUP BY ?item
} AS %items
WITH
{
  SELECT ?item ?coord ?item2 ?coord2 ?dist
  {
    INCLUDE %items
    {
      SELECT (?item AS ?item2) (?coord AS ?coord2) { INCLUDE %items }
    }
    FILTER (STR(?item) < STR(?item2))
    BIND(geof:distance(?coord, ?coord2) as ?dist)
    FILTER (?dist <= 10)
  }
} AS %dists
{
  INCLUDE %dists
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,ja" }
}
ORDER BY ?dist

Try it!

--Dipsacus fullonum (talk) 05:07, 22 August 2022 (UTC)

Probably also worth sorting out dam items having >1 coordinate statement.

SELECT ?item ?itemLabel ?coord ?rank ?coord2 ?rank2 ?dist
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q12323. 
  ?item p:P625 ?stat . ?stat ps:P625 ?coord. ?stat wikibase:rank ?rank .
  ?item p:P625 ?stat2 . ?stat2 ps:P625 ?coord2. ?stat2 wikibase:rank ?rank2 .
  ?item wdt:P17 wd:Q17
  filter(str(?stat) < str(?stat2))
  BIND(geof:distance(?coord, ?coord2) as ?dist)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

And fwiw, there's also a 'search around point' approach to the solution, slower than Df's approach, and finding more results because it is not suppressing rows arising out of exact duplicate coordinates on a single item:

SELECT ?item ?itemLabel ?coord ?place ?placeLabel ?location ?dist
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q12323. 
  ?item wdt:P625 ?coord. 
  ?item wdt:P17 wd:Q17
    BIND (geof:longitude(?coord) AS ?long)
    FILTER (?long > 136)
    FILTER (?long < 138)
  
  SERVICE wikibase:around { 
      ?place wdt:P625 ?location . 
      bd:serviceParam wikibase:center ?coord . 
      bd:serviceParam wikibase:radius "10.00001" . 
      bd:serviceParam wikibase:distance ?dist.
  } 
  ?place wdt:P31 wd:Q12323. 
  filter(str(?item) < str(?place))
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 05:41, 22 August 2022 (UTC)

Well, the search around point is much faster for small values of n, like below where set n=1, removed longitude limits and included subclasses of dams for ?place:

SELECT ?item ?itemLabel ?coord ?place ?placeLabel ?location ?dist
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q12323. 
  ?item wdt:P625 ?coord. 
  ?item wdt:P17 wd:Q17
  
  SERVICE wikibase:around { 
      ?place wdt:P625 ?location . 
      bd:serviceParam wikibase:center ?coord . 
      bd:serviceParam wikibase:radius "1.00001" . 
      bd:serviceParam wikibase:distance ?dist.
  } 
  ?place wdt:P31/wdt:P279* wd:Q12323. 
  filter(str(?item) < str(?place))
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}
ORDER BY ?dist

Try it!

--Dipsacus fullonum (talk) 06:20, 22 August 2022 (UTC)

This is brilliant and finding me lots of duplicate items to merge. Thanks both — Martin (MSGJ · talk) 09:46, 22 August 2022 (UTC)

Written matter published in certain towns from 1700 to 1799

Hi, I am trying to get printed matter (wd:Q1261026 wd:Q732577) published from 1700 to 1799 in four towns, i.e. Bilbao (Q8692), San Sebastián (Q10313), Tolosa (Q497801) and Vitoria-Gasteiz (Q14318). I am struggling with the query syntax. Would it also be possible to get all value levels (two or three in depth) for (wdt:P31) wd:Q1261026 wd:Q732577, in order to get results for subclasses like Q571 (book), or Q83818856 (grammar book)? My attempt has been as follows:

# Work or printed material published in Vitoria-Gasteiz, Bilbao, Tolosa, Donostia-San Sebastián from 1799 to 1800 with date of publication
SELECT ?item ?label ?place ?coord ?publishing date WHERE {
  VALUES ?type {wd:Q1261026 wd:Q732577}
  ?item wdt:P31 ?type;
    wdt:P577 ?publishing date.
  FILTER((?publishing date > "1700-01-01"^^xsd:dateTime) && (?publishing date < "1799-12-31"^^xsd:dateTime))
  ?item rdfs:label ?label.
  FILTER((LANG(?label)) = "en")
  FILTER {?item (wdt:P291) wd:Q8692 wd:Q10313 wd:Q497801 wd:Q14318 ?place.
    ?place wdt:P625 ?coord.
  }
  OPTIONAL { ?item wdt:P577 ?publishing date. }
}

Try it!

Iñaki LL (talk) 10:28, 22 August 2022 (UTC)

@Iñaki LL: Here's a rework, not yet responding to all of your ask, but going in the right direction. I think I might be inclined to add more values to the VALUES ?type {} list ... getting into the publications class tree quickly runs into timeouts. Or you could dispense with the ?item wdt:P31 ?type. altogether to find everything published in the places of interest. Some changes: you require a P577 at the top of the query so do not need the OPTIONAL P577 at the bottom.

# Work or printed material published in Vitoria-Gasteiz, Bilbao, Tolosa, Donostia-San Sebastián from 1799 to 1800 with date of publication
SELECT ?item ?itemLabel ?place ?coord ?publishing_date WHERE {
  VALUES ?type {wd:Q1261026 wd:Q732577}
  ?item wdt:P31 ?type.
  ?item wdt:P577 ?publishing_date.
  FILTER(?publishing_date > "1700-01-01"^^xsd:dateTime && ?publishing_date < "1799-12-31"^^xsd:dateTime)

  VALUES ?place {wd:Q8692 wd:Q10313 wd:Q497801 wd:Q14318}  
  ?item wdt:P291 ?place.
  ?place wdt:P625 ?coord.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 10:56, 22 August 2022 (UTC)

I suggest checking P577 and P291 first, limiting the numbers of results, and then check P31. A few items checked with forward gearing for all subclasses of P31 values should be no problem. --Dipsacus fullonum (talk) 13:45, 22 August 2022 (UTC)

PS. Is it intentional you exclude publications from the dates 1700-01-01 and 1799-12-31? If not, use ">=" and "<=" for the comparisons. --Dipsacus fullonum (talk) 13:51, 22 August 2022 (UTC)

Intentional? Not at all. I will fix that. Thanks both for the answers! Iñaki LL (talk) 15:06, 22 August 2022 (UTC)

Need help with a query to get the winners of the Athenaeum Literary Award that also have an ethnic group on their item

I'm working on a project to create entries for all the Athenaeum Literary Award winners Q112035151 and one of the things we want to track is the diversity of the winners. I finally got a query with no errors, but it also has no results, and for the entries I have done so far, there should be around 6, so something is still wrong. I'd appreciate any help. I'll paste in the query that gets no results below. Librarianlois (talk) 15:15, 22 August 2022 (UTC)

1. Athenaeum Literary Award winners with ethnic group DRAFT

SELECT ?itemlabel ?ethniclabel

WHERE

{

?item wdt:P166 wd:Q112035151 .

?item wdt:P1552 wd:Q41710 . #wdt needs something about has this statement, but this isn't getting results

} Librarianlois (talk) 15:16, 22 August 2022 (UTC)

@Librarianlois: The simpel query:

SELECT ?item ?itemLabel ?ethnic_groupLabel
{
  ?item wdt:P166 wd:Q112035151 .
  ?item wdt:P172 ?ethnic_group .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}

Try it!

With additional info about year and awarded work:

SELECT ?item ?itemLabel ?forLabel ?year ?ethnic_groupLabel
{
  ?item p:P166 ?award_stm .
  ?award_stm ps:P166 wd:Q112035151 .
  OPTIONAL { ?award_stm pq:P1686 ?for }
  OPTIONAL
  {
    ?award_stm pq:P585 ?date 
    BIND (YEAR(?date) AS ?year)
  }
  ?item wdt:P172 ?ethnic_group .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}

Try it!

--Dipsacus fullonum (talk) 15:49, 22 August 2022 (UTC)

Thank you so much! Librarianlois (talk) 17:26, 22 August 2022 (UTC)

Operations between numerical columns

Hi! Given this query:

SELECT DISTINCT ?item ?n (COUNT(DISTINCT ?article2) AS ?num)
WHERE {
  ?item wdt:P31/wdt:P279* wd:Q486972 ; wdt:P131* wd:Q16245 ; wikibase:sitelinks ?n .
  MINUS { ?item wdt:P31 wd:Q747074 } .
  MINUS { ?item wdt:P31 wd:Q954172 } .
  ?article schema:about ?item . 
  OPTIONAL { ?article2 schema:about ?item ; schema:isPartOf ?w .
  VALUES ?w { <https://ceb.wikipedia.org/> <https://sh.wikipedia.org/> <https://sr.wikipedia.org/> } . }
}
GROUP BY ?item ?n
ORDER BY DESC(?n)

Try it!

would it be possible to add an additional column having ?n - ?num? Thanks, --Epìdosis 20:33, 22 August 2022 (UTC)

@Epìdosis: Sure, just add (?n - ?num AS ?difference) (with whatever variable name you want) to the SELECT clause. --Dipsacus fullonum (talk) 23:41, 22 August 2022 (UTC)

Order items on their P625 in a specific way

Hello, given that basis, how to sort the points in list in a geographical way from closest to other closest point or from distance with Q112823927#P625? Bouzinac ^{💬●✒️●💛} 09:02, 23 August 2022 (UTC)

@Bouzinac: This will order the tram stops after distance to Q112823927. However that isn't in order of the tram line. I think the only reliable way to do that, is to use adjacent station (P197) or series ordinal (P1545) for each tram stop.

SELECT ?item ?itemLabel ?coords ?dist WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item wdt:P81 wd:Q18165631; wdt:P625 ?coords.
  filter(?item not in(wd:Q112825155))
  
  wd:Q112823927 wdt:P625 ?Burnley_St_Victoria_St_coord .
  BIND(geof:distance(?Burnley_St_Victoria_St_coord, ?coords) as ?dist) 
}
ORDER BY ?dist

Try it!

--Dipsacus fullonum (talk) 12:10, 23 August 2022 (UTC)

Thanks! That solution should be fine when the line is almost straight . It is not if the line is U/S/0 shaped obviously... Thanks anyway! Bouzinac ^{💬●✒️●💛} 12:48, 23 August 2022 (UTC)

PS. We also have linear reference (P6710) but it seems not to be used at all. --Dipsacus fullonum (talk) 12:17, 23 August 2022 (UTC)

Finding out 2 things at once and putting in json

For the warship Redoutable (Q1785916) it fought in 6 conflicts and had 4 types of gun, but when I ask for both things with

SELECT DISTINCT ?conflictLabel ?armamentLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
  {
    SELECT DISTINCT ?conflict ?armament WHERE {
      VALUES ?item { wd:Q1785916 }
      ?item  wdt:P607 ?conflict.
      ?item  wdt:P520 ?armament.
    }
    LIMIT 100
  }
}

Try it!

it multiples these out, so I get 24 lines in a table, and 24 blocks when downloading to json, when I really wanted something similar to

{
"armament": [ "carronade", "18-pounder long gun", "8-pounder long gun", "36-pounder long gun" ],
"conflicts": [ "Battle of Trafalgar, "Battle of Groix" etc]
}

If I download the raw json for the entity, I can get the arrays, if in a more convoluted form, but it seemed better to run a SPARQL query to get my starting point Vicarage (talk) 21:13, 22 August 2022 (UTC)

@Vicarage: The query gives the Cartesian product of the two properties it interrogates. I'm not really sure there's a good cure, beyond two queries, insofar as there is no correspondence between armaments and conflicts in the item. Here are two queries in a single query, which might or might not help.

SELECT DISTINCT ?conflictLabel ?armamentLabel WITH 
{
  SELECT ?item WHERE { 
   VALUES ?item { wd:Q1785916 }
} } as %item
WITH 
{
  SELECT ?item ?conflict WHERE {
    INCLUDE %item
    ?item  wdt:P607 ?conflict.
} } as %i
WITH 
{
  SELECT ?item ?armament WHERE {
    INCLUDE %item
    ?item  wdt:P520 ?armament.
} } as %j
WHERE {
  {INCLUDE %i}
  UNION
  {INCLUDE %j}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 21:41, 22 August 2022 (UTC)

Thanks for doing that. Its interesting that SPARQL struggles with this, as I'd have thought one key target use for wikidata is the infobox, or, as I'm trying to achieve, a tarted up page for a subset of the wikidata pages, like Resonator but with a layout better targetted to the subject. See http://johnbray.org.uk/shipcentral/Q1785916, which gets most of its data by working on the raw json file, but needs some indirection, like the dates of conflicts Vicarage (talk) 22:15, 22 August 2022 (UTC)

@Vicarage: Not convinced it struggles. Use distinct queries for distinct elements of the tarted up page. If instead you ask it for apples & pears, it will give you all combinations of apples and pears that it can. If you absolutely must ask for apples and pears in a single query, then you probably need a radical redesign, such as:

SELECT DISTINCT ?property ?propertyLabel ?value ?valueLabel WHERE {
  VALUES ?item { wd:Q1785916 }
  ?item ?predicate ?stat . 
  VALUES ?property {wd:P520 wd:P607}
  ?property wikibase:claim ?predicate.
  ?stat ?predicate2 ?value .
  ?property wikibase:statementProperty ?predicate2.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } 
}

Try it!

--Tagishsimon (talk) 22:50, 22 August 2022 (UTC)

Well, there is the option to use GROUP_CONCAT as in this query:

SELECT (GROUP_CONCAT(DISTINCT ?armament_label; SEPARATOR=', ') AS ?armaments) (GROUP_CONCAT(DISTINCT ?conflict_label; SEPARATOR=', ') AS ?conflicts) WHERE {
  OPTIONAL { 
    wd:Q1785916 wdt:P520 ?armament .
    ?armament rdfs:label ?armament_label .
    FILTER(LANG(?armament_label) = 'en') .
  }
  OPTIONAL {
    wd:Q1785916 wdt:P607 ?conflict .
    ?conflict rdfs:label ?conflict_label .
    FILTER(LANG(?conflict_label) = 'en') .
  }
}

Try it!

However, if you aggregate in SPARQL already, you lose the possibility to change the outcome later (pretty much). A better workflow would be to query both aspects separately, fetch the query results with whatever scripting language that is usable in your environment, and do the aggregation within the scripting language. —MisterSynergy (talk) 23:14, 22 August 2022 (UTC)

Your solution provides text closer to what I finally want to display, but as you say its rather over-processed compared with Tagishsimon's second idea which I like as its very amenable to grep/sed . I had hoped to get a query that could be run directly from ExternalData extension in Mediawiki, but the json text processing is not strong in the extension, and exception handing might struggle, and I'd be concerned about the performance implications and stuttering of using too many API calls. For the moment I'm doing a single read of a JSON file I craft locally, but that needs a refresh mechanism, which always disappoints people who do a WD edit and then expect my site to respond instantly. My real case has perhaps 2 dozen fields, so I need something that scales. I will have a play. Thanks to you both for your input. Vicarage (talk) 22:31, 23 August 2022 (UTC)

@Vicarage: With many independent fields, I would suggest a solution with one variable for field name and another for the value. That will scale for many fields.

SELECT ?field ?valueLabel WHERE
{
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]" }
  {
    SELECT ?field ?value WHERE
    {
      {
        BIND ("conflict" AS ?field)
        VALUES ?item { wd:Q1785916 }
        ?item wdt:P607 ?value
      }
      UNION
      {
        BIND ("armament" AS ?field)
        VALUES ?item { wd:Q1785916 }
        ?item wdt:P520 ?value
      }
    }
    LIMIT 100
  }
}

Try it!

--Dipsacus fullonum (talk) 05:18, 24 August 2022 (UTC)

Get bubble chart based on the number of printed matter published each year

Hi, could you help with this? Based on the syntax of this bubble chart model, I have attempted to generate one in which the years with the largest proportion of printed matter (Q1261026)/publication(Q732577) are larger and at the centre of graph. However, it did not work for me. It would also need to constrain years and publication place values (filter). Thanks again

Period: FILTER(?publishing_date >= "1700-01-01"^^xsd:dateTime && ?publishing_date <= "1799-12-31"^^xsd:dateTime)
Publication place (P291): VALUES ?place {wd:Q8692 wd:Q10313 wd:Q497801 wd:Q14318}
Iñaki LL (talk) 14:48, 23 August 2022 (UTC)

@Iñaki LL: This seems to work. The chart fu seems to insist on the first variable being a string; or, at least, it didn't like whatever data type ?year_ is. The ?count of items is the bubble size. Largest in the centre seems to depend on the ORDER BY DESC(?count). The UI is documented here (for some value of "documented", such as, not very well).

#title:Publications by year
#defaultView:BubbleChart
SELECT (str(?year_) as ?year) (count(*) as ?count)
WHERE
{
  VALUES ?place {wd:Q8692 wd:Q10313 wd:Q497801 wd:Q14318}
  ?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

--Tagishsimon (talk) 02:18, 24 August 2022 (UTC)

Thanks Tagishsimon. The approach is correct, but I see only 60 hits and there should be 106. (Q1261026)/publication(Q732577) are missing, so I guess that should be explicitly stated at some point. Iñaki LL (talk) 08:10, 24 August 2022 (UTC)

Ah, not so fast, @Iñaki LL:. Each hit is a year; there are 60 discrete years in which there are publications. Each year has a varying sum of publications, which per below, seems to amount to 112 discrete publications across the century. The query does not ask about publcation type (cf. Q1261026, Q732577) ... querying down this line would not add more hits, b/c that's limited by the requirements for the item to have a P921 and P577. Best it would do is reduce the number of hits.

SELECT (count(DISTINCT ?item) as ?count)
WHERE
{
  VALUES ?place {wd:Q8692 wd:Q10313 wd:Q497801 wd:Q14318}
  ?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_)
}

Try it!

--Tagishsimon (talk) 08:24, 24 August 2022 (UTC)

Actually so, you are absolutely right! GROUP BY ?year_ ORDER BY DESC(?count) is missing above. Iñaki LL (talk) 10:34, 24 August 2022 (UTC)

Table of US presidential elections, % votes for each party by state, since 1856

Something like this https://en.wikipedia.org/wiki/List_of_United_States_presidential_election_results_by_state#Chronological_table_with_states_listed_by_Census_region

But the linked tables only show which party won in each state - I want to see the percentage each party got since 1856. Just dems and reps would do, but ideally all the parties vote %ages (I accept this will mean a lot of blank cells as most 3rd parties exist for a short time).

Thanks Risingrain (talk) 13:55, 24 August 2022 (UTC)

Generally, as far as I can see, there is not yet data for US presidential elections at state level in Wikidata. --Dipsacus fullonum (talk) 15:43, 24 August 2022 (UTC)

Agreed. There's the United States presidential elections in a single state (Q112711344) class tree, but I've yet to find any voting data, even as qualifiers, of the typical set of properties. --Tagishsimon (talk) 16:18, 24 August 2022 (UTC)

Ah ok... I kind of assumed you were querying wikipedia's infoboxes and tables, didn't realise wikidata is separate to whats on wikipedia. Thanks anyway. Risingrain (talk) 16:36, 24 August 2022 (UTC)

Filter using P1435 items with multiple values

Hi, I am trying to do a query where I would filter out items with heritage designation (P1435) = registered archaeological site in Finland (Q31027091) but include if item has any other P1435 values. Single item can have multiple different P1435 values so it can have Q31027091 AND some other value which is reason why it should be in the list. However, my problem is that filtering will drop these multiple value cases too.

Example: The Keitele boundary stone (Q113617253) is the test case which will be filtered out.

1.) All results without filtering

SELECT ?item ?itemLabel ?p1435 
WHERE 
{
  ?item wdt:P131 wd:Q1534714 .
  ?item wdt:P1435 ?p1435

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

Try it!

2.) Broken filtering which will drop also the Keitele boundary stone (Q113617253) with P1435 = Q18089563 and Q31027091

SELECT ?item ?itemLabel ?p1435 
WHERE 
{
  ?item wdt:P131 wd:Q1534714 .
  ?item wdt:P1435 ?p1435

  FILTER NOT EXISTS { ?item wdt:P1435 wd:Q31027091 }

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

Try it!

Any idea how I should write the query so that it would include the Q113617253 too in the results? Zache (talk) 09:08, 25 August 2022 (UTC)

@Zache: If I understand, then this. Query rejects P1435 values of Q31027091, but includes the item if it has another P1435 value.

SELECT ?item ?itemLabel ?p1435 ?p1435Label 
WHERE 
{
  ?item wdt:P131 wd:Q1534714 .
  ?item p:P1435 ?statement .
  FILTER NOT EXISTS { ?statement ps:P1435 wd:Q31027091 }
  ?statement ps:P1435 ?p1435.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fi". }
}

Try it!

--Tagishsimon (talk) 09:15, 25 August 2022 (UTC)

@Zache, Tagishsimon: In my opinion FILTER NOT EXISTS seems a little overkill here. Wont you get the results with just a filter on the accepted values of ?p1435?

SELECT ?item ?itemLabel ?p1435 
WHERE 
{
  ?item wdt:P131 wd:Q1534714 .
  ?item wdt:P1435 ?p1435

  FILTER (?p1435 != wd:Q31027091 )

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

Try it!

--Dipsacus fullonum (talk) 10:04, 25 August 2022 (UTC)

Thanks, both are working and the second one is also query speed neutral in my case. -- Zache (talk) 10:23, 25 August 2022 (UTC)

Interesting. tbh, it did not occur to me that FILTER (?p1435 != wd:Q31027091 ) was an option. It's a rarely used filter construction, in my experience of this forum (the main locus of my SPARQL learning). --Tagishsimon (talk) 22:19, 25 August 2022 (UTC)

List the labels of all properties for one object

Hi, there !

I'm learning SPARQL and Wikidata, and for my information, is it possible to list all the name of all the predicates (properties) of one object ?

By example : Mona Lisa (Q12418). Is it possible to have this result ? :

NameOfPredicate - Content

instance of - painting

inception - 1500s

name after - wife

name after - Lisa del Giocondo

etc. for all the predicates ?

I found "wikibase:directClaim", but I have no idea how to implement it correcty :-(

Thanks in advance ! Klymandre (talk) 13:47, 29 August 2022 (UTC)

@Klymandre: It is. Here's an example, limiting the predicates to those which are claims or directClaims. The key thing to understand about wikibase:directClaim and its ilk, is that they are the predicates which link a property item (e.g. wd:P31) to all of its predicates. So wd:P31 wikibase:directClaim wdt:P31 and wd:P31 wikibase:claim p:P31 and so on. The full list is here.

SELECT ?item ?itemLabel ?property ?propertyLabel ?predicateType ?predicate ?predicateLabel ?value ?valueLabel WHERE
{
    VALUES ?item {wd:Q12418}
    ?item ?predicate ?value .                # The item has some predicates & values
    ?property ?predicateType ?predicate .    # gratuitously find out what type the predicate it
    {?property wikibase:directClaim ?predicate .} # limit the predicates of interest to directClaims - i.e. wdt:
    UNION
    {?property wikibase:claim ?predicate .}       # and to claims - i.e. p:
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

I think it is double to include both wikibase:directClaim and wikibase:claim in the result table. I would suggest:

SELECT ?item ?itemLabel ?property ?propertyLabel ?value ?valueLabel ?rank ?bestRank WHERE
{
    VALUES ?item {wd:Q12418}
    ?item ?predicate ?statement .              # The item has some predicates & values
    ?property wikibase:claim ?predicate .      # Select predicates that are claims (properties) - i.e. p:
    ?property wikibase:statementProperty ?sP . # Get the corresponding statementProperty - i.e. ps:
    ?statement ?sP ?value .                    # And the statement value
    ?statement wikibase:rank ?rank .
    OPTIONAL { ?statement a wikibase:BestRank BIND(true AS ?bestRank) }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Dipsacus fullonum (talk) 17:16, 29 August 2022 (UTC)

Oh, thank you ! these two examples are exactly what I need ! Not so easy to understand, but I'm going to study it ! Klymandre (talk) 20:45, 29 August 2022 (UTC)

@Klymandre: Feel free to ask questions. --Dipsacus fullonum (talk) 20:55, 29 August 2022 (UTC)

Why does the group_concat multiply up results for a few items?

SELECT ?item ?itemLabel ?itemDescription ?location  ?tripadvisor
  (GROUP_CONCAT(DISTINCT ?propertyCategoryLabel; SEPARATOR = ", ") AS ?categories) WHERE {

  {
    SELECT ?item ?propertyCategoryLabel ?itemLabel ?itemDescription ?location ?tripadvisor WHERE {
      {
        ?item p:P31 ?statement0.
        ?statement0 (ps:P31/(wdt:P279*)) wd:Q1785071.
      }
      UNION
      {
        ?item p:P31 ?statement1.
        ?statement1 (ps:P31/(wdt:P279*)) wd:Q23413.
      }
      UNION
      {
        ?item p:P31 ?statement2.
        ?statement2 (ps:P31/(wdt:P279*)) wd:Q56344492.
      }
      ?item p:P7959 ?statement3.
      ?statement3 (ps:P7959/(wdt:P279*)) wd:Q67479626.
    
      OPTIONAL { ?item wdt:P31 ?propertyCategory. }
      OPTIONAL { ?item wdt:P3134 ?tripadvisor.}
      OPTIONAL { ?item wdt:P625 ?location.}
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
    }
  }
}

GROUP BY ?item ?itemLabel ?itemDescription ?location ?tripadvisor

Try it!

produces a table with nearly what I want, one line per fortification with the multiple possible instance_of values combined. Except for some places, like Hever Castle, when it goes mad and repeats the 3 instances multiple times. Why? Vicarage (talk) 18:42, 29 August 2022 (UTC)

@Vicarage: Probably, the item can be found through multiple paths associated with the UNIONed requirements. The results will include one row for each combination of P31_type and the statement values produced in the UNIONed requirements. The cure is to group_concat DISTINCT values; I amended your query above to add that word. --Tagishsimon (talk) 20:10, 29 August 2022 (UTC)

@Vicarage: The reason is that labels generated by the label service in automatic mode cannot be used in aggregation functions like GROUP_CONCAT (this is mentioned at mw:Wikidata Query Service/User Manual#Label service). Fortunately it is easy the remove the duplicates.

• Method 1: Don't use the label service for ?propertyCategoryLabel
• Method 2: Use the label service in manual mode
• Method 3: Add the DISTINCT keyword to the GROUP_CONCAT parameter:

  GROUP_CONCAT(DISTINCT ?propertyCategoryLabel; SEPARATOR = ", ")

I recommend method 3 if it OK or desirable to remove legit duplicates too. --Dipsacus fullonum (talk) 20:18, 29 August 2022 (UTC)

Thanks both for your advice. Its allowed me to complete http://johnbray.org.uk/shipcentral/Q67479626, where you can filter by both descriptions and instance labels so I can spot 'museum' Vicarage (talk) 20:32, 29 August 2022 (UTC)

set layer color in a map

Hi, I'm working on a presentation where I'd like to show a sort of timeline of the work I did in creating items of libraries, I'm creating a series of layered maps filtered by the first item number of each batch so I could show it in a sort of gif or whatever. BUT every time I run the query the colors of the layers change. Is there any way to make them stick? Reading this makes me suspect it could be done but I couldn't fine any examples. Could it work in a query like this? Thanks! divudì 17:57, 30 August 2022 (UTC)

@Divudi85:. Seems to involve stuffing hex rgb colour values, shorn of their conventional leading hash - i.e. 000000 not #000000 - into a variable named ?rgb, and remembering to include ?rgb in the select. The ?rgb value can be hidden in the mouse-click pop-up by some javascript-formatted nonsense - #defaultView:Map{"hide":"?rgb"}. Here's an example of colour control; cartographic design is my passion.

#title:Distribuzione biblioteche per tipologia in Italia. layers (lista ridotta)
#defaultView:Map{"hide":"?rgb"}
SELECT DISTINCT ?item ?itemLabel ?itemDescription ?coord ?layer ?rgb WHERE {
  {?item wdt:P31/wdt:P279* wd:Q7075} .
  ?item wdt:P17 wd:Q38; wdt:P791 ?isil.
  OPTIONAL { ?item wdt:P31 wd:Q7075 . BIND("biblioteche" as ?layer) BIND("FF0000" as ?rgb) }
  OPTIONAL { ?item wdt:P31 wd:Q2326815 . BIND("comunali" as ?layer) BIND("222222" as ?rgb) }
  OPTIONAL { ?item wdt:P31 wd:Q28564 . BIND("pubbliche" as ?layer) BIND("000000" as ?rgb) }
  OPTIONAL { ?item wdt:P31 wd:Q385994 . BIND("specializzate" as ?layer) BIND("FFFFFF" as ?rgb) } 
  OPTIONAL { ?item wdt:P31 wd:Q105763925 . BIND("conservazione" as ?layer) BIND("00FF00" as ?rgb) } .
  OPTIONAL { ?item wdt:P31 wd:Q1622062. BIND("universitarie" as ?layer) BIND("0000FF" as ?rgb) } .
  OPTIONAL { ?item wdt:P31 wd:Q106231131. BIND("scolastiche" as ?layer) BIND("888888" as ?rgb) } .
  BIND(IF(!BOUND(?rgb),"444444",?rgb) as ?rgb) # covers cases not dealt with by the OPTIONALs above
   ?item wdt:P625 ?coord 
  FILTER ((xsd:integer(SUBSTR(STR(?item),33))) < 113086038) . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it, en". }
}

Distribuzione biblioteche per tipologia in Italia. layers (lista ridotta)

--Tagishsimon (talk) 18:17, 30 August 2022 (UTC)

amazing! thanks a million divudì 19:39, 30 August 2022 (UTC)

Check on multiple qualifiers

Hi! The following

SELECT ?item ?aut
WHERE {
  ?item p:P4223 ?ei .
  ?ei pq:P50 wd:Q3772798 .
}

Try it!

find items having author (P50)Goffredo Coppola (Q3772798) as qualifier of Treccani's Enciclopedia Italiana ID (P4223). I would be interested in all items having Treccani's Enciclopedia Italiana ID (P4223) with a P50 qualifier, excluding those having author (P50)Goffredo Coppola (Q3772798) as only P50 qualifier (e.g. Hermippus (Q1235541)) but including items having author (P50)Goffredo Coppola (Q3772798) among other P50 qualifiers (e.g. Posidonius (Q185770)). Thanks, --Epìdosis 17:13, 31 August 2022 (UTC)

@Epìdosis: I'm not sure how you want the results presented. This query just list the items. I can add labels and/or other information is you want.

SELECT DISTINCT ?item
WHERE {
  ?item p:P4223 ?ei .
  ?ei pq:P50 ?author .
  FILTER (?author != wd:Q3772798 )
}

Try it!

--Dipsacus fullonum (talk) 17:42, 31 August 2022 (UTC)