Search code examples
gissparqlgeocodingwikidata

How to fetch GPS coordinates of world's largest cities from WikiData via SPARQL, so that the result set includes Paris?

By searching on Google and SO, I came up with the following SPARQL query for finding world's largest cities for the purpose of rudimentary geocoding:


SELECT ?city ?cityLabel ?countryLabel ?iso ?population ?gps
WHERE {
  ?city wdt:P31 wd:Q515 . hint:Prior hint:runFirst true .
  ?city wdt:P17 ?country .
  ?country wdt:P297 ?iso .
  ?city wdt:P625 ?gps .
  ?city wdt:P1082 ?population .
  FILTER (?population > 100000) .

  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY DESC(?population)
LIMIT 5000

https://query.wikidata.org/#SELECT%20%3Fcity%20%3FcityLabel%20%3FcountryLabel%20%3Fiso%20%3Fpopulation%20%3Fgps%0AWHERE%20%7B%0A%20%20%3Fcity%20wdt%3AP31%20wd%3AQ515%20.%20hint%3APrior%20hint%3ArunFirst%20true%20.%0A%20%20%3Fcity%20wdt%3AP17%20%3Fcountry%20.%0A%20%20%3Fcountry%20wdt%3AP297%20%3Fiso%20.%0A%20%20%3Fcity%20wdt%3AP625%20%3Fgps%20.%0A%20%20%3Fcity%20wdt%3AP1082%20%3Fpopulation%20.%0A%20%20FILTER%20(%3Fpopulation%20%3E%20100000)%20.%0A%0A%20%20SERVICE%20wikibase%3Alabel%20%7B%20bd%3AserviceParam%20wikibase%3Alanguage%20%22en%22.%20%7D%0A%7D%0AORDER%20BY%20DESC(%3Fpopulation)%0ALIMIT%205000

For some reason, the result set does not include Paris (France) but includes smaller cities in France. What am I doing wrong?

Thank you!

UPD: final query fetching Paris as well: https://query.wikidata.org/#SELECT%20DISTINCT%20%3Fcity%20%3FcityLabel%20%3FcountryLabel%20%3Fiso%20%3Fpopulation%20%3Fgps%0AWHERE%20%7B%0A%20%20%20%20%3Fcity%20wdt%3AP31/wdt%3AP279%2A%20wd%3AQ515%20.%0A%20%20%20%20%3Fcity%20wdt%3AP17%20%3Fcountry%20.%0A%20%20%20%20%3Fcity%20wdt%3AP1082%20%3Fpopulation%20.%0A%20%20%20%20%3Fcity%20wdt%3AP625%20%3Fgps%20.%0A%20%20%20%20%3Fcountry%20wdt%3AP297%20%3Fiso%20.%0A%0A%20%20%20%20FILTER%20%28%3Fpopulation%20%3E%20100000%29%20.%0A%0A%20%20%20%20SERVICE%20wikibase%3Alabel%20%7B%20bd%3AserviceParam%20wikibase%3Alanguage%20%22en%22.%20%7D%0A%7D%0AORDER%20BY%20DESC%28%3Fpopulation%29%0ALIMIT%205000

Solution 
  • ?city wdt:P31 wd:Q515 .

    This triple pattern excludes Paris (Q90), because it’s not an instance of city (Q515).

    It’s an instance of subclasses of city (Q515), though. For example: capital city (Q5119).

    To find all items that are instances of city (Q515) or of a subclass of city (Q515), you can use a property path:

    wdt:P31/wdt:P279*

    As a city can be an instance of multiple city subclasses, you might want to make the results distinct, otherwise these cities will appear multiple times in the result:

    SELECT DISTINCT ?city ?cityLabel #etc.