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
Recent Posts
- 11 Parallels Between Architecture and Interface Design
- Analyticstime!
- Thrown to the Ad-Wolves... or, Learn from My AdWords Mistakes
- Review: 5 Options for Embedding Charts in a Web Page
- Godin Dumps on Bar Charts; Data Visualization Record Falls to 1 and 1
- Why Analytical Applications Fail
- Microsoft's Executive Dashboard... Magnifying Glass Required
- Clever != Smart Naming: Don't make your customers work
- Mashing Google Analytics With External Data
- 10 Minute Reviews: Open Flash Chart
9 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
said:
Add a comment