OpenStreetMap

Generating a list of all the cities in the world with Wikipedia links

Posted by PlaneMad on 8 March 2017 in English (English)

A small tutorial on how one can export a CSV of all the cities in the world with their associated Wikipedia and Wikidata pages. This is useful if you want to do some spreadsheet analysis of data from OpenStreetMap.

Overpass Turbo is a great way to quickly extract data from OpenStreetMap by querying tags. An easy way to generate the query is to type "city" or the specific tag "place=city" in the wizard. Since most cities are tagged as just a point node, we can remove the query for ways and relations.

Also instead of the default geojson output, you can use the CSV output format and specify the the data columns to export. The end query looks like this:

[out:csv(::type,::id,name,"name:en",wikipedia,wikidata)][timeout:200];

( node["place"="city"] ({{bbox}}); );

Try the live query (takes around 2 minutes to run) | View results

The same query with a geojson output gives the map view

You might also be interested in reading about how one can query a similar list from Wikidata.

Comment from dieterdreist on 15 March 2017 at 15:35

You'd better change the live example with something less ressource consuming, if it's for demonstration purposes only. Why do you think it takes 2 minutes?

Login to leave a comment