Shortcut: WD:RAQ

Wikidata:Request a query

From Wikidata
(Redirected from Wikidata:RAQ)
Jump to navigation Jump to search

Request a query
Fishing in the Wikidata river requires both an idea where to look for fish and a suitable fishing method. If you have the former, this page can help you find the latter.

This is a page where SPARQL 1.1 Query Language (Q32146616) queries can be requested. Please provide feedback if a query is written for you.

For sample queries, see Examples. Property talk pages include also summary queries for these.

For help writing your own queries, or other questions about queries, see Wikidata talk:SPARQL query service/queries.

Help resources about Wikidata Query Service (Q20950365) and SPARQL: Wikidata:SPARQL query service/Wikidata Query Help and Category:SPARQL.

On this page, old discussions are archived. An overview of all archives can be found at this page's archive index. The current archive is located at 2018/12.

Project
chat

Lexicographical
data

Administrators'
noticeboard

Development
team

Translators'
noticeboard

Request
a query

Requests
for deletions

Requests
for comment

Bot
requests

Requests
for permissions

Property
proposal

Properties
for deletion

Partnerships
and imports

Interwiki
conflicts

Bureaucrats'
noticeboard

Contents

Slide show with images[edit]

I am trying to make a slideshow with automatic looping over the a set of images returned from a WDQS query. I have made some progress on a page that loops via CCS and Javascript. I was wondering if there is a simpler way. For instance, with SAMPLE and HTML meta refresh. This will not work:

#defaultView:ImageGrid
SELECT (SAMPLE(DISTINCT ?image) AS ?image) WHERE {
  ?image ^wdt:P18 / wdt:P50 / (wdt:P108| wdt:P463 | wdt:P1416/wdt:P361*) wd:Q24283660 .
}

Try it!

 – The preceding unsigned comment was added by Fnielsen (talk • contribs) at 00:51, 21 February 2018‎ (UTC).

Country and city attribution of geolocated item[edit]

I am trying to make use of GeoData API to perform aforementioned task.

I found GeoData API very clean, simple and user friendly in retrieving various data according to geo location of the item. But there are difficulties with retrieving country/city affiliation of the item. While country can theoretically be get in a single request (also not always but only if being specified and not in name format but rather by its alphabetic designation), the city is possible to be get only for items which are cities by themselves. Let's imagine I want to determine in one request providing the coordinates of the Sagrada Familia temple the name of the item and that it is located in Barcelona, Spain. As far as I understood there is no way to do that. From the second hand this information does exist for every geo tagged item and is available for example through WikiData SPARQL query service. But then I'll need to perform a second request to WikiData which I would have liked to avoid by all means.

Looking on advice on the optimal strategy to perform the desired. If not is it possible to add the country/city attributes to GeoData list=geosearch attributes?

 – The preceding unsigned comment was added by Chainastole (talk • contribs) at 14:05, 8 September 2018 (UTC).

Possible to search to return list of items, as per website search box?[edit]

I was wondering if it is possible to use the query service to replicate the search box found on the website.

This will return results simple enough - but the ordering needs to be there.

SELECT distinct ?item ?itemLabel ?itemDescription WHERE{

 ?item ?label "Michael Jackson"@en.  
 ?article schema:about ?item .
 ?article schema:inLanguage "en" .
 ?article schema:isPartOf <https://en.wikipedia.org/>.	
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }    

}

 – The preceding unsigned comment was added by 185.217.68.243 (talk • contribs) at 22:20, 17 October 2018‎ (UTC).

How to get the timePrecision associated with a Point in Time for a population statement?[edit]

I'm trying to query all the populations for Albany (Q671480). I'm stuck on how to get the precision associated with each population statement. How to fix my attempt?

SELECT ?geoEntity ?geoEntityLabel ?population ?date ?datePrecision WHERE {
  BIND(wd:Q671480 AS ?geoEntity)
  ?geoEntity p:P1082 ?populationStatement.
  ?populationStatement ps:P1082 ?population.
  ?populationStatement pq:P585 ?date.
 
  # how to get datePrecision
  OPTIONAL {
                ?date wikibase:timePrecision ?datePrecision. # precision of at least year
                ?date wikibase:timeValue ?dateValue.
              } 
    
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

@RaymondYee: I've thrown in Rank as well, since that's often the best way to find the current value, as opposed to historic values. All the statements had date, so I threw the OPTIONAL away. --Tagishsimon (talk) 12:38, 6 December 2018 (UTC)
SELECT ?geoEntity ?geoEntityLabel ?population ?date ?datePrecision ?dateValue ?rank WHERE {
  values ?geoEntity {wd:Q671480}
  ?geoEntity p:P1082 ?populationStatement.
  ?populationStatement ps:P1082 ?population.
  ?populationStatement pqv:P585 ?date.
  ?date wikibase:timePrecision ?datePrecision. # precision of at least year
  ?date wikibase:timeValue ?dateValue.
  ?populationStatement wikibase:rank ?rank.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
Try it! --Tagishsimon (talk) 12:38, 6 December 2018 (UTC)

Popular person[edit]

How to count persons with >25 sitelinks without timeout?

SELECT (COUNT(?item) AS ?cnt) WHERE {
  ?item wikibase:sitelinks ?count.
  ?item wdt:P31 wd:Q5.
  FILTER (?count >25)
}
LIMIT 30000

Try it!

Infovarius (talk) 22:22, 6 December 2018 (UTC)

@Infovarius: This query is stolen from Nikki ... I've not wrapped a count around it, but it fetches 26604 results in 58213 ms. --Tagishsimon (talk) 22:47, 6 December 2018 (UTC)
select ?item ?itemLabel ?number
with { # select all the numbers of sitelinks
  select ?number (count(*) as ?total) { # we don't use ?total but without it the query times out, so we'll just ignore it
    [] wikibase:sitelinks ?number .
  } group by ?number
} as %numbers
where {
  include %numbers .
  filter (?number > 25) .
  ?item wikibase:sitelinks ?number .
  ?item wdt:P31 wd:Q5.
  service wikibase:label { bd:serviceParam wikibase:language "en" }
} order by desc(?number)
Try it! --Tagishsimon (talk) 22:47, 6 December 2018 (UTC)
Oh, so unobvious, looks like a hack :) @Tagishsimon, Nikki: Thanks! --Infovarius (talk) 13:43, 7 December 2018 (UTC)

List of all towns and cities in Catalonia in Spanish language and in Catalan language[edit]

Hi i would like to get a list in two columns with all the towns and cities in Catalonia in Spanish language and in Catalan language,
In this way:

Figueras/Figueres
Alto Ampurdán/Alt Ampurdà
Lérida/Lleida
Gerona/Girona
...
Thanks, --Astur (talk) 19:26, 7 December 2018 (UTC)

Hi, try this (show in a map, may be shown in a table too)
SELECT ?localisation ?label_es ?label_ca ?label_fr ?label_en ?coordinates 
WHERE {
  ?localisation wdt:P31 wd:Q33146843.
  ?localisation wdt:P625 ?coordinates.
  ?localisation rdfs:label ?label_es filter (lang(?label_es) = "es").
  ?localisation rdfs:label ?label_ca filter (lang(?label_ca) = "ca").
  ?localisation rdfs:label ?label_fr filter (lang(?label_fr) = "fr").
}

Try it! Bouzinac (talk) 20:45, 7 December 2018 (UTC)

And this is a different approach; it gets a set of items located in the administrative entity of Catalonia; and then checks that they are instances or subclasses of a human settlement. My presumption - and it is only that - is that towns and cities will be subclasses of human settlement. However the query will also fetch the villages. So it is possible to add report terms to look explicitly for cities or municipalities. There are a couple of commented-out lines which will restrict the query to these classes; more can be added on your suggestion. For now I have added a fourth column telling you what sort of settlement it is. I hope you can tweak to the report so it gives you exactly what you want, or else come back here and specify the changes you would like making.
select distinct ?item ?caLabel ?esLabel (group_concat(distinct ?typeLabel) as ?typeLabel) 
 with { select distinct ?item where {
  ?item wdt:P131/wdt:P131* wd:Q5705 .                       # item is located in Catalonia
   } 
 } as %inplace
where {
  include %inplace .
  ?item wdt:P31/wdt:P279* wd:Q486972 .                      # item is a human settlement or subclass of
#  ?item wdt:P31 wd:Q515                                    # item is a city
#  ?item wdt:P31 https://www.wikidata.org/wiki/Q33146843 .  # item is a municipality of Catalonia
  ?item wdt:P31 ?type. 
  optional { ?type rdfs:label ?typeLabel. filter(lang(?typeLabel)="es") } #get the P31 label
  optional { ?item rdfs:label ?esLabel. filter(lang(?esLabel)="es") }
  optional { ?item rdfs:label ?caLabel. filter(lang(?caLabel)="ca") }                             
} group by ?item ?esLabel ?caLabel order by ?caLabel
Try it! --Tagishsimon (talk) 21:00, 7 December 2018 (UTC)

List of all towns and cities in Germany[edit]

Hi, I'm looking for a query that shows all towns and cities in Germany (similar to the Catalonia example above). Querying through instance of (P31) and subclass of (P279) urban municipality of Germany (Q42744322) does not return the result I'm looking for. Several cities are returned multiple times because multiple subclasses apply to them. I'm looking for a way to either truncate these additional, but unwanted results or limit the output to the deepest subclass, so that cities like Cologne (Q365) only appear once. Thanks in advance for any help or suggestions. Regards, Christoph Braun (talk) 12:12, 8 December 2018 (UTC)

SELECT ?item ?itemLabel  WHERE {
  ?item wdt:P31/wdt:P279* wd:Q42744322;
        wdt:P625 ?geo .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

urban municipality of Germany (Q42744322) is compromised of 6 subclasses. See:

SELECT * WHERE {
  ?item  wdt:P279 wd:Q42744322 ;
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

@Christoph Braun: Presuming all of your towns & cities have a P31 in the subclass tree of Q42744322, then this should be good to go. Main changes are a) use of DISTINCT and b) group results on ?item ?itemLabel and (sample) the coordinates, lest there be two coords for the same item. However we might need to find more P31 values to query against - I'd guess Germany has more than ~1500 towns & cities.
SELECT distinct ?item ?itemLabel (sample(?geo) as ?geo)  WHERE {
  ?item wdt:P31/wdt:P279* wd:Q42744322.
  optional { ?item wdt:P625 ?geo . }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?item ?itemLabel
Try it! --Tagishsimon (talk) 12:39, 8 December 2018 (UTC)
@Tagishsimon: Thanks for your help. Germany has 2058 towns and cities (see en:List of cities and towns in Germany), which I added via QuickStatements. I think the problem is that cities like Wanfried (Q1432) feature a coordinate location (P625), but others like Bebra (Q558530) don't. Any idea how to return all urban municipality of Germany (Q42744322) and its subclasses where coordinate location (P625) is missing? Regards, Christoph Braun (talk) 21:23, 8 December 2018 (UTC)
@Christoph Braun: I'm sorry to tell you that the immediately above query does exactly that - return all urban municipality of Germany (Q42744322) and its subclasses where coordinate location (P625) is missing. So I'm not sure where the gap between the results (1506) and your 2058 arises from. Sadly you now need to find a missing town so we can study its properties. --Tagishsimon (talk) 21:28, 8 December 2018 (UTC)
@Christoph Braun: Here is an example. Aach (Q62158) is missing from the results. If we look at it, we find that municipality of Germany (Q262166) is given a higher rank than urban municipality of Germany (Q42744322). So if we want to look for municipality of Germany (Q262166) as well, then as below; but that returns us 12298 rows :(
SELECT distinct ?item ?itemLabel (sample(?geo) as ?geo)  WHERE {
  {?item wdt:P31/wdt:P279* wd:Q42744322.}
  UNION
  {?item wdt:P31/wdt:P279* wd:Q262166.}
  optional { ?item wdt:P625 ?geo . }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?item ?itemLabel
Try it!
So, maybe instead we look for items with urban municipality of Germany (Q42744322) irrespective of rank - that seems a bit more like it - 2051 results
SELECT distinct ?item ?itemLabel (sample(?geo) as ?geo)  WHERE {
  ?item p:P31/p:P279* ?statement .
  ?statement ps:P31 wd:Q42744322.
  optional { ?item wdt:P625 ?geo . }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?item ?itemLabel
Try it! --Tagishsimon (talk) 21:40, 8 December 2018 (UTC)
Finally, maybe, an added column showing the truthy P31 values for each row.
SELECT distinct ?item ?itemLabel (sample(?geo) as ?geo)  (group_concat(distinct ?typeLabel;separator=', ') as ?typeLabel) WHERE {
  ?item p:P31/p:P279* ?statement .
  ?statement ps:P31 wd:Q42744322.
  optional { ?item wdt:P625 ?geo . }
  optional {?item wdt:P31 ?type. ?type rdfs:label ?typeLabel . filter(lang(?typeLabel)="en") }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?item ?itemLabel
Try it! --Tagishsimon (talk) 21:43, 8 December 2018 (UTC)
Thanks for your tireless effort @Tagishsimon:. urban municipality of Germany (Q42744322) applies to places with city rights in Germany, whereas municipality of Germany (Q262166) applies to so called Gemeinden which might not have city rights. By legal definition every urban municipality of Germany (Q42744322) is also a municipality of Germany (Q262166), hence the subclass, but not the other way round. Regards, Christoph Braun (talk) 08:27, 9 December 2018 (UTC)

Data issue discussion[edit]

@Christoph Braun: So we probably have a problem with the data. Not sure if you're familiar with property ranks; there are three - preferred, normal, and deprecated. Report users will get the highest ranking properties when using wdt: to fetch items - this is why we got only ~1500 urban municipalities on an earlier query; ~550 were not returned because there was a property with a higher rank. But if every urban municipality of Germany (Q42744322) is also a municipality of Germany (Q262166) we would not ever expect the municipal_rank to be greater than the urban_municipality rank. And, indeed, an item that has urban municipality of Germany (Q42744322) should not have municipality of Germany (Q262166) at all since urban municipality of Germany (Q42744322) is already a subclass of municipality of Germany (Q262166). So the solution, by & large, will be to remove municipality of Germany (Q262166) from items having urban municipality of Germany (Q42744322). Here's a list of items having both properties (and showing their ranks, fwiw). It would be a 2 minute job in quickstatements to fix the issue. Discuss.
SELECT distinct ?item ?itemLabel ?municipality_rankLabel ?urban_municipality_rankLabel WHERE {
  ?item p:P31 ?statement2 .
  ?statement2 ps:P31 wd:Q262166 .
  ?statement2 wikibase:rank ?municipality_rank .             
  ?item p:P31/p:P279* ?statement .
  ?statement ps:P31 wd:Q42744322.
  ?statement wikibase:rank ?urban_municipality_rank .           
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Tagishsimon (talk) 11:51, 9 December 2018 (UTC)
@Tagishsimon:Thanks for pointing this out. Yes I'm familiar with property ranks. This would apply all 7 subclasses of urban municipality of Germany (Q42744322) as well. Seems like the entire subclass structure is due for some maintenance.
SELECT ?item ?itemLabel WHERE {
  ?item  wdt:P279* wd:Q42744322 ;
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!
I spent some time removing false positives and false negatives based on official records of Germany's towns and cities. Doing so I noticed that there's another dimension to this problem. Some towns and cities limit the status of urban municipality of Germany (Q42744322) by time, which should be reflected by the query. Ideally the query would only return results based on a given date (e.g. 9th of December 2018). Any ideas? Regards, Christoph Braun (talk) 16:09, 9 December 2018 (UTC)
@Christoph Braun: I'm only finding one item having a P31/P279 of urban municipality of Germany (Q42744322) that has an end time (P582); and nothing with dissolved, abolished or demolished (P576). But for sure, if there are qualifiers that rule items out, point to example records and the query can be tailored.
SELECT distinct ?item ?itemLabel ?municipality_rankLabel ?urban_municipality_rankLabel ?end_date WHERE {
  ?item p:P31 ?statement2 .
  ?statement2 ps:P31 wd:Q262166 .
  ?statement2 wikibase:rank ?municipality_rank .             
  ?item p:P31/p:P279* ?statement .
  ?statement ps:P31 wd:Q42744322.
  ?statement wikibase:rank ?urban_municipality_rank .
  ?statement pq:P582 ?end_date.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Tagishsimon (talk) 16:21, 9 December 2018 (UTC)
@Tagishsimon: The examples I found are not only limited by end date but also by start date, like Oberzent (Q1682542), which is a new town that has been established on January 1st 2018. Eislingen/Fils (Q81905) never ceased to be a urban municipality of Germany (Q42744322), because all Große Kreisstadt (Q448801) are subclasses of urban municipality of Germany (Q42744322). urban municipality of Germany (Q42744322) in Baden-Württemberg with more than 20.000 inhabitants are municipality of Germany (Q262166) if they applied for this status. Regards, Christoph Braun (talk) 16:38, 9 December 2018 (UTC)
@Christoph Braun: Do we need to be concerned with start date? For sure it confirms something started, but we're concerned with end-date only. Anyway, I stand-by to amend queries as you direct. Below query just looks at members of the urban municipality of Germany (Q42744322) class tree and shows if they have start or end dates (wherein I'm not concerning myself with date precision - so a year precision date will show as 1 January XXXX).
SELECT distinct ?item ?itemLabel ?municipality_rankLabel ?urban_municipality_rankLabel ?start_date ?end_date WHERE {
  ?item p:P31/p:P279* ?statement .
  ?statement ps:P31 wd:Q42744322.
  ?statement wikibase:rank ?urban_municipality_rank .
  optional {?statement pq:P580 ?start_date . }
  optional {?statement pq:P582 ?end_date . }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Tagishsimon (talk) 16:46, 9 December 2018 (UTC)
@Tagishsimon: Thanks! Ideally all urban municipality of Germany (Q42744322) will feature a start point in the future. Adding +2000 start points manually (with proper sources) is a tedious process. What I'm looking for is a time filter, so that I could filter all cities founded between 1400 and 1500 based on their start dates. Regards, Christoph Braun (talk) 20:04, 9 December 2018 (UTC)
@Christoph Braun: This sort of thing - based on filtering the start date of Template:Q\42744322
SELECT distinct ?item ?itemLabel ?urban_municipality_rankLabel ?start_date ?end_date WHERE {
  ?item p:P31/p:P279* ?statement .
  ?statement ps:P31 wd:Q42744322.
  ?statement wikibase:rank ?urban_municipality_rank .
  optional {?statement pq:P580 ?start_date . }
  optional {?statement pq:P582 ?end_date . }
  filter(year(?start_date)>2017)             # FROM
  filter(year(?start_date)<2019)             # TO
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?start_date
Try it! --Tagishsimon (talk) 20:13, 9 December 2018 (UTC)
@Tagishsimon: Perfection. Thank you so much for your time. This should end up as an example in https://query.wikidata.org Regards, Christoph Braun (talk) 20:29, 9 December 2018 (UTC)

@Tagishsimon: Based on your query, I added filter not exists to exclude former urban municipality of Germany (Q42744322). Some cities like Bonn (Q586) are currently not included by the query, because they rely on independent city of Germany (Q22865) which is a subclass of urban municipality of Germany (Q42744322). Could you change the query, so that all subclasses of urban municipality of Germany (Q42744322) are included, please? Regards, Christoph Braun (talk) 19:11, 10 December 2018 (UTC)

SELECT distinct ?item ?itemLabel ?urban_municipality_rankLabel ?start_date ?end_date WHERE {
  ?item p:P31/p:P279* ?statement .
  ?statement ps:P31 wd:Q42744322.
  ?statement wikibase:rank ?urban_municipality_rank .
  optional {?statement pq:P580 ?start_date . }
  optional {?statement pq:P582 ?end_date . }
  filter not exists {?statement pq:P582 ?end_date . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?itemLabel

Try it!

@Christoph Braun: Bit of a cludge - my P31/P279 property path is not working, and I've not found a fix. But there are few subclasses, so this should do it.
SELECT distinct ?item ?itemLabel ?urban_municipality_rankLabel ?start_date ?end_date WHERE {
  VALUES ?type {wd:Q42744322 wd:Q22865 wd:Q134626 wd:Q448801 wd:Q1548518 wd:Q1548525 wd:Q54935786}
  ?item p:P31/p:P279* ?statement .
  ?statement ps:P31 ?type.
  ?statement wikibase:rank ?urban_municipality_rank .
  optional {?statement pq:P580 ?start_date . }
  optional {?statement pq:P582 ?end_date . }
  filter not exists {?statement pq:P582 ?end_date . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?itemLabel
Try it! --Tagishsimon (talk) 19:59, 10 December 2018 (UTC)

Henrique Pousão[edit]

Please, how to put a list sorted chronologically? Is it possible to make this change at 2 previous lists in WPPT, namely w:pt:Lista de pinturas de Henrique Pousão and w:pt:Lista de pinturas de Silva Porto? Thank you, GualdimG (talk) 00:12, 9 December 2018 (UTC)

@GualdimG: If I understand you well, this query lists pictures by Henrique Pousão in date order, albeit many of them for the first artist seem to have the same date; many for the second artist have no date (using the inception (P571) property). You point to two w:pt pages, but I think they're blank. Did you want Listeria lists on those pages?
#Henrique Pousão
SELECT ?item ?itemLabel (year(?date) as ?year) WHERE {
  ?item wdt:P170 wd:Q3847723.
  optional {?item wdt:P571 ?date .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?date
Try it!
#Silva Porto
SELECT ?item ?itemLabel (year(?date) as ?year) WHERE {
  ?item wdt:P170 wd:Q610761.
  optional {?item wdt:P571 ?date .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?date
Try it! --Tagishsimon (talk) 00:26, 9 December 2018 (UTC)
@GualdimG: I've changed the two pages; they now sort by date. --Tagishsimon (talk) 00:36, 9 December 2018 (UTC)
Thank you @Tagishsimon:, for your attention to my request. Now I understand that must put the expression "order by ?date" at the end of the query. Is it possible correct the label of the two first colunms of w:pt:Lista de pinturas de Silva Porto by "Título" and "Descrição"? Thank you again, GualdimG (talk) 00:51, 9 December 2018 (UTC)
@GualdimG: Ah, be careful, GualdimG. That is how you do it in SPARQL, but for a Listeria list you do this. I have changed the column titles with this edit. --Tagishsimon (talk) 00:56, 9 December 2018 (UTC)
@Tagishsimon: Thank you, so much! Greetings, GualdimG (talk) 01:10, 9 December 2018 (UTC)

Show me the earliest 100 people on earth who are listed in wikidata[edit]

Hi, I'm looking for a querey that shows the earliest 100 people on earth who are listed in wikidata. Thanks in advance for any help or sugestions. Regards

I think, hard/no possible to do in wikidata because evaluating dates of birth or death within the minute of allotted runtime tends to fail with a timeout. If it were possible, we'd then need a discussion on what constitutes a person - Homo sapiens sapiens, or earlier models; and, for the oldest people we have, we may have uncertainties about the date - if we have a date at all. Lucy (Q245388) is missing any date information (which is a bit of a shame). Sorry about that. --Tagishsimon (talk) 15:55, 9 December 2018 (UTC)

Query for news items across the globe[edit]

Custom built query that will search for news with the following attributes: 1. People, Occupation, Nationality 2. Organizations 3. Event description 4. Google News ID 5. Location of the event - Country, City 6. Date of Occurrence 7. related published content

That's not something wikidata is geared up to do; at least not right now. Our coverage of events is negligible. --Tagishsimon (talk) 15:39, 9 December 2018 (UTC)

Description of each language in a column[edit]

I need to have the description in both French and English for a series of elements, each language in a column, not on a new line.

I know that I can achieve this for labels in different languages with this request:

#List of countries in English and French
SELECT ?station ?label_en ?label_fr  WHERE {
  ?station wdt:P31 wd:Q190107.
  ?station rdfs:label ?label_en.
  ?station rdfs:label ?label_fr.
  FILTER((LANG(?label_en)) = "en")
  FILTER((LANG(?label_fr)) = "fr")
  ?station wdt:P17 wd:Q16.
}

Try it!

How can I proceed to have the same behaviour, but for description instead of label?

Dirac (talk) 19:39, 10 December 2018 (UTC)

@Dirac: Like this / Comme ca. Plus I have made labels and descriptions optional, presuming you want the row even if one of the four is missing. And I've given you label plus description - you can see your way around removing the labels if you want to.
#List of countries in English and French
SELECT ?station ?label_en ?desc_en ?label_fr ?desc_fr WHERE {
  ?station wdt:P31 wd:Q190107.
  optional {?station rdfs:label ?label_en.}
  optional {?station rdfs:label ?label_fr.}
  optional {?station schema:description ?desc_en.}
  optional {?station schema:description ?desc_fr.}
  FILTER((LANG(?label_en)) = "en")
  FILTER((LANG(?label_fr)) = "fr")
  FILTER((LANG(?desc_en)) = "en")
  FILTER((LANG(?desc_fr)) = "fr")
  ?station wdt:P17 wd:Q16.
}
Try it! --Tagishsimon (talk) 21:41, 10 December 2018 (UTC)

URLs for the world's newspapers[edit]

Would like to know the URLs to every newspaper in the world, or anyway those available on Wikidata. -- GreenC (talk) 21:29, 10 December 2018 (UTC)

@GreenC: This:

SELECT ?item ?itemLabel ?url 
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q11032.
  ?item wdt:P856 ?url.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?itemLabel
Try it! --Tagishsimon (talk) 21:37, 10 December 2018 (UTC)

Lista de pinturas de Alfredo Keil[edit]

Would you please run the following query, had I alredy created the WKPT article: W:pt:Lista de pinturas de Alfredo Keil

  1. Lista de pinturas de Alfredo Keil
  2. defaultView:table

SELECT ?pic ?item ?data_de_publicação ?retrata ?coleção ?localização WHERE {

?item wdt:P31 wd:Q3305213.
?item wdt:P170 wd:Q1437304.

|sort=P571

?item wdt:P18 ?pic.

Optional { ?item wdt:P577 ?data_de_publicação.} Optional { ?item wdt:P180 ?retrata.} Optional { ?item wdt:P195 ?coleção.} Optional { ?item wdt:P276 ?localização.} }

Thank you, GualdimG (talk) 23:46, 10 December 2018 (UTC)

@GualdimG: I followed the pattern used in w:pt:Lista de pinturas de Silva Porto ... see w:pt:Lista de pinturas de Alfredo Keil. Hope that works for you. --Tagishsimon (talk) 23:57, 10 December 2018 (UTC)
@Tagishsimon:, thank you! Very good. But I would like to make an improvement, in the form I had proposed above: 1. changing the sequence of columns, so that would be (from left): Imagem (Image), Título (Label), Data de publicação (Date of pub.), Retrata (Depicts), Coleção (collection), Localização (location), with initial upper letters, and only in portuguese; and 2. replacing the column labeled Descrição (Description) with Retrata (Depicts). I am sorry for the extra demanding. Greetings, GualdimG (talk) 09:17, 11 December 2018 (UTC)
@GualdimG: I think that's now done ... but Depicts is empty - no data? Let me know if you want more changes. --Tagishsimon (talk) 12:00, 11 December 2018 (UTC)
@Tagishsimon: Thank you, so much! You are right about the weak fill of the "Depicts" column, because this statement is weakly filled in wich of the items, but even so I think is better than a column that merely repeats for all the items "painting of Alfredo Keil". Greetings, GualdimG (talk) 16:36, 11 December 2018 (UTC)


Map of nuclear power plants (by status)[edit]

Is it possible to show in a map of nuclear power plant (Q134447) with different kind of markers/colors for the status of each one? For instance: operational, planned, under construction, shut down, etc. --Micru (talk) 00:04, 11 December 2018 (UTC)

@Micru: You'd have to advise on the properties against which we can pin status. Here's an experiment with service entry (P729) and service retirement (P730)
#defaultView:Map
SELECT ?item ?itemLabel ?coord ?layer
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q134447.
  ?item wdt:P625 ?coord.
  optional {?item wdt:P730 ?retired .}
  optional {?item wdt:P729 ?inservice .}
  BIND(
    IF(bound(?retired), "retired",
    IF(bound(?inservice), "in service",
    "unknown"))
    AS ?layer).
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Tagishsimon (talk) 00:38, 11 December 2018 (UTC)
@Tagishsimon: Thank you! Any recommended way to enter the "planned" or "in construction" status, or do we need a new property for that?--Micru (talk) 00:42, 11 December 2018 (UTC)
@Micru:
No advice from me on that. I note Hinkley Point C nuclear power station (Q5767271), but think we're maybe not good at (or I have not seen) very good thing-lifecycle examples on wikidata. Films distinguish between P31s of Film and Film Project. I think there's a Proposed Airport item, again to be used with P31. So you might create an item for a Proposed Nuclear Plant; and then add Significant Events for 'construction start', etc (e.g. per the lifecycle for RMS Titanic (Q25173)). I would _always_ hesitate to reach for the creation of a new property and instead head for use of existing properties possibly combined with new items. So. Advice after all. --Tagishsimon (talk) 00:54, 11 December 2018 (UTC)

Running a Query with information base from the past[edit]

I'm trying to measure the impact of a wiki editathon using Wikidata query service. I already prepared my query, but the issue is I want to have an idea on the number of articles before and after the project started. Unfortunately for us, we didn't run any query before the competition started. Is there a way to run a query as if I was in September 2018? HandsomeBoy (talk) 18:04, 11 December 2018 (UTC)

@HandsomeBoy: You may be able to do something with petscan, which has an option to look for "Only pages created during the above time window" (in page properties) - if you're talking about language wiki article creation, rather than expansion or wikidata linking. You can incorporate SPARQL into a Petscan query. Come back with specifics if you want more help. --Tagishsimon (talk) 18:22, 11 December 2018 (UTC)

Getting words and decletions[edit]

I'd like to build a simple application to learn conjugations and declentions for myself, then maybe for all people :) I'd like to have a SPARQL script that returns all Finnish nouns, by now. Do I get also all decletions by getting data about a noun? If not so, given a word, I need to get a particular form (e.g. partitive singular) of the word. Thank you very much! --★ → Airon 90 19:05, 11 December 2018 (UTC)

@Airon90:
SELECT ?representation {
  VALUES (?lexeme) {(wd:L5100)}
  ?lexeme ontolex:lexicalForm [ wikibase:grammaticalFeature wd:Q110786, wd:Q857325;
                                ontolex:representation ?representation ]
}
Try it!
SELECT ?representation ?numberLabel ?caseLabel {
  VALUES (?lexeme) {(wd:L5100)}
  ?lexeme ontolex:lexicalForm ?form . 
  ?form ontolex:representation ?representation .
  ?form wikibase:grammaticalFeature ?number , ?case .
  ?number wdt:P31 wd:Q104083 .
  ?case wdt:P31 wd:Q128234 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fi" }
}
Try it! -- Luitzen (talk) 22:22, 14 December 2018 (UTC)
Thank you Luitzen but I need a better implementation fo the second query: you entered a lexeme (L5100) but I need the declention of every Finnish lexemes. Could you adapt that code, please? --★ → Airon 90 17:31, 17 December 2018 (UTC)
@Luitzen: Well, this maybe, although I should specify that I don't have a clue when it comes to interrogating lexemes. Presuming it is doing what you want, you will probably have to sort out the ordering of the conjugations, maybe later in a spreadsheer.
SELECT ?lexeme ?representation ?numberLabel ?caseLabel {
  ?lexeme <http://purl.org/dc/terms/language> wd:Q1412.
  ?lexeme ontolex:lexicalForm ?form . 
  ?form ontolex:representation ?representation .
  ?form wikibase:grammaticalFeature ?number , ?case .
  ?number wdt:P31 wd:Q104083 .
  ?case wdt:P31 wd:Q128234 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fi" }
} order by ?lexeme
Try it! --Tagishsimon (talk) 18:01, 17 December 2018 (UTC)

Patronage of an Australian airport[edit]

Hello I've put inside some data for airport frequentation in Australia. I'd like to

Thanks!

Here's a basic query for the data for Adelaide Airport (Q14302). If you select the Graph Builder option (select the eye-icon just above the ?item column) and make time=x, number=y and applies_to=colour, you get a very pretty graph, which answers to your first & third points
SELECT  ?item (year(?time) as ?year) ?applies_toLabel ?number 
WHERE 
{
  values ?item {wd:Q14302} 
?item p:P3872 ?statement .
?statement pq:P585 ?time .
?statement pq:P518 ?applies_to .
?statement ps:P3872 ?number .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?year ?applies_toLabel
Try it!
Next, to answer your second point, here are the numbers for the 'everything' category, for 2016 for Oz airports. Graph Builder will probably do good things with this, as well?
SELECT  ?item ?itemLabel (year(?time) as ?year) ?number 
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q62447 .
  ?item wdt:P17 wd:Q408 .
  ?item p:P3872 ?statement .
    ?statement pq:P585 ?time .
    ?statement pq:P518 ?applies_to .
    ?statement ps:P3872 ?number .
  filter(year(?time)=2016)
  filter(?applies_to=wd:Q2165236)
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by desc(?number)
Try it! --Tagishsimon (talk) 23:28, 11 December 2018 (UTC)

W:pt:Lista de pinturas de Silva Porto[edit]

Would you please change the sequence of columns, so that would be (from left): Imagem (Image), Título (Label), Data de publicação (Date of pub.), Retrata (Depicts), Coleção (collection), Localização (location), with initial upper letters of the column label, and only in portuguese; and 2. replacing the column labeled Descrição (Description) with Retrata (Depicts). I am sorry for the extra demanding. Greetings, GualdimG (talk) 11:17, 12 December 2018 (UTC)

@GualdimG: No problem, always happy to do this sort of stuff. My pt.wiki edit count is pretty low, so it's v.helpful ;). Let me know if the change I made works for you; come back with anything further you need. Equally, check out the diff - I just stole the pertinent line from the w:pt:Lista de pinturas de Alfredo Keil article. You'd better check w:pt:Lista de pinturas de Henrique Pousão which has a different column layout. I'm guessing you might want the following three to be consistent? --Tagishsimon (talk) 12:11, 12 December 2018 (UTC)
Once again, @Tagishsimon:, thank you, so much. The lists of Alfredo Keil and Silva Porto, for me, are quite allright. However, the last (right) column of w:pt:Lista de pinturas de Henrique Pousão, "being part of", is not meaningful in this case, because they are all isolated paintings, and so its content would be better the "Localização(location)". Greetings, GualdimG (talk) 14:37, 12 December 2018 (UTC)
@GualdimG: Done. --Tagishsimon (talk) 14:44, 12 December 2018 (UTC)

List of italian party secretaries[edit]

SELECT ?item ?itemLabel ?person ?personLabel  WHERE {
?item wdt:P31 wd:Q7278 .
?item wdt:P17 wd:Q38 .
?item wdt:P210 ?person .
SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
} ORDER BY ?itemLabel
Try it!

I expect that for the "Democratic Party" (https://www.wikidata.org/wiki/Q47729) the query will provide eight secretaries instead of one. In fact in https://www.wikidata.org/wiki/Q47729 there are eight people with property P210. For the "Movement for Autonomies" (http://www.wikidata.org/entity/Q1233269) instead the result is correct because the query provides three results how many people are with property P210.

--Mataopen (talk) 19:07, 12 December 2018 (UTC)

@Mataopen: It's the difference between 'truthy' statements - those with relatively higher ranks and which can be retrieved with a wdt: - and all statements, which need to be retrieved with a ps: and for which a ?statement must be found as a linking object. See Statement types for more clues.
SELECT ?item ?itemLabel ?person ?personLabel  WHERE {
  ?item wdt:P31 wd:Q7278 .
  ?item wdt:P17 wd:Q38 .
  ?item p:P210 ?statement .
  ?statement ps:P210 ?person
SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
} ORDER BY ?itemLabel
Try it! --Tagishsimon (talk) 19:23, 12 December 2018 (UTC)

Get only january[edit]

Hi,
With this query, I can get date of death (P570) by month. It work for month 2 to 12, but for month 1, it also get the values with only the year/century/millenia. Do someone know how to get only the value for month = 1 ? :

SELECT DISTINCT ?item ?itemLabel ?mort WHERE {
 ?item wdt:P570 ?mort
   FILTER(month(?mort) = NUMBER)
   SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
}
LIMIT 1000

Try it! --Simon Villeneuve (talk) 12:01, 13 December 2018 (UTC)

@Simon Villeneuve: You need to be looking at date precision which I do in the query below, albeit in a fairly unhelpful scrunched up form, which uses a property path p:P570/psv:P570 meaning "get me a psv: value associated with the p: value of this statement" and then [wikibase:timePrecision ?datePrecision; wikibase:timeValue ?mort ] . which are values linked to the found psv:. I can offer User_talk:Tagishsimon#Wittylama as an explainer of the whole square brackets thing, and of the absolute importance of understanding the RDF data model, fwiw.
In short, dates with precision of 9 are accurate to the year, and the month defaults to January. Same with 8, 7, 6, etc - decade, century - all give January months when asked. Ensuring that filter(?datePrecision>9) means you're looking at dates which have genuine months in them.
SELECT DISTINCT ?item ?itemLabel ?mort ?datePrecision WHERE {
  ?item p:P570/psv:P570 [wikibase:timePrecision ?datePrecision; wikibase:timeValue ?mort ] .
  filter(?datePrecision>9)  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
}
LIMIT 100
Try it! --Tagishsimon (talk) 13:05, 13 December 2018 (UTC)

A list of the most used properties which don't have 5 model item (P5869) and a list of likely candidates[edit]

Hi all

So I've been working on something called Wikidata:Model items which aim to highlight the best examples on specific subjects, or examples of items that use properties well.

I want to create a kind of to do list to make sure the most used properties link to items that show how to use the property well.

I think the way to do this is through a query that shows

  • A list of the most used properties which don't have 5 model item (P5869)
  • For each of the properties in the list, a list of the 10 items which use this property which have the highest number of statements

Maybe a table something like this?

Most used properties without 5 model items Current number of model items Items using this property with highest number of statements

 – The preceding unsigned comment was added by John Cummings (talk • contribs).

Explain the 5 in 5 model item (P5869). Confused. --Tagishsimon (talk) 14:16, 13 December 2018 (UTC)
Sorry @Tagishsimon:, that's not very clear is it. I mean that there should be multiple examples (5 is a good number) when using model item (P5869) to show good examples of the broad spectrum of a subject. E.g having only William Shakespeare as the only example of a playwright is probably not very helpful. --John Cummings (talk) 16:02, 13 December 2018 (UTC)


Maybe its helpful to show exactly what each table column should show
Most used properties without 5 model items Current number of model items Items using this property with highest number of statements
Most commonly used properties on Wikidata, minus items which use model item (P5869) and have more than 5 values for the property Number of times there is a statement using model item (P5869) in the property, e.g for an property which doesn't have any statements using model item (P5869) then the number is 0, for properties which have a statement which uses model item (P5869) and has 2 examples, the number will be 2 The 10 items on Wikidata which use this property which have the most statements
John Cummings (talk) 16:42, 13 December 2018 (UTC)

This query gets pretty close to what was requested.

  • Shows all properties with less than 5 model item statements
  • Sorted by property number as very basic way to determine "top" properties (the query would time out with any actual counting of property uses as far as I can tell?)
  • Top 10 items using each property is found via a separate query link
#Properties with 5 or less Model Item statements, with links to query for top 10 items using the property
SELECT ?property ?propertyLabel ?model_item_statement_count ?query_top_ten_items
WHERE {
  {
    SELECT DISTINCT ?property (COUNT(?modelItem) as ?model_item_statement_count)
    WHERE {
      ?property rdf:type wikibase:Property . #all properties
      OPTIONAL { ?property wdt:P5869 ?modelItem } #with model items if present
    } GROUP BY ?property 
  }
  
  FILTER (?model_item_statement_count < 5) #only show properties with 5 or less Model Item statements
  BIND (xsd:integer( SUBSTR(STR(?property), 33) ) as ?propNumber) #use property number as a basic rank for 'top' properties 
  BIND ("https://query.wikidata.org/embed.html#SELECT%20%3Fitem%20%3FitemLabel%20%3Fstatement_count%0AWHERE%20%7B%0A%20%20%7B%0A%20%20%20%20SELECT%20%3Fitem%20%3Fstatement_count%0A%20%20%20%20WHERE%20%7B%0A%20%20%20%20%20%20%3Fitem%20wdt%3APproperty%20%5B%5D%20.%0A%20%20%20%20%20%20%3Fitem%20wikibase%3Astatements%20%3Fstatement_count%20.%0A%20%20%20%20%7D%0A%20%20%20%20ORDER%20BY%20DESC%20%28%3Fstatement_count%29%0A%20%20%20%20LIMIT%2010%0A%20%20%7D%0A%20%20SERVICE%20wikibase%3Alabel%20%7B%20bd%3AserviceParam%20wikibase%3Alanguage%20%22%5BAUTO_LANGUAGE%5D%2Cen%22.%20%7D%0A%7D%0A%0A"
        as ?query_template)       
  BIND (URI(REPLACE(?query_template,"property", STR(?propNumber))) as ?query_top_ten_items) #generate query URL by inserting property number into template
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY (?propNumber)

Try it!

Very interested to see if anyone can has another approach that gets closer to the original request? Best, NavinoEvans (talk) 18:33, 17 December 2018 (UTC)

@NavinoEvans: I can't (right now) get closer, but just to say your report-within-a-report technique was swiftly reappropriated in Wikidata:WikiProject British Politicians/ambassadors/ambassador by country list with much thanks. --Tagishsimon (talk) 23:10, 17 December 2018 (UTC)
@Tagishsimon: Brilliant, it's great to see that going to use so quickly! All the best, NavinoEvans (talk) 10:24, 18 December 2018 (UTC)

Exclusion of items[edit]

select ?item ?itemLabel ?sitelinks
with {
   select distinct ?item 
   where {
      hint:Query hint:optimizer "None".
      values ?item_class {wd:Q34 wd:Q183}
      ?item wdt:P27 ?item_class; wdt:P21 wd:Q6581072; wdt:P31 wd:Q5.
      filter not exists {[] schema:about ?item; schema:isPartOf <https://de.wikipedia.org/>}
   }
} as %subquery
where {
   include %subquery.
   bind(xsd:integer(substr(str(?item), 33)) as ?num).
   ?item wikibase:sitelinks ?sitelinks 
   service wikibase:label {bd:serviceParam wikibase:language "de,en".}
} 
order by desc(?sitelinks) asc(?num)

Try it!

Hi! How do I exclude these items where pornographic actor (Q488111) or sex worker (Q852857) is in occupation (P106)? Doc Taxon (talk) 17:04, 14 December 2018 (UTC)

@Doc Taxon:
select ?item ?itemLabel ?sitelinks
with {
   select distinct ?item 
   where {
      hint:Query hint:optimizer "None".
      values ?item_class {wd:Q34 wd:Q183}
      ?item wdt:P27 ?item_class; wdt:P21 wd:Q6581072; wdt:P31 wd:Q5.
      filter not exists {[] schema:about ?item; schema:isPartOf <https://de.wikipedia.org/>}
      minus {?item wdt:P106 wd:Q488111 . }
      minus {?item wdt:P106 wd:Q852857 . }
   }
} as %subquery
where {
   include %subquery.
   bind(xsd:integer(substr(str(?item), 33)) as ?num).
   ?item wikibase:sitelinks ?sitelinks 
   service wikibase:label {bd:serviceParam wikibase:language "de,en".}
} 
order by desc(?sitelinks) asc(?num)
Try it! --Tagishsimon (talk) 18:12, 14 December 2018 (UTC)
@Tagishsimon: at first sight it really does work, thank you Doc Taxon (talk) 18:37, 14 December 2018 (UTC)

extincted plants[edit]

is it possble to get that out? I tried a bit with is_a plant and extinct but it did not work:

?Pflanzen wdt:P31 wd:Q756.
?Pflanzen wdt:P141 wd:Q237350.
Doesn't look like P141 is widely populated in the plant class tree - only 39 hits out of 12075 plants.
select ?Pflanzen ?PflanzenLabel ?thingLabel where
{
  ?Pflanzen wdt:P31/wdt:P279* wd:Q756.
  ?Pflanzen wdt:P141 ?thing .
#  values ?thing {wd:Q239509 wd:Q237350} .
   service wikibase:label {bd:serviceParam wikibase:language "en".}
}
Try it! --Tagishsimon (talk) 23:54, 14 December 2018 (UTC)

Lista_de_pinturas_de_Josefa_de_Óbidos[edit]

I made a request at - w:pt:user:GualgimG/testes/listas - about the "Lista_de_pinturas_de_Josefa_de_Óbidos" ("List of paintings by Josefa_de_Óbidos").

I had previously create an article about the same issue: w:pt:Lista de pinturas de Josefa de Óbidos.

The BOT status says "no Template".

Please, tell me, what I am doing wrong, so the next time it will go alright. Thank you and Greetings, GualdimG (talk) 00:40, 15 December 2018 (UTC)

@GualdimG: You can now check out my diff, but in short, I think mainly you were missing three lines of the template:
|sort=P571
|columns=P18:Imagem,label:Título,P571:Data de publicação,P180:Retrata,P195:Coleção,P276:Localização
|thumb=128
and you had not closed the '{{Lista do Wikidata' template properly; it wanted }} but you gave it |}
In other news, you do not need SPARQL code to get properties like all of these ?item wdt:P18 ?Imagem. Optional { ?item wdt:P577 ?data_de_publicação.} Optional { ?item wdt:P180 ?retrata.} Optional { ?item wdt:P195 ?coleção.} Optional { ?item wdt:P276 ?localização.} Listeria will fetch these for you. The only reason to use, for instance, ?item wdt:P18 ?Imagem., would be to *require* that the item had an image (i.e. and to stop the row appearing if there is not an image). All of the optional clauses are entirely redundant (although not harmful). hth. --Tagishsimon (talk) 01:40, 15 December 2018 (UTC)
@Tagishsimon: Thank you, so much. Hope now I can made this kind of list by myself. Greetings, GualdimG (talk) 10:03, 15 December 2018 (UTC)

Wikispecies journal without ISSN[edit]

I tried, but SPARQL might as well be Olmec as far as it goes for me. I'm trying to get a query for items

  1. In Wikispecies
  2. With an ISSN (P236) on Wikidata
  3. Whose article title on Wikispecies does NOT start with "ISSN"

The only part I'm not entirely sure about is the third, but it doesn't seem unlikely to me. It would make the job of finding periodicals that needs to move to a different name so much easier for us. Circeus (talk) 05:34, 15 December 2018 (UTC)

@Circeus: ~50 items
SELECT DISTINCT ?article {
  ?article schema:about ?journal ;
           schema:name ?name ;
           schema:isPartOf <https://species.wikimedia.org/> .
  ?journal wdt:P236 ?issn .
  FILTER (!STRSTARTS(?name, "ISSN"))
}
Try it! --Luitzen (talk)
Thanks! this is great! Circeus (talk) 10:23, 15 December 2018 (UTC)
@Luitzen: I'm not sure why, but I can't make this work for use with {{Wikidata list}}... I tried
SELECT ?item {
  ?item schema:about ?journal ;
           schema:name ?name ;
           schema:isPartOf <https://species.wikimedia.org/> .
  ?journal wdt:P236 ?issn .
  FILTER (!STRSTARTS(?name, "ISSN"))
}
Try it! Which works fine in query service, but still won't satisfy Listeriabot... 11:30, 15 December 2018 (UTC)
@Circeus: The secret of Listeria is that it wants the variable named ?item to be a QId. In your query, ?item is a URL. Amend it roughly as follows, and then use Listeria's |columns statement to define the columns and column names you want in your table.
SELECT ?item ?article {
  ?article schema:about ?item ;
           schema:name ?name ;
           schema:isPartOf <https://species.wikimedia.org/> .
  ?item wdt:P236 ?issn .
  FILTER (!STRSTARTS(?name, "ISSN"))
}
Try it! --Tagishsimon (talk) 13:15, 15 December 2018 (UTC)
Thanks. It took some wrangling (mostly because after coming home form a night shift, I needed a good smack on the noggin tpo realize I was missing the end template gah!), but I got it working. Circeus (talk) 15:45, 15 December 2018 (UTC)

English Wikipedia articles with no item[edit]

Is it possible to query for English Wikipedia articles that don't have an item? w:en:Category:Articles without Wikidata item is only populated by one template, so it doesn't really work as a mechanism for this. Jc86035 (talk) 16:53, 15 December 2018 (UTC)

It's most easy to do this in Petscan, rather than WDQS. Example - articles in the category:Women and 5 subcategories, which have no wikidata item, and which are not marked as articles for deletion. (The wikidata bit is in the wikidata tab). And there's this special page of 10,000 unconnected-to-wikidata articles. I think there's a graph of the backlog, somewhere, but I cannot find it (anyone know where it is?).
Meanwhile it looks as if articles end up in w:en:Category:Articles without Wikidata item only if they have a template on them which has the capability to pull data from wikidata and which has code to recognise that the article lacks a wikidata link and as a result add the article to the category. --Tagishsimon (talk) 17:02, 15 December 2018 (UTC)

Without article[edit]

Hello. I want to find all items with Statistical Service of Cyprus Geocode (P4812) with any value, that have not article in Greek Wikipedia. If I am correct, they must be 5 or 7 articles. Xaris333 (talk) 01:07, 16 December 2018 (UTC)

@Xaris333: 6 or 8 depending on which WDQS server I hit. It's maddening:
SELECT ?item ?itemLabel where
{
  ?item wdt:P4812 [] .
  filter not exists {?article schema:about ?item ;
                              schema:isPartOf <https://el.wikipedia.org/> . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Tagishsimon (talk) 01:18, 16 December 2018 (UTC)
@Tagishsimon: thanks!. Miliou (Q11781254) and Kato Paphos (Q47313327) have an article but are in the results. And why Kato Amiantos (Q48413729) is not in? Is it because deprecated rank? Xaris333 (talk) 01:25, 16 December 2018 (UTC)
@Xaris333: Depends which server your query hits, I'm afraid. There are 6 report servers, and they differ in number of triples stored by up to 20 million. Getting them properly synchronised seems to be an unresolved problem. And on occasion you'll get a situation like this, where a row that should not appear does appear. If it's of great concern - and actually, I've done this just now for an experiment - remove the value from the record; save; and then re-add the value. That should purge the gunk out of the system. If the two appear in a report done more than 3 minutes after this post, let me know, just out of interest.
Kato Amiantos (Q48413729) is because of its rank, yes. So here's a fix for that:
SELECT ?item ?itemLabel where
{
  ?item p:P4812 ?s .
  ?s ps:P4812 [] .
  filter not exists {?article schema:about ?item ;
                              schema:isPartOf <https://el.wikipedia.org/> . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it! --Tagishsimon (talk) 01:36, 16 December 2018 (UTC)

It is ok now. Thanks!! Xaris333 (talk) 01:39, 16 December 2018 (UTC)

Federates searches with https://query.wikidata.org/bigdata/namespace/categories/sparql?query=SPARQL[edit]

I came across Wikidata Query Service/Categories, but find it's not accessible via WDQS, but available from https://query.wikidata.org/bigdata/namespace/categories/sparql?query=SPARQL - which I vaguely understand to be a SPARQL endpoint. Can we access that endpoint thru WDQS using a federated query? If so, the SPARQL below is a candidate; I tried replacing gas:service with <https://query.wikidata.org/bigdata/namespace/categories/sparql?query=SPARQL> somewhat after this example, but that turned out to be a fail.

PREFIX gas: <http://www.bigdata.com/rdf/gas#>
prefix mediawiki: <https://www.mediawiki.org/ontology#> 

SELECT * WHERE {
SERVICE gas:service {
     gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.BFS" .
     gas:program gas:linkType mediawiki:isInCategory .
     gas:program gas:traversalDirection "Reverse" .
     gas:program gas:in <https://en.wikipedia.org/wiki/Category:Ducks>. # one or more times, specifies the initial frontier.
     gas:program gas:out ?out . # exactly once - will be bound to the visited vertices.
     gas:program gas:out1 ?depth . # exactly once - will be bound to the depth of the visited vertices.
     gas:program gas:maxIterations 8 . # optional limit on breadth first expansion.
  }
} ORDER BY ASC(?depth)

Try it!

Presuming, reader, you are still with me, I notice the /categories page remarks " the dataset includes only categories and not pages belonging to categories (the latter would be much bigger data set)." Do we know if any thought has been given to putting that dataset in place? It would be ideal to have a petscan-like ability within WDQS. The games we could play. --Tagishsimon (talk) 03:09, 16 December 2018 (UTC)

Wikielements whose geography coordinates do not match with they country[edit]

Hello, has it already been asked for? A query that would return wikielements (for my need : airports) that are in a country albeit having coordinates pinpointing another country. An example : https://www.wikidata.org/w/index.php?title=Q4402324&oldid=717348794 Bouzinac (talk) 10:48, 16 December 2018 (UTC)

@Bouzinac: I suspect many of these will be false positives. Country items can have coordinates of northernmost point (P1332) through to coordinates of westernmost point (P1335), but some countries like France have more than one set (for the main country, and for its far-flung colonies). Some countries like the UK (iirc) have just one bounding set of coords, but we have airports with country=UK which are in far-flung places not within the UK's bounding coords (e.g. Bermuda). So. Here's the basic query. I remove countries with more than one set of bounding coords (because an airport will always fail against one or other set of bounds), and remove airports with more than one country (because same reason, the airport will always fail against one or other country's bounds), and then check the lat & long coords against the country bounding coords. Even this is a very rough test - we're testing against 4 values, and not against a shapefile of the entire set of bounding coords for the country. An airport coordinate can be within the 4 compass bounds but outside the country, and we will not spot it. Note also that you may have to select small sets of airports to avoid timeouts. The example below uses international airports only. Even ?item wdt:P31 wd:Q644371. seems to be too much for it, so there is a commented out values ?airport_country {wd:Q30 wd:Q123 wdQ456} statement in the airport named-query ... you can uncomment that and add a list of countries to be checked if you want to work through all airports.
SELECT ?item ?itemLabel ?countryLabel ?lat ?lon ?nlat ?slat ?elong ?wlong with 
{
 select distinct ?country  ?nlat ?stal ?elong ?wlong (count(?country) as ?count) where
  { # restrict query to countries that have only one set of bounding coords
    ?country wdt:P463 wd:Q1065 . #membership of UN taken as a proxy that this is a country
    ?country p:P1332 [psv:P1332 [wikibase:geoLatitude ?nlat;] ; ] .   #get the northernmost coord for the country
    ?country p:P1333 [psv:P1333 [wikibase:geoLatitude ?slat;] ; ] .   #if there is more than one northrn coord
    ?country p:P1334 [psv:P1334 [wikibase:geoLongitude ?elong;] ; ] . # the having( ?count=1) at the foot of this
    ?country p:P1335 [psv:P1335 [wikibase:geoLongitude ?wlong;] ; ] . # named-query will reject the country
  } group by ?country ?nlat ?stal ?elong ?wlong having( ?count=1) } as %countries

with {select ?item ?country (count(?item) as ?count2) where
{  # restrict query to airports that are in one country only
  ?item wdt:P31 wd:Q644371.
  ?item wdt:P17 ?country .          #get country for the airport
  # values ?airport_country {wd:Q30 wd:Q123 wd:Q456}  #restrict the set of countries for which we will examine airports
  } group by ?item ?country having (?count2=1) } as %airports

{ 
  include %countries 
  include %airports   
  ?item p:P625 [ps:P625 ?coord; psv:P625 [           # get coords for the airport        
             wikibase:geoLongitude ?lon;
             wikibase:geoLatitude ?lat;] ; ] .
  bind(if(?lat>?nlat,1,0) as ?ntest)                 #compare country northernmost latitude with the airport northern latitude
  bind(if(?lat<?slat,1,0) as ?stest) 
  bind(if(?lon>?elong,1,0) as ?etest) 
  bind(if(?lon<?wlong,1,0) as ?wtest)
 
  #check if any of the above tests=1 indicating there's a problem and we should display the row
  bind(if(?ntest=1,1,if(?etest=1,1,if(?wtest=1,1,(if(?stest=1,1,0))))) as ?bigtest)
  filter(?bigtest=1)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" .}
}
Try it! --Tagishsimon (talk) 17:17, 16 December 2018 (UTC)
To avoid such false positives I used the distance shortest to the country / territory / transportation hub and ranked the airport with a long distance. These may also give false positives as not every airport has a territory filled.
SELECT ?airport ?airportLabel (MIN(?dist) AS ?distance )
WHERE {
 ?airport wdt:P238 ?any. # any airport (having an IATA airport code)
 ?airport wdt:P625 ?airportcoord.

 ?airport wdt:P17|wdt:P131|wdt:P931 ?loc. # country / territory / transportation hub
 ?loc wdt:P625 ?loccoord.
 BIND(geof:distance(?airportcoord, ?loccoord) AS ?dist) 

 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }         
}
GROUP BY ?airport ?airportLabel
ORDER BY DESC(?distance)
LIMIT 200
Try it!
HenkvD (talk) 17:42, 16 December 2018 (UTC)
Thanks. As every country does not have round-shape, it is indeed difficult to check that on a "who is the farthest from the geographical center" basis. The second query looks simple and interesting, should be used with an exclusion of remote colonies, territories : there is indeed many false positives with French Polynésia, Alaska and so on. The hunt on the ugly duckling is still on :) Bouzinac (talk) 20:46, 16 December 2018 (UTC)
  • I think we already had a couple of iterations of this. Apparently some still fell through. --- Jura 15:26, 17 December 2018 (UTC)

Biggest catastrophes[edit]

Hello, I am surprised not to see any illnesses in these results :

SELECT ?item ?itemLabel (sample(?geo) as ?coord) (sample(?typeLabel) as ?typeLabel) (max(?morts) as ?maxmorts) WHERE { 
?type wdt:P279* wd:Q3839081 .
?item wdt:P31 ?type ;
      wdt:P625 ?geo .
?item wdt:P1120 ?morts
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
group by  ?item ?itemLabel
order by desc(?maxmorts)

Try it!

@Bouzinac: Pandemics tend not to have coords, becuase they're widespread, geographically. Try:
SELECT ?item ?itemLabel (sample(?geo) as ?coord) (sample(?typeLabel) as ?typeLabel) (max(?morts) as ?maxmorts) WHERE 
{ 
  ?type wdt:P279* wd:Q3839081 . 
  ?item wdt:P31 ?type .
  optional {?item wdt:P625 ?geo . }
  ?item wdt:P1120 ?morts
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
group by  ?item ?itemLabel
order by desc(?maxmorts)
Try it! --Tagishsimon (talk) 18:13, 17 December 2018 (UTC)
Of course! Many thanks Simon Bouzinac (talk) 20:11, 17 December 2018 (UTC)

List of countries (current+old)[edit]

Hello, I'd like to enrich this query with the following needs :

SELECT ?pays #country
?paysLabel #countryname
(sample(?naturelabel) as ?nature) 
(sample(?capitaleLabel) as ?capital) 
?PaysPrecedentLabel #preceding country
(year(?Fondation) as ?AnneeFondation) #foundation year
(year(?Disparition) as ?AnneeDisparition) #disappearance year
?PaysRemplacantLabel #following country
WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?pays (wdt:P31/wdt:P279*) wd:Q6256 .
  OPTIONAL { ?pays wdt:P17 ?pays. }
  OPTIONAL { ?pays wdt:P31 ?nature. }
  OPTIONAL { ?pays wdt:P36 ?capitale. }
  OPTIONAL { ?pays wdt:P1365 ?PaysPrecedent. }
  OPTIONAL { ?pays wdt:P571 ?Fondation. }
  OPTIONAL { ?pays wdt:P576 ?Disparition. }
  OPTIONAL { ?pays wdt:P1366 ?PaysRemplacant. }
}
group by ?pays ?paysLabel ?PaysPrecedentLabel ?Fondation ?Disparition ?PaysRemplacantLabel
order by ?paysLabel ?Fondation

Try it!

concatenate every information so that each country has only one row ? Bouzinac (talk) 12:35, 18 December 2018 (UTC)

@Bouzinac: This isn't necessarily a great solution, mainly through lack of time. Major changes are that everything in the select except ?paysLabel is aggregated; and, moreover, we now have (max(?Fondation) as ?AnneeFondation) so for each country we're trying to get only the immediately prior country, rather than all of the prior countries. The logic is that we should get a row for Country(n) was preceded by Country(n-1); and we should have a separate row for Country(n-1) preceded by Country(n-2). Right now, I suspect ?PaysRemplacantLabel/?PRL is somewhat arbitrary, because we sample, and could get any of the succeeding countries ... ideally we'd play a (min(?start date) type game to get the immediately succeeding country. Obviously, where start dates are missing we're back to getting arbitrary values on a garbage in garbage out principle.
SELECT ?pays #country
?paysLabel #countryname
(sample(?naturelabel) as ?nature) 
(sample(?capitaleLabel) as ?capital) 
(sample(?PaysPrecedentLabel) as ?PPL) #preceding country
(max(?Fondation) as ?AnneeFondation) #foundation year
(year(?Disparition) as ?AnneeDisparition) #disappearance year
(sample(?PaysRemplacantLabel) as ?PRL) #following country
WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?pays (wdt:P31/wdt:P279*) wd:Q6256 .
  OPTIONAL { ?pays wdt:P17 ?pays. }
  OPTIONAL { ?pays wdt:P31 ?nature. }
  OPTIONAL { ?pays wdt:P36 ?capitale. }
  OPTIONAL { ?pays wdt:P1365 ?PaysPrecedent. }
  OPTIONAL { ?pays wdt:P571 ?Fondation. }
  OPTIONAL { ?pays wdt:P576 ?Disparition. }
  OPTIONAL { ?pays wdt:P1366 ?PaysRemplacant. }
}
group by ?pays ?paysLabel ?PaysPrecedentLabel ?Fondation ?Disparition ?PaysRemplacantLabel
order by ?paysLabel ?Fondation
Try it! --Tagishsimon (talk) 13:44, 18 December 2018 (UTC)

Thanks! Very much oddities inside… ;/ Bouzinac (talk) 19:34, 18 December 2018 (UTC)

traversing through multiple properties[edit]

I would like to build a treeview mapping all public bodies under the central government of a country (in my particular case: Q52060250 as root item). The difficulty is that to map the whole system, items should be traversed through any of the following properties: instance of (P31), part of (P361) or subclass of (P279), since the relationship between the various public bodies are coded in any of those properties. I know how to do a query that traverses through one property, but not traversing through multiple properties.--Tdombos (talk) 17:36, 18 December 2018 (UTC)