Geocoding - get lat/long from 11000 address

Question

What I have:

• an Excel file with 11 thousand address (street + zip code + city)
• all address are from Portugal
• the address are not accurate (some address have the name street in a short way)

What I am looking for:

• get "geocodes" (latitude/longitude) of all address.
• using a opensource API, or a service of a website

What I will do with the data collected:

• with the "geocodes" I will create a file like a KML file with all address and some others information about each address
• the "geocodes" will be use in a commercial product by the company I am working.

In my research I have found some API and websites. But I am not sure if they suit my requirements

• Google Maps API
  • I think it was a limit if 2500 requests per IP per day.
  • I think I can not collect the latitude/longitude, only show the location in a Google Map webpage
• Yahoo PlaceFinder API
  • I think it is not free
• website Gisgraphy
  • The results on my test are not good for Portugal address
• website GPS Visualizer's Address Locator
  • uses Google and API Yahoo API, So have the same restrictions above

Some links I found:

• http://blog.programmableweb.com/2012/06/21/7-free-geocoding-apis-google-bing-yahoo-and-mapquest/#comment-189818
• https://developers.google.com/maps/articles/geocodestrat?hl=pt-PT#quota-limits

Answer 1

I think you will find numerous answers to similar questions on our site by searching the geocode tag.

A few that stick out are:

• Geocoding API Comparison
• Bulk Geocode 20 million records
• What reliable GeoCoding service would you recommend me?
• Is there an open source Geocoding tool which can be used commercially?
• Geocoding that's anonymous, good, and free - does it exist?

Answer 2

Maybe this will also work for you: How to find coordinates for a lot of locations based on the town or village name?

The presented programme uses Nominatim (Open Street Map). I expect it to also work well with street names in most cases.

Answer 3

Openstreetmap (OSM) API can help finding lat,lon information without any subscription/ API key requirement. I have also added a related answer in How to Geocode a Google SpreadSheet?

When the address is not accurate, it might bring unexpected results. You might need to get all search results to validate.

1. B1 = Address e.g 1600 Pennsylvania Avenue NW, Washington, DC 20500

2. Replace space with "%20" in address

   B2 =substitute(B1," ","%20")

3. Create search link with response in xml format:

   B3 ="https://nominatim.openstreetmap.org/search?q="&B2&"&format=xml&addressdetails=1"

4. Get lat and lon data in XML format and parse it with XPath filters.

   B4 =ImportXML(B3,"//place[1]/@lat") B5 =ImportXML(B3,"//place[1]/@lon")

5. Get all search results in rows B7 =TRANSPOSE(ImportXML(B3,"*"))

Implementation Example: How to find Latitude, Longitude of given address?