How to find coordinates for a lot of locations based on the town or village name?

Question

I have got an Excel spreadsheet with roundabout 15,000 locations, which are only identified by the towns or villages name and the country. I am intending to present these on a world map.

Therefore, I need to know, whether there is a tool, which can find the coordinates for me based on the location's name.

Pasting every name into google maps is no solution due to the high number of locations.

Thank you for your help and I wish you a satisfying Christmas!!

Answer 1

The GeoNames API would be a good place to start if you are able to write a script to pull the data for you. There is a nice example, written in Python, in this github project.

You can also download the GeoNames data set here. Natural Earth also provides a data download.

Answer 2

I decided to develop a python script using the Nominatim tool by Open Streetmap to find the coordinates of the locations and it is working pretty good.

I made a screenshot, because for some reason, I am not able to add the code here:

There are some things you should not forget, when using this script:

• According to the rules of Nominatim you should have a 1 second time out between every request (included in the script)
• Add your mail adress in the request, thus you can be contacted as soon as problems appear
• You will need to create an infile and an outfile .txt file and fix the paths in the script
• The infile should be formatted in "UTF-8 no BOM" (you can use editors like notepad++ for this)

A friend developed the script for me, therefore I am not able to answer you that much questions about it. Nevertheless, if there is a question, I will do my best and also ask him.

Answer 3

There are a lot of options here. While GeoNames is a good option, and you may be able to use the google maps API (depends on your legal needs - read the small print to know how the google terms apply to your particular circumstance), you also may be able to use one of the OSM options:

• Overpass API. Usage examples of query by exact name are described in the query documentation.
• Nominatim. Here is a usage example: http://nominatim.openstreetmap.org/search?q=quairading&format=xml (or format=json if you'd prefer that).

Again, for 15k locations, please consider the terms of use for public servers.

Answer 4

Or if you've knowledge of the R language Here is one of many examples using the rjson package and the google maps api:

require(rjson) # load rjson package
getCoordinates <- function(address) {
url <- paste("http://maps.google.com/maps/geo?q=",address,"&output=json",sep="")
map_data <- fromJSON(paste(readLines(url),collapse=""))
coord <- map_data$Placemark[[1]]$Point$coordinates[1:2]
return(coord)
}

# example query
address <- paste(data$zip[1],"+",data$city[1],"+",data$state[1],sep="")
getCoordinates(address)