Wikidata:Request a query/Archive/2019/08

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

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 Gualeguaychú (Q52585) or Puerto Iguazú (Q55378) in Argentina (Q414))? --190.226.77.162 06:39, 30 July 2019 (UTC)

Hi. You might try this one

#defaultView:Map
SELECT ?border_city ?border_cityLabel ?countryLabel
(GROUP_CONCAT(DISTINCT ?natureLabel; SEPARATOR = "; ") AS ?nature) ?coords 
WHERE {
    ?border_city wdt:P31 wd:Q902814.
  ?border_city wdt:P31 ?nature. 
    OPTIONAL { ?border_city wdt:P17 ?country. }
  OPTIONAL { ?border_city wdt:P625 ?coords. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
                           ?nature rdfs:label ?natureLabel . 
                         ?border_city rdfs:label ?border_cityLabel.
                         ?country  rdfs:label ?countryLabel.}
}
GROUP BY ?border_city ?border_cityLabel ?countryLabel ?coords
order by ?countryLabel ?border_cityLabel

Try it!

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

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

#defaultView:Map
SELECT ?border_city ?border_cityLabel ?countryLabel ?coords 
WHERE {
  ?border_city wdt:P31 wd:Q902814.
  OPTIONAL { ?border_city wdt:P17 ?country. }
  OPTIONAL { ?border_city wdt:P625 ?coords. }
  # Countries in South America
  FILTER (?country IN (wd:Q77, wd:Q414, wd:Q298, wd:Q750, wd:Q419, wd:Q717, wd:Q736, wd:Q739, wd:Q733, wd:Q155))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
                         ?border_city rdfs:label ?border_cityLabel.
                         ?country  rdfs:label ?countryLabel.}
}
GROUP BY ?border_city ?border_cityLabel ?countryLabel ?coords
order by ?countryLabel ?border_cityLabel

Try it!

What I was interested in was getting the border cities of South America (Q18) (except Guyana (Q734), Suriname (Q730) and French Guiana (Q3769)) 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 border city (Q902814). So if you see some of them, please add border city (Q902814) 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:

El Amparo (Q1323834), San Antonio del Táchira (Q2602785) and Nueva Palmira (Q1023521) have two coordinates in coordinate location (P625).
Yacuíba (Q328303), Tacna (Q272795), Municipio Autónomo Atures (Q2295357), Arauca River (Q1059928), Riosucio (Q2196660) and Tabatinga (Q22064419) 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 Hjart about Tønder Municipality (Q3162907) : 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 Tønder Municipality (Q3162907) is not a city or town, but a large predominantly rural area and as such cannot fit the definition "border town". The main town Tønder (Q3223979) 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. Padborg (Q707345) 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 : SELECT ?glei ?legalName ?identifiedBy ?leiCode ?legalEntityStatus WHERE { ?glei voc:legalName ?legalName . ?glei voc:legalAddress ?legalAddress . ?glei voc:identifiedBy ?identifiedBy. ?identifiedBy voc:leiCode ?leiCode. ?glei voc:legalEntityStatus ?legalEntityStatus. ?legalAddress voc:country "KY" . #Cayman Islands }

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

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 ???

SELECT ?item ?date ?prec ?cal WHERE {
  ?item p:P569/psv:P569 [
    wikibase:timeValue ?date;
    wikibase:timePrecision ?prec;
    wikibase:timeCalendarModel ?cal
  ].
  ?item wdt:P27 wd:Q1005.
}

Try it!

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 date of birth (P569) in the item, then I want to get a literary "empty" into the cell (e.g. in Cecilia Davies (Q65660832)). Can you help me, please? Doc Taxon (talk) 16:57, 1 August 2019 (UTC)

@Doc Taxon:

SELECT ?item ?date_prec_cal WHERE {
  ?item wdt:P27 wd:Q1005.
  OPTIONAL { ?item p:P569/psv:P569 [
    wikibase:timeValue ?date;
    wikibase:timePrecision ?prec;
    wikibase:timeCalendarModel ?cal
  ].
  }
  BIND(IF(BOUND(?date),CONCAT(STRBEFORE(STR(?date),'T'),',',STR(?prec),',',STRAFTER(STR(?cal),'entity/')),'empty') AS ?date_prec_cal)
}
ORDER BY ?date_prec_cal

Try it!

--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: candidacy in election (P3602) 2017 Catalan parliamentary election (Q25226840) with the following qualifiers: electoral district (P768) Girona (Q24932380) and represents (P1268) Ciudadanos (Q1393123). --Davidpar (talk) 20:16, 1 August 2019 (UTC)

@Davidpar: this, probably:

SELECT ?item ?itemLabel 
WHERE 
{
  ?item p:P3602 [ps:P3602 wd:Q25226840 ; pq:P768 wd:Q24932380 ; pq:P1268 wd:Q1393123  ] .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],es". }
}

Try it!

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

@Tagishsimon: Thanks! It is possible to show column with candidacy in election (P3602)/series ordinal (P1545)? --Davidpar (talk) 06:47, 2 August 2019 (UTC)

@Davidpar: Seems so:

SELECT ?item ?itemLabel ?ordinal
WHERE 
{
  ?item p:P3602 [ps:P3602 wd:Q25226840 ; pq:P768 wd:Q24932380 ; pq:P1268 wd:Q1393123 ; pq:P1545 ?ordinal] .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],es". }
}

Try it!

--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.

#Search for items with descriptions both in Basque and Spanish.
#Here only people born in Bilbao
SELECT ?item ?genderLabel ?occupationLabel ?domainLabel ?positionLabel ?itemDescription ?itemLabel WHERE {
  ?item wdt:P31 wd:Q5.
  OPTIONAL { ?item wdt:P21 ?gender. }
  OPTIONAL { ?item wdt:P106 ?occupation. }
  OPTIONAL { ?item wdt:P101 ?domain. }
  OPTIONAL { ?item wdt:P39 ?position. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "eu, es". }
  FILTER(EXISTS {
    ?item schema:description ?itemdesc, ?itemdesc2.    FILTER(((LANG(?itemdesc)) = "eu") && ((LANG(?itemdesc2)) = "es"))
  })
  
  ?item wdt:P19 wd:Q8692.
}
ORDER BY (?itemLabel)

Try it!

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, @Jura1: 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)

@Jura1, Ksarasola: 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)

@Llywelyn2000: 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)

@Ksarasola: 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:

# Labels and descriptions of humans that have an article and description both in the Basque and Spanish Wikipedia
SELECT DISTINCT ?item ?esLabel ?euLabel ?itemdesc ?itemdesc2 WHERE {
  ?item wdt:P31 wd:Q5.
  ?weu schema:about ?item;
    schema:inLanguage "eu".
  ?wes schema:about ?item;
    schema:inLanguage "es".
  ?item rdfs:label ?esLabel.
  FILTER((LANG(?esLabel)) = "es")
  ?item schema:description ?itemdesc.
  FILTER((LANG(?itemdesc)) = "es")
  ?item rdfs:label ?euLabel.
  FILTER((LANG(?euLabel)) = "eu")
  ?item schema:description ?itemdesc2.
  FILTER((LANG(?itemdesc2)) = "eu")
}

Try it!

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)

@Ksarasola: 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:

they should have instance of = humans, occupation = writer.
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.
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)

@Roy17: something like that?

SELECT * WHERE {
  ?q wdt:P106 wd:Q36180 ; wdt:P31 wd:Q5 . #writer
  OPTIONAL { ?q wdt:P570 ?dod . FILTER ( year(?dod) < 1948 ) } #death before 1948
  OPTIONAL { ?q wdt:P569 ?dob . FILTER (year(?dob) < 1840) } #birth before 1840
  { ?q wdt:P103/wdt:P279* wd:Q7850 } UNION { ?q wdt:P1412/wdt:P279* wd:Q7850 }
}

Try it!

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

@VIGNERON: 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.

SELECT * WHERE {
  ?q wdt:P106 wd:Q36180 ; wdt:P31 wd:Q5 . #writer
  OPTIONAL { ?q wdt:P570 ?dod . FILTER ( year(?dod) < 1948 ) } #death before 1948
  OPTIONAL { ?q wdt:P569 ?dob . FILTER (year(?dob) < 1840) } #birth before 1840
  OPTIONAL { ?q wdt:P1559 ?name}
  OPTIONAL { ?q wdt:P18 ?photo}
  { ?q wdt:P103/wdt:P279* wd:Q7850 } UNION { ?q wdt:P1412/wdt:P279* wd:Q7850 }
}
ORDER BY DESC(?dod)

Try it!

Or

SELECT ?q ?qLabel ?dod ?dob
WHERE {
  ?q wdt:P106 wd:Q36180 ; wdt:P31 wd:Q5 . #writer
  OPTIONAL { ?q wdt:P570 ?dod . FILTER ( year(?dod) < 1948 ) } #death before 1948
  OPTIONAL { ?q wdt:P569 ?dob . FILTER (year(?dob) < 1840) } #birth before 1840
  { ?q wdt:P103/wdt:P279* wd:Q7850 } UNION { ?q wdt:P1412/wdt:P279* wd:Q7850 }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "zh,[AUTO_LANGUAGE],en" }
}
ORDER BY DESC(?dod)

Try it!

--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 located in the administrative territorial entity (P131) and Geolex ID (P6202), 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:

SELECT ?title ?article ?item ?location ?geolex WHERE {
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Generator" .
    bd:serviceParam wikibase:endpoint "en.wikipedia.org" .
    bd:serviceParam mwapi:gcmtitle "Category:United States geologic formation stubs" .
    bd:serviceParam mwapi:generator "categorymembers" .
    bd:serviceParam mwapi:gcmprop "title" .
    bd:serviceParam mwapi:gcmtype "page" .
    bd:serviceParam mwapi:gcmlimit "max" .
    ?title wikibase:apiOutput mwapi:title .
    ?item wikibase:apiOutputItem mwapi:item .
  } .
  ?article schema:about ?item; schema:isPartOf <https://en.wikipedia.org/> .
  OPTIONAL { ?item wdt:P131 ?location } .
  OPTIONAL { ?item wdt:P6202 ?geolex } .
}

Try it!

--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?

SELECT DISTINCT ?item ?itemLabel
WHERE
{
	?item wdt:P106 wd:Q10873124 .
	OPTIONAL { ?item schema:description ?itemDescription . FILTER(lang(?itemDescription)="de") }
	FILTER (!BOUND(?itemDescription))
	SERVICE wikibase:label { bd:serviceParam wikibase:language "de"}
}

Try it!

-- 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 publication date (P577) (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)

SELECT ?p (CONCAT(STR(?value), '/10') AS ?date) WHERE {
  VALUES ?p {} .
  ?p p:P577 ?statement1, ?statement2 .
  FILTER( ?statement1 != ?statement2 ) .
  ?statement1 psv:P577 [ wikibase:timePrecision 10; wikibase:timeValue ?value ] .
  ?statement2 psv:P577 [ wikibase:timePrecision 11 ] .
}

Try it!

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

Thank you very much @Matěj Suchánek:. I modified this version of the query to select the same month and year. This can be tested for VALUES ?p { wd:Q23916181 } .

SELECT ?p (CONCAT(STR(?value), '/10') AS ?date) WHERE {
  VALUES ?p {} .
  ?p p:P577 ?statement1, ?statement2 .
  FILTER( ?statement1 != ?statement2 ) .
  ?statement1 psv:P577 [ wikibase:timePrecision 10; wikibase:timeValue ?value ] .
  ?statement2 psv:P577 [ wikibase:timePrecision 11; wikibase:timeValue ?value2 ] .
  FILTER( SUBSTR(STR(?value), 1, 7) = SUBSTR(STR(?value2), 1, 7)) .
}

Try it!

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 (U.S. National Archives Identifier (P1225)) 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)

@Dominic: Here's a way to get pairs of items with the same NAID:

SELECT ?item1 ?item1Label ?item2 ?item2Label ?code {
  ?item1 wdt:P1225 ?code.
  ?item2 wdt:P1225 ?code.
  FILTER(STR(?item1) < STR(?item2))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

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 instance of (P31) -> human (Q5) that have a reference with reference URL (P854) -> http://www.parliament.cy/images/media/assetfile/VIVLIO%2050HRONA.pdf. I want in the results to have item label and page(s) (P304) of that reference. Xaris333 (talk) 00:43, 11 August 2019 (UTC)

SELECT ?item ?itemLabel ?property ?page WHERE {
  ?refHandle pr:P854 <http://www.parliament.cy/images/media/assetfile/VIVLIO%2050HRONA.pdf> .
  OPTIONAL { ?refHandle pr:P304 ?page }
  ?item ?property [ prov:wasDerivedFrom ?refHandle ]; wdt:P31 wd:Q5 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}

Try it!

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 retrieved (P813) and no other properties, (b) all statements which have a reference which contains only one property–value pair, and (c) all statements which use retrieved (P813) as a qualifier without reference URL (P854)? (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.

Items used: Stockholm (Q1754)   , writer (Q36180)   , Sweden (Q34)   , female (Q6581072)   , human (Q5)

Properties used: place of birth (P19)   , location (P276)   , located in the administrative territorial entity (P131)   , occupation (P106)   , country of citizenship (P27)   , sex or gender (P21)   , date of death (P570)   , date of birth (P569)   , instance of (P31)

SELECT DISTINCT ?item ?itemLabel ?itemDescription ?gender ?genderLabel ?pob ?pobLabel
WHERE
{
  ?item wdt:P19 / wdt:P276* / wdt:P131* wd:Q1754.
  ?item wdt:P106 wd:Q36180.
  ?item wdt:P27 wd:Q34.
  VALUES ?gender { wd:Q6581072 }.
  ?item wdt:P21 ?gender .
  { ?item wdt:P570 ?dod . FILTER( YEAR(?dod) > 1900 ) } 
  UNION { ?item wdt:P569 ?dob . FILTER( YEAR(?dob) > 1900 && YEAR(?dob) < 2001 ) } 
  ?item wdt:P31 wd:Q5 .
  ?item wdt:P19 ?pob .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],sv,en". }
}
LIMIT 1000

Try it!

--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?

SELECT ?item ?itemLabel
WHERE
{
  ?item wdt:P136 wd:Q26928598;
  wdt:P136 wd:Q7252;
  wdt:P136 wd:Q12132683 .
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,es". }
  }

Try it!

– The preceding unsigned comment was added by Lmerice (talk • contribs) at 15 augusti 2019 kl. 07.59‎ (UTC).

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 :

SELECT ?item ?itemLabel
WHERE
{
  ?item wdt:P31/wdt:P279* wd:Q571 ; #books and subclasses of book
        wdt:P136 wd:Q26928598 ; # distopia genre 
        wdt:P50 ?author . #author of the book
  ?author wdt:P21 wd:Q6581072 . #author is female
#  wdt:P136 wd:Q7252;
#  wdt:P136 wd:Q12132683 .
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,es". }
  }

Try it!

If you want all the books from these 3 genres, you must put a union between all of them

SELECT distinct ?itemLabel ?authorLabel ?genreLabel ?item
WHERE
{
  ?item wdt:P31/wdt:P279* wd:Q571 ; #books and subclasses of book
        wdt:P136 ?genre ; #genre of the book
        wdt:P50 ?author . #author of the book
  ?author wdt:P21 wd:Q6581072 . #author is female
  {{?item wdt:P136 wd:Q26928598 .} union {?item wdt:P136 wd:Q7252 .} union {?item wdt:P136 wd:Q12132683 .}} #book is a science-fiction novel, a distopia or about feminism
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,es". }
  }

Try it!

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)

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

SELECT DISTINCT ?item ?itemLabel_en ?itemLabel_ga
WHERE
{
  ?item wdt:P31/wdt:P279* wd:Q55488 . 
  ?item wdt:P17 wd:Q27 .
  OPTIONAL { ?item rdfs:label ?itemLabel_en . FILTER(lang(?itemLabel_en)="en") }
  OPTIONAL { ?item rdfs:label ?itemLabel_ga . FILTER(lang(?itemLabel_ga)="ga") }
} order by ?itemLabel_en

Try it!

--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 scholarly article (Q13442814), published in (P1433) in Open-File Report (Q53953275), without author (P50) or author name string (P2093). 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)

@Trilotat: Two approaches, fwiw.

SELECT ?item ?itemLabel 
WHERE
{
  ?item wdt:P1433 wd:Q53953275 .
  ?item wdt:P31 wd:Q13442814 .
  optional { ?item wdt:P50 ?author . }
  optional { ?item wdt:P2093 ?authorstring . }
  filter(!bound(?author) && !bound(?authorstring) )
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

SELECT ?item ?itemLabel
WHERE
{
  ?item wdt:P1433 wd:Q53953275 .
  ?item wdt:P31 wd:Q13442814 .
  filter not exists { ?item wdt:P50 [] . }
  filter not exists  { ?item wdt:P2093 [] . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

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

@Tagishsimon: 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 municipality of Catalonia (Q33146843) where its IDESCAT territorial code in Catalonia (P4335) has 43 in the two first digits of the code. Thanks, Amadalvarez (talk) 05:29, 16 August 2019 (UTC)

@Amadalvarez:

SELECT ?item ?itemLabel (?p4335 AS ?IDESCAT_code) {
  ?item wdt:P31 wd:Q33146843 .
  ?item wdt:P4335 ?p4335 .
  FILTER(STRSTARTS(?p4335,'43'))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?p4335

Try it!

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

Thanks, Larske. 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:

select ?player ?playerLabel ?event (COUNT(?team) as ?teams) 
WHERE {
  ?player  wdt:P106 wd:Q937857.
  ?player p:P1344  ?event. 
?event ps:P1344 wd:Q28937555 ;  #how to change so that I can list all instances, not just 1 individual
        pq:P54 ?team ;
         OPTIONAL { ?event pq:P1350 ?matches;}
         OPTIONAL { ?event pq:P1351 ?goals ;}
service wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }  
}
GROUP BY ?player ?playerLabel ?event ?teams HAVING ( ?teams > 1 )
ORDER BY ?teamLabel ?playerLabel

Try it!

--[

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

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

select ?player ?playerLabel ?event ?eventLabel (COUNT(?team) as ?teams) 
WHERE {
  ?player  wdt:P106 wd:Q937857.
  ?player p:P1344  ?statement. 
  ?statement ps:P1344 ?event ;  #how to change so that I can list all instances, not just 1 individual
        pq:P54 ?team ;
         OPTIONAL { ?event pq:P1350 ?matches;}
         OPTIONAL { ?event pq:P1351 ?goals ;}
service wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }  
}
GROUP BY ?player ?playerLabel ?event ?eventLabel ?teams  HAVING ( ?teams > 1 )
ORDER BY ?teamLabel ?playerLabel

Try it!

--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"

SELECT ?item ?itemLabel ?occupationLabel ?dateofbirth ?dateofdeath (URI(CONCAT("https://www.animenewsnetwork.com/encyclopedia/people.php?id=",?ANNid)) AS ?ANN)
WHERE {
  ?item wdt:P31 wd:Q5 .
  ?item wdt:P21 wd:Q6581072 .
  ?item wdt:P1982 ?ANNid .
  { ?item wdt:P106 wd:Q3455803 } UNION { ?item wdt:P106 wd:Q2059704 } UNION { ?item wdt:P106 wd:Q11284414 } UNION { ?item wdt:P106 wd:Q2526255 }
  ?item wdt:P106 ?occupation
  OPTIONAL { ?item wdt:P569 ?dateofbirth . }
  OPTIONAL { ?item wdt:P570 ?dateofdeath . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,ja" }
}

Try it!

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

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

SELECT ?item ?itemLabel (group_concat(distinct ?occupationLabel;separator=", ") as ?occlabel) ?dateofbirth ?dateofdeath  (URI(CONCAT("https://www.animenewsnetwork.com/encyclopedia/people.php?id=",?ANNid)) AS ?ANN)
WHERE {
  ?item wdt:P31 wd:Q5 .
  ?item wdt:P21 wd:Q6581072 .
  ?item wdt:P1982 ?ANNid .
  { ?item wdt:P106 wd:Q3455803 } UNION { ?item wdt:P106 wd:Q2059704 } UNION { ?item wdt:P106 wd:Q11284414 } UNION { ?item wdt:P106 wd:Q2526255 }
  ?item wdt:P106 ?occupation
  OPTIONAL { ?item wdt:P569 ?dateofbirth . }
  OPTIONAL { ?item wdt:P570 ?dateofdeath . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,ja" .
                         ?occupation rdfs:label ?occupationLabel .
                         ?item rdfs:label ?itemLabel .}
} group by ?item ?itemLabel ?dateofbirth ?dateofdeath ?ANNid

Try it!

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

@Tagishsimon: 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.

#defaultView:Map
SELECT ?sign ?signLabel ?pobLabel ?podLabel ?coorb ?coord ?layer WHERE {
  VALUES ?item { wd:Q66486391 }
  ?item wdt:P1891 ?sign.
  ?sign wdt:P19 ?pob .
  ?sign wdt:P20 ?pod .
  {?pob wdt:P625 ?coord .} union {?pod wdt:P625 ?coord .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Jklamo (talk) 23:03, 18 August 2019 (UTC)

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

#defaultView:Map
SELECT ?sign ?signLabel ?pobLabel ?podLabel ?coorb ?coord ?layer WHERE {
  VALUES ?item { wd:Q66486391 }
  ?item wdt:P1891 ?sign.
  ?sign wdt:P19 ?pob .
  ?sign wdt:P20 ?pod .
  {?pob wdt:P625 ?coord .} union {?pod wdt:P625 ?coord .}
  optional {?pob wdt:P625 ?pobcoord .} 
  optional {?pod wdt:P625 ?podcoord .}  
  bind(if(?coord = ?pobcoord && ?coord = ?podcoord, "pob & pod",if(?coord = ?pobcoord,"pob","pod")) as ?layer)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

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

@Jklamo: 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 CAS Registry Number (P231) number? I know that this number is present in the wikidata item blue chamomile oil (Matricaria chamomilla) (Q60863109) 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)

@Ελλίντερεστ:

select ?item { ?item wdt:P231 "8015-92-7" . }

Try it!

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

Layer does not display

I have the following query:

The following query uses these:

Items: United States of America (Q30)   , Spanish (Q1321)

Properties: twinned administrative body (P190)   , country (P17)   , official language (P37)   , coordinate location (P625)   , population (P1082)   , inception (P571)   , image (P18)

Features: map (Q24515275)  

#defaultView:Map
SELECT DISTINCT ?twin ?twinLabel ?geo ?pop ?image ?twinyear ?layer WHERE {
  ?city wdt:P190 ?twin .
  MINUS {?city wdt:P17 wd:Q30}
  ?twin wdt:P17 ?country .
  ?country wdt:P37 wd:Q1321 .
  ?twin wdt:P625 ?geo .
  ?twin wdt:P1082 ?pop .
  ?twin wdt:P571 ?inception .
  BIND(STR(YEAR(?inception)) AS ?twinyear)
  BIND(IF(?twinyear < 1700, "Pre-1700", 
          IF(?twinyear < 1751, "1700-1750", 
             IF(?twinyear < 1801, "1751-1800", 
                IF(?twinyear < 1851, "1801-1850", 
                   IF(?twinyear < 1901, "1851-1900", 
                      IF(?twinyear < 1951, "1901-1950", "Post-1950") 
                     ) 
                  ) 
               ) 
            )
         ) AS ?layer )
  FILTER (?pop < 100000 )
  FILTER (?pop > 10000 )
  OPTIONAL {?twin wdt:P18 ?image}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

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:

The following query uses these:

Items: United States of America (Q30)   , Spanish (Q1321)

Properties: twinned administrative body (P190)   , country (P17)   , official language (P37)   , coordinate location (P625)   , population (P1082)   , inception (P571)   , image (P18)

Features: map (Q24515275)  

#defaultView:Map
SELECT DISTINCT ?twin ?twinLabel ?geo ?pop ?image ?twinyear ?layer WHERE {
  ?city wdt:P190 ?twin .
  MINUS {?city wdt:P17 wd:Q30}
  ?twin wdt:P17 ?country .
  ?country wdt:P37 wd:Q1321 .
  ?twin wdt:P625 ?geo .
  ?twin wdt:P1082 ?pop .
  ?twin wdt:P571 ?inception .
  BIND(YEAR(?inception) AS ?twinyear)
  BIND(IF(?twinyear < 1700, "Pre-1700", 
          IF(?twinyear < 1751, "1700-1750", 
             IF(?twinyear < 1801, "1751-1800", 
                IF(?twinyear < 1851, "1801-1850", 
                   IF(?twinyear < 1901, "1851-1900", 
                      IF(?twinyear < 1951, "1901-1950", "Post-1950") 
                     ) 
                  ) 
               ) 
            )
         ) AS ?layer )
  FILTER (?pop < 100000 )
  FILTER (?pop > 10000 )
  OPTIONAL {?twin wdt:P18 ?image}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--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 Leadscope ID (P2083), 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:

#Author name strings 
SELECT ?item ?itemLabel
{
  ?item wdt:P2093 "Jane Doe" .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

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:

#Author name strings 
SELECT ?item ?itemLabel ?value 
{
  ?item wdt:P2093 ?value .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

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)

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

@Mfchris84: 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)

@Dirac: 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)

@Mfchris84:, 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)

@Dirac: Funny stats - but as @Jura1: 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)

@Mfchris84: 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)

@Mfchris84, Jura1:, I've written an R script to address this. It moves author from author name string (P2093) to author (P50) 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)

@Dirac: @Mfchris84: 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)

@Hadi: 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:

SELECT ?item ?itemLabel ?HDS_ID
WHERE 
{
  ?item wdt:P902 ?HDS_ID.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de,fr". }
} order by ?itemLabel

Try it!

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/> .

SELECT ?item ?itemLabel ?HDS_ID ?sitelink with { SELECT ?item ?sitelink ?HDS_ID
WHERE 
{
  ?item wdt:P902 ?HDS_ID.
  ?sitelink ^schema:name ?article .
  ?article schema:about ?item ;
          schema:isPartOf <https://en.wikipedia.org/> .
} } as %i
where
{
  include %i
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de,fr". }
} order by ?itemLabel

Try it!

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 – The preceding unsigned comment was added by 77.222.144.250 (talk • contribs) at 14:53, 21 August 2019‎ (UTC).

It's as easy as

SELECT ?item ?itemLabel WHERE {
  ?item wdt:P31/wdt:P279* wd:Q7930989.
  ?item wdt:P17 wd:Q159.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--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.

Items used: clinical trial (Q30612)

Properties used: instance of (P31)   , ClinicalTrials.gov ID (P3098)

SELECT ?item ?itemLabel ?nctid ?enwikipediaArticleTitle
WHERE 
{
  ?item wdt:P31 wd:Q30612.
  ?item wdt:P3098 ?nctid
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it! EncycloABC (talk) 18:48, 21 August 2019 (UTC)

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

SELECT ?item ?itemLabel ?nctid ?sitelink
WHERE 
{
  ?item wdt:P31 wd:Q30612.
  OPTIONAL { ?item wdt:P3098 ?nctid . }
  OPTIONAL { ?sitelink ^schema:name ?article .
             ?article schema:about ?item ;
                      schema:isPartOf <https://en.wikipedia.org/> . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--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)

SELECT ?item ?itemLabel ?species {
  ?item wdt:P31 wd:Q5 ; wdt:P570 ?dod. FILTER(YEAR(?dod) < 1949) # people who died before 1949
  ?species schema:about ?item; schema:isPartOf <https://species.wikimedia.org/> . # with a page in Wikispecies
  MINUS { ?source schema:about ?item . filter(contains(str(?source),"wikisource.org")) } # but no page in any Wikisource
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

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

#Australian paintings/drawing/print/sculpture by institution and female creator

SELECT ?creatorLabel ?institutionLabel (COUNT(*) AS ?count) WHERE {
  {?artwork wdt:P31 wd:Q3305213.}  # painting
  UNION
   {?artwork wdt:P31 wd:Q11060274.}# print
  UNION 
   {?artwork wdt:P31 wd:Q93184.}   # drawing
  UNION 
   {?artwork wdt:P31 wd:Q860861.}  # sculpture
  ?artwork wdt:P170 ?creator.      # define variable creator
  ?creator wdt:P21 wd:Q6581072.    # creator is female
  ?institution wdt:P131 wd:Q3258.  # institution in ACT
  ?artwork wdt:P195 ?institution.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?creatorLabel ?institutionLabel
ORDER BY ?count ?creatorLabel

Try it!

MargaretRDonald (talk) 13:42, 23 August 2019 (UTC)

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

Thanks very much, @Tagishsimon: 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?

@Xaris333:

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

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

@TomT0m: can you change it to include also the items with position held (P39) -> list of Ministers of Education and Culture of Cyprus (Q66581831)? Xaris333 (talk) 14:00, 19 August 2019 (UTC)

@Xaris333:

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)

@TomT0m: 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 Christodoulos Veniamin (Q65807458) 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)

The following query uses these:

Items: Minister of Defence (Q65243720)   , list of Ministers of Education and Culture of Cyprus (Q66581831)

Properties: position held (P39)  

, start time (P580)  

, end time (P582)  

, series ordinal (P1545)  

, replaces (P1365)  

, replaced by (P1366)  

SELECT ?office ?item ?itemLabel ?itemDescription ?start ?end ?order ?predecessor ?successor
{
  ?item p:P39 ?fc_stmt .
  ?fc_stmt ps:P39 ?office ; pq:P580 ?start  .
  values ?office { wd:Q65243720 wd:Q66581831 }
  optional { ?fc_stmt pq:P582 ?end } 
  optional { ?fc_stmt pq:P1545 ?order }
  optional { ?fc_stmt  pq:P1365 ?predecessor }
  optional { ?fc_stmt  pq:P1366 ?successor}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "el" }
}

Try it!

@Tagishsimon: 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)

@Xaris333: An amended query in WDQS seems straightforward:

SELECT ?officeLabel ?item ?itemLabel ?itemDescription ?start ?end ?order ?predecessorLabel ?successorLabel ?ordernumber
{
  ?item p:P39 ?fc_stmt .
  ?fc_stmt ps:P39 ?office ; pq:P580 ?start  .
  values ?office { wd:Q65243720 wd:Q66581831 }
  optional { ?fc_stmt pq:P582 ?end } 
  optional { ?fc_stmt pq:P1545 ?order 
    bind(if(strlen(str(?order))=1,concat("0",?order),?order) as  ?ordernumber)            
           }
  optional { ?fc_stmt  pq:P1365 ?predecessor }
  optional { ?fc_stmt  pq:P1366 ?successor}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "el" }
} order by ?ordernumber

Try it!

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 Polycarpos Georkatzis (Q7226186) have two rows in WDQS, one in Listeria; cause is that the position statement in Q7226186 has two replaces (P1365) 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. (Polycarpos Georkatzis (Q7226186) is a unique situation). Xaris333 (talk) 04:24, 24 August 2019 (UTC)

age not calculating/appearing

SELECT DISTINCT ?female ?femaleLabel ?dob ?birthplaceLabel ?dod ?age ?deathplaceLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?female wdt:P21 wd:Q6581072;             # is female
          wdt:P6379 wd:Q1464509.           # has works in the collection of the NGV
  bind(year(?dod)-year(?dob) as ?age )     # crude calculation of age
  OPTIONAL { ?female wdt:P569 ?dob. }
  OPTIONAL { ?female wdt:P570 ?dod. }
  OPTIONAL { ?female wdt:P19 ?birthplace. }
  OPTIONAL { ?female wdt:P20 ?deathplace. }
}

Try it!

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

@MargaretRDonald: Order (sometimes) matters:

SELECT DISTINCT ?female ?femaleLabel ?dob ?birthplaceLabel ?dod ?age ?deathplaceLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?female wdt:P21 wd:Q6581072;             # is female
          wdt:P6379 wd:Q1464509.           # has works in the collection of the NGV
  OPTIONAL { ?female wdt:P569 ?dob. }
  OPTIONAL { ?female wdt:P570 ?dod. }
  OPTIONAL { ?female wdt:P19 ?birthplace. }
  OPTIONAL { ?female wdt:P20 ?deathplace. }
  bind(year(?dod)-year(?dob) as ?age )     # crude calculation of age
}

Try it!

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

Thanks again. @Tagishsimon: 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? – The preceding unsigned comment was added by Midleading (talk • contribs).

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:

SELECT ?item WHERE {
  ?item wdt:P31 wd:Q7397.
  ?item wdt:P31 wd:Q506883.
}

Try it!

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 MPlayer (Q131344), media player software (Q210337) 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:

SELECT ?item WHERE {
  ?item p:P31/ps:P31 wd:Q7397.
  ?item p:P31/ps:P31 wd:Q506883.
}

Try it!

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.

SELECT ?item WHERE {
  ?item wdt:P275 ?license.
  ?license wdt:P31 ?subclass.
  ?subclass wdt:P279 wd:Q1156659.
  MINUS { ?item wdt:P31 wd:Q341. }
}

Try it!

--Reseletti (talk) 12:42, 26 August 2019 (UTC)

@Reseletti: Same issue as above. Try:

SELECT ?item WHERE {
  ?item wdt:P275 ?license.
  ?license wdt:P31/wdt:P279* wd:Q1156659.
  MINUS { ?item p:P31/ps:P31 wd:Q341. }
}

Try it!

--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

SELECT ?item ?itemLabel?taxon_name ?NT_Flora_ID  WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
    ?item wdt:P5953 ?NT_Flora_ID.
    ?item wdt:P225 ?taxon_name.
    ?item wdt:P171 wd:Q81666.   # whose genus is Acacia
  MINUS
  {?article schema:about ?item. # and have an article about them
  ?article schema:isPartOf <https://en.wikipedia.org/> . # on *English* Wikipedia}
  }
}
ORDER BY ?itemLabel ?item ?taxon_name

Try it!

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)

@MargaretRDonald: 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:

SELECT ?item ?itemLabel ?taxon_name ?NT_Flora_ID  ?link WHERE {
  ?item rdfs:label ?itemLabel . filter(lang(?itemLabel)="en")                      
  ?item wdt:P5953 ?NT_Flora_ID.
  ?item wdt:P225 ?taxon_name.
  ?item wdt:P171 wd:Q81666.   # whose genus is Acacia
  bind(concat("[[",?itemLabel,"]]") as ?link)
  MINUS
  {?article schema:about ?item. # and have an article about them
  ?article schema:isPartOf <https://en.wikipedia.org/> . # on *English* Wikipedia}
  }
}
ORDER BY ?itemLabel ?item ?taxon_name

Try it!

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

Thanks, once more, @Tagishsimon:. 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)

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

SELECT ?woven_fabric ?woven_fabricLabel (group_concat(distinct ?fabrication_method;separator=", ") as ?fm) (group_concat(distinct ?fabrication_methodLabel;separator=", ") as ?fmL) (group_concat(distinct ?material_used;separator=", ") as ?mu) (group_concat(distinct ?material_usedLabel;separator=", ") as ?mul) (group_concat(distinct ?described_by_source;separator=", ") as ?dbs) (group_concat(distinct ?described_by_sourceLabel;separator=", ") as ?dbsL) 
WHERE 
{
  ?woven_fabric wdt:P279 wd:Q5295538.
  OPTIONAL { ?woven_fabric wdt:P2079 ?fabrication_method. }
  OPTIONAL { ?woven_fabric wdt:P186 ?material_used. }
  OPTIONAL { ?woven_fabric wdt:P1343 ?described_by_source. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
                           ?fabrication_method rdfs:label ?fabrication_methodLabel .
                           ?material_used rdfs:label ?material_usedLabel . 
                           ?described_by_source rdfs:label ?described_by_sourceLabel .}
} group by ?woven_fabric ?woven_fabricLabel
LIMIT 1000

Try it!

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

@Tagishsimon: 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)

@PKM: this

SELECT ?woven_fabric ?woven_fabricLabel (group_concat(distinct ?fm1;separator=", ") as ?fm) (group_concat(distinct ?fabrication_methodLabel;separator=", ") as ?fmL) (group_concat(distinct ?mu1;separator=", ") as ?mu) (group_concat(distinct ?material_usedLabel;separator=", ") as ?mul) (group_concat(distinct ?dbs1;separator=", ") as ?dbs) (group_concat(distinct ?described_by_sourceLabel;separator=", ") as ?dbsL) 
WHERE 
{
  ?woven_fabric wdt:P279 wd:Q5295538.
  OPTIONAL { ?woven_fabric wdt:P2079 ?fabrication_method. bind(replace(str(?fabrication_method),"http://www.wikidata.org/entity/","") as ?fm1) }
  OPTIONAL { ?woven_fabric wdt:P186 ?material_used. bind(replace(str(?material_used),"http://www.wikidata.org/entity/","") as ?mu1) }
  OPTIONAL { ?woven_fabric wdt:P1343 ?described_by_source. bind(replace(str(?described_by_source),"http://www.wikidata.org/entity/","") as ?dbs1) }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
                           ?fabrication_method rdfs:label ?fabrication_methodLabel .
                           ?material_used rdfs:label ?material_usedLabel . 
                           ?described_by_source rdfs:label ?described_by_sourceLabel .
                           ?woven_fabric rdfs:label ?woven_fabricLabel . }
} group by ?woven_fabric ?woven_fabricLabel
LIMIT 1000

Try it!

@Tagishsimon: 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)

SELECT DISTINCT ?item ?itemLabel ?poblacio ?superficie ?imatge_de_l_escut_d_armes ?pointInTime  WHERE {
  ?item wdt:P31 wd:Q33146843;
    wdt:P4335 ?p4335.
  FILTER(STRSTARTS(?p4335, "25"))
 
  OPTIONAL { ?item wdt:P1082 ?poblacio.}
  OPTIONAL { ?item wdt:P2046 ?superficie. }
  OPTIONAL { ?item wdt:P94 ?imatge_de_l_escut_d_armes. }
 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 100

Try it!

@Amadalvarez:

SELECT DISTINCT ?item ?itemLabel ?poblacio ?superficie ?imatge_de_l_escut_d_armes ?pointInTime WHERE {
  ?item wdt:P31 wd:Q33146843;
    wdt:P4335 ?p4335.
  FILTER(STRSTARTS(?p4335, "25"))
  OPTIONAL { ?item p:P1082 [ps:P1082 ?poblacio; pq:P585 ?pointInTime; wikibase:rank wikibase:PreferredRank] . }
  OPTIONAL { ?item wdt:P2046 ?superficie. }
  OPTIONAL { ?item wdt:P94 ?imatge_de_l_escut_d_armes. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 100

Try it!

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

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

Malformed count

# Australian women artist with institution count

SELECT ?artist ?artistLabel (COUNT(?institution) as ?count) WHERE {

   {?artist wdt:P106 wd:Q483501.}     # any form of artist
  UNION
   {?artist wdt:P106 wd:Q11569986 .}  # occupation printmaker
  UNION 
   {?artist wdt:P106 wd:Q1028181.}    # occupation painter
  UNION 
   {?artist wdt:P106 wd:Q1281618.}    # occupation sculptor
 
   ?artist wdt:P31 wd:Q5.             # is human
   ?artist wdt:P21 wd:Q6581072.       # is female
   ?artist wdt:P6379 ?institution.    # has works in ?institution
   ?institution wdt:P131* wd:Q408.    # institution in Australia

  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?artist
ORDER BY DESC (?count)

Try it!

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

@MargaretRDonald: 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 - Barbara Hanrahan (Q4858957) 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.

# Australian women artist with institution count

SELECT ?artist ?artistLabel (COUNT(?institution) as ?count) with { select distinct ?artist WHERE 
{
   {?artist wdt:P106 wd:Q483501.}     # any form of artist
  UNION
   {?artist wdt:P106 wd:Q11569986 .}  # occupation printmaker
  UNION 
   {?artist wdt:P106 wd:Q1028181.}    # occupation painter
  UNION 
   {?artist wdt:P106 wd:Q1281618.}    # occupation sculptor
   ?artist wdt:P31 wd:Q5.             # is human
   ?artist wdt:P21 wd:Q6581072.       # is female
   ?artist wdt:P6379/wdt:P131* wd:Q408.    # has work in institution in Australia
   hint:Prior hint:gearing "forward".
} } as %i where
{
  include %i
  ?artist wdt:P6379 ?institution.    # has works in ?institution
  ?institution wdt:P131* wd:Q408.    # institution in Australia
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?artist ?artistLabel
ORDER BY DESC (?count)

Try it!

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

# Australian women artist with institution count

SELECT ?artist ?artistLabel (COUNT(?institution) as ?count) with { select distinct ?artist WHERE 
{
   ?artist wdt:P106 ?occ.
   VALUES ?occ {wd:Q483501 wd:Q11569986 wd:Q1028181 wd:Q1281618}    # occupations
   ?artist wdt:P31 wd:Q5.             # is human
   ?artist wdt:P21 wd:Q6581072.       # is female
   ?artist wdt:P6379/wdt:P131* wd:Q408.    # has work in institution in Australia
   hint:Prior hint:gearing "forward".
} } as %i where
{
  include %i
  ?artist wdt:P6379 ?institution.    # has works in ?institution
  ?institution wdt:P131* wd:Q408.    # institution in Australia
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?artist ?artistLabel
ORDER BY DESC (?count)

Try it!

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

Thanks, yet again, @Tagishsimon:. 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)

@Visite fortuitement prolongée: something along these lines. ?items with more than 2 P279s get multiple rows - such as Habla Congo (Q5636912).

SELECT ?item ?itemLabel ?P279_1 ?P279_1Label ?P279_2 ?P279_2Label WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q20162172 .
  ?item wdt:P279 ?P279_1 .
  ?item wdt:P279 ?P279_2 .
  filter(str(?P279_1) < str(?P279_2) ) 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--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 "$"

Items used: George Washington (Q18689133)   , The $5,000,000 Counterfeiting Plot (Q7711690)   , Empirical verification of the even Goldbach conjecture and computation of prime gaps up to $4\cdot 10^{18}$ (Q29302883)   , H_2_O in stellar atmospheres. II. ISO spectra of cool red giants and hydrostatic models (Q29396284)   , Deficiency in Cardiac Dystrophin Affects the Abundance of the $\alpha$ -/ $\beta$ -Dystroglycan Complex (Q24806537)   , Measurement of three-jet distributions sensitive to the gluon spin ine + e − annihilations at $$\sqrt s = 91$$ GeV (Q29301301)   , Production, characterization and operation of 76Ge enriched BEGe detectors in GERDA (Q27012930)

Properties used: title (P1476)

SELECT ?item ?value ?test ?formatconstraint
{
  VALUES ?item { wd:Q18689133 #zero pass
                           wd:Q7711690 #one   pass 
                           wd:Q29302883 wd:Q29396284 #two fail
                           wd:Q24806537 #more fail
                           wd:wd:Q29301301 wd:Q27012930 #pair fail                       
                }          
  ?item wdt:P1476 ?value .
  BIND( !REGEX( STR( ?value ), "\\$[^$]*\\$" )  as ?test) .          
  BIND( REGEX( STR( ?value ), "^[^$]+$" )  as ?formatconstraint) .          
} 
ORDER BY ?test

Try it!

To find such titles, I'm tying to define a format constraint on title (P1476) 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)

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

SELECT ?item ?value ?test ?formatconstraint
{
  VALUES ?item { wd:Q18689133 #zero pass
                           wd:Q7711690 #one   pass 
                           wd:Q29302883 wd:Q29396284 #two fail
                           wd:Q24806537 #more fail
                           wd:wd:Q29301301 wd:Q27012930 #pair fail                       
                }          
  ?item wdt:P1476 ?value .
  BIND( !REGEX( STR( ?value ), "\\$[^$]*\\$" )  as ?test) .          
  BIND( REGEX( STR( ?value ), "^[^$]*(\\$[^$]*)?$" )  as ?formatconstraint) .          
} 
ORDER BY ?test

Try it!

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 Steven Spielberg (Q8877).

The following query uses these:

Items: film (Q11424)   , Steven Spielberg (Q8877)

Properties: instance of (P31)  

, subclass of (P279)  

, director (P57)  

, publication date (P577)  

#movies by Steven Spielberg sorted by year
SELECT DISTINCT ?movie ?movieLabel ?year WHERE {
  ?movie wdt:P31/wdt:P279* wd:Q11424.
  ?movie wdt:P57 wd:Q8877.
  ?movie wdt:P577 ?date.
  BIND (year(?date) as ?year)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} ORDER BY ?year

Try it!

However the problem is that some movies were released in different years in different countries. For instance Close Encounters of the Third Kind (Q320588) 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 place of publication (P291) is United States of America (Q30), 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)

@Frog23: Aggregation and the use of MIN, MAX, SAMPLE, &c?

#movies by Steven Spielberg sorted by year
SELECT DISTINCT ?movie ?movieLabel (min(?year) as ?myear) WHERE {
  ?movie wdt:P31/wdt:P279* wd:Q11424.
  ?movie wdt:P57 wd:Q8877.
  ?movie wdt:P577 ?date.
  BIND (year(?date) as ?year)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?movie ?movieLabel ORDER BY ?year

Try it!

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

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

Showing the year part only in a timeline

# AGSA artworks/ paintings/drawing/print/sculpture by year of inceptin
#defaultView:Timeline
SELECT DISTINCT ?date ?year  (COUNT(?artwork) AS ?count) WHERE {
  {?artwork wdt:P31 wd:Q3305213.}    # painting
  UNION
   {?artwork wdt:P31 wd:Q11060274.}  # print
  UNION 
   {?artwork wdt:P31 wd:Q93184.}     # drawing
  UNION 
   {?artwork wdt:P31 wd:Q860861.}    # sculpture
    UNION 
   {?artwork wdt:P31 wd:Q42314818.}  # visual art
   ?artwork wdt:P195 wd:Q705557.     # Art work is in AGSA
   ?artwork wdt:P571 ?date
  BIND(str(YEAR(?date)) AS ?year)           
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?date ?year

Try it!

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)

@MargaretRDonald:

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

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

Thanks, very much, @Larske: 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 creator (P170) 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)

@Ham II: Extending the query you pointed to gives:

SELECT ?item WHERE 
{ 
  ?item wdt:P136 wd:Q557141 . 
  ?item wdt:P131 ?sub0 . 
  ?sub0 (wdt:P131)* wd:Q23939248 .
  ?item wdt:P170/wdt:P21 wd:Q6581072 .
}

Try it!

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:

SELECT ?item ?itemLabel ?creator ?creatorLabel WHERE 
{ 
  ?item wdt:P136 wd:Q557141 . 
  ?item wdt:P131 ?sub0 . 
  ?sub0 (wdt:P131)* wd:Q23939248 .
  ?item wdt:P170 ?creator .
  ?creator wdt:P21 wd:Q6581072 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--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)

@Tagishsimon: 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:

SELECT ?titleLabel ?item ?itemLabel ?genreLabel ?locationLabel
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q2431196. # instances of subclass audiovisual works - have done this because I want to find all films AND television series. Would UNION be another way to approach this?
  ?item wdt:P449 wd:Q907311. # original network is Netflix
  ?item wdt:P179 ?title .  # can't really explain how/why I've done this! But it seems to be the best way to find and display the title of a TV series
  OPTIONAL {?title wdt:P136 ?genre. }
  OPTIONAL {?title wdt:P840 ?location. } # these are just two optional values. I would like more in future, such as number of episodes, etc. 
                                         # But I'm keeping it simple for the moment
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  } ORDER BY ?titleLabel

Try it!

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?

– The preceding unsigned comment was added by [[User:|?]] ([[User talk:|talk]] • contribs).

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

Items used: Netflix (Q907311)   , television series episode (Q21191270)   , television series season (Q3464665)

Properties used: original broadcaster (P449)   , title (P1476)   , genre (P136)   , narrative location (P840)   , instance of (P31)

SELECT ?title ?item ?itemLabel ?itemDescription ?genreLabel ?narrlocationLabel
WHERE 
{
  ?item wdt:P449 wd:Q907311.
  OPTIONAL {?item wdt:P1476 ?title }  
  OPTIONAL {?item wdt:P136 ?genre. }
  OPTIONAL {?item wdt:P840 ?narrlocation. }
  MINUS { ?item wdt:P31 wd:Q21191270 }
  MINUS { ?item wdt:P31 wd:Q3464665 }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} 
ORDER BY ?title

Try it! --- Jura 15:08, 30 August 2019 (UTC)

Below a version that groups by film/series:

Items used: Netflix (Q907311)   , television series episode (Q21191270)   , television series season (Q3464665)

Properties used: original broadcaster (P449)   , title (P1476)   , genre (P136)   , narrative location (P840)   , instance of (P31)

SELECT
  ?item ?itemLabel ?itemDescription 
  (SAMPLE(?title) as ?title) 
  (GROUP_CONCAT(DISTINCT ?genreLabel; separator= ", ") as ?genres) 
  (GROUP_CONCAT(DISTINCT ?locationLabel; separator= ", ") as ?narrlocs) 
WHERE
{
    { SELECT ?item ?itemLabel ?itemDescription ?title ?genreLabel ?locationLabel 
    WHERE 
    {
        hint:Query hint:optimizer "None".
        ?item wdt:P449 wd:Q907311.
        OPTIONAL {?item wdt:P1476 ?title }  
        OPTIONAL {?item wdt:P136 ?genre. }
        OPTIONAL {?item wdt:P840 ?location. } 
        MINUS { ?item wdt:P31 wd:Q21191270 }
        MINUS { ?item wdt:P31 wd:Q3464665 }
      SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
    }
   }
}          
GROUP BY ?item ?itemLabel ?itemDescription
ORDER BY ?title

Try it! --- 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:

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

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

Try it!

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

Gee manner of death (P1196) has gotten messy. --- Jura 15:41, 30 August 2019 (UTC)

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

SELECT DISTINCT ?p1196label (COUNT(?x) AS ?count) WHERE {
 ?x wdt:P1196 [rdfs:label ?p1196label] .
 filter (lang(?p1196label)="en")
} group by ?p1196label
ORDER BY DESC(?count) ?p1196label

Try it!

--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, Turnpike Trust Parish Boundary Marker (Q26422032) which I do not wish to see, how to filter elements having both part of (P361)=political border (Q1292279) and any qualifyer of dissolved, abolished or demolished date (P576) in part of (P361) ? something like this MINUS { ?item wdt:P361 wd:Q1292279 pq:P576 _:b2.} ? Thanks ! Bouzinac (talk) 08:11, 31 August 2019 (UTC)

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

@Tagishsimon: :-)Bouzinac (talk) 12:57, 31 August 2019 (UTC)

Wikidata:Request a query/Archive/2019/08

Border cities of a country

Federated query with lei.info

bind, coalesce or concat ???

Candidacies by electoral district

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

Chinese writers whose copyright expired

Getting QIDs from a en:Wikipedia category page?

Chess players without description

Select time in two statements with P577

Identifiers used in multiple Wikidata items

P304 of a reference

References with implicit URL

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

A query about genre books

Railway stations in Ireland

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

Select items where a property starts by xx

List instances of multiple instances of same qualifier per property

Avoiding duplicates

Layers by property

Find a specific CAS number I'm searching for

Layer does not display

Authors most cited with no entry in Wikidata

HDS and WP

Get cities and town

get english wikipedia URL (page) for wikidata Q item

Old taxonomists, missing from Wikisource

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

Wikidata list

age not calculating/appearing

List of failed space missions

Newbie problems with sparql

Simple intersect

More complex matching

Generating labels with pairs of square brackets

Border posts of EU

Fabrics by weave and fiber

Get a qualifier

Malformed count

more than 1 value

exactly 1 value

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

Selecting the first of several equally ranked statements

Showing the year part only in a timeline

Public artworks in London by female artists

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

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

Filter when a specific qualifier

Navigation menu

Search