Wikidata:Request a query/Archive/2023/07

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


Col de l'Iseran (Q547474)

With this data https://www.wikidata.org/wiki/Q547474#P837, how to calculate the number of days where this moutain road was opened, per year? Bouzinac 💬●✒️●💛 08:39, 23 June 2023 (UTC)

@Bouzinac: This can't be done at present because the item is missing some closing dates, but assuming they were added, solving this with just SPARQL could be done like this: https://w.wiki/6tPP . Infrastruktur (talk) 13:24, 28 June 2023 (UTC)
Hi, i've slighlty modified your query as follows : https://w.wiki/6vUK
Seems working (but not conclusive at all regarding global warming (Q7942) ) Bouzinac 💬●✒️●💛 11:22, 1 July 2023 (UTC)

Properly formatted years as x-axis for a bar chart

Hello,

I've made the following query: 

# count of games published in ROM format per year, 1980-1990
SELECT DISTINCT
# ?game
# ?gameLabel
# ?format
# ?formatLabel
# ?pubDate
?pubYear
(COUNT(*) AS ?game_count)
WHERE { 
  ?game wdt:P31 wd:Q7889.
  ?game wdt:P437 ?format.
  ?game wdt:P577 ?pubDate

  BIND (YEAR(?pubDate) AS ?pubYear)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  
  FILTER (?pubYear >= 1980 && ?pubYear <= 1990)
  }
GROUP BY ?pubYear
ORDER BY ASC(?pubYear)
Try it!

However, when displaying the results as a bar chart, it looks like this:

How do I get a better-looking x-axis please, with 1 tick per year?

Thanks! Vincent Tep (talk) 12:53, 2 July 2023 (UTC)

The X and Y axes use the first eligible columns of the output table, so if you use strings/labels for the first column, it will do what you want: https://w.wiki/6wHM . See also: Wikidata:SPARQL_query_service/Wikidata_Query_Help/Result_Views —Infrastruktur (talk) 13:42, 2 July 2023 (UTC)
Thank you very much @Infrastruktur ! Vincent Tep (talk) 19:25, 2 July 2023 (UTC)
Hello again,
Here's my finalised query.
It demonstrates double grouping.
I'm sharing it here for people who might be interested in achieving similar results.
Thank you for pointing me in the right direction!
# count of games published in ROM format per platform per year, 1995-1997
#defaultView:BarChart
SELECT DISTINCT
?platformLabel
(COUNT(*) AS ?game_count) (STR(?pubYear_) AS ?pubYear) # turn back pubYear_ YEAR into STR for the bar chart x-axis to look good
WHERE { 
  ?game wdt:P31 wd:Q7889. # instance of video game
  ?game wdt:P437 wd:Q633454. # distribution format is ROM cartridge
  ?game wdt:P577 ?pubDate.
  ?game wdt:P400 ?platform.

  BIND (YEAR(?pubDate) AS ?pubYear_) # cast pubDate as YEAR for filtering
  
  FILTER (?pubYear_ >= 1995 && ?pubYear_ <= 1997)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?pubYear_ ?platformLabel
ORDER BY ASC(?platformLabel)
Try it!
Vincent Tep (talk) 12:16, 3 July 2023 (UTC)

Display options for charts

Hello, I've made this simple query: 

# line graph of annual number of weddings for Mulhouse
#defaultView:LineChart
SELECT
(STR(YEAR(?year)) AS ?year_)
?wedCount
WHERE {
  wd:Q79815 p:P5982 ?s.
  ?s ps:P5982 ?wedCount.
  ?s pq:P585 ?year.
  }
Try it!

I would like to customise the way it looks. For instance, I would like to:

  • Axis:
    • pick the min/max values for the Y-axis;
    • display the labels in italics or in bold;
  • Line:
    • pick the color of the line;
    • pick the thickness, or make it dotted, dashed, etc.;
  • Hoverbox:
    • pick what values to be displayed in the hoverbox;
    • define how numbers should be displayed in the hoverbox (i.e. for 1984, I'd like to display "630" rather than "0.6k")
  • etc.

Can you please point me to any relevant documentation - in this case, for line charts, but also for other types of charts? Thank you. Vincent Tep (talk) 10:29, 6 July 2023 (UTC)

@Vincent Tep I think the short answer is that you basically can't do any of those things in the query service, unfortunately - blazegraph is very basic in terms of what it supports for graphs. Even really basic customisation like colours is only available for the bubblechart and "graph" (not LineChart) views - see b:SPARQL/Views#LineChart. Andrew Gray (talk) 23:02, 7 July 2023 (UTC)

Empty attribute column

Hello, I have a problem with a query, which I stripped down to the following example query: 

SELECT DISTINCT ?obj ?objLabel ?att ?count WITH {
  SELECT ?obj (COUNT(?wikipage) AS ?count) WHERE {
    hint:Query hint:optimizer "None" .
    ?obj wdt:P31 wd:Q15633582 .
    ?obj wdt:P571 ?att . 
    ?wikipage schema:about ?obj .
    ?wikipage schema:isPartOf/wikibase:wikiGroup "wikipedia" . }
  GROUP BY ?obj HAVING (?count >= 100)
} AS %get WHERE {
  INCLUDE %get
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en" . } }
ORDER BY desc(?count)
Try it!

Here, I want to display MediaWiki sites with over 100 Wikipages and their dates of inception. However, the "att" column comes back empty even though the items have that property. I think this is due to the SELECT ?obj clause, but I don't know to fix it and ChatGPT didn't either. How can I display the values in that column? Appreciate all help. --π π π (talk) 21:27, 7 July 2023 (UTC)

@Π π π The perils of asking a chatbot to write code - though it's pretty good, all things considered! You need one small tweak, which is for your first query to select ?att - otherwise it finds ?att, checks it exists, and then doesn't bother recording it.
SELECT DISTINCT ?obj ?objLabel ?att ?count WITH {
  SELECT ?obj ?att (COUNT(?wikipage) AS ?count) WHERE {
    hint:Query hint:optimizer "None" .
    ?obj wdt:P31 wd:Q15633582 .
    ?obj wdt:P571 ?att .
    ?wikipage schema:about ?obj .
    ?wikipage schema:isPartOf/wikibase:wikiGroup "wikipedia" . }
  GROUP BY ?obj ?att HAVING (?count >= 100)
} AS %get WHERE {
  INCLUDE %get
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en" . } }
ORDER BY desc(?count)
Try it!
It also works if you select ?att later on in the process along with the labels:
SELECT DISTINCT ?obj ?objLabel ?att ?count WITH {
  SELECT ?obj (COUNT(?wikipage) AS ?count) WHERE {
    hint:Query hint:optimizer "None" .
    ?obj wdt:P31 wd:Q15633582 .
    ?wikipage schema:about ?obj .
    ?wikipage schema:isPartOf/wikibase:wikiGroup "wikipedia" . }
  GROUP BY ?obj ?att HAVING (?count >= 100)
} AS %get WHERE {
  INCLUDE %get
    ?obj wdt:P571 ?att .
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en" . } }
ORDER BY desc(?count)
Try it!
Andrew Gray (talk) 22:58, 7 July 2023 (UTC)
Thanks so much! I thought I'd tried this, but didn't think to add ?att in the grouping as well and got an error. BTW, ChatGPT didn't write this code, I just asked it for help in fixing and got lots of suggestions, none of which worked. Though I have to say that it is indeed impressively good at generating code. --π π π (talk) 23:47, 7 July 2023 (UTC)

Query: protected areas

I would like to have a query that lists all protected areas of the Federal Inventory of raised and transitional bogs of national importance in Switzerland (Q108060374). The output in a table with the official name, the municipality(ies), geographical coordinates (only whether, present/not present), picture (if available). Is this possible? Many thanks in advance. Matutinho 17:12, 8 July 2023 (UTC) Matutinho 17:12, 8 July 2023 (UTC)

Items with Hindi Wikipedia articles but no English Wikipedia articles

I'm new to Wikidata Query Service and I want to parse a query that will show items with Hindi Wikipedia articles but no English Wikipedia articles. It will be useful since I'm going to translate such Hindi Wikipedia articles into Bengali. Sbb1413 (he) (talk • contribs) 10:00, 10 July 2023 (UTC) 

SELECT ?item ?sitelink WHERE {
  ?sitelink schema:about ?item ;
        schema:isPartOf <https://hi.wikipedia.org/> .
  MINUS {?sitelink2 schema:about ?item ;
                    schema:isPartOf <https://en.wikipedia.org/> .}
}
LIMIT 1000
Try it!

Find all the reference statements for a particular statement across all items

Hello! I am fairly new to Wikidata. I'm working with a particular statement, "on focus list of Wikimedia project (P5008)" "Harvard Film Archive Project (Q111249030)". I can construct a basic SPARQL query to retrieve all of the items that have that particular statement. However, I want to retrieve all the items that ONLY have reference statement(s). At the same time, I want to retrieve all of the reference statement(s) and their properties and the values, and want the query to display that information for me. Thank you in advance! Xtine0709 (talk) 13:02, 7 July 2023 (UTC)

That's a lot. There are over 600 items with more than 15 000 references. Clicking on one of the links in the "st" column will take you to the statement.
select ?itemLabel ?st ?propertyLabel ?predicate ?value
where {
  {
    select ?item ?st ?property ?predicate ?value
    where {
      ?item wdt:P5008 wd:Q111249030 . # 613
      ?item ?p ?st .
      ?st a wikibase:BestRank .
      ?st prov:wasDerivedFrom ?refn . # 15756
      ?refn ?predicate ?value .
      ?property wikibase:reference ?predicate .
    }
  }
  service wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
order by ?st
Try it!
Infrastruktur (talk) 16:20, 8 July 2023 (UTC)
Thank you so much for this query! I think I might've explained what I wanted a bit poorly. First, I want to retrieve all the items with the "on focus list of Wikimedia project (P5008)" "Harvard Film Archive Project (Q111249030)" statement. So there are 613 items for that! And then I want to retrieve all the reference statement(s) for those 613 items, but only for that particular statement. I imagine there may be 1 or 2 reference statement(s) per "on focus list of Wikimedia project (P5008)" "Harvard Film Archive Project (Q111249030)" statement for each item, so I think I was expecting the number of reference statement(s) to be around 1200 or so. Would this be a possible feat to accomplish in one query? Thank you so much for the help-- I appreciate it so much! Xtine0709 (talk) 19:26, 10 July 2023 (UTC)
select ?itemLabel ?st ?propertyLabel ?predicate ?value
where {
  {
    select ?item ?st ?property ?predicate ?value
    where {
      ?item p:P5008 ?st .
      ?st a wikibase:BestRank .
      ?st ps:P5008 wd:Q111249030 . # 613
      ?st prov:wasDerivedFrom ?refn .
      ?refn ?predicate ?value .
      ?property wikibase:reference ?predicate .
    }
  }
  service wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
}
order by ?st
Try it!
Infrastruktur (talk) 11:14, 11 July 2023 (UTC)
Thank you so much!!!!!!!!!!! This is perfect!!! Would you mind if I asked some more questions about how the SPARQL code is actually written? (would love to hear a breakdown of how to come up with this) I'd love to learn more about how this actually works all in a single query! Or if not, would you know of any resources that tackles creating complex SPARQL queries such as this? I tried researching for HOURS, trying to find any example code that had to do with retrieving reference statements, both all properties and values, but they all came with the assumption of knowing what reference properties to write. So this is absolutely amazing! You are a lifesaver!! Have a wonderful day :) Xtine0709 (talk) 18:45, 11 July 2023 (UTC)
Thanks. Ask away. I found the following illustration helpful in understanding how things are connected. In the last query I follow the link from the item to the statement node and then from the statement node to the reference node.
Infrastruktur (talk) 19:35, 11 July 2023 (UTC)

Query optimization

Any chance this query doesn't time out? 

SELECT ?item ?dob ?dod ?sitelinks (COUNT(DISTINCT ?article) AS ?lang_wiki_sitelinks)
WHERE {
  ?item wdt:P31 wd:Q5 .
  OPTIONAL { ?item wdt:P569 ?dob }
  OPTIONAL { ?item wdt:P570 ?dod } 
  ?article schema:about ?item ;
           schema:isPartOf ?partof .
  ?partof wikibase:wikiGroup "wikipedia" .
  ?item wikibase:sitelinks ?sitelinks .
}
GROUP BY ?item ?dob ?dod ?sitelinks
HAVING (?lang_wiki_sitelinks > 200)
ORDER BY DESC(?lang_wiki_sitelinks)
Try it!

Jklamo (talk) 13:13, 13 July 2023 (UTC)

Gotta go fast.
SELECT ?item ?dob ?dod ?sitelinks ?lang_wiki_sitelinks WHERE {
  {
    SELECT ?item ?sitelinks (COUNT(?article) AS ?lang_wiki_sitelinks) WHERE {
      hint:SubQuery hint:optimizer "None".
      ?item wikibase:sitelinks ?sitelinks . hint:Prior hint:rangeSafe true . FILTER (?sitelinks > 200)
      ?item wdt:P31 wd:Q5 .
      ?article schema:about ?item ;
        schema:isPartOf / wikibase:wikiGroup "wikipedia" .
    }
    GROUP BY ?item ?sitelinks HAVING (?lang_wiki_sitelinks > 200)
  }
  OPTIONAL { ?item wdt:P569 ?dob }
  OPTIONAL { ?item wdt:P570 ?dod } 
}
ORDER BY DESC(?lang_wiki_sitelinks)
Try it!
Infrastruktur (talk) 14:37, 13 July 2023 (UTC)
Thanks, that's much faster than I expected! Jklamo (talk) 12:04, 14 July 2023 (UTC)

Following up on this archived query request from 22/06/23

Hello, @MargaretRDonald requested this query be written for her last month.

I've come up with the query below. I hope more experienced Wikidata users will chime in and make it return more hits / run faster / etc. 

SELECT
  ?imageSrc 
  (GROUP_CONCAT(?imageLegendLanguage;separator=",") AS ?allLang)
  WHERE {
     ?statement ps:P18 ?imageSrc; # all images
      pq:P2096 ?imageLegend.      # all their legends
      BIND(LANG(?imageLegend) AS?imageLegendLanguage). # get each legend's language tag (any image/legend w/o a language tag is left out)
  }
  GROUP BY ?imageSrc
  HAVING contains(?allLang,"ko")
Try it!

@MargaretRDonald this is a list of pictures whose legends include a Korean one. You can download the results & filter out those that already have an English legend.

Any comments & help to make this query better would be much appreciated. Thanks! Vincent Tep (talk) 15:13, 10 July 2023 (UTC)

Thanks @Vincent Tep: Returned 606 images(!) And I have now annotated a few, thanks to you. Regards, MargaretRDonald (talk) 23:19, 10 July 2023 (UTC)
That's one of the things that bother me - I guess there must be many more than 606 images with a legend in Korean. Either my request is poorly-written (quite likely) and/or there are legends in Korean w/o a language tag (I guess that's possible?), in which case looking for legends that contain Korean characters would be a possibility. Vincent Tep (talk) 05:15, 11 July 2023 (UTC)
Hello again @MargaretRDonald,
I've come up with the request below to look for all media legends containing Korean characters:
SELECT 
?imageSrc 
?imageLegend 
?imageLegendLanguage 
WHERE {
  OPTIONAL {
    ?statement ps:P18 ?imageSrc;
      pq:P2096 ?imageLegend.
    BIND(LANG(?imageLegend) AS ?imageLegendLanguage)
  }
  FILTER(REGEX(?imageLegend, "[가-힣]"))
}
Try it!
It returns 609 hits (the original 606 + 3 en comments containing a few Korean characters).
Is it really possible that only 600ish pictures on wikicommons have Korean descriptions? I would have expected many, many more results. Any help would be appreciated. Thanks! Vincent Tep (talk) 14:48, 11 July 2023 (UTC)
@Vincent Tep: There is a simple answer to that. I think you are querying wikidata, and not wikicommons. The query needs to search wikicommons images, which is possible. I have a query for plants beginning with P which does just that but I am not smart enough to modify it.MargaretRDonald (talk) 21:25, 11 July 2023 (UTC)
Hi @MargaretRDonald,
I've played around with your request and tried to use the Wikimedia Commons Query Service but I just can't figure out how to achieve your goal (getting all images on wikicommons that have an English description AND that don't have a Korean one). If anyone reading this is able to write such a query, please help - I'm stumped. Thanks Vincent Tep (talk) 12:51, 15 July 2023 (UTC)
@Vincent Tep: Thanks for your efforts on my behalf. MargaretRDonald (talk) 16:35, 15 July 2023 (UTC)

Change

About the item "list of Brazilian records in swimming" (Q17006288): I ask that you change the interwiki in Portuguese from the article "Lista de recordes brasileiros na natação" to the article "Lista de recordes brasileiros da natação": as the 2 articles talk about the same subject , but the second one is much more complete and updated and the first one is abandoned, I ask you to change the interwiki for the second article. Thanks.177.142.138.240 16:17, 13 July 2023 (UTC)

Hello there. This isnt really the right place for this request, as this page is for requesting help with the query service. Perhaps the Interwiki conflicts page be better. However i've taken a quick look at the two articles on Portuguese Wikipedia and the article currently linked to Wikidata was actually edited more recently than the second, although it does seem less complete. I would suggest that the duplication issue needs to be reolved with the community on Portuguese Wikipedia before making any changes to the Wikidata item. I hope that helps! Jason.nlw (talk) 16:07, 14 July 2023 (UTC)

Listing the DOIs for items missing the "published in" property

I'd like to add the published in (P1433) property for items. If possible, may I get a query that returns a list of items that have a DOI (P356), but are missing published in (P1433)?

If that list has too many items to run successfully, can I get a query that lists items that starts with "10.3301/IJG"; please include the DOI (P356) and published in (P1433). Disregard the alternative request; I figured out how to resolve the 10.3301/IJG items.

Eventually, I will run the DOI through the SourceMD to attempt to add the published in (P1433) property.

Thank you. Trilotat (talk) 20:24, 15 July 2023 (UTC)

filtering by occupation (P106) = researcher (Q1650915)

@Fnielsen:, thank you for generating this query for me recently. I neglected to thank you and time got away. Can I ask you to edit it so it lists only researchers? Thank you in advance. Trilotat (talk) 00:34, 12 July 2023 (UTC) 

SELECT 
  ?human ?humanLabel
  ?orcid
  ?article
WITH {
  SELECT 
    ?human ?orcid ?article  
  WHERE { 
    ?human wdt:P31 wd:Q5 ;
           wdt:P496 ?orcid ;
           ^schema:about ?article .
    ?article schema:inLanguage "en" .
  }
  LIMIT 100  # Change if more humans should be returned
} AS %result
WHERE {
  INCLUDE %result
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }        
}
Try it!

Trilotat (talk) 00:34, 12 July 2023 (UTC)

@Trilotat: I am sorry I do not remember the original question. It is possible to use FILTER on the occupation. However, when a person has an ORCID that would usually mean that it is a researcher, so I think that those listed with your query above are only researchers. I see that there might be double-listings, because a researcher may have an entry in English Wikipedia, Wikimedia Commons, English Wikiquote, Wikispecies or other English language wiki. — Finn Årup Nielsen (fnielsen) (talk) 20:46, 16 July 2023 (UTC) 

SELECT 
  ?human ?humanLabel
  ?orcid
  ?article
WITH {
  SELECT 
    ?human ?orcid ?article  
  WHERE { 
    ?human wdt:P31 wd:Q5 ;
           wdt:P496 ?orcid ;
           ^schema:about ?article .
    ?article schema:inLanguage "en" .
  }
  LIMIT 100  # Change if more humans should be returned
} AS %humans
WITH {
  SELECT 
    ?human ?orcid ?article  
  WHERE {
    INCLUDE %humans
    FILTER EXISTS { ?human wdt:P106 / wdt:P279* wd:Q1650915 }
  }
} AS %result
WHERE {
  INCLUDE %result
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }        
}
Try it!

You may also want to take a look at https://synia.toolforge.org/#orcid — Finn Årup Nielsen (fnielsen) (talk) 20:46, 16 July 2023 (UTC)

Awards and research prices: Number of person entries and wikipedia articles

Hi there,

I have written a SPARQL that gives me all awards (Q618779) and research prizes (Q11448906) and counts the number of person entries per award/prize and the number of wikipedia articles. However, as far as I can see, my final list has only awards/research prizes with at least one person with an entry of having received these awards (P166).

https://w.wiki/72p7

Has someone an idea, of how to correct this? I would like to have also awards/prices with no person entries in the list. Best regards and thanks in advance! Richirikken (talk) 11:52, 13 July 2023 (UTC)

Hello @Richirikken,
I think this is because you start from people who have an "awards received" property. If someone has not received any award, they won't have this property at all since it would have no statements. So you're doing your GROUP BY on a list of people with at least 1 award.
I'm not sure why you would be interested in awards that have no recipients (which would be quite strange - would such awards even be notable enough to warrant a Wikidata item?), but I've tried to write a query that does the opposite, starting from all existing awards and then finding their recipients, drawing inspiration from this - but I can't get it to work and I don't know if I'm going in the right direction: https://w.wiki/747n Vincent Tep (talk) 13:54, 16 July 2023 (UTC)
Hello Vincent,
Thanks, that is already helpful. We want to have a base list of all awards. If an award has no winner in the wikidata, it just means that the wikidata entries are incomplete, I think.
You are right in pointing out the reason for my "error", but I cannot find the solution. I'll try to work with your script. Hovewer, if someone can help I would be very happy.
Best regards, --Richirikken (talk) 05:05, 17 July 2023 (UTC)

I managed t write a SPARQL that gives mit all awards and research prices and counting the number of wikipedia articles. 

SELECT DISTINCT ?item (COUNT(DISTINCT ?article) as ?wiki_article_count)   WHERE {
  { ?item p:P31 ?statement0.
    ?statement0 (ps:P31) wd:Q618779.}
  UNION
  {?item p:P31 ?statement1.
    ?statement1 (ps:P31) wd:Q11448906.}
           OPTIONAL {
          ?article schema:about ?item .
       } SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY  ?item
Try it!

Now I would need to integrate the number of award/price winners in wikidata (including awards/research prices with no winner). Can someone help? Richirikken (talk) 05:34, 17 July 2023 (UTC)

Numbers of main subject statements for a group of items

Hi, I'm trying to get the numbers of main subject main subject (P921) statements for items in the NZ Thesis Project NZThesisProject (Q111645234), e.g. 5000 theses have 1 statement, 10,000 theses have 2 statements, etc. Thanks. DrThneed (talk) 21:44, 16 July 2023 (UTC)

@DrThneed: Here it is with an exploding link Easter Egg: [1]. If you want to visualise your OG query, use line chart view. --99of9 (talk) 06:13, 17 July 2023 (UTC)
That's brilliant, thanks @99of9 just what I needed! DrThneed (talk) 09:29, 17 July 2023 (UTC)

Returning deprecated values

Hello. Would someone be able to show me how to return the values for deprecated statements. I'm able to return them in the query below however I cant figure out how to display the actual value (return the actual ID). Any help would be much appreciated! Query Jason.nlw (talk) 15:57, 14 July 2023 (UTC)

Hello @Jason.nlw,
Is this what you want? https://w.wiki/74y7 Vincent Tep (talk) 15:50, 18 July 2023 (UTC)
Perfect! Thanks for your help. Jason.nlw (talk) 08:33, 19 July 2023 (UTC)

Items with the property "is part of"

I would like to query items with the property "is part of" and the value "Federal inventory of fenlands of national importance". What is the appropriate query code? Thank for your help. Matutinho 18:49, 18 July 2023 (UTC)

Here's your query : https://w.wiki/7544
Thank you, can you please show me the code? --Matutinho 20:28, 18 July 2023 (UTC)
I've found the code, sorry. I'm beginner. Matutinho 21:05, 18 July 2023 (UTC)

species

Can I have species with the most sitelinks yet not in English Wikipedia?

115.188.159.190 21:42, 1 July 2023 (UTC)

Here's a query that should do what you ask, but unfortunately it times out as Wikidata knows about so many species. Many someone else here can work out how to get it to finish? MartinPoulter (talk) 16:12, 3 July 2023 (UTC)
@MartinPoulter: would it work if it only includes species with a IUCN conservation status (P141) 115.188.159.190 05:32, 20 July 2023 (UTC)
SELECT ?item ?links WHERE {
  ?item wdt:P105 wd:Q7432 . # Taxon rank "species"
  MINUS{?enwp schema:about ?item; schema:isPartOf <https://en.wikipedia.org/>} # not on English Wikipedia
  ?item wikibase:sitelinks ?links # count sitelinks
} ORDER BY DESC(?links) LIMIT 50
Try it!

Identifier shared with

I still have problems with queries that include qualifiers. I would like to create a maintenance list for Template:P227:

Examples of items that should be displayed:

Any help appreciated. --Kolja21 (talk) 22:21, 22 July 2023 (UTC)

@Kolja21: I'm not sure to understand the request, but here is my try:
SELECT DISTINCT ?item ?itemLabel ?id ?named ?shared ?sharedLabel  WHERE {
  ?item p:P227 ?statement.
  ?item wdt:P227 ?id.
  ?statement pq:P1810 ?named.
  ?statement pq:P4070 ?shared.
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de,en" . 
  }
}
Try it!
It only shows items where the P227 statement has both P1810 and P4070 qualifiers. Depending on what you want you could include one of them in OPTIONAL or use each one in its own query.--Pere prlpz (talk) 17:33, 25 July 2023 (UTC)
Thanks, that seems to be the way to go ("shows items where the P227 statement has P4070 qualifier") but there are still problems. 1. I've tried it with Wikidata Query Service: Lexical error at line 1, column 6. Encountered: "=" (61), after : "query". 2. With Wikidata list (Property talk:P227/Identifier shared with): There was an error running the query [Field 'message' doesn't have a default value]. --Kolja21 (talk) 21:17, 25 July 2023 (UTC)
@Kolja21: Sorry. Template fixed. And the same result in https://w.wiki/77mL --Pere prlpz (talk) 21:29, 25 July 2023 (UTC)
Perfect! Thanks --Kolja21 (talk) 21:32, 25 July 2023 (UTC)

Articles in cawiki without interwikis

I tried two approaches to get articles without interwikis but both of them time out:

1: Selecting items with articles in cawiki and filtering out itens with articles in other projects: 

SELECT ?item ?articlecat WHERE {
   ?articlecat schema:about ?item.
  ?articlecat schema:isPartOf <https://ca.wikipedia.org/>.
  MINUS {
   ?article schema:about ?item.
    MINUS {?article schema:isPartOf <https://ca.wikipedia.org/>.}
  }
}
Try it!

2: Selecting items with articles in cawiki and counting how many interwikis they have: 

SELECT ?item (COUNT(DISTINCT(?article)) AS ?totiw) 
  WITH {SELECT ?item  WHERE {
   ?articlecat schema:about ?item.
  ?articlecat schema:isPartOf <https://ca.wikipedia.org/>.
} } AS %1
WHERE {
  INCLUDE %1.
   ?article schema:about ?item.
}
GROUP BY ?item
Try it!

Surprisingly, I can't find any post related to such a problem, although it seems unlikely that I'm the first to search articles without interwikis.

Any suggestion or optimization will be welcome. Pere prlpz (talk) 12:05, 25 July 2023 (UTC)

You can use precalculated wikibase:sitelinks and filtered it to 1 – it also contains sister projects, but for this purpose it will be ok (You already know it's part of cawiki, so this is The One ;) ):
SELECT ?item ?articlecat {
  ?articlecat schema:about ?item;
              schema:isPartOf <https://ca.wikipedia.org/>.
  ?item wikibase:sitelinks ?sitelinks .
  FILTER (?sitelinks = 1)
}
Try it!
įmho GROUPING/HAVING should do work, but for me it is also too slow in this form – if line for checking if this is wikipedia group is on:
SELECT ?item ?articlecat (COUNT(?articleall) AS ?allarticles_count) {
  ?articlecat schema:about ?item;
              schema:isPartOf <https://ca.wikipedia.org/>.
  ?articleall schema:about ?item;
              schema:isPartOf/wikibase:wikiGroup "wikipedia".
}
GROUP BY ?item ?articlecat
HAVING (?allarticles_count = 1)
Try it!
regards, Piastu (talk) 15:09, 25 July 2023 (UTC)
@Piastu: The first one works fine and I learned something new from both. Thank you.--Pere prlpz (talk) 15:15, 25 July 2023 (UTC)
@Pere prlpz: After consideration, I'm not sure the first (I don't know how accurate results do You need) – for example result will not contain some biography with wikiquote, and without wikipedia interwikis – for that it will be good to speed up the second one. Piastu (talk) 15:26, 25 July 2023 (UTC)
@Piastu: Since the goal is have a tool to search for interwikis for the articles lacking them, I think the first one will be enough. I don't expect a large number of such exceptions to exist. The query as it is, is already a big improvement when compared to the previous tools.--Pere prlpz (talk) 15:33, 25 July 2023 (UTC)

Humans with a WorldCat Identities ID (superseded) but no WorldCat Entities

I am new to SPARQL. I am trying to write something like below but that does not time out. 

#instances of humans with a WorldCat Identities ID (superseded) but no WorldCat Entities ID
SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P31 wd:Q5. #instance of (P31) human (Q5)
  ?item wdt:P7859 [] #with a WorldCat Identities ID (superseded) (P7859) 
  MINUS { ?item wdt:P10832 [] } . #without a WorldCat Entities ID (P10832) 
  
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 5
Try it!

I would appreciate any guidance. Peaceray (talk) 05:54, 20 July 2023 (UTC)

Hello @Peaceray,
This query shows there are 1.92m items with a P7859: https://w.wiki/78Ku
I wrote a query that retrieves these items then leaves out those with a P10832 and finally leaves out any item that isn't a human, but it takes too long and fails. I have to use LIMIT for it to work.
Thankfully @Tagishsimon suggested another way: https://w.wiki/78Kx this method uses the slice service and works for 500k-item chunks; I suggest you run it 4 times and download the results each time, that should do the trick. Vincent Tep (talk) 19:53, 26 July 2023 (UTC)
I will have a look. Thanks! Peaceray (talk) 20:46, 26 July 2023 (UTC)

Henrik Ibsen

May I have a query showing all items related to Henrik Ibsen (Q36661). I know it is a tricky one, but trying to find a way to get a list of items for Wikidata:WikiProject Henrik Ibsen. @CIstudies: for info Pmt (talk) 17:27, 21 July 2023 (UTC)

@Pmt: If "all items related to Henrik Ibsen (Q36661)" means items with any statement using Henrik Ibsen (Q36661), you don't even need a query: Special:WhatLinksHere/Q36661. You may want to restrict the output to the main namespace to get only items: https://www.wikidata.org/wiki/Special:WhatLinksHere?target=Q36661&namespace=0 --Pere prlpz (talk) 11:55, 25 July 2023 (UTC)

@Pere prlpz: Thank you for your answer. Of course, easy as that! Also @CIstudies: for info. Breg Pmt (talk) 12:39, 26 July 2023 (UTC)

Only 1 alias

Hello, given a certain field, eg railway station (Q55488), find items that have only one alias in fralias and have frlabel, fralias, enlabel, delabel ? Bouzinac 💬●✒️●💛 07:56, 1 July 2023 (UTC)

Hello @Bouzinac,
I *think* this does what you want. Returned items have only 1 fr alias AND they have labels in fr AND en AND de. Is this what you wanted?
# find items that have:
# 1 - only one alias in fralias 
# AND
# 2 - labels in en AND fr AND de
SELECT
?item
(COUNT(?aliases) AS ?aliases_count)
WHERE {
  ?item wdt:P31 wd:Q55488 .
  ?item skos:altLabel ?aliases .   # this gets the *aliases*
  BIND(LANG(?aliases) AS ?aliases_lg)
  { 
 SELECT  ?item  (COUNT(?labels_lg) AS ?labels_lg_count)
  WHERE {
    VALUES ?lg {"fr" "en" "de"}
    ?item wdt:P31 wd:Q55488 .
    ?item rdfs:label ?labels .  # this gets the *labels*
    BIND(LANG(?labels) AS ?labels_lg)
#     SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    FILTER(?labels_lg IN (?lg)) }
  GROUP BY ?item
  HAVING(?labels_lg_count = 3)
}
#   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  FILTER(?aliases_lg = "fr")
 }
GROUP BY (?item)
HAVING(?aliases_count = 1)
Try it!
(how do I prevent these semicolons from appearing? Thanks) Vincent Tep (talk) 12:43, 12 July 2023 (UTC)
Hello, not sure if it fulfills the job : I wished for every frlabel only one fralias. And possibly enlabel, delabel. Sorry if it wasn't clear. I tried to change your query but was not successful... Bouzinac 💬●✒️●💛 20:01, 18 July 2023 (UTC)
Hello Bouzinac, I'm not sure how I can help you. You can write me your requirements in French if you like! Vincent Tep (talk) 19:42, 24 July 2023 (UTC)
On va essayer ^^ : j'aimerais, pour faire simple, lister pour les stations de métro, leur fr-alias fr + leur fr-label, à condition qu'il n'y existe qu'un seul fr-alias (une seule valeur quoi). En option, l'éventuel en-label, de-label. Bouzinac 💬●✒️●💛 21:04, 30 July 2023 (UTC)

a list of all items with a the TabakaleraID+ genre+birthdate

Hi! I am trying to get a list with all items with a Tabakalera ID+genre and birthdate. I think that I am very close. Could you help me? I am new with SPARQL 

#instances of humans with a Tabakalera Identities ID (P10069)
SELECT ?item ?itemLabel ?genre ?birthdate
WHERE 
{
  ?item wdt:P31 wd:Q5. #instance of (P31) human (Q5).
  ?item wdt:P10069 ?TabakaleraID. #with a Tabakalera Identities ID (P10069)
        ?genre wdt:P21 ?genre.
        ?birthdate wdt:P569 ?birthdate.
  
  
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 100
Try it!

Lmerice (talk) 11:28, 20 July 2023 (UTC)

@Lmerice: Yes, you are close:
#instances of humans with a Tabakalera Identities ID (P10069)
SELECT ?item ?itemLabel ?genre ?genreLabel ?birthdate
WHERE 
{
  ?item wdt:P31 wd:Q5. #instance of (P31) human (Q5).
  ?item wdt:P10069 ?TabakaleraID. #with a Tabakalera Identities ID (P10069)
        ?item wdt:P21 ?genre.
        ?item wdt:P569 ?birthdate. 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
Please notice that this query only returns items which have both sex or gender (P21) and date of birth (P569). If you also want items without any of them, you may use OPTIONAL clauses.--Pere prlpz (talk) 21:35, 25 July 2023 (UTC)
Thanks @Pere prlpz! It makes me!! Lmerice (talk) 17:28, 27 July 2023 (UTC)

Requesting a query for people who own organisations in Russia

Hi, I am having trouble with Sparql syntax where I want to traverse links. Please can you provide me with a query for instances of Human who are owner of instances of organisation where the organisation is located in Russia. The query should return the personlabel, the organisationlabel and optionally the organisations geographic coordinates. Thank you! 82.16.160.198 21:28, 31 July 2023 (UTC)

Retrieved from "https://www.wikidata.org/w/index.php?title=Wikidata:Request_a_query/Archive/2023/07&oldid=1954352728"