Wikidata:Request a query/Archive/2018/01

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

Lighthouses in Sweden with more than one color without qualifier(s)

I have managed to make a query of lighthouses in Sweden by the number of colors (P462), but I want to exclude colors with the qualifier (P518). How do I do that? Simply adding FILTER NOT EXISTS { ?color pq:P518 ?part } does not seems to work. /ℇsquilo 10:27, 1 January 2018 (UTC)

This seems to work. There might be a more efficient way to do it, by collapsing ?item wdt:P462 ?colour and OPTIONAL {?item p:P462 ?mem . ?mem ps:P462 ?colour; pq:P518 ?qual . together in some clever way; but in my version the first of these restricts the query to lighthouses with colour, the second enquires about the qualifier ... I've not found a way to test for the absence of the qualifier without including lighthouses with no colour at all, in a single line of code. As normal, I'd like to stress that I really have not got a clue about SPARQL, and you should really consult an expert :) --Tagishsimon (talk) 12:28, 1 January 2018 (UTC)

SELECT DISTINCT ?item ?itemLabel ?colour ?colourLabel ?qual WHERE {
  ?item wdt:P31 wd:Q39715 . #instance of lighthouse
  ?item wdt:P17 wd:Q34 . #country of Sweden
  ?item wdt:P462 ?colour . #must have a colour
  
  OPTIONAL {?item p:P462 ?mem . #item has a colour
  ?mem ps:P462 ?colour; pq:P518 ?qual . } # colour has a P518 qualifier

  FILTER(!BOUND(?qual)) # qualifier is null
  SERVICE wikibase:label { bd:serviceParam wikibase:language "se,en,fr,nl,de,it,es". }
  }

Try it!

--Tagishsimon (talk) 12:28, 1 January 2018 (UTC)

And, presuming it is the count of colours that is important, items with a colour qualifier are excluded from the count in this example. (Amend the query to # FILTER(!BOUND(?qual)) to see the difference.)

SELECT ?item ?itemLabel (count(?colour) as ?count) WHERE {
  ?item wdt:P31 wd:Q39715 . #instance of lighthouse
  ?item wdt:P17 wd:Q34 . #country of Sweden
  ?item wdt:P462 ?colour . #must have a colour
  
  OPTIONAL {?item p:P462 ?mem . #item has a colour
  ?mem ps:P462 ?colour2; pq:P518 ?qual . } # colour has a P518 qualifier

  FILTER(!BOUND(?qual)) # qualifier is null
  SERVICE wikibase:label { bd:serviceParam wikibase:language "se,en,fr,nl,de,it,es". }

  }
group by ?item ?itemLabel

Try it!

--Tagishsimon (talk) 12:51, 1 January 2018 (UTC)

@Tagishsimon: Your second query does not only exclude colors with a P518, is seems to exclude the entire lighthouse, e.g. Q40125473, if any of its colors has a P518. --Larske (talk) 13:08, 1 January 2018 (UTC)

Agreed. Ditto the first query. Not yet obvious to me why, but I'll only have time much later today to stat digging into it some more. --Tagishsimon (talk) 14:20, 1 January 2018 (UTC)

Note: The language code for Swedish is "sv", not "se".

Here is another approach with the number of colors (both total and excluding P518) rather than the single colors listed. Note that only lighthouses with at least one P462 are included.

SELECT ?item ?itemLabel (SUM(?colors) AS ?colors_without_P518) (COUNT(?colorstatement) AS ?all_colors) WHERE
{
  ?item wdt:P31 wd:Q39715 .
  ?item wdt:P17 wd:Q34 .    
  ?item p:P462 ?colorstatement .
  OPTIONAL {?colorstatement pq:P518 ?del . }
  BIND(IF(BOUND(?del),0,1) AS ?colors)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}
GROUP BY ?item ?itemLabel 
ORDER BY DESC(?colors_without_P518) ?itemLabel

Try it!

--Larske (talk) 12:54, 1 January 2018 (UTC)

@Esquilo: Looking at the thread title you may want to add the line

HAVING (?colors_without_P518>1)

just after the GROUP BY... line

--Larske (talk) 13:22, 1 January 2018 (UTC)

Thank you Tagishsimon and Larske for your answers! The first queries did not exclude lighthouses with only one color, but the last one did exactly what I wanted. /ℇsquilo 14:30, 1 January 2018 (UTC)

Graffiti artists

Hi, I'm looking for all graffiti sprayers without known identity. So far I used this query to get all graffiti artists (don't know why I get everything in German):

SELECT ?Beruf ?offizieller_Name ?BerufLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?Beruf wdt:P106 wd:Q15095148.
  OPTIONAL { ?Beruf wdt:P1448 ?offizieller_Name. }
  ?Beruf wdt:P31 wd:Q5.
}

Try it!

Thanks in advance.

You seem to be pretty much there already, if I'm not missing something. No graffiti artist has a official name (P1448) so far as I can see. FILTER (BOUND(?offizieller_Name)) in the example below restricts the result set to those items that have official name (P1448) - i.e. zero rows (and FILTER (!BOUND(?offizieller_Name)) would find the set that have a null official name (P1448). I confess to being uncertain about what the "official name" of a graffiti artist would be...

SELECT ?Beruf ?offizieller_Name ?BerufLabel WHERE {
 ?Beruf wdt:P31 wd:Q5.
 ?Beruf wdt:P106 wd:Q15095148.
 OPTIONAL { ?Beruf wdt:P1448 ?offizieller_Name. }
 FILTER (BOUND(?offizieller_Name))
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr,nl,de,it,es". }
}

Try it!

--Tagishsimon (talk) 12:43, 1 January 2018 (UTC)

Check CAS numbers with a SPARQL request

Hello,

I would like a SPARQL request for validate the all CAS Number (CAS Registry Number (P231)) in wikidata, without a Python script.

With this Python validation script from Github https://github.com/simonengelke/CAS_Validation i have fix CAS numbers who failed.

The goal, now, is to have this validation in a request for add this request as a complexe constraint in Property talk:P231. Manu1400 (talk) 14:51, 2 January 2018 (UTC)

With PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> we can use the maths operator xsd:float(?value). Manu1400 (talk) 14:59, 2 January 2018 (UTC)

This query lists all the items with CAS Registry Number (P231) and the status for "correct pattern" and "correct checksum". If you only want to see the erroneus ones, activate the filter on line 20.

SELECT ?item ?cas ?correct_pattern ?correct_checksum WHERE
{
  ?item wdt:P231 ?cas .
  BIND(REGEX (str(?cas), '^[1-9][0-9]{1,6}-[0-9]{2}-[0-9]$') AS ?correct_pattern )
  BIND(replace(str(?cas), "-","") AS ?c)
  BIND(STRLEN(?c) AS ?strlen) 
  BIND(xsd:integer(substr(?c,?strlen,1)) AS ?val )
  BIND(xsd:integer(substr(?c,?strlen-1,1)) AS ?x1 )
  BIND(xsd:integer(substr(?c,?strlen-2,1)) AS ?x2 )
  BIND(xsd:integer(substr(?c,?strlen-3,1)) AS ?x3 )
  BIND(IF(?strlen>4,xsd:integer(substr(?c,?strlen-4,1)),0) AS ?x4 )
  BIND(IF(?strlen>5,xsd:integer(substr(?c,?strlen-5,1)),0) AS ?x5 )
  BIND(IF(?strlen>6,xsd:integer(substr(?c,?strlen-6,1)),0) AS ?x6 )
  BIND(IF(?strlen>7,xsd:integer(substr(?c,?strlen-7,1)),0) AS ?x7 )
  BIND(IF(?strlen>8,xsd:integer(substr(?c,?strlen-8,1)),0) AS ?x8 )
  BIND(IF(?strlen>9,xsd:integer(substr(?c,?strlen-9,1)),0) AS ?x9 )
  BIND(?x1+?x2*2+?x3*3+?x4*4+?x5*5+?x6*6+?x7*7+?x8*8+?x9*9 AS ?sum0)
  BIND(?sum0-(xsd:integer(?sum0/10)*10) AS ?sum )
  BIND(?sum=?val AS ?correct_checksum)
#  FILTER(!?correct_pattern|| !?correct_checksum)
}
ORDER BY ?cas

Try it!

--Larske (talk) 20:19, 2 January 2018 (UTC)

Items without aliases

Hello. I'm beginner in sparql, and I've a problem writing a sparql query that get all items without aliases, for a specific language (ar e.g.). I've written this query using quarry, but I don't know how to write it in sparql. Thanks--ASammour (talk) 11:57, 5 January 2018 (UTC)

Me too. Still; there are elements in the following that might help:

SELECT ?item ?itemLabel ?article ?aliasLabel
WHERE
{
  # wikidata item has a link to ar.wikipedia. 
  ?article 	schema:about ?item ;
			schema:isPartOf <https://ar.wikipedia.org/> .

  # wikidata record is a person. If you don't cut down the results set, you'll get a time-out ... that'll probably be your biggest problem
  ?item wdt:P31 wd:Q5 .

  # If there's an ar. alias, then we'll be wanting that ... 
  optional {?item skos:altLabel ?alias FILTER (LANG (?alias) = "ar")}
  
  # Now show only items that have no alias
  filter(!bound(?alias))

  # Access the label service.
	SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],ar" }
}
LIMIT 200

Try it!

--Tagishsimon (talk) 17:13, 5 January 2018 (UTC)

Twice-broken lighthouse colour query

This query has (at least) two faults: it miscounts the number of colours possessed by the lighthouse item; and when the filter is applied, fails to find a lighthouse that has a colour with no P518 qualifier. If anyone would like to explain the query's failings to me, I'd be grateful. I was kinda expecting that the query would find & hence count only combinations of lighthouse-colour-noP518 ... it has other ideas. Thanks --Tagishsimon (talk) 18:05, 5 January 2018 (UTC)

SELECT ?item ?itemLabel (count(?colour) as ?count) WHERE {
  ?item wdt:P31 wd:Q39715 . #instance of lighthouse
  ?item wdt:P17 wd:Q34 . #country of Sweden
  ?item wdt:P462 ?colour . #must have a colour
  
  OPTIONAL {?item p:P462 ?mem . #item has a colour
  ?mem ps:P462 ?colour2; pq:P518 ?qual . } # colour has a P518 qualifier

  FILTER(!BOUND(?qual)) # qualifier is null
  SERVICE wikibase:label { bd:serviceParam wikibase:language "se,en,fr,nl,de,it,es". }

  values ?value {wd:Q40125473}
         filter(?item=?value)
  }
group by ?item ?itemLabel

Try it!

@Tagishsimon: When you ask both for ?colour and ?colour2 what you get is the en:Cartesian product of the two sets of colours. There are, for the example lighthous, three colours and the product thus consists of nine (3×3) rows of which three have an unbound ?qual. See the following query:

SELECT ?item ?itemLabel ?colourLabel ?colour2Label ?qualLabel WHERE {
  VALUES ?value {wd:Q40125473} FILTER(?item=?value)
                  
 # ?item wdt:P462 ?colour . #compare the results with this line commented and not commented

  ?item p:P462 ?mem . #item has a colour
  ?mem ps:P462 ?colour2 .
  OPTIONAL { ?mem pq:P518 ?qual . }  # colour has a P518 qualifier
# FILTER(!BOUND(?qual)) # qualifier is null
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,sv". }
}

Try it!

So if you uncomment the line with ?colour, you get the nine rows. With this line commented, you just get three rows, i.e. one for each color. And from these three rows only one has an unbound ?qual. And then you can apply the FILTER for !BOUND(?qual) and COUNT to get the number of non-P518 colors.

Don't know if that made anything clearer. Maybe someone else can explain better. --Larske (talk) 00:59, 6 January 2018 (UTC)

Thank you, @Larske:, much appreciated. I can see the Cartesian product now that you point it out and, by & large, how I got myself into such difficulties. Thanks for taking the time. --Tagishsimon (talk) 01:28, 6 January 2018 (UTC)

Item description in specific language

How can I get the description of an item in a specific lanugage, say "de"?

SERVICE wikibase:label { bd:serviceParam wikibase:language "de" }

This does it for the label, but how can I do this with the description? Thanks for your help. --84.187.215.195 01:49, 6 January 2018 (UTC)

By the looks of it, something like:

?item schema:description ?d FILTER (LANG (?d) = "de") .

So, for instance,

SELECT ?item ?itemLabel ?d WHERE {
  
  ?item wdt:P31 wd:Q1793804.
  ?item schema:description ?d FILTER (LANG (?d) = "de") .
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de". }
}

Try it!

--Tagishsimon (talk) 02:51, 6 January 2018 (UTC)

Or just export ?itemDescription in the select clause. Matěj Suchánek (talk) 09:35, 6 January 2018 (UTC)

Members of Academy of Esperanto (Q202479) with start time (P580) and end time (P582)

I already have the following query which gives a list of members of Academy of Esperanto (Q202479). I would like to add start time (P580) and end time (P582) to this query, but I am not sure how. An example of a person with those qualifiers.

SELECT ?item WHERE {
  ?item wdt:P31 wd:Q5.
  ?item wdt:P463 wd:Q202479.

Try it!

Thanks for the help. Robin van der Vliet (talk) (contribs) 14:02, 7 January 2018 (UTC)

@Robin van der Vliet:, here it is:

SELECT ?item ?start ?end WHERE {
  ?item wdt:P463 wd:Q202479 ; wdt:P31 wd:Q5.
  OPTIONAL { ?item p:P463 [ ps:P463 wd:Q202479 ; pq:P580 ?start ] . }
  OPTIONAL { ?item p:P463 [ ps:P463 wd:Q202479 ; pq:P582 ?end ] . }
}

Try it!

I've put two OPTIONAL to give results even when the start time (P580) and end time (P582) is not indicated but you can remove them to only have results when both are indicated.

Cdlt, VIGNERON (talk) 14:21, 7 January 2018 (UTC)

I noticed that it sometimes shows "1 January" when only the year is indicated. Is this changeable so that it only shows the year, when only the year is available? Robin van der Vliet (talk) (contribs) 14:38, 7 January 2018 (UTC)

SELECT ?item ?start_time ?end_time WHERE {
  ?item wdt:P31 wd:Q5;
        p:P463 ?statement.
  ?statement ps:P463 wd:Q202479.
  OPTIONAL { 
    ?statement pqv:P580 [
        wikibase:timePrecision ?start_precision;
        wikibase:timeValue ?start ;
      ];
  }
  OPTIONAL { 
    ?statement pqv:P582 [
        wikibase:timePrecision ?end_precision;
        wikibase:timeValue ?end ;
      ];
  }
  BIND(IF((?start_precision = 9), YEAR(?start), ?start) AS ?start_time)
  BIND(IF((?end_precision = 9), YEAR(?end), ?end) AS ?end_time)
}

Try it!

This will print a year if only a year is available, otherwise it prints the full date. Shinnin (talk) 22:52, 7 January 2018 (UTC)

Born in January of a leap year

Me again. I'm looking for people born in January of a leap year. Two issues:

• My (commented out) comparison of the Label for a leap year, with the year of birth, breaks things.
• I'm fishing out the label for the leap year ?year rdfs:label ?yearname . but that seems to break the ?itemLabel value. What's going on here?

SELECT ?item ?itemLabel ?dob ?dobmonth ?dobyear ?strdobyear ?year ?yearname
WHERE 
{
  ?item wdt:P31 wd:Q5. # human
  ?item wdt:P569 ?dob. # with a DoB
  ?item wdt:P106 wd:Q82955. # politician - cut down the result set size
  ?item wdt:P27 wd:Q145. #British - ditto
  bind(year(?dob) as ?dobyear) # get the year
  bind(month(?dob) as ?dobmonth) # get the month
  filter(?dobmonth=1) # Only interested in January births
  bind(str(?dobyear) as ?strdobyear) # just in case ?dobyear is an int
        
#  ?year wdt:P31 wd:Q19828. # year is a leap year
#  filter(?yearname=?strdobyear) #DoB year is a leap year
        
  SERVICE wikibase:label { 
    bd:serviceParam wikibase:language "en".
    ?year rdfs:label ?yearname .
  }
}

Try it!

- thanks --Tagishsimon (talk) 12:14, 9 January 2018 (UTC)

In such cases it is often advisable to quickly restrict the item set very early, here with a subquery:

SELECT ?item ?itemLabel ?dateOfBirth WITH {
  SELECT DISTINCT ?item WHERE {
    ?item wdt:P27 wd:Q145; wdt:P106 wd:Q82955; p:P569 []; wdt:P31 wd:Q5 .
  }
} AS %subquery WHERE {
  INCLUDE %subquery .
  ?item p:P569/psv:P569 [ wikibase:timeValue ?dateOfBirth; wikibase:timePrecision ?precision ] .
  FILTER(MONTH(?dateOfBirth) = 1 && ?precision >= 10) .
  ?leapYear wdt:P31 wd:Q19828; wdt:P585 ?pointInTime .
  FILTER(YEAR(?dateOfBirth) = YEAR(?pointInTime)) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}

Try it!

I also added a precision check for the date of birth, since you otherwise see all dates with year-precision (precision=9) as well. —MisterSynergy (talk) 13:01, 9 January 2018 (UTC)

Thank you very much, MisterSynergy. Several things learnt from that example; precision I was aware of; would have been the next thing to be tackled. Can I come back to the failed filter(?yearname=?strdobyear) in my example ... I understand & defer to your approach, but I'd still like to know why I failed to establish a relationship between the ?item ?dob and the ?leapyear label. And to know what was going on with ?itemLabel. --Tagishsimon (talk) 14:15, 9 January 2018 (UTC)

The label service works differently than you think. Actually, there are two mechanisms to access labels (same for descriptions, not sure about aliases right not):

• SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' } should be done in the end, and it adds ?…Label and ?…Description variables with English terms (where available) for all item- or property-type variables ?…. You do not have to specify for which variables labels and descriptions should be bound, it just does it for all applicable ones (which is why this is a very expensive operation if large result sets are processed). Important to note: you cannot access these variables inside the queries itself, the ?…Label and ?…Description variables are for output only; thus do it in the end always.
• The other method is to use ?item rdfs:label ?label, ?item schema:description ?description and ?item skos:altLabel ?alias explicitly for variable ?item only inside the query. Mind that this must not be wrapped into a label service command. It binds pairs of terms in any language available, so a subsequent FILTER(LANG(?label) = 'en') and so on is advisable. Unlike the query service variables, you can access and use these one inside the query.
• Two more hints to your query: make sure that you always reduce the number of involved items as quick as possible, thus add criterions ordered from most specific to most general. Try to avoid string-based operations if ever possible, they have relatively bad performance.

—MisterSynergy (talk) 14:35, 9 January 2018 (UTC)

Thank you again, MisterSynergy. I appreciate the time you spent explaining all of the above. Much obliged. --Tagishsimon (talk) 17:41, 9 January 2018 (UTC)

Let me add this query from @Fnielsen:, who responded (substantially more competently than I) to a tweet which started this whole January in a leap year business ... Request a Query archives are improved by providing great coding examples from which we can steal later.

SELECT
  (COUNT(?page) AS ?count) ?person ?personLabel
WITH {
  SELECT DISTINCT ?person WHERE {
    hint:Query hint:optimizer "None".
    ?person wdt:P569 ?birthdate .
    BIND(MONTH(?birthdate) AS ?month)
    FILTER (?month = 1)

    ?person p:P569/psv:P569 ?timenode .
    ?timenode wikibase:timePrecision 11 .
    
    VALUES ?year { 1964 1968 1972 1976 1980 1984 1988 1992 1996 2004 2008 }
    BIND(YEAR(?birthdate) AS ?year)
  }
  LIMIT 1000
} AS %persons
WHERE {
  INCLUDE %persons
  ?page schema:about ?person .
  ?person wdt:P31 wd:Q5 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?person ?personLabel
ORDER BY DESC(?count)

Try it!

--Tagishsimon (talk) 18:15, 9 January 2018 (UTC)

Getting urls with identifier properties

For one of my tools I use this query.

SELECT ?item ?image ?creator ?institution ?invnum ?location ?url WHERE {
        ?item wdt:P31/wdt:P279* wd:Q3305213 .
        OPTIONAL { ?item wdt:P18 ?image } .
        OPTIONAL { ?item wdt:P170 ?creator } .
        OPTIONAL { ?item wdt:P195 ?institution } .
        OPTIONAL { ?item wdt:P217 ?invnum } .
        OPTIONAL { ?item wdt:P276 ?location } .
        OPTIONAL { ?item wdt:P973 ?url } .
}

Try it!

Heavy one, but works. Recently a lot of new identifier properties have been created and described at URL (P973) has been removed so I have updated the query:

SELECT ?item ?image ?creator ?institution ?invnum ?location ?url ?idurl WHERE {
        ?item wdt:P31/wdt:P279* wd:Q3305213 .
        OPTIONAL { ?item wdt:P18 ?image } .
        OPTIONAL { ?item wdt:P170 ?creator } .
        OPTIONAL { ?item wdt:P195 ?institution } .
        OPTIONAL { ?item wdt:P217 ?invnum } .
        OPTIONAL { ?item wdt:P276 ?location } .
        OPTIONAL { ?item wdt:P973 ?url } .
        OPTIONAL { ?item ?identifierproperty ?identifier .
                   ?property wikibase:directClaim ?identifierproperty .
                   ?property wikibase:propertyType wikibase:ExternalId .
                   ?property wdt:P1630 ?formatterurl .
                   BIND(IRI(REPLACE(?identifier, '^(.+)$', ?formatterurl)) AS ?idurl).
                    } 
} LIMIT 100000

Try it!

This works, but when I remove the LIMIT (or make it higher) it just gets stuck on "running query". Any suggestions to improve this query? Multichill (talk) 21:04, 7 January 2018 (UTC)

I just updated my bot and the query does work without the limit. Seems to be an interface problem? Any idea what would be causing that? Any suggestions to further improve the performance of this query? Multichill (talk) 22:07, 7 January 2018 (UTC)

The "running query" stuck is probably some kind of timeout that the GUI was unable to handle for some reason. If you could see network trace (e.g. in developers tools in chrome) that'd say what actually happened. May be some subtle GUI bug. Smalyshev (WMF) (talk) 01:04, 9 January 2018 (UTC)

@Smalyshev (WMF): just noticed your reply. Can't reproduce any more. If I run into something like this again, I'll get a stack trace and file a bug in phab. Multichill (talk) 17:40, 13 January 2018 (UTC)

I can observe the same behavior for the second query without limit (neverending running query), and this does happen occasionally on my system. The Firefox devtool shows a console error (with red color) ~40 seconds after starting the query: allocation size overflow; send/i/< https://query.wikidata.org/js/vendor.min.1cdb49cf3499e796258e.js:1:80695. If I should have a more detailed look or provide further information from the devtool, please let me know. —MisterSynergy (talk) 17:51, 13 January 2018 (UTC)

Recursive query to retrieve items located in a Region

I was trying to write a query to get all things/people/... located (or any of its subproperties) in a place (a region named Anoia, in Catalonia). This query returns me the items related to that region (cities, in fact), but not the items related to the cities located in that region.

SELECT ?item ?itemLabel ?subPropertiesLabel WHERE {
  ?subProperties wdt:P1647* wd:P276.
  ?subProperties wikibase:directClaim ?claim .

  ?item ?claim wd:Q15352 .
 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
}

Try it!

For example, the same query with the capital city of the region (named Igualada), returns me a very long list of items located (or any of its subproperties) there.

SELECT ?item ?itemLabel ?subPropertiesLabel WHERE {
  ?subProperties wdt:P1647* wd:P276.
  ?subProperties wikibase:directClaim ?claim .
  ?item ?claim wd:Q15950 .
 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
}

Try it!

I would like that the first query return me the items located (or any of its subproperties) in Anoia, and in any of the items located (or any of its subproperties) there too (for instance, those in the second query pasted). In other words, I would like to make the query become 'recursive'. How could I do this? Thanks a lot.

--Marcmiquel (talk) 16:05, 11 January 2018 (UTC)

Remembering that I have not got a clue what I'm doing, does this get you anywhere? Clearly it does not find everything ... I'm hoping that the * in P131* gives us a recursive sort of located in, and the result set lends some support to this, but...:

SELECT ?item ?itemLabel WHERE {
  {?item wdt:P131* wd:Q15352 .} #places
  UNION
 {?item wdt:P19 ?pob.           #people
  ?pob wdt:P131* wd:Q15352 .} 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
}

Try it!

--Tagishsimon (talk) 21:41, 13 January 2018 (UTC)

2002 events in Tokyo

Hi, I am looking for a list of events that took place in the municipality of Tokyo in 2002.--Wolbo (talk) 15:00, 13 January 2018 (UTC)

Initial results are not good...

SELECT ?item ?itemLabel ?when ?when2 WHERE {
  ?item wdt:P31/wdt:P279* wd:Q1656682. #item is an event or subclass of an event
  ?item wdt:P131 wd:Q1490.             #located in the administrative territorial entity of Tokyo prefecture
  ?item wdt:P585 ?when.                #It has a 'point in time' statement
  BIND(year(?when) as ?when2)          #from which we extract the year
  filter(?when2=2002)                  #and filter for 2002
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
}

Try it!

--Tagishsimon (talk) 20:22, 13 January 2018 (UTC)

Query for compete data

If the data was complete, something like the following could work (5 results currently):

• Items used: event (Q1656682)   , Tokyo (Q1490)   

• Properties used: instance of (P31)   , subclass of (P279)   , location (P276)   , located in the administrative territorial entity (P131)   , point in time (P585)   , start time (P580)   , end time (P582)   

SELECT DISTINCT ?item ?itemLabel ?when ?start ?end
WHERE
{
  
  ?item wdt:P31/wdt:P279* wd:Q1656682 .
  ?item wdt:P276/wdt:P131* wd:Q1490 .  
  { ?item wdt:P585 ?when   FILTER(YEAR(?when)=2002) }
  UNION
  { ?item wdt:P580 ?start ; wdt:P582 ?end . 
   FILTER ( ?start < "2003-00-00T00:00:00Z"^^xsd:dateTime 
           && ?end > "2001-12-32T00:00:00Z"^^xsd:dateTime )
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}

Try it!

Query for missing locations (events in Japan)

If you want to add missing location (P276), try:

• Items used: event (Q1656682)   , Japan (Q17)   

• Properties used: instance of (P31)   , subclass of (P279)   , country (P17)   , location (P276)   

#1772 results (some are not relevant)
SELECT DISTINCT ?item ?itemLabel 
WHERE
{
  ?item wdt:P31/wdt:P279* wd:Q1656682. 
  ?item wdt:P17 wd:Q17. 
  FILTER NOT EXISTS { ?item wdt:P276 [] }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Query for missing dates (events in Tokyo)

If you want to add missing point in time (P585) or start time (P580)/end time (P582), try:

• Items used: event (Q1656682)   , Tokyo (Q1490)   

• Properties used: instance of (P31)   , subclass of (P279)   , location (P276)   , located in the administrative territorial entity (P131)   , point in time (P585)   , start time (P580)   , end time (P582)   

#84 results
SELECT DISTINCT ?item ?itemLabel 
WHERE
{
  ?item wdt:P31/wdt:P279* wd:Q1656682. 
  ?item wdt:P276/wdt:P131* wd:Q1490.  
  FILTER NOT EXISTS { ?item wdt:P585 [] }
  FILTER NOT EXISTS { ?item wdt:P580 [] }
  FILTER NOT EXISTS { ?item wdt:P582 [] }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Maybe some ..

• Items used: event (Q1656682)   , Japan (Q17)   , Tokyo (Q1490)   

• Properties used: instance of (P31)   , subclass of (P279)   , country (P17)   , location (P276)   , located in the administrative territorial entity (P131)   , point in time (P585)   , start time (P580)   , end time (P582)   

#results: 44
SELECT DISTINCT ?item ?itemLabel ?location ?locationLabel ?inTokyo ?when ?start ?end 
WHERE
{
  ?item wdt:P31/wdt:P279* wd:Q1656682. 
  ?item wdt:P17 wd:Q17.   
  ?item rdfs:label ?l . FILTER( lang(?l)="en" && CONTAINS( str(?l), "2002") )
  OPTIONAL { ?item wdt:P276 ?location } 
  OPTIONAL { ?item wdt:P276 / wdt:P131* wd:Q1490 . BIND("Y" as ?inTokyo) } 
  OPTIONAL { ?item wdt:P585 ?when }
  OPTIONAL { ?item wdt:P580 ?start } 
  OPTIONAL { ?item wdt:P582 ?end }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it! An approximation to find Japanese events of 2002.
--- Jura 22:28, 13 January 2018 (UTC)

Map and Graph at the same time

There’s this query (thanks to User:VIGNERON for sharing it!), that works both as Graph and as Map. I’d like to have it as both at the same time: Getting the relationship arrows between the items on the map and having the items without coordinates floating between those with coordinates. Is this possible somehow? --Nenntmichruhigip (talk) 20:59, 4 January 2018 (UTC)

I take this silence as „not possible yet“ and asked on WD:DEV. --Nenntmichruhigip (talk) 07:57, 16 January 2018 (UTC)

Get label in any language if it's not available in predefined languages

I currently have such query:

SELECT ?item (IF(REGEX(STR(?itemLabel), "^Q[0-9]+$"),?label,?itemLabel) as ?final_label) WHERE {
  values ?item {wd:Q40006707 wd:Q30345737 }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "lv, fr" . }
  ?item rdfs:label ?label .
}
#group by ?item

Try it!

So basically: if item doesn't have label in Latvian or French, get any label (of course, there could be some kind of ranking - first from Latin based languages, then some Slavic, Japanase etc. etc.) from rdfs:label. There was once posted answer to such question, but I really can't find it :( --Edgars2007 (talk) 18:37, 10 January 2018 (UTC)

Maybe this is a step towards what you ask for. The example objects are bridges with and without lv and fr labels.

SELECT ?item ?lv_label ?fr_label (SAMPLE(?any_label) AS ?any_label) (SAMPLE(?final_label) AS ?final_label) WHERE {
  values ?item {wd:Q125006 wd:Q1394625 wd:Q80593 wd:Q182945}
  OPTIONAL {?item rdfs:label ?lv_label . FILTER(lang(?lv_label)="lv") }
  OPTIONAL {?item rdfs:label ?fr_label . FILTER(lang(?fr_label)="fr") }
  ?item rdfs:label ?any_label . 
  BIND(IF(BOUND(?lv_label),?lv_label,IF(BOUND(?fr_label),?fr_label,?any_label)) AS ?final_label) 
}
GROUP BY ?item ?lv_label ?fr_label

Try it!

--Larske (talk) 21:24, 11 January 2018 (UTC)

I remember it was possible to do that with SERVICE wikibase:label and it wasn't so hacky, but you version is as you say step towards. --Edgars2007 (talk) 18:58, 15 January 2018 (UTC)

@Edgars2007: Related task i Phabricator:T154260

Tracked in Phabricator
Task T154260

Larske (talk) 06:34, 16 January 2018 (UTC)

Venezuelan people

Hi! I'm still learning how to use the Query Service and my computer is currently very slow. I'm working on creating lists in the Spanish Wikipedia about years in Venezuela, so I wanted to search Venezuelan persons that were either born or have died in the following years: 1833, 1834, 1838, 1839, 1841, 1843, 1844, 1845, 1846, 1847, 1849, 1850, 1851, 1852, 1853, 1855, 1856, 1857, 1865, 1866, 1867, 1868, 1869, 1870, 1871, 1872, 1874, 1876, 1877, 1878, 1879, 1880 and 1882. Many thanks in advance! --Jamez42 (talk) 19:01, 4 January 2018 (UTC)

• Items used: Venezuela (Q717)   , human (Q5)   

• Properties used: country of citizenship (P27)   , date of birth (P569)   , date of death (P570)   , instance of (P31)   

SELECT ?y ?item ?itemLabel ?itemDescription ?dob ?dod 
{
    ?item wdt:P27 wd:Q717 .
    { ?item wdt:P569 ?d . BIND(YEAR(?d) as ?y) }
    UNION
    { ?item wdt:P570 ?d . BIND(YEAR(?d) as ?y) }
    FILTER ( ?y IN ( 1833, 1834, 1838, 1839, 1841, 1843, 1844, 1845, 1846, 1847, 1849, 1850, 1851, 1852, 1853, 1855, 1856, 1857, 1865, 1866, 1867, 1868, 1869, 1870, 1871, 1872, 1874, 1876, 1877, 1878, 1879, 1880, 1882 ) )
    ?item wdt:P31 wd:Q5 . 
    OPTIONAL { ?item wdt:P569 ?dob }
    OPTIONAL { ?item wdt:P570 ?dod }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],es,en". }
}      
ORDER BY ?y ?itemLabel

Try it! Maybe like the above.
--- Jura 19:36, 4 January 2018 (UTC)

@Jura1: This is perfect, thank you so much for the soon response! --Jamez42 (talk) 07:04, 6 January 2018 (UTC)

@Jura1, Jamez42: The FILTER on years is really elegant - I didn't know you could do that! Here's an adapted query, which also finds anyone who was born or died in Venezuela in those years, even if they're not listed as Venezuelan. There's about 24 extra names - I think some are missing the country and others like Gregor MacGregor (Q330882) might be dual citizens where we've only listed one nationality. Andrew Gray (talk) 21:22, 16 January 2018 (UTC)

• Items used: human (Q5)   , Venezuela (Q717)   

• Properties used: date of birth (P569)   , instance of (P31)   , place of birth (P19)   , located in the administrative territorial entity (P131)   , date of death (P570)   , place of death (P20)   , country of citizenship (P27)   

SELECT DISTINCT ?y ?item ?itemLabel ?itemDescription ?dob ?birthLabel ?dod ?deathLabel
{
    { ?item wdt:P569 ?b . BIND(YEAR(?b) as ?y) .  # year of birth known
      ?item wdt:P31 wd:Q5 .                       # was a human
      ?item wdt:P19 [wdt:P131* wd:Q717] . }       # born somewhere in Venezuela
    UNION
    { ?item wdt:P570 ?d . BIND(YEAR(?d) as ?y) .  # year of death known
      ?item wdt:P31 wd:Q5 .                       # was a human
      ?item wdt:P20 [wdt:P131* wd:Q717] . }       # died somewhere in Venezuela
    UNION
    { ?item wdt:P569 ?b . BIND(YEAR(?b) as ?y) .  # year of birth known
      ?item wdt:P31 wd:Q5 .                       # was a human
      ?item wdt:P27 wd:Q717 . }                   # nationality was Venezuelan
    UNION
    { ?item wdt:P570 ?d . BIND(YEAR(?d) as ?y) .  # year of death known
      ?item wdt:P31 wd:Q5 .                       # was a human
      ?item wdt:P27 wd:Q717 . }                   # nationality was Venezuelan
 
    # the UNION sections above mean we get any person who satisfies one or more of the four searches
 
    FILTER ( ?y IN ( 1833, 1834, 1838, 1839, 1841, 1843, 1844, 1845, 1846, 1847, 1849, 1850, 1851, 1852, 1853, 1855, 1856, 1857, 1865, 1866, 1867, 1868, 1869, 1870, 1871, 1872, 1874, 1876, 1877, 1878, 1879, 1880, 1882 ) )
                                                  # filter so the year of birth/death was one of the ones we're interested in
 
    OPTIONAL { ?item wdt:P569 ?dob }              # date of birth if known
    OPTIONAL { ?item wdt:P19 ?birth }             # place of birth if known
    OPTIONAL { ?item wdt:P570 ?dod }              # date of death if known
    OPTIONAL { ?item wdt:P20 ?death }             # place of death if known
 
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],es,en". }
}      
ORDER BY ?y ?itemLabel

Try it!

@Andrew Gray: Perfect. I was worried that important persons might have been left out because of missing information in Wikidata. I'll also include these in the lists, thank you a lot!--Jamez42 (talk) 21:37, 16 January 2018 (UTC)

Year above 2100

years above 2100 AD. Artix Kreiger (talk) 04:57, 17 January 2018 (UTC)

SELECT  ?object ?objectLabel (YEAR(?date) as ?year)
WHERE
{
  ?object wdt:P31 wd:Q577.        #item is a year 
  ?object wdt:P585 ?date .        #item has a 'point in time' property
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }               
  FILTER (YEAR(?date) > 2100)     #value from the 'point in time' property greater than 2100
}
ORDER BY ?year

Try it!

--Tagishsimon (talk) 08:33, 17 January 2018 (UTC)

Country of citizenship, where person died before the country existed or was born after the country existed, and other similar statements

Fram has noted on enwiki that Arnold van Westerhout (Q18511480) incorrectly describes its subject as country of citizenship (P27) Belgium (Q31), since van Westerhout died in 1725 and the modern state of Belgium did not exist until 1830. It would be useful to find other items where this is the case.

I'm not sure if it's correct to have e.g. country (P17) Egypt (Q79) for Library of Alexandria (Q435), since Egypt the modern state did not exist when the library did but it's geographically correct, so for statements not specifically tied to a political state or nation, a different query which does the same thing would be useful. Jc86035 (talk) 14:18, 17 January 2018 (UTC)

Here's a basic report, which could be considerably embellished & improved, but shows the basic approach:

SELECT  distinct ?item ?itemLabel ?country_of_citizenship ?country_of_citizenshipLabel
WHERE
{
  ?item wdt:P31 wd:Q5.                   # item is a person
  ?item wdt:P27 ?country_of_citizenship. # has a country of citizenship
  ?item wdt:P569 ?dob.                  # has date of birth
        
  ?country_of_citizenship wdt:P571 ?inception.                      # the country of citizenship has an inception date
  optional{?country_of_citizenship wdt:P576 ?dissolved.}            # might or might not have a dissolved date

  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } # get Labels for items
  FILTER (?dob >?dissolved)                                         # show items only when birth after dissolution
 }

Try it!

We could make other of the statements optional, but quickly run into timeouts. -- Tagishsimon (talk) 14:49, 17 January 2018 (UTC)

(ec)@Jc86035: Here's a quick and slightly naive query to tally numbers of people who died before the inception (P571) of the country they are said to be citizens of.

SELECT ?country ?countryLabel ?inception ?count WHERE {
  {  
    SELECT ?country ?inception (COUNT(DISTINCT ?person) AS ?count) WHERE {
      hint:Query hint:optimizer "None".
      ?person wdt:P27 ?country .
      ?person wdt:P570 ?dod .
      ?country wdt:P571 ?inception .
      FILTER (?dod < ?inception) .
     } GROUP BY ?country ?inception 
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?count)

Try it!

As you can see, one issue is that some countries may have multiple inception dates -- eg the various dates the query considers for Spain (Q29) as you go down the results.

Another issue is, do we really insist that the UK has only existed since 1927? Or that no-one was German before 1949? India (Q668) escapes because it doesn't actually have a inception (P571) statement at the moment, but would presumably also have similar issues.

When we come to something like "Flemish", how should that be represented anyway? Was there truly a state, of which "Flemish" was a citizenship? And what about sources that describe people as "Welsh" or "Scottish" or "Catalan"?

Some of these questions are currently being thrown around in the "Welsh" thread on Project Chat; but I think it's fair to say that this is an area which is overdue some serious thinking. Jheald (talk) 15:00, 17 January 2018 (UTC)

Yes, I think we're long overdue getting a clearly stated best practice here. I'll try and pull together a suggested guideline on historic states from that thread and put it up to an RFC. On a different note, a lot of the "born too late" examples are interesting, and look more like glitches or ambiguities rather than substantive data errors. About a third are born in "2000", which is probably people with century-precision births; they are mostly Soviets, East Germans, and Czechs. Another few dozen are Austro-Hungarians born in 1919 or Russian Empire people born in 1918, which suggests a bit of confusion as to when exactly those countries ceased to exist. Certainly some maintenance work needed, but it's better than I expected - I wonder if someone's been doing maintenance work on this before. 16:12, 17 January 2018 (UTC)

It seems that Magnus did at one point have a query results page running for this, but it's been inactive for some time. While we're at it, here's a list of countries that are obects of country of citizenship (P27) but have no inception (P571): tinyurl.com/y95sdrh4. Some quite big names here, including France, Sweden, Austria, India, Iran, Netherlands, Wales, Egypt... Jheald (talk) 16:46, 17 January 2018 (UTC)

Meanwhile, birth>dissolved report with time precision...

SELECT  distinct ?item ?itemLabel ?country_of_citizenship ?country_of_citizenshipLabel
WHERE
  {
  ?item wdt:P27 ?country_of_citizenship .                           # item has a country of citizenship
  ?country_of_citizenship wdt:P576 ?dissolved .                     # country of citizenship has a dissolved date
  ?item wdt:P569 ?dob .                                             # item has date of birth
  ?item p:P569/psv:P569 ?timenode .
  ?timenode wikibase:timePrecision 11 .                             # dob date has precision to the day
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } # get Labels for items
  FILTER (?dob >?dissolved)                                         # show items only when  birth after dissolution
  }

Try it!

The counterpart report for death<initiated times out, which for the time being stumps me. --Tagishsimon (talk) 17:16, 17 January 2018 (UTC)

Also a slight variant, showing the dates tinyurl.com/ydeb48z3 (with earliest and latest for dissolution in separate columns). Jheald (talk) 17:27, 17 January 2018 (UTC)

Date of baptism from significant event (P793)

I am trying to get dates of baptism which are stored using old method as qualifiers of significant event (P793) = baptism (Q35856). The following query works fine if there is only a single significant event (P793), but returns additional pairs if there is mode. Can someone help me fix it, so I only get qualifiers of significant event (P793) = baptism (Q35856) statement and not other significant event (P793) statements in same item?

SELECT ?person ?baptism  WHERE {
  ?person wdt:P31 wd:Q5 .
  ?person wdt:P793 wd:Q35856 .
  ?person p:P793/pq:P585 ?baptism .
}

Try it!

--Jarekt (talk) 21:34, 17 January 2018 (UTC)

@Jarekt: like this, I think. Always seems a little around-the-houses to me, but there we go. The ?mem variable name is because I can't think what to call that variable :)

SELECT ?person ?baptism  WHERE {
  ?person wdt:P31 wd:Q5 .
  ?person p:P793 ?mem .
  ?mem ps:P793 wd:Q35856; pq:P585 ?baptism .  
}

Try it!

--Tagishsimon (talk) 22:25, 17 January 2018 (UTC)

Here is a version without the ?mem variable:

SELECT ?person ?baptism  WHERE {
  ?person wdt:P31 wd:Q5 .
  ?person p:P793 [ps:P793 wd:Q35856; pq:P585 ?baptism] .  
}

Try it!

--Larske (talk) 09:00, 18 January 2018 (UTC)

Maybe you want to include also objects with a significant event (P793)/baptism (Q35856) where the point in time (P585) is missing in the result.

SELECT ?person ?baptism  WHERE {
  ?person wdt:P31 wd:Q5 .
  ?person p:P793 ?mem .
  ?mem ps:P793 wd:Q35856 . 
  OPTIONAL{ ?mem pq:P585 ?baptism }  
}

Try it!

For this query I don't know if/how it is possible to avoid the ?mem variable. Maybe someone else can tell.

--Larske (talk) 09:19, 18 January 2018 (UTC)

I, for one, am glad to see the return of ?mem ;) --Tagishsimon (talk) 09:21, 18 January 2018 (UTC)

It might be worth noting that date of baptism (P1636) is mainly there as a substitute for missing DOB. It's not the "new" method.
--- Jura 11:09, 18 January 2018 (UTC)

Thanks a lot Tagishsimon and Larske --Jarekt (talk) 13:12, 18 January 2018 (UTC)

Categories used in english Wikipedia

I'm interesetd in a deeper understanding of how Wikidata handle wikipedia categories. With a Query like this I can explore categories as they are linked to a specific Wikipedia.

QUERY REQUEST: Number of categories with a sitelink to "en.wikipedia.org"

I know there are already 4032165 wikimedia categories (executing this query: SELECT (COUNT(?item) AS ?total) WHERE { ?item wdt:P31 wd:Q4167836.} ) but due to sitelinks aren't properties, is complex for me to know which of these are used in english - or spanish or other - Wikipedia.

Thanks  – The preceding unsigned comment was added by Tsaorin (talk • contribs) at 13:56, 18 January 2018‎ (UTC).

SELECT (COUNT(?article) AS ?total) WHERE 
{  
  ?item wdt:P31 wd:Q4167836 .
  ?article schema:about ?item ; schema:isPartOf <https://es.wikipedia.org/> .
}

Try it!

In theory this approach should do it - 343347 for Spanish Wikipedia with this query; change es to en to run it for English. However, while it works for most sites, it times out for English Wikipedia. Not sure how to get around that. Andrew Gray (talk) 13:16, 18 January 2018 (UTC)

Frankly, there is not much to learn about Wikipedia categories in Wikidata. Each category gets its item (to manage interwikilinks), and there are some basic attempts to describe what the categories contain. However, the category structure itself is not mapped to Wikidata, and probably never will.

For categorization-related queries, Petscan is in many regards a more versatile tool than the Query Service is, since it contains lots of mapping functionality between Wikidata queries/items and Wikipedia content—including Wikipedia categorization. When it comes to a plain count of connected items, direct SQL queries (not SPARQL) on the enwiki MediaWiki databases would do the job (see mw:Manual:Database layout, access for instance with Quarry; example: quarry:query/24068). —MisterSynergy (talk) 13:51, 18 January 2018 (UTC)

Arbitrary list of items and language labels

Hi all,

I have a list of about 50 concepts that are associated with the field of “migration” and I am wanting to find out how well translated their labels and descriptions are across the EU official languages.

Is it possible to make a query result for an arbitrary list of Q items where,

• each row is a new item.
• each column is the labels/description (or ‘’absence’’ of label/description) for a chosen language.

Here is my working concept using Listeria on my userspace: User:Wittylama/Migration vocabulary. With a live query I could visualise this set of items by: the most common “subclass of” these items are; or a “completeness” count for each language’s labels - which would show which languages need the most work to add labels.

Unlike Listeria’s features - I do NOT want it to fallback to English when the designated language label is unavailable. I want it to be very clear when the label is missing. Is this possible?

Wittylama (talk) 12:37, 16 January 2018 (UTC)

It is. This is the basic pattern; now repeat for as many languages as you like. QIds go in the values statement.

SELECT ?item ?en_label ?lv_label ?fr_label WHERE {
values ?item {wd:Q125006 wd:Q1394625 wd:Q80593 wd:Q182945}
  OPTIONAL {?item rdfs:label ?en_label . FILTER(lang(?en_label)="en") }
  OPTIONAL {?item rdfs:label ?lv_label . FILTER(lang(?lv_label)="lv") }
  OPTIONAL {?item rdfs:label ?fr_label . FILTER(lang(?fr_label)="fr") }
 }

Try it!

--Tagishsimon (talk)

Writ large, it turns into

SELECT ?item ?bg_label ?cs_label ?da_label ?de_label ?el_label ?en_label ?es_label ?et_label ?fi_label ?fr_label ?ga_label ?hr_label ?hu_label ?it_label ?lt_label ?lv_label ?mt_label ?nl_label ?pl_label ?pt_label ?ro_label ?sk_label ?sl_label ?sv_label  WHERE {
values ?item {wd:Q4006 wd:Q7167 wd:Q7181 wd:Q8434 wd:Q8458 wd:Q8461 wd:Q8463 wd:Q41397 wd:Q59816 wd:Q79913 wd:Q128393 wd:Q131288 wd:Q131572 wd:Q133004 wd:Q133346 wd:Q154278 wd:Q166005 wd:Q170404 wd:Q177626 wd:Q181784 wd:Q185598 wd:Q187668 wd:Q188863 wd:Q230533 wd:Q283509 wd:Q367293 wd:Q379693 wd:Q452573 wd:Q705818 wd:Q837556 wd:Q912512 wd:Q1338878 wd:Q1351807 wd:Q1368367 wd:Q1397926 wd:Q1932461 wd:Q2608153 wd:Q12547146 wd:Q15320003 wd:Q16513600 wd:Q18344718}

OPTIONAL {?item rdfs:label ?bg_label . FILTER(lang(?bg_label)="bg") }
OPTIONAL {?item rdfs:label ?cs_label . FILTER(lang(?cs_label)="cs") }
OPTIONAL {?item rdfs:label ?da_label . FILTER(lang(?da_label)="da") }
OPTIONAL {?item rdfs:label ?de_label . FILTER(lang(?de_label)="de") }
OPTIONAL {?item rdfs:label ?el_label . FILTER(lang(?el_label)="el") }
OPTIONAL {?item rdfs:label ?en_label . FILTER(lang(?en_label)="en") }
OPTIONAL {?item rdfs:label ?es_label . FILTER(lang(?es_label)="es") }
OPTIONAL {?item rdfs:label ?et_label . FILTER(lang(?et_label)="et") }
OPTIONAL {?item rdfs:label ?fi_label . FILTER(lang(?fi_label)="fi") }
OPTIONAL {?item rdfs:label ?fr_label . FILTER(lang(?fr_label)="fr") }
OPTIONAL {?item rdfs:label ?ga_label . FILTER(lang(?ga_label)="ga") }
OPTIONAL {?item rdfs:label ?hr_label . FILTER(lang(?hr_label)="hr") }
OPTIONAL {?item rdfs:label ?hu_label . FILTER(lang(?hu_label)="hu") }
OPTIONAL {?item rdfs:label ?it_label . FILTER(lang(?it_label)="it") }
OPTIONAL {?item rdfs:label ?lt_label . FILTER(lang(?lt_label)="lt") }
OPTIONAL {?item rdfs:label ?lv_label . FILTER(lang(?lv_label)="lv") }
OPTIONAL {?item rdfs:label ?mt_label . FILTER(lang(?mt_label)="mt") }
OPTIONAL {?item rdfs:label ?nl_label . FILTER(lang(?nl_label)="nl") }
OPTIONAL {?item rdfs:label ?pl_label . FILTER(lang(?pl_label)="pl") }
OPTIONAL {?item rdfs:label ?pt_label . FILTER(lang(?pt_label)="pt") }
OPTIONAL {?item rdfs:label ?ro_label . FILTER(lang(?ro_label)="ro") }
OPTIONAL {?item rdfs:label ?sk_label . FILTER(lang(?sk_label)="sk") }
OPTIONAL {?item rdfs:label ?sl_label . FILTER(lang(?sl_label)="sl") }
OPTIONAL {?item rdfs:label ?sv_label . FILTER(lang(?sv_label)="sv") }
 }

Try it!

at which point the query service turns the "table" result into a series of lists, with no row for a blank label. However exporting to CSV, for instance, transforms it back into a table with blank cells for missing data. --Tagishsimon (talk) 13:39, 16 January 2018 (UTC)

• I was wondering if there was a dynamic way to generate the variables that are being returned. (e.g. SELECT * without specifying each language label variable in WHERE. It could select the languages from the list of official languages of the EU).
  --- Jura 13:44, 16 January 2018 (UTC)

@Wittylama: Two queries to list the label completeness per concept and language:

Label completeness (in percent) per concept:

SELECT ?item ?itemLabel (round(100*SUM(?label_exists)/SUM(1)) AS ?percentage_of_labels) WHERE {
values ?item {wd:Q4006 wd:Q7167 wd:Q7181 wd:Q8434 wd:Q8458 wd:Q8461 wd:Q8463 wd:Q41397 wd:Q59816 wd:Q79913 wd:Q128393 wd:Q131288 wd:Q131572 wd:Q133004 wd:Q133346 wd:Q154278 wd:Q166005 wd:Q170404 wd:Q177626 wd:Q181784 wd:Q185598 wd:Q187668 wd:Q188863 wd:Q230533 wd:Q283509 wd:Q367293 wd:Q379693 wd:Q452573 wd:Q705818 wd:Q837556 wd:Q912512 wd:Q1338878 wd:Q1351807 wd:Q1368367 wd:Q1397926 wd:Q1932461 wd:Q2608153 wd:Q12547146 wd:Q15320003 wd:Q16513600 wd:Q18344718}

wd:Q458 wdt:P37 ?official_language .     # get all official languages of the EU
?official_language wdt:P424 ?langcode .  # and their Wikimedia language codes
OPTIONAL {
  ?item rdfs:label ?item_label .         # and match the item label language...
  FILTER(?langcode=lang(?item_label))    # ...with the language
  BIND(IF(BOUND(?item_label),1,0) AS ?label_exists) # count the number of existing labels
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }  # change "en" to your preferred language
}
GROUP BY ?item ?itemLabel
ORDER BY DESC(?percentage_of_labels)

Try it!

Label completeness (in percent) per language:

SELECT ?langcode ?official_languageLabel (round(100*SUM(?label_exists)/SUM(1)) AS ?percentage_of_labels) WHERE {
values ?item {wd:Q4006 wd:Q7167 wd:Q7181 wd:Q8434 wd:Q8458 wd:Q8461 wd:Q8463 wd:Q41397 wd:Q59816 wd:Q79913 wd:Q128393 wd:Q131288 wd:Q131572 wd:Q133004 wd:Q133346 wd:Q154278 wd:Q166005 wd:Q170404 wd:Q177626 wd:Q181784 wd:Q185598 wd:Q187668 wd:Q188863 wd:Q230533 wd:Q283509 wd:Q367293 wd:Q379693 wd:Q452573 wd:Q705818 wd:Q837556 wd:Q912512 wd:Q1338878 wd:Q1351807 wd:Q1368367 wd:Q1397926 wd:Q1932461 wd:Q2608153 wd:Q12547146 wd:Q15320003 wd:Q16513600 wd:Q18344718}

wd:Q458 wdt:P37 ?official_language .     # get all official languages of the EU
?official_language wdt:P424 ?langcode .  # and their Wikimedia language codes
OPTIONAL {
  ?item rdfs:label ?item_label .         # and match the item label language...
  FILTER(?langcode=lang(?item_label))    # ...with the language
  BIND(IF(BOUND(?item_label),1,0) AS ?label_exists) # count the number of existing labels
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }  # change "en" to your preferred language
}
GROUP BY ?langcode ?official_languageLabel 
ORDER BY DESC(?percentage_of_labels)

Try it!

--Larske (talk) 20:54, 16 January 2018 (UTC)

Thank you so much Tagishsimon, Larske and Jura for your rapid replied! I'll play with this and come back to you directly if I have any questions. Your help is greatly appreciated :-) Wittylama (talk) 11:01, 19 January 2018 (UTC)

Time line for the administrative organisations of the USA

Hoi, I am adding the governors of US States and districts. Districts are often the precursor of states. Is there a query that shows how one follows the other. How can this in turn be applied to have a succession of governors of districts to states? Thanks, GerardM (talk) 12:40, 19 January 2018 (UTC)

I don't think we have the data to show what follows what, based on a quick looksee at:

• Illinois Territory (Q1658828) & Illinois (Q1204)
• Nebraska Territory (Q751536) & Nebraska (Q1553)

Putting aside questions of whether States exactly follow or are isomorphic with Territories, we'd need a relation like 'preceded by' or 'followed by', or else a common value held by both and indicating that each was concerned with administration of the same land mass. We have none of these. Had we the data, it would be possible to list the succession of governors across the change in the administrative entity. --Tagishsimon (talk) 17:15, 19 January 2018 (UTC)

We do need an analogue for the little flags with arrows that exist at the top or bottom of the infoboxes in pages like the ones Tagishsimon cited. (Just noticed, @GerardM:, that you marked William Hale (Q3568679) as the governor when it should have been William Hale (Q435937); please recheck the rest of your added claims on territorial governors.) Mahir256 (talk) 17:36, 19 January 2018 (UTC)

I picked up on the William Hale already :).. It is slow going but I get there. Thanks, GerardM (talk) 17:44, 19 January 2018 (UTC)

Area chart of populations compared

Hi,

I want to compare the population of Beetz (Q14947347), Kremmen (Q587094), Flatow (Q47446677), Groß-Ziethen (Q47450894), Hohenbruch (Q47450923) and Sommerfeld (Kremmen) (Q14947358) in an Area chart. here is what I got so far. Can I influence the order of Layers? I want the biggest populations in the bottom Layer. Your Help is appreciated 😀 --Shisma (talk) 13:48, 16 January 2018 (UTC)

@Shisma: Good question, I have no answer but I'm interrested to have an answer too. @Lydia Pintscher (WMDE): any idea? VIGNERON (talk) 11:13, 19 January 2018 (UTC)

@Lucas Werkmeister (WMDE): Handing this over to you :) --Lydia Pintscher (WMDE) (talk) 15:52, 20 January 2018 (UTC)

@Shisma: It looks like there’s no way to do this currently – the first label column is used for the “series key”, and that is also what the chart is ordered by (alphabetically). We probably ought to make this configurable. But in the meantime I can at least offer an optimization of your query :)

#defaultView:AreaChart
SELECT (str(YEAR(?pointInTime)) AS ?Jahr) ?Einwohnerzahl ?OrtsteilLabel ?Ortsteil   
WHERE  
{
  ?Ortsteil wdt:P131? wd:Q587094.
  ?Ortsteil p:P1082 ?populationStatement.
  ?populationStatement ps:P1082 ?Einwohnerzahl. 
  ?populationStatement pq:P585 ?pointInTime.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--TweetsFactsAndQueries (talk) 11:33, 22 January 2018 (UTC)

I’ve created phabricator:T185476 to make this configurable --Lucas Werkmeister (WMDE) (talk) 11:43, 22 January 2018 (UTC)

How to create a clickable URL

How do I make ?url2 clickable? --Tagishsimon (talk) 23:27, 19 January 2018 (UTC)

SELECT ?item ?itemLabel ?inventory_no ?url  ?url2 WHERE {
  ?item wdt:P31 wd:Q3305213 .
  ?item wdt:P973 ?url .
  ?item wdt:P217 ?inventory_no .
  FILTER(CONTAINS(str(?url), "http://data.collectienederland.nl/resource/aggregation/rce-kunstcollectie/") )
  BIND(concat("<http://data.collectienederland.nl/page/aggregation/rce-kunstcollectie/",?inventory_no,">") as ?url2)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],nl". }
}

Try it!

@Tagishsimon: Remove the < and > and use the IRI-function.

In case you are only interested in the inventories of Cultural Heritage Agency of the Netherlands Art Collection (Q18600731), I made a slight modification to the query to get only the inventory numbers from that collection (P195).

SELECT ?item ?itemLabel ?inventory_no ?url ?url2 WHERE {
  ?item wdt:P31 wd:Q3305213 .
  ?item wdt:P973 ?url .
  ?item p:P217 ?inventory_no_stm .
  ?inventory_no_stm ps:P217 ?inventory_no .
  ?inventory_no_stm pq:P195 wd:Q18600731 . # filter out inventory numbers for "Cultural Heritage Agency of the Netherlands Art Collection" only 
  FILTER(CONTAINS(str(?url), "http://data.collectienederland.nl/resource/aggregation/rce-kunstcollectie/") )
  BIND(IRI(concat("http://data.collectienederland.nl/page/aggregation/rce-kunstcollectie/",?inventory_no)) as ?url2)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],nl". }
}
ORDER BY ?inventory_no

Try it!

--Larske (talk) 02:12, 20 January 2018 (UTC)

Excellent; many thanks @Larske:. Your slight modification is spot on. And IRI. Magic. --Tagishsimon (talk) 11:59, 20 January 2018 (UTC)

Query my contributions?

I’d like to get a list of “stated in” references (1) added by me to any item, (2) where the title of the “stated in” value contains the string “Oxford”. Is this even possible? - PKM (talk) 00:15, 21 January 2018 (UTC)

No, there's no information about the author of a specific information. Matěj Suchánek (talk) 08:52, 21 January 2018 (UTC)

That's what I suspected. Thanks. - PKM (talk) 01:21, 22 January 2018 (UTC)

Theoretically it is possible with this tool. But ir probably would time-out. But you have to have SPARQL query anyway :) --Edgars2007 (talk) 08:24, 22 January 2018 (UTC)

Reporting on qualifiers irrespective of the property they qualify

As an extension of PKM's question, above, if we consider, for instance, handloom (Q47481343) we find stated in (P248) qualifications for two properties. Presuming we gathered the set of QIds that PKM has edited from some other source, how do we query for records amongst that set in which there's a stated in (P248) associated with any property ... i.e. without having to supply a set of properties of interest? --Tagishsimon (talk) 18:39, 22 January 2018 (UTC)

SELECT ?property ?value WHERE {
  ?property wikibase:claim ?p; wikibase:statementProperty ?ps . # where "?property = wd:Pxxx", "?p = p:Pxxx", and "?ps = ps:Pxxx" for any property Pxxx; one could also filter for property types, etc. here ...
  wd:Q47481343 ?p [ ?ps ?value; prov:wasDerivedFrom [ pr:P248 wd:Q28913836 ] ] .
}

Try it!

—MisterSynergy (talk) 19:17, 22 January 2018 (UTC)

Thank you, @MisterSynergy:. That, for me, is a whole new level of SPARQL. I may have follow-up questions eventually :) --Tagishsimon (talk) 22:36, 22 January 2018 (UTC)

(ec) It's not so hard to unpack, if you expand it out:

SELECT ?property ?propertyLabel ?value ?valueLabel WHERE {
  ?property wikibase:claim ?p . 
  ?property wikibase:statementProperty ?ps .
 
  wd:Q47481343 ?p ?stmt .
  ?stmt ?ps ?value . 
  ?stmt prov:wasDerivedFrom ?ref .
  ?ref pr:P248 wd:Q28913836

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }     
}

Try it!

-- Jheald (talk) 23:28, 22 January 2018 (UTC)

So here, @PKM:, is where I've got so far (with a hefty push from Mr.S, above) ... "Oxford" in 'Stated In", for the items you have created. If there's no other way, we could scrape your edit history for QIds you've edited, dedupe them, bug 'em in the VALUES statement, and you'd be home & dry. Is there another way of getting the list of QIds a user has touched? We must have a tool for that? --Tagishsimon (talk) 23:24, 22 January 2018 (UTC)

SELECT ?item ?itemLabel ?property ?propertyLabel ?value ?valueLabel ?source_qid ?source_label WHERE {
  VALUES ?item {wd:Q46951799 wd:Q46975025 wd:Q46976206 wd:Q46976611 wd:Q46998339 wd:Q46998368 wd:Q46998374 wd:Q46998381 wd:Q46998397 wd:Q47004153 wd:Q47006402 wd:Q47008525 wd:Q47008705 wd:Q47008741 wd:Q47008958 wd:Q47010885 wd:Q47011175 wd:Q47011275 wd:Q47014250 wd:Q47038396 wd:Q47072983 wd:Q47073011 wd:Q47073013 wd:Q47073016 wd:Q47073021 wd:Q47089651 wd:Q47137276 wd:Q47159674 wd:Q47212035 wd:Q47212148 wd:Q47215384 wd:Q47216171 wd:Q47314015 wd:Q47326250 wd:Q47365265 wd:Q47415031 wd:Q47415676 wd:Q47424623 wd:Q47450958 wd:Q47450979 wd:Q47451002 wd:Q47451049 wd:Q47451078 wd:Q47460105 wd:Q47460122 wd:Q47460142 wd:Q47460174 wd:Q47463254 wd:Q47463331 wd:Q47463825 wd:Q47463828 wd:Q47463841 wd:Q47463849 wd:Q47468023 wd:Q47469120 wd:Q47481343 wd:Q47481513 wd:Q47482383}
  ?property wikibase:claim ?p; wikibase:statementProperty ?ps .
  ?item ?p [ ?ps ?value; prov:wasDerivedFrom [ pr:P248 ?source_qid ] ] .
   SERVICE wikibase:label { bd:serviceParam wikibase:language "en".}
   ?source_qid rdfs:label ?source_label . FILTER(LANG(?source_label) = 'en') .
   FILTER(CONTAINS(str(?source_label), "Oxford") ) .
}

Try it!

--Tagishsimon (talk) 23:24, 22 January 2018 (UTC)

Thanks, all! This is great, and waaaay beyond my query skills. - PKM (talk) 03:47, 23 January 2018 (UTC)

Statements available at dbpedia, but not at Wikidata

Per the discussion at m:Grants_talk:Project/DBpedia/GlobalFactSync#Query/Sparql. It should be possible to query dbpedia for statements available at enwiki (or dewiki/elwiki), but not on Wikidata. A (Listeria?) report on a per property basis could be helpful.
--- Jura 03:25, 23 January 2018 (UTC)

Most sitelinks articles

I want to find out most sitelinks articles. I trying this but it does not work. How can I fix it?

# most sitelinks articles
PREFIX schema: <http://schema.org/>

SELECT ?item ?itemLabel ?linkcount
WHERE {
  ?item wikibase:sitelinks ?linkcount .
  FILTER (?linkcount > 9) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}
GROUP BY ?item ?itemLabel ?linkcount
ORDER BY DESC(?linkcount)
LIMIT 50

Try it!

--Kanashimi (talk) 07:30, 21 January 2018 (UTC)

• The "GROUP BY" isn't needed. Unless you limit the field with some P31, maybe it's quicker without the filter for >9. It should give you something like Wikidata:Database reports/Most sitelinked items.
  --- Jura 07:37, 21 January 2018 (UTC)

Thank you. I try to remove "GROUP BY" or "FILTER", but it still gives wdqs-action-timeout. May I speed up the query? --Kanashimi (talk) 22:22, 21 January 2018 (UTC)

Based on Wikidata:Database reports/Most sitelinked items, here is one that works:

SELECT ?item ?linkcount
WHERE 
{
  ?item wikibase:sitelinks ?linkcount .
  FILTER (?linkcount > 300)
}
ORDER BY DESC(?linkcount)
LIMIT 50

Try it!

If you filter with "?item wdt:P17 wd:Q30" or something else, it might be more interesting (and avoids timeouts). Sample:

SELECT ?item ?itemLabel ?linkcount
WHERE 
{
  ?item wikibase:sitelinks ?linkcount .
  ?item wdt:P17 wd:Q30 .
  FILTER (?linkcount > 50)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?linkcount)
LIMIT 50

Try it!

It could be helpful to monitor statements on most sitelinked items: Wikidata:Database reports/Most sitelinked items/with statements.
--- Jura 03:25, 23 January 2018 (UTC)

Thank you. I get inspiration from Wikidata:Database reports/Most sitelinked items/Configuration, and it seems a faster way to get most sitelinked items above linkcount > 30. --Kanashimi (talk) 21:45, 23 January 2018 (UTC)

How do I get the descriptions for different languages in table columns?

How do I get the descriptions for different languages in table columns? E.g. for Portrait of a Lady in a Bonnet (Q30087097) I'd like to get the table:

?item	?descriptionEn	?descriptionDe	?descriptionNl
wd:Q30087097	painting by Lucas Cranach d. Ä.	Gemälde von Lucas Cranach d. Ä.	schilderij van Lucas Cranach d. Ä.

How is that possible? Do I have to use subqueries? Thanks a lot in advance! --Marsupium (talk) 21:48, 24 January 2018 (UTC), 21:50, 24 January 2018 (UTC)

The rdf helps here:

SELECT ?item ?descriptionEn ?descriptionDe ?descriptionNl WHERE {
  ?item wdt:P170 wd:Q191748 .
  ?item schema:description ?descriptionEn FILTER (LANG (?descriptionEn) = "en") . 
  ?item schema:description ?descriptionDe FILTER (LANG (?descriptionDe) = "de") . 
  ?item schema:description ?descriptionNl FILTER (LANG (?descriptionNl) = "nl") . 
  } LIMIT 50

Try it!

Multichill (talk) 21:58, 24 January 2018 (UTC)

Oh, I didn't know that it is possible to filter single statements! Thank you! --Marsupium (talk) 22:12, 24 January 2018 (UTC)

Getting Wikipedia articles linked to Wikidata items

Hello everyone! Could somebody help me with a query that would give me linked wikipedia articles to specific wikidata items? For example here is a query of the artworks in the collection of the Royal Museum of Fine Arts Antwerp:

SELECT ?work ?inventory_number ?creator ?creatorLabel ?collection ?collectionLabel WHERE {
  ?work wdt:P195 wd:Q1471477.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "nl". }
  OPTIONAL { ?work wdt:P170 ?creator. }
  OPTIONAL { ?work wdt:P195 ?collection. }
  OPTIONAL { ?work wdt:P217 ?inventory_number. }
}

Try it!

But I would also like to get a list of Wikipedia articles in all the languages for each artwork. Is this possible? Thank you! Alina data (talk) 13:44, 29 January 2018 (UTC)

@Alina data: Something like this - ES & EN examples given; you can extend to the languages of your choice.

SELECT ?article_en ?article_es ?work ?inventory_number ?creator ?creatorLabel ?collection ?collectionLabel WHERE {
  ?work wdt:P195 wd:Q1471477.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "nl" . }
  OPTIONAL { ?work wdt:P170 ?creator . }
  OPTIONAL { ?work wdt:P195 ?collection . }
  OPTIONAL { ?work wdt:P217 ?inventory_number . }
  OPTIONAL { ?article_en schema:about ?work ; schema:isPartOf <https://en.wikipedia.org/> . }
  OPTIONAL { ?article_es schema:about ?work ; schema:isPartOf <https://es.wikipedia.org/> . }
}

Try it!

Pro-tip is to export the results to CSV if you want them set out as a table; too many columns and the query service switches into a list-of-lists output. I don't know if there's a clever way to do this, avoiding specifying the languages of interest. --Tagishsimon (talk) 18:43, 29 January 2018 (UTC)

@Alina data: Here is another approach if you don't want a lot of columns and don't mind to have several rows, one per article, per artwork. You don't have to specify the languages in advance.

SELECT ?work ?workLabel ?inventory_number ?creatorLabel ?lang ?article  WHERE {
  ?work wdt:P195 wd:Q1471477 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "nl,en,fr,uk" . }
  OPTIONAL { ?work wdt:P170 ?creator . }
  OPTIONAL { ?work wdt:P217 ?inventory_number . }
  ?article schema:about ?work; schema:inLanguage ?lang .
  FILTER(!REGEX(str(?article),'commons'))    #remove this filter if you want to see the commons categories as well
}
ORDER BY ?work ?lang

Try it!

--Larske (talk) 19:32, 29 January 2018 (UTC)

Another approach :

SELECT ?article ?wp ?work WHERE {
 ?work wdt:P195 wd:Q1471477 .
 ?article schema:about ?work;
          schema:isPartOf ?siteurl .
  
 ?wp wdt:P31 wd:Q10876391.       # selects every wikipedia linguistic version
 ?wp wdt:P856 ?url .             # finds their base urls (string datatype)
 bind ( uri(?url) as ?siteurl )  # convert the urls strings to sparql url datatype
}

Try it!

or a variant grouping by work, all uris concatenated on the same column :

SELECT (group_concat(?article;separator=' ; ') as ?articles) (count(?article) as ?count) ?work WHERE {
SELECT ?article ?wp ?work WHERE {
 ?work wdt:P195 wd:Q1471477 .
 ?article schema:about ?work;
          schema:isPartOf ?siteurl .
  
 ?wp wdt:P31 wd:Q10876391. # selects every wikipedia
 ?wp wdt:P856 ?url . 
  bind ( uri(?url) as ?siteurl )
}
} group by ?work order by desc(?count)

Try it!

author  TomT0m / talk page 21:15, 29 January 2018 (UTC)

@Tagishsimon: @Larske: @TomT0m: These are perfect! Thank you! Alina data (talk) 14:32, 30 January 2018 (UTC)

Boundary polygons for London boroughs

I would like to get a set of boundary polygons for the London boroughs, in order to help determine which borough a coordinate is in.

I can get close to what I want with the following federated query, which gets the data from the UK Ordnance Survey, matching on the GSS code (2011) (P836) identifier.

SELECT ?item ?itemLabel ?gss ?os_toid ?bounds WHERE {
  	?item wdt:P31 wd:Q211690 .
  
    OPTIONAL {
      ?item wdt:P836 ?gss .
  
      SERVICE <http://data.ordnancesurvey.co.uk/datasets/os-linked-data/apis/sparql> {
        ?os_toid <http://data.ordnancesurvey.co.uk/ontology/admingeo/gssCode> ?gss .
        ?os_toid <http://data.ordnancesurvey.co.uk/ontology/geometry/extent> ?extent .
        ?extent <http://data.ordnancesurvey.co.uk/ontology/geometry/asGML> ?bounds .         
      }
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} ORDER BY ?itemLabel

Try it!

However, the results returned are in UK National Grid coordinates, rather than WGS 84 latitudes and longitudes.

Is there any other service, eg OpenStreetMap, that I can get latitudes and longitudes from instead? (And, just for fun, is it possible to plot such boundaries on WDQS maps?) Jheald (talk) 17:04, 29 January 2018 (UTC)

(It would also be OK if the boundaries were slightly lower resolution). Jheald (talk) 17:08, 29 January 2018 (UTC)

A "poor man's solution" might be to simply find the nearest item to the coordinates that we have a London borough for. But (as the map from this query shows) the data is often patchy (too often located in the administrative territorial entity (P131) is not detailed enough), and also has rather too many anomalies. Also it can get led astray if point coordinates have been given for extended linear features. Pretty, though:

#defaultView:Map
SELECT ?place ?placeLabel ?layerLabel ?location ?layer WHERE {
  wd:Q5090115 wdt:P625 ?Wloc .
  wd:Q1851584 wdt:P625 ?Eloc .
  SERVICE wikibase:box {
      ?place wdt:P625 ?location .
      bd:serviceParam wikibase:cornerWest ?Wloc .
      bd:serviceParam wikibase:cornerEast ?Eloc .
  }
  OPTIONAL {
    ?place wdt:P131 ?layer .
    ?layer wdt:P31 wd:Q211690
  }
  FILTER(BOUND(?layer)) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

So, all in all, maybe better to stick to plan A and actual boundary polygons. Jheald (talk) 17:37, 29 January 2018 (UTC)

Names of town which end with something

Hi, I search the french town with a name wich end with "rison".

I do :

SELECT ?item ?itemLabel ?coord WHERE {
  ?item wdt:P31 wd:Q484170.
  ?item rdfs:label ?itemLabel.
  ?item wdt:P625 ?coord.
  FILTER(REGEX(?itemLabel, "(rison)$"))
}

Try it!

...from exemple Wikidata:SPARQL query service/queries/examples/fr#Distribution des noms d'établissements humains se terminant par « ow » ou « itz » en Allemagne

But in the result, there is "Montbrison" a lot of lines. Why ?

--Touam (talk) 09:18, 31 January 2018 (UTC)

@Touam: because you are getting one row for each ?itemLabel which Montbrison possesses ... the item has a "Montbrison" label for many distinct languages. Adding FILTER (LANG (?itemLabel) = "fr") to the ?itemLabel line sorts it out:

SELECT ?item ?itemLabel ?coord WHERE {
  ?item wdt:P31 wd:Q484170.
  ?item rdfs:label ?itemLabel FILTER (LANG (?itemLabel) = "fr") .
  ?item wdt:P625 ?coord.
  FILTER(REGEX(?itemLabel, "(rison)$"))
  }

Try it!

--Tagishsimon (talk) 09:45, 31 January 2018 (UTC)

Worth noting, also, that Contrisson (Q724007) and Brusson (Q1103546), which were found by your query, are not found by the filtered query, since the French spelling has a ss spelling, and so does not match your regex. So another approach, to list any item having a label in any language that matches your regex, is:

SELECT ?item (sample(?itemLabel) as ?itemLabel) (sample(?coord) as ?coord) WHERE {
  ?item wdt:P31 wd:Q484170.
  ?item rdfs:label ?itemLabel.
  ?item wdt:P625 ?coord.
  FILTER(REGEX(?itemLabel, "(rison)$"))
}
group by ?item

Try it!

... there is probably a better approach (show one row per regex match for each item/label-language) but right now it eludes me. --Tagishsimon (talk) 09:45, 31 January 2018 (UTC)

I don’t really know if this property is widely used, but there is also a language independant property for the names in their native language : official name (P1448) . This gives

SELECT ?item ?name ?coord WHERE {
  ?item wdt:P31 wd:Q484170.
  # ?item rdfs:label ?itemLabel.
  ?item wdt:P625 ?coord.
  ?item wdt:P1448 ?name.
  FILTER(REGEX(?name, "(rison)$"))
}limit 100

Try it!