Sparql, Smarql, Omsk and Tomsk

Task: Use Wikidata to find all Russian or Ukrainian or Belorussian towns/cities/... with names ending in "sk" (old Tom Lehrer reference:
I have a friend in Minsk
who has a friend in Pinsk
whose friend in Omsk
has friend in Tomsk
with friend in Akmolinsk...)

Turns out not to be so easy:
not every city (Q7930989) is a human settlement (Q486972), so take the UNION.

"Located in country" (P17) seemed the only reasonable relation of cities and countries, so iterate that over Russia (Q159), Belarussia (Q184) and Ukraina (Q212) but it turns out that at least Omsk (Q898) is not in the country of Russia (Q159) but instead is an "instance of" (P31) an "administrative territorial entity of Russia" (Q192287), so UNION over "administrative territorial entities" of Russia and Ukrainia.

Turns out that there is an "administrative territorial entity of Crimea", so who knows what other non-orthogonal classifications people used to enter ex-Soviet cities into WikiData. Not exactly promising.

SELECT DISTINCT ?cityLabel WHERE {
  { ?city wdt:P31 wd:Q486972. }
  UNION
  { ?city wdt:P31 wd:Q7930989. }
  
  { ?city wdt:P17 wd:Q159. }
  UNION
  { ?city wdt:P17 wd:Q212. }
  UNION
  { ?city wdt:P17 wd:Q184. }
  UNION
  { ?city wdt:P31 wd:Q192287. }
  UNION
  { ?city wdt:P31 wd:Q203323. }
  
  ?city rdfs:label ?cityLabel.  FILTER( LANG(?cityLabel)="en" )

  FILTER(STRENDS(?cityLabel, "sk"))
}
ORDER BY STRLEN(?cityLabel)

(Click to run the query)

UPDATE: Omsk (Q898) is infact "in the country" (P17) of Russia (Q159), my oversight.
UPDATE: FILTER( LANG(?cityLabel)="en" ) is much wiser than to use the translation SERVICE.

Mon, 21 Jan 2019
[/unsorted] permanent link