October 26th, 2005

Filed under:
Technology
6 Comments »

Excel Yahoo! MapMaker

Yesterday, while considering going to a local public house to watch some of the Canucks vs. Minnesota pay-per-view game, I consulted this list of pubs showing the game. It’s just one big, unsortable table covering all of BC. Unless you only looking to confirm that a particular pub is showing the game, it’s nearly useless.

I wondered aloud (as I was by myself), “wouldn’t it be cool if somebody could feed all this data into Google Maps, and I could use that to determine which pubs were closest.”

Today, via Lifehacker, I discovered the very promising Excel Yahoo! MapMaker. Here’s what it does:

Yahoo! MapMaker for Excel is a Microsoft Excel template that enables any user of Excel to plot data on a Yahoo! map without programming. Just enter your data into the spreadsheet, and after a few clicks, whammo, instant map. No web server required. You can provide address/city/state, latitude/longitude pairs, or a column containing nothing but ZIP codes. Optionally you can assign titles and descriptions to each map point.

That’s exactly what I want to do! The gods were finally listening! And it’s so simple even an idiot like me can do it. Alas, I tried it out, and it balked. I’m guessing that it only works in the US. If anybody’s interested to play with it, I entered 5 pubs in this excel file. After the jump I’ll dump the RSS (RSS?) that it spat out.

NERDY QUESTION: I put <pre> tags around my RSS markup below. How come it isn’t displaying the entire file?

UPDATE: Jeffrey McManus, the guy who built this nifty spreadsheet, heard my plea and has developed a version that supports Canadian addresses. I gave it a try by adding the addresses from the Canucks’ list. Unfortunately, I had to go find postal codes for each address, so I only did A through C. Still, it works like a charm. Unfortunately, I can’t link to the map because Yahoo gives me the rare 414 error message: “The requested URL’s length exceeds the capacity limit for this server.” That’s not Jeffrey’s fault, it’s Yahoo’s, and it’s peculiar. Didn’t they figure somebody might want to work with an array of 30+ locations using their web service?

This time I saved it as an Excel template file. If anybody’s keen, feel free to find some more postal codes for Vancouver PPV locations.



http://developer.yahoo.net/maps/
Your data on a Yahoo! map.
en-us
Wed, 26 Oct 2005 8:24:52 GMT

Foo14th Avenue Pub 32516 - 14th Avenue
Mission, BC
V2V 2N7

Foo25126 Sooke Road
Victoria, BC
V9C 4C4

Foo350 West Cordova
Vancouver, BC
V6B 1C9

Foo41028 Hillside Avenue
Victoria, BC
V8T 2A3

Foo514817 - 108 Ave
Surrey, BC
V3R 1W2

Comments: 6 Responses so far

double-plus-ungood
October 26th, 2005
12:35 pm

Do you mean “Why don’t the xml tags display?” If so, I think it’s because <pre> preserves line endings and spacing, but tags are still interpreted. To make it display properly, you need to change the < and > characters to &lt; and &gt; inside the <pre> tags. I tried it with a copy of your page and it worked, although I’d shrink the font if I were you.

[Reply]

Jeffrey McManus
October 26th, 2005
1:05 pm

Isn’t what you really want?

I’m fixing the Excel template to accept a country so it will work with US and CA locations. I’ll post an update to my blog soon.

[Reply]

Patrick Dinnen
October 27th, 2005
6:39 am

Hi Darren
I just played with the latest Excel template, with Jeffrey’s Canada-friendly update. It looks like you don’t actually need the zip/postal code. I just deleted the zip column in your PPV data and Yahoo seemed to map it without a problem.

Couple of notes, only the first 100 venues get mapped (looks like that’s a limitation of Yahoo’s api). The MapMaker script looks for a continuous range of data around the selected cell, and assumes that’s what you want to map. So, you need to make sure there are no gaps (empty rows or columns) in the range of data you want to map. I deleted the empty row between ‘Croatian Cultural Center’ and ‘Darby D. Dawes’, otherwise only the data up to the gap was mapped.

[Reply]

Darren
October 27th, 2005
8:12 am

Patrick - Cool, thanks for that. I’ll try it without the postal codes. I embedded that empty row to limit the data set. Still, 100’s pretty good.

[Reply]

Jeffrey McManus
October 30th, 2005
10:38 pm

New version of the template’s officially out: http://www.well.com/user/jeffreyp/mapmaker/

[Reply]

Canucks Pay Per View Bars and Restaurants in Google Maps
March 25th, 2008
11:57 am

[...] and half years ago, I described a local search problem: Yesterday, while considering going to a local public house to watch some of the Canucks vs. [...]