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