Skip to main content
1 of 3
philshem
  • 17.7k
  • 7
  • 69
  • 171

Expand Wikidata SPARQL query to self-join

I have this query to get the population of cities and the country they belong to:

SELECT ?city ?population ?country ?countryLabel ?continent ?continentLabel ?cityLabel
WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?city wdt:P31 wd:Q515.
  OPTIONAL { ?city wdt:P1082 ?population. }
  OPTIONAL { ?city wdt:P17 ?country. }
  OPTIONAL { ?city wdt:P30 ?continent. }
}
LIMIT 10

But how can I expand this query to also return the population of the entire country?

I've added this statement to the query

OPTIONAL { ?country wdt:P17 ?population. }

but then I get timeouts.


Actually, my final query will be to return the most-populated city in each country, with the following structure:

city_name, city_population, country_name, country_population, continent_name

For the record, I've tried the downloads at Geonames but I find some inconsitencies: for example, "city" population of Singapore varies from "country" population of Singapore when there is only one city in Singapore (from two files, perhaps from two sources).

philshem
  • 17.7k
  • 7
  • 69
  • 171