Wikidata:Request a query

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 and Help:Dataset sizing. 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 and Wikidata:SPARQL query service/query optimization. Help resources about Wikidata Query Service (Q20950365) and SPARQL: Wikidata:SPARQL query service/Wikidata Query Help and Category:SPARQL. To report an issue about the Query Service (interface, results views, export...) please see Wikidata:Contact the development team/Query Service and search.

Translate this header box! Start a new discussion

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

Protected areas in Switzerland

Hello, I would like a query of all objects (protected areas),

• which are a subclass of (P279) Protected area in nature or landscape conservation (Q473972),
• whose state (P17) is Switzerland (Q39).

And I wish the result in a table with the following fields in horizontal order:

• Q-ID of the object (protected area)
• official name
• is part of (P36) (in German, p. ex. Bundesinventar der Auengebiete von nationaler Bedeutung)
• year of admission (P547)
• Canton(s) in German (located in administrative unit P131)
• municipality(ies) in German (located in administrative unit P131)
• geogr. coordinates
• image (thumbnail)
• Commons category

Is this possible? Many thanks in advance. Matutinho (talk) 19:51, 2 August 2023 (UTC)

Hi, is it ok ? https://w.wiki/7BNd

(note there can be duplicates because of possible multiples entries) Bouzinac ^{💬●✒️●💛} 08:17, 3 August 2023 (UTC)

Hi@Bouzinac. Thank you very much, for the moment your solution is OK. I will analyse the result. It helps me to develop a handy model in Wikidata for the objects of the federal inventories of amphibian spawning areas (e.g. Q108204981), alluvial zones (e.g. Q108086388), raised and transitional mires (e.g. Q108086997), fenlands (e.g. Q108088003) and dry grasslands and pastures (e.g. Q108205837). In dewiki the lists are quite complete. In the canton of Graubünden alone there are more than 1000 Dry Grasslands/Pastures of national importance. Thanks.Till later. Matutinho (talk) 09:39, 3 August 2023 (UTC)

Hi @Bouzinac. I have a question. Can you change the request code so that all values are output for the property 'lies in the administrative unit'? Normally, an item has the value of a canton and a municipality (for example Q108086922). In some cases, however, an item has the value of one canton and several municipalities (for example Q108086547) or even several cantons and several municipalities (for example Q108085987). Thanks. Matutinho (talk) 11:50, 4 August 2023 (UTC)

significant event and time

I want query all objects in my hometown Dresden with significant event (P793). I also get the kind of event like demolition (Q331483). But I need also the time. So that I can order all events by time. Sometime it is type start time (P580), end time (P582). Often it is point in time (P585)

#---------------------------------------------------------------------------------------
#---------------------------------------------------------------------------------------
#defaultView:Table
SELECT ?item ?itemLabel  ?significant_eventLabel ?typeLabel ?time 
WHERE {
  ?item (wdt:P131/wdt:P279*) wd:Q1731.                # items in Dresden
  ?item wdt:P793 [  pq:* ?significant_event  ].       # significant event
  #?item p:P793 [  pq:* ?eventstatment ].   
  #?eventstatment prov:* ?type.
  #?eventstatment psv:* ?time.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
}
order by ?time ?significant_eventLabel ?itemLabel

Try it!

How can I fill the time column. And also the type? --sk (talk) 20:44, 4 August 2023 (UTC)

@Stefan Kühn: My try:

#defaultView:Table
SELECT ?item ?itemLabel  ?significant_eventLabel ?typeLabel ?time 
WHERE {
  ?item (wdt:P131/wdt:P279*) wd:Q1731.                # items in Dresden
  ?item wdt:P793 [  pq:* ?significant_event  ].       # significant event
  OPTIONAL {?significant_event wdt:P31 ?type}
  ?item p:P793 ?eventstatment .   
  ?eventstatment pq:P585 ?time.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
}
order by ?time ?significant_eventLabel ?itemLabel

Try it!

I think that query does what you want.--Pere prlpz (talk) 11:03, 10 August 2023 (UTC)

lakes in Sicily

Iwant to get a list of all the lakes in Sicily (the Italian region, not the island).

SELECT ?See ?SeeLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?See wdt:P31 wd:Q23397;
    wdt:P131 wd:Q1460.
}

Try it!

gets just one result. So does

SELECT ?See ?SeeLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?See (wdt:P31/wdt:P279*) wd:Q23397;
    wdt:P131 wd:Q1460.
}

Try it!

where the subclasses of lake are included.

Other lakes are indicated as lying in one of the nine admistrative area which is a subdivision of the region Sicily (e.g Metropolitan City of Palermo or Free municipal consortium of Agrigento), still others as lying in municipalties which ly in an admistrative area which is a subdivision of the region Sicily. How to get them all into the list? Bjs (talk) 21:11, 4 August 2023 (UTC)

You have do add a plus after P131.

SELECT ?See ?SeeLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?See wdt:P31 wd:Q23397;
    wdt:P131+ wd:Q1460.
}

Try it!

So you get also alle lakes in all subregions. --sk (talk) 22:09, 4 August 2023 (UTC)

Just a little + sign? Thanks for the help. However, this query only finds 14 lakes. It appears that most of the lakes are not assigned to an administratve unit at all. I probably will have to make a query for state=Italy and output a map. --Bjs (talk) 22:56, 4 August 2023 (UTC)

I use in this case mostly a wikibase:around sparql. So I can see all lakes and add an administratve unit.

#defaultView:Map
SELECT ?item ?itemLabel ?coordinate WHERE {
  wd:Q1460 wdt:P625 ?Center.    # Sicily
  ?item wdt:P31 wd:Q23397;      # lake
  SERVICE wikibase:around {
    ?item wdt:P625 ?coordinate.
    bd:serviceParam wikibase:center ?Center.
    bd:serviceParam wikibase:radius "275". # 275 Kilometer around
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]". }
}

Try it!

So I find all lakes. --sk (talk) 05:06, 5 August 2023 (UTC)

That's better, thank you. Good idea to take the distance from the coordinates indicated for Sicily. But still, only few lakes are found. I tried to include subclasses like reservoir (Q131681) by using (wdt:P31/wdt:P279*), but that caused a timeout. I shall try at least with with UNION of lake (Q23397) and reservoir (Q131681). --Bjs (talk) 07:08, 5 August 2023 (UTC)

Union works well, but even so, many of the lakes are not found because no Wikidata entries exists. It appears I will have to use de:Liste von Seen in Sizilien for completing Wikidata instead of using Wikidata to complete my list. Thanks for your help. Bjs (talk) 07:57, 5 August 2023 (UTC)

I now looke how many underclasses lake (Q23397) has and understand why a time-out occurs. Is it possible to restrict the search to the first-level underclasses? --Bjs (talk) 10:00, 5 August 2023 (UTC)

@Bjs: Find things within 275 km of the Sicily coord. Find all lakes and subclasses. Join them. Filter to ensure they're within a Sicily bounding box.

#defaultView:Map
SELECT ?item ?itemLabel ?coordinate WITH {
  SELECT ?item WHERE {
  wd:Q1460 wdt:P625 ?Center.    # Sicily
  SERVICE wikibase:around {
    ?item wdt:P625 ?coordinate.
    bd:serviceParam wikibase:center ?Center.
    bd:serviceParam wikibase:radius "275". # 275Kilometer around
  }
    } } as %i
WITH {
  SELECT ?item WHERE {
      ?item wdt:P31/wdt:P279* wd:Q23397. hint:Prior hint:gearing "reverse".      # lake
} } as %j
WHERE
{
  include %i 
  include %j
  ?item p:P625 ?stat .
  ?stat a wikibase:BestRank .  
  ?stat psv:P625/wikibase:geoLongitude ?long.
  ?stat psv:P625/wikibase:geoLatitude ?lat.
  wd:Q1460 p:P1332/psv:P1332/wikibase:geoLatitude ?north . filter(?lat <= ?north )
  wd:Q1460 p:P1333/psv:P1333/wikibase:geoLatitude ?south . filter(?lat >= ?south )
  wd:Q1460 p:P1334/psv:P1334/wikibase:geoLongitude ?east . filter(?long <= ?east )
  wd:Q1460 p:P1335/psv:P1335/wikibase:geoLongitude ?west . filter(?long >= ?west )
  ?item wdt:P625 ?coordinate.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en,[AUTO_LANGUAGE]". }
}

Try it!

--Tagishsimon (talk) 11:02, 5 August 2023 (UTC)

This is phantastic, exactly what I needed, and also fast. Ho does this query avoid that P31/wdt:P279* causes a time-out whereas replacing :P31 by P31/wdt:P279* in the suggestion by sk does?

@Tagishsimon: The rectangle including Sicily (Q1460) also includes Malta (Q233). Is it possible to exclude Malta (Q233) by an additional condition that (long <= 13.5) OR (lat >= 36.5) or so that all items southeast of this point are removed? --Bjs (talk) 20:28, 8 August 2023 (UTC)

After some failures, I now succeeded with filter((?long <= 13.5) || (?lat >= 36.5) ) Bjs (talk) 14:50, 9 August 2023 (UTC)

@Bjs: Might be as easy to chop Maltese hits out with the wikibase:around service, something like (and 50km is just a guess from me):

#defaultView:Map
SELECT ?item ?itemLabel ?coordinate WITH {
  SELECT ?item WHERE {
  wd:Q1460 wdt:P625 ?Center.    # Sicily
  SERVICE wikibase:around {
    ?item wdt:P625 ?coordinate.
    bd:serviceParam wikibase:center ?Center.
    bd:serviceParam wikibase:radius "275". # 275 Kilometer around
  }
    } } as %i
WITH {
  SELECT ?item WHERE {
      ?item wdt:P31/wdt:P279* wd:Q23397. hint:Prior hint:gearing "reverse".      # lake
} } as %j
WHERE
{
  include %i 
  include %j
  ?item p:P625 ?stat .
  ?stat a wikibase:BestRank .  
  ?stat psv:P625/wikibase:geoLongitude ?long.
  ?stat psv:P625/wikibase:geoLatitude ?lat.
  wd:Q1460 p:P1332/psv:P1332/wikibase:geoLatitude ?north . filter(?lat <= ?north )
  wd:Q1460 p:P1333/psv:P1333/wikibase:geoLatitude ?south . filter(?lat >= ?south )
  wd:Q1460 p:P1334/psv:P1334/wikibase:geoLongitude ?east . filter(?long <= ?east )
  wd:Q1460 p:P1335/psv:P1335/wikibase:geoLongitude ?west . filter(?long >= ?west )
  
  minus  {
    wd:Q233 wdt:P625 ?Center2.    # Sicily
    SERVICE wikibase:around {
      ?item wdt:P625 ?coordinate2.
      bd:serviceParam wikibase:center ?Center2.
      bd:serviceParam wikibase:radius "50". # 50 Kilometer around
  } }
   
  ?item wdt:P625 ?coordinate.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en,[AUTO_LANGUAGE]". }
}

Try it!

--Tagishsimon (talk) 15:56, 9 August 2023 (UTC)

This works fine. With regard to performance, is this minussection better than filter((?long <= 13.5) || (?lat >= 36.5) ) which also excludes Malta? --Bjs (talk) 21:15, 9 August 2023 (UTC)

tbh, I didn't really understand how that pair of coords helped to rule out Malta, but continue to include Lampedusa, so I went for plan B. --Tagishsimon (talk) 00:04, 10 August 2023 (UTC)

Lampedusa has ?lat < 36.5, but ?long <= 13.5 so the OR-relation outputs true. This pair of coords merely cuts-off a smaller rectangle in the south-east corner of the larger Sicily rectangle. Luckily, there is no part of Sicilia directly south of Malta. Bjs (talk) 09:44, 10 August 2023 (UTC)

Just a comment: a problem with that kind of questions is that a lot of items lack located in the administrative territorial entity (P131). In Italy, nearly 20000 items have coordinates but not P131, including more than 200 lakes, a few of them in Sicily.--Pere prlpz (talk) 09:44, 10 August 2023 (UTC)

no lakes in Sicily any longer --Bjs (talk) 18:01, 14 August 2023 (UTC)

But the problem is also the other way round. Many items, also lakes, have no coordinates, but P131, or they do not have either of them, or they have no wikidata enty at all. I'm just working on the lakes in Sicily to make sure that all of them included in de:Liste von Seen in Sizilien have a wikidata entry indicating both coordinate location (P625) and located in the administrative territorial entity (P131). Bjs (talk) 09:52, 10 August 2023 (UTC)

250 km was to small to include Lampedusa, 275 does the job. I changed the queries above correspondingly--Bjs (talk) 16:50, 14 August 2023 (UTC)

Missing result

I have this query (to show cadatral areas in Prague ordered by number of houses with wikidata item):

SELECT DISTINCT ?item ?itemLabel (count (DISTINCT ?bud) as ?count) 
WHERE {
    ?item wdt:P31 wd:Q20871353 .
    ?item wdt:P17 wd:Q213 . 
    ?item wdt:P131 ?prs .
    {  
    ?prs wdt:P131 wd:Q1085 .
    }
    UNION
    {
    ?prs wdt:P131 ?vrs .
    ?vrs wdt:P131 wd:Q1085 .  
    } 
    UNION
    {
    ?prs wdt:P131 ?vrs .
    ?vrs wdt:P131 ?drs .  
    ?drs wdt:P131 wd:Q1085 .  
    } 
    SERVICE wikibase:label { bd:serviceParam wikibase:language "cs" }
    ?bud wdt:P131 ?item .
    ?bud wdt:P4856 ?cp .
} GROUP BY ?item ?itemLabel
ORDER BY DESC (?count)

Try it!

However, for example New Town (Q753219) does not appear in the results, any idea why? Jklamo (talk) 09:00, 10 August 2023 (UTC)

First check: there are 700 buildings in New Town (Q753219), so the problem is a different one.--Pere prlpz (talk) 11:12, 10 August 2023 (UTC)

Changing line 5 from "?item wdt:P131 ?prs ." to "?item wdt:P131? ?prs ." seems to do the trick. Infrastruktur (talk) 11:17, 10 August 2023 (UTC)

Nice, it works! Any documentation about these magic symbols (?/* or maybe even more)? Jklamo (talk) 16:13, 10 August 2023 (UTC)

It was more of a quick and dirty fix, a better fix would probably be something like https://w.wiki/7E9U. It's documented here. Infrastruktur (talk) 17:10, 10 August 2023 (UTC)

Grouping years in a bar graphic

Hello, I'm working in Scholia with some graphics using a series of academic publications about a certain topic in order to to some research. In this link theres a bar graphic with years in x axis, but I would like to groups the years, for instance, in decades, nos in single years. Is it possible? how? By the way, sorry if my english is not clear, and thanks in advance Mikelzubi (talk) 23:05, 14 August 2023 (UTC)

You can do it with two changes in the query you provided:

1. replace ?year with ?year_ in the GROUP BY (probably not needed; but it keeps the query consistent)

2. replace (STR(?year) AS ?year) with (STR(FLOOR(?year_/10)) AS ?year) in the SELECT code. This makes it bucket by the year divided by 10 (and truncated with FLOOR, so it's not a decimal number)

if you need to shift the buckets, for example so the last bucket is 2013 to 2023, you can do it like this: (STR(FLOOR((?year_-7)/10)) AS ?year)

PREFIX target: <http://www.wikidata.org/entity/Q115632712>
:SELECT
:  (STR(FLOOR(?year_/10)) AS ?year)
:  (COUNT(?work) AS ?number_of_publications)
:  # Work type used to color the bar chart
:  ?type
:WITH {
:  # Find works with the topic. Also report the year
:  SELECT
:    ?work (MIN(?years) AS ?year_) (1 AS ?dummy) (SAMPLE(?article_type_) AS ?article_type)
:  WHERE {
:    ?work wdt:P921 / (wdt:P1433*/wdt:P279*  ) target: .
:    ?work wdt:P577 ?dates .
:    BIND(YEAR(?dates) AS ?years) .
:    ?work wdt:P31 ?article_type_ .
:  }
:  GROUP BY ?work 
:} AS %works
:WITH {
:  SELECT ?year_ WHERE {
:    # default values = 0
:    ?year_item wdt:P407 wd:Q577 . 
:    ?year_item wdt:P585 ?date .
:    BIND(YEAR(?date) AS ?year_)
:  }
:} AS %default_counts
:WITH {
:  # Find earliest publication year
:  SELECT (MIN(?year_) AS ?earliest_year) WHERE {
:    INCLUDE %works
:  }
:  GROUP BY ?dummy
:} AS %earliest  
:WHERE {
:  {
:    INCLUDE %works
:    ?article_type rdfs:label ?type . FILTER (LANG(?type) = "es")
:  }
:  UNION
:  {
:    INCLUDE %default_counts
:    BIND("_" AS ?type)
:  }
:  INCLUDE %earliest
:  BIND(YEAR(NOW()) AS ?this_year)
:  FILTER (?year_ >= ?earliest_year && ?year_ <= ?this_year && ?year_ >= YEAR("1860-01-01"^^xsd:dateTime))
:}
:GROUP BY ?year_ ?type
:ORDER BY ?year_
:

Try it!

ProgVal (talk) 19:33, 15 August 2023 (UTC)

Forts with 2 coordinates quoted, very close each other

A fort should only have one location, but we can end up with many values, often with inappropriate references, like wikipedia itself. So I'd like a list of fort (Q1785071) which have 2 coordinate location (P625) quoted, with the reference label if present but with a de-minimis difference of 100m, to avoid genuine disagreements. Vicarage (talk) 10:26, 15 August 2023 (UTC)

:SELECT ?fort ?fortLabel ?location1 ?location2 ?distance
:WHERE 
:{
:  ?fort wdt:P31 wd:Q1785071.
:  ?fort wdt:P625 ?location1.
:  ?fort wdt:P625 ?location2.
:  BIND(geof:distance(?location1, ?location2) as ?distance).
:  
:  # deduplicate (by picking location1 to be either east or exactly north of location2)
:  FILTER (
:    geof:longitude(?location1) < geof:longitude($location2)
:    || (
:      geof:longitude(?location1) = geof:longitude($location2)
:      && geof:latitude(?location1) < geof:latitude($location2)
:    )
:  ).
:
:  # locations must be different
:  FILTER (?distance > 0).
:  
:  # maximum distance
:  FILTER (?distance < 100).
:  
:  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
:}
:

Try it!

ProgVal (talk) 19:24, 15 August 2023 (UTC)