There are a lot of free geocoders out there on the Internet, but they all have their issues. Either a limit to the number of addresses you can geocode, or they are really slow. So a while back I built a geocoder that uses a popular mapping service, but does so in a way that allows for much faster results. It can do up to 3000 addresses in around five minutes. If you open the page up again in another browser window, it can do another 3000 in that same five minutes.

Normally I enjoy describing how what I built works, but I want to keep this a secret so that its magic continues to work.

Note: The local file doesn’t seem to work in Chrome due to some strange ‘same-origin’ security concerns . Use another browser, or host the file on a web server running locally on your computer.

Geocoding Addresses

Here are the basic steps to make it work:

Step 1) Combine your address fields into a distinct text key. If you have separate ADDRESS, CITY, STATE, COUNTRY fields, create a new one that combines them all together — like “600 Congress Ave., Austin, TX, United States of America”. This gives you a key to join your results back on.

If you are using Excel, the & symbol is the text concatenate operator. So you can put columns together with commas like: A2&”, “&B2&”, “&C2

Note: Remove duplicates from your set, so that you only have one line for each address. In the end we are going to JOIN them back together, and multiple rows in the geocoder will cause row duplication in your result set.

Step 2) Copy all of those address into the geocoder page. Leave the default option “Gecode Single Addresses”

Step 3) Press “Geocode My Addresses”. The page will show a countdown as the results come back

Step 4) Once the results are in, a popup will appear with them

You’ll notice this is a PIPE ( | ) delimited file — commas are just too common in addresses to use as a separator. Whether you process further in Excel, load into a database, or stick directly in Tableau, you want to make sure you are converting on | rather than commas or tabs.

The first column is the exact address as you passed it in originally. This lets you use Tableau’s JOIN functionality on these results. The second column is the address that was found to be closest by the geocoder. If you have full addresses, they should match, but if you put in something like a city name or a ZIP code, the second column can help you understand WHY you are at a certain point. The lon(gitude) and lat(itude) columns should be self-explanatory.

Step 5) Copy the results either to a text file or Excel.

Step 6) If you copy to Excel, you can split using “Text to Columns” under the Data tab. Choose “Delimited” then Other: | . Now you can use Tableau to JOIN on the address field with your original data set, and you’ll have longitude and latitude for all of the points.

Driving Distance and Time

The same magic is available for driving distance and expected time. In this mode, you put in your two addresses in comma separated format for each address, with a | between the two addresses:

333 West Camden Street, Baltimore, MD, 21201|4 Yawkey Way, Boston, MA, 02215

4 Yawkey Way, Boston, MA, 02215|333 West 35th Street, Chicago, IL, 60616

333 West 35th Street, Chicago, IL, 60616|333 West Camden Street, Baltimore, MD, 21201

Reverse Geocoding of Lon,Lat pairs

May 13, 2016: I added in another mode, which allows you to put in rows in the form of “Longitude,Latitude” like: -122.32000,49.03000. It should return back to you a full address, which you can split into the constituent parts if you want to do aggregated geographic analysis but only have exact location point data.