Bulk Geocode 20 million records

Question

Are there any free or reasonably priced databases for the US which can be searched and return latitude and longitude information?

Answer 1

For that many records, don't even consider a web service. They will throttle or cut you off before you can finish your task.

So then your option becomes to run it locally, and for that you have several commercial or free options.

The free options will use the census TIGER dataset which you will need to load into a spatial database. You can find libraries that geocode against TIGER for PostGIS or even sqlite. Heck you can even use ArcGIS to geocode against TIGER. Of course, ArcGIS is not free, which brings me to the next commercial options. If you do have an ArcGIS license chances are you have StreetMap DVD with a TeleAtlas (I mean Tom Tom) or Navteq dataset. That depends if you got StreetMap Premium bundled. Any of those two datasets will probably give you more consistent results than TIGER.

Do yourself a favor and make several copies of the street database once your data is loaded and run the geocoding process on several machines with a subset of the input data. Dont try to run it on just one machine or you will be waiting for days for it to finish not to mention that most likely whatever process you run will probably leak memory and crash several times before it finishes. This means that you want to have different checkpoints for your process.

Answer 2

if your geocode request does not exceed 2,500 per day, you can use The Google Geocoding API . you should take a glance to api, it can return to results as json or xml.

Usage Limit:

Use of the Google Geocoding API is subject to a query limit of 2,500 geolocation requests per day. (User of Google Maps API for Business may perform up to 100,000 requests per day.)

Example:

http://maps.google.com/maps/geo?key=yourkeyhere&output=json&q=520+3rd+Street+San+Francisco+CA

Example Result:

{
  "name": "520 3rd Street San Francisco CA",
  "Status": {
    "code": 200,
    "request": "geocode"
  },
  "Placemark": [ {
    "id": "p1",
    "address": "520 3rd St, San Francisco, Kaliforniya 94107, Amerika Birleşik Devletleri",
    "AddressDetails": {
   "Accuracy" : 8,
   "Country" : {
      "AdministrativeArea" : {
         "AdministrativeAreaName" : "CA",
         "SubAdministrativeArea" : {
            "Locality" : {
               "LocalityName" : "San Francisco",
               "PostalCode" : {
                  "PostalCodeNumber" : "94107"
               },
               "Thoroughfare" : {
                  "ThoroughfareName" : "520 3rd St"
               }
            },
            "SubAdministrativeAreaName" : "San Francisco"
         }
      },
      "CountryName" : "USA",
      "CountryNameCode" : "US"
   }
},
    "ExtendedData": {
      "LatLonBox": {
        "north": 37.7826364,
        "south": 37.7799384,
        "east": -122.3942267,
        "west": -122.3969247
      }
    },
    "Point": {
      "coordinates": [ -122.3955757, 37.7812874, 0 ]
    }
  } ]
}

and you can check some example link for geocoding from google:

1. Single Code

2. Reverse GeoCoding

3. Starting with Google GeoCoding

i hope it helps u

Answer 3

Why do you need to geocode the 20,000,00 addresses? Can you start with towns or zip codes? The results will not be perfect, but for some applications it will be good enough.

20 Million records at 2,500 per day will take years, at 100K per day that will still take months.

Answer 4

I work at SmartyStreets (an address verification company). Our service is free for nonprofit organizations (school, library, church, charity...), so if you fit that classification, there's no charge for our unlimited service.

Ragi recommends against a web-service, however, our API can easily clean, standardize and geocode 20 million addresses for you in about 5 hours (approximately 1000 per second). Some of that time will depend on the speed of your machine (how many cores you have) and your network connection (don't try it over 3G, but a standard broadband connection will do just fine).

Just wanted to point out that it is certainly possible with a webservice.

Answer 5

I'm guessing you want to Geocode but not pay anything for it? There are a bunch of services that you can geocode 20 million records on, but it will cost you. Esri, Pitney Bowes and other offer these services via subscription or on a cost per x geocodes. 20 million isn't trivial, but I'm assuming there is a business case for this.

Answer 6

I used this walkthrough describing how to build a postgis geocoder using 2010 TigerLine data. I'm running it right now - it's not fast, as it's going to take 3 weeks to geocode 2 million addresses.

However, it's free, unthrottled, and took someone with minimal coding and postgres skills less than 2 days to set up and load with one (large) state's data to begin geocoding. I've also done absolutely no postgres tuning for the system and it's running over NFS mounts, so I suspect there's one or two orders of magnitude worth of performance gains I could get out of it if I needed to.

Rather than using web services, I loaded all my addresses into the postgres database, and then I'm running a quick and dirty perl script to geocode them all one at a time:

perl -e for ($i=1; $i<[max_key_value]; $i+=1) 
   {printf "UPDATE source_addresses
               SET (rating, new_address, lon, lat) 
                     = (g.rating, pprint_addy(g.addy), 
                       ST_X(g.geomout), ST_Y(g.geomout) ) 
              FROM (SELECT DISTINCT ON (address_id) address_id, (g1.geo).* 
                      FROM (SELECT address_id, (geocode(address)) As geo 
                              FROM source_addresses As ag 
                             WHERE ag.rating IS NULL and address_id = $i 
                           ) As g1 
                     ORDER BY address_id, rating LIMIT 1
                   ) As g WHERE g.address_id = source_addresses.address_id;\n"
  } | psql -d geocoder 

(line breaks solely for readability)

So that generates a "geocode the address with this ID value and use the best match" update statement, and pipes it to psql to do it. It only attempts to geocode address with no rating - i.e. ones it's not already geocoded. So it's restartable, and each one is done independently.

Answer 7

As you'll be geocoding US addresses, I think the Street Address to Coordinates tool from the Data Science Toolkit should work well for you.

This API takes either a single string representing a postal address, or a JSON-encoded  
array of addresses, and returns a JSON object with a key for every address. The value 
for each key is either null if no information was found for the address, or an object 
containing location information, including country, region, city and latitude/longitude 
coordinates.

You might want to download the virtual machine though and run it from your own hardware. That way,you don't have to worry about API limits plus you control it too. Oh and did I say that it's free? ;-)

Answer 8

I have not seen any reference to the level of precision you need but I am assuming that you want rooftop long lat or close to that. The quality of the input address can be a factor as well. A well cleaned list of addresses will code better and faster than a list of incomplete or bad addresses. Also, is a 90% hit rate acceptable or do you need all 20 million addresses coded? I do not have a free solution, but there is a relatively inexpensive one that I know of and use. ZP4 from Semaphore Corp, http://www.semaphorecorp.com/ offers a address scrubber/geocoder and add ons which will process the addresses and return a cleaned address, a flag which indicates if the address is USPS deliverable, and the long lat for the ZIP+4. ZIP+4 precision is generally close to rooftop precision in builtup areas (correct side of the street and on the correct block) and not close in rural areas. The cost for a 30 day license is $120. After that time the address scrubber will still function, but Delivery Point Validation (DPV) and the geo returns will not function. With a relatively quick computer, built within the last two years, and all of hte data stored and accessed locally, the 20 million records should be done in about 10 days. I have been geocoding addresses for the last 15 years and I have beeen using ZP4 for most of that time. Before they offered long-lat or DPV, I used it to pre clean addresses prior to geocoding.

Answer 9

The location of your data is very important because the quality of web-services change (precision, scale etc...) I geocode my adress from google api, facebook api and ex-simplegeo with my geodatabase.

http://blog.programmableweb.com/2012/06/21/7-free-geocoding-apis-google-bing-yahoo-and-mapquest/