Obtain relation ID from city name in a spreadsheet

Question

Hi,

I have a spreadsheet where in column A are listed some italian cities, is there a formula that can retrieve the relation ID of that city automatically? To do it I'm actually going to nominatin, searching the city, select first result and copy the relation ID. Is there a way to automate this work?

example:

COLUMN A COLUMN B

Milano 44915

L'Aquila 41842

Nissoria 39323

Thanks

Answer 1

This could, on the Unix command line, be achieved by a combination of using curl to send a request to Nominatim followed by processing the JSON result with the jq utility, for example:

curl -s "https://nominatim.openstreetmap.org/search?q=Milano&format=json" | 
  jq '.[] | select(.class=="place") | select(.type=="city") | 
    select(.osm_type=="relation").osm_id'

You might have to toy with the jq expression a bit to achive the best result. Of course, you can do the same in a programming language of your choice - "request this URL", "decode JSON", "find objects that are cities", "extract their osm_id" - this should be possible with most common scripting languages.

Obtain relation ID from city name in a spreadsheet

Related questions