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:

What is your question - how do you geocode 11,000 addresses for free for commercial use? — Mapperz, Jun 11 '13 at 16:56
For Europe ViaMichelin is very good and stable for geocoding http://dev.viamichelin.com/web/api-rest/documentation/1.0/geocoding for free see Mapquest Open API http://developer.mapquest.com/web/products/open/geocoding-service — Mapperz, Jun 11 '13 at 16:58
Yahoo Geocoding (Boss) Placefinder is $4 for 1000 geocodes http://developer.yahoo.com/boss/geo/ [PlaceFinder=10,001 - 35,000 $4.00] so $44 in total. — Mapperz, Jun 11 '13 at 17:01
@mapperz thnaks for pointing to the free Mapquest Open API, looks very useful — Kurt, Jun 11 '13 at 17:22
possible duplicate of What reliable GeoCoding service would you recommend me? — RyanKDalton, Jun 11 '13 at 17:27
Mapperz, Yes my question is: how to collect all the 11000 geocodes (latitude/longitude) . — Ricardo PC, Jun 12 '13 at 08:20

score 6 · Answer 1 · edited Apr 13 '17 at 12:34

6

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

A few that stick out are:

edited Apr 13 '17 at 12:34

Community

1

answered Jun 11 '13 at 17:24

RyanKDalton

23,068
17
110
178

Good job with the recursive answer! Thanks for including some specific answers. – Jeffrey Jun 11 '13 at 18:20

score 1 · Answer 2 · edited Apr 13 '17 at 12:33

1

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.

edited Apr 13 '17 at 12:33

Community

1

answered Jun 12 '13 at 19:42

Gideon

1,120
3
18
34

score 0 · Answer 3 · answered Dec 23 '20 at 20:22

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.

B1 = Address e.g 1600 Pennsylvania Avenue NW, Washington, DC 20500
Replace space with "%20" in address

B2 =substitute(B1," ","%20")
Create search link with response in xml format:

B3 ="https://nominatim.openstreetmap.org/search?q="&B2&"&format=xml&addressdetails=1"
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")
Get all search results in rows B7 =TRANSPOSE(ImportXML(B3,"*"))

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

Geocoding - get lat/long from 11000 address

3 Answers3