Testwiki:Request a query/Archive/2019/08

Border cities of a country

What would be the query to get a list of the cities of a country A that are X km from another country B or the border cities of a given country (such as Template:Q or Template:Q in Template:Q)? --190.226.77.162 06:39, 30 July 2019 (UTC)

Hi. You might try this one

Template:Sparql Bouzinac (talk) 11:10, 30 July 2019 (UTC)

Thanks, it was just what I needed. After some adjustments I stay like this:

Template:Sparql

What I was interested in was getting the border cities of Template:Q (except Template:Q, Template:Q and Template:Q) so I'm pretty happy with the result. --190.226.77.162 13:59, 30 July 2019 (UTC)

Note that there is a lack of data : not every city on a border has been flagged Template:Q. So if you see some of them, please add Template:Q on their wikidatas page. Bouzinac (talk) 14:26, 30 July 2019 (UTC)

I was adding that property to some South American cities that I saw in 1 and 2 and others may also be interested in marking other cities around the world so I took the liberty of adding the query in the examples. With regard to the query I was working with, I noted the following inconsistencies:

• Template:Q, Template:Q and Template:Q have two coordinates in Template:P.
• Template:Q, Template:Q, Template:Q, Template:Q, Template:Q and Template:Q are not border cities.

--190.31.204.96 13:27, 31 July 2019 (UTC)

Solved some obvious inconsistencies. I am curious : what is your definition of border town? I was in "conflict" with Template:U about Template:Q : he states his town wouldn't be called a border town, although it lies on the border Denmark/Germany.

• Would it be any cities whose shapes are touching another country (my preferred definition)?
• Or would it be any cities/town having its downtown/buildings very much touching another country ?
• Or any cities that have a means of transport between countries? Example : town A ==road==town B >>borders cities. But what if a mountain/river separates A and B, and no direct means of transport? Bouzinac (talk) 14:43, 31 July 2019 (UTC)

Please note that Template:Q is not a city or town, but a large predominantly rural area and as such cannot fit the definition "border town". The main town Template:Q maybe could be called a "border town", since it's situated just a few kilometers north of the border, but IMHO isn't really a traditional border town in the same way i.e. Template:Q is. --Hjart (talk) 16:24, 31 July 2019 (UTC)

I've asked a similar question some time ago: "what cities (or towns, or other populated places) share boundaries?" (I can't find it in archives because of inexact wording). But it requires checking of $O(n^2)$ linkings which always timeouts. I wonder if someone can propose more effective solution. --Infovarius (talk) 12:06, 1 August 2019 (UTC)

Federated query with lei.info

Hi,

We have many LEI code in Wikidata database. I request a query on this identifier.

On webpage https://lei.info/sparql , i construct a query for having examples of real LEI code from database lei.info : Template:Code

I need a federated query with wikidata database, for check some LEI codes. --Manu1400 (talk) 16:59, 1 August 2019 (UTC)

Template:Support LEI.INFO uses AllegroGraph RDF triple store and SPARQL 1.1 query engine. Source Manu1400 (talk) 17:22, 1 August 2019 (UTC)

Are you sure that the LEI SPARQL endpoint supports federated queries, and Wikidata in particular? I don't get anything federated here. We cannot use WDQS, since lei.info is not a supported endpoint for federation according to mw:Wikidata Query Service/User Manual/SPARQL Federation endpoints. —MisterSynergy (talk) 18:42, 1 August 2019 (UTC)

"AllegroGraph supports queries with distributed databases. You can group multiple triple-stores, both local and remote into a single virtual store. It allows thread-safe opening of multiple triple-databases from one application (for the read only parts of the database). Queries over multiple databases are easy with direct data access from applications. It also supports physical merging of databases." Source: from paragraph GeoTemporal Reasoning, Social Network Analysis, and Additional Features Manu1400 (talk) 19:41, 1 August 2019 (UTC)

Not sure whether this necessarily means that you can actually use this feature. Usually I'd say that something like this should work via their endpoint:

PREFIX voc: <http://lei.info/voc/l1/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>

SELECT ?glei ?leiCode ?legalName ?legalEntityStatus ?wdItem WHERE {
  SERVICE <https://query.wikidata.org/bigdata/namespace/wdq/sparql> {
    ?wdItem wdt:P1278 ?leiCode .
    FILTER(ISLITERAL(?leiCode)) . # to ignore unknown values
  }
  ?glei voc:identifiedBy/voc:leiCode ?leiCode .
  ?glei voc:legalAddress/voc:country 'KY' . # Cayman Islands
  ?glei voc:legalName ?legalName .
  ?glei voc:legalEntityStatus ?legalEntityStatus .
}

However, it does not, and it does not tell me why. —MisterSynergy (talk) 19:55, 1 August 2019 (UTC)

bind, coalesce or concat ???

Hi, I want to get the values of ?date, ?prec and ?cal in one column cell separated by a comma. And if there is no Template:P in the item, then I want to get a literary "empty" into the cell (e.g. in Template:Q). Can you help me, please? Doc Taxon (talk) 16:57, 1 August 2019 (UTC)

Template:Ping

Template:SPARQL

--Larske (talk) 03:17, 2 August 2019 (UTC)

wow, cool, thanks! Doc Taxon (talk) 07:29, 2 August 2019 (UTC)

Candidacies by electoral district

I would like to request the following query: Template:P Template:Q with the following qualifiers: Template:P Template:Q and Template:P Template:Q. --Davidpar (talk) 20:16, 1 August 2019 (UTC)

Template:Ping this, probably:

Template:SPARQL --Tagishsimon (talk) 21:24, 1 August 2019 (UTC)

Template:Ping Thanks! It is possible to show column with Template:P/Template:Q? --Davidpar (talk) 06:47, 2 August 2019 (UTC)

Template:Ping Seems so:

Template:SPARQL --Tagishsimon (talk) 13:59, 2 August 2019 (UTC)

Searching for all the Wikidata items with descriptions both in Basque and Spanish

I would like to collect the items in Wikidata with descriptions both in Basque and Spanish languages. I would like to use those bilingual descriptions to improve a Machine Translation system specialised in translating such descriptions. I get some results when I make a restricted query, for example, I get 144 items when I restrict the search to people born in Bilbao (https://w.wiki/6Wh). But if the query is more general, and asks ,for example, for humans then it happens an "time out" error.

Template:SPARQL Can anyone help me to collect so a list of bilingual descriptions? Have I to download the whole Wikidata Dump? Thanks.Ksarasola (talk) 16:34, 25 July 2019 (UTC)

• I can't help you directly with your question, but maybe this is interesting: User:Pasleim/Language_statistics_for_items has some statistics. For eu, there are 7.6 million descriptions. Obviously, it might just be "Wikimediako argipen orri". --- Jura 12:54, 2 6 July 2019 (UTC)
  • You are right, Template:Ping That is not what I looking for. But thanks for your answer, your information could be interesting to know the number of descriptions in Basque and Spanish. 7,623,021 (13.4%) descriptions in Basque and 30,584,776 (53.8%) in Spanish. Ksarasola (talk) 21:14, 27 July 2019 (UTC)

Template:Ping Date of Birth is set to the Country, so I think we need to set it to anywhere in the country using:

UNION { ?item wdt:P20/wdt:P131* wd:Q3995 }

UNION { ?item wdt:P937/wdt:P131* wd:Q3995 }

It also looks like the descriptions are of people only.

PS I think you taught me that one! Llywelyn2000 (talk) 15:59, 29 July 2019 (UTC)

Template:Ping The relation wdt:P19 is not 'Date of Birth', it is 'Place of Birth'. So I think the query is well formed. However, that query is not what I want. My goal is to collect all the concepts (or as many as possible) with description both in Basque and Spanish, whatever they are persons or not. But my problem is that when I remove this restriction (Born in Bilbao), even though I am still asking for humans, as the number of concepts is too big the query finishes with "time out". And I have no results. However what I want is to collect bilingual descriptions not only for humans, but for any comcepts. But in that case the problem will be bigger; if the nunber for humans is too big, the number of concepts will be even bigger. I think I should work with the Wikidata dump, I know it will be more complex, but it seems that it is the only way to get what I want. Thanks anyway.Ksarasola (talk) 23:35, 29 July 2019 (UTC)

Template:Ping I meant Place of Birth, Froydian slip! Sorry. I agree that working on the datadump is the way forward. All the best! Llywelyn2000 (talk) 03:55, 30 July 2019 (UTC)

Small step: Using the following query I get 840 items that are instance-of human and that have description both in Basque and Spanish: Template:SPARQL However what I want is to collect bilingual descriptions not only for humans, but for all items in Wikidata. And when I remove "?item wdt:P31 wd:Q5." I get a 'Query timeout limit reached' message.

I download the wikidata dump. (latest-all.json.bz2 from https://dumps.wikimedia.org/wikidatawiki/entities/ ) But its is 36Gb and I can not use it in my laptop.Ksarasola (talk) 16:26, 30 July 2019 (UTC)

Template:Reply to searching for labels or descriptions by language is not very efficient in the query service. I think the regular search is actually a much better fit for this, because it has the hasdescription keyword: see Special:Search/hasdescription:es hasdescription:eu. If you’re familiar with the API, you can download up to 5000 results at a time (API sandbox) and follow continuation until you have all the search results; several API client libraries can take care of the continuation automatically (e. g. python-mwapi). --TweetsFactsAndQueries (talk) 13:34, 7 August 2019 (UTC)

Chinese writers whose copyright expired

Hi! I would like to know how to query a group like the title. I think the logic could be like this:

1. they should have instance of = humans, occupation = writer.
2. they should have native lang = zh/cmn/yue/wuu/nan/hak/gan/cdo, or lang spoken, written or signed = zh/zh-xx(all the varieties)/cmn/yue/wuu/nan/hak/gan/cdo/lzh.
3. to determine copyright expiration, I thought of three ways:
   1. those who died before a given year, e.g. 1948
   2. those who died in a given year (for example for those whose copyright expire in 2031, their year of death would be 1960).
   3. those who were born before a given year. (In case date of death is missing in wikidata, which is quite a common problem for Chinese people's items. Writers born 110+70=180 years ago definitely have copyright expired.)

Thanks in advance!--Roy17 (talk) 13:04, 2 August 2019 (UTC)

Template:Ping something like that?

Template:SPARQL

Cheers, VIGNERON (talk) 09:13, 6 August 2019 (UTC)

Template:Re your codes were great, but a column of names is most important for making a list, so I tried adapting it but I couldnt figure out how to call labels. I call p1559 and p18 instead.

Template:SPARQL

Or

Template:SPARQL--Roy17 (talk) 18:58, 9 August 2019 (UTC)

Getting QIDs from a en:Wikipedia category page?

Is there a query that will list all the items on this wikipedia article, https://en.wikipedia.org/wiki/Category:United_States_geologic_formation_stubs ? Ultimately, I'd like to find their Template:P and Template:P, too. Thanks. Trilotat (talk) 17:23, 5 August 2019 (UTC)

Not that easy task. For looking up items for articles in a category, I would recommend you PetScan (right now it seems out of order though). But it cannot look up additional data, like P131. Queries can do this but not for subcategories (unless done individually), like this:

Template:SPARQL

--Matěj Suchánek (talk) 08:01, 6 August 2019 (UTC)

You can generate a PagePile with PetScan and then use the input directly for TABernacle. I did not try it, but I don't see why it should not work. Steak (talk) 09:30, 9 August 2019 (UTC)

Chess players without description

Hi, I am looking for all chess player items without a german description. I have looked at some examples and came up with this query, but it seems to be wrong, because it gives zero results althouth there are target items. Can somebody fix?

Template:SPARQL

-- Steak (talk) 18:31, 9 August 2019 (UTC)

• fixed above. --- Jura 18:33, 9 August 2019 (UTC)

Thanks! Steak (talk) 18:43, 9 August 2019 (UTC)

Select time in two statements with P577

Hello. Let a variable ?p in a specific query contain elements that have exactly two statements with Template:P (analogously this).

The query should select items for which:

1) in both statements the year and month are the same;

2) for one of statements, precision=10 (months), and for the other statement, precision=11 (days).

The query should output an selected element and time+precision for statement with precision=10 in form (example for Q65683198):

 Q65683198    2018-02-01T00:00:00Z/10

Thanks. --Renamerr (talk) 16:05, 4 August 2019 (UTC)

Template:SPARQL

--Matěj Suchánek (talk) 08:13, 6 August 2019 (UTC)

Thank you very much Template:Ping. I modified this version of the query to select the same month and year. This can be tested for VALUES ?p { wd:Q23916181 } .

Template:SPARQL

Yours sincerely, --Renamerr (talk) 11:26, 11 August 2019 (UTC)

Identifiers used in multiple Wikidata items

I would like to find all instances where a NAID (Template:P) value is reused on more than one Wikidata item. I know how to get a list of all items with P1225 and their values, but I haven't been able to figure out if there is a way to make a query such that only the items whose value appears more than once across all Wikidata items are returned. Basically, NAID is a unique identifier, so I'd like to check for anomalies (possible duplicates) if multiple items have a same identifier. Thanks! Dominic (talk) 18:51, 8 August 2019 (UTC)

Template:Ping Here's a way to get pairs of items with the same NAID: Template:SPARQL Hope that helps. Mahir256 (talk) 18:55, 8 August 2019 (UTC)

You can also have a look at Wikidata:Database reports/Constraint violations/P1225#"Unique value" violations :-) —MisterSynergy (talk) 18:58, 8 August 2019 (UTC)

Nice, thank you both! Dominic (talk) 19:06, 8 August 2019 (UTC)

Dominic: The property talk page also includes links to queries for this (next to the constraint display). --- Jura 11:37, 10 August 2019 (UTC)

P304 of a reference

Hello. I want to find all items with Template:P -> Template:Q that have a reference with Template:P -> http://www.parliament.cy/images/media/assetfile/VIVLIO%2050HRONA.pdf. I want in the results to have item label and Template:P of that reference. Xaris333 (talk) 00:43, 11 August 2019 (UTC)

Template:SPARQL If you are not interested in the properties of the statements which have this reference, this query could also be interesting for you (basic mapping "person -> page"). —MisterSynergy (talk) 07:10, 11 August 2019 (UTC)

Thanks! Xaris333 (talk) 12:52, 11 August 2019 (UTC)

References with implicit URL

Would it be possible to query for (a) all statements which have a reference which only includes Template:P and no other properties, (b) all statements which have a reference which contains only one property–value pair, and (c) all statements which use Template:P as a qualifier without Template:P? (This is for Wikidata:Project chat#P813 as qualifier.) Jc86035 (talk) 11:44, 11 August 2019 (UTC)

Female Swedish writers of the 20th century, who were born in Stockholm

How to write a query for it. I have started, but something is not working well.

--Juandev (talk) 01:44, 12 August 2019 (UTC)

I edited it above. --- Jura 02:23, 12 August 2019 (UTC)

A query about genre books

Hello! I would like to make a query to find all books with some genre and write by women. I have create this one but it doesn´t work. Can you help me?

I have removed a space before Q1213268 in your query above. Now it returns one item.

--Larske (talk) 08:23, 15 August 2019 (UTC)

Your query ask for items who have 3 genres (distopia, feminism and science-fiction). As Larske said, there's only one result for this.
This query give you all distopian books and subclasses of book who are written by a female. Il you want another genre, just replace Q26928598 by the other one you want : Template:SPARQL
If you want all the books from these 3 genres, you must put a union between all of them
Template:SPARQL
Simon Villeneuve (talk) 11:41, 15 August 2019 (UTC)

Railway stations in Ireland

I’m trying to create a list of railway stations in the Republic of Ireland, listed in English alphabetical order, with their Irish name equivalent next to it. I tried adapting examples but I didn’t succeed. Thank you! --Metrophil (talk) 20:05, 10 August 2019 (UTC)

Template:Ping This lists all items in the railway station subclass tree, where country=Ireland.

Template:SPARQL --Tagishsimon (talk) 21:32, 10 August 2019 (UTC)

Hi Tagishsimon, thank you and apologies for replying so late! Really helpful --Metrophil (talk) 13:49, 16 August 2019 (UTC)

Example of query where an item does NOT have a specified property

I'm not sure how to build this. I'd like to search, for example, for Template:Q, Template:P in Template:Q, without Template:P or Template:P. Can you point to one I can edit to look like that or might someone be so kind? Thanks. Trilotat (talk) 18:57, 15 August 2019 (UTC)

Template:Ping Two approaches, fwiw.

Template:SPARQL

Template:SPARQL --Tagishsimon (talk) 20:04, 15 August 2019 (UTC)

Template:Ping Thanks! That's so very helpful. Trilotat (talk) 01:39, 16 August 2019 (UTC)

Select items where a property starts by xx

Hi, I need all the Template:Q where its Template:P has 43 in the two first digits of the code. Thanks, Amadalvarez (talk) 05:29, 16 August 2019 (UTC)

Template:Ping

Template:SPARQL

--Larske (talk) 07:18, 16 August 2019 (UTC)

Thanks, Template:U'. I know for next time. Amadalvarez (talk) 06:16, 17 August 2019 (UTC)

List instances of multiple instances of same qualifier per property

I've been able to write this query:

Template:SPARQL

--[

which finds cases where for a particular P1344 there are multiple entries in the P54 member of sports team. So for instance for this item https://www.wikidata.org/wiki/Q22342392 under 2017-18 Bundesliga, there are 2 entries under member of sports team (P54). What I can not figure out is how to run the query across the dataset, not just limited to a single P1344. I recognize I may need to limit the result set, but I'm not able to get the syntax right. Thanks

Template:Ping Probably this sort of direction. Your ?event was a statement, not a value. hth.

Template:SPARQL --Tagishsimon (talk) 03:33, 17 August 2019 (UTC)

Avoiding duplicates

I've made this query to get all women whose occupation is either "director" or "film director" or "television director" or "anime director" and have an Anime News Network identifier (p:P1982), and I want to see their occupation(s), and their dates of birth and death when they have one, but how can I make people with more than one occupation appear only once? I've tried with GROUP_CONCAT, GROUP BY etc. but it says "bad aggregate"

Template:SPARQL --Lombres (talk) 13:28, 18 August 2019 (UTC)

Template:Ping Several things to do; not least, cause the label values to exist so they can be aggregated. hth.

Template:SPARQL --Tagishsimon (talk) 19:35, 18 August 2019 (UTC)

Template:Ping I tried something similar. If I well remember, my only mistake was "group by" only by item and itemLabel instead of adding everything else... I don't think I can understand why it works, but I hope I can do this again in the future. Thanks a lot --Lombres (talk) 20:02, 18 August 2019 (UTC)

Layers by property

I am still struggling with layers. All I want is one color for coordinates from ?pob and second color for coordinates from ?pod. Template:SPARQL --Jklamo (talk) 23:03, 18 August 2019 (UTC)

Template:Ping you probably need 3 colours; the coord used may match both the pob & pod coord, or just pob, or just pod.

Template:SPARQL --Tagishsimon (talk) 23:36, 18 August 2019 (UTC)

Template:Ping You are right, thanks for the query.--Jklamo (talk) 07:29, 19 August 2019 (UTC)

Find a specific CAS number I'm searching for

How do I modify this so that it will search for a specific Template:Q number? I know that this number is present in the wikidata item Template:Q but I want to learn a this query so that I can query any number I want. Thanks in advance!

?item wdt:P231 wd:8015-92-7.

Ελλίντερεστ (talk) 06:16, 19 August 2019 (UTC)

Template:Ping Template:Sparql author  TomT0m / talk page 09:12, 19 August 2019 (UTC)

Layer does not display

I have the following query: Template:SPARQL2 I do not understand why the ?layer does not display. Any pointers? --Daniel Mietchen (talk) 07:54, 19 August 2019 (UTC)

OK, now I see — removing the STR() from the ?twinyear line solves the problem:

Template:SPARQL2

--Daniel Mietchen (talk) 08:03, 19 August 2019 (UTC)

Authors most cited with no entry in Wikidata

I want to have a list of all authors with no entry on Wikidata using Template:P, ordered by the most strings with the most occurences. The goal is to identify the most cited author with no entry in Wikidata, so we can create entries that would fit in the most articles already in WD.

From the discussion page of the property article I have the query for an exact match: Template:SPARQL

This is not what I am looking for. If I try to have all the matchs displayed in a list, I have a time out error:

Does anyone knows how I could have a successful request to identify which are those popular yet not in Wikidata authors?

Thanks, Dirac (talk) 19:58, 7 August 2019 (UTC)

Template:Ping there are more than 94,000.000 statements using Template:P so imho it is unfortunately not possible to run a succesful sparql-query. to get an overview about the usage of Template:P you can use instead the following ldf-query Mfchris84 (talk) 11:39, 8 August 2019 (UTC)

Template:Ping Thanks for the answer. I didn't know the ldf-query tool. It seems handy for this type of request. However, one web page for every 100 results is not easy to manipulate. Is there a way to get the result in a text file? I tried to add &application/ld+json at the end of the URL, with no luck. Should I use the WikidataQueryServiceR R package or is there a more convenient (ie on-line) way of getting the results in one text file? Thanks, Dirac (talk) 13:13, 8 August 2019 (UTC)

Template:Ping The page size of LDF seems to be configurated server-side only. LDF endpoint As you have already written you can parse the result directly as json-ld but you have to write your own parser to iterate through the whole result set. Unfortunately i am not familiar with your mentioned R package. Mfchris84 (talk) 13:39, 8 August 2019 (UTC)

Template:Ping, I did parse the first 10k pages and, for your curiosity, here are the 10 most cited authors with no entry, starting with the number of occurences:

• 10263 Wei Wang
• 6136 Lei Zhang
• 6104 Li Zhang
• 6100 Li Li
• 6085 Yan Wang
• 4769 Hui Wang
• 4630 Jian Wang
• 4583 Hui Li
• 4470 Jie Zhang
• 4143 Jian Zhang

Curiously, they all seems chinese. Thanks for the chat, that was fun to do! Dirac (talk) 16:44, 8 August 2019 (UTC)

It's the most frequent author name strings, not authors. I vaguely recall a paper attempting to determine the most prolific authors .. I think it came up with a Japanese university dean who had his name on most papers written there. --- Jura 16:57, 8 August 2019 (UTC)

Template:Ping Funny stats - but as Template:Ping already mentioned that are not the most cited distinct authors, that are only the most common author names. so there are many Wei Wangs out there to write so many papers. what plans do you have with the list of all author names? imho to create new author names or link to existing author items you need additional information or references to disambiguate them. Mfchris84 (talk) 04:55, 9 August 2019 (UTC)

Template:Ping Indeed, there is Wei Wang, Wei Wang, Wei Wang and he even dances! I was a bit naive by creating this list, not expecting that so many people could have the same name. I may look at least common names, ones that sounds like people from Québec and see if I can identify their uniqueness and then create their element. Or not. Do you see another useful usage? Dirac (talk) 12:37, 9 August 2019 (UTC)

You could pick one or several names and then disambiguation everybody else with the same name, e.g. Special:Search/Roger Moore haswbstatement:P31=Q5. --- Jura 15:03, 9 August 2019 (UTC)

I realized that since there was 100 people per page, the 10K pages downloaded include the whole dataset. Hence, the classification includes everyone. 100k * 100 = 1M, which is a little bit more than 1% of the whole dataset. The file for this 1% can be found online here: https://octet.ca/arc/most-popular-names_P2093_wikidata.txt

Dirac (talk) 12:50, 12 August 2019 (UTC)

• It's more like 940k of pages: [1]. --- Jura 17:23, 12 August 2019 (UTC)

Q66426695, found thanks to WikiProject Random, probably appears in some 1000 papers on Wikidata .. but I think physicists are generally ignored in this type of stat .. --- Jura 20:06, 13 August 2019 (UTC)

Template:Ping, I've written an R script to address this. It moves author from Template:P to Template:P with corresponding qualifiers and references. I'll try it on Q66426695. Do you have an idea how I could find unique author names with a lot of entries? Dirac (talk) 16:19, 17 August 2019 (UTC)

Template:Ping Template:Ping I think it would be helpful to create and periodically update a list of values from P2093 (similar to the one you did) and check these. The problem with these values is that many are limited to an initial and a surname. If you delete common surnames, it might give better candidates, but it still doesn't exclude the possibility that you have people with the same given name(s) and family name writing in the similar field. BTW, have a look at https://tools.wmflabs.org/author-disambiguator/ sample: [2]. --- Jura 14:50, 20 August 2019 (UTC)

HDS and WP

The editor of the Historical Dictionary of Switzerland (Q642074) is interested to have a list of all Wikipedia articles (for example in English) which have the HDS identifier P902. I hope this an easy query for an experienced Wikidatian. I tried to create such a query but wasn't able to do it. Thank you for your answer. --Hadi (talk) 13:57, 21 August 2019 (UTC)

Template:Ping Two queries for you. Item labels are fetched as your default language, and if a label in such a language does not exist, then the en, de or fr label in that order. You can amend the label choice in the wikibase:label statement.

1. Wikidata items with HDS ID:

Template:SPARQL

2. English Wikiperdia articles linked to from Wikidata items that have HDS ID. You can probably see that to adapt this to e.g. French wikipedia requires only amendment of the URL in schema:isPartOf <https://en.wikipedia.org/> .

Template:SPARQL

It's possible to do more than this, so if you want amendments, please come back for more. hth --Tagishsimon (talk) 14:50, 21 August 2019 (UTC)

Thank you very much Tagishsimon! As the HDS is published in 3 languages it's interesting for them to have also the Wikipedia languages separately. I will try these searches and come back if necessary. --Hadi (talk) 15:31, 21 August 2019 (UTC)

Get cities and town

Can you help me to get cities and town certain country? thanks a lot  – Template:Autotranslate

It's as easy as

Template:SPARQL --Infovarius (talk) 16:19, 22 August 2019 (UTC)

Иut in some countries there are also "communes" or "municipalities"... Are they count? --Infovarius (talk) 16:24, 22 August 2019 (UTC)

get english wikipedia URL (page) for wikidata Q item

I have a query that needs to be finished. It gets 3 items but the 4th - I don't know how to do. Also, how to make the NCT item optional (e.g., if no NCT property is there, don't require it) Similarly the wikipedia article may not exist. So I want an outer join. (get all trials and some trials will not have wikipedia page and will not have nctid. Template:SPARQL5 EncycloABC (talk) 18:48, 21 August 2019 (UTC)

Template:Ping None of them seem to have en.wiki articles; the code in the query would return article names were there any.

Template:SPARQL --Tagishsimon (talk) 20:35, 21 August 2019 (UTC)

Old taxonomists, missing from Wikisource

Please:

People who died before 1949, with a page in Wikispecies, but no page in any Wikisource. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 15:45, 21 August 2019 (UTC)

Template:SPARQL Mahir256 (talk) 16:12, 21 August 2019 (UTC)

Thank you. Someone on en.Wikisource adapted it, to remove some duplicates: https://w.wiki/7Mc -- Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 10:53, 23 August 2019 (UTC)

Hoping someone can modify this query to list for all administrative entities in Australia

Query which lists number of artworks in wikidata for women whose work is held in institutions in the ACT Template:SPARQL MargaretRDonald (talk) 13:42, 23 August 2019 (UTC)

Template:Ping This - https://w.wiki/7PC - probably. --Tagishsimon (talk) 13:50, 23 August 2019 (UTC)

Thanks very much, Template:Ping MargaretRDonald (talk) 14:04, 23 August 2019 (UTC)

Wikidata list

Hello. User:Xaris333/Ministers/Ministers of Defense. I want the Wikidata list to show the qualifiers P580, P581, P1545, P1365, P1366. I am sure if I have to change something in the query or to add the qualifiers (and how) to "columns". Maybe I have to ask to Template talk:Wikidata list?

Template:Ping Template:Done author  TomT0m / talk page 11:59, 19 August 2019 (UTC)

Many thanks!! Xaris333 (talk) 13:58, 19 August 2019 (UTC)

Template:Ping can you change it to include also the items with Template:P -> Template:Q? Xaris333 (talk) 14:00, 19 August 2019 (UTC)

Template:Ping Template:Done quickly, for some reason however the list showed by wdqs has less lines than the rew results of Wdqs. Some lines on wdqs are duplicated because of the duplicate predecessor/successors of some ministers. This seems to vanish post-treated by the bot, it seems there is only one result per minister. If you want refinements like this sorted out or more, like sectioning by function, it’s possible. author  TomT0m / talk page 15:43, 19 August 2019 (UTC)

Template:Ping I want to be able to have a full list. So if someone was minister twice, I want both periods, 2 lines. I want to have all the numbers in ?order column for example. Xaris333 (talk) 16:07, 19 August 2019 (UTC)

Without getting too much involved, and iirc, Listeria will emit only one row per ?item in its query. So if an individual has held two positions and ?item is the individual, you get just one row / one position. So Template:Q is one example - WDQS gives two rows, Listeria only one. Any work-around is sufficiently not trivial that I'm not going to go further; clearly this is territory in which one person may have held multiple positions; may have held the same positions multiple times; and in which positions may have been held by multiple persons - thus there's no quick & easy candidate for the ?item in the query. Group_concat (e.g. of the positions per individual) is possible, but that doesn't produce a simple tabular result. --Tagishsimon (talk) 15:44, 20 August 2019 (UTC)

Template:SPARQL2

Template:Ping Well I can use the above query. But there are two problems: The order is problematic, is not 1,2,3,4... but 1,10,11...,2,21... I want the order to be normal so I would be able to check the items. And the columns for office, item, predecessor and successor are the items numbers. I want to see the labels in Greek. Xaris333 (talk) 03:27, 21 August 2019 (UTC)

Template:Ping An amended query in WDQS seems straightforward:

Template:SPARQL

But Listeria fetches the Greek item label without need for the label service; so the query in this example Listeria /seems/ to do what you want. EXCEPT ... if you compare it with the WDQS report, you'll see that individuals like Template:Q have two rows in WDQS, one in Listeria; cause is that the position statement in Q7226186 has two Template:P qualifiers. So either you need to do some group_concat stuff here, or else sort out the data (?) --Tagishsimon (talk) 20:17, 23 August 2019 (UTC)

Thanks Tagishsimon!! I can use both listeria and query for checking. (Template:Q is a unique situation). Xaris333 (talk) 04:24, 24 August 2019 (UTC)

age not calculating/appearing

Template:SPARQL MargaretRDonald (talk) 21:23, 23 August 2019 (UTC)

Template:Ping Order (sometimes) matters:

Template:SPARQL --Tagishsimon (talk) 21:26, 23 August 2019 (UTC)

Thanks again. Template:Ping MargaretRDonald (talk) 21:29, 23 August 2019 (UTC)

List of failed space missions

Hello everyone, I don't know how to query the list of failed space missions using Wikidata. Can anyone help me? Template:Unsigned

• Any suggestion what to look for? Space mission + number of deaths? --- Jura 14:42, 25 August 2019 (UTC)

Newbie problems with sparql

I want to learn some sparql. I hope somebody can make me understand my failures. These are my mysteries at the moment:

Simple intersect

The following is supposed to match items that are an instance of both software and free and open-source software: Template:SPARQL It turns up nothing – which would be fine if there wasn't e.g. Q131344 obviously matching the criteria.

So that's as a result of the rank of the statements. Statements on items can have one of three ranks - preferred, normal, or deprecated. A query using wdt: asks for truthy statements, and these are defined as "the best non-deprecated rank for given property. Namely, if there is a preferred statement for property P2, then only preferred statements for P2 will be considered truthy. Otherwise, all normal-rank statements for P2 are considered truthy.". For Template:Q, Template:Q has a preferred rank, and is the only property that will be returned by a wdt: query. To query against all properties, use p:/ps:, so:

Template:SPARQL

There's some more info here - https://www.mediawiki.org/wiki/Wikibase/Indexing/RDF_Dump_Format#Statement_types --Tagishsimon (talk) 12:50, 26 August 2019 (UTC)

More complex matching

I tried to match items that have a license that is an instance of a free software license. I thought my solution worked until i tried to exclude stuff using MINUS and lots of items showed up that match the exclusion criterium like e.g. Q136775 while using MINUS in simpler queries works as expected. Template:SPARQL --Reseletti (talk) 12:42, 26 August 2019 (UTC)

Template:Ping Same issue as above. Try:

Template:SPARQL --Tagishsimon (talk) 12:54, 26 August 2019 (UTC)

Also, see https://www.w3.org/TR/sparql11-property-paths/#path-language for info on constructions like wdt:P31/wdt:P279* --Tagishsimon (talk) 12:56, 26 August 2019 (UTC)

Thanks a lot!--Reseletti (talk) 13:31, 26 August 2019 (UTC)

Generating labels with pairs of square brackets

Template:SPARQL I used the query above to generate a list of missing NT Acacia articles within Petscan and inserted the resulting wiki markup in my sandbox to generate a list of articles to be written. However, when I click upon a label it takes me to a wikidata item. I want to generate a red wikilink which will turn blue when an article is completed, i.e., I want my labels to be, for example, [[Acacia caracteae]]. MargaretRDonald (talk) 02:49, 27 August 2019 (UTC)

Template:Ping This sounds like a Listeria redlist, such as this example. It'll refresh daily, removing blue-links. If you'd like it, please steal the page. --Tagishsimon (talk) 06:13, 27 August 2019 (UTC)

But to answer the question; like this:

Template:SPARQL --Tagishsimon (talk) 06:30, 27 August 2019 (UTC)

Thanks, once more, Template:Ping. MargaretRDonald (talk) 08:22, 27 August 2019 (UTC)

Border posts of EU

Hello there Is it possible to show a map of all European (EU) border posts connected to non-EU (+UK) countries (for instance Lituania >> Russia but not Lituania >> Latvia)?Bouzinac (talk) 14:25, 27 August 2019 (UTC)

Fabrics by weave and fiber

How can I change this query so I get one line per item with all of the values, instead of a separate line for each item/value combination? Thanks! - PKM (talk) 19:26, 21 August 2019 (UTC)

Template:Ping Aggregation. Changes in three areas: 1) the select 2) the group by 3) in the wikibase:label to make the labels available for grouing.

Template:SPARQL --Tagishsimon (talk) 20:30, 21 August 2019 (UTC)

Template:Ping Thanks! “woven_fabricLabel” is returning blank values - can you fix that? Also, is there a way to have the other entities not display a full URL? - PKM (talk) 21:03, 21 August 2019 (UTC)

Template:Ping this

Template:SPARQL

Template:Ping Perfect!! Thank you. - PKM (talk) 20:51, 27 August 2019 (UTC)

Get a qualifier

Hi. In this query, I'd like to get, in addition, the qualifier P585 (?pointInTime) of the P1082 with rank preferred. Thanks, Amadalvarez (talk) 11:01, 27 August 2019 (UTC)

Template:Ping

Template:SPARQL --Tagishsimon (talk) 20:39, 27 August 2019 (UTC)

Thanks a lot !!. Amadalvarez (talk) 04:11, 28 August 2019 (UTC)

Malformed count

Template:SPARQL (I thought I knew how to do this!! But clearly I don't) MargaretRDonald (talk) 20:43, 27 August 2019 (UTC)

Template:Ping I'm afraid this got complicated. Your query fails 'cos the GROUP BY lacks ?artistLabel - should be GROUP BY ?artist ?artistLabel, but even if we fix that, it looks as though the query will pick up an artist multiple times if they have multiple occupations matching the four you specify, and when grouping, produce a count which is is a multiple of the real count - Template:Q gets picked up three times, ends up with a count of 12.

So the below uses subqueries; the first query gets all ?artist that qualify and the second query does the count. Part of the qualification is ?artist wdt:P6379/wdt:P131* wd:Q408. - they have a work in an Oz inst ... this causes a timeout unless the magic hint:Prior hint:gearing "forward". is used. Some more on both of these in Wikidata:SPARQL query service/query optimization.

Template:SPARQL

It's also possible that using VALUES might have been quicker / easier than the unions:

Template:SPARQL --Tagishsimon (talk) 21:08, 27 August 2019 (UTC)

Thanks, yet again, Template:Ping. Very helpful indeed... Some close scrutiny required at this end. Cheers, MargaretRDonald (talk) 21:34, 27 August 2019 (UTC)

more than 1 value

Hello, Please tell me a request that give: the items which are in the tree of subclasses of Q20162172 and have more than 1 P279 value. Visite fortuitement prolongée (talk) 20:10, 26 August 2019 (UTC)

Template:Ping something along these lines. ?items with more than 2 P279s get multiple rows - such as Template:Q.

Template:SPARQL --Tagishsimon (talk) 20:22, 26 August 2019 (UTC)

Thank you! Visite fortuitement prolongée (talk) 20:34, 26 August 2019 (UTC)

exactly 1 value

Theoretical problem: how to get items with exactly 1 value of specific property? --Infovarius (talk) 10:13, 28 August 2019 (UTC)

Format constraint to exclude titles with two or more "$"

To find such titles, I'm tying to define a format constraint on Template:P that doesn't match titles with two or more "$" (?test=false in the above does that).

To make it into a format constraint, this needs to be written differently (second regex), but somehow I don't quite get there. --- Jura 11:59, 28 August 2019 (UTC)

Template:Ping: I think the RegEx ^[^$]*(\\$[^$]*)?$ will solve you problem. Here is the complete query.

Template:SPARQL

I hope this helps. --Frog23 (talk) 13:23, 28 August 2019 (UTC)

Selecting the first of several equally ranked statements

I was working on a little query about movies from certain directors and wanted to sort them by year. Here is an example for Template:Q. Template:SPARQL2 However the problem is that some movies were released in different years in different countries. For instance Template:Q was released in 1977 in the US and in 1978 in Sweden and Germany. I know I could write the query to look where the qualifier Template:P is Template:Q, but I am interested in the general solution. How can I select only one out of several equally ranked statements, either by sorting them (like in this case of the date) or by just selecting the first (sometimes this is sufficient and I don't want to have to handle multiple answers to keep my result set concise)? Thanks for any hints on how to handle this. --Frog23 (talk) 13:43, 28 August 2019 (UTC)

Template:Ping Aggregation and the use of MIN, MAX, SAMPLE, &c?

Template:SPARQL --Tagishsimon (talk) 14:12, 28 August 2019 (UTC)

Template:Ping Thanks, this works great. --Frog23 (talk) 21:01, 28 August 2019 (UTC)

Showing the year part only in a timeline

Template:SPARQL The query counts the artworks at AGSA by year and shows a timeline. Ideally, the only the YEAR part should display. MargaretRDonald (talk) 03:04, 29 August 2019 (UTC)

Template:Ping

Replace #defaultView:Timeline with #defaultView:Timeline{"hide": "?date"}

--Larske (talk) 07:44, 29 August 2019 (UTC)

Thanks, very much, Template:Ping MargaretRDonald (talk) 18:15, 29 August 2019 (UTC)

Public artworks in London by female artists

Could someone please show me how to do a query for public artworks in London where the Template:P is female? A query for public artworks in London is already at en:Wikipedia:WikiProject Public Art/London/Wikidata. Ham II (talk) 12:37, 30 August 2019 (UTC)

Template:Ping Extending the query you pointed to gives:

Template:SPARQL

which is fine for Listeria. The last line says that the ?item must have a ?creator (P170) who has a P21 gender of female; it uses wdt:P170/wdt:P21 to chain these two concepts together - a property path.

For WDQS, perhaps:

Template:SPARQL --Tagishsimon (talk) 13:42, 30 August 2019 (UTC)

This one splits out the relation ?item must have ?creator, and ?creator must be female, so that we can put a ?creator column in the results; it also uses the label service to get labels, something not required for Listeria. hth --Tagishsimon (talk) 13:43, 30 August 2019 (UTC)

Template:Ping Thank you so much! Ham II (talk) 15:19, 30 August 2019 (UTC)

Request all (or most!) data for Netflix Original Titles

Hi all, completely new to SPARQL and have what I believe is a pretty simple query (or should be). I'm trying to get a list of all Netflix Originals (films AND television series). Ideally, I also want to get some addition info, where possible, for each of these titles, such as genre, country of production, number of episodes in total, etc.

I've spent the last couple of days reading through various help guides and also watched a really helpful tutorial on YouTube. Having done this, I'm able to put together a pretty simple query, which seems to generate a list of Netflix Originals (films AND television series) with some additional info, where available, for each of these titles. Here's the code that I'm using:

This returns some 729 results. There aren't that many Netflix Originals, and the reason this number is so high is because it is returning a row for every episode of each Netflix Original television series. I only want to return one row for each series - I'm not really that interested in the level of detail of episode at the moment. I'm sure the solution to this is very simple, and that I'm probably going about things the wrong way with the code above, so I'd really appreciate any input on this.

And one last question - another the reason the list is so long is because not only are there rows for every episode, there are also multiple rows for episodes with more than one genre. If and when I manage to get this to return just titles of programmes/films, I assume it's going to give me multiple rows for each instance where there are multiple genres. Is there a way to just choose the first instance of the genre (which presumably is the primary genre) and/or have all genres appear in the same row?

Maybe something like the following can work for you. I skipped P31 entirely, but use MINUS to exclude episodes and seasons.

Template:SPARQL5 --- Jura 15:08, 30 August 2019 (UTC)

Below a version that groups by film/series: Template:SPARQL5 --- Jura 15:18, 30 August 2019 (UTC)

This works perfectly, thanks Jura! Not quite sure I understand how it works, but I can reverse engineer this, so to speak. Japeskay (talk)

Can someone tell me how to get results with distinct values in the "col1" column?

SELECT DISTINCT ?x ?y (?xLabel as ?col1) ("death" as ?col2) WHERE {

 ?x wdt:P1196 ?y . 
 ?y rdfs:label ?xLabel .
 filter (lang(?xLabel)="en")
 

}

I get results like:

x               y              col1                    col2
wd:Q65038299	 wd:Q198	war	                death
wd:Q23661525	 wd:Q198	war	                death
wd:Q55261312	 wd:Q198	war	                death
wd:Q28599127	 wd:Q198	capital punishment	death
wd:Q12023977	 wd:Q198	war	                death

I want results of the form:

x               y              col1                    col2
wd:Q28599127	 wd:Q198	capital punishment	death
wd:Q12023977	 wd:Q198	war	                death

If someone can do better, then I would prefer this:

col1                   col2
capital punishment	death
war	                death

That is, just give me results which are distinct on col1

Not sure if I'm going in the right direction, but:

Template:SPARQL --Tagishsimon (talk) 15:28, 30 August 2019 (UTC)

• Gee Template:P has gotten messy. --- Jura 15:41, 30 August 2019 (UTC)

Yes, some "creative" values are used for the Template:P property, but the seven most common values account for 99.3 percent of all Template:P claims whereas the other 243 values account for just 0.7 percent, see this query:

Template:SPARQL

--Larske (talk) 17:05, 30 August 2019 (UTC)

<raises one eyebrow on seeing> ?x wdt:P1196 [rdfs:label ?p1196label] . --Tagishsimon (talk) 17:38, 30 August 2019 (UTC)

Hey thanks, this helps a lot! Can you also answer the other queries on this page?

Filter when a specific qualifier

Hello Like this element, Template:Q which I do not wish to see, how to filter elements having both Template:P=Template:Q and any qualifyer of Template:P in Template:P  ? something like this MINUS { ?item wdt:P361 wd:Q1292279 pq:P576 _:b2.} ? Thanks ! Bouzinac (talk) 08:11, 31 August 2019 (UTC)

Template:Ping Yes, that style: MINUS { ?item p:P361 [ps:P361 wd:Q1292279 ; pq:P576 ?P576 ] . } --Tagishsimon (talk) 12:46, 31 August 2019 (UTC)

Template:Ping :-)Bouzinac (talk) 12:57, 31 August 2019 (UTC)