Construct a Google Map from live data

Overview

Here's the scenario: you have some kind of geographically based data that you'd like to represent. i.e. houses that you've sold, parks located within 100 km of city center, etc.

Gather your data

Start by gathering the appropriate information into one spreadsheet. I'm going to use Google Docs for the sake of demonstrating this. I like to have columns like: Street Address, City, Province, Country, ZIP Code, Project Number, Description.

Format your data appropriately

Now depending on what kind of geocoding you're going to do, you will need the information in different formats. Sometimes you'll need a comma delimited address and nothing more, sometimes you'll need to prepend/append more fields (i.e. to set the color, icons, pop-out description, etc.). Basically use functions like CONCATENATE() to massage the fields that you have into the right output format.

A practical demonstration

I like to take photos and I like to walk both. I thought it would be interesting to have a map of all the city parks in one concise list. Perhaps then I could take a photo at each park and link back to the original map.

OK, so a couple Google searches reveals that http://www.city.pg.bc.ca/rec_culture/parks/list/ has the official list of Prince George parks.

• 1. I first copy and paste the information into the spreadsheet below (tab 1)

  2. I then break down the information into a couple different fields. I create Street and City fields. I then do a copy-and-paste of the formatted column into my favorite text editor and do a regex search and replace (s/^.*: //)  so that I have a simple list of Street names. I know already that all my parks are in "Prince George, BC" so I can paste this through the City column. (tab 2)

  3. I then reformat my information to be exported using a CONCATENATE() function to set up the right-most column (tab 3)

Concluding

Now obviously you can do grossly more complicated things. You can have an image column with a URL to a photo, you can change icons. You can export to KML and then import back into Google Earth and plot from there. You can do time lapse mapping, plotting real pictures against satellite view, show a sequence of locations as street view locations, etc.