Today I found myself sitting in a classroom discussion when the need arose to figure out how far each of 344 survey respondents had come to the place where the survey was being conducted. The survey included the home zip code of the subjects, and we knew, of course, the zip code of the survey location.

While I was Googling for how to best geocode the zip codes, I happened to also ask in the #geo irc channel (on whether there were any free distance calculation web services. Rich Gibson, who constructed piped up and said he was adding to the API for its web services and did people have any requests. What luck! I asked for something that given two zip codes would return a distance.

Not long thereafter, here’s what he came up with:

<a href=";zip2=02139">;zip2=02139</a>

which would return the distance between Petersburg, KY and Cambridge, MA.

So I was off to the races! I copied the list of zip codes from the Excel spreadsheet and pasted them into an Emacs buffer, then saved them as ‘zips’. Then in the shell (I use tcsh…):

First to get rid of duplicates:

sort zips | uniq > zips2

Then, loop through them. I put line breaks with continuation characters into the geocoder line to fit inside this blog post.

foreach zip ( `cat zips2` )
 echo ${zip} `curl \
"${zip}" \
 | sed -e 's/.*=//' | sed -e 's/ miles$//'` >> distances

And that’s it! I pasted the results back into the spreadsheet, got rid of the three bogus zipcodes in the data that resulted in errors from, used VLOOKUP to pull out the distances and I was done.

The service returned errors three times. I used the USPO zip code reverse lookup to verify that the codes were indeed bogus.

That’s my kind of web service. No WSDL, WADL, or WS-anything.

(Yes, I could have used Perl, Python, Ruby, etc. but I learned shell commands in 1981 before we had all that fancy stuff and it’s the first thing I try.)

Posted in Geo
4 comments on “
  1. Sean Gillies says:

    You probably could have done it all without even leaving emacs, like I was doing at More and more, I think curl-ability is the ultimate test of a service’s usefulness.

  2. Allan Doyle says:

    Well, I did do it in Emacs, sort of, using shell-mode. My elisp is not up to snuff to do it in Lisp… I suspect there’s even a way to have done it in Excel directly.

    I agree wholeheartedly with your curlability metric of service usefulness.

  3. It would have been a breeze with Resolver One – the programmable spreadsheet (spreadsheets become programs in real time and can be intermingled with real code). You should check it out (free to try).

1 Pings/Trackbacks for ""
  1. […] growdesign 27 days ago3 votesChiefMall Contractor Search Engine>> saved by kwill 33 days ago5>> saved by boofydb 37 days ago2 votesGoogle Map API JavaScript Example>> saved by clotho56 37 days […]