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:

image

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

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

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

    image
  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.

image

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

13 comments:

  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 (http://code.google.com/apis/maps/documentation/javascript/v2/reference.html). 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!

    ReplyDelete
  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!

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

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

    ReplyDelete
  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?

    ReplyDelete
    Replies
    1. I would recommend you contact the developer.

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

    ReplyDelete
  7. I need help setting one of these up internally w/out using a google API. Thinking census bureau tiger shape files (or something similar) should work. Need to reverse addresses into lat/longs for a project processing well above the API limit. Fair compensation can be discussed. Help! cgabler@compassperformancegroup.com

    ReplyDelete
    Replies
    1. Corey, have you looked into Alteryx?

      Delete
    2. only briefly, as far as i could tell from the demo, it's just a prettier MS Access? Unless it is able to handle crazy shape files and such as well?

      Delete
    3. I'm not a Alteryx users, so I don't want to steer you in the wrong direction. I'd recommend you connect with Chris Luv from The Information Lab.
      Twitter: https://twitter.com/ChrisLuv
      Information Lab profile: http://www.theinformationlab.co.uk/team/

      Delete