0

I am trying to get all cities and their states/province of a country. Here I got all cities of Canada successfully using this below query.

Cities Wikidata SPARQL -- It works

SELECT ?city ?cityLabel WHERE {
  ?city wdt:P17 wd:Q16;
    (wdt:P31/(wdt:P279*)) wd:Q515;
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY ?cityLabel

I want to get the state/province of all cities. So, I tried this below query. But, it didn't work.

Cities & State/Province Wikidata SPARQL -- Didn't work

SELECT ?city ?cityLabel ?stateLabel WHERE {
  ?city wdt:P17 wd:Q16;
    (wdt:P31/(wdt:P279*)) wd:Q515;
    wdt:P131* ?state . ?state wdt:P31 wd:Q11828004
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
ORDER BY ?cityLabel
LIMIT 5000
Karuppiah RK
  • 3,894
  • 9
  • 40
  • 80
  • 2
    sometimes the query optimizer isn't smart enough. We can try to "help" and tell him that our order is the best for execution:`SELECT ?city ?cityLabel ?stateLabel WHERE { hint:Query hint:optimizer "None". ?city wdt:P17 wd:Q16; (wdt:P31/(wdt:P279*)) wd:Q515 . ?city wdt:P131* ?state .?state wdt:P31 wd:Q11828004 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } } ORDER BY ?cityLabel LIMIT 5000` – UninformedUser May 23 '20 at 08:07
  • @UninformedUser Thanks. It works. But, it returns too many duplicate city values.. :-( – Karuppiah RK May 23 '20 at 08:12
  • 1
    well, of course it does - one row per matching in the dataset - that's the nature of SPARQL. Do `SELECT DISTINCT` in the beginning of your query. Anything beyond - i.e. multiple regions per city have to be aggregated via a function. But as far as I can see it's not necessary here, so just add `DISTINCT` and you're done – UninformedUser May 23 '20 at 09:03
  • 1
    @UninformedUser Can you post this as answer? So, I can upvote & accept this as an answer. – Karuppiah RK May 23 '20 at 10:41

0 Answers0