Wikidata:Request a query/Archive/2019/02

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

Remove porno actresses from a query

Hello,

I am training Canadian librarians at University York / Ryerson and I'd like to use the query below. It is targeting Canadian subjects that has no articles in English.

Now, I would like to remove pornographic actor (Q488111), erotic photography model (Q3286043) and model (Q4610556) from that query, because it would be inappropriate to present this request with such a result in front of this audience... Thanks in advance, Benoit Rochon (talk) 12:26, 29 January 2019 (UTC)

SELECT ?item ?itemLabel ?cnt WHERE {
{
  SELECT ?item (COUNT(?sitelink) AS ?cnt) WHERE {
  ?item wdt:P27|wdt:P205|wdt:P17 wd:Q16 .
  ?sitelink schema:about ?item .
  FILTER NOT EXISTS {
    ?article schema:about ?item .
    ?article schema:isPartOf <https://en.wikipedia.org/> .
  }
  } GROUP BY ?item ORDER BY DESC (?cnt) LIMIT 1000
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr" }  
} ORDER BY DESC (?cnt)

Try it!

@Benoit Rochon: After a false start, query below.

SELECT ?item ?itemLabel ?cnt WHERE {
{
  SELECT ?item (COUNT(?sitelink) AS ?cnt) WHERE {
  ?item wdt:P27|wdt:P205|wdt:P17 wd:Q16 .
  minus {?item wdt:P106 wd:Q488111 .}
  minus {?item wdt:P106 wd:Q3286043 .}
  minus {?item wdt:P106 wd:Q4610556 .}  
  ?sitelink schema:about ?item .
  FILTER NOT EXISTS {
    ?article schema:about ?item .
    ?article schema:isPartOf <https://en.wikipedia.org/> .
  }
  } GROUP BY ?item ORDER BY DESC (?cnt) LIMIT 1000
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr" }  
} ORDER BY DESC (?cnt)

Try it!

--Tagishsimon (talk) 13:43, 29 January 2019 (UTC)

Thank you Tagishsimon, once again, it was very useful. I add it in the examples. Best, Benoit Rochon (talk) 13:13, 2 February 2019 (UTC)

Population of countries neighbouring Germany

Hello I've come accross this query and was surprised to find Saarland (Q1201) as a country neighbouring Germany (it was at a certain time but not now). I wasn't able to find what is wrong into the Saarland (Q1201) . Bouzinac (talk) 12:31, 2 February 2019 (UTC)

#Population des pays voisins de l'Allemagne
SELECT ?year ?population ?countryLabel WHERE {
  {
    SELECT ?country ?year (AVG(?population) AS ?population) WHERE {
      {        SELECT ?country (YEAR(?date) AS ?year) ?population WHERE {
          ?country wdt:P47 wd:Q183.
          ?country p:P1082 ?populationStatement.
          ?populationStatement ps:P1082 ?population.
          ?populationStatement pq:P585 ?date.
          FILTER(year(?date)>=1900) .
          #FILTER( ?country not in (wd:Q1201)).
        }      }
    }
    GROUP BY ?country ?year
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
}

Try it!

Besides, if the template {{SPARQL}} was able to clean white-not very white-spaces ? Bouzinac (talk) 12:31, 2 February 2019 (UTC)

@Bouzinac: There's nothing much wrong. Items are included in the query if they have a wdt:P47 wd:Q183 property/value, which Saarland does. You need to add further conditions if you want to eliminate non-countries. --Tagishsimon (talk) 12:52, 2 February 2019 (UTC)

Ok, i've found out this query (raising same Saarland problem :) ) it shows the current neighbours of Germany

SELECT ?border ?borderLabel ?country1Label ?country2Label ?isLandBorder ?isMaritimeBorder ?constraint {
  VALUES (?country1) {(wd:Q183)}
  ?border wdt:P31 wd:Q12413618 ;
          wdt:P17 ?country1 , ?country2 .
  FILTER (?country1 != ?country2)
  BIND (EXISTS {?border wdt:P31 wd:Q15104814} AS ?isLandBorder)
  BIND (EXISTS {?border wdt:P31 wd:Q3089219} AS ?isMaritimeBorder)
  BIND ((?isLandBorder|| ?isMaritimeBorder) AS ?constraint)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
  } ORDER BY ?country1Label

Try it!

Bouzinac (talk) 13:15, 2 February 2019 (UTC)

I tried to adapt and was able to get all (lol) countries neighbouring Germany : how to fix it ?

#Population des pays voisins de l'Allemagne SELECT ?year ?population ?country2Label WHERE {

 {    SELECT ?country2 ?year (AVG(?population) AS ?population) WHERE {
     {        SELECT ?country2 (YEAR(?date) AS ?year) ?population WHERE {
         ?country wdt:P47 wd:Q183.
         ?border wdt:P31 wd:Q12413618 ;
         wdt:P17 ?country , ?country2 .
         FILTER (?country != ?country2)
         BIND (EXISTS {?border wdt:P31 wd:Q15104814} AS ?isLandBorder)
         BIND (EXISTS {?border wdt:P31 wd:Q3089219} AS ?isMaritimeBorder)
         BIND ((?isLandBorder|| ?isMaritimeBorder) AS ?constraint)
         ?country2 p:P1082 ?populationStatement.
         ?populationStatement ps:P1082 ?population.
         ?populationStatement pq:P585 ?date.
         FILTER(year(?date)>=1900) .
       }
     }
   }
   GROUP BY ?country2 ?year  }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }   }

Thank you! Bouzinac (talk) 13:23, 2 February 2019 (UTC)

Visualising gene structure 'is part of' relationships as diagram

I originally posted to project chat, but I've realised here might be a more sensible location to ask.

Hello, I'm trying to encode the relationships and references in the two diagrams in w:Gene_structure within Wikidata. I feel like it should be possibly to recapitulate the structure of that diagram with a Wikidata Query network graph.

• Starting at Gene
• Edges of Property:P361, Property:P31, and Property:P279 in different colours (showing only those that have qualifier of (P642) eukaryotes.
• Edges down from Gene, but also across between elements
• Colour nodes by whether they are part of open reading frame, UTR or regulatory sequence

Here is my first attempt butchered from a previous query written by Fnielsen because I cant work out the syntax enough to implement most of the features above. Any ideas on how to fix it? Evolution and evolvability (talk) 03:42, 31 January 2019 (UTC)

@Evolution and evolvability: The Graph view doesn't allow to colour edges, see here. -- Luitzen (talk) 19:30, 31 January 2019 (UTC)

@Luitzen: Ah, thank you. Can the edge labels be customised (e.g. 'P' for 'part of', 'i' for 'instance of'). Evolution and evolvability (talk) 01:21, 1 February 2019 (UTC)

I've made an updated version, but I suspect there are still redundant parts. Any ideas on how to simplify it, perhaps with some sort of structure like this?

PREFIX gas: <http://www.bigdata.com/rdf/gas#>

#defaultView:Graph
# parts of a gene and their subclasses
SELECT DISTINCT ?down ?downLabel ?up ?upLabel ("#ff0000" AS ?rgb) WHERE {
  {
    {
      SELECT ?down ?up WHERE { # subclass of untranslated region
        SERVICE gas:service {
          gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.BFS".
          gas:program gas:in wd:Q424345.
          gas:program gas:traversalDirection "Reverse".
          gas:program gas:out ?down.
          gas:program gas:out1 ?depth.
          gas:program gas:out2 ?up.
          gas:program gas:linkType wdt:P279.
        }
      }
    }
  }
  UNION
  {
    {
      SELECT ?down ?up WHERE { # subclass of regulatory sequence
        SERVICE gas:service {
          gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.BFS".
          gas:program gas:in wd:Q3238407.
          gas:program gas:traversalDirection "Reverse".
          gas:program gas:out ?down.
          gas:program gas:out1 ?depth.
          gas:program gas:out2 ?up.
          gas:program gas:linkType wdt:P279.
        }
      }
    }
  }
  UNION
  {
    {
      SELECT ?down ?up WHERE { # part of coding region
        SERVICE gas:service {
          gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.BFS".
          gas:program gas:in wd:Q3780824.
          gas:program gas:traversalDirection "Reverse".
          gas:program gas:out ?down.
          gas:program gas:out1 ?depth.
          gas:program gas:out2 ?up.
          gas:program gas:linkType wdt:P361.
        }
      }
    }
  }
  UNION
  {
    {
      SELECT ?down ?up WHERE { # instance of regulatory sequence
        SERVICE gas:service {
          gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.BFS".
          gas:program gas:in wd:Q424345.
          gas:program gas:traversalDirection "Reverse".
          gas:program gas:out ?down.
          gas:program gas:out1 ?depth.
          gas:program gas:out2 ?up.
          gas:program gas:linkType wdt:P31.
        }
      }
    }
  }
   UNION
  {
    {
      SELECT ?down ?up WHERE { # part of gene
        SERVICE gas:service {
          gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.BFS".
          gas:program gas:in wd:Q7187. # gene
          gas:program gas:traversalDirection "Reverse".
          gas:program gas:out ?down.
          gas:program gas:out1 ?depth.
          gas:program gas:out2 ?up.
          gas:program gas:linkType wdt:P361.
        }
      }
    }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,da,sv,jp,zh,ru,fr,de". }
}

Try it!

I'm also struggling to work out how to use the ?edgeLabel parameter (simplified example). Any advice appreciated! Evolution and evolvability (talk) 06:02, 3 February 2019 (UTC)

Get all release dates, platforms, genres, etc. for a video game in one query

What I'm trying to do is create a query that can be used to get more than one property at a time. For example this query gets the platforms (platform (P400)) for the game Half-Life 2 (Half-Life 2 (Q193581)):

SELECT ?value ?valueLabel WHERE {
    wd:Q193581 wdt:P400 ?value .
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en". }
}
LIMIT 10

I've been able to create queries that have multiple properties, but it can only ever get one of each (e.g. one platform, one release date, one genre) when I want to be able to see all the platforms/release dates/genres together in the same query (preferably in one row).

Thanks, Nicereddy (talk) 20:34, 1 February 2019 (UTC)

@Nicereddy: I can think of a couple of options. The first gets you a tabular view of data, but requires you to specify in advance the platforms of interest (or genres, &c)

SELECT ?item ?itemLabel ?Android ?Linux ?MS_Windows ?PS3 ?MacOS ?Xbox360 ?Xbox with {
  SELECT ?item WHERE 
  {
    ?item wdt:P31 wd:Q7889 .
  } LIMIT 10 } as %i
WHERE
{
  include %i
  optional {?item wdt:P400 ?Q94 . filter (?Q94=wd:Q94) }
  optional {?item wdt:P400 ?Q388 . filter (?Q388=wd:Q388) }
  optional {?item wdt:P400 ?Q1406 . filter (?Q1406=wd:Q1406) }
  optional {?item wdt:P400 ?Q10683 . filter (?Q10683=wd:Q10683) }
  optional {?item wdt:P400 ?Q14116 . filter (?Q14116=wd:Q14116) }
  optional {?item wdt:P400 ?Q48263 . filter (?Q48263=wd:Q48263) }
  optional {?item wdt:P400 ?Q132020 . filter (?Q132020=wd:Q132020) }
  bind(if(bound(?Q94),1,"") as ?Android)
  bind(if(bound(?Q388),1,"") as ?Linux)
  bind(if(bound(?Q1406),1,"") as ?MS_Windows)
  bind(if(bound(?Q10683),1,"") as ?PS3)
  bind(if(bound(?Q14116),1,"") as ?MacOS)
  bind(if(bound(?Q48263),1,"") as ?Xbox360)
  bind(if(bound(?Q132020),1,"") as ?Xbox)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en". }
}

Try it!

And the second, using group_concat, gets you a much less tabular view, but doesn't require a priori knowledge

SELECT ?item ?itemLabel (group_concat(distinct ?platform;separator=", ") as ?platforms) (group_concat(distinct ?genre;separator=", ") as ?genres) with {
  SELECT ?item WHERE 
  {
    ?item wdt:P31 wd:Q7889 .
  } LIMIT 10 } as %i
WHERE
{ 
  include %i
  ?item wdt:P400 ?value . ?value rdfs:label ?platform . filter(lang(?platform)="en")
  ?item wdt:P136 ?value2 . ?value2 rdfs:label ?genre . filter(lang(?genre)="en")
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en". }
} group by ?item ?itemLabel

Try it!

Both queries make use of named subqueries in an effort to make them more efficient - when scaled up from LIMIT 10, you may avoid timeout issues that would otherwise arise. There may be other techniques I'm not aware of - don't take any of the above as the canonical word on the matter. hth --Tagishsimon (talk) 00:03, 2 February 2019 (UTC)

@Tagishsimon: That helps a lot, thanks! One other question: Would it be possible to list the platforms and genres via their Wikidata IDs (e.g. Q1234) rather than their labels in that second query? I assume it'd be possible to replace `rdfs:label` with something, but I haven't been able to figure out what that'd be. Thanks a ton, Nicereddy (talk) 16:31, 3 February 2019 (UTC)

After playing around with it for a while longer, it looks like it's possible to do this with the query below, with the caveat that it outputs the full wikidata URL rather than just the ID. I'm not sure if there's a better way to do this, but it works :)

SELECT ?item ?itemLabel (group_concat(distinct ?platform;separator=", ") as ?platforms) (group_concat(distinct ?genre;separator=", ") as ?genres) with {
  SELECT ?item WHERE 
  {
    ?item wdt:P31 wd:Q7889 .
  } LIMIT 10 } as %i
WHERE
{ 
  include %i
  ?item wdt:P400 ?platform.
  ?item wdt:P136 ?genre.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en". }
} group by ?item ?itemLabel

Try it!

Nicereddy (talk)

@Nicereddy: like this?

SELECT ?item ?itemLabel (group_concat(distinct ?platform;separator=", ") as ?platforms) (group_concat(distinct ?genre;separator=", ") as ?genres) with {
  SELECT ?item WHERE 
  {
    ?item wdt:P31 wd:Q7889 .
  } LIMIT 10 } as %i
WHERE
{ 
  include %i
  ?item wdt:P400 ?p1.
  ?item wdt:P136 ?g1.
  bind(strafter(str(?p1), "http://www.wikidata.org/entity/") as ?platform)
  bind(strafter(str(?g1), "http://www.wikidata.org/entity/") as ?genre)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} group by ?item ?itemLabel

Try it!

--Tagishsimon (talk) 17:20, 3 February 2019 (UTC)

Exactly like that! Thanks! Nicereddy (talk)

How do I get the full time series of data for items like Population or Number of Out-of-school Children?

Let's say I'm looking at https://www.wikidata.org/wiki/Q27. How do I write a query to give me back the time series of data for Population? My query as is is extremely simple and only returns a single value. I'm not able to find any example queries that showcase this functionality.

SELECT ?population WHERE {
  BIND(wd:Q27 as ?Ireland).
  ?Ireland wdt:P1082 ?population.
}
LIMIT 100

Try it!

Thanks! Hebejebelus (talk) 06:30, 3 February 2019 (UTC)

Aha, I have found an answer here: Wikidata:Request a query/Archive/2018/12#How to get the timePrecision associated with a Point in Time for a population statement? Hebejebelus (talk) 07:19, 3 February 2019 (UTC)

@Hebejebelus: somethine like this. You need to get get the ps:value, via the p: statement. wdt: will get you only a single value having, in this case, a preferred rank. There's an explainer at User talk:Tagishsimon#Wittylama

SELECT ?population ?date WHERE {
  wd:Q27 p:P1082 [ps:P1082 ?population ; pq:P585 ?date] .
} order by ?date

Try it!

--Tagishsimon (talk) 08:39, 3 February 2019 (UTC)

Labling both axes on 'all against all' comparison using dimensions view

Hello all, I'm trying to make a dimensions diagram of drug interactions. Any idea on how to label both axes? Evolution and evolvability (talk) 09:33, 3 February 2019 (UTC)

#defaultView:Dimensions
SELECT DISTINCT ?drug_A ?drug_B
WHERE
{
  VALUES ?toggle { true false }
  ?interaction wdt:P31+ wd:Q12140;
           wdt:P769 ?dr.
  ?dr rdfs:label ?drLabel.
    FILTER(LANG(?drLabel) = "en").
  ?interaction rdfs:label ?interactionLabel.
    FILTER(LANG(?interactionLabel) = "en").
  BIND(IF(?toggle,?interactionLabel,?drLabel) AS ?drug_A).
  BIND(IF(?toggle,?interaction,?dr) AS ?drug_B).
  BIND(IF(?toggle,"",?interaction) AS ?link).
}

Try it!

ɦÂ

Get count of statements for specified property

Hello. Please modify this query.

SELECT DISTINCT ?p
WHERE  {  VALUES ?p { # in fact there can be more than 30,000 items
   wd:Q30385394 wd:Q30359318 # two statements P577 (now)
   wd:Q21030593 wd:Q21045381 # exact one statement P577
   wd:Q1305037               # without statement P577
 } }

Try it!

Changed query must return only the items which contain exactly one statement with the property publication date (P577) (i.e. wd:Q21030593 wd:Q21045381 elements). As a result, only the query result variable ?p must be displayed. Thanks. --Renamerr (talk) 22:48, 2 February 2019 (UTC)

@Renamerr: Basically, this:

SELECT ?p { 
  VALUES ?p { # in fact there can be more than 30,000 items
    wd:Q30385394 wd:Q30359318 # two statements P577 (now)
    wd:Q21030593 wd:Q21045381 # exact one statement P577
    wd:Q1305037               # without statement P577
  } 
  ?p wdt:P577 []
} GROUP BY ?p HAVING (COUNT(*) = 1)

Try it!

or

SELECT ?p { 
  VALUES ?p { # in fact there can be more than 30,000 items
    wd:Q30385394 wd:Q30359318 # two statements P577 (now)
    wd:Q21030593 wd:Q21045381 # exact one statement P577
    wd:Q1305037               # without statement P577
  } 
  ?p wdt:P577 ?date1
  FILTER NOT EXISTS {
    ?p wdt:P577 ?date2
    FILTER(?date2 != ?date1)
    }
}

Try it!

-- Luitzen (talk) 20:32, 3 February 2019 (UTC)

@Luitzen: Thank you very much. Best regards, Renamerr (talk) 21:47, 3 February 2019 (UTC) P.S. Feedback. Both of the proposed queries are running fast. For 40000 elements (39985 items found) ≈13000 ms was spent. --Renamerr (talk) 22:08, 3 February 2019 (UTC)

Youngest executed criminals

Hello, I'd like to get a short list of those executed (exemple : George Stinney (Q1336052) ) when very young. Say, all those executed under 18. Thanks ! Bouzinac (talk) 11:33, 4 February 2019 (UTC)

@Bouzinac: List of candidates:

SELECT ?item ?itemLabel ?age  {
  ?item wdt:P1196 wd:Q8454 . 
# ?item wdt:P1399 [] .
  ?item p:P569 [ a wikibase:BestRank ;
                 psv:P569 [ wikibase:timeValue ?birth;
                            wikibase:timePrecision ?precision_birth ] ] .
  ?item p:P570 [ a wikibase:BestRank ;
                 psv:P570 [ wikibase:timeValue ?death;
                            wikibase:timePrecision ?precision_death ] ] . 
  FILTER (?precision_birth > 8 && ?precision_death > 8) 
  BIND ((?death - ?birth)/365.25 AS ?age) 
  FILTER (?age <= 18.5)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en"}
} ORDER BY ?age

Try it!

-- Luitzen (talk) 10:50, 5 February 2019 (UTC)

Very interesting. Thanks! Bouzinac (talk) 12:40, 5 February 2019 (UTC)

Cities names ending en 'ac' ==> time out

#added before 2016-10
SELECT ?item ?itemLabel ?coord WHERE {
  ?item (wdt:P31/wdt:P279*) wd:Q486972.
  ?item wdt:P17 wd:Q142.
  ?item rdfs:label ?itemLabel.
  ?item wdt:P625 ?coord.
  FILTER((LANG(?itemLabel)) = "fr")
  FILTER(REGEX(?itemLabel, "ac$"))
}

Try it!

== timing out...any clue? Thanks ! Bouzinac (talk) 17:02, 4 February 2019 (UTC)

@Bouzinac: The following query is fast and returns more than 90% of results:

SELECT ?item ?itemLabel ?coord  {
  ?item wdt:P31/wdt:P279* wd:Q484170 .
  ?item wdt:P625 ?coord.  
  ?item wdt:P1448 ?itemLabel .
  FILTER(LANG(?itemLabel) = "fr")  
  FILTER(REGEX(?itemLabel, "ac$"))
}

Try it!

 -- Luitzen (talk) 21:49, 4 February 2019 (UTC)

Yes, thanks! I'll update the query example Bouzinac (talk) 08:29, 5 February 2019 (UTC)

Federated query to sparql endpoint defined in property

This works.

SELECT * WHERE 
{
  wd:Q2013 wdt:P5305 ?sparql_endpoint .
  SERVICE <https://query.wikidata.org/sparql>
  {
  ?a wdt:P31 wd:Q146.
  }
}
LIMIT 1

Try it!

However is it possible to query to some endpoint which address will come from query itself?

Example in pseudocode (doesn't work)

SELECT * WHERE 
{
  wd:Q2013 wdt:P5305 ?sparql_endpoint .
  SERVICE <?sparql_endpoint>
  {
  ?a wdt:P31 wd:Q146.
  }
}
LIMIT 1

Try it!

--Zache (talk) 03:10, 5 February 2019 (UTC)

Answer to myself. It just didnt work with query.wikidata.org, but it did work in Sophox like this:

PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX ps: <http://www.wikidata.org/prop/statement/>
PREFIX pq: <http://www.wikidata.org/prop/qualifier/>
PREFIX schema: <http://schema.org/>

SELECT * WHERE 
{
  BIND(uri("https://query.wikidata.org/sparql") AS ?sparql_endpoint)
  SERVICE  ?sparql_endpoint
  {
        VALUES ?sparql_services { wd:Q2013 wd:Q234110 }
        ?sparql_services wdt:P5305 ?sparql_endpoint2 .
  }
  SERVICE  ?sparql_endpoint2
  {
      SELECT * WHERE { ?a ?b ?c } LIMIT 1
  }
}

Try it!

--Zache (talk) 04:07, 5 February 2019 (UTC)

@Zache:

Tracked in Phabricator
Task T196798

It seems it had been implemented in Blazegraph (see here), but is not working on Wikidata. -- Luitzen (talk) 08:50, 5 February 2019 (UTC)

Thanks! --Zache (talk) 10:44, 5 February 2019 (UTC)

Twins with different place of birth

I am looking for siblings (connected by sibling (P3373)) with same date of birth (P569) but different place of birth (P19). Steak (talk) 09:38, 5 February 2019 (UTC)

@Steak: Remarkable. You'll get two rows for some pairs; we should also look for Place of Birth being missing on one of the pairs, sometime.

SELECT ?item ?itemLabel ?pob1Label ?sibling ?siblingLabel ?pob2Label 
WHERE 
{
  ?item wdt:P3373 ?sibling.
  ?item wdt:P569 ?dob .
  ?sibling wdt:P569 ?dob .
  ?item wdt:P19 ?pob1 .
  ?sibling wdt:P19 ?pob2 .
  filter (?pob1 != ?pob2) 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 09:43, 5 February 2019 (UTC)

Thank you! Didn't expect that much... Steak (talk) 09:46, 5 February 2019 (UTC)

No. Here's 'only one sibling has a place of birth'

SELECT ?item ?itemLabel ?pob1 ?pob1Label ?sibling ?siblingLabel ?pob2Label 
WHERE 
{
  ?item wdt:P3373 ?sibling.
  ?item wdt:P569 ?dob .
  ?sibling wdt:P569 ?dob .
  ?item wdt:P19 ?pob1 .
  filter not exists {?sibling wdt:P19 ?pob2 .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

And, shall we call them, unidirectional siblings

SELECT ?item ?itemLabel ?sibling ?siblingLabel  
WHERE 
{
  ?item wdt:P3373 ?sibling.
  ?item wdt:P569 ?dob .
  ?sibling wdt:P569 ?dob .
  filter not exists {?sibling wdt:P3373 ?item .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 10:00, 5 February 2019 (UTC)

Weather station with no closure date OR closure after 1953

I am able to write a query to get the weather stations open in 1953 and that were close afterwards, and another one for stations with no closure date.

Somehow, I am not able to combine the two conditions in one SPARQL query. The result of what I've been able to achieve only shows station with a closure date after 1953 but not those with no closure date (if the quere were successful, Kincardine (Q61039179) should be in the result of the query below). I tried to revert the clauses between the "||" but it didn't change anything.

# Stations d'Ontario actives en 1953 
#defaultView:Map
SELECT ?item ?itemLabel ?coord 
WHERE {
  ?item (wdt:P31/wdt:P279*) wd:Q190107.
  ?item wdt:P131 wd:Q1904 .

  ?item wdt:P729 ?service_entry . 
  # service entry is before 1953
  FILTER (?service_entry <= "1953-01-01T00:00:00Z"^^xsd:dateTime) .
  # and closure date not before 1953
  ?item wdt:P3999 ?closureDate .
  FILTER(
    # Closure date is not set
    ?closureDate > "1953-01-01T00:00:00Z"^^xsd:dateTime||  NOT EXISTS { ?item wdt:P3999 [] }     # or closure date is after 1953
  )
 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  OPTIONAL { ?item wdt:P625 ?coord. }
}

Try it!

Could someone help me to have both conditions filtering the result at the same time (one OR the other)?

Thanks, Dirac (talk) 16:03, 5 February 2019 (UTC)

@Dirac: E.g.

# Stations d'Ontario actives en 1953 
#defaultView:Map
SELECT ?item ?itemLabel ?coord 
WHERE {
  ?item (wdt:P31/wdt:P279*) wd:Q190107.
  ?item wdt:P131 wd:Q1904 .

  ?item wdt:P729 ?service_entry . 
  # service entry is before 1953
  FILTER (?service_entry <= "1953-01-01T00:00:00Z"^^xsd:dateTime) .
  # and closure date not before 1953
  OPTIONAL { ?item wdt:P3999 ?closureDate }
  FILTER(
    # Closure date is not set
    ?closureDate > "1953-01-01T00:00:00Z"^^xsd:dateTime|| !BOUND(?closureDate)     # or closure date is after 1953
  )
 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  OPTIONAL { ?item wdt:P625 ?coord. }
}

Try it!

See also https://www.w3.org/TR/sparql11-query/#alternatives. -- Luitzen (talk) 17:31, 5 February 2019 (UTC)

sparql

se desea que los resultados de una consulta federada, empleando Wikidata y Europeana, sean empleados para alimentar un simple catálogo de imágenes asociadas a las obras de pintores impresionistas y/o expresionistas. Para cada obra, será necesario recopilar, como mínimo, la siguiente información: Nombre del autor Título de la obra Enlace a la imagen  – The preceding unsigned comment was added by 79.144.202.117 (talk • contribs) at 29. 12. 2018, 19:37‎ (UTC).

Using Wikidata only:

#defaultView:ImageGrid
select ?item ?itemLabel ?author ?authorLabel ?image {
  values (?movement) {(wd:Q40415)}
  ?author wdt:P135 ?movement .
  ?item wdt:P170 ?author .
  optional { ?item wdt:P18 ?image }
  service wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

 -- Luitzen (talk) 09:02, 6 February 2019 (UTC)

Release date of earliest edition of a book

Hi,

I need the chronologically earliest publication date (P577) of a literary work (Q7725634). This is what I have so far:

select ?work ?workLabel ?earliestEdition ?earliestEditionReleaseDate {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?work wdt:P31 wd:Q7725634.
  ?work wdt:P747 ?edition. # but only the ?earliestEdition.
  # something, something wdt:P577 and wdt:P577
}

Try it!

Thank you 😅 --Shisma (talk) 19:50, 3 February 2019 (UTC)

@Shisma: The idea is the following:

SELECT DISTINCT ?work ?workLabel ?earliestEdition (year(?earliestEditionReleaseDate) AS ?year) {
  ?work wdt:P31 wd:Q7725634.
  ?work wdt:P747 ?earliestEdition. 
  ?earliestEdition wdt:P577 ?earliestEditionReleaseDate .
  FILTER (!isBLANK(?earliestEditionReleaseDate))
  FILTER NOT EXISTS {
    ?work wdt:P747 ?edition . 
    ?edition wdt:P577 ?editionReleaseDate . 
    FILTER (?editionReleaseDate < ?earliestEditionReleaseDate )
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

 -- Luitzen (talk) 18:39, 5 February 2019 (UTC)

as soon as I replace (year(?earliestEditionReleaseDate) AS ?year) with ?earliestEditionReleaseDate it will find the earliest edition multiple times. Any idea why that is?--Shisma (talk) 20:02, 5 February 2019 (UTC)

The number of results should be the same in both cases. Why multiple? Look into e.g. The Godfather (Q243556) -- Luitzen (talk) 20:41, 5 February 2019 (UTC)

Black/African-American and LGBT individuals in STEM

In honor of Black History Month, I am hoping to write some biographies on Black/African-American and LGBT people. I mostly focus on members of the STEM community. If there are enough, I was hoping to write about people who are both Black and LGBT that do not already have an article on English Wikipedia. I am also interested if they have a Twitter username. Thank you! Thsmi002 (talk) 16:08, 1 February 2019 (UTC)

@Thsmi002: I think there are lack-of-data issues which will frustrate this query. Example: here are counts of sexual orientation (P91):

SELECT ?sexualityLabel (COUNT(?sexuality) AS ?count)
WHERE {
  ?item wdt:P91 ?sexuality .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by  ?sexualityLabel

Try it!

and ethnic group (P172)

SELECT ?ethnicityLabel (COUNT(?ethnicity) AS ?count)
WHERE {
  ?item wdt:P172 ?ethnicity .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by  ?ethnicityLabel

Try it!

perhaps slightly better for African Americans. So here are AAs with no en.wiki article, showing the number of sitelinks, which might or might not be useful:

SELECT ?item ?itemLabel ?linkcount WHERE {
  ?item wdt:P172 wd:Q49085 .
  FILTER NOT EXISTS {                              # has no en.wikipedia sitelink
    ?wen schema:about ?item .
    ?wen schema:isPartOf <https://en.wikipedia.org/> .
  }
  ?item wdt:P31 wd:Q5 .                            # human
  OPTIONAL {?item wikibase:sitelinks ?linkcount .} # count of sitelinks 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by DESC(?linkcount)

Try it!

Here is other then heterosexual people:

SELECT ?item ?itemLabel ?sexualityLabel ?linkcount WHERE {
  ?item wdt:P91 ?sexuality .
  values ?sexuality {wd:Q592 wd:Q6636 wd:Q6649 wd:Q43200 wd:Q51415 wd:Q255155 wd:Q271534 wd:Q339014 wd:Q724351 wd:Q8354594
}  FILTER NOT EXISTS {                              # has no en.wikipedia sitelink
    ?wen schema:about ?item .
    ?wen schema:isPartOf <https://en.wikipedia.org/> .
  }
  ?item wdt:P31 wd:Q5 .                            # human
  OPTIONAL {?item wikibase:sitelinks ?linkcount .} # count of sitelinks 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by DESC(?linkcount)

Try it!

And the only record which combines these two is Skitzo (Q37022934). Not sure how much help any of that was. There is also w:en:Wikipedia:WikiProject LGBT studies/Missing biographies of nonbinary, trans and intersex people and w:en:Wikipedia:WikiProject Women in Red/Black Lunch Table, both found on the w:en:Wikipedia:WikiProject Women in Red/Redlist index page, which may be of interest. --Tagishsimon (talk) 16:48, 1 February 2019 (UTC)

@Tagishsimon: Thank you for these queries, I appreciate it! I figured there would not be many results, but that it was at least worth checking :) Thsmi002 (talk) 13:47, 7 February 2019 (UTC)

All articles from Wikipedia project without description

Hi, maybe I want just too much, but is there a possibility of having a query which will show me all the items linked with Slovak Wikipedia that don't have a description? Thanks for the help, --Luky001 (talk) 18:45, 6 February 2019 (UTC)

@Luky001: Probably not in a single query, but yes if you break the problem down into a series of queries. One key question is, in which language(s) must the item not have a description? EN? SK? Any language?

The basic query is below. There are iirc 312,000 items pointing to sk.wikipedia. Looks like a query checking for no descriptions in any language can handle about 100k at a time. So with judicious use of the OFFSET n and LIMIT n, you can with three or four queries go through all the items. The query below is for offset 200000 limit 100000. You would need to adapt that to cover:

• limit 100000
• offset 100000 limit 100000
• offset 200000 limit 100000
• offset 300000 limit 20000

SELECT ?item ?itemLabel with {
  select ?article where
    {
      ?article schema:isPartOf <https://sk.wikipedia.org/> .
    } offset 200000 limit 100000 } as %i
where
{
  include %i
  ?article schema:about ?item .
  filter not exists {?item schema:description ?desc .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

If, instead, you want to check for no description in a specific language - I use EN as the example here, but you can switch it to any langauge you choose by altering the language code in filter(lang(?desc)="en") - you want:

SELECT ?item ?itemLabel with {
  select ?article where
    {
      ?article schema:isPartOf <https://sk.wikipedia.org/> .
    } offset 200000 limit 100000 } as %i
where
{
  include %i
  ?article schema:about ?item .
  filter not exists {?item schema:description ?desc . filter(lang(?desc)="en") }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Finally, if you start getting timeouts, make your increments smaller - e.g. 50k chunks instead of 100k chunks. --Tagishsimon (talk) 19:15, 6 February 2019 (UTC)

Search for genus & species items missing descriptions

How can I get significantly more than a paltry 2000-3000 results from

SELECT ?rank WHERE {
  ?rank wdt:P105 wd:Q34740. # Q37517=class; Q36602=order; Q35409=family; Q34740=genus; Q7432=species
  FILTER(NOT EXISTS {
    ?rank schema:description ?itemdesc.
    FILTER(LANG(?itemdesc) = "en") # with missing English description
  })
}

Try it!

for genus & species items with empty descriptions? —Tom.Reding (talk) 13:50, 8 February 2019 (UTC)

I'm confused...when I set "LIMIT = 5000" I get either a server error or a timeout... Then when I remove the LIMIT, I get 28,786 results...wtf? —Tom.Reding (talk) 13:58, 8 February 2019 (UTC)

It's very odd. Works fine with LIMIT 5, LIMIT 50, LIMIT 500, timesout or server error at LIMIT 5000, works fine with no limit - 28787 results in 24061 ms. @Smalyshev (WMF), Lucas Werkmeister (WMDE): any thoughts? --Tagishsimon (talk) 17:33, 8 February 2019 (UTC)

Good point! If a query has LIMIT and doesn't have ORDER BY, Blazegraph divides query into smaller parts. Compare execution plans: 1, 2.

It seems that setting … hint:pipelinedHashJoin false can't override this behavior. Just add ORDER BY:

SELECT ?rank WHERE {
  ?rank wdt:P105 wd:Q34740.
  FILTER NOT EXISTS {
    ?rank schema:description ?itemdesc.
    FILTER(LANG(?itemdesc) = "en")
  } 
} ORDER BY ?something LIMIT 29000

Try it!

-- Luitzen (talk) 18:20, 8 February 2019 (UTC)

@Tom.Reding: As for species… I'd suggest to divide your query into smaller parts that correspond to classes. Like this.

Example for mammals:

SELECT DISTINCT ?species {
    ?species wdt:P171+ wd:Q7377. 
    ?species wdt:P105 wd:Q7432
    FILTER NOT EXISTS {
        ?species schema:description ?itemdesc
        FILTER(LANG(?itemdesc) = 'en')
    }
}

Try it!

Unfortunately, this doesn't work for insects, so divide them into subtaxons etc. -- Luitzen (talk) 11:54, 9 February 2019 (UTC)

@Luitzen: wow, thank you! Those are some high-powered queries - I'm amazed at the utility of '+'. Lots to play with :) —Tom.Reding (talk) 15:32, 9 February 2019 (UTC)

Header of Query

Hi!. The following query has the title "zzzother": [1] I would like to be able to provide a custom name for the table, i.e. "Members of the 7th Parliament of the 4th Republic of Ghana". Thanks. —M@sssly^✉ 03:00, 10 February 2019 (UTC)

@Masssly: Not sure if that's possible, but I've got rid of zzzother, which seemed to result from trying to section the tables based on located in the administrative territorial entity (P131), which was never going to work. There is a header_template parameter, but right now that's still above my pay grade ... if I figure out how to use it - and it might be the solution - I'll let you know. --Tagishsimon (talk) 03:20, 10 February 2019 (UTC)

The page title is odd - the table is a blue-list, not a red-list. Not sure what that's all about. --Tagishsimon (talk) 03:23, 10 February 2019 (UTC)

You might also want to be aware of Wikidata:WikiProject every politician, if you've not come across it. I did a complete treatment on Zambia and as much as I could on Malawi, should you want to talk to anyone about getting a complete representation of the 7th P of the 4th R on wikidata. --Tagishsimon (talk) 04:03, 10 February 2019 (UTC)

Hi @Tagishsimon: I was getting really frustrated with that header, thank you so much for your help. I'm planning a series of Editathons to update Wikidata items about Ghanaian MP's. I would be glad to have a complete treatment of representation of the 7th Parliament of the 4th Republic of Ghana on wikidata. Could you help us with that? —M@sssly^✉ 10:01, 10 February 2019 (UTC)

@Masssly: Sure thing. I'll have a look at it in about 12 hours time. I have a CSV of all of the MPs, so its a question of, do we have items for all the constituencies and parties, and then a quick project to add items for MPs we don't have and/or add position statements to items we do have and who are MPS - so, matching names. If I can remember how, I'll set up a Ghana section on Wikidata:WikiProject every politician. To be honest, the biggest risk is that the whole job gets done before your planned editathons - although the CSV I have lacks information on gender, so there will be a job to be done to identify and add that for all records; and I do not have anything on ministerial post-holders, to that will be to do, too. Does that work? It'll be really great to get another African country up & running properly. And of coure, out of it will come a proper redlist of MPs who lack articles. --Tagishsimon (talk) 11:49, 10 February 2019 (UTC)

I do note you have "Editathon at Wikipedia:Madina Institute of Science and Technology (Dates: 2019 February 23, April 27 and May 25)". If you would prefer me not to do most of the work, so that the planned editathon still has work to do, I'm equally happy to hold off - just let me know how you want to play it. --Tagishsimon (talk) 12:00, 10 February 2019 (UTC)

I'm excited at how this is going. I think that there are a lot more statements beside Position held (P39) and their qualifiers, that participants of the events may still get to add. Like you mentioned, gender and ministerial post-holders are potentials candidates. New ideas may come up and so I don't mind if we can get as much of the work done now. So yes, please go ahead. —M@sssly^✉ 12:54, 10 February 2019 (UTC)

Amount of current US congressmen who have a certain major

I would like to have a list of university majors and the amount of congressmen/women that have each major. Thanks in advance ; ) ChristianKl ❪✉❫ 11:47, 9 February 2019 (UTC)

I started doing this query for just any US senators and their academic majors, but those are so sparsely populated that I didn't proceed, the data just doesn't seem to be there.

SELECT ?person ?personLabel ?acaMajorLabel WHERE {
  ?person wdt:P31 wd:Q5.
  ?person wdt:P39 wd:Q13217683.
  ?person p:P69 ?pEdu.
  optional {?pEdu pq:P812 ?acaMajor.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--37.201.30.22 21:53, 10 February 2019 (UTC)

Yeah, the "major" qualifier isn't well-populated at all (partly because it's hard to import, I guess). There are just nine for current Senators/Representatives (below) Andrew Gray (talk) 22:04, 10 February 2019 (UTC)

SELECT distinct ?person ?personLabel ?institutionLabel ?acaMajorLabel WHERE {
  ?person wdt:P31 wd:Q5.
  { ?person p:P39 ?positionStatement . ?positionStatement ps:P39 wd:Q13217683. }
  union
  { ?person p:P39 ?positionStatement . ?positionStatement ps:P39 wd:Q13218630. }
  ?person p:P69 ?pEdu. ?pEdu ps:P69 ?institution . ?pEdu pq:P812 ?acaMajor.
  ?positionStatement pq:P2937 wd:Q28227688 . # current term only - comment out for all
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

What is the "inverse" of wikibase:geoLatitude

If I have a "coordinate value" as a result (node) in a SPARQL query, I can dig further into that node and get stuff like "wikibase:geoLatitude" and "wikibase:geoLongitude". But how do I do the opposite, i.e. how to I "construct" a "value" from variables ?latitude and ?longitude, a "value" that I can BIND to ?coord in order to have it properly displayed in the defaultView Map?

I want to write something like BIND( ?????(?latitude, ?longitude)^^xsd:?????? AS ?coord ) --Larske (talk) 12:31, 11 February 2019 (UTC)

There is mw:Wikibase/Indexing/RDF Dump Format#Globe coordinate which might be helpful. —MisterSynergy (talk) 13:10, 11 February 2019 (UTC)

@Larske:

#defaultView:Map
SELECT * {BIND (STRDT("Point(-0.001475 51.4778111)", geo:wktLiteral) AS ?point)}

Try it!

or

#defaultView:Map
SELECT ?observatory ?point {
  VALUES (?observatory) {(wd:Q192988)}
  ?observatory p:P625/psv:P625 [wikibase:geoLongitude ?longitude; wikibase:geoLatitude ?latitude]
  BIND (
        STRDT(
              CONCAT( 'POINT(', STR(?longitude), ' ', STR(?latitude), ')' ),
              geo:wktLiteral
             )
        AS ?point
       )
}

Try it!

 -- Luitzen (talk) 13:47, 11 February 2019 (UTC)

@MisterSynergy, Luitzen: Thank you very much for the pointer and the examples. What about the geoPrecision and geoGlobe, can they be CONCAT-ed together with geoLongitude and geoLatitude in the first parameter, i.e. POINT(), of the STRDT function to give a value for ?point that can be complete and possible to test for an exact match of all four "point components" to values obtainted by psv:P625? Probably not a good idea due to possible rounding errors, but just out of curiosity...

--Larske (talk) 17:46, 11 February 2019 (UTC)

I do not think that you need a full quadruple entity to compare values. You can just compare against the psv:P625 components. For further reading, there are GeoSPARQL (Q5533918) (with sitelinks) and OGC GeoSPARQL - A Geographic Query Language for RDF Data (Q50233318) (with weblinks) available. From what I saw at a glance, there is no GeoSPARQL data type that holds all four values we use here at Wikidata. Please also mind that the wikibase:geoPrecision component is pretty useless right now, as it definition is poor. IMO it is not even worth to even only look at it. —MisterSynergy (talk) 18:20, 11 February 2019 (UTC)

Items with specific property in the radius from the point

Hey, guys! Can you write me a query to display items with P2788 in a specific km distance from a pre set point? Lets say I will chose a village or town in the Czech republic, than set radius in kilometers and than query will display points in map or settlement names in tab which are in that specific radius. Many thanks.--Juandev (talk) 17:46, 5 February 2019 (UTC)

@Juandev:. Weirdly, as far as I can see, the easting is first and the northing second in the point coordinate. I'm completely used to the northing first, easting second. So watch out for that. This is P2788s within 10km of Kadolec (Q72395). There seem to be lots of them :)

#defaultView:Map
SELECT ?place ?location ?distance ?placeLabel WHERE {
    SERVICE wikibase:around { 
      ?place wdt:P625 ?location . 
      bd:serviceParam wikibase:center "Point(16.141761 49.37474 )"^^geo:wktLiteral .
      bd:serviceParam wikibase:radius "100" . 
      bd:serviceParam wikibase:distance ?distance .
    } 
   ?place wdt:P2788 ?P2788.
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 18:01, 5 February 2019 (UTC)

Cool. Could I have names of settlements in the table with the links to Wikipedia articles? --Juandev (talk) 19:07, 5 February 2019 (UTC)

@Juandev: I've removed the map default display to give you a table, and provided a couple of columns for the article sitelinks ... depending on what you want to do with this, one or other of the columns miht be better for you; the whole thing might be better in a Listeria page, for all I know. Note that this example is selecting EN labels and en.wiki sitelinks - you can tweak that to other languages as you like.

SELECT ?place ?placeLabel ?location ?distance ?sitelink ?article WHERE {
    SERVICE wikibase:around { 
      ?place wdt:P625 ?location . 
      bd:serviceParam wikibase:center "Point(16.141761 49.37474 )"^^geo:wktLiteral .
      bd:serviceParam wikibase:radius "100" . 
      bd:serviceParam wikibase:distance ?distance .
    } 
   ?place wdt:P2788 ?P2788.
   optional { ?article schema:about ?place ;
                       schema:isPartOf <https://en.wikipedia.org/> . 
              ?sitelink ^schema:name ?article .}
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 10:13, 6 February 2019 (UTC)

Cool. I am modified it a little bit and here is the code if someone would like to use it (I dont know, how to save it to example queries:

# Czech settlements in radius of a specific distance from the point
# Česká sídla (vesnice, města) v určité vzdálenosti od bodu
SELECT ?place ?location ?centerpointLoc ?placeLabel ?distance ?article ?sitelink WHERE {
   
  wd:Q2180733 wdt:P625 ?centerpointLoc . # wd stays for point (settlement) from which the radius goes - proměnná za wd je položkou (sídlem) dle Wikidat, od kterého se v učité vzdálenosti vypíší sídla
  SERVICE wikibase:around { 
      ?place wdt:P625 ?location . 
      bd:serviceParam wikibase:center ?centerpointLoc .
      bd:serviceParam wikibase:radius "35" . # set number of a diameter in km - číslo urči kilometrickou vzdálenost od bodu
      bd:serviceParam wikibase:distance ?distance .
    } 
   ?place wdt:P2788 ?P2788.
  optional { ?article schema:about ?place ;
                       schema:isPartOf <https://cs.wikipedia.org/> . 
              ?sitelink ^schema:name ?article .}
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],cs". }
}

Try it!

--Juandev (talk) 21:17, 11 February 2019 (UTC)

Select items that are plants

Given a list of QIDs I need to select those that are associated with the plant taxon (Q756). I imagine this is accomplished by recursively evaluating the items 'parent taxon' property but am unsure how to structure such a query.

 – The preceding unsigned comment was added by RDS0601 (talk • contribs) at 19:24, 11 February 2019‎ (UTC).

@RDS0601:

select * { 
  ?plant wdt:P171+ wd:Q756 .
  hint:Prior hint:gearing "forward" 
  } values (?plant) { (wd:Q145992) (wd:Q47128) }

Try it!

See also https://www.w3.org/TR/sparql11-query/#propertypaths -- Luitzen (talk) 19:49, 11 February 2019 (UTC)

Missing labels

Could you please create the following query?

• missing label in German an/or English
• not an instance of Scholary article (Q13442814)
• at least one Wikipedia article in any language
• output only the 10 lowest item numbers

Thank you!

-- Hundsrose (talk) 07:00, 6 February 2019 (UTC)

No chances. Try Quarry. -- Luitzen (talk) 09:54, 6 February 2019 (UTC)

@Luitzen: Thanks a lot for your fast reply :)

I will dive into quarry now. I thought my request was similar to some I saw on this page. Could you please go more into detail why mine was problematic or not possible? Were there too many conditions? Is there a specific point which causes trouble? -- Hundsrose (talk) 07:12, 8 February 2019 (UTC)

Hey Hundsrose, the problem with your request is that each of the first three criteria involve *really* large amounts of items, i.e. roughly half of Wikidata or so which then would need to be subsetted. It is way too expensive to evaluate this in a query, particularly if you only want to sort it to output just ten items then. Maybe it would help if you could describe a little more in detail what you plan to do.
Regarding de:Wikipedia:Fragen zur Wikipedia#Quarry: Erste Schritte (Tabellen, Performance): Quarry is an interface to query the regular MediaWiki databases using SQL. All wikis typically have the tables listed at mw:Manual:Database layout, and if you consider involving Wikidata information, you might also be interested in mw:Wikibase/Schema. From my experience, the latter option could be relevant if you want to query very large amounts of terms (labels, descriptions, aliases) of Wikidata, as this is apparently much more efficient in SQL than in SPARQL. —MisterSynergy (talk) 20:22, 10 February 2019 (UTC)

@MisterSynergy: Thanks a lot for the answer. This already helps a lot. The idea behind my request was to get more involved in editing wikidata items. I started to insert missing labels and description in german and english with the "Random item" option in Wikidata. However, these items are very often scholary items I'd prefer not to edit and label/description is already inserted. That's why I tried to build my own customized "Random item" for missing labels in my favourite language. Sorting is not really important for me and limiting was meant to limit the query. Any further recommendations? Thanks a lot for the help. Much appreciated and best regards --Hundsrose (talk) 21:57, 11 February 2019 (UTC)

@Hundsrose: A good approach is to try and limit the scope - so rather than "all items except X", try "items in group Y". For example, here's "programming languages, with no label in German, and some number of Wikipedia articles". Andrew Gray (talk) 22:37, 11 February 2019 (UTC)

SELECT ?item (COUNT(DISTINCT ?article) AS ?count) WHERE
{
  ?item wdt:P31 wd:Q9143.                 # item is a programming language
  FILTER(NOT EXISTS { ?item rdfs:label ?lang_label. FILTER(LANG(?lang_label) = "de") })
                                          # no label in German
  ?article schema:about ?item .  FILTER (SUBSTR(str(?article), 11, 15) = ".wikipedia.org/") .
                                          # has some number of sitelinks which are WP articles
} group by ?item order by ?count

Try it!

@Andrew Gray: Thank you very much. This is exactly what I need. I can customize the where-clause and the language also. Thank you all, you pushed me forward. :) Best regards -- Hundsrose (talk) 07:56, 12 February 2019 (UTC)

Integration of MWAPI to parse categories runs into timeout only by de.wikipedia

I tried to create a query to fetch the categories of the corresponding wikipedia-pages from a set of filtered items via SPARQL. I query all railway line (Q728937) in Austria (Q40) and look for the Categories of their Wikipedia-Pages.

If i used en.wikipedia.org it works pretty fine (also for different other endpoints: nl.wikipedia, jp.wikipedia, fr.wikipedia)

SELECT ?cat ?title WHERE {
   ?Eisenbahn wdt:P31 wd:Q728937.
   ?Eisenbahn wdt:P17 wd:Q40.
   ?wikiLink schema:isPartOf <https://fr.wikipedia.org/>;
                      schema:name ?title ;
                      schema:about ?Eisenbahn.
   SERVICE wikibase:mwapi {
     bd:serviceParam wikibase:api "Categories" .
     bd:serviceParam wikibase:endpoint "fr.wikipedia.org" .
     bd:serviceParam mwapi:titles ?title .
     ?cat wikibase:apiOutput mwapi:category .
  }
}
LIMIT 300

Try it!

only running against the German Wikipedia it runs in to a timeout. yesterday it succeed for some times with limitations up to 500, but since yesterday afternoon it won't work anymore...

SELECT ?cat ?title WHERE {
   ?Eisenbahn wdt:P31 wd:Q728937.
   ?Eisenbahn wdt:P17 wd:Q40.
   ?wikiLink schema:isPartOf <https://de.wikipedia.org/>;
                      schema:name ?title ;
                      schema:about ?Eisenbahn.
   SERVICE wikibase:mwapi {
     bd:serviceParam wikibase:api "Categories" .
     bd:serviceParam wikibase:endpoint "de.wikipedia.org" .
     bd:serviceParam mwapi:titles ?title .
     ?cat wikibase:apiOutput mwapi:category .
  }
}
LIMIT 30

Try it!

Thanks for some information. --Mfchris84 (talk) 13:12, 7 February 2019 (UTC)

@Mfchris84: I've asked in the project chat: https://www.wikidata.org/wiki/Wikidata:Project_chat#MWAPI_against_de.wikipedia.org

@Mfchris84: It seems it was fixed. -- Luitzen (talk) 09:01, 12 February 2019 (UTC)

results containing english sitelink

Got a query set up for people whose place of birth is within a country (Australia as an example) and who lack a gender P21] (Australia as an example, borrowed from Tagishsimon)

SELECT ?item ?itemLabel WHERE {
  ?item wdt:P19 ?place.
  ?place wdt:P17 wd:Q408.
  ?item wdt:P31 wd:Q5.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  FILTER(NOT EXISTS { ?item wdt:P21 _:b1. })
}

Try it!

Is there any way to include only the results that have a sitelink to english wikipedia? Thanks! Nat965 (talk) 09:00, 12 February 2019 (UTC)

@Nat965: Sure:

SELECT ?item ?itemLabel WHERE {
  ?item wdt:P19 ?place.
  ?place wdt:P17 wd:Q408.
  ?item wdt:P31 wd:Q5.
  ?article schema:about ?item ;
          schema:isPartOf <https://en.wikipedia.org/> .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  FILTER(NOT EXISTS { ?item wdt:P21 _:b1. })
}

Try it!

and you can bung ?article in the select if you want to see it, or add another line ?sitelink ^schema:name ?article . to look at the sitelink. iirc one is the short name, the other a full URL; I forget which is which. --Tagishsimon (talk) 09:23, 12 February 2019 (UTC)

Also, welcome to property paths - the first two lines of the query can be collapsed:

SELECT ?item ?itemLabel WHERE {
  ?item wdt:P19/wdt:P131* wd:Q408.
  ?item wdt:P31 wd:Q5.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  FILTER(NOT EXISTS { ?item wdt:P21 _:b1. })
}

Try it!

--Tagishsimon (talk) 14:03, 12 February 2019 (UTC)

Get family and order of mammal taxa missing in a given language

Hello! I'm trying to get the family and order of any mammal taxa that is not in a given language.

SELECT DISTINCT ?item ?taxonname ?overLabel WHERE {
  ?item wdt:P31 wd:Q16521.
  ?item wdt:P105 wd:Q7432.
  ?item wdt:P171* wd:Q7377.
  ?item wdt:P225 ?taxonname.
  ?item wdt:P171/wdt:P171 ?over.
  ?article schema:about ?item .
  FILTER NOT EXISTS { ?wen schema:about ?item ; schema:inLanguage "en" }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

How could I get the family and order without using a very weird wdt:P171/wdt:P171 system? -Theklan (talk) 18:31, 2 February 2019 (UTC)

By using wildcards like this:

SELECT DISTINCT ?item ?taxonname ?familyLabel ?orderLabel WHERE {
  ?item wdt:P31 wd:Q16521 .
  ?item wdt:P105 wd:Q7432 .
  ?item wdt:P225 ?taxonname .
  ?item wdt:P171+ wd:Q7377 .
  ?item wdt:P171+ ?family .
  ?family wdt:P105 wd:Q35409 .
  ?family wdt:P171+ ?order .
  ?order wdt:P105 wd:Q36602 .
  FILTER NOT EXISTS { ?wen schema:about ?item ; schema:inLanguage "en" } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}

Try it!

Matěj Suchánek (talk) 08:25, 6 February 2019 (UTC)

Interestingly, it doesn't show distinct items if they have more than one possible orders. -Theklan (talk) 09:55, 13 February 2019 (UTC)

Video game genres and OLAC catalogue

Hi,

I want to make a graph of video game genre (Q659563) and outline the ones that are linked to OLAC Video Game Genre Vocabulary (Q60440967) via OLAC video game genre vocabulary ID (P6352)

I got as far as the following (as well as many many variations) but I don’t seem to be able to get the colours properly… Any help appreciated! Jean-Fred (talk) 17:07, 13 February 2019 (UTC)

SELECT DISTINCT ?genre ?genreLabel ?OLAC ?_subclass_of ?_subclass_ofLabel ?rgb WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?genre wdt:P31 wd:Q659563.
  ?genre wdt:P279 ?_subclass_of.
  #?_subclass_of wdt:P31 wd:Q659563. 
  OPTIONAL { ?genre wdt:P6352 ?OLAC. }
  #OPTIONAL { ?_subclass_of wdt:P6352 ?OLAC. }
  BIND(IF(BOUND(?OLAC),"FFA500","7FFF00") AS ?rgb).
}

Try it!

@Jean-Frédéric: Perhaps you need something like this:

#defaultView:Graph
SELECT DISTINCT ?genre ?genreLabel ?rgb ?OLAC ?_subclass_of ?_subclass_ofLabel {
  ?genre wdt:P31 wd:Q659563.
  OPTIONAL { ?genre wdt:P279 ?_subclass_of. ?_subclass_of wdt:P31 wd:Q659563. }
  OPTIONAL { ?genre wdt:P6352 ?OLAC. }
  BIND(IF(BOUND(?OLAC),"FFA500","7FFF00") AS ?rgb)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

  --Luitzen (talk) 13:02, 14 February 2019 (UTC)

@Luitzen: Thanks, that makes sense! I added one band-aid so that the central node video game (Q7889) would be added to make

#defaultView:Graph
SELECT DISTINCT ?genre ?genreLabel ?rgb ?OLAC ?_subclass_of ?_subclass_ofLabel WHERE {
  ?genre wdt:P31 wd:Q659563.
  VALUES ?gametype { wd:Q659563 wd:Q2249149 }
  OPTIONAL {
    ?genre wdt:P279 ?_subclass_of.
    { ?_subclass_of wdt:P31 wd:Q659563. } UNION {?_subclass_of wdt:P279 wd:Q2249149.}
  }

  OPTIONAL { ?genre wdt:P6352 ?OLAC. }
  BIND(IF(BOUND(?OLAC), "FFA500", "7FFF00") AS ?rgb)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Thanks! Jean-Fred (talk) 13:52, 14 February 2019 (UTC)

Authors who contributed papers to a specific journal

Can someone show me how to create a query to identify people who contributed scholarly article (Q13442814) to Costume (Q35387261) without including all of the individual articles in the query results? I'd like a Listeria list with EN label, EN description, birth/death dates, and field of work. Thanks! - PKM (talk) 00:16, 14 February 2019 (UTC)

@PKM: This is a game of two halves; we need a query to find the items, then a Listeria report to flesh out the details you're after. The query is:

SELECT ?item 
WHERE 
{
  ?article wdt:P1433 wd:Q35387261. # article was published in costume
  ?article wdt:P50 ?item . # here's an author
}

Try it!

and the example Listeria report is at User:Tagishsimon/junk2. The two important things to note are 1) Listeria is driven by whatever you select as the variable ?item - so in this case we make the author the ?item and 2) Listeria's column parameter is the place to set most of your desired columns - they don't need to be referenced in the query if they're a simple property - so in this case, |columns=number:#,label,description,P569:DoB,P570:DoD,P101:field,item:wikidata item is what's wanted. --Tagishsimon (talk) 00:29, 14 February 2019 (UTC)

@Tagishsimon: thanks, that's perfect! I've copied the report over to one of my user pages. - PKM (talk) 00:59, 14 February 2019 (UTC)

@Tagishsimon, PKM: Just a comment about authors. Not all authors are given as property author (P50) of the article object since they don't have a Wikidata object. Some are given as author name string (P2093) and have none of the wanted properties in Wikidata. They may nevertheless be of interest to be listed by their names.

SELECT DISTINCT ?item 
WHERE 
{
  ?article wdt:P1433 wd:Q35387261. # article was published in costume
  ?article wdt:P2093 ?item . # here's an author (without a Wikidata object)
}
ORDER BY ?item

Try it!

This query gives 397 distinct author names. The query with P50 only gives 76 distinct author objects.

Maybe it is possible to combine these two queries into one to get also the authors without Wikidata object as rows, containing only the author names, in the Listeria.

--Larske (talk) 01:22, 14 February 2019 (UTC)

@Larske: Thanks. I am actually working on the items with "author name string" through Scholia - the remaining "author name string" authors have only 1 or 2 contributions and are a bit harder to track down. I've matched all of the authors with 3 or more contributions. - PKM (talk) 01:33, 14 February 2019 (UTC)

Still, it's instructive to look at how the two might be combined. It's a bit of a pest, tbh. The SPARQL is something like this:

SELECT ?item ?author ?authorQID ?dob ?dod ?field ?description with 
{ 
  select (sample(?it) as ?item) ?author ?description (sample(?aut3) as ?authorQID) (group_concat(distinct ?fiel;separator=", ") as ?field) ?dob ?dod
WHERE 
{
  ?it wdt:P1433 wd:Q35387261. # article was published in costume
  ?it wdt:P50 ?aut . # here's an author
  bind(strafter(str(?aut),"http://www.wikidata.org/entity/") as ?aut2)
  BIND (CONCAT ("[http://www.wikidata.org/entity/", ?aut2, " ", ?aut2,"]") as ?aut3) .
  optional {?aut wdt:P569 ?dob .}
  optional {?aut wdt:P570 ?dod .}
  optional {?aut wdt:P101 ?fi . ?fi rdfs:label ?fiel . filter(lang(?fiel)="en")} 
  optional {?aut rdfs:label ?author .  filter(lang(?author)="en")} 
  optional {?aut schema:description ?description .  filter(lang(?description)="en")} 
} group by ?author ?dob ?dod ?description
} as %i with 
{ 
  select (sample(?it) as ?item) ?author ?authorQID ?field ?dob ?dod ?description WHERE 
  {
    ?it wdt:P1433 wd:Q35387261. # article was published in costume
    ?it wdt:P2093 ?author . # here's an author (without a Wikidata object)
  } group by ?author ?authorQID ?field ?dob ?dod ?description
} as %j
where
{
{  include %i}
          UNION
{  include %j }
}

Try it!

and the Listeria is something like User:Tagishsimon/junk2 - and even then, we lose the blue & redlinks to wikipedia articles ... we might get them back with even more tinkering, but I'm not inclined to do much more on it. --Tagishsimon (talk) 02:24, 14 February 2019 (UTC)

Perhaps this is better:

SELECT ?item ?author ?authorQID ?dob ?dod ?field ?description with 
{ 
  SELECT ?item ?author ?description
WHERE 
{
  ?article wdt:P1433 wd:Q35387261. # article was published in costume
  ?article wdt:P50 ?item . # here's an author
  optional {?item rdfs:label ?author .  filter(lang(?author)="en")} 
  optional {?item schema:description ?description .  filter(lang(?description)="en")} 

}
} as %i with 
{ 
  select (sample(?it) as ?item) ?author ?description WHERE 
  {
    ?it wdt:P1433 wd:Q35387261. # article was published in costume
    ?it wdt:P2093 ?author . # here's an author (without a Wikidata object)
  } group by ?author ?description
} as %j
where
{
{  include %i}
          UNION
{  include %j }
}

Try it!

and User:Tagishsimon/junk2 for the Listeria. --Tagishsimon (talk) 07:30, 14 February 2019 (UTC)

At least that mostly pointless exercise led me to three items for obituaries in which we mistakenly listed the deceased as authors; now fixed. --Tagishsimon (talk) 08:03, 14 February 2019 (UTC)

Display date of birth

I have made a query to find catalan centenarians alive in ca.wikipedia.org:

SELECT ?s ?desc WHERE {
  ?s wdt:P31 wd:Q5.
  ?s wdt:P569 ?birth.
  ?s wdt:P19 ?place.
  ?place (wdt:P131*/wdt:P706*) wd:Q5705.
  MINUS { ?s wdt:P570 _:b5. }
  ?s rdfs:label ?desc.
  ?article schema:about ?s.
  ?article schema:inLanguage "ca".
  FILTER((?birth > "1898-01-01"^^xsd:dateTime) && (?birth < "1918-01-01"^^xsd:dateTime))
  FILTER((LANG(?desc)) = "ca")
}
GROUP BY ?s ?desc
LIMIT 100

Try it!

Could somebody help me displaying on a third column their date of birth?

Thanks in advance! Paucabot (talk) 21:19, 14 February 2019 (UTC)

@Paucabot: Not quite sure where you're going with this, Paucabot. There's a lot of cruft in your query, such as ?place and ?article, and the group statement. Without starting surgery on any of that, a minimal thing to do is as shown below. Note, however, that the query as it stands is not concerning itself with date precision. Dates in wikidata can be stored to day precision, or to month, year, decade, etc. Where a person's DoB is specified only to the year, you'll get a 1 January date, which can be misleading.

SELECT ?s ?desc ?birth WHERE {
  ?s wdt:P31 wd:Q5.
  ?s wdt:P569 ?birth.
  ?s wdt:P19 ?place.
  ?place (wdt:P131*/wdt:P706*) wd:Q5705.
  MINUS { ?s wdt:P570 _:b5. }
  ?s rdfs:label ?desc.
  ?article schema:about ?s.
  ?article schema:inLanguage "ca".
  FILTER((?birth > "1898-01-01"^^xsd:dateTime) && (?birth < "1918-01-01"^^xsd:dateTime))
  FILTER((LANG(?desc)) = "ca")
}
GROUP BY ?s ?desc ?birth 
LIMIT 100

Try it!

If you want to deal with the precision issue, then something like this is the way to go (albeit even this only sorts things out to precision=year, and will get precisions of decade, century or millenium wrong)

SELECT ?s ?desc ?dob WHERE {
  ?s wdt:P31 wd:Q5.
#  ?s wdt:P569 ?birth.
  ?s  p:P569/psv:P569 [wikibase:timePrecision ?precision; wikibase:timeValue ?birth]
  bind(if(?precision=11,?birth,if(?precision=10,concat(month(?birth)," ",year(?birth)),year(?birth))) as ?dob)                
  ?s wdt:P19 ?place.
  ?place (wdt:P131*/wdt:P706*) wd:Q5705.
  MINUS { ?s wdt:P570 _:b5. }
  ?s rdfs:label ?desc.
  ?article schema:about ?s.
  ?article schema:inLanguage "ca".
  FILTER((?birth > "1898-01-01"^^xsd:dateTime) && (?birth < "1918-01-01"^^xsd:dateTime))
  FILTER((LANG(?desc)) = "ca")
}
GROUP BY ?s ?desc ?dob 
LIMIT 100

Try it!

--Tagishsimon (talk) 22:38, 14 February 2019 (UTC)

Thanks, Tagishsimon. The query is crufty simply because I don't know a lot of SPARQL and I make queries using parts of other queries (Frankenstein-style). Furthermore, my original query had no precision time handling because simply I didn't know how to do it. Thanks again. Paucabot (talk) 22:48, 14 February 2019 (UTC)

@Paucabot: Yup, me too - the Frankenstein thing. A very good way to learn. I just didn't want to de-cruft it in case you had plans. Examining the cruft more carefully reveals most of it to be useful; the requirement 'must have a Catalan label' /might/ exclude some otherwise valid oldsters, and the group by is not doing anything.

SELECT ?s ?desc ?dob WHERE {
  ?s wdt:P31 wd:Q5.                           # must be human
  ?s  p:P569/psv:P569 [wikibase:timePrecision ?precision; wikibase:timeValue ?birth]
  bind(if(?precision=11,?birth,if(?precision=10,concat(month(?birth)," ",year(?birth)),year(?birth))) as ?dob)                
  ?s wdt:P19 ?place.                         # must have a place of birth
  ?place (wdt:P131*/wdt:P706*) wd:Q5705.     # place of birth must be located within or on Catelonia
  MINUS { ?s wdt:P570 _:b5. }                # must be alive - no date of death
  ?s rdfs:label ?desc.                       # must have a label ...
  FILTER((LANG(?desc)) = "ca")               # label must be in Catalan
  ?article schema:about ?s.                  # must have a sitelink
  ?article schema:inLanguage "ca".           # sitelink must be (by inference) to the Catalan wikipedia
  FILTER((?birth > "1898-01-01"^^xsd:dateTime) && (?birth < "1918-01-01"^^xsd:dateTime))
}
GROUP BY ?s ?desc ?dob                       # right now the group by serves no purpose whatsoever - there are to aggregates in the select
LIMIT 100                                    # obvs, only give me 100 results

Try it!

--Tagishsimon (talk) 23:05, 14 February 2019 (UTC)

@Paucabot, Tagishsimon: The issue with time values stated with precision less than 9 (year) that you mention is quite a big problem if you ask me. There does not seem to be a common understanding on how to use and how interprete them and the Wikidata user interface is still confusing in many languages.

See Help_talk:Dates#Clarifying_“century”-_and_“millennium”-precision_dates where there is a discussion on "logic versus established norms". My conclusion is that you should be very careful and not thrust time values in Wikidata stated with less than year precision without investigating them.

In the following query, a modification of the above query, I have choosen to include, in the FILTER clause, also objects where date of birth (P569) is given with a precision less than 9, as they will have to be manually investigated.

SELECT ?s ?desc ?dob ?dobvalue WHERE {
  ?s wdt:P31 wd:Q5.                           # must be human
  ?s  p:P569/psv:P569 [wikibase:timePrecision ?precision; wikibase:timeValue ?birth] .
  ?s  p:P569/ps:P569 ?dobvalue .
  bind(if(?precision=11,?birth,if(?precision=10,concat(month(?birth)," ",year(?birth)),if(?precision<9,concat(STR(?birth),' precision=',STR(?precision)),year(?birth)))) as ?dob)                
  ?s wdt:P19 ?place.                         # must have a place of birth
  ?place (wdt:P131*/wdt:P706*) wd:Q5705.     # place of birth must be located within or on Catelonia
  MINUS { ?s wdt:P570 _:b5. }                # must be alive - no date of death
  ?s rdfs:label ?desc.                       # must have a label ...
  FILTER((LANG(?desc)) = "ca")               # label must be in Catalan
  ?article schema:about ?s.                  # must have a sitelink
  ?article schema:inLanguage "ca".           # sitelink must be (by inference) to the Catalan wikipedia
  FILTER((?birth > "1898-01-01"^^xsd:dateTime) && (?birth < "1918-01-01"^^xsd:dateTime)|| ?precision<9 )
}
 GROUP BY ?s ?desc ?dob ?dobvalue            # right now the group by serves no purpose whatsoever - there are to aggregates in the select
ORDER BY ?dobvalue
# LIMIT 100                                    # obvs, only give me 100 results

Try it!

Just an example to show the problem. Time values "1900-01-01T00:00:00Z" is displayed as "19. century" on the item page (English) whereas "1901-01-01T00:00:00Z" is displayed as "20. century". In the result of the above query you will find several examples of both.

A date of birth (P569) with the value ("1901-01-01T00:00:00Z"), and precision=7, has been used for both Miquel Curet i Roure (Q20005978) (see diff) and Teresa Font (Q21075432) (see diff), but from the Wikipedia articles it seems like the first of them was active around 1880 whereas the second of them was active from around 1976. They are definitely, despite having the same value for date of birth (P569) in Wikidata, not born in the same "century".

--Larske (talk) 08:58, 15 February 2019 (UTC)

Title or label

Hello. I want to find all items that have:

1) at the title of the article to elwiki the word ΠΓΔΜ

or

2) at the el label of the item the word ΠΓΔΜ.

Xaris333 (talk) 16:23, 15 February 2019 (UTC)

HTH: https://mediawiki.org/wiki/Wikidata_Query_Service/User_Manual/MWAPI#Examples -- Luitzen (talk) 16:57, 16 February 2019 (UTC)

need help with a query

I am trying to get a query with properties of items that have sitelings pointing to pages on c:Category:Commons_licensing_help_by_country. I come up with :

SELECT ?item ?itemLabel ?instanceLabel ?countryLabel ?jurisdictionLabel ?subjectLabel  {
  SERVICE wikibase:mwapi {
	 bd:serviceParam wikibase:api "Generator" .
     bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
     bd:serviceParam mwapi:gcmtitle "Category:Commons_licensing_help_by_country" .
     bd:serviceParam mwapi:generator "categorymembers" .
     bd:serviceParam mwapi:gcmtype "page" .
     bd:serviceParam mwapi:gcmlimit "max" .
     bd:serviceParam mwapi:gcmsort "timestamp" .
     bd:serviceParam mwapi:gcmdir "descending" .
     ?item wikibase:apiOutput mwapi:title  .
  }
  #?commonsSitelink schema:about ?item; schema:isPartOf <https://commons.wikimedia.org/>; schema:name ?sitelink_label .
  Optional { ?item wdt:P31 ?instance. }
  Optional { ?item wdt:P17 ?country. }
  Optional { ?item wdt:P1001 ?jurisdiction. }
  Optional { ?item wdt:P921 ?subject. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}

Try it!

But did not get it to work yet. can someone help? --Jarekt (talk) 19:52, 14 February 2019 (UTC)

I can get close, but no cigar. First, consider the query:

SELECT ?sitelink ?article ?item where {
  values ?sitelink  {"Commons:Copyright rules by territory/Armenia"@en}
  ?sitelink ^schema:name ?article .
  ?article schema:about ?item ;
           schema:isPartOf <https://commons.wikimedia.org/> .
}

Try it!

Clearly that's working; we're finding the ?item starting from a ?sitelink string. But applying that approach in your query still fails for me. Consider a second query, which seems to suggest that I'm generating a ?sitelink akin to that in the above query

SELECT ?commonsTitle ?sitelink ?item ?itemLabel ?instanceLabel ?countryLabel ?jurisdictionLabel ?subjectLabel  {
  SERVICE wikibase:mwapi {
	 bd:serviceParam wikibase:api "Generator" .
     bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
     bd:serviceParam mwapi:gcmtitle "Category:Commons_licensing_help_by_country" .
     bd:serviceParam mwapi:generator "categorymembers" .
     bd:serviceParam mwapi:gcmtype "page" .
     bd:serviceParam mwapi:gcmlimit "max" .
     bd:serviceParam mwapi:gcmsort "timestamp" .
     bd:serviceParam mwapi:gcmdir "descending" .
     ?commonsTitle wikibase:apiOutput mwapi:title  .
  }
  hint:Query hint:optimizer "None" .
  bind(concat("\"",?commonsTitle,"\"","@en") as ?sitelink)
#  ?sitelink ^schema:name ?article . 
#  ?article schema:about ?item ;
#           schema:isPartOf <https://commons.wikimedia.org/> .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}

Try it!

But enable the commented-out rows, and nothing's found. So, sorry about that. I join the queue in wanting to know where it all went wrong. --Tagishsimon (talk) 22:25, 14 February 2019 (UTC)

The query

SELECT ?sitelink ?item ?itemLabel ?instanceLabel ?countryLabel ?jurisdictionLabel ?subjectLabel where {
  FILTER(STRSTARTS(STR(?sitelink), "Commons:Copyright rules by territory/")) .
  ?sitelink ^schema:name ?article .
  ?article schema:about ?item ;
           schema:isPartOf <https://commons.wikimedia.org/> .
  Optional { ?item wdt:P31 ?instance. }
  Optional { ?item wdt:P17 ?country. }
  Optional { ?item wdt:P1001 ?jurisdiction. }
  Optional { ?item wdt:P921 ?subject. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}

Try it!

 has the same final results. --Jarekt (talk) 04:21, 15 February 2019 (UTC)

@Jarekt:

select ?item ?itemLabel ?instanceLabel ?jurisdictionLabel ?subjectLabel  {
  service wikibase:mwapi {
	 bd:serviceParam wikibase:api "Generator" .
     bd:serviceParam wikibase:endpoint "commons.wikimedia.org" .
     bd:serviceParam mwapi:gcmtitle "Category:Commons_licensing_help_by_country" .
     bd:serviceParam mwapi:generator "categorymembers" .
     bd:serviceParam mwapi:gcmtype "page" .
     bd:serviceParam mwapi:gcmlimit "max" .
     bd:serviceParam mwapi:gcmsort "timestamp" .
     bd:serviceParam mwapi:gcmdir "descending" .
     ?item wikibase:apiOutputItem mwapi:item  .
  }
  filter(bound(?item)) .
  ?commonsSitelink schema:about ?item;
                   schema:isPartOf <https://commons.wikimedia.org/>;
                   schema:name ?sitelink_label .
  optional { ?item wdt:P31 ?instance. }
  optional { ?item wdt:P17 ?country. }
  optional { ?item wdt:P1001 ?jurisdiction. }
  optional { ?item wdt:P921 ?subject. }
  service wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
} -- [[User:Luitzen|Luitzen]] ([[User talk:Luitzen|<span class="signature-talk">talk</span>]]) 12:46, 17 February 2019 (UTC)

Try it!

Get names and license of all Linux media players

I can't wrap my head around how to get all players in "Linux media players" category (https://www.wikidata.org/wiki/Q8591688) and show their name and license. But this gets the data from `dbpedia`, not `wikidata`, and some players like https://en.wikipedia.org/wiki/MusikCube are missing from the output.

PREFIX dbc: <http://dbpedia.org/resource/Category:>
PREFIX dct: <http://purl.org/dc/terms/>
PREFIX owl: <http://www.w3.org/2002/07/owl#>

PREFIX dbo: <http://dbpedia.org/ontology/>
PREFIX dbp: <http://dbpedia.org/property/>

SELECT ?wsubjectLabel ?licenseName WHERE {
  SERVICE <http://dbpedia.org/sparql> {
    ?subject dct:subject dbc:Linux_media_players.
    ?subject owl:sameAs ?wsubject.
    ?subject dbo:license ?license.
    ?license dbp:name ?licenseName.
    FILTER(STRSTARTS(STR(?wsubject), "http://www.wikidata.org"))
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Query

--Techtonik (talk) 01:03, 17 February 2019 (UTC)

@Techtonik: Like this? —

prefix encat: <https://en.wikipedia.org/wiki/Category:>
select distinct ?itemLabel ?licenseLabel {
  service <https://query.wikidata.org/bigdata/namespace/categories/sparql> {
    ?s mediawiki:isInCategory* encat:Linux_media_players ;
       rdfs:label ?label .
  }
  bind (concat("Category:", ?label) as ?title)
  service wikibase:mwapi {
     bd:serviceParam wikibase:api "Generator" .
     bd:serviceParam wikibase:endpoint "en.wikipedia.org" .
     bd:serviceParam mwapi:gcmtitle ?title .
     bd:serviceParam mwapi:generator "categorymembers" .
     bd:serviceParam mwapi:gcmprop "ids" .
     bd:serviceParam mwapi:gcmlimit "max" .
     ?ns wikibase:apiOutput "@ns" .
     ?item wikibase:apiOutputItem mwapi:item .
  }
  filter(bound(?item) && ?ns = "0")
  optional {?item wdt:P275 ?license} 
  service wikibase:label {bd:serviceParam wikibase:language "en"}
}

Try it!

 -- Luitzen (talk) 12:34, 17 February 2019 (UTC)

Video games with a Steam Application ID imported from PCGamingWiki

How would I go about creating a query that can find all the items with a Steam application ID (P1733) and the "reference" for that property set to imported from Wikimedia project (P143) PCGamingWiki (Q17013880)?

For example, this item has a Steam Application ID with this reference: Terminal Velocity (Q955289). There are at least a dozen others I've seen, but I didn't keep track of what items had it, so this is the only one I'm able to link to as an example.

The reason I want this is because I'd like to find all the items with data imported from PCGamingWiki that don't have a PCGamingWiki ID (P6337), as that'd be an easy way to find holes in our data. Also I'm just curious :D

Thanks for the help! Nicereddy (talk) 20:01, 17 February 2019 (UTC)

@Nicereddy: This, I think

SELECT ?item ?itemLabel 
WHERE 
{
  ?item p:P1733 [ ps:P1733 ?steam_ID ; prov:wasDerivedFrom [pr:P143 wd:Q17013880 ] ].
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?itemLabel

Try it!

--Tagishsimon (talk) 20:06, 17 February 2019 (UTC)

Awesome, that looks right to me. Thanks! Nicereddy (talk) 20:08, 17 February 2019 (UTC)

@Nicereddy: This may also help - answers the question "I'd like to find all the items with data imported from PCGamingWiki that don't have a PCGamingWiki ID (P6337)"

SELECT ?item ?itemLabel 
WHERE 
{
  ?item p:P1733 [ ps:P1733 ?steam_ID ; prov:wasDerivedFrom [pr:P143 wd:Q17013880 ] ].
  filter not exists {?item wdt:P6337 [] . } 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by ?itemLabel

Try it!

--Tagishsimon (talk) 20:09, 17 February 2019 (UTC)

Books that have a title in a given language but not a label in that language

I don't understand why this doesn't work:

SELECT ?item ?lang ?title WHERE {
?item wdt:P31/wdt:P279* wd:Q571 ; # Q571 for book
      wdt:P1476 ?title .
  BIND(LANG(?title) as ?lang).
MINUS{?item rdfs:label ?itemLabel FILTER (?lang = lang(?itemLabel))}
}

Try it!

The MINUS clause here seems to have no effect. Yet when I try this with a given language (filter for LANG(?title)="en" and also for LANG(?itemLabel)="en") it works, and only gets books with an English title but no English label. MartinPoulter (talk) 23:49, 18 February 2019 (UTC)

PS Now fixed by replacing MINUS{} with FILTER {NOT EXISTS{}}. I must not understand how MINUS works.

SELECT ?item ?lang ?title WHERE {
?item wdt:P31/wdt:P279* wd:Q571 ; # Q571 for book
      wdt:P1476 ?title .
  BIND(LANG(?title) as ?lang) FILTER (?lang != "und")
FILTER (NOT EXISTS{ ?item rdfs:label ?itemLabel. FILTER(lang(?itemLabel) = ?lang) })
}

Try it!

MartinPoulter (talk) 23:58, 18 February 2019 (UTC)

Something a little like this was discussed recently at https://twitter.com/generalising/status/1091645162575532032 ... I think you do understand minus, but there's perhaps a gotcha lurking in binding versus braces that we all don't fully understand. --Tagishsimon (talk) 00:32, 19 February 2019 (UTC)

@MartinPoulter: Your ?lang is not "visible" inside MINUS https://www.w3.org/TR/sparql11-query/#idp899488 -- Luitzen (talk) 06:53, 19 February 2019 (UTC)

Query on Lexemes needing fixing

Hi,

I wrote this query to have the number of words (representation of forms) in English sorted by number:

SELECT ?firstLetter (COUNT(?form) AS ?count ) WHERE {
  ?lexeme a ontolex:LexicalEntry ; dct:language ?language ; ontolex:lexicalForm ?form .
  ?form ontolex:representation ?word .
  ?language wdt:P218 'en' .
  BIND ( SUBSTR(UCASE(?word), 1, 1) AS ?firstLetter )
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?firstLetter
ORDER BY DESC(?count)

Try it!

But something has gone wrong, the same first letter appears multiple times when the shouldn't. There is a row « M 1779 » and « M 47 » (and more at the bottom) when M is the same letter… (I've seen there is also an other problem like homographic letters, for instance machy (L13058) where a form starts with the letter Μ "mu" in Greek, but still there is multiple time the same letter).

Could someone look into it and tell me what is wrong and how to correct it?

Cheers, VIGNERON (talk) 14:36, 19 February 2019 (UTC)

@VIGNERON:SUBSTR preserves language tags. The first "M" is "M"@en; the second "M" is "M"@en-gb. Write STR(?word). -- Luitzen (talk) 17:06, 19 February 2019 (UTC)

Thank you a lot Luitzen, so simple and yet so hard to find. Cheers, VIGNERON (talk) 17:52, 19 February 2019 (UTC)

Query a company based on its official website

Hi, I would like to query Wikidata for a company's details with its official website or domain.

For instance, if I were to pass google.com, it should return details about Google Inc.

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

Hi,

Here a very simple and crude way to do it:

SELECT * WHERE {
  ?item wdt:P856 <https://www.google.com> .
}

Try it!

Cdlt, VIGNERON (talk) 16:24, 19 February 2019 (UTC)

Enhanced query to get details like ISIN, Stock Exchange, Ticker and Other names for a given company.

SELECT distinct
  ?official_name
  (GROUP_CONCAT( DISTINCT ?isin; separator=";") AS ?isin)
  ?item
  ?itemLabel
  ?stock_exchange
  ?stock_exchangeLabel
  (GROUP_CONCAT( DISTINCT ?Ticker; separator=";") AS ?Ticker)
  (GROUP_CONCAT( DISTINCT ?other_name; separator=";") AS ?other_name)
WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item (wdt:P31/wdt:P279*) wd:Q783794.
  {
            { ?item wdt:P856 <https://www.google.com> }
      UNION { ?item wdt:P856 <http://www.google.com> }   
  }
  OPTIONAL { ?item wdt:P1448 ?official_name. }
  OPTIONAL { ?item wdt:P946 ?isin. }
  OPTIONAL { ?item wdt:P414 ?stock_exchange. }
  OPTIONAL { ?item skos:altLabel ?other_name. FILTER (LANG (?other_name) = "en") }
  ?SE pq:P249 ?Ticker
}
GROUP BY ?official_name ?item ?stock_exchange ?itemLabel ?stock_exchangeLabel

Try it!

Isolate attributes starting with a list of 1200 names

Hi there,

For a datavizualisation on the subject of famous people whom obtnained their french nationality after they where born, i wish to fetch a certain quantity of data, for 1200 entries : name, occupation, date of birth, country of birth, image, biography, etc. Is it possible to query this ? I don't get much of SPARQL if someone could enlight me on the process.

Thanks dear Wikipedians.

@Nesego: voici la liste des personnes dont la nationalité française comporte une date de début. Il n'y a que 293 personnes pour le moment.

SELECT DISTINCT ?item ?itemLabel WHERE {
  ?item wdt:P27 wd:Q142 .
  ?item p:P27 [ ps:P27 wd:Q142 ; pq:P580 ?dateDebut ] .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr" } .
}

Try it!

Cordialement, VIGNERON (talk) 15:01, 19 February 2019 (UTC)

Great people! —

#defaultView:ImageGrid{"hide": "?image"}
SELECT DISTINCT ?item ?itemLabel ?image {
  ?item wdt:P27 wd:Q142 .
  ?item wdt:P19 [] .
  FILTER NOT EXISTS {
    ?item wdt:P19 ?place .
    ?place wdt:P131*  wd:Q142 .
    hint:Prior hint:gearing "forward"
  }
  ?item wdt:P18 ?image .
  ?item wikibase:sitelinks ?sitelinks .
  ?item wikibase:statements ?statements .
  SERVICE wikibase:label {bd:serviceParam wikibase:language "fr"}
} ORDER BY DESC(?sitelinks) DESC(?statements) LIMIT 1500

Try it!

  I hope the rest of the query is trivial :). -- Luitzen (talk) 23:38, 19 February 2019 (UTC)

Female species authorities / missing species authorities

Hi there,
I would like to have a list of animal species with female species authorities. Tis would mean:

• P31= Q16521
• P171= species Tree below Q151542
• Qualifier P405 for P225 = Item with P21 = Q6581072

Additionally I would like to generate a working list of mammals (or other taxa) with missing Qualifier P405 for P225.

Someone here who would be able to do this? -- Achim Raschka (talk) 09:45, 20 February 2019 (UTC)

@Achim Raschka:

First something is strange with the data, there is only 7 taxon (Q16521) under Metazoa (Q151542). Then, if I'm using animal (Q729) (which I'm guessing if more or less what your looking for, but I'm not an expert in biology taxonomy) is too big and timeout without giving results :( So I'm using Tetrapoda (Q19159) instead (the highest taxon I found that doesn't timeout; maybe someone else can find a way to revrite my query to be less expensive?).

SELECT ?species ?speciesLabel ?authorLabel WHERE {
  ?species p:P225 [ ps:P225 [] ; pq:P405 ?author ] .
  ?author wdt:P21 wd:Q6581072 .
  ?species wdt:P31 wd:Q16521 ; wdt:P171* wd:Q19159 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

And for you second request :

SELECT ?species ?speciesLabel WHERE {
  ?species wdt:P31 wd:Q16521 ; wdt:P171* wd:Q7377 .
  MINUS { ?species p:P225 [ ps:P225 [] ; pq:P405 ?author ] . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Does it answer your questions? Cheers, VIGNERON (talk) 11:18, 20 February 2019 (UTC)

Hi @VIGNERON:

thanks, this work exactly how I wanted to have it. Concerning taxon (Q16521) and Metazoa (Q151542) I try to find out what the problem is. All the best -- Achim Raschka (talk) 12:33, 20 February 2019 (UTC)

Categorías

Buenos días

 Necesito orientación en el tema de crear categorías. He creado Imprenta en Navarra, intento crear Impresores de Navarra, similar a Impresores de España. Y no lo consigo. No se si por que no hay una entrada con artículo en la wiki o por que no lo sé hacer. 

Además he creado en wikidata autoridades de nombre "Matías Mares" "Carlos de Labayen" y "Jerónimo Anchuela" pero creo que no están bien. Alguien puede ponerse en contacto conmigo para echarme una mano. Gracias  – The preceding unsigned comment was added by Lozano Manzanedo (talk • contribs) at 11:01, 19 February 2019‎ (UTC).

@Lozano Manzanedo: Esta es una página donde se pueden solicitar consultas SPARQL. -- Luitzen (talk) 22:00, 20 February 2019 (UTC)

If I have a list of DOI (P356), how can I make a list of QIDs?

I have a list of DOIs in excel. What I need is a list of QIDs so I can generate a batch for Quick Statement. I don't want to search for each DOI so I can assemble the list as I've got over 150 DOIs to work on. Can I build a query that gives me QID for a list of DOI? I can figure out how to turn the list into a query service list, but I don't know the query I should use. Thanks. Trilotat (talk) 02:00, 20 February 2019 (UTC)

Roughly like this. Be aware that capitalization of DOIs matter here, although DOIs are case-insensitive. —MisterSynergy (talk) 06:00, 20 February 2019 (UTC)

@MisterSynergy: Thanks! That's exactly what I needed. Trilotat (talk) 02:41, 21 February 2019 (UTC)

Show only highest territorial level

Hello, how to show only highest {P|131}} ? I work on this list and I do not wish to read city X, county X but only Province X of Canada as of P.131.. :

PREFIX wdno: <http://www.wikidata.org/prop/novalue/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT DISTINCT ?airport ?airportLabel (SAMPLE(?coord) AS ?coord) ?ICAO ?IATA (SAMPLE(?Local_Code) AS ?Local_Code)  ?localisation_administrativeLabel WHERE {
?airport (wdt:P31/wdt:P279*) wd:Q62447.
?airport wdt:P17 wd:Q16.
MINUS { ?airport wdt:P576 _:b2. }
MINUS { ?airport wdt:P582 _:b3. }
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q1311670. }
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q7373622. }
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q502074. }
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q2265915. }
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q44665966. }
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q782667. }
MINUS { ?airport rdf:type wdno:P17. }
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr,[AUTO_LANGUAGE]". }
OPTIONAL { ?airport wdt:P625 ?coord. }
OPTIONAL { ?airport wdt:P239 ?ICAO. }
OPTIONAL { ?airport wdt:P238 ?IATA. }
OPTIONAL { ?airport wdt:P5699 ?Local_Code. }
OPTIONAL { ?airport wdt:P131* ?localisation_administrative. }
{?localisation_administrative wdt:P31 wd:Q2879.}
}
GROUP BY ?airport ?airportLabel ?ICAO ?IATA ?localisation_administrative ?localisation_administrativeLabel
ORDER BY ?coord

Try it!

Thanks ! Bouzinac (talk) 21:45, 16 February 2019 (UTC)

Ok, I think it was OPTIONAL { ?airport wdt:P131/ wd:Q2879* ?localisation_administrative. } Bouzinac (talk) 22:34, 16 February 2019 (UTC)

Edit conflicted, but here's my answer anyway :-) A problem here is that Ontario is an instance of province of Canada (Q11828004), which is a subclass of province or territory of Canada (Q2879). So P31:Q2879 won't match any items at all - you need to add an extra P279 line to match a province/territory.

You also need to bring this bit inside the overall P131 optional clause, but once you've done that it works correctly. It shows the top-level admin area (if one exists). If it has a P131 but that doesn't link into a chain going up to a province/territory, then it's blank. eg/ Niagara Central Airport (Q94904) has a P131 but it doesn't have a complete chain up to Ontario. Andrew Gray (talk) 22:35, 16 February 2019 (UTC)

PREFIX wdno: <http://www.wikidata.org/prop/novalue/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT DISTINCT ?airport ?airportLabel (SAMPLE(?coord) AS ?coord) ?ICAO ?IATA (SAMPLE(?Local_Code) AS ?Local_Code)  ?localisation_administrativeLabel WHERE {
?airport (wdt:P31/wdt:P279*) wd:Q62447.
?airport wdt:P17 wd:Q16.
MINUS { ?airport wdt:P576 _:b2. }
MINUS { ?airport wdt:P582 _:b3. }
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q1311670. }
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q7373622. }
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q502074. }
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q2265915. }
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q44665966. }
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q782667. }
MINUS { ?airport rdf:type wdno:P17. }
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr,[AUTO_LANGUAGE]". }
OPTIONAL { ?airport wdt:P625 ?coord. }
OPTIONAL { ?airport wdt:P239 ?ICAO. }
OPTIONAL { ?airport wdt:P238 ?IATA. }
OPTIONAL { ?airport wdt:P5699 ?Local_Code. }
OPTIONAL { ?airport wdt:P131* ?localisation_administrative.
           ?localisation_administrative wdt:P31 ?type .
           ?type wdt:P279 wd:Q2879. }
}
GROUP BY ?airport ?airportLabel ?ICAO ?IATA ?localisation_administrative ?localisation_administrativeLabel
ORDER BY ?coord

Try it!

@Bouzinac: looking at these two queries, your OPTIONAL { ?airport wdt:P131/ wd:Q2879* ?localisation_administrative. } doesn't quite seem to work as expected - it returns all the P131 values, regardless of whether they're a town or a province. So Niagara Central Airport (Q94904) has one value (not a province) and Bamfield Water Aerodrome (Q338950) has two (one the province, one the town). I'm not quite sure how the / syntax works though so I can't see why. Andrew Gray (talk) 22:41, 16 February 2019 (UTC)

Hi there, thank you, I buy the OPTIONAL { ?airport wdt:P131* ?localisation_administrative.

          ?localisation_administrative wdt:P31 ?type .
          ?type wdt:P279 wd:Q2879. } The data is often poor on obscure airports… So data is not clean :/ Bouzinac (talk) 23:03, 16 February 2019 (UTC)

Hello again, I'm surprised by this code : ?airport wdt:P131* ?localisation_administrative. ?localisation_administrative wdt:P31 ?type. ?type wdt:P279 wd:Q852446. does show any level of US territories [so does'nt limit to US States] but ?airport wdt:P131* ?localisation_administrative. ?localisation_administrative wdt:P31 ?type. ?type wdt:P279 wd:Q35657. renders an empty column… Any thoughts? Bouzinac (talk) 22:56, 18 February 2019 (UTC)

Q35657 is a P279 of Q852446, but Q35657 has no useful P279 relation with, say Utah (Q829) so if you want to look for locations that are part of Q35657 only, you'd use ?airport wdt:P131* ?localisation_administrative. ?localisation_administrative wdt:P31 wd:Q35657. whereas if you're interested in Q852446 you require the P279 lookup. --Tagishsimon (talk) 23:40, 18 February 2019 (UTC)

Like this (I added a *) ?

PREFIX wdno: <http://www.wikidata.org/prop/novalue/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>

SELECT DISTINCT ?localisation_administrativeLabel ?airport ?airportLabel (SAMPLE(?coord) AS ?coord) ?ICAO ?IATA (SAMPLE(?Local_Code) AS ?Local_Code) WHERE {
 ?airport (wdt:P31/wdt:P279*) wd:Q62447.
?airport wdt:P17 wd:Q30.
MINUS { ?airport wdt:P576 _:b2. }
MINUS { ?airport wdt:P582 _:b3. }
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q1311670. }
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q7373622. }
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q2265915. }
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q44665966. }
MINUS { ?airport (wdt:P31/wdt:P279*) wd:Q782667. }
MINUS { ?airport rdf:type wdno:P17. }
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr,[AUTO_LANGUAGE]". }
OPTIONAL { ?airport wdt:P625 ?coord. }
OPTIONAL { ?airport wdt:P239 ?ICAO. }
OPTIONAL { ?airport wdt:P238 ?IATA. }
OPTIONAL { ?airport wdt:P240 ?Local_Code. }
OPTIONAL {
?airport wdt:P131* ?localisation_administrative.
?localisation_administrative wdt:P31 ?type.
?type wdt:P279* wd:Q35657.
}
}
GROUP BY ?airport ?airportLabel ?ICAO ?IATA ?localisation_administrative ?localisation_administrativeLabel
ORDER BY ?coord

Try it!

Bouzinac (talk) 10:13, 21 February 2019 (UTC)

Several categories

Hello

I already have this query, that list for me all username participating in Wiki loves Africa 2015. How can I change this query so that it gives me the list of unique participants to all 4 years of Wiki Loves Africa ?

Thank you for the help !

Anthere (talk)

SELECT DISTINCT rev_user_text, rev_user, cl_to
FROM commonswiki_p.page p 
INNER JOIN commonswiki_p.categorylinks cl ON p.page_id = cl.cl_from 
INNER JOIN commonswiki_p.revision r ON p.page_id = r.rev_page 
WHERE p.page_namespace = 6 
AND cl.cl_to IN ("Images_from_Wiki_Loves_Africa_2014")
AND r.rev_parent_id = 0;

@Anthere: This is a page where SPARQL 1.1 Query Language (Q32146616) queries can be requested. However, see https://quarry.wmflabs.org/query/33627 -- Luitzen (talk) 19:13, 21 February 2019 (UTC)

Perfect ! Thanks, I go forward with this. Anthere (talk)

Subquery for 'joining'

Hello,

This is kind of a not so good use case, but here we go: I want to make a Listeria-based dashboard of the count of video game (Q7889) per Wikipedia site link. The query for that is fairly simple:

SELECT ?item (COUNT(?game) as ?count) WHERE {
  ?game wdt:P31 wd:Q7889.
  ?article schema:about ?game .
  ?article schema:isPartOf ?item.
} GROUP BY ?item

Try it!

But since Listeria can only handle Q-items, I have been trying to get a Q-item in there, by 'joining' with the lang<>Q-item of the Wikipedia. I got as far as something like the following but it does not work as expected:

SELECT ?item ?count
WITH {
  SELECT ?lang (COUNT(DISTINCT ?article) AS ?count) WHERE {
    ?article schema:about/wdt:P31 wd:Q7889 .
    FILTER (SUBSTR(str(?article), 11, 15) = ".wikipedia.org/") .
    ?article schema:inLanguage ?lang .
  } 
  GROUP BY ?lang
} AS %items
WITH {
  SELECT ?item WHERE {
    INCLUDE %items.
    ?item wdt:P31 wd:Q10876391.
    ?item wdt:P424 ?lang.
  }
} AS %wikipedias
WHERE {
  INCLUDE %items.
  INCLUDE %wikipedias.
} ORDER BY DESC(?count)

Try it!

Any help appreciated :) Jean-Fred (talk) 19:35, 21 February 2019 (UTC)

@Jean-Frédéric: I may be missing the point, JF, but this?

select ?item ?lang ?count with {
SELECT ?lang (COUNT(DISTINCT ?article) AS ?count) WHERE {
    ?article schema:about/wdt:P31 wd:Q7889 .
    FILTER (SUBSTR(str(?article), 11, 15) = ".wikipedia.org/") .
    ?article schema:inLanguage ?lang .
    } 
group by ?lang } as %i
where
{
  include %i
    ?item wdt:P31 wd:Q10876391.
    ?item wdt:P424 ?lang      
}
     ORDER BY DESC(?count)

Try it!

--Tagishsimon (talk) 19:48, 21 February 2019 (UTC)

Thanks! Still have to learn a few things about subqueries…

The point is that I can make Wikidata:WikiProject Video games/Statistics/Sitelinks − Listeria cannot handle something else than Q-items for its main working item. :-)

Jean-Fred (talk) 19:57, 21 February 2019 (UTC)

requesting a value

Hello,

Please give me a query showing Wikidata items which have IETF language tag (P305) with qualifier start time (P580) "2005-10-16", such in special:diff/855691502. Visite fortuitement prolongée (talk) 21:27, 21 February 2019 (UTC)

@Visite fortuitement prolongée: This:

SELECT ?item ?itemLabel 
WHERE 
{
  ?item p:P305 [pq:P580 "2005-10-16"^^xsd:dateTime] .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 21:53, 21 February 2019 (UTC)

Thank you! Visite fortuitement prolongée (talk) 22:08, 21 February 2019 (UTC)

Municipalties of Czech Republic

Hi! I want to get the list of municipalities in Czech Republic. I want to see name of municipality in English, name of municipality in Russian, population, and located in the administrative territorial entity (P131), also both in english and in russian language.

So as a result i want to get a table like this one.

I tried to do query by myself:

SELECT ?municipality_of_the_Czech_Republic ?municipality_of_the_Czech_RepublicLabel ?country ?countryLabel ?population ?located_in_the_administrative_territorial_entity ?located_in_the_administrative_territorial_entityLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?municipality_of_the_Czech_Republic wdt:P31 wd:Q5153359.
  OPTIONAL { ?municipality_of_the_Czech_Republic wdt:P17 ?country. }
  OPTIONAL { ?municipality_of_the_Czech_Republic wdt:P1082 ?population. }
  OPTIONAL { ?municipality_of_the_Czech_Republic wdt:P131 ?located_in_the_administrative_territorial_entity. }
}
LIMIT 100

Try it!

It's good, but I don't really know how to add to columns for russian "name of municipality" (municipality of the Czech Republic (Q5153359)) and for russian name of located in the administrative territorial entity (P131)". Please, help me with such problem. Rampion (talk) 10:43, 19 February 2019 (UTC)

@Rampion: Somthing like this, perhaps. You can either rely on the SERVICE wikibase:label to get labels for you - but (afaik) it'll only fetch them for a single language - or else you use a triple with rdfs:label as the predicate, and a language filter to specify which language you're after. I've also shortened some of your variable names, to stop the results showing as a list of lists rather than as columns.

SELECT ?municipality_of_the_Czech_Republic ?en_label ?ru_label ?country ?countryLabel ?population ?located ?en_located_label ?ru_located_label  WHERE {
  ?municipality_of_the_Czech_Republic wdt:P31 wd:Q5153359.
  OPTIONAL { ?municipality_of_the_Czech_Republic rdfs:label ?en_label . filter(lang(?en_label)="en") }
  OPTIONAL { ?municipality_of_the_Czech_Republic rdfs:label ?ru_label . filter(lang(?ru_label)="ru") }  
  OPTIONAL { ?municipality_of_the_Czech_Republic wdt:P17 ?country. }
  OPTIONAL { ?municipality_of_the_Czech_Republic wdt:P1082 ?population. }
  OPTIONAL { ?municipality_of_the_Czech_Republic wdt:P131 ?located . 
  OPTIONAL { ?located rdfs:label ?en_located_label . filter(lang(?en_located_label)="en") }
  OPTIONAL { ?located rdfs:label ?ru_located_label . filter(lang(?ru_located_label)="ru") } }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 100

Try it!

--Tagishsimon (talk) 10:56, 19 February 2019 (UTC)

@Tagishsimon: FYI: https://stackoverflow.com/a/49129274/7879193 -- Luitzen (talk) 12:53, 19 February 2019 (UTC)

Thank you, @Luitzen:. I should probably make time to start reading through this ... bloke called Stanislav there seems to have a clue about this SPARQL on wikidata lark ;).

There exists another Stanislav, who knows even more :). Much more. -- Luitzen (talk) 18:31, 19 February 2019 (UTC)

Have you got any suggestions for the following, where the filter not exists seems to be a query killer? It's an attempt to answer Wikidata:Request a query#Isolate attributes starting with a list of 1200 names, above, if we suppose the OP wants French citizens who were not born in France. TBH, I've had as little success with this query if I turn it around and feed it a long list of VALUES of countries other than France as objects for the ?item wdt:P19/wdt:P131*. I vaguely remember a Phab saying "filter not exists isn't very performant" which was catagorised as "Meh, maybe later".

SELECT ?item ?lang WHERE {
  ?item wdt:P27 wd:Q142 .
  filter not exists {?item wdt:P19/wdt:P131* wd:Q142 .}
} limit 1200

Try it!

Actually, the problem is rather in wdt:P19/wdt:P131* than in FILTER NOT EXISTS (however, you could read about the filterExists hint):

SELECT DISTINCT ?item {
  ?item wdt:P27 wd:Q142 .
# ?item wdt:P19 [] .
  FILTER NOT EXISTS {
    ?item wdt:P19 ?place .
    ?place wdt:P131*  wd:Q142 .
    hint:Prior hint:gearing "forward"
  }
} LIMIT 1200

Try it!

-- Luitzen (talk) 18:31, 19 February 2019 (UTC)

And, finally, noting your https://www.w3.org/TR/sparql11-query/#idp899488 do you have anything for @generalising's initial question at https://twitter.com/generalising/status/1091645162575532032, which didn't involve minus but merely bracketed statements. thx, as normal, for your expert insights. --Tagishsimon (talk) 13:52, 19 February 2019 (UTC)

@Tagishsimon: Nested groups should be evaluated (logically) first: https://wiki.blazegraph.com/wiki/index.php/SPARQL_Bottom_Up_Semantics -- Luitzen (talk) 18:31, 19 February 2019 (UTC)

Thank you, Luitzen. I'll see if I can accommodate both of these in my fragile understanding of SPARQL. --Tagishsimon (talk) 14:35, 22 February 2019 (UTC)

Actresses from a specific show

Hello, I would like to request a query for actresses from a specific show, as an example "Descendants of the Sun (Q18652415)", but only those that were born in South Korea. This is for use with listeria bot. --Redalert2fan (talk) 11:46, 19 February 2019 (UTC)

@Redalert2fan: Here's the basic query...the show is encoded in the values ?show {wd:Q18652415} line.

SELECT ?item ?itemLabel 
WHERE 
{
  values ?show {wd:Q18652415}
  ?show wdt:P161 ?item.
  ?item wdt:P19/wdt:P131* wd:Q884 .
  ?item wdt:P21 wd:Q6581072 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

For Listeria, all you need is:

SELECT ?item 
WHERE 
{
  values ?show {wd:Q18652415}
  ?show wdt:P161 ?item.
  ?item wdt:P19/wdt:P131* wd:Q884 .
  ?item wdt:P21 wd:Q6581072 .
}

Try it!

Happy to help with the Listeria end of it if you need. --Tagishsimon (talk) 12:02, 19 February 2019 (UTC)

Thank you very much, I just couldn't get it right myself. Thanks again, --Redalert2fan (talk) 12:32, 19 February 2019 (UTC)

@Tagishsimon: Now that I think of it, would it be possible to add multiple shows? --Redalert2fan (talk) 16:36, 19 February 2019 (UTC)

@Redalert2fan: I think the problem you'd get, right now, is that if an actress were in two shows, they'd show up only once in the list. But you could do something along the lines of:

SELECT ?item ?itemLabel (group_concat(distinct ?showLabel;separator=", ") as ?shows)
WHERE 
{
  values ?show {wd:Q18652415 wd:Q11241311 wd:Q13548225} # Add your shows here
  ?show wdt:P161 ?item.
  ?item wdt:P19/wdt:P131* wd:Q884 .
  ?item wdt:P21 wd:Q6581072 .
  optional {?show rdfs:label ?showLabel . filter(lang(?showLabel)="en")}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?item ?itemLabel

Try it!

--Tagishsimon (talk) 14:31, 22 February 2019 (UTC)

@Tagishsimon: That's fine, for the listeria side its not a problem for me. I see I can always find it on the query service if I would need to. Thanks for the help again! --Redalert2fan (talk) 16:34, 22 February 2019 (UTC)

Multiple values

I am still struggling a bit with multiple values. Here:

SELECT DISTINCT ?item ?itemLabel ?streetno ?img
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q41176.
  ?item wdt:P669 wd:Q847613 .
  ?item p:P669 ?street .
  OPTIONAL {?street ps:P669 wd:Q847613; pq:P670 ?streetno } . 
  OPTIONAL {?item wdt:P18 ?img } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "cs,en". }
}
ORDER BY xsd:integer(?streetno)

Try it!

I need streetno of located on street (P669) Wenceslas Square (Q847613) also in case of multiple located on street (P669) values.--Jklamo (talk) 19:17, 22 February 2019 (UTC)

@Jklamo: Is this better?

SELECT DISTINCT ?item ?itemLabel ?streetno ?img
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q41176.
  ?item p:P669 ?street .
  ?street ps:P669 wd:Q847613 .
  OPTIONAL {?street pq:P670 ?streetno } . 
  OPTIONAL {?item wdt:P18 ?img } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "cs,en". }
}
ORDER BY xsd:integer(?streetno)

Try it!

--Tagishsimon (talk) 19:29, 22 February 2019 (UTC)

Great, that is it.--Jklamo (talk) 19:34, 22 February 2019 (UTC)

Count of tennis players by gender

Hi, I would like to have a total count of the tennis players (occupation P106) in Wikipedia by sex or gender (p21). Thanks, --Wolbo (talk) 11:51, 23 February 2019 (UTC)

@Wolbo: This:

SELECT ?genderLabel (count(?gender) as ?count) 
WHERE 
{
  ?item wdt:P106 wd:Q10833314.
  ?item wdt:P21 ?gender .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?genderLabel

Try it!

--Tagishsimon (talk) 12:14, 23 February 2019 (UTC)

Thx. --Wolbo (talk) 16:11, 23 February 2019 (UTC)

Query Request: Band / Genre / Location

So I would like a query that displays the following:

Bands/Solo Artists

With:

• Country of origin
• ISO 2-Digit Alpha code of country
• state/providence of formation
• Location of Formation
• ISO 2-Digit Alpha Code of location of formation
• Parent Genre
• Genre
• inception

So for example:

• U2 | Ireland | IR | Leinster | Dublin | IE-D | Rock | Alternative Rock | 1976
• Metallica | United States | US | Los Angeles | California | CA| Metal | Thrash Metal | 1982

When show just bands. it pulls up about 71,772 results . but's missing several well known bands Like Metallica and Motley Crue. So that can't be returning a full list of bands. So i'm not sure what that problem is. also not sure how to add in Solo artists to the results.

I found a query to get the Genre and the parent genre. but i'm not sure how to link that to the band look up.

Genre With parent Genre

I have not been able to get the ISO 2 Digit Alpha country code show anything.

Many things i have tested have timed out or had errors.

Thank You, -Dev

I've formatted your question a little so we can see what you want. --Tagishsimon (talk) 20:08, 22 February 2019 (UTC)

@DevXen: You can have quite a lot of what you're after, but there are a couple of caveats. First, I give you in the query below bands, not solo artists. We're almost getting a timeout. Adding more items into the query is not helpful. You can adapt the query to deal with solo artists, if you wish - although there may be issues in identifying them - you cannot use P31, but instead would have to use occupation (P106) and a variety of values - singer, entertainer, singer-songwriter, etc etc. Second, and more serious, is the concept of parent genres. Let's look at Metallica (Q15920). They have 4 genres:

• thrash metal (Q483352) which has as parent heavy metal (Q38848)
• heavy metal (Q38848) which has as parent rock music (Q11399)
• hard rock (Q83270) which has as parent rock music (Q11399)
• rock music (Q11399) which has as parent popular music (Q373342)

And so your problems here are four-fold:

1. There's redundancy in the statements, which is unhelpful
2. There are a plurality of genres, which either means more rows or the need to group_concat, as I do for genre in the report below
3. Given the redundancy, it's not clear what getting the parent shows
4. it's not clear to me that we will have enough time to collect the parents before we hit a timeout.

So. Discuss

SELECT ?item ?itemLabel ?CofOLabel ?CofO_ISO ?formLabel ?Cform_ISO ?inception ?genre with 
{  
SELECT ?item ?CofO ?CofO_ISO ?Cform_ISO ?form ?inception (group_concat(?gl;separator=", ") as ?genre)
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q215380. #is a band or subclass of a band
  OPTIONAL {?item wdt:P495 ?CofO . # has a country of origin 
  OPTIONAL {?CofO wdt:P297 ?CofO_ISO .} # which has an ISO
            }
  OPTIONAL {?item wdt:P740 ?form . # has a location of formation
  OPTIONAL {?form wdt:P131* ?Cform.
  ?Cform wdt:P31 wd:Q3624078 .
  ?Cform wdt:P297 ?Cform_ISO .} # which can be mapped to a sovereign state with an ISO
            }
  OPTIONAL {?item wdt:P571 ?inception . } # has an inception date
  OPTIONAL {?item wdt:P136 ?g .           # has a genre
            ?g rdfs:label ?gl . filter(lang(?gl)="en") } 
} group by ?item ?CofO ?CofO_ISO ?Cform_ISO ?form ?inception } as %i
where
{
  include %i
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }

Try it!

hth --Tagishsimon (talk) 23:55, 22 February 2019 (UTC)

@Tagishsimon: Thank you for all your help so far. The genres i'll probably need to fix some manually. but Just the first genre should be fine as well as the Parent of that. the idea is to group genres. Rock for example would have Hard rock, Classic Rock, Alternative rock, etc.

CForm_ISO - is MOSTLY showing the same as the CofO_ISO - A couple are different but it's mostly showing the country abbreviation instead of the state abbreviation. For Example in this the second US should be CA for california. as that's where Los Angeles is located: Metallica|United States of America|US|Los Angeles|US|1982-10-28T00:00:00Z|Thrash Metal|Heavy Metal

DevXen (talk) 02:01, 24 February 2019 (UTC)

Using qualifiers in a query

I have this Query for the govenors of the US as per today. How can I add the qualifiers for start time (P580) and end time (P582) as end date of his government and series ordinal (P1545)? Pmt (talk) 17:55, 24 February 2019 (UTC)

:SELECT ?USAs_delstater ?USAs_delstaterLabel ?_verste_politiske_forvaltningsleder ?_verste_politiske_forvaltningslederLabel ?startdato ?bilde_av_segl ?v_penbilde ?hovedstad ?hovedstadLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?USAs_delstater wdt:P31 wd:Q35657.
  OPTIONAL { ?USAs_delstater wdt:P6 ?_verste_politiske_forvaltningsleder. }
  OPTIONAL { ?USAs_delstater wdt:P580 ?startdato. }
  OPTIONAL { ?USAs_delstater wdt:P158 ?bilde_av_segl. }
  OPTIONAL { ?USAs_delstater wdt:P94 ?v_penbilde. }
  OPTIONAL { ?USAs_delstater wdt:P36 ?hovedstad. }
}

Try it!

@Pmt: Probably something like this. None of them seem to have a series ordinal, as far as I can see. I wrote an essay for Wittylama - User_talk:Tagishsimon#Wittylama - to which I refer you for an explainer on how to get qualifiers and references. In this case, I'll add that, because we have to fetch p: and ps: values, we would get all of the P6s listed for a state if we did not have the filter not exists since another difference between the p: ps: route and the wdt: route is that the query returns all values, not merely truthy values; and that point is explained (albeit opaquely for mere humans) in the RDF model page which the Wittylama essay commends to your attention. (Oh: and *much* shorter variable names would probably allow you to get results as a tabular list, rather than a list of lists.)

SELECT ?USAs_delstater ?USAs_delstaterLabel ?_verste_politiske_forvaltningsleder ?_verste_politiske_forvaltningslederLabel ?startdato ?end ?series ?bilde_av_segl ?v_penbilde ?hovedstad ?hovedstadLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?USAs_delstater wdt:P31 wd:Q35657.
  ?USAs_delstater p:P6 ?statement .
  ?statement ps:P6 ?_verste_politiske_forvaltningsleder .
  OPTIONAL { ?statement pq:P580 ?startdato .}
  filter not exists { ?statement pq:P582 ?end .}
  OPTIONAL { ?statement pq:P1545 ?series .}
  OPTIONAL { ?USAs_delstater wdt:P158 ?bilde_av_segl. }
  OPTIONAL { ?USAs_delstater wdt:P94 ?v_penbilde. }
  OPTIONAL { ?USAs_delstater wdt:P36 ?hovedstad. }
}

Try it!

hth --Tagishsimon (talk) 09:21, 25 February 2019 (UTC)

Query a company based on its official website.

Amazon has multiple official websites listed. If I search for <https://www.amazon.com>, it returns a result but when I search for <https://www.amazon.co.uk/> it does not return any result.

SELECT distinct
  ?official_name 
  (GROUP_CONCAT( DISTINCT ?isin; separator=";") AS ?isin) 
  ?item 
  ?itemLabel
  ?stock_exchange 
  ?stock_exchangeLabel
  (GROUP_CONCAT( DISTINCT ?ticker; separator=";") AS ?ticker)
  (GROUP_CONCAT( DISTINCT ?other_name; separator=";") AS ?other_name)
WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item (wdt:P31/wdt:P279*) wd:Q783794.
  {
            { ?item wdt:P856 <https://www.amazon.co.uk/> }   
  }  
  OPTIONAL { ?item wdt:P1448 ?official_name. }
  OPTIONAL { ?item wdt:P946 ?isin. }  
  OPTIONAL {  
    ?item p:P414 ?SE . 
    ?SE ps:P414 ?stock_exchange . 
    ?SE pq:P249 ?ticker .
   } 
  OPTIONAL { ?item skos:altLabel ?other_name. FILTER (LANG (?other_name) = "en") }
}
GROUP BY ?official_name ?item ?itemLabel ?stock_exchange ?stock_exchangeLabel

Try it!

Is the difference between the truthy value and all values. The .com URL is marked as preferred rank, and so wdt: will return it and nothing else. Use ?item p:P856 [ ps:P856 <https://www.amazon.co.uk/> ] . if you want to be able to search against all of the URLs. See RDF Dump Format#Statement types. --Tagishsimon (talk) 12:55, 25 February 2019 (UTC)

Russian districts

I'm using this query to map Russian districts. Right now it only selects a district if located in the administrative territorial entity (P131) matches Kamchatka Krai (Q7948), but it should also select entities such as Tigilsky District (Q1042950) (which has the chain of properties located in the administrative territorial entity (P131) -> Koryak Okrug (Q204809) -> Kamchatka Krai (Q7948)).

SELECT ?id
  (if(BOUND(?link),
      concat('[[', substr(str(?link),31,500),  '|', ?idLabel, ']]'),
      ?idLabel)
   as ?title)
WHERE {
  ?id p:P31/ps:P31/wdt:P279* wd:Q1849719 .
  ?id p:P131 ?locst .
  ?locst ps:P131 wd:Q7948
  MINUS { ?locst pq:P582 [] }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language 'en' .
    ?id rdfs:label ?idLabel .
  }
  OPTIONAL {?link schema:about ?id .
  ?link schema:isPartOf <https://en.wikipedia.org/> . }
} GROUP BY ?id ?link ?idLabel

Try it!

--Underlying lk (talk) 11:05, 24 February 2019 (UTC)

@Underlying lk: Without checking, does substituting in ?locst ps:P131/wdt:P131* wd:Q7948 instead of ?locst ps:P131 wd:Q7948 help? --Tagishsimon (talk) 11:13, 24 February 2019 (UTC)

That seems to work okay, thanks.--Underlying lk (talk) 11:22, 24 February 2019 (UTC)

@Underlying lk: The folloiwng should be essentially faster:

hint:Query hint:optimizer "None" .
?_ wdt:P131* wd:Q7948 .
?locst ps:P131 ?_ .
?id p:P131 ?locst  . 
?__ wdt:P279* wd:Q1849719 .
?id p:P31/ps:P31 ?__ .
minus { ?locst pq:P582 [] }

-- Luitzen (talk) 18:10, 24 February 2019 (UTC)

I can't get this to run, "Query is malformed: Encountered " <PNAME_LN> "hint:Query".--Underlying lk (talk) 18:38, 24 February 2019 (UTC)

@Underlying lk: Full version:

SELECT ?id
  (if(BOUND(?link),
      concat('[[', substr(str(?link),31,500),  '|', ?idLabel, ']]'),
      ?idLabel)
   as ?title)
WHERE {
  hint:Query hint:optimizer "None" .
  ?_ wdt:P131* wd:Q7948 .
  ?locst ps:P131 ?_ .
  ?id p:P131 ?locst  . 
  ?__ wdt:P279* wd:Q1849719 .
  ?id p:P31/ps:P31 ?__ .
  MINUS { ?locst pq:P582 [] }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language 'en' .
    ?id rdfs:label ?idLabel .
  }
  OPTIONAL {?link schema:about ?id .
  ?link schema:isPartOf <https://en.wikipedia.org/> . }
} GROUP BY ?id ?link ?idLabel

Try it!

-- Luitzen (talk) 09:00, 25 February 2019 (UTC)

Query works but the mapframe extension throws up a JSON Syntax error, so unfortunately it can't be used for w:Template:Russia district OSM map.--Underlying lk (talk) 21:19, 25 February 2019 (UTC)

Add a column instead of a line

I'm a trying to retrieve the information of every weather station (Q190107) of the Meteorological Service of Canada (Q349450). With this in hand, I will be able to parse the CSV export and update the list whenever there is new information published by the MSC. My problem is, when there is more than one value set to a parameter, my query returns more than one line for each station.

For example, when I try to retrieve the property reports periodicity (P6339), which can have up to 3 values (hourly measurement (Q59657010), monthly measurement (Q59657037), daily measurement (Q59657036)), I get more than one line for many stations:

# Toutes les stations du SMC
SELECT ?item ?itemLabel ?coord ?intervalle_des_donn_es WHERE {
  ?item (wdt:P31/wdt:P279*) wd:Q190107.
  ?item wdt:P127 wd:Q349450.
  ?item wdt:P6242 ?MSCID
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  OPTIONAL { ?item wdt:P625 ?coord. }
  OPTIONAL { ?item wdt:P6339 ?intervalle_des_donn_es. }
}
ORDER BY(?MSCID)

Try it!

I am looking to have each value of reports periodicity (P6339) on a single column and, moreoever, that each qualifier of these values (start time (P580) and end time (P582)) on a single column as well (for a total of 6 columns only for this parameter).

How could I achieve this?

Thanks, Dirac (talk) 19:35, 26 February 2019 (UTC)

@Dirac: I didn't find an example of yearly (too lazy), but here's daily & monthly. You can follow the pattern (carefully) for yearly. Use short variable names to preserve the grid output format (although if you're exporting to CSV, it's less important). Optionally, read User_talk:Tagishsimon#Wittylama for info on how to access qualifiers. If all daily / monthly / yearly have at least start dates, then you do not really need those three columns (?daily ?monthly ?yearly), maybe, just the dates themselves.

# Toutes les stations du SMC
SELECT ?item ?itemLabel ?coord ?daily ?d_start ?d_end ?monthly ?m_start ?m_end WHERE {
  ?item (wdt:P31/wdt:P279*) wd:Q190107.
  ?item wdt:P127 wd:Q349450.
  ?item wdt:P6242 ?MSCID
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  OPTIONAL { ?item wdt:P625 ?coord. }
  OPTIONAL { ?item p:P6339 ?d_statement .          # there is a P6339 property
             ?d_statement ps:P6339 wd:Q59657036 .  # and it's a daily
             ?d_statement ps:P6339 ?daily .        # and we get its value, in case there are no dates
             OPTIONAL { ?d_statement pq:P580 ?d_start .} # might be a start date
             OPTIONAL { ?d_statement pq:P582 ?d_end .}   # might be an end date
           }
  OPTIONAL { ?item p:P6339 ?m_statement .          # Note we change to ?m_statement from ?d_statement
             ?m_statement ps:P6339 wd:Q59657037 .  # same for monthly
             ?m_statement ps:P6339 ?monthly .     
             OPTIONAL { ?m_statement pq:P580 ?m_start .}
             OPTIONAL { ?m_statement pq:P582 ?m_end .}
           }
}
ORDER BY(?MSCID)

Try it!

hth --Tagishsimon (talk) 20:06, 26 February 2019 (UTC)

This is what I was looking for. Thanks Tagishsimon! Dirac (talk) 20:39, 26 February 2019 (UTC)

Optimizing a query for the mapframe extension

I've been using the mapframe module to create dynamic locator maps of subnational entities, such as this and this one. After several tweaks they now work rather well, but the main issue seems to be one of performance: the maps are often slow to load, and sometimes they won't load at all. Any help in improving their efficiency is most welcome.

This is the 'pure' query (without some parser functions required to make them work with mapframe, which can be found at w:Template:Russia district OSM map):

SELECT ?id
  (if(BOUND(?link),
      concat('[[', substr(str(?link),31,500),  '|', ?idLabel, ']]'),
      ?idLabel)
   as ?title)
WHERE {
  { ?id p:P31/ps:P31/wdt:P279* wd:Q2198484 . }
  UNION
  { ?id p:P31/ps:P31/wdt:P279* wd:Q13626398 . }  
  UNION
  { ?id p:P31/ps:P31/wdt:P279* wd:Q4057820 . }  
  ?id p:P131 ?locst .
  ?locst ps:P131/wdt:P131* wd:Q5400.
  MINUS { ?locst pq:P582 [] }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language 'en' .
    ?id rdfs:label ?idLabel .
  }
  OPTIONAL {?link schema:about ?id .
  ?link schema:isPartOf <https://en.wikipedia.org/> . }
} GROUP BY ?id ?link ?idLabel

Try it!

--Underlying lk (talk) 21:37, 25 February 2019 (UTC)

I'm afraid these suggestions are very much (from me) cargo cult, but it may well be worth playing with hint:Prior hint:gearing "forward". My quick experiments seem to show speed improvements; only one is uncommented here, but there are perhaps three other positions it might work in. Luitzen's your man for this sort of thing. Details here, albeit I'm not at a point at which I grok it or its implications. Best to play & take some measurements.

SELECT ?id
  (if(BOUND(?link),
      concat('[[', substr(str(?link),31,500),  '|', ?idLabel, ']]'),
      ?idLabel)
   as ?title)
WHERE {
  { ?id p:P31/ps:P31/wdt:P279* wd:Q2198484 .
  #  hint:Prior hint:gearing 'forward' 
}
  UNION
  { ?id p:P31/ps:P31/wdt:P279* wd:Q13626398 . 
  #  hint:Prior hint:gearing 'forward' 
}  
  UNION
  { ?id p:P31/ps:P31/wdt:P279* wd:Q4057820 . 
  #  hint:Prior hint:gearing 'forward' 
}  
  ?id p:P131 ?locst .
  ?locst ps:P131/wdt:P131* wd:Q5400.
  hint:Prior hint:gearing 'forward' 
  MINUS { ?locst pq:P582 [] }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language 'en' .
    ?id rdfs:label ?idLabel .
  }
  OPTIONAL {?link schema:about ?id .
  ?link schema:isPartOf <https://en.wikipedia.org/> . }
} 
GROUP BY ?id ?link ?idLabel

Try it!

--Tagishsimon (talk) 21:47, 25 February 2019 (UTC)

Adding hints, even commented out, returns an error when trying to load the map.--Underlying lk (talk) 22:49, 25 February 2019 (UTC)

Bummer. Perhaps something @Evad37: could advise on? --Tagishsimon (talk) 22:51, 25 February 2019 (UTC)

This is coming from the extension, not the template/module, since it also doesn't work with plain <mapframe>...</mapframe> tags:

<mapframe>: Couldn't parse JSON: Control character error, possibly incorrectly encoded

You can report it in Phabricator, and hopefully the developers will eventually get around to fixing it. (The module could probably remove comments, but that doesn't really help you out) - Evad37 [talk] 23:25, 25 February 2019 (UTC)

I've started the ball rolling at https://phabricator.wikimedia.org/T217103 ... Underlying lk, I suspect it might help if the sandbox example you provided is left in place, since it's an exemplar of the issue. --Tagishsimon (talk) 23:57, 25 February 2019 (UTC)

Your JSON needs to be valid, and that includes the value containing the SPARQL value. The " of the hint was ending the value of "query" prematurely (use ' instead for string in sparql queries to avoid the problem). The other page also had an incorrect trailing comma. TheDJ (talk) 14:14, 26 February 2019 (UTC)

That's a very helpful workaround @TheDJ:, but there's still a parser fail here, if we take the charitable view that Blazegraph should be allowed to specify what is the valid syntax for hints on their own engine - see https://wiki.blazegraph.com/wiki/index.php/QueryHints --Tagishsimon (talk) 16:10, 26 February 2019 (UTC)

@Tagishsimon: Yes that is true. First of all, the userinput is incorrect. It should be json escaped. But even when you use \" instead of " it seems things still break, not entirely sure why that is. Possibly the core doesn't decode the sparql value correctly, but that is a deeper investigation. Someone should file a separate ticket on that. TheDJ (talk) 16:21, 26 February 2019 (UTC)

I have implemented the sandbox changes in the mainspace version, and everything seems to work faster indeed.--Underlying lk (talk) 08:35, 27 February 2019 (UTC)

Largest worldwide cities per country - final tweaks on an aggregate query

I have a half-finished query that I would like to tweak. You can see the original discussion on the OpenData StackExchange forum.

This query returns the maximum (not necessarily latest) population for the most populated city for all current or historical country, and in the same row return the country's total population.

select distinct ?country ?countryLabel ?country_pop?city ?cityLabel ?city_pop
 with {select * { ?city wdt:P31/wdt:P279* wd:Q515; wdt:P17 ?country; wdt:P1082 ?city_pop. ?country wdt:P1082 ?country_pop } } AS %include
  where { 
    { select ?country (max(?city_pop) AS ?city_pop) (max(?country_pop) as ?country_pop) {include %include } group by ?country }
      include %include
      service wikibase:label {bd:serviceParam wikibase:language 'en'}
  } order by desc(?city_pop)

Try it!

To finalize the query, I'm posting here for the last few tweaks.

• latest populations, and not historic maximum

• only current "existing" countries (for example, don't want the Kingdom of Bavaria)

• mark each city if it is the capital or not (true/false, yes/no, etc)

• add `continentLabel` for which continent the country belongs to (this should be easy but the aggregate eludes me)

• add a column which is the ratio of latest_city_population : latest_country_population for each row.

Here's my target results:

continent_name, country_name, is_existing_country, country_population, city_name, city_latest_population, is_capital_city, city_to_country_population_ratio
Asia,India,true,1339180127,Delhi,26495000,true,0.01978449311
North America,United States of America,true,325145963,New York City,false,8537673,0.02625797018

I'm flexible with everything, including the naming and the exact results in terms of "latest" population. Many thanks in advance!

--Philshem (talk) 14:53, 26 February 2019 (UTC)

@Philshem: First, hats off to Stanislav, who is known in this parish, and whose query, above, caused me some headscratching; not least the double inclusion of %include. But I see it now, having taken it to pieces a couple of times.

I've mangled the query some more to try to meet your spec, but there are issues & comments. First, this business of max versus latest. tbh, because of the way that data is (or should be) coded in wikidata, the above query is (or should be) giving you the latest population figures for each country or city (the distinction is between wdt: truthy values and ps: any/all values - in short, items should mark the latest population as preferred and all others as normal, meaning the truthy value is the latest population). So I've made no adjustment there. We could, of course, do it longhand, use ps: values and look at dates, but we'd be as likely to run into incomplete date information as we are likely to run into suboptimal truthy values using wdt:. Next, there is the always interesting question of what is a current country and how is it defined. I've implemented a particular method of choosing current countries, stolen from a WDQS example query without much inspection. We could as easily have used a proxy such as current membership of the United Nations. If you spot issues, we can work on this area. Next, continents ... again not straightforward. Russia, Turkey, USA, many other countries are found in or on more that one continent. For now I've given you a single column with a concatenation of all of the continents associated with a country. Discuss. This is probably not ready to be posted back to stackexchange, especially until you've assessed whether any current countries are missing, or historic countries still included. Finally, I have no clue how to get decimal places under control. Sorry about that :)

select distinct (group_concat(?contLabel) as ?cc) ?country ?countryLabel ?country_pop ?city ?cityLabel ?isitcapital ?city_pop ?ratio with 
{ select * 
 { ?city wdt:P31/wdt:P279* wd:Q515 ; 
         wdt:P17 ?country ; 
         wdt:P1082 ?city_pop . 
  ?country wdt:P1082 ?country_pop } } AS %include
with 
{ select distinct ?continent ?country ?countryLabel ?country_pop ?city ?cityLabel ?isitcapital ?city_pop ?ratio where 
{ 
  { select ?country (max(?city_pop) AS ?city_pop) (max(?country_pop) as ?country_pop) 
           {include %include } group by ?country
  }
  include %include

  ?country wdt:P31 wd:Q3624078 .
  #not a former country
  FILTER NOT EXISTS {?country wdt:P31 wd:Q3024240}
  #and no an ancient civilisation (needed to exclude ancient Egypt)
  FILTER NOT EXISTS {?country wdt:P31 wd:Q28171280}
  OPTIONAL { ?country wdt:P36 ?capital } .
  bind(if(?capital=?city,"yes","no") as ?isitcapital)
  bind(((?city_pop*100) / ?country_pop) as ?ratio)
  ?country wdt:P30 ?continent . 
  service wikibase:label {bd:serviceParam wikibase:language 'en'}
} order by desc(?city_pop) } as %i
where
{include %i
 ?continent rdfs:label ?contLabel . filter(lang(?contLabel)="en")
} group by ?country ?countryLabel ?country_pop ?city ?cityLabel ?isitcapital ?city_pop ?ratio

Try it!

--Tagishsimon (talk) 01:12, 27 February 2019 (UTC)

@Philshem: Another version: http://tiny_url.com/y362a2cu (remove "_" in the URL) -- Luitzen (talk) 08:08, 27 February 2019 (UTC)

Add reference for the number

Hello, how to add the reference that provided the number ?

SELECT ?item ?itemLabel ?year
(sample(?number) as ?number)
WHERE {
values ?item {
wd:Q384788
} .
?item p:P3872 ?statement.
?statement pq:P585 ?time.
?statement ps:P3872 ?number.
bind (YEAR(?time) AS ?year) 
SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
MINUS { ?statement wikibase:rank wikibase:DeprecatedRank }
} group by ?year ?item ?itemLabel
order by ?item desc (?year)

Try it!

Bouzinac (talk) 14:18, 27 February 2019 (UTC)

@Bouzinac:

SELECT ?item ?itemLabel ?year (sample(?number) as ?number) ?ref WHERE 
{
values ?item {wd:Q384788} .
?item p:P3872 ?statement.
?statement pq:P585 ?time.
?statement ps:P3872 ?number.
?statement prov:wasDerivedFrom ?statement2 .
?statement2 pr:P4656 ?ref.
bind (YEAR(?time) AS ?year) 
SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
MINUS { ?statement wikibase:rank wikibase:DeprecatedRank }
} group by ?year ?item ?itemLabel ?ref
order by ?item desc (?year)

Try it!

--Tagishsimon (talk) 16:44, 27 February 2019 (UTC)

Hi Tagishsimon, thank you. I'm surprised by the result : shouldn't it be picking randomly whatever reference Wikimedia import URL (P4656)/reference URL (P854) ? If I were to reuse this query for another airport, say Orly Airport (Q223416), it might not be filled with P4656 or P854... Bouzinac (talk) 22:14, 27 February 2019 (UTC)

@Bouzinac: Umm, yes. This overcomes that limitation, I think

SELECT ?item ?itemLabel ?year (sample(?number) as ?number) ?reftype ?ref WHERE 
{
values ?item {wd:Q384788} .
?item p:P3872 ?statement.
?statement pq:P585 ?time.
?statement ps:P3872 ?number.
?statement prov:wasDerivedFrom ?statement2 .
?statement2 ?reftype ?ref.
bind (YEAR(?time) AS ?year) 
SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
MINUS { ?statement wikibase:rank wikibase:DeprecatedRank }
} group by ?year ?item ?itemLabel ?reftype ?ref
order by ?item desc (?year)

Try it!

--Tagishsimon (talk) 22:23, 27 February 2019 (UTC)

What about this one?

SELECT ?item ?itemLabel ?year (sample(?number) as ?number) (sample(?reftype) as ?reftype) (sample(?ref) as ?ref) 
WHERE 
{
values ?item {wd:Q223416} .
?item p:P3872 ?statement.
?statement pq:P585 ?time.
?statement ps:P3872 ?number.
?statement prov:wasDerivedFrom ?statement2 .
?statement2 ?reftype ?ref.
bind (YEAR(?time) AS ?year) 
SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
MINUS { ?statement wikibase:rank wikibase:DeprecatedRank }
} group by ?year ?item ?itemLabel 
order by ?item desc (?year)

Try it!

Bouzinac (talk) 22:32, 27 February 2019 (UTC)

Depends what you want. That will sample the refs (i.e. provide a single of the many) rather than listing them all. If it's one row per ?number you are after, group_concat would be another route to consider. I am missing part of the jigsaw - how to use a triple to show a subset of pr: values such as reference URL or imported from, but perhaps not date accessed (or whatever the property is called). Could do that longhand with filters; depends whether it's worth spending the time. --Tagishsimon (talk) 22:48, 27 February 2019 (UTC)

Count of en.wiki biography articles

Is it possible to count the number of biographies on en.wiki? The following times out:

SELECT (count(?item) as ?all_count) WHERE {
  ?item wdt:P31 wd:Q5 .       # human
  ?wen schema:about ?item . ?wen schema:isPartOf <https://en.wikipedia.org/>.
}

Try it!

thx --Tagishsimon (talk) 17:42, 28 February 2019 (UTC)

@Tagishsimon: Try brute force:

SELECT (COUNT(?item) AS ?all_count) WHERE {
  hint:Query hint:optimizer "Runtime" .
  hint:Query hint:maxParallel 50 .
  ?item wdt:P31 wd:Q5 . 
  ?wen schema:about ?item . ?wen schema:isPartOf <https://en.wikipedia.org/> 
}

Try it!

~1600000 -- Luitzen (talk) 21:05, 28 February 2019 (UTC)

More hints. More homework to do. thx, Luitzen --Tagishsimon (talk) 21:13, 28 February 2019 (UTC)

How to select English OR most popular non-English language Wikipedia article?

Is it possible to query the equivalent of

1. Get English Wikipedia link if it exists, 2. otherwise get the most popular non-English Wikipedia article

Something like

 ?link schema:isPartOf <https://en.wikipedia.org/> .
 OR IF THAT DOESN'T EXIST,
 ?link (by most popular / longest article / most edits / etc?) *.wikipedia.org

That could be in a query like this,

PREFIX schema: <http://schema.org/>
  SELECT DISTINCT ?item ?itemLabel ?sitelink ?Instagram_username WHERE {
    ?item wdt:P2003 ?Instagram_username.
    ?sitelink schema:about ?item.
    ?sitelink schema:isPartOf <https://en.wikipedia.org/>.
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }

Try it!

I cannot for the life of me figure this out. It seems like it's either select a language or get the firehose of all language variants?

There are two elements to the problem: 1) how you decide which other Wikipedia to select and 2) the mechanics of rejecting the not-available en.wiki and optins instead for the fallback wiki. I don't have a solution for the first; wikidata does not have article length, number of edits, &c. The only thing it does have, that I can think of, are FA and GA flags, which is great for the minority of articles that have such flags. As to mechanics, something like the following; you can extend the pattern for more language wikipedias; the query is constrained to three exemplar items.

PREFIX schema: <http://schema.org/>
  SELECT DISTINCT ?item ?itemLabel ?sitelink_final ?Instagram_username WHERE {
    values ?item {wd:Q728958  wd:Q2408789 wd:Q1927604}
    ?item wdt:P2003 ?Instagram_username.
    OPTIONAL { ?sitelink schema:about ?item.
    ?sitelink schema:isPartOf <https://en.wikipedia.org/>. }
    OPTIONAL { ?sitelink2 schema:about ?item.
    ?sitelink2 schema:isPartOf <https://fr.wikipedia.org/>. }
    bind(if(bound(?sitelink), ?sitelink, if(bound(?sitelink2),?sitelink2,"nada")) as ?sitelink_final)
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }

Try it!

--Tagishsimon (talk) 23:58, 28 February 2019 (UTC)

Thank you. I guess there isn't a way to query all the languages? I don't have a specific fallback language in mind.

Is there a way to aggregate the instance of using a separator like ,

I would like to get the different instance of for a given item and group it by concatenating with a ";". E.g. if the item is an instance of business and company, I want the labels to be concatenated as business;company instead of having it as two separate rows.

Yes. Let's take IBM (Q37156) as an example, and suppose that our query was wating to find software company (Q1058914) and list all of their P31 values in a single cell of a row.

select ?item ?itemLabel (group_concat(?P31;separator="; ") as ?instance) where
{
  values ?item {wd:Q37156}     # constrain this query to a single item
  ?item wdt:P31 wd:Q1058914 .  # find software companies
  ?item wdt:P31 ?P31 .         # get the P31 values for this item
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?item ?itemLabel

Try it!

And we can see that's clearly a bit of a bodge job; the SERVICE wikibase:label is no good for aggregates such as group_concat, so instead we do:

select ?item ?itemLabel (group_concat(?P31Label;separator="; ") as ?instance) where
{
  values ?item {wd:Q37156}     # constrain this query to a single item
  ?item wdt:P31 wd:Q1058914 .  # find software companies
  ?item wdt:P31 ?P31 .         # get the P31 values for this item
  ?P31 rdfs:label ?P31Label .  # get labels fo the ?P31
  filter(lang(?P31Label)="en") # but only en labels
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?item ?itemLabel

Try it!

--Tagishsimon (talk) 23:44, 28 February 2019 (UTC)