Creating a map from a spreadsheet using Google Docs and GPSVisualizer:

Note: this is the first in a series of several detailed multimedia walkthroughs that I’ll be publishing for my students and anyone else who’s interested. I’ll also be dealing with Spreadsheet Mapper 2.0, a tool from Google for both Google Earth and Google Maps, before moving on to other multimedia storytelling tools.

1. Start with some data that includes names and addresses.

Finding data was the hardest part: the first half-dozen places I went already had their locations tied to maps and there was no raw list of addresses. When I finally found something, I needed to copy and paste it into TextEdit and do some cleanup, so I wound up with a list of names and addresses, one to a line, separated by a tab space:

Company name[tab] Address

2. Open a new Excel spreadsheet

Set up two headers — Name and Desc — over the first two columns and then copy the data in. Because of the tab I used in TextEdit, the list came neatly into the two columns. (I could have gone directly into Google spreadsheet, but Excel has some deeper features that I’ll use next.)

3. Copy the content of the “desc” column and go to www.gpsvisualizer.com/geocoder.

Paste the column of addresses into the input box. Press “Start geocoding,” and it builds the list of latitude & longitude coordinates, plotting each on a map on the page. The map is handy: If a flag appears where you’re not expecting it to, there may be a mistake in an address.

5. Once the addresses are all geocoded…

…copy the results from the output box. Be careful to copy only the address data (the first two lines of my output were header information, so I ignored them.) In Excel, create two new headers — Lat and Long — and paste the info from the website into the first cell of the Lat column. The result is latitude, longitude and address in a single column.

6. To separate them

Use Data->Text to Columns, choose delimited and, in the next screen, select comma. When finished, there are three columns — one with latitude, one with longitude, and one that repeated the addresses. Delete the last one, which leaves four columns: Name, Desc, Lat and Long.

7. Save the doc as a spreadsheet, log into Google Docs, and upload the spreadsheet you saved on your computer. Once it’s uploaded, open it.

8. Go to Share->Publish as Web page (upper right).

Press “Start publishing.” In the bottom of the box, make sure Web page is selected in “Get link…” and then copy the URL in the box. You can close the box.

9. Go to www.gpsvisualizer.com/map_input

…and paste the URL into the box labelled “Or a URL that the map will load dynamically.” While you’re on that page you can set the size of your map, the colour of the points that will be plotted (default marker colour) and the shape of the points (Icon).

10. Once you’ve set your parameters, press “Draw the Map.”

The map will open in a new window. Take a look at the map and make sure everything looks right. You can then close that window, but leave the map_input window open.


11. Go back to your spreadsheet and create a new column with the header “symbol” or “icon.”

You can change the pointer on the map to any of the Google pointers: airport, camera, circle, diamond, google (the default), googleblank, googlemini (which is what’s on the map now), pin, square, star, triangle, etc. Set new icons for some of your locations. (If your list included groups such friends, family and classmates, you could assign a different shaped icon to each group.) Go back to the map_input and again click “Draw the Map.” (Your original URL should still be there; if not, copy it again from the Google Doc.) Note that some of the icons on the new version of the map have changed shape. (You could also add a “Color” column to the spreadsheet and assign different colours to different locations.) Close the map.

12. Go back to the spreadsheet and add a new column “photo.”

In the first cell enter this URL: http://farm3.static.flickr.com/2581/3745284938_67536e8f92_m.jpg (Note: This is a small version of one of the photos from my Flickr photostream.) Go back to the map input page and generate a new map. Locate the item you added the photo to and click: the Google bubble opens with the name, address (from the “desc” column) and the photo. Note: You have make sure the photos you link to are small: 200-300 pixels wide seems to work well.

13. You can use some basic HTML to add more information to the “desc” for each item.

Right now, the description contains only the address:

6077 Trapp Avenue, Burnaby, BC, V3NV3

If you want to add information below that, use the <br /> code:

6077 Trapp Avenue, Burnaby, BC, V3NV3<br />Ace German Used Parts is the Lower Mainland’s only source of used auto parts for German-made cars. It specializes in BMW and Volkswagen products.

That will generate, inside the Google bubble:

6077 Trapp Avenue, Burnaby, BC, V3NV3
Ace German Used Parts is the Lower Mainland’s only source of used auto parts for German-made cars. It specializes in BMW and Volkswagen products.

14. To use your map.

At the top of the Google maps output page at gpsvisualizer, there’s a link to the temporary version of your map (all created maps are cleared from the server each night.) Click on that, to bring up a page with your map and nothing else on it. Once the page is loaded, save the page as “Web Page, HTML only.” To publish it, you’ll need a Google Maps API key and you’ll have to upload the html to a server, and then use an <iframe> to place it into your blog or web page. Full instructions are here: www.gpsvisualizer.com/faq.html#section:google.

Note that if you checked “Automatically republish when changes are made” when you published your spreadsheet in Google docs, the map will change every time you make a change to the original spreadsheet, even if it’s embedded in a new site.

Share