Testwiki:Request a query/Archive/2018/05

Check whether a given list of element IDs fullfills certain criteria and return list of matching items

Hello, for a project of mine I'm trying to filter items of a given list of $n$ items (e.g. {Q14944328,Q2013}) so that the final list only contains items which fullfill a certain condition (e.g. wdt:P31/wdt:P279* wd:Q19729799). How would I be able to do so? Thanks in advance for any pointers --Naseweis520 (talk) 23:02, 30 April 2018 (UTC)

Template:Ping If I understand your question, something like this (which right now finds nothing, since the two values you gave are not instances/subclass of the third item you gave. Come back with more questions, if needbe:

Template:SPARQL hth --Tagishsimon (talk) 23:10, 30 April 2018 (UTC)

Template:Ping Thank you very very much. This is exactly what I needed. --Naseweis520 (talk) 18:19, 1 May 2018 (UTC)

Request EVERY items with a specific description

Hi,
I try to get every element with the word "Treaty" in the label : Template:Sparql Obviously, the timeout "wall" rise, even if I put a LIMIT at the end. Someone know if this kind of request is possible ? Simon Villeneuve (talk) 15:21, 1 May 2018 (UTC)

Alphabets sorted by number of letters

We do not seem to indicate directly how many Template:Q are in an Template:Q, so getting that number and sorting across alphabets is a bit more involved already, and then there are steps like Template:Q that complicate matters even further. --Daniel Mietchen (talk) 23:16, 1 May 2018 (UTC)

Query without duplicates

I want all items with P1087, but without P3314. As far as I can see, his query works in principle: Template:SPARQL However, it contains a lot of duplicate entries. How are the duplicates removed? Steak (talk) 15:01, 2 May 2018 (UTC)

Template:Ping you need to look for DISTINCT values - query above amended. --Tagishsimon (talk) 16:08, 2 May 2018 (UTC)

Thank you! Steak (talk) 17:16, 2 May 2018 (UTC)

Show Wikidata properties that are external identifiers and have a regex

Wikidata property - external ID - list

This comes close, but also lists results from item name space: Template:SPARQL Any idea why ISNI comes up twice? The two result rows look identical to me.

Template:SPARQL —MisterSynergy (talk) 21:23, 27 April 2018 (UTC)

Wikidata property - external ID - group and count

A part from a pure list, grouping would be nice, I fail to display the propertyLabels Template:SPARQL 85.179.115.121 21:04, 27 April 2018 (UTC)

Template:SPARQL

MisterSynergy, thanks for your help above, now improved here with your suggestion. Any idea about displaying the propertyLabels? 85.179.115.121 21:58, 27 April 2018 (UTC)

Template:SPARQL You cannot further process labels that were collected with the label service (as you want to do by group concatenation). rdfs:label and a subsequent lang filter is suitable for that purpose. —MisterSynergy (talk) 22:05, 27 April 2018 (UTC)

MisterSynergy would it be possible to have a link to each property and as link label the propertyLabel? 77.179.61.171 21:40, 4 May 2018 (UTC)

To my knowledge this is not possible in the Query Service GUI. You would have to query with a script, reshape the result to the desired format and output it e.g. by bot to a wikipage. Probably even ListeriaBot is not able to do this … —MisterSynergy (talk) 21:57, 4 May 2018 (UTC)

Indian National Highways below 100

Hi, I am looking for Indian National highways below 100 Template:SPARQL --Naveenpf (talk) 06:14, 3 May 2018 (UTC)

Template:Ping Assuming the numbers in the English labels are the correct numbers. (For some roads, i.e. Template:Q the numbers in different language labels seem to disagree)

Template:SPARQL

--Larske (talk) 07:34, 3 May 2018 (UTC)

Chess elo ratings awarded after player's death

Hi, I want to give deprecated rank to all chess elo ratings that were awarded by FIDE after player's death. Can you please help me with query returning these statements? Thank you. Wesalius (talk) 14:01, 5 May 2018 (UTC)

Template:Ping This might be along the road you want to travel ... shows (I think) all non-deprecated ELO ratings given post mortem. --Tagishsimon (talk) 14:25, 5 May 2018 (UTC)

Template:SPARQL

and more concisely, just the players affected:

Template:SPARQL - hth --Tagishsimon (talk) 14:29, 5 May 2018 (UTC)

Thank you, sir! Wesalius (talk) 05:33, 6 May 2018 (UTC)

Ten most used references for date of birth

I want to have a list of the ten most used references for Template:P by number. Can someone help?. But excluding imported from Wikipedias. Breg Pmt (talk) 17:15, 6 May 2018 (UTC)

Template:Reply to Template:SPARQL – and since the query takes a long time, here are the current results:

• Template:St, Template:St
• Template:St
• Template:St
• Template:St, Template:St
• Template:St
• Template:St
• Template:St, Template:St
• Template:St, Template:St
• Template:St, Template:St
• Template:St, Template:St

– each used between 5000 and 10000 times. --TweetsFactsAndQueries (talk) 20:48, 6 May 2018 (UTC)

• If look for Template:P as well, data.bnf.fr and rkd.nl are frequent.
  --- Jura 21:59, 6 May 2018 (UTC)

What is the query behind "Search Wikidata" ?

The one on top of each wikidata page : it seems to launch an approximate search on every field/property of an object.

It's quite efficient, to say the least. And exactly what I am looking for.

Anyone know the recipe ?

Thanks !

Atopus (talk) 15:21, 3 May 2018 (UTC)

Template:Ping something called CirrusSearch, the tech of which is described here. --Tagishsimon (talk) 14:43, 3 May 2018 (UTC)

Template:Ping Great ! Thank you very much for the hint :) It looks it can easily be used through the mediawiki api. Do you know if the same tool is used for the page behind the "containing" link, which gives even more results ? I cannot find an api endpoint for this one... Atopus (talk) 10:29, 7 May 2018 (UTC)

AFAIK they use the same backend. You may get the best answers at WD:DEV, though. Matěj Suchánek (talk) 18:32, 7 May 2018 (UTC)

Show Wikidata properties that are external identifiers and are used on instances of Q5

Something is wrong, no idea what. Template:SPARQL And if possible show a count for usage. 77.180.23.13 02:39, 7 May 2018 (UTC)

You need to use the claim predicate (?p, p:P…) instead of the property itself (?property, wd:P…): Template:SPARQL – however, that query just results in a timeout: I think there are too many human items on Wikidata to do this. --TweetsFactsAndQueries (talk) 09:12, 7 May 2018 (UTC)

Growth of Wikimedia chapters and user groups over time

Hi

I'd like a query that showed a map with timeline to show the growth of Wikimedia chapters and user groups over time. Each chapter or user groups that is present should colour that section of the map in (I realise this excludes the thematic organisations). This would basically be an animated map of the graph that appears in this page.

I want this for two reasons:

1. I'm running a Mozilla Sprint project to encourage people to add information to Wikidata about the open movement see here and would really like to use this as an example.
2. I think a non timeline version could be used as a useful explorer for the Meta page on Chapters and User groups. If it could look like the maps on Wikidata:Wikidata_in_Wikimedia_projects#Maps_and_graphs that would be super amazing, e.g clicking on an area brought up website, contact info etc.

To make sure the query is correct I guess there should be additional queries for number of chapters and usergroups, if a country or region is stated on their item etc. I'm very happy to do the legwork to fill in gaps.

Thanks

--John Cummings (talk) 15:00, 6 May 2018 (UTC)

Template:Reply to Hm, I don’t think that’s possible as you describe it… as far as I’m aware we don’t directly support animated maps. A non-animated map with different layers for different years would in theory be possible, but in practice I don’t think we don’t have enough Template:P statements on countries yet. So far, I think we can only create a simple timeline: Template:SPARQL – but note that many user groups and even chapters are missing their Template:P: query --TweetsFactsAndQueries (talk) 20:59, 6 May 2018 (UTC)

Template:Ping, this is great, thanks. Is it possible to just have a static map like the one on the chapters and user groups page but with details pop up when you hover over an area? --John Cummings (talk) 22:16, 6 May 2018 (UTC)

Template:Reply to that would be possible if we had geoshapes for countries, but currently the only three countries (values of Template:P statements) with a geoshape are Template:Q, Template:Q and Template:Q (query). --TweetsFactsAndQueries (talk) 23:27, 6 May 2018 (UTC)

Thanks for the explanation Template:Ping, --John Cummings (talk) 07:29, 7 May 2018 (UTC)

Template:Ping Template:Q is a country? I must investigate this... Matěj Suchánek (talk) 19:14, 7 May 2018 (UTC)

Template:Reply to I couldn’t even find the statement with a query anymore because you already fixed it :( --TweetsFactsAndQueries (talk) 19:25, 7 May 2018 (UTC)

Get de.wiki article name

In short, I'm wanting to extract the de.wiki article name from an item, and compare it with a string. If we imagine I'm looking for an item which has a de.wiki sitelink named "Monkey Island (England)", how do I achieve that? Some broken SPRQL, below, as a starter for 10. (1. in the borked SPARQL, why are the binds failing? 2. and I appreciate that even if they worked, I'd be extracting a string with underscores in it, rather than space ... presumably we also store the de.wiki name, without underscores, as we see it displayed in the wikipedia links section of the item record. 3. VALUEs ?itemLabel is commented out as I was working on the stringslicing problem.). thx Tagishsimon --16:27, 6 May 2018 (UTC)

Template:SPARQL

Matěj Suchánek (talk) 18:44, 7 May 2018 (UTC)

Thank you, Matěj Suchánek. Much obliged. --Tagishsimon (talk) 19:02, 7 May 2018 (UTC)

Books written in a language the author, supposedly, can't write

Hi, I'm stuck with this. I want a list of books written in X by an author whose Template:P or his Template:P does not include X or any dialect of X —I'm using Template:P for this. I got some results but my attempt is not working properly and —even worse— it looks ugly. It keeps showing, for example, Template:Q, written in Template:Q by Template:Q, who has Template:Q among his Template:P. Template:SPARQL SPARQL is not among my Template:P, definitely. Can someone help me? Thanks in advance. --Wikidelo (talk) 17:47, 10 May 2018 (UTC)

The problem with Template:Q is that he has Template:Q set as the preferred language. I worked around that in my query and but I cannot guarantee it's 100% correct:

Template:SPARQL

Matěj Suchánek (talk) 12:24, 11 May 2018 (UTC)

Template:Ping: Thank you very much! So MINUS is the key. Your query returns a lot of results. I'm baffled. It will be hard to manually check what's wrong with those books. Many thanks again. --Wikidelo (talk) 13:37, 11 May 2018 (UTC)

Template:Ping There may be nothing wrong. My books have been translated into languages I can't write - I'm still the author. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 04:14, 12 May 2018 (UTC)

Template:Ping Of course, so the query excludes Template:Q. Now it also excludes, for example, works written in Template:Q by authors who speak Template:Q or Template:Q but it doesn't work viceversa, i.e.: it still shows works written in Template:Q by authors who speak or write plain Template:Q. I think this can be solved, but the main flaw of the query is in cases of books by two or more people, like Template:Q, written in Template:Q, where Template:Q knows Template:Q but Template:Q does not, so the book appears in the list. I've not checked the 15000 results, but randomly looking at some of them I can tell there's a lot of people with Template:P missing and, definitely, something wrong with a bunch of those books. Cheers. Wikidelo (talk) 10:14, 12 May 2018 (UTC)

Displaying labels in two or more languages

I have:

which works well; but what should I change, to display the English and Armenian labels, in separate columns? Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 12:46, 11 May 2018 (UTC)

I tried to use two SERVICE's having bound different variables for each but it didn't let me. So the following is one possibility:

Template:SPARQL

Matěj Suchánek (talk) 13:02, 11 May 2018 (UTC)

Troubleshoot wdt:P31*

I run this query that works excellent finding museums Template:Sparql as museums could be of a specific type I would like to change the query to something like this

?museum  wdt:P31 wd:Q33506.

to

?museum  wdt:P31* wd:Q33506.

==> I get an timeout and I guess because of some bad structure. Question: How do I troubleshoot that

I have tested this workaround

 {?museum  wdt:P31 wd:Q33506}
 UNION
 {?museum  wdt:P31/wdt:P31 wd:Q33506}

that works great but maybe I miss some more complex structures.... - Salgo60 (talk) 09:38, 12 May 2018 (UTC)

wdt:P31* or wdt:P31/wdt:P31 don't make sense since superclasses are linked to using Template:P (not Template:P). The correct "complex structure" is wdt:P31/wdt:P279*. Note that these queries tend to timeout. Matěj Suchánek (talk) 09:53, 12 May 2018 (UTC)

Captain Cook memorials, and things named after him in Australia

In the Australian press there was discussion about memorials, statues, etc. named after Captain James Cook. There was a comment whether Google could map such memorials, and I thought that it was an interesting query which Wikidata may be able to manage. Any takers to see what we can do?  — billinghurst sDrewth 11:17, 12 May 2018 (UTC)

Template:SPARQL

Things located in Australia that were named after James Cook, shown on a map. The query returns 4 results. Shinnin (talk) 11:26, 12 May 2018 (UTC)

20 results in the example below, because Template:P is most often not populated. Changing "Captain.Cook|James.Cook" to "Cook gives a much larger list, but with heaps of false positives. The ideal is to populate P138 for all items that are actually named after him.

Template:SPARQL - hth --Tagishsimon (talk) 11:47, 12 May 2018 (UTC)

Calculate percentage

This query displays the number of churches by region in France. I'd like to add another column with the percentage of churches with Template:P. Template:SPARQL Is it possible? Thanks. — Ayack (talk) 15:56, 7 May 2018 (UTC)

It is:

Template:SPARQL

Matěj Suchánek (talk) 18:41, 7 May 2018 (UTC)

Great! Thanks a lot Matěj Suchánek! — Ayack (talk) 11:16, 8 May 2018 (UTC)

In fact, there is an issue: ?depcount is different from the initial query which have the right number. — Ayack (talk) 11:23, 8 May 2018 (UTC)

I made a correction. Matěj Suchánek (talk) 12:37, 8 May 2018 (UTC)

Thanks Matěj Suchánek! Results are right now. Do you know how to round percentages to two decimal places? I have tried ROUND() but it doesn't work. — Ayack (talk) 07:57, 9 May 2018 (UTC)

SPARQL seems not to provide a function for this, so I hacked around it by multiplying the result by 100, rounding it and then dividing by 100. Matěj Suchánek (talk) 14:47, 9 May 2018 (UTC)

If you want the column "withCoord" to be properly sortable, you may want to sacrifice the "%" by skipping the STR and CONCAT stuff and just use

(ROUND((IF(?_withCoord > ?depCount, ?depCount, ?_withCoord) / ?depCount) * 100 * 100) / 100 AS ?withCoord)

--Larske (talk) 06:14, 12 May 2018 (UTC)

Template:Ping Thanks a lot! — Ayack (talk) 18:49, 14 May 2018 (UTC)

Unit conversion

I want a list of units, their type, and conversion to SI units. The following query gives that, but is missing the unit for the SI conversion.

SELECT
?unitLabel ?dimensionLabel ?siconvLabel
WHERE
{
  ?unit wdt:P2370 ?siconv .
  ?unit wdt:P111 ?dimension .
SERVICE wikibase:label {
bd:serviceParam wikibase:language "en" .
}
}

For example, this query gives me that "1 foot = .3048", but I'd like to know that "1 foot = .3048 m". Can this query be completed?

Meingbg (talk) 17:19, 13 May 2018 (UTC)

Template:Ping Seems to be something like

Template:SPARQL hth --Tagishsimon (talk) 19:00, 13 May 2018 (UTC)

Perfect, thanks!

Meingbg (talk) 09:23, 17 May 2018 (UTC)

Query for complex constraint: if property has value A then qualifier B should be present

The problem is with Template:P and its qualifiers. In many items it looks like

• Template:Statement
  • Template:P
  • Template:P
  • Template:P
  • Template:P

I every case if Template:Statement the set of 4 qualifiers should be present (and in many items right now e.g. P877 is absent). So I'm trying to add a complex constraint (I was advised that it can't be done using normal constraints) for this.

Template:Collapse top I thought that if I add this constraint to P993/P994/P995/P877 and the constraint is like select all items where Template:Statement and there is no qualifier Pxxx, the problem will be solved. I managed to write this (SPARQL is not my thing, I wrote this based on other queries): Template:SPARQL But obviously this is not what I'm looking for, because it finds items where Template:Statement has no P993... (and in this case there shouldn't be any P993 because other set of qualifiers should be here). In the worst scenario (rare but possible) there may be multiple Template:Statement in one item, each with different values. E.g. in one item it may look like:

• Template:Statement
  • Template:P = 1 / Template:P = 1 / Template:P = 1 / Template:P = OX
• Template:Statement
  • Template:P = 1 / Template:P absent / Template:P = 2 / Template:P = OX
• Template:Statement
  • Template:P = ... / Template:P = ... / Template:P = ... / Template:P = ...

Template:Collapse bottom So I have two questions:

1. is it possible to somehow get select all items with Template:Statement, then look if Pxxx is present in each such statement (but without checking P4952 that has other value than Q51139288)?
2. maybe there is simpler/better way to achieve this (by this I mean that: if there is Template:Statement then all 4 qualifiers P993/P994/P995/P877 should be present). Wostr (talk) 20:44, 8 May 2018 (UTC)

Template:Ping, I'm not sure where we're going with this. Your query example is only picking up P4952=Q51139288 and Q51139288 has no P993, so where you say "this is not what I'm looking for, because it finds items where ,,statement||P4952|Q2005334,, has no P993" does not, for me, compute. Slightly amending & expanding your query, below, currently allows us to find P4952=Q51139288 with any one of the four pqs missing, and you can play with different arrangements of filter not exist to see different combinations of missingness. The query below demands that a single instance of Q51139288 must have each of the 4 pqs ... it does not deal with a situation in which there are multiple P4952=Q51139288 statements, each having a single qualifier. Do we need to solve that. (Or do we need to find such cases, and knock some sense into them. Umm. Help me out. What direction do you want to go in? I'm not familiar with complex constraints ... maybe I should go read up on them. Read this. As far as I can see, the below will serve as a custom constraint for the condition P4952=Q51139288 and Q51139288 has any one of P877, P993, P994 or P995 missing.

Template:SPARQL --Tagishsimon (talk) 22:47, 8 May 2018 (UTC)

• Template:Re the problem is that:

  P4952=Q51139288 should always have all 4 qualifiers (P993/P994/P995/P877)

  P4952=Q2005334 should always have all 4 qualifiers (P5040/P1033/P5041/P5042; but this is not the problem right now)

The query is picking items where P4952=Q51139288 has no P993/P994/P995/P877 (this is correct) but also items where P4952=Q2005334 has no P993/P994/P995/P877 (this is not). There is e.g. Template:Q in the results, where

So all 4 qualifiers for the first statements are present, but the query looked in the second statement and found no P993/P994/P995/P877 (and there shouldn't be any P993/P994/P995/P877, because it's the different statement). Wostr (talk) 07:07, 9 May 2018 (UTC)

I think ethanol is being selected because of the P877=no value in Q51139288 - see, for instance, this section. I think Q2005334 is a red herring; nothing is being selected because of it. So talk to me about P877=no value. Is that valid, or do we treat P877=no value as being the same as an absent P877? If the latter, then the query is okay. If the former, we need to rejig the query to deal with the no value issue. Not yet sure how to do that, right now, but maybe ?statement a wdno:PQ877 is the direction of travel; the query below, for instance, finds items with a P877 of no value. Not yet sure how to wrangle that into useful filter, and right now I urgently need to go and murder a busker playing very loudly amplified accordian music with an easy listening backing tape. I may be some time. irl, eh? --Tagishsimon (talk) 12:46, 9 May 2018 (UTC)

Template:SPARQL

So, this, maybe, if "no value" is an acceptable record (and thus should be excluded from the report).

Template:SPARQL --Tagishsimon (talk) 15:37, 9 May 2018 (UTC)

Okay, I think it's working :) thank you very much. In fact, P877=no value is correct and it'll be the most common case when I correct all the statements where P877 is absent. The difference between number of items from your first and second queries seems okay, so it really filtering out all the no values. The only thing is, why your last query returns 1032 items and the query without spaces before "." returns 1036 or 1035 items, depending on... I really don't know on what. Does the spaces in the query matters?

Template:Sparql

Wostr (talk) 16:52, 9 May 2018 (UTC)

No, wait; now both queries gives 1036, so nevermind ;) Thanks for help. Wostr (talk) 16:54, 9 May 2018 (UTC)

Template:Ping - sorry, we're not quite there. I'd leave the constraint you added in place since I think for practical purposes the issue is on the margins given the current state of the data.

All - the problem is explained in the comments in the report below --Tagishsimon (talk) 00:30, 10 May 2018 (UTC)

Template:SPARQL Template:Ping Maybe this is what you are looking for. Template:SPARQL Please note that 'unknown value' is treated as valid PQ in this query. --Larske (talk) 05:33, 12 May 2018 (UTC)

Thank you Larske; 'unknown value' is in fact a valid value in these qualfiers; I'll try to check this query as soon as I can :) Wostr (talk) 19:29, 14 May 2018 (UTC)

It seems that you query found 4 items more and I really don't know why ;) I'll update the constraint though, thanks again. Wostr (talk) 19:31, 20 May 2018 (UTC)

Map of languages

Template:Election map example Hi

I'm working on a proposal for a project to try and improve coverage of languages on Wikidata and Wikipedia and want to create a prototype to visualise languages on a map as a way to explore them. Is there some way to create a map which shows which languages are present in which countries? I know there are some issues with lack of shape files for countries and country regions. But I've also seen maps like this? I'm very happy for it to work for a very small region, all I want to show is that its is possible and you can click a button to get to the Wikipedia article for that language. This could either be a map to show all the languages in a region or which countries a language is spoken in or both.

Thanks very much

--John Cummings (talk) 10:11, 15 May 2018 (UTC)

Here's a query that can start to answer this question: a map showing every sovereign state and its official languages. Issues that still need to be fixed are: using shape polygons to cover the countries instead of using a point coordinate per country, and linking to the wiki pages of the languages instead of just showing them in a concatenated field. I'm pretty sure those issues can be fixed with Kartographer, but I haven't worked with Kartographer in the past so I can't be of help there.

Template:SPARQL2 --CalvinBall (talk) 11:13, 18 May 2018 (UTC)

Query all International Masters awarded in a specific year

How to build a query that queries all Template:P: Template:Q that have been awared in a specific year (Template:P), e. g. 1995? Steak (talk) 19:37, 20 May 2018 (UTC)

Template:Ping Like this ... the data model is, for these sorts of things, instructive, fwiw.

Template:SPARQL --Tagishsimon (talk) 19:48, 20 May 2018 (UTC)

Thank you! Steak (talk) 20:29, 20 May 2018 (UTC)

Querying a value with qualifiers

I would appreciate some help with the following query. I am trying to create a SparQL query that will list all the articles that have Template:P and Template:Q, and also the specific qualifier Template:P plus Template:Q. I created the query I have placed underneath here, but it doesn't give any result(s). (At the moment it should at least produce Template:Q.) Ideally I would like to also include the qualifier Template:P with value 19th of May 2018 (in combination with Amsterdam as a location), but I honestly have no idea how to format that date part of the query.

Thank you in advance for all the help Template:Smiley, Ecritures (talk) 14:39, 20 May 2018 (UTC)

Template:SPARQL Shinnin (talk) 14:46, 20 May 2018 (UTC)

Template:Ping (ec) this may take you a bit further.

Template:SPARQL - hth --Tagishsimon (talk) 14:48, 20 May 2018 (UTC)

Not exactly what you asked for, but all items that *do have an article at nl-wiki* and are present in the Amnesty list: Template:SPARQL Edoderoo (talk) 15:18, 20 May 2018 (UTC)

Thank you all so very, very much! I will be using all these queries for different purposes. Ecritures (talk) 13:18, 21 May 2018 (UTC)

Norwegian citicens holding a political party membership

I want to make a Query listing only Norwegians who has Template:P102. I am able to make this list, but this includes all Norwegians... Breg Pmt (talk) 17:30, 22 May 2018 (UTC)

Template:Ping Template:SPARQL - hth ... if you'd like other attributes, sorts, etc, let us know. --Tagishsimon (talk) 17:54, 22 May 2018 (UTC)

-Thank you very much. Breg Pmt (talk) 17:39, 23 May 2018 (UTC)

Reference URL's timeout

I would like to query all reference url's of a specific website (https://www.cbs.nl/) but receive a timeout when going over a limit of 26.

current working query:

The Le Figaro example is proposed here as "A query that has difficulties" for automatic optimization. The proposed solution appears to be turning the optimizer off, but my query still times out when the limit is removed. https://www.wikidata.org/wiki/Wikidata:SPARQL_query_service/query_optimization

So i suppose my question is: Is there any way to get find all uses of "http://statline.cbs.nl" in the entire wikidata dataset without getting a timeout? Template:Unsigned2

• This is not possible with the Query Service, but you can simply use Special:LinkSearch/statline.cbs.nl. —MisterSynergy (talk) 14:09, 23 May 2018 (UTC)

Get all Lexeme lemmas and IDs

With the Lexeme property officially launched, I wanted to do a simple query to return all the current lexeme IDs and their Lemma label. Here's an example of a lexeme. It's modeled similarly to wikidata properties, it seems, using a Lexeme namespace (Lexeme:L1). I tried to query all the lexemes in the same way one would query all the properties, which didn't seem to work. Does anyone know how to properly list all of them currently?

--CalvinBall (talk) 12:55, 24 May 2018 (UTC)

According to the announcement at WD:PC, lexemes cannot be queried yet in the Query Service. You probably have to wait for this to work. —MisterSynergy (talk) 08:33, 24 May 2018 (UTC)

okay, thanks for the answer! I'm looking forward to being able to query Lexemes in the near future ^-^ --CalvinBall (talk) 12:55, 24 May 2018 (UTC)

Potential articles that might be mosaics, based on categories, but haven't been identified as such on Wikidata

Hey All, I would like to replicate much of the same function as the list at Wikidata:WikiProject_sum_of_all_paintings/Possible_paintings, for looking for other potential pieces of art (in my case, I am really interested in mosaics). Multichill currently runs that list with a both, but running a copy of that bot on my own is beyond my skill, and it would be great if I could run a similar result via Listeria Bot (see conversation at User_talk:Multichill#Mosaics_report_like_Wikidata:WikiProject_sum_of_all_paintings/Possible_paintings). Could someone write a query that could do this? Working with categories, and Wikipedias to create this list in the query service is a bit beyond my understanding, and I can't find a good example that uses categories. Sadads (talk) 18:27, 26 May 2018 (UTC)

Template:Ping Listeria may be of use, such as to keep tabs on the statements associated with an identified set of wikidata items, such as those with a P31 of Template:Q. But it cannot do the main thing you're after, which is fishing around inside language wikipedia categories to find articles which have no wikidata item, or articles which have an item but do not have a P31 Q133067. A quick look at multichill's code suggests it is taking the various language names for category:paintings from a wikidata item, and then throwing a succession of queries at petscan, and then presumably doing something interesting with the results. So, right now, I'd point you at petscan. Are you familiar with it? Here, for instance, is a report on articles in en.wiki's category:Mosaics, or in categories up to 3 levels below that one, which do have a wikidata item, but do not have Q133067 in the item record. You can amend this to query other language wikipedias, by finding the category name listed in Template:Q ... so here's a version for the it.wiki (and in both cases, hit the do it button to run the report.

From a quick look at a sample of 1, the article in the mosaics category had a P31 of sculpture, because it is some combination of sculpture and mosaic (w:en:In the Shadow of the Elm) ... so you might find it all tedious going. I'm happy to talk to you some more about petscan, listeria or the query service; but sadly I'm too much of a wimp right now to fork multichill's code. --Tagishsimon (talk) 23:20, 26 May 2018 (UTC)

I am fairly comfortable with petscan, but really would like some type of share report for working from this: otherwise, I will just hack inconsistently at stuff on enwiki and Commons with a combo of petscan and others lists. I guess I really should be making a multichill or listeria fork request for pinging petscan. Sadads (talk) 02:20, 27 May 2018 (UTC)

Age at death

I'd like to run a query for age at death, listing people who were a certain age at death (or a range of ages), e.g. the "27 club". It could be exact or based on birth and death years. I looked up how to handle dates,[1] but I don't understand the query language well enough. Fences and windows (talk) 18:17, 25 May 2018 (UTC)

Template:Ping something like this. Your biggest problem is that the timeout is too short to allow you to run the query across all humans, so you have to find subsets of them. In the query below, I've found 73-year old geologists for you. How wide the part of the query that defines how many humans the report will be considering can be drawn, is a matter for experimentation, but you'll be wanting one or more lines in the query along the lines of ?item wdt:P106 wd:Q520549. where you're saying, the item (human) has a property with this value. (e.g. gender=female, occupation=plumber, country of citizenship=Australia, etc. Finally, my SPARQL may be a bit more clunky than needsbe ... I'm still at the rubbing sticks together hopefully stage of my learning :)

Template:SPARQL - hth - --Tagishsimon (talk) 20:21, 25 May 2018 (UTC)

Template:Ping, wow, you're good at this. Thank you. A shame the full search times out - could they not batch larger searches? Fences and windows (talk) 22:48, 26 May 2018 (UTC)

Template:Ping Thank you. I'm quite intrepid in the shallow end. I don't know enough about the infrastucture, but odd snippets suggest to me there's no appetite to increase the timeout value; or to provide a quarry-type batch report running facility. I get the impression that queries that would run, a year or more ago, now timeout. So, yes; it's a shame, especially when trying to address large & poorly statemented items such as many of our humans records, or list article items. --Tagishsimon (talk) 23:31, 26 May 2018 (UTC)

Template:Ping Maybe not important in this case, but the division by 365 gives the wrong result for persons that are near, but not full, 73 years of age at their death. This is because they have lived a number of leap years. See this modified query which will list Template:Q who was 72 years, 11 months and 18 days at his death:

Template:SPARQL

There are some other problems with these querys;

1. They don't take the timePrecision into consideration. Some persons just have a year precision for their birth and/or death years and they can thus be faulty listed (or not listed) as "73 years at death person". Template:Q is one example of a person with birth date with year precision.
2. They don't take the timeCalendarModel into consideration. Some persons might have different calenders for their birth and death dates, Template:Q is one example of such a person. This might lead them to be faulty listed as well as these calendars differ with a couple of week.

--Larske (talk) 08:02, 27 May 2018 (UTC)

Template:Ping thank you; that's all understood, and a fine illustration of deep end versus shallow. Much better analysis of the problem, and an elegant & easy to follow solution. I very much enjoy trying to put together queries, but I'm always properly grateful to be schooled in this way: several things learned, which is the whole point of my lurking on this page. It's fair to say that much of what I've comprehended in the last 6 or 8 months has been as a result of observing your queries, and those of Matěj Suchánek, MisterSynergy, and others. If you could hear how often I say ah, so that's how you do that; such as for your solution in 'Query for complex constraint: if property has value A then qualifier B should be present' where I just couldn't get a handle on ?statement a wdno:P995. and you came along with exists{}. Please, never hesitate to jump in and suggest improvements, because that's what makes this page the best SPARQL tutorial on the interweb. --Tagishsimon (talk) 12:42, 27 May 2018 (UTC)

Template:Ping and Template:Ping, can the filter "span" accept a range of values? It works with < or > rather than =. Fences and windows (talk) 21:35, 27 May 2018 (UTC)

Template:Ping I've amended my query, above, to show geologists between 64 & 73 by the addition of another filter. Is that what you're after? Obvs, adopting Larske's creation and amending the filter in it, along the lines of the filter in mine, is the way to go. --Tagishsimon (talk) 22:34, 27 May 2018 (UTC)

Or, if you're after a non-sequential set of ages, FILTER (?span in (62.0, 63.0, 73.0)) seems to work a treat. --Tagishsimon (talk) 22:41, 27 May 2018 (UTC)

Using qualifiers

I have this Query workin fine, but I want to add the qualifier Template:P Any suggestion

SELECT DISTINCT ?item ?itemLabel ?partyLabel ?partitilh_righet ?partitilh_righetLabel ?f_dselsdato ?Historisk_befolkningsregister_ID ?virkested ?virkestedLabel ?av_innenfor ?av_innenforLabel

WHERE {
 ?item wdt:P27 wd:Q20.
 ?item wdt:P39 wd:Q30185.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],no". }
 OPTIONAL { ?item wdt:P102 ?partitilh_righet. }
 OPTIONAL { ?item wdt:P569 ?f_dselsdato. }
 OPTIONAL { ?item wdt:P4574 ?Historisk_befolkningsregister_ID. }
 OPTIONAL { ?item wdt:P937 ?virkested. }

}

 Best Regards Pmt (talk) 19:55, 28 May 2018 (UTC)

Template:Ping

Template:SPARQL like this - I've wrapped the necessary clauses in an Optional{} and ordered results so you can see some ofs. I'm presuming you wanted of P39? The statement I added could be combined with your P39 statement; I've chosen not to do that for now. If you visit the RDF model you can probably trace out the connections which necessitate this circumlution. --Tagishsimon (talk) 21:52, 28 May 2018 (UTC)

Template:Ping Thank you very much. Breg Pmt (talk) 22:00, 28 May 2018 (UTC)

Defining formula (P2534)

I am looking for a query for items that have Template:P = "c^2=a^2+b^2". The query should return Template:Q. --Rotpunkt (talk) 13:57, 24 May 2018 (UTC)

Template:Ping I have a not very pretty solution. Others may be able to provide a less painful route.

Template:SPARQL --Tagishsimon (talk) 15:51, 24 May 2018 (UTC)

Thanks, but actually I was looking for a query without regex. In Template:QP if you click "edit" the value is "c^2=a^2+b^2" I want to query for that exact value without regex, in the same way I can do for all other datatypes. I have this problem only with Help:Data_type#Mathematical expression properties. --Rotpunkt (talk) 10:30, 26 May 2018 (UTC)

Good question. Maybe one for Wikidata:Contact the development team. Maybe the "alttext/displaystyle" should be in a separate triple. Its value for the above is alttext="{\displaystyle c^{2}=a^{2}+b^{2}}"-
--- Jura 11:14, 26 May 2018 (UTC)

Crushed as I am by Rotpunkt's reaction to my query ;) there is a certain compelling logic that we should be able to search using the string we used at data input time. (contains would allow you to find c^{2}=a^{2}+b^{2} & dispense with regex, but c^2=a^2+b^2 is beyond my reach. Are we certain it is not stored anywhere?) --Tagishsimon (talk) 11:58, 26 May 2018 (UTC)

Wikidata:Contact the development team#Queries for maths formula --Tagishsimon (talk) 12:05, 26 May 2018 (UTC)

Oh, don't be! At least, he did thank you and provided valuable feedback. ;)

In https://www.wikidata.org/wiki/Special:EntityData/Q11518.json there is

"P2534":[{"mainsnak":{"snaktype":"value","property":"P2534","datavalue":{"value":"c^2=a^2+b^2","type":"string"},"datatype":"math"},"type":"statement","id":"Q11518$fe4a5b31-4f7e-29a3-06da-25f0186d9a42","rank":"normal"}],

Looks like its simplicity somehow got lost in WQS. Maybe Template:Ping can help?

Personally, I had hoped we could do more with the datatype and I don't think people use it much. So it might primarily work for 3rd party tools. For now, it might be a bit like PDFs in Wikidata.
--- Jura 12:13, 26 May 2018 (UTC)

Template:Ping It does look overly complex to me. But rendering of the type is defined by the implementation of the type (e.g. the extension) so it needs to be changed there. So let's discuss on Template:Phab and figure out how to improve it. Smalyshev (WMF) (talk) 23:21, 29 May 2018 (UTC)

Why, Sir, you find no person, at all intellectual, who is willing to leave Regex. No, Sir, when a person is tired of Regex, she is tired of life; for there is in Regex all that life can afford. --Tagishsimon (talk) 15:18, 26 May 2018 (UTC)

Template:Reply to I’ve created phabricator:T195765 to track this. --Lucas Werkmeister (WMDE) (talk) 12:42, 28 May 2018 (UTC)

Nice, thanks to all. I will monitor that task. --Rotpunkt (talk) 17:09, 28 May 2018 (UTC)

Classification of animals

Hello there,

I'm interacting with a third-party who would like to reuse our classification about animals. I was thinking about making a query with a tree view that would render all animals. Since I never had the time to dig properly into our biology ontologies, I'm a bit lost :)

Can anyone help me to build a query that would give an overview on how we describe and organize data about animals?

Thank you very much, Lea Lacroix (WMDE) (talk) 12:39, 28 May 2018 (UTC)

Template:Ping Scholia has a graph like that in its taxon aspect. --Daniel Mietchen (talk) 00:04, 29 May 2018 (UTC)

Template:Ping There are more than 2.4 million Template:Q of which some are in the Template:Q of Template:Q. Have a look at this graph to see how the Template:Q are connected in some kind of Template:P hierarchy from Template:Q via Template:Q, Template:Q, Template:Q, Template:Q, Template:Q, Template:Q, Template:Q and Template:Q to Template:Q. --Larske (talk) 09:59, 29 May 2018 (UTC)

Template:Ping: Please note that in Wikidata all taxa from different taxonomies are included and that it will not be possible to create a "valid" taxonomy out of it - all depends on the sources and hypotheses included. I am sure that it is not possible to built a valid and solid tree of animals in WikiData. Please ask Template:Ping on this topic - from my opinion he is the one who best knows about these areas in Wikidata. -- Achim Raschka (talk) 06:00, 30 May 2018 (UTC)

Template:Ping contrary to the names given to Template:Q in different languages, it does not represent a taxon (a particular group of organisms) but a taxon name, as has been made even clearer recently by the discussion at Property talk:P1420. The relationship between taxon names and taxa is N:M, since a given taxon may have more than one name (e.g. Template:Q and Template:Q are just two of the names in Wikidata for the same taxon), and a taxon name may apply to more than one taxon (e.g. Template:Q is the name of a taxon that includes Template:Q as a subfamily and the name of a taxon that excludes it). Within its severe limitations, particularly its inability to represent taxa, Wikidata attempts to represent multiple taxonomic views, as User:Achim Raschka says, so it should never be used to try to build a self-consistent taxonomic tree. Peter coxhead (talk) 09:02, 30 May 2018 (UTC)

There is a related topic at my talk page. --Succu (talk) 17:34, 30 May 2018 (UTC)

Qualifier Template:P for Template:P of artworks

Dear all, I can not figure out how to write what should be a simple query: one that would give me all of the artworks from a specific collection with their Template:P and the Template:P qualifier for that Template:P. I have tried this, but I think I am not getting the qualifiers fully:

Template:SPARQL Could somebody help me? That would be much appreciated! Alina data (talk) 14:18, 29 May 2018 (UTC)

Template:Ping It's not a very straightforward query, but with luck, this works.

Template:SPARQL - hth - --Tagishsimon (talk) 14:57, 29 May 2018 (UTC)

Template:Ping it works perfectly! thank you! Alina data (talk) 08:20, 30 May 2018 (UTC)

Combine two queries

Hi, I would like to combine these two queries into one:

This returns the streets in Haarlem here on Wikidata:

Template:SPARQL &

This returns the streets in Haarlem in the BAG:

would like to combine these based on label so I have suggestions on what to add. Seem to have gotten a bit stuck. Maybe a fresh pair of eyes will help? Multichill (talk) 18:45, 31 May 2018 (UTC)

Template:Reply to Template:SPARQL --TweetsFactsAndQueries (talk) 19:54, 31 May 2018 (UTC)

Thanks! I put some variants at Property talk:P5207 for the curious reader :-) Multichill (talk) 22:26, 31 May 2018 (UTC)