How To Batch Geocode Address Identified Data and Load It Into Google Earth FAST!
What seems like ages ago I was in charge of an environmental risk geocoding effort for a company that has been sold and sold and sold again until it no longer exists. I have geocoded 10's of thousands of environmental risks (CERCLA, SWRB-site, NPL, etc.). Before that I geocoded population estimates displayed as circles overlaid on a census map. So I know a thing or two about geocoding.
For you rookies - address geocoding is taking a valid street address (2002 Elm St, Any Town, CA 91007) and determining the lat/lon. If you have a single address, type it into Google Earth or any similar programs and, most often, you are directed to the appropriate location on a map. If you use on-line mapping programs to locate a destination - you are doing address geocoding.
Let's look at a much larger database, all of the service stations located in California (20,000+). How do we geocode that database? You could try it "one-at-a-time". Some of you have the time and the commitment, but I don't. To address geocode 20,000+ service stations one needs to batch geocode - let sophisticated software do the job. The problem is the cost. 20,000+ sites could cost between $5,000 to $20,000 to batch geocode. You would end up with two additional columns of information - a lat and a lon for the address. Some companies pay the price without blinking an eye. To be able to reliably locate, on a map, a fixed asset is an important aspect of doing business in today's world. But I'm cheap and I know of a FREE batch geocoding service. Let me show you how to address geocode a portion of the service stations located in Alameda county California.
Cal Energy - Service Stations
1. First we have to locate the data. At the California Energy Commission web site they have, in Excel format, a listing of 20,000+ service stations located throughout the state. That is what I'll download to gather my data from.
MS Excel Data Prep - Delete and Rearrange Columns
2. Next I'll prep my data in MS Excel. First I copy and paste the Alameda county data, load it into a new spreadsheet. I will go through the data and delete any unnecessary columns of information. I then copy the data to address geocode and paste it into Batch Geocoder.
Batch Geocoder - Load Data, Validate, Process and Create KML File
3. Now for the batch geocoding site, which is called Batch Geocoder. A motivated hacker built this nifty package that allows one to geocode up to 500 address identified data points using Yahoo mapping. Simply put, you load, validate and then process your address identified data and this program geocodes and appends a lat/lon to the record. Cool!
Google Earth - Alameda Service Stations after Icon Change
Google Earth - Creanova Inc Facility
4. After creating the Alameda Service Station KML file from within Batch Geocoder, I double click this new file and it shows up in Google Earth plotted with little grey circles and service station names as a temporary data layer. I like the nifty yellow gas pump icon, so I modify the main file and switch all of my data points to the gas pump icon. And there you have it, address geocoded data in Google Earth done fast and done free.
Pretty cool, huh?
Enjoy!
Labels: batch geocode, Batch Geocoder, geocode, Google Earth, MS Excel
