Wikidata:Request a query/Archive/2019/12

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

How to list a range of Q-items?

Is this possible. I'm trying to catch (timeout) a range of Q's before and after 4626 AM (Q16128701) to curate them with the right P31.--So9q (talk) 22:06, 23 November 2019 (UTC)

I could generate this myself in a spreadsheet and use in a values block perhaps?--So9q (talk) 10:17, 24 November 2019 (UTC)

It worked! Is there a way to generate a range of Q-items dynamically say a special word RANGE(1, 100) that would result in a list of wd:-prefixed Q-items? If not I think we should open a ticket for this, because it is really useful to be able to do to limit queries to a certain range of Q-items.--So9q (talk) 11:36, 24 November 2019 (UTC)

I tried with 5000 in the range and the WDQS UI freeze all the time making it almost impossible to type in the SPARQL window. I think this is related to auto completion which should be possible to turn of (as it acts strangely on mobile devices also). This is bug I think.--So9q (talk) 11:46, 24 November 2019 (UTC)

SPARQL doesn't afaik have a concept of range, beyond creative use of FILTER to establish them; but I get the impression that's not very efficient. I can imagine a bespoke WDQS service which provides better support for QId ranges (and other matters - append/edit dates & ranges). I doubt such a thing will be commissioned, but by all means open a ticket, since it's even less likely to be built if the suggestion is not made.

Not sure about the UI bug; again, worth raising, if there's for instance a client process that could be knocked on the head for super-large queries. --Tagishsimon (talk) 20:44, 24 November 2019 (UTC)

On some SPARQL systems (but, I think, not ours) there is full string indexing of all strings and URLs, so a strategy would be to find all URLs that were Q-ids, and CONTAINED the initial digits of the range you were looking for after their Q-number. FILTER could then be applied to this set to reduce it to the range that you were looking for.

Full indexing of strings was not implemented on our system, because (i) it is a perfrmance overhead, and (ii) it was considered that such string searching could be achieved through the ElasticSearch API.

Nevertheless, STRSTARTS still works better than one might expect (I think -- see Lucas's tips on range-searching in the optimisation page for more. But perhaps it only applies to numbers/dates). So that might be one strategy that could be investigated. Alternatively, one could investogate whether the string search API could be used to extract a block of Qids, that could then be filtered. Jheald (talk) 22:57, 24 November 2019 (UTC)

Where is the optimisation page? What about filtering on the dump file?

wget https://dumps.wikimedia.org/wikidatawiki/entities/latest-truthy.nt.gz && zcat latest-truthy.nt.gz | wikidata-filter

Or a simpler sed that works on the (much bigger) json newline devided dump:

wget https://dumps.wikimedia.org/wikidatawiki/entities/latest-all.json.gz && zcat latest-all.json.gz | sed -e '/nifty expression/' > output.json.gz

--So9q (talk) 19:47, 27 November 2019 (UTC)

@So9q: Wikidata:SPARQL query service/query optimization --Tagishsimon (talk) 21:23, 27 November 2019 (UTC)

Thanks :)--So9q (talk) 21:54, 27 November 2019 (UTC)

Went ahead and created a ticket for the UI freeze bug: https://phabricator.wikimedia.org/T239385--So9q (talk) 07:28, 28 November 2019 (UTC)

@So9q: I couldn’t find an efficient way to generate a range of items – but item IDs aren’t supposed to be significant anyways. And since you were limiting the results to those with a specific description anyways, it’s possible to efficiently query for all of those items with this description, without any range restriction:

SELECT ?item WHERE {
  ?item schema:description "Hebrew calendar year"@en;
        wdt:P155 ?follows.
  FILTER NOT EXISTS { ?item wdt:P31 ?instanceOf. }
}

Try it!

– does that help? (Alternatively, one thing you could do efficiently is filter by the last modification date of the item, which is available in schema:dateModified. But that’s the only bit of the history that’s available – you can’t get the item creation time like that.) --TweetsFactsAndQueries (talk) 16:38, 1 December 2019 (UTC)

Big thanks! It had only one result so I guess I succeded in my quest. I'm a little surprised it did not time out, but that is a good thing.--So9q (talk) 18:23, 1 December 2019 (UTC)

People of a certain type with the closest connections to a particular person

I would like to request a SPARQL query that allows me to find the people of a certain type with the closest connections to a particular person (counting only parents, children, spouses, and siblings). Miraclepine (talk) 22:19, 1 December 2019 (UTC)

@Miraclepine: Perhaps something like this, to start. It's a bidirectional relationship, so there's information to be had from (up to) two items for each relationship. Here's Robbie Burns, whose item doesn't seem to record the fact of one of his children. With more work this could be coalesced into a more simple list.

SELECT distinct ?item ?itemLabel ?item2 ?item2Label ?relation_to_subjectLabel ?relation_to_objectLabel
WHERE 
{
  VALUES ?item {wd:Q81960}
 { ?item2 wdt:P26| wdt:P40 | wdt:P3373 | wdt:P22 | wdt:P25 ?item .
    ?item2 ?propertyRel ?item .  
   ?relation_to_subject wikibase:directClaim ?propertyRel.            
}
  union 
 { ?item wdt:P26 | wdt:P40 | wdt:P3373 | wdt:P22 | wdt:P25 ?item2 .
   ?item ?propertyRel ?item2 .  
   ?relation_to_object wikibase:directClaim ?propertyRel.            
 }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 22:50, 1 December 2019 (UTC)

@Tagishsimon: I tried Queen Victoria who, unlike Burns, has a lot of relatives, descendants, and ancestors, but it only gives me everyone who is one relationship (sibling/parent/child/spouse) removed, and displays most of them twice. I'd like it to go beyond the first degree and to display only the relationship in the direction away from the search target. Miraclepine (talk) 22:59, 1 December 2019 (UTC)

Query for last name

• Items used: Douglas Adams (Q42)   , family name (Q101352)   

• Properties used: instance of (P31)   

SELECT *
WITH
{
    SELECT ?item ?name ?l 
    {
        BIND( wd:Q42 as ?item) 
        ?item rdfs:label ?l . 
        FILTER( lang(?l) = "en" )
        # BIND(strafter(?l, " ") as ?name)
        BIND(REPLACE(?l, "^.+ (.+)$", "$1") as ?name)      
    }
}  as %a
WHERE
{
  INCLUDE %a 
  OPTIONAL { ?lastname wdt:P31 wd:Q101352 ; rdfs:label ?name }
}

Try it!

Is there an easy way to speed this up? I tried a couple of ways with more items, but they tend to timeout or the optional part not even matching. --- Jura 10:17, 2 December 2019 (UTC)

Actually, it's much easier to highlight when it gets slow:

SELECT *
WHERE
{
  BIND("Douglas Adams"@en as ?name1)  BIND(strafter(?name1, " ") as ?name)  #slow
  # BIND( strlang("Adams", "en") as ?name)                                  #slow
  # BIND("Adams"@en as ?name)                                               #fast
  OPTIONAL { ?lastname wdt:P31 wd:Q101352 ; rdfs:label ?name }
}

Try it!

Compare the first two binds with the last bind. Actual times can very a lot from one name to the other. Is there a way to ensure the first two are just as quick? --- Jura 13:04, 2 December 2019 (UTC)

Multiple ID values, but not counting deprecated values

Please can we have a version of https://w.wiki/D9v that excludes deprecated ID values? Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 18:41, 2 December 2019 (UTC)

@Pigsonthewing: Try https://w.wiki/D9x

That query will exclude Richard Bowdler Sharpe (Q432586) who has a lot of deprecated ID values, but only one that is not deprecated.

--Larske (talk) 19:00, 2 December 2019 (UTC)

Q

Is there a way to auto create an item by scanning selected wikis (specifically en, sr, zh, zh-min-nan & fa)? For example, en, sr and zh has Template:Taxonomy/A but there is no item link for it, is there a way to make a query so the item can be "auto-created"? Secondly, if the item already exists but the page on a specific wiki, lets say zh is not linked, is it possible to use the item label that is already available to scan a wiki for the page to be added into the item? 大诺史 (Talk/留言/토론/Discussion) 06:22, 29 November 2019 (UTC)

@大诺史: The most obvious thing to point you at is petscan (manual) which can generate lists of Wikipedia (and related projects) pages, such as all pages in a certain category tree, and compare them with wikidata - do they have an item; and if they do not, is there not item which has the same label (i.e. identify articles for which items can be created automatically) and if there is (and using a linked tool called Duplicity) show the list of possibly matching items allowing the user to choose which one to sitelink to.

There's an array of such petscans at https://en.wikipedia.org/wiki/Wikipedia:WikiProject_Women_in_Red/Metrics/Wikidata which you might have a poke around to better familiarise yourself. It helps to be logged in via Oath when using Petscan - look for a login button allowing you to do so.

I'm not very sure of Petscan's ability to look across multiple language wikipedias at once, or so compare one with another. It may be that it is for the most part "limited" to working in a single wikipedi, or comparing that wikipedia with wikidata. It's a remarkable tool. --Tagishsimon (talk) 08:28, 29 November 2019 (UTC)

@Tagishsimon: To be honest, after reading the manual and the en page, I still have no idea how to use PetScan. 大诺史 (Talk/留言/토론/Discussion) 08:55, 29 November 2019 (UTC)

@大诺史: Very understandable. Let me take you through one example, to start, Consider this petscan. First, notice that petscan has tabbed pages - Categories, Page properties, Templates&links, etc. In the categories tab, we see that Language is set to en; Project to Wikipedia; and the categories field has 'living people' in it. So that tells petscan to look at all the article in https://en.wikipedia.org/wiki/Category:Living_people . Next place to look is the Wikidata tab, where the setting "Only pages without item (enables Wikidata item creation mode)" is checked. That says: only show articles where there is no wikidata item, i.e. the article has not been linked to an item.

If you hit the "Do it" button, after a few minutes (it's doing a lot of work), you'll get a list of the articles in that category with no wikidata link. Some of them will have a tick in a blue-background box against them: that means that there is not an item in wikidata with a label that matches the article title. The rest have no tick; that means there is an item with the same label value as the article title. To the right of each name on the list, there's a 'check wikidata' link, which invokes an application called Duplicity, which will (often very slowly) show the article in question, a list of wikidata items that might match the item, and the details of a highlighted wikidata item, and which provides buttons allowing you to link the article to a selected wikidata item. Note also the "Log into WiDaR to perform Wikidata edits" ... in short, you should do this so as to enable editing under your control through Petscan and Duplicity ... so for instance, the buttons Duplcity gives you will not work if you are not logged in; and you get an additional box in Petscan results which allows you to select a set of articles and create items with a specified list of property values.

Obvs, this is but one example of Petscan in action; and in this example, all of the other petscan fields and settings were left at their default values.

So, next example: this petscan which finds all articles in https://en.wikipedia.org/wiki/Category:Women and in any of its subcategories to a depth of 5 levels (the Depth parameter in the categories tab). In this one, if we check out the Wikidata tab, it has 'Only pages with item' checked and a value of P21 in 'Uses items/props', with, to the right, 'None' checked. Together, these say "the article must be linked to an item, but the item must not have a P21 property on it". And then there's one more step: in the 'Other sources' tab, in the SPARQL field, there's a little SPARQL query: select ?item where {?item wdt:P31 wd:Q5} which requires that any of the articles selected by the Petscan query must be linked to an item which has a P31 value of Q5. So overall, the report finds articles in Category:Women plus subcats, where there is an item, the item is an instance of Human (P31=Q5), but the item lacks a Gender property (P21 is null).

I hope that quick guided tour gives you a bit more insight. It's well worth bashing your head against Petscan until you grok it, if you want to compile the sorts of lists your question indicates. It's capable of thousands of variations on a basic theme of 'inspect articles in a category'. Feel free to bring Petscan questions here if you want more input; I think this board is a wide enough church to stray from SPARQL into Petscan. --Tagishsimon (talk) 16:05, 30 November 2019 (UTC)

What a wonderful introduction! Maybe we should augment the manual with these examples? --So9q (talk) 20:42, 30 November 2019 (UTC)

I think that would be welcome - perhaps as a supplement to the exising manual? But I've never felt enough on an expert on Petscan to presume to do so ... I'm sure I'm barely scratching the surface in my understanding of it. There are whole areas of related tooling - like pagepile - on which I don't have a grasp. Obvs, I'd benefit from some sort of extended manual. --Tagishsimon (talk) 21:14, 30 November 2019 (UTC)

@Tagishsimon: Thank you for your explanation! I've tried with articles in the main space and it worked. However, it doesn't seem to work with templates. 大诺史 (Talk/留言/토론/Discussion) 04:52, 6 December 2019 (UTC)

Automatically update Wikidata page

I am trying to figure out how to automatically update a Wikidata page that is missing data from the Wikidata Query Service? For example, if I am trying to pull the start and end dates for a particular venue (Denver Broncos) but it does not have them listed on the Wikidata page yet, is there a way to write a statement to update those start and end dates from the Query Service and then pull those dates back? Do I need to use another service for this? Thank you  – The preceding unsigned comment was added by 216.116.232.243 (talk • contribs) at 3 december 2019 at 02.36‎ (UTC).

Such data can be extracted from wikidata if it exists, which is not the case for Denver Broncos and Sports Authority Field at Mile High. Updating a wikipedia page direct from wikidata (presuming we're talking about e.g. in an infobox or within the article text, but excluding the Wikidata:Listeria approach) requires the language wikipedia to have appropriate templates (such as en:Template:Wikidata) and to permit articles to be updated in this fashion. Generally there'll be a family of such templates for different occasions - e.g. en:Category:Wikidata_templates. --Tagishsimon (talk) 03:03, 3 December 2019 (UTC)

Wikidata Query Service is a tool to get (read) data already in Wikidata, so when it comes to the question how to update (write) a Wikidata page (=a Wikidata object?), I think you have to look for other tools. (If Wikidata SPARQL supports some kind of UPDATE I would also like to learn about that.)

Anyway, here is an addition to the answer given above, although I think it is an answer to a different question.

If the values you want to get from Wikidata are properties (and not qualifiers) you can get the value(s) by just using the magic word #property on the Wikipedia page, like this:

• To get the inception (P571) for Denver Broncos (Q223507), write like this:

{{#property:P571|from=Q223507}} → 1960

• To get the date of official opening (P1619) for Empower Field at Mile High (Q1046135), write like this:

{{#property:P1619|from=Q1046135}} → 10 September 2001

If the object is linked to the Wikipedia article where you want to have this information, you don't have to include the "from" part.

--Larske (talk) 12:38, 4 December 2019 (UTC)

Need help fixing up an old query I request here a couple of years ago

Back in April 2017 I asked for help creating a query that generates a list of Wikivoyage articles sorted by the amount of articles that exists for each destination in all Wikivoyage editions.

The user Pasleim then created this amazing query.

SELECT ?item (count(*) as ?cnt) WHERE {
    ?article schema:about ?item;
             schema:isPartOf/wikibase:wikiGroup "wikivoyage".
} GROUP BY ?item ORDER BY DESC(?cnt)
LIMIT 4000

Try it!

If possible, please help me upgrade it so that the list would include not only the wikidata id (for example Q5859), but also the Wikidata name and description, so that it would be much easier to go over the entire list fast. ויקיג'אנקי (talk) 21:34, 6 December 2019 (UTC)

Also, if possible please fix the code so that the list generated would only show instances in which there isn't an article in the Hebrew Wikivoyage. ויקיג'אנקי (talk) 21:38, 6 December 2019 (UTC)

Pulling back Q codes by IPEDs ID

I am trying to write a query to pull back the Q code for colleges/universities by their IPEDs ID. I want it to be as if I do not know what the colleges Q code is but it will pull back all the Qcodes for the colleges that I have an IPEDs ID for but am unsure how to do this. The code I have below does not do what I want for my purposes but it's just an example of terms I am using to pull back the information. Can anyone help me? Thank you!

SELECT ?college ?teamUnitID ?teamQCode WHERE { VALUES ?college {wd:Q61930514 } OPTIONAL {?college wdt:P1771 ?teamUnitID.} OPTIONAL {?college wdt:P115 ?teamQCode.} SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } }

 – The preceding unsigned comment was added by 216.116.232.243 (talk • contribs) at 8 december 2019 at 15.40‎ (UTC).

Does the following query give you the expected result?

SELECT ?college_IPED_ID ?college ?collegeLabel ?college_Q_code WHERE {
 ?college wdt:P1771 ?college_IPED_ID. 
 BIND(STRAFTER(STR(?college),'entity/') AS ?college_Q_code )
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY ?college_IPED_ID

Try it!

--Larske (talk) 17:54, 8 December 2019 (UTC)

Yes, this is great! Do you also know how to then pull back the same information but by a specific IPEDS ID (for example if I wanted to pull back the college name and Q code for the IPED ID 100636)?

 – The preceding unsigned comment was added by 216.116.232.243 (talk • contribs) at 8 december 2019 at 19.18‎ (UTC).

Just add the following line to the query:

VALUES ?college_IPED_ID { '100636' }

like this:

SELECT ?college_IPED_ID ?college ?collegeLabel ?college_Q_code WHERE {
 VALUES ?college_IPED_ID { '100636' }
 ?college wdt:P1771 ?college_IPED_ID. 
 BIND(STRAFTER(STR(?college),'entity/') AS ?college_Q_code )
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY ?college_IPED_ID

Try it!

--Larske (talk) 23:15, 8 December 2019 (UTC)

Overcoming timeout

#defaultView:LineChart
# Scholarly articles as a function of gender
SELECT ?age (COUNT(?gender) as ?count) (SAMPLE(?genderLabel) AS ?genderLabel ) WHERE {
  ?article wdt:P31 wd:Q13442814 .
  ?article wdt:P577 ?date .
  ?article wdt:P50 ?author .
  ?author wdt:P569 ?dob .
  ?author wdt:P21 ?gender .
  ?gender rdfs:label ?genderLabel .
  FILTER((LANG(?genderLabel)) = "en")
  bind( 1000+year(?date)-year(?dob) as ?age_num )
  bind( SUBSTR(str(?age_num),1,4) as ?age )
  FILTER(year(?date)>=2000)
  FILTER(BOUND(?age_num))
  FILTER(?age_num<=1100)
  FILTER(?age_num>=1000)
}
GROUP BY ?genderLabel ?age

Try it!

This query (one of 99of9's used to work. I am hoping it can be made to work again. Thanks, MargaretRDonald (talk) 22:01, 6 December 2019 (UTC)

@MargaretRDonald: It seems more than 40 percent of Wikidata consists of items that are instance of (P31) scholarly article (Q13442814) - almost 30 million objects! So it is not much you can do with all these objects in just 60 seconds, just 2 microseconds per object.

--Larske (talk) 14:52, 7 December 2019 (UTC)

Hi MargaretRDonald - I love that query too. I'd be interested to know what you're using it for. Here's a version that works even with the massive scale of publication data in Wikidata now. You can reduce the LIMIT 300000 if you start getting timeouts.

#defaultView:LineChart
SELECT
	?age ?count ?genderLabel
WHERE
{
	{ SELECT ?gender ?age (count(*) as ?count) WHERE {
		{ SELECT ?gender ?age WHERE {
  
         ?author p:P569/psv:P569 ?dob_node . # node for date of birth
         ?dob_node wikibase:timePrecision "11"^^xsd:integer . # date precision = day
         ?dob_node wikibase:timeValue ?dob . 
         ?author wdt:P21 ?gender .
         ?article wdt:P50 ?author .  
         ?article wdt:P31 wd:Q13442814 .
         ?article wdt:P577 ?date .

        FILTER(year(?date)>=2000)

        bind( 1000+year(?date)-year(?dob) as ?age_num )
        bind( SUBSTR(str(?age_num),1,4) as ?age )
  
        FILTER(BOUND(?age_num))
        FILTER(?age_num<=1100)
        FILTER(?age_num>=1000)
        }
         LIMIT 300000
        }
	} GROUP BY ?gender ?age  }
	SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

--99of9 (talk) 06:42, 9 December 2019 (UTC) Thanks @Larske: and @99of9: (I wasn't using it for anything except to demonstrate the power of these 3 component linked databases...) And yes it is a great query. MargaretRDonald (talk) 08:08, 9 December 2019 (UTC)

Writing to Wikidata

Does anyone know how to write a GET/POST request to write to Wikidata from the Wikidata Query Service in order to update information on a Wikidata page? I am wanting to be able to add in a new entry to a Wikidata page if there is information missing through the Wikidata Query Service. Thank you!  – The preceding unsigned comment was added by 216.116.232.243 (talk • contribs) at 19:00, 8 December 2019‎ (UTC).

You cannot write to Wikidata via WDQS, it is a read-only interface. However, there are plenty of Wikidata:Tools available that allow you to edit Wikidata via scripts (some rules apply). QuickStatements is the tool which is used most to automate editing. WDQS will then be fed internally with your edits. —MisterSynergy (talk) 20:28, 9 December 2019 (UTC)

finding redirected wikidata items used by SDC

I am trying to find cases where wikidata items stored in Structured data on Commons (SDC) point to redirected items which need updating. I was hoping to run something like:

SELECT ?commonsItem ?wikidataItem ?target
WHERE 
{
  ?commonsItem wdt:P6243 ?wikidataItem.
  ?wikidataItem owl:sameAs ?target.
}
limit 100

Try it!

on the new SDC query service. but it does not work. Closer look shows that the query

SELECT ?wikidataItem ?target
WHERE 
{
  VALUES ?wikidataItem { wd:Q57935507 }
  OPTIONAL { ?wikidataItem owl:sameAs ?target. }
}

Try it!

run on standard Wikidata query service finds that Monkey with violets. (Q57935507) redirects to other item. Similar query run on new SDC query service:

SELECT ?commonsItem ?wikidataItem ?target
WHERE 
{
  #VALUES ?commonsItem { sdc:M50692878 }
  VALUES ?wikidataItem { wd:Q57935507 }
  ?commonsItem wdt:P6243 ?wikidataItem.
  OPTIONAL { ?wikidataItem owl:sameAs ?target. }
}

Try it!

does not find redirects. Any ideas how to perform my query? --Jarekt (talk) 20:05, 9 December 2019 (UTC)

--Jarekt (talk) 20:05, 9 December 2019 (UTC)

End date with precision limited to year (and not day+month)

Hello I've come across this query and for a specific case use [BIND(wd:Q4337360 AS ?siège)], the result are disappointing because of this :

• if a date has only year level precision, the calculations will assume it will be at January 1st.
• For a start date, OK, makes sense.
• However, when it comes to an end time (P582), it's confusing. Is there a way for P582 to behave as if it was December 31th ?

select ?siège ?siègeLabel (sample(?whereLabel) as ?whereLabel2) ?start ?end ?days {
SELECT ?siège ?siègeLabel ?whereLabel ?start ?end ?days WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
?siège wdt:P31 wd:Q188055.
 BIND(wd:Q4337360 AS ?siège)
OPTIONAL { ?siège wdt:P580 ?start. }
OPTIONAL { ?siège wdt:P582 ?end. }
OPTIONAL { ?siège wdt:P276 ?where. } 
OPTIONAL { ?siège p:P2047/psn:P2047/wikibase:quantityAmount ?lengthNorm.}
BIND(COALESCE(?lengthNorm/86400,(?end - ?start+1)) as ?days)

}
}
group by ?siège ?siègeLabel ?start ?end ?days
order by desc(year(?end)) ?whereLabel2

Try it!

Bouzinac (talk) 20:18, 5 December 2019 (UTC)

@Bouzinac: In the following modified query the end time (P582) value used for days calculation is changed to 31 December if the time precision is "year" (9):

select ?siège ?siègeLabel (sample(?whereLabel) as ?whereLabel2) ?start ?end ?days {
SELECT ?siège ?siègeLabel ?whereLabel ?start ?end ?days WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
?siège wdt:P31 wd:Q188055.
 BIND(wd:Q4337360 AS ?siège)
OPTIONAL { ?siège wdt:P580 ?start. }
OPTIONAL { 
           ?siège p:P582/psv:P582 ?p582node . 
           ?p582node wikibase:timeValue ?tv .       # get the time value
           ?p582node wikibase:timePrecision ?tp .   # get the time precision
           BIND(IF(?tp=9,xsd:dateTime(REPLACE(STR(?tv),"-01-01","-12-31")),?tv) AS ?end) # set end date to 31 December if time precision is "year" (9)
         }
OPTIONAL { ?siège wdt:P276 ?where. } 
OPTIONAL { ?siège p:P2047/psn:P2047/wikibase:quantityAmount ?lengthNorm.}
BIND(COALESCE(?lengthNorm/86400,(?end - ?start+1)) as ?days)
}
}
group by ?siège ?siègeLabel ?start ?end ?days
order by desc(year(?end)) ?whereLabel2

Try it!

--Larske (talk) 23:21, 5 December 2019 (UTC)

• Items used: Siege of Zierikzee (Q2139412)   , Siege of Autun (Q585291)   , Battle of Mainz (Q840250)   , Siege of Bensheim (Q815122)   , siege (Q188055)   , day (Q573)   

• Properties used: instance of (P31)   , Wikidata time precision (P2803)   , start time (P580)   , end time (P582)   , conversion to standard unit (P2442)   

SELECT ?item ?itemLabel ?start ?t1 ?end ?t2 ?startprecision ?endprecision ?precision ?duration ?tp ?conv ?converted 
{
    {  SELECT *
       WHERE
       {
          #  VALUES ?item { wd:Q2139412 wd:Q585291 wd:Q840250 wd:Q815122 }
           ?item wdt:P31 wd:Q188055 .
           ?item p:P580/psv:P580 [ wikibase:timeValue ?start ; wikibase:timePrecision ?startprecision ] .
           ?item p:P582/psv:P582 [ wikibase:timeValue ?end ; wikibase:timePrecision ?endprecision ]
       }
       LIMIT 200
    }
    #determine lowest precision
    BIND( IF( ?startprecision > ?endprecision , ?endprecision , ?startprecision ) as ?precision) 
    #change dates to lowest precision (here: only year precision)
    BIND( IF( ?startprecision < 10, xsd:dateTime( CONCAT( str(YEAR(?start)),"-01-01T00:00:00Z")), ?start)  as ?t1) 
    BIND( IF( ?endprecision < 10, xsd:dateTime( CONCAT( str(YEAR(?end)),"-12-31T00:00:00Z")), ?end)  as ?t2) 
    #calc duration on adjusted dates (in days)
    BIND( ?t2 - ?t1 as ?duration)                  
    #get unit corresponding to precision and conversion factor from days
    ?tp wdt:P2803 ?pr . FILTER( str(?pr) = str(?precision) )
    OPTIONAL { ?tp p:P2442 / psv:P2442 [ wikibase:quantityAmount ?conv ; wikibase:quantityUnit wd:Q573 ] .
             ?tp rdfs:label ?unit . FILTER(lang(?unit) = "en") 
             } 
    # convert from days to unit corresponding to precision, add "ca." and unit label
    BIND( CONCAT( "ca. ", str(ROUND(FLOOR((1 / ?conv * ?duration) +1))), " ", ?unit ) as ?converted) 
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
}

Try it!

See also Topic:Vccde0jydludhi2l. --- Jura 13:15, 6 December 2019 (UTC)

Hi there, thank you Larske, I tried to rewrite your query thinking it was more simpler + need to handle the problem of end months but still works not ; am I wrong in the writing and calculations with xsd:duration stuff ? :

select ?siège ?siègeLabel (sample(?whereLabel) as ?whereLabel2) ?start ?tp_end ?tv_end ?end ?days {
SELECT ?siège ?siègeLabel ?whereLabel ?start ?tp_end ?tv_end ?end ?days WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
?siège wdt:P31 wd:Q188055.
OPTIONAL { ?siège wdt:P580 ?start. }
OPTIONAL { 
           ?siège p:P582/psv:P582 ?p582node . 
           ?p582node wikibase:timeValue ?tv_end .       # get the time value
           ?p582node wikibase:timePrecision ?tp_end .   # get the time precision
           values ?durationYear { "P1Y.000S"^^xsd:duration }
           values ?durationMonth { "P1M.000S"^^xsd:duration }
           values ?durationDay { "P1D.000S"^^xsd:duration }
           BIND(
             IF(?tp_end=9,?tv_end + ?durationYear - ?durationDay,# set end date to 31 December if time precision is "year" (9)
             IF(?tp_end=10,?tv_end + ?durationMonth - ?durationDay,# set end date to 28th if time precision is "month" (10)
             ?tv_end))
              AS ?end).
         }
OPTIONAL { ?siège wdt:P276 ?where. } 
OPTIONAL { ?siège p:P2047/psn:P2047/wikibase:quantityAmount ?lengthNorm.}
BIND(COALESCE(?lengthNorm/86400,(?end - ?start+1)) as ?days)
}
}
group by ?siège ?siègeLabel ?start ?tp_end ?tv_end ?end ?days
order by desc(year(?end)) ?whereLabel2

Try it!

Bouzinac (talk) 22:22, 8 December 2019 (UTC)

Here's a good solution(préfix + xsd:duration ), I think

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

select ?siège ?siègeLabel (sample(?whereLabel) as ?whereLabel2) ?start ?tp_end ?tv_end ?end ?days {
SELECT ?siège ?siègeLabel ?whereLabel ?start ?tp_end ?tv_end ?end ?days WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
?siège wdt:P31 wd:Q188055.
OPTIONAL { ?siège wdt:P580 ?start. }
OPTIONAL { 
           ?siège p:P582/psv:P582 ?p582node . 
           ?p582node wikibase:timeValue ?tv_end .       # get the time value
           ?p582node wikibase:timePrecision ?tp_end .   # get the time precision
           BIND(
             IF(?tp_end=9,?tv_end + "P1Y"^^xsd:duration - "P1D"^^xsd:duration,# set end date to 31 December if time precision is "year" (9)
             IF(?tp_end=10,?tv_end + "P1M"^^xsd:duration - "P1D"^^xsd:duration,# set end date to 28th if time precision is "month" (10)
             ?tv_end))
              AS ?end).
         }
OPTIONAL { ?siège wdt:P276 ?where. } 
OPTIONAL { ?siège p:P2047/psn:P2047/wikibase:quantityAmount ?lengthNorm.}
BIND(COALESCE(?lengthNorm/86400,(?end - ?start+1)) as ?days)
}
}
group by ?siège ?siègeLabel ?start ?tp_end ?tv_end ?end ?days
order by ?whereLabel2 desc(year(?end))

Try it!

Bouzinac (talk) 12:19, 13 December 2019 (UTC)

Stats on the number of identifiers for a given collection

Hi!

I want to track our efforts with Identifiers in Wikidata:WikiProject Video games. I already track how many items have no identifiers at all ; but I would like to get, basically, histograms bins: out of 40K items, X have 0 ids, Y have between 1 and 5, and Z have more than 10 (I’ll adjust the bins later :))

I tried hard, but I’m really not sure how to do that in SPARQL

That’s how far I got for the counts for every item:

SELECT ?entity (COUNT(DISTINCT(?property)) as ?count) WHERE {
  ?entity wdt:P31 wd:Q7889.
  OPTIONAL {
    ?property rdf:type wikibase:Property;
      wikibase:propertyType wikibase:ExternalId;
      (wdt:P31/(wdt:P279*)) wd:Q28147643;
      wikibase:directClaim ?wdt.
    ?entity ?wdt ?id.
  }
}
GROUP BY ?entity

Try it!

Thanks! Jean-Fred (talk) 17:59, 11 December 2019 (UTC)

(I would also be ok without binning, with output for every count − X items have 1 ID, Y have 2, Z have 3, and so on for every count that exists Jean-Fred (talk) 18:01, 11 December 2019 (UTC)).

@Jean-Frédéric: Does the following meet your expectations?

#defaultView:LineChart   
SELECT ?count (COUNT(?entity) AS ?entities) WITH {
SELECT ?entity (COUNT(DISTINCT(?property)) as ?count) WHERE {
  ?entity wdt:P31 wd:Q7889.
  OPTIONAL {
    ?property rdf:type wikibase:Property;
      wikibase:propertyType wikibase:ExternalId;
      (wdt:P31/(wdt:P279*)) wd:Q28147643;
      wikibase:directClaim ?wdt .
    ?entity ?wdt [] .
  }
}
GROUP BY ?entity
} AS %i WHERE {
  include %i
}
GROUP BY ?count
ORDER BY ?count

Try it!

Try changing the view to BarChart in the top left corner of the output if you prefer that format. By view in Table format you get the numbers.

--Larske (talk) 20:37, 11 December 2019 (UTC)

@Larske: Awesome, thanks! I had a hunch for subqueries but could not figure out − much appreciated :) I threw this into Wikidata:WikiProject Video games/Maintenance/Identifiers count to keep history of it going forward.

(If someone can figure out how to do bins, that would be the icing on the cake, but I’m happy with that already :) Jean-Fred (talk) 10:58, 12 December 2019 (UTC)

Fishers that have an article on the French Wikipedia

I'd like someone to buid a query that would list every human being that has fisher (Q331432) listed as one of his occupations (Property:P106 and maybe also Property:P101) on Wikidata and that has an article on the French Wikipedia in order for me to list these individuals in the category fishers that I recently created on the French project. Thanks in advance.--Kimdime (talk) 11:15, 13 December 2019 (UTC)

@Kimdime: Yeah, that's not happening. Zero results. If you comment out (#) the three lines below the currently commented out line; and uncomment that line, you'll get a successful query for dancers, demonstrating that the query works. So zero results means no item meets those conditions.

SELECT DISTINCT ?item ?itemLabel ?sitelink ?article (group_concat(distinct ?occupationL;separator="; ") as ?occupationLabel) (group_concat(distinct ?fieldL;separator="; ") as ?fieldLabel) WHERE 
{
#  ?item wdt:P106 wd:Q5716684. 
 {?item wdt:P106 wd:Q331432.}
  UNION
 {?item wdt:P101 wd:Q331432.}
 OPTIONAL {?item wdt:P106 ?occupation.}
 OPTIONAL {?item wdt:P101 ?field.}
 ?sitelink ^schema:name ?article .
 ?article schema:about ?item ;
          schema:isPartOf <https://fr.wikipedia.org/> .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
                         ?item rdfs:label ?itemLabel .
                         ?occupation  rdfs:label ?occupationL .
                         ?field rdfs:label ?fieldL .}
} group by ?item ?itemLabel ?sitelink ?article

Try it!

--Tagishsimon (talk) 12:17, 13 December 2019 (UTC)

Tagishsimon Thanks a lot ! I'm a bit puzzled. Why Q3294791 doesn't appear in the results ? He is listed as a fisher on wd and has an entry on the French WP.--Kimdime (talk) 12:41, 13 December 2019 (UTC)

@Kimdime: Ah yes; that. Because I'm an idiot. Query above now fixed :) --Tagishsimon (talk) 12:57, 13 December 2019 (UTC)

@Kimdime: And so here's a bonus query - fishers & subclasses of fishers - more results:

SELECT distinct ?item ?itemLabel ?sitelink ?article (group_concat(distinct ?occupationL;separator="; ") as ?occupationLabel) (group_concat(distinct ?fieldL;separator="; ") as ?fieldLabel) WHERE 
{
 {?item wdt:P106/wdt:P279* wd:Q331432.}
  UNION
 {?item wdt:P101/wdt:P279* wd:Q331432.}
 OPTIONAL {?item wdt:P106 ?occupation.}
 OPTIONAL {?item wdt:P101 ?field.}
 ?sitelink ^schema:name ?article .
 ?article schema:about ?item ;
          schema:isPartOf <https://fr.wikipedia.org/> .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
                         ?item rdfs:label ?itemLabel .
                         ?occupation  rdfs:label ?occupationL .
                         ?field rdfs:label ?fieldL .}
} group by ?item ?itemLabel ?sitelink ?article

Try it!

--Tagishsimon (talk) 12:59, 13 December 2019 (UTC)

Tagishsimon Excellent ! Would it be possible to generate a list of fishers & subclasses of fishers that do not have an article on the French Wikipedia sortable by number of interwikis ?--Kimdime (talk) 15:19, 13 December 2019 (UTC)

@Kimdime: Assuredly yes; below. There's also need to do a petscan report showing articles in the category that do not have a P106 of fisher. I'll do that later.

SELECT distinct ?item ?itemLabel ?linkcount (group_concat(distinct ?occupationL;separator="; ") as ?occupationLabel) (group_concat(distinct ?fieldL;separator="; ") as ?fieldLabel) WHERE 
{
 {?item wdt:P106/wdt:P279* wd:Q331432.}
  UNION
 {?item wdt:P101/wdt:P279* wd:Q331432.}
 OPTIONAL {?item wdt:P106 ?occupation.}
 OPTIONAL {?item wdt:P101 ?field.}
 FILTER NOT EXISTS {?sitelink ^schema:name ?article .
 ?article schema:about ?item ;
          schema:isPartOf <https://fr.wikipedia.org/> .}
  ?item wikibase:sitelinks ?linkcount .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
                         ?item rdfs:label ?itemLabel .
                         ?occupation  rdfs:label ?occupationL .
                         ?field rdfs:label ?fieldL .}
} group by ?item ?itemLabel ?sitelink ?article ?linkcount order by desc(?linkcount)

Try it!

--Tagishsimon (talk) 17:19, 13 December 2019 (UTC)

Problem with query

Hello. I use this query User:Xaris333/Communities of Cyprus in Greek Wikipedia w:el:Κατάλογος κοινοτήτων της Κυπριακής Δημοκρατίας. Listeriabot update so often the list but actually there is nothing to update. Can anyone find the problem? Xaris333 (talk) 09:19, 14 December 2019 (UTC)

potential issues

Hello. I want to find all items with:

• occupation (P106) -> politician (Q82955)
• country of citizenship (P27) -> Cyprus (Q229)

that have any potential issues (constraints). Xaris333 (talk) 09:44, 14 December 2019 (UTC)

Trouble with preferred values/normal rank values

Hello, I've got two problems : - this query works fine on the duration formulas but deduplicates item having preferred+normal end dates. See for example Siege of Corfu (Q11794876)

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
SELECT ?siège ?siègeLabel (sample(?whereLabel) as ?whereLabel2) ?start ?tp_end ?tv_end ?end ?days {
SELECT ?siège ?siègeLabel ?whereLabel ?start ?tp_end ?tv_end ?end ?days WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
?siège wdt:P31 wd:Q188055.
#BIND(wd:Q11794876 AS ?siège) #use this filter to see the problem of preferred rank
OPTIONAL { ?siège wdt:P580 ?start. }
OPTIONAL { 
           ?siège p:P582/psv:P582 ?p582node . 
           ?p582node wikibase:timeValue ?tv_end .       # get the time value
           ?p582node wikibase:timePrecision ?tp_end .   # get the time precision
           BIND(
             IF(?tp_end=9,?tv_end + "P1Y"^^xsd:duration - "P1D"^^xsd:duration,# set end date to 31 December if time precision is "year" (9)
             IF(?tp_end=10,?tv_end + "P1M"^^xsd:duration - "P1D"^^xsd:duration,# set end date to 28th if time precision is "month" (10)
             ?tv_end))
              AS ?end).
         }
OPTIONAL { ?siège wdt:P276  ?where. } 
OPTIONAL { ?siège p:P2047/psn:P2047/wikibase:quantityAmount ?lengthNorm.}
BIND(COALESCE(?lengthNorm/86400,(?end - ?start+1)) as ?days)
}
}
group by ?siège ?siègeLabel ?start ?tp_end ?tv_end  ?end ?days
order by ?whereLabel2 desc(year(?end))

Try it!

- this other query works on case Siege of Corfu (Q11794876) but duration formula no longer works on many cases.

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
SELECT ?siège ?siègeLabel (sample(?whereLabel) as ?whereLabel2) ?start ?tp_end ?tv_end ?end ?days {
SELECT ?siège ?siègeLabel ?whereLabel ?start ?tp_end ?tv_end ?end ?days WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
?siège wdt:P31 wd:Q188055.
#BIND(wd:Q11794876 AS ?siège) #use this filter to see the problem of preferred rank
OPTIONAL { ?siège wdt:P580 ?start. }
OPTIONAL { 
           ?siège wikibase:rank wikibase:PreferredRank ; p:P582/psv:P582 ?p582node . 
           ?p582node wikibase:timeValue ?tv_end .       # get the time value
           ?p582node wikibase:timePrecision ?tp_end .   # get the time precision
           BIND(
             IF(?tp_end=9,?tv_end + "P1Y"^^xsd:duration - "P1D"^^xsd:duration,# set end date to 31 December if time precision is "year" (9)
             IF(?tp_end=10,?tv_end + "P1M"^^xsd:duration - "P1D"^^xsd:duration,# set end date to 28th if time precision is "month" (10)
             ?tv_end))
              AS ?end).
         }
OPTIONAL { ?siège wdt:P276  ?where. } 
OPTIONAL { ?siège p:P2047/psn:P2047/wikibase:quantityAmount ?lengthNorm.}
BIND(COALESCE(?lengthNorm/86400,(?end - ?start+1)) as ?days)
}
}
group by ?siège ?siègeLabel ?start ?tp_end ?tv_end  ?end ?days
order by ?whereLabel2 desc(year(?end))

Try it!

So how to get only one end date and make calculations on this end date?

Thanks, Bouzinac (talk) 09:50, 14 December 2019 (UTC)

@Bouzinac: To get only best rank statements, you have to break some property paths to get the statement in some variable. The best ranks statements (preferred ranks if there is some, or normal if there is not) for a property in an item are rdf-instances of « wikibase:BestRank », so I added a ?déclaration_siège a wikibase:BestRank in your first query after retrieving the statement in the corresponding variable. This fixes not everything probably, but at least the problem you highlighted :

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
SELECT ?siège ?siègeLabel (sample(?whereLabel) as ?whereLabel2) ?start ?tp_end ?tv_end ?end ?days {
SELECT ?siège ?siègeLabel ?whereLabel ?start ?tp_end ?tv_end ?end ?days WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
?siège wdt:P31 wd:Q188055.
BIND(wd:Q11794876 AS ?siège) #use this filter to see the problem of preferred rank
OPTIONAL { ?siège wdt:P580 ?start. }
OPTIONAL { 
           ?siège p:P582 ?déclaration_siège .
           ?déclaration_siège psv:P582 ?p582node 
                              ; a wikibase:BestRank 
                              .
           ?p582node wikibase:timeValue ?tv_end .       # get the time value
           ?p582node wikibase:timePrecision ?tp_end .   # get the time precision
           BIND(
             IF(?tp_end=9,?tv_end + "P1Y"^^xsd:duration - "P1D"^^xsd:duration,# set end date to 31 December if time precision is "year" (9)
             IF(?tp_end=10,?tv_end + "P1M"^^xsd:duration - "P1D"^^xsd:duration,# set end date to 28th if time precision is "month" (10)
             ?tv_end))
              AS ?end).
         }
OPTIONAL { ?siège wdt:P276  ?where. } 
OPTIONAL { ?siège p:P2047/psn:P2047/wikibase:quantityAmount ?lengthNorm.}
BIND(COALESCE(?lengthNorm/86400,(?end - ?start+1)) as ?days)
}
}
group by ?siège ?siègeLabel ?start ?tp_end ?tv_end  ?end ?days
order by ?whereLabel2 desc(year(?end))

Try it!

Note that to avoid some duplications it’s best, if you need to use the full statement form with « p: », to avoid using « wdt: » in parralel. The wdt values are retrievable in the statement itself. author  TomT0m / talk page 10:30, 14 December 2019 (UTC)

Thanks TomT0m, I'm buying it! Bouzinac (talk) 16:17, 14 December 2019 (UTC)

Disappointing query :(

Hi, I'm a beginner in SPARQL so I'm trying very basic queries.

Here is the one I created. The idea is to get a list of every archives (or subclass of) located in France. To my disappointment many are missing, such as Q182542, Q856640, Q2860545 although they are marked as archives or subclass of archives and are marked as located in France. So what's wrong here ? Thanks in advance.--Kimdime (talk) 16:28, 14 December 2019 (UTC)

SELECT DISTINCT ?item ?itemLabel WHERE {
  ?item (wdt:P31/wdt:P279) wd:Q166118; 
 wdt:P17 wd:Q142.                        
  OPTIONAL {  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
}

Try it!

@Kimdime:

Replace wdt:P31/wdt:P279 with wdt:P31/wdt:P279*

--Larske (talk) 16:48, 14 December 2019 (UTC)

@Larske: TXS !--Kimdime (talk) 18:11, 14 December 2019 (UTC)

A list of every personality which have a personal page in the french wikipedia with their date of birth

Hi, I'm looking for an Excel file with in the A colomn "every personality which have a personal page in the french wikipedia" and in the B colomn "date of birth" (in the european format).

I have absolutely zero knowledge in this Query stuff, a help would be much appreciated !

How do you define a personality? A human (Q5)? Ainali (talk) 20:25, 14 December 2019 (UTC)

Thanks for your answer, and yes exactly, I mean every human being who has his/her own wikipedia page.

European trips

Hello, Can you tell me if/where I can find the trips, in European country, of Prime Minister of Israel (Q208487) or President of Israel (Q327948), between 1974 and 2020? enwiki of Category:Israeli prime ministerial visits (Q65626007) is far from complete. Visite fortuitement prolongée (talk) 18:21, 15 December 2019 (UTC)

Articles not in "Deaths in year" articles

I'm planning to improve lists of deaths for year/month in enwiki and I need:

• One query that gives me all the enwiki articles for people who died in a certain year but are not listed in enwiki's list of deaths for that particular year; and
• one query that gives me all the enwiki articles for people who died in a certain month/year combo but are not listed in enwiki's list of deaths for that particular month/year combo.

Miraclepine (talk) 21:25, 15 December 2019 (UTC)

@Miraclepine: With PetScan you can get a list of enwp articles connected to a Wikidata object with date of death (P570) in a certain year (YYYY), while not belonging to the corresponding enwp category YYYY deaths.

Here is an example for year 2019, right now there are 265 articles in the list.

• Link to query (Note: The query takes some 45 seconds to run)

To get a list for another year, change the category under Categories and the FILTER under Other sources and click the Do it! button.

--Larske (talk) 23:26, 15 December 2019 (UTC)

@Larske: Not what I asked for but thanks. How do I do it for a particular month/year combo (ex. December 2019)? Miraclepine (talk) 23:43, 15 December 2019 (UTC)

@Miraclepine: With PetScan you can get a list of enwp articles connected to a Wikidata object with date of death (P570) in a certain year/month combo (YYYY-MM), while not linked from the corresponding enwp list article Deaths in MMMM YYYY.

Here is an example for the year and month 2019-11, right now there are 22 articles in the list.

• Link to query

To get a list for another year/month, change the "Linked from" under Templates&links and the FILTER under Other sources and click the Do it! button.

--Larske (talk) 00:21, 16 December 2019 (UTC)

And here is a query for enwp articles connected to a Wikidata object with date of death (P570) in 2019, while not linked from any of the twelve list articles Deaths in MMMM 2019.

• Link to query

Right now there are 748 articles in the list.

--Larske (talk) 00:33, 16 December 2019 (UTC)

A query to find lexemes

I work on my graduation work and can't fix my query:

SELECT ?l ?lemma ?myPartLabel ?genderLabel ?word (GROUP_CONCAT(DISTINCT ?grammaticalFeature; SEPARATOR=', ') AS ?lem)
WHERE {
  ?l a ontolex:LexicalEntry ; dct:language ?language ;
    wikibase:lexicalCategory ?myPart ;
    wikibase:lemma ?lemma ;
    ontolex:lexicalForm ?form .
    values ?myPart { wd:Q1084 wd:Q34698 wd:Q24905 wd:Q380057}
  ?form ontolex:representation ?word ;
    wikibase:grammaticalFeature ?grammaticalFeature .
  ?language wdt:P218 'ru'.
  optional { ?l wdt:P5185 ?gender. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
} GROUP BY ?l ?lemma ?myPartLabel ?genderLabel ?word ?lem

Try it!

it works too long (I've got "Query timeout limit reached"). As I found out it works slowly due to nouns. Can you help me to get all data from my query? --Raccoon-sw (talk) 08:15, 16 December 2019 (UTC)

To specify, the problem is to get all features of forms (<1mln) of Russian nouns . Even without labels and with limit the query times out:

SELECT ?l ?lemma ?word (GROUP_CONCAT(DISTINCT ?grammaticalFeature; SEPARATOR=', ') AS ?lem)
WHERE {
  ?l a ontolex:LexicalEntry ; dct:language ?language ;
    wikibase:lexicalCategory wd:Q1084 ;
    wikibase:lemma ?lemma ;
    ontolex:lexicalForm ?form .
  ?form ontolex:representation ?word ;
    wikibase:grammaticalFeature ?grammaticalFeature .
  ?language wdt:P218 'ru'.
#  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
} GROUP BY ?l ?lemma ?word ?lem
LIMIT 10

Try it!

How to optimize it? --Infovarius (talk) 15:08, 16 December 2019 (UTC)

Find Wikidata items for a list of persons with known biographical data

For an academic project we want to find the Wikidata items for a list of about 1,500 artists. The following SPARQL query does the job for one artist:

# find the Wikidata item for a person with given family name, date of birth and date of death
SELECT ?artist ?artistLabel ?dob ?dod WHERE {
  VALUES ?artistName {
    "Mechlen"
  }
  ?artist wdt:P569 ?dob;
    wdt:P570 ?dod;
    rdfs:label ?artistLabel.
    FILTER((LANG(?artistLabel)) = "de")
    FILTER(CONTAINS(?artistLabel, ?artistName))
    FILTER(("1888-01-01"^^xsd:dateTime <= ?dob) && (?dob < "1889-01-01"^^xsd:dateTime))
    FILTER(("1961-01-01"^^xsd:dateTime <= ?dod) && (?dod < "1962-01-01"^^xsd:dateTime))
}

Try it!

We have to use the label, not family name (P734), because it's the only 'attribute' available for all existing items (P734 is missing in many cases).

Given the long list of artists we want to process (with a JavaScript tool), I have the following questions:

• Is there a more efficient/elegant solution for this query?
• Can all parameters (also dob, dod) be bound to VALUES?
• If so, is it possible to write a query that handles a list of artists with their corresponding dob/dod so we could run batch requests for say 20 artists at a time?--MichaelMBerlin (talk) 07:40, 16 December 2019 (UTC)

Two more artists with Wikidata item: Becker, 1904-1972 (--> Q1145456), Kahler, 1882-1911 (--> Q1235151), and without: Benkert, 1896-1941 (Lotte Benkert, no entry, mentioned in de.wikipedia.org)

A kind query request

I would like to ask your help to build few queries. I would like to get…

• language stats of items which are subclass of audiovisual work (Q2431196) and have IMDb ID (P345) based on original language of film or TV show (P364). I would like to get the bubble charts of the biggest languages; for example, Ender's Game (Q1758112) should be counted as one for English
• line chart of audiovisual work (Q2431196) and written work (Q47461344) items by date of publication (with two different lines, if it is possible)
• image grid of smartphone model (Q19723451) items with a date option (released before/after a specific date)
• image (logo) grid of items with higher Alexa rank (P1661) than 100 (TOP 100 website)

I would be very thankful for your help! Regards, Bencemac (talk) 16:58, 14 December 2019 (UTC)

@Máté: Esetleg tudnál segíteni? Későn gondoltam rá, hogy még ma kezdenem kellene ezekkel valamit. Előre is köszi a választ! Bencemac (talk) 07:57, 15 December 2019 (UTC)

Az első ez lenne, de nekem eléri az időkorlátot:

#defaultView:BubbleChart
SELECT ?lang (count(?work) as ?count) WHERE { ?work wdt:P31/wdt:P279* wd:Q2431196; wdt:P345 []; wdt:P364 [rdfs:label ?lang] FILTER (lang(?lang) = "hu") } GROUP BY ?lang

Try it!

Viszont a mögötte lévő adatokat eléred, ha nem próbálod a címkét is mellétenni:

SELECT ?lang (count(?work) as ?count) WHERE { ?work wdt:P31/wdt:P279* wd:Q2431196; wdt:P345 []; wdt:P364 ?lang } GROUP BY ?lang

Try it!

Jön a többi is. Máté (talk) 08:35, 15 December 2019 (UTC)

Ez a negyedik:

#defaultView:ImageGrid
SELECT ?logo ?item WHERE { ?item wdt:P154 ?logo; wdt:P1661 ?rank } HAVING (?rank < 101) ORDER BY ASC(?rank)

Try it!

Ebbe belecsúfít, hogy nem minden elemnél van preferred rangra álítva a legfrissebb érték, azokat javítani kell. (Illetve ha több logó van egy elemen, akkor most mindet hozza.) Máté (talk) 08:46, 15 December 2019 (UTC)

A harmadikat kiegészítettem a smartphone model series (Q71266741) elemekkel is. Itt vannak az ideiek képei:

#defaultView:ImageGrid
SELECT DISTINCT ?image ?item WHERE { {?item wdt:P31 wd:Q19723451} UNION {?item wdt:P31 wd:Q71266741} . ?item wdt:P18 ?image; wdt:P577|wdt:P571 ?date FILTER (?date >= "2019-01-01T00:00:00Z"^^xsd:dateTime) }

Try it!

Máté (talk) 09:04, 15 December 2019 (UTC)

@Máté: Köszönöm szépen a segítséget, jövök eggyel! Bencemac (talk) 15:47, 15 December 2019 (UTC)

I think that this discussion is resolved and can be archived. If you disagree, don't hesitate to replace this template with your comment. Bencemac (talk) 14:29, 17 December 2019 (UTC)

All dates for a given item

I would like to generate a list of events for any item. It would list the statements that have dates as values or as qualifier values.

property

value

qualifier dateproperty

qualifier value

Previously, this has been tackled by filtering by date value type, but I would like to query for any subproperty of point in time (P585). – Susanna Ånäs (Susannaanas) (talk) 10:07, 17 December 2019 (UTC)

list all female employees and those who have positions at the Bank of England

Hi, I can do a query to list female employees of the Bank, but most of the people associated with the Bank of England (Q183231) are with the position held (P39). Is there a way to get a list of employees and people who have positions at the Bank? Many thanks MassiveEartha (talk) 10:53, 17 December 2019 (UTC)

Sustainable Development Goal (Q53580881) - Sustainable Development Goal Target (Q56724848) - Sustainable Development Goal Indicator (Q56726345)

Could someone help me construct a query that shows all Sustainable Development Goal (Q53580881) with each of their Sustainable Development Goal Target (Q56724848) and each of those Sustainable Development Goal Indicator (Q56726345) respectively. I would expect something like a table with 244 rows (the number of indicators) and then the other ones repeated as needed. Ainali (talk) 14:02, 14 December 2019 (UTC)

I think i figured it myself now. Here is my query:

SELECT ?goalLabel ?goal ?targetLabel ?target ?indicatorLabel ?indicator WHERE {
  ?indicator wdt:P31 wd:Q56726345;
             wdt:P1269 ?target .
  ?target wdt:P1269 ?goal.
  ?goal p:P361 ?part.
  ?part ps:P361 wd:Q7649586;
        pq:P1545 ?order .
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . 
  }
}
ORDER BY ASC (xsd:integer(?order))

Try it!

Ainali (talk) 17:51, 20 December 2019 (UTC)

Member of the French National Assembly but only those who belong to the 13th legislature of the Fifth French Republic

I spent hours trying to do it !

The only approaching code I achieved to do is the following :

SELECT ?DeputeLabel ?LegislatureLabel WHERE {

 ?Depute wdt:P31 wd:Q5.
 ?Depute wdt:P39 wd:Q3044918.
 ?Legislature wdt:P361 wd:Q200686.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }

}

I think this does "Member of the French National Assembly but only those who belong to a Parliamentary term which belong to the Fifth French Republic" but I'm not even sure because I obtained 220140 results and it seems a bit too much.

I think the "Parliamentary term" is a subcategory of "Position held" and I don't know how to query that ...

 – The preceding unsigned comment was added by NoelKiwi (talk • contribs) at 17 december 2019 at 13.35‎ (UTC).

@NoelKiwi: The following query will give you 504 results where I also added start time (P580) and end time (P582).

SELECT ?Depute ?DeputeLabel ?LegislatureLabel ?p580 ?p582 WHERE {
 VALUES ?Legislature { wd:Q3570394 }
 ?Depute wdt:P31 wd:Q5.
 ?Depute p:P39 ?p39stm . 
 ?p39stm ps:P39 wd:Q3044918.
 ?p39stm pq:P2937 ?Legislature .
 OPTIONAL { ?p39stm pq:P580 ?p580 }
 OPTIONAL { ?p39stm pq:P582 ?p582 }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY ?DeputeLabel

Try it!

--Larske (talk) 07:49, 18 December 2019 (UTC)

Request a query for Battles in history and the generals who won it

Please i have have been trying to write a SPARQL query to extract data about military battles in history from wikidata.

would like to have data in the following columns;

I.year and duration of battle I.name and description of battle II.countries or parties involved III.notable generals involved IV.the military general who won the battle ++(Date of birth and death of the Military general, sex) v.Number of people that died in the battle

i would be glad if you held me to generate the Sparql code for the above

I don't think we have data for most of that, most of the time. Most remarkably, I don't think we record the victor of battles. Here's a starter report, which groups countries and people.

SELECT ?item ?itemLabel (group_concat(distinct ?countryLabel;separator="; ") as ?countries) (group_concat(distinct ?personLabel;separator="; ") as ?people) ?start ?end ?point_in_time
WHERE 
{
  ?item wdt:P31 wd:Q178561 .
  optional {?item wdt:P710 ?country . filter not exists {?country wdt:P31 wd:Q5. } }
  optional {?item wdt:P710 ?person . ?person wdt:P31 wd:Q5 . }
  optional {?item wdt:P580 ?start . }
  optional {?item wdt:P580 ?end . }
  optional {?item wdt:P585 ?point_in_time . }
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
                         ?country rdfs:label ?countryLabel .
                         ?person rdfs:label ?personLabel .
                         ?item rdfs:label ?itemLabel . 
                         ?gender rdfs:label ?genderLabel .}
} group by ?item ?itemLabel ?start ?end ?point_in_time

Try it!

And here a report which shows biographical details of the people.

SELECT ?item ?itemLabel  ?personLabel ?genderLabel ?dob ?dod
WHERE 
{
  ?item wdt:P31 wd:Q178561 .
  optional {?item wdt:P710 ?person . ?person wdt:P31 wd:Q5 . 
    optional {?person wdt:P21 ?gender.}
    optional {?person wdt:P569 ?dob.}
    optional {?person wdt:P570 ?dod.}
} 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
                         ?person rdfs:label ?personLabel .
                         ?item rdfs:label ?itemLabel . 
                         ?gender rdfs:label ?genderLabel .}
}

Try it!

Others may be able to improve from the above bases, notably if they can find a victor property; and if they can solve the who is on whose side issue ... the 'participant' property doesn't obviously suggest that (though perhaps there's a qualifier that I'm missing) --Tagishsimon (talk) 02:27, 21 December 2019 (UTC)

Time out

The following query runs in about 20 seconds:

It looks for all Scottish items that have a P131 to a Scottish council area (Q15060255), and also to a Scottish civil parish (Q5124673) or large burgh (Q6489113) or small burgh (Q7543008), and returns any with a council area that cannot be 'explained' by any of the parishes or burghs:

SELECT ?hs ?item ?itemLabel ?council_area ?council_areaLabel ?parish ?parishLabel (GROUP_CONCAT(?ca2_label; separator=' / ') AS ?ca2List) ?coords WHERE {
  VALUES ?loc_type {wd:Q5124673 wd:Q6489113 wd:Q7543008} .
  ?item wdt:P131 ?parish .
  ?parish wdt:P31 ?loc_type .
  ?item wdt:P131 ?council_area .
  ?council_area wdt:P31 wd:Q15060255 .
  MINUS {
    VALUES ?loc_type1 {wd:Q5124673 wd:Q6489113 wd:Q7543008}
    ?item wdt:P131 ?parish1 .
    ?parish1 wdt:P31 ?loc_type1 .
    ?parish1 wdt:P131 ?council_area
  } .
  ?parish wdt:P131 ?ca2 .
  ?ca2 wdt:P31 wd:Q15060255 .
  ?ca2 rdfs:label ?ca2_label FILTER(lang(?ca2_label) = 'en') .
  
  optional {?item wdt:P625 ?coords} . 
  optional {?item wdt:P709 ?hs} . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} GROUP BY ?item ?itemLabel ?council_area ?council_areaLabel ?parish ?parishLabel ?coords ?hs
ORDER BY ?itemLabel ?parishLabel ?ca2Label

Try it!

However, in a few cases the P131 for the council area has been given preferred ranks, so I would like to change the wdt:P131 to a p:P131/ps:P131 for the parish-or-burgh statements. But that (apparently simple?) change makes the following time out:

SELECT ?hs ?item ?itemLabel ?council_area ?council_areaLabel ?parish ?parishLabel (GROUP_CONCAT(?ca2_label; separator=' / ') AS ?ca2List) ?coords WHERE {
  VALUES ?loc_type {wd:Q5124673 wd:Q6489113 wd:Q7543008} .
  ?item p:P131/ps:P131 ?parish .
  ?parish wdt:P31 ?loc_type .
  ?item wdt:P131 ?council_area .
  ?council_area wdt:P31 wd:Q15060255 .
  MINUS {
    VALUES ?loc_type1 {wd:Q5124673 wd:Q6489113 wd:Q7543008}
    ?item p:P131/ps:P131 ?parish1 .
    ?parish1 wdt:P31 ?loc_type1 .
    ?parish1 wdt:P131 ?council_area
  } .
  ?parish wdt:P131 ?ca2 .
  ?ca2 wdt:P31 wd:Q15060255 .
  ?ca2 rdfs:label ?ca2_label FILTER(lang(?ca2_label) = 'en') .
  
  optional {?item wdt:P625 ?coords} . 
  optional {?item wdt:P709 ?hs} . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} GROUP BY ?item ?itemLabel ?council_area ?council_areaLabel ?parish ?parishLabel ?coords ?hs
ORDER BY ?itemLabel ?parishLabel ?ca2Label

Try it!

Can anyone identify why this is so much slower, and/or think of a way round it.

I tried putting the 'find-items-with-scottish-parishes' code into an initial WITH clause, which in itself only takes 2.1 seconds to run (https://w.wiki/EFy); but the full query times out just as before (https://w.wiki/EFx).

Have I made some really really simple slip-up (eg forgotten to rename a variable in one line); or can anyone see any explain any reason why such an apparently small change is causing the query to take so much longer? Thanks, Jheald (talk) 18:19, 21 December 2019 (UTC)

Query to match WD tapestries with Commons images based on Palissy ID

In Commons, there is a category c:Category:Objets monuments historiques: tapestries which lists tapestries that are historic monuments of France. These items have a template that includes each item's ID in the Palissy database of heritage furnishings.

In WD, there were several early imports of Palissy data including hundreds of tapestries, most of which do not have images.

I would like some sort of report/worklist that would take items that are (1) instance of tapestry (Q184296), (2) have a value for Palissy ID (P481), and (3) do not have an image, and provide a link to likely images for these items based on matching the Palissy ID in Commons. I expect multiple possible matches for some WD items.

Can anyone help with this? - PKM (talk) 20:34, 21 December 2019 (UTC)

@PKM: On the Wikidata side, one could start with a query like:

SELECT DISTINCT ?palissy ?item ?itemLabel ?locLabel ?adm ?admLabel WHERE {
  ?item wdt:P31 wd:Q184296 .
  ?item wdt:P481 ?palissy .
  MINUS {?item wdt:P18 []} .
  
  OPTIONAL {
    ?item wdt:P276 ?loc
  }
  OPTIONAL {
     ?item wdt:P276?/wdt:P131+ ?adm .
     ?adm wdt:P31 wd:Q6465 .
  }
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr,en". }
} ORDER BY ?admLabel ?itemLabel ?palissy

Try it!

to give the Palissy number, the Q-number, the name of the tapestry, the location of the tapestry (if available), and the name of the French department that the tapestry is in.

On the Commons side, the structure appears to be that the first level of categories is the department, the second level (if available) is the location. So my first instinct would be to start building from a Quarry query like this https://quarry.wmflabs.org/query/40780 (aiming to start with to return all second-level categories below c:Category:Objets_monuments_historiques:_tapestries, with the name of the first level and second level category), to which one could then add the filenames; you could then align the two sides in a spreadsheet (eg by hand) to produce likely matches. Unfortunately the quarry query doesn't seem to be working, and I don't know why...

Plan B would might be to use the experimental SPARQL copy of the Commons category hierarchy (which gets updated about once a week), which I'd need to look up to remember how that works. Unfortunately the filenames aren't in the SPARQL set, so it would only get us so far.

Plan C would be to try to get hold of the Palissy numbers. These are held in a template on the Commons filepage, so not immediately accessible from any database (at least, not until somebody moves it into structured data). It wouldn't be too hard to write a scraper to download the wikitext of each of the filepages, and extract the template values, though I'm a bit pushed for time at the moment. It's possible that somebody might have already written a generic extractor to do this sort of thing -- it would be incredibly useful if they had -- but I don't know if such a tool exists as yet. Jheald (talk) 21:20, 21 December 2019 (UTC)

@Jheald: thanks! This is most helpful. - PKM (talk) 21:27, 21 December 2019 (UTC)

Strange quantityUnit in P4214 property for 11 objects

There are more than 3,400 Wikidata objects having the longest observed lifespan (P4214) property. Almost all of them only have one value, year (Q577), for the wikibase:quantityUnit.

But, for 11 objects the Wikidata Query Service returns two values for the quantityUnit, both year (Q577) and publication date (P577), see query below.

SELECT ?item (COUNT(?unit) AS ?number_of_units) (GROUP_CONCAT(?unit;SEPARATOR=' and ') AS ?units) {
  ?item p:P4214 [ psv:P4214 [ wikibase:quantityUnit ?unit ] ] .
}
GROUP BY ?item
HAVING(?number_of_units>1)

Try it!

Can anyone explain this strange result? --Larske (talk) 19:50, 22 December 2019 (UTC)

@Larske: phab:T230588? --Tagishsimon (talk) 23:31, 22 December 2019 (UTC)

Thanks. --Larske (talk) 01:06, 23 December 2019 (UTC)

Not include items with P582

SELECT ?item ?label
WHERE
{
  ?item wdt:P131 wd:Q59133 .
  {?item wdt:P31 wd:Q29414133 .}
  UNION
  {?item wdt:P31 wd:Q16739079 . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Hello. I want the query not to have items that with P31 -> wd:Q29414133 or with P31 -> wd:Q16739079 have as qualifier end time (P582) with any value. Xaris333 (talk) 20:34, 23 December 2019 (UTC)

@Xaris333: Here is a query proposal:

SELECT ?item ?itemLabel WHERE {
VALUES ?p31 { wd:Q29414133 wd:Q16739079 }
  ?item wdt:P131 wd:Q59133 .
  ?item p:P31 ?p31stm .
  ?p31stm ps:P31 ?p31 .
  OPTIONAL { ?p31stm pq:P582 ?p582 }
  FILTER(!BOUND(?p582))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?itemLabel

Try it!

--Larske (talk) 02:45, 24 December 2019 (UTC)

@Larske: thanks! It's working. But I have applied it to some list. But I have problems to apply it to 2 list that have more options. They are in Greek wikipedia. w:el:Επαρχία Πάφου#Πληθυσμός, έκταση και υψόμετρο ανά δήμο/κοινότητα and w:el:Χρήστης:Xaris333/Επαρχία Πάφου/Εικόνες. Can you help? Xaris333 (talk) 13:33, 24 December 2019 (UTC)

To add images for items which dont have images

Kindly help to provide query to add images in WD by using Q. Statement for items which do not have images in WD, and images from Wiki commons. ?item wdt:P31 wd:Q34442 ; wdt:P17 wd:Q878 .--Akbarali (talk) 05:59, 24 December 2019 (UTC)

German federal ministers

I'm trying to find all German cabinet-level ministers and the starting and end times that they held a particular minister position (the same person might have served as one type of minister for two terms, and for another type for another term, which should result in three rows for that person).

SELECT ?person ?personLabel ?position ?positionLabel ?st ?et WHERE {
  ?person wdt:P39/wdt:P279* wd:Q248352.
  ?person p:P39/p:P279* ?pMinister.
  ?pMinister ps:P39/ps:P279* ?position.
  #?position wdt:P31/wdt:P279* wd:Q248352.
  optional { ?pMinister pq:P580 ?st }
  optional { ?pMinister pq:P582 ?et }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,[AUTO_LANGUAGE]". }
} order by ?person

Try it!

However, I cannot seem to find the right way to make sure that position is in fact a minister. I only filter persons down to those that have held a minister position (first statement, which is a crutch I'd like to remove), but then naturally the result list also contains other positions of that person like vice-chancellor, member of German Bundestag that I don't want to include. The commented statement with position on the left side seems to be the wrong approach, using it leads to an empty list. Thanks for any help. --37.201.29.61 02:30, 26 December 2019 (UTC)

Something like this, probably. At #1, the query is trying to get the claim node for the P39 statement, so does not need p:P279*. At #2, the query is trying to get the statement property for P39, and so again does not need the ps:P279*. At #3 - and this is where the real error crept in - the query needs to check that the ?position is a subclass of Q248352, and so it needs to be looking only at the P279 values ... there is no item that has a P31 of Q248352, and the property path wdt:P31/wdt:P279* requires that there be such a P31 (find a P31 with this value, or find a P31 that can be arrived at via a chain of 0 to n P279 connections). (Normal health warning: I'm not a SPARQL expert & am just piecing it all together as best I can, like you are ;)

SELECT ?person ?personLabel ?position ?positionLabel ?st ?et WHERE {
  #?person wdt:P39/wdt:P279* wd:Q248352.
  ?person p:P39 ?pMinister.                # 1
  ?pMinister ps:P39 ?position.             # 2
  ?position wdt:P279* wd:Q248352.          # 3
  optional { ?pMinister pq:P580 ?st }
  optional { ?pMinister pq:P582 ?et }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,[AUTO_LANGUAGE]". }
} order by ?person

Try it!

--Tagishsimon (talk) 12:26, 26 December 2019 (UTC)

Number of characters

Hi, I need a list with each episode and the number of characters (how many values of characters (P674))

SELECT ?item ?label ?numberOfCharacters WHERE {
  ?item wdt:P179 wd:Q16290.
  # some magic probably using COUNT() and wdt:P674 🤷‍♀️
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en".
    ?item rdfs:label ?label.
  }
}

Try it!

Thank you 🙇‍♀️ --Shisma (talk) 15:12, 26 December 2019 (UTC)

@Shisma: "# some magic probably using COUNT() and wdt:P674 🤷‍♀️". Indeed; indistinguishable from.

SELECT ?item ?label (count(?char) as ?numberOfCharacters) WHERE {
  ?item wdt:P179 wd:Q16290.
  optional {?item wdt:P674 ?char .}
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en".
    ?item rdfs:label ?label.
  }
} group by ?item ?label

Try it!

--Tagishsimon (talk) 16:16, 26 December 2019 (UTC)

@Tagishsimon: Thank you. a modified copy has been added here 🙂--Shisma (talk) 16:54, 26 December 2019 (UTC)

People who are most probably dead

Need a list of articles in Ukrainian Wikipedia w:uk: about people in who where born 100+ years ago and who are without date of death. --Perohanych (talk) 08:08, 17 December 2019 (UTC)

SELECT (COUNT(?person) AS ?cnt) WHERE {
  ?person wdt:P569 ?dob.
  MINUS{?person wdt:P570 []}
  FILTER (?dob < "1900")
#  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }
ORDER BY ?dob

Try it!

I started with getting a count of all oldest people but it times out... --Infovarius (talk) 20:45, 26 December 2019 (UTC)

Listings of women with nationality

Hi. I found this tool that would come in handy. Only that I would like to list not only one country but several due to external use but also to be able to upload images to Commons if possible.

Currently this is the code I have:

#Mujeres mexicanas (5000)
SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P21 wd:Q6581072 . ?item wdt:P27 wd:Q96 . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "es,en". }
}

Try it!

In reality they would be countries like Guatemala or Trinidad and Tobago because they have the least items, photographs or information. --Hispano76 (talk) 19:55, 27 December 2019 (UTC)

@Hispano76: Here's an example using a VALUES statement, in which there is a list of the countries of interest - right now Mexico and Guatamala, but more can be added. This is probably the simplest approach.

SELECT ?item ?itemLabel ?country ?countryLabel
WHERE 
{
  VALUES ?country {wd:Q96 wd:Q774}
  ?item wdt:P27 ?country . 
  ?item wdt:P21 wd:Q6581072 . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "es,en". }
} order by ?countryLabel

Try it!

hth --Tagishsimon (talk) 05:48, 28 December 2019 (UTC)

@Tagishsimon: Thanks! --Hispano76 (talk) 15:03, 28 December 2019 (UTC)

List of places

Hello. I want a query to find all items that:

• have Statistical Service of Cyprus Geocode (P4812) with any value, but with out end time (P582) as a qualifier
• have located in the administrative territorial entity (P131) -> Limassol District (Q59150)

Xaris333 (talk) 20:01, 29 December 2019 (UTC)

@Xaris333:

SELECT ?item ?itemLabel ?P4812
WHERE 
{
  ?item p:P4812 ?stat. 
  ?stat ps:P4812 ?P4812.
  filter not exists {?stat pq:P582 [] . }
  ?item wdt:P131 wd:Q59150 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?itemLabel

Try it!

--Tagishsimon (talk) 21:04, 29 December 2019 (UTC)

Thanks! Xaris333 (talk) 00:38, 30 December 2019 (UTC)