November 8, 2011

Batch Geocoding: Convert addresses or locations into latitude-longitude coordinate pairs

I’ve been adding customers to the Penetration Reporting I presented at TCC11 (word is spreading and people love it!), but there are often records in our internal systems that do not match up with the master address list for our customers from Nielsen Spectra.

One of the great features of Tableau is data blending and this project is a perfect example of how you might use it. I have my sales and internal customer list in SQL server, but the location information, including latitude/longitude are in an Excel file.  Tableau allows me to integrate these data sources via a key field, store number in this case.

As I referred to earlier, this works perfectly for ~95% of the records, but there are ALWAYS stores in our internal system that do not exist in Spectra.  Here’s a sample of missing stores:


To prepare the data to address this problem I take the following steps:

  1. Concatenate the address fields together in Excel with the formula CONCATENATE(TRIM(Address)," ,",TRIM(City),",",TRIM(State)," ",TRIM(Zip))
  2. Copy all of the rows in the new “Full Address” column

Now the fun and magical part begins.  A colleague led me to the tool Batch Geocoding, which basically takes text strings and returns the latitude/longitude coordinates.  Here’s how it works:

  1. In the “Input” box, paste the data you copied from Excel above

  2. Choose your output format and click the “geocode” button

  3. Watch the magic as the tool populates the Output box.
  4. Copy all records from the Output box and paste into Notepad

  5. Save the Notepad file in txt format
  6. Open the TXT file in Excel
  7. Copy the records from the TXT file and append to the end of the master customer list from Spectra

That’s it!  So simple!  I have no idea how it works, but it does.  Of course, it’s not going to be 100% accurate, because the addresses may not exist, but it does tell you how well it was able to match the records.


Definitely keep this link in your toolkit.  I’ve also added a link on the right side of this blog under “Useful Data Sources”.


  1. Andy, I got curious and started digging through the Javascript the site is using and found that it's doing the geocoding with the Google Maps Javascript API ( My first instinct when someone says they're not sure how something works is to tear it apart and find out how it works. :)

    Most of these free geocoders online are either using Google Maps or Yahoo APIs. Because of this, they typically have a batch limit. Previous geocoding sites I've used have had 1k and 2k batch limits on these APIs. Did you run into this with this site? If not, it must be doing something different.

    Anyway, interesting find!

  2. I'm the site owner. I am using the API a bit differently and have reports of people running batches in excess of the 2500/day limit. Not sure if my alternative method is responsible for the permissive data crunching, but I kind of doubt it. That certainly wasn't the intent!

  3. I did a batch of over 3300 with no issues.

  4. Very helpful Info, thanks so much!
    Saved me $100 that my programmer wanted =D

  5. Hi there just tried using your GeoBatch software and had some issues. I inputted about 1300 locations and the whole thing slowed down at around 475. Not sure how to speed things up. Should I convert smaller batches?

    1. I would recommend you contact the developer.

  6. This tool is amazing! Thanks for the info.