GIS for the rest of us... or Another Excel-based Mapping Tool
By Zach Gemignani
May 17, 2007
Find more about:
geocoding
gis
googleearth
mapping
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.
Subscribe & Browse
- Visitors Guide Our most popular posts and tools
- Subscribe via RSS or Email
- Browse by Topic, Date or Author
Explore our world
Recent Posts
- Are you using information effectively?
- Predictive Analytics: Interview with Eric Siegel, Ph.D.
- Important Dialogue
- Automated PowerPoint Generation, or Making a "Slide Factory"
- Depth and Discovery: Powering Visualizations with the Google Analytics API
- Juice's Simple Font Framework
- Designing Great Dashboards - Part 3
- Airline and Airport Traffic and Delays: A JuiceKit Visualization Demo
- Designing Great Dashboards - Part 2
- Designing Great Dashboards - The Book
10 comments | Show all comments only the last 5 are shown
Bazily said:
Cool tool. I built a geocoding tool for the web off of Google Maps and Yahoo Geocoding API, hosted here:
http://www.bazily.com/freegeo
It'll do thousands of geocodings in a snap!
Recently I was asked to use that experience to figure the distance between 5,000 retail locations and 30,000 competitors. Awesome.
Hagge said:
Doh.
F-Secure thinks the Excel-file contains an unknown virus.
Hagge said:
I submitted the file for whitelisting, or whatever it is that they do to it, and the next f-secure database-update will fix the erroneous detection.
Chris said:
This is an excellent tool that offers me a quick way to visualize my data. However, the location names do not seem to load into the Google Earth file. I'm using Excel 2003 on XP.
Aran said:
Cool file. I have a long list of zip codes that I would like to experiment with. I wonder if there is a way to tweak the file so that it will geocode a zip code if the other address cells are left blank.
Zach said:
Last I tried, Yahoo's geocoding service (the one baked into this tool) returns a lat/long value for just a zip code.
kelvin said:
This is a GREAT TOOL! but ya, I can't see the site names from the JUICE tool...however, geocode 3.1 xls works, BUT it ends with a debug error after it completes the macro. the end result file provides the site names tho, which is VERY HELPFUL for me! is there a way to plugin the address for each site name/location? that would be ideal.
I use this tool to help me assess the number of apartment complexes in a given area. VERY USEFUL for me to get a quick idea of competition in a particular area i'm looking to invest in!
Jerry said:
Chris (#4) It has nothing to do with Excel 2003. The outputKML macro is referring to wrong column numbers. I changed my Geocode tab to make the Name/Description column into two separate columns & then changed two lines to reflect it:
name = CStr(ActiveSheet.Cells(r, 7))
description = CStr(ActiveSheet.Cells(r, 8))
Jerry said:
If you're behind a firewall, the calls to http.send will fail ....
Function yahooAddressLookup(addr As String, Optional Row As Long) As String
' perform RESTian lookup on Yahoo
Dim response As String
URL = "http://local.yahooapis.com/MapsService/V1/geocode?appid=yahoo_test&location=" & addr
'Create Http object
If IsEmpty(http) Then Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
'Send request To URL
http.Open "GET", URL
http.send
....
....
Only way around it is to add code for http options to work with proxy authentication
Eric said:
Do you guys know how I can implement something similar to your Excel geocoder into MS Access where I can use a table of address records instead of a sheet of address rows in Excel.
said:
Add a comment