Getting full list of cities and regions from OSM

Question

I need to match cities/postal codes from a large dataset with their regions (NUTS3 if available, otherwise administrative divisions as can be found here https://en.wikipedia.org/wiki/List_of_administrative_divisions_by_country). Cities can be all around the world and have all sizes.

I've come across that OSM provides regions for all(?) cities. Searching for a small village in Germany ('Unterkirnach'), I get the following result: Village Unterkirnach, Verwaltungsgemeinschaft Villingen-Schwenningen, Schwarzwald-Baar-Kreis, Regierungsbezirk Freiburg, Baden-Württemberg, Germany

That's all the information I need, namely administrative divisions on several levels, and it also seems to work for small towns, e.g., in China.

However, I would need to extract a list/table of towns and their assignment to regions worldwide in order to upload it to my database and to look up the regions of the cities in my dataset. At this point, I do not want to do any 'fancy' stuff I do not understand, I simply want to create a table with as many cities as possible and columns for their administrative regions on different levels.

Does anyone have an idea how to do this?

Answer 1

You could use the useful OSM key [is_in=*] to get a list of the administrative regions where your place is situated.

You can query the OSM data using the overpass API. You can output some data directly in a csv table. See the following query to request the name of the place + the is_in tag for Unterkirnach in Germany and get the result in a csv format. Here's the overpass-turbo link: http://overpass-turbo.eu/s/qcO

[out:csv(name, is_in)][timeout:50];
{{geocodeArea:Germany}}->.searchArea;
(
  node["name"="Unterkirnach"](area.searchArea);
);
out body;

For querying several places, you may run some overpass query into a loop. See this post for some inspiration.

However, this won't work if there is no is-in tag. But it should be available for major cities worldwide.

J.