Skip to main content
3 of 3
added 466 characters in body
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 as ?city_pop) ?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.

Bonus: I think getting the continent name for "country" instead of "city" would be more reliable, too.


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

Here's a query from the tutorial that gives the max populated city in a country, but similar issues expanding to get the entire country's population in the same row

SELECT ?country (MAX(?population) AS ?maxPopulation)
WHERE
{
  ?city wdt:P31/wdt:P279* wd:Q515;
        wdt:P17 ?country;
        wdt:P1082 ?population.
}
GROUP BY ?country

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