GIS for the rest of us… or Another Excel-based Mapping Tool

A client recently asked us if we could help him find the five locations that have the most customers nearby. I dug into the annals of our blog to find two tools that together ("wonder twin powers activate... shape of mini-GIS solution") could solve this problem:

1. Juice geo-coding tool lets you get the precise latitude and longitude for a list of addresses and plot these locations in Google Earth.

2.ZIP code finder lets you enter a US address and returns zip codes within a certain number of miles of that address.

Led by our resident Excel guru David, we combined these features into an Excel tool that lets you answer common location-based questions such as:

  • How many libraries are within 10 miles of Worchester, MA?
  • Which cities have the most libraries within 10 miles? 20 miles?
  • Could I see the library locations on a map, please?

Here’s how it works:

1. Drop your location information into the "Geocode data" tab. We are using the Yahoo geocoding service.

2. Go to the results tab and fill in the table with your selected location addresses and distances. Press "Calculate."

Download the Excel file here: Juice Distance Tool

We had a couple other features in mind, but thought it would be better to get some reader reaction before we loaded it up.

This tool is released under the MIT license.

Finding zip codes close to an address

Just got an email from someone we work with:

I’m researching options for being able to find zip codes within a specific distance from a location. Thus far I’ve found a couple of desktop applications, ZIP Code Download’s Lookup GXE and Xionetic’s ZIPFind Deluxe, both for about $200.

I was curious if you had any experience or thoughts on either of those, or if you had any other product suggestions for this functionality. As an example, we’re looking to be able to define an audience consisting of contacts with ZIP codes between 20 and 30 miles from Chicago.

This is a pretty common problem. The applications referenced don’t seem to bring a lot to the table. Why not stick with the tools you already know. Geocoded zip code lists aren’t hard to find (the Yahoo Geocoder, for instance, lets you build your own).

So, here’s an Excel-based tool that lets you enter a US address and returns zip codes within a certain number of miles of that address.

Find Zip Codes within

Address lookup is courtesy of the free Yahoo geocoder.

Note: This tool was updated to use the new Yahoo geocoding service.

Excel Geocoding Tool v2

Update (March 2012): Max Rice has generously updated the Excel Geocoding Tool. It is shared on Github here. I’m happy to announce a few small revisions to our Excel geocoding tool. The tool takes a list of addresses and will look up the latitude and longitude of those addresses. The addresses can then be exported as a Google Earth map.

A user pointed out that the tool wasn’t looking up zip+4 codes properly in Yahoo and this problem is fixed along. Also, I’m sorry to report that Yahoo has lowered their limit of free geocodes to 5,000 per day from 50,000 per day.

Geocoding Tool v3.1.xls

Geocoding tool roundup has put together a clean-looking front end to Yahoo’s geocoding service if you need to map small numbers of addresses.

Here our Excel-based tool that performs similar duty; it takes a list of addreses, geocodes and maps them. We’ve used this tool to map thousands of names. It’s quite a bit quicker than the batchgeocode web tool, though not as flexible in its mapping output. Enjoy.

Note: I’ve just posted an update of the Excel geocoding tool here.