This is an introductory tutorial on how to plot points on an online map using Google's experimental Fusion Tables service.
To illustrate the procedure, I will plot some real data. Some friends of mine recently hosted a fun bicycling contest called the 'Coffeeneuring Challenge'. The contest is over, the results are in, and I plotted the name of each participant on a map, along with their location, and a link to their blog if available. This article explains how I did it.
This tutorial is presented in two parts. This first very basic section will create a CSV file, upload it to Fusion Tables, tweak a group of settings, and publish a URL Link. The second section will enhance the map to make it more useful.
Prereqs
You need a Google account in order to use the Fusion Table service.Part 1. Basic Map
Create CSV file
The results of the bicycling contest were published here: http://chasingmailboxes.com/2012/12/06/coffeeneuring-challenge-winners-and-honorable-mentions/
I scrolled down about halfway through the story and found the list of participants. For my first experiment, I started with the first three participants from the list:
Bill A. the ultimate coffeeneur. Portland, Oregon*
Crystal B. Aesthetics of Everywhere. (team with Adam) Washington, D.C.
Dan B. Pittsburgh, Pennsylvania
I manually created a CSV file on my computer using a text editor, and rearranged excerpts of the data to look like this:
Name, Location
"Bill A.", "Portland, OR"
"Crystal B.", "Washington, DC"
"Dan B.", "Pittsburgh, PA"
Then I removed all extra whitespace in the file (except that between quotes) and saved it on my computer.
Name,Location
"Bill A.","Portland, OR"
"Crystal B.","Washington, DC"
"Dan B.","Pittsburgh, PA"
To recreate my map, you should create a file with the same contents.
Create Fusion Table
Next, start your browser, go here: https://drive.google.com/ and sign in with your Google account. In the upper left, click the red button 'Create' -> More -> Fusion Table (experimental).
Click 'Choose File', navigate to your CSV file and upload it. Click ok-> Next-> Finish.
Your data will now appear in a Fusion Table:
Let's change the settings of the 'Locations' column from being interpreted as text to being interpretated as a map location. Click the small pulldown arrow adjacent to 'Locations'-> Change-> Type-> Location. Wait a minute for it to complete.
Add a map to the table.
Click the small plus sign adjacent to tab 'Cards 1'-> Add Map
Wait a minute, and the new map appears. Notice that there are three small red dots on the map, corresponding to the three cities listed in our CSV file.
Say yay. We created a map.
Tweak: Let's make the points more visible. Click the small pulldown arrow adjacent to tab 'Map 1'-> Change map styles -> select large blue icon-> Save. Wait for the map to refresh. Much nicer.
Publish the map. This takes several steps to make the map public and then get the URL link.
Click the pulldown arrow adjacent to tab 'Map 1' again-> Publish
Click 'Change Visibility'
At 'Who has access', click 'Change'
Click 'Public on the web'-> Save
Save the URL link which now appears. This link can be shared with others.
Test: Start another browser window and go to that URL. You will see a nice map. Viewers can scroll around and click on the placemarks, but they can't edit it. Cool.
Part 2. Enhanced Map
Shortly after I started this tutorial, I realized that the bicycling contest has more than one participant in some cities. That means my original data organization won't work. So let's swizzle the data a little and try again.
While we are at it, let's show points for more participants, and let's add the URL to the participants' blogs...
Delete the first map
It seems like a waste to delete what we just created, but this is an imprtant skill. You need to know how to delete maps. Browse to http://drive.google.com Find your CSV file in the list. Check the checkbox, then click the trash can icon up top. Gone.
Create another CSV file
This time, let's organize the data with the location column first, followed by a list of all the participant names for that city. On the top header row, create columns for eight names and URLs. These will hold the names and URLs of each participant in that city. If there are fewer participants than eight, those columns can stay empty. Let's add data for several participants...
Location,name0,url0,name1,url1,name2,url2,name3,url3,name4,url4,name5,url5,name6,url6,name7,url7
"Wilmington, DE","Patti B.",http://chasingmailboxes.com/2012/11/20/pattis-coffeeneuring-rewind-delaware-coffeeneuring-with-a-side-of-trail-running/
"Wheaton, MD","Simon B."
"Washington, DC","Kate C.",http://girlonabicycle.blogspot.com/search?q=coffeeneuring&max-results=20&by-date=true,"Kirstin C",http://ultrarunnergirl.blogspot.com/p/coffeeneuring-challenge.html,"Tom C"
Save the file and upload to Fusion Tables as we did before.
View the map, click on Washington DC placemark, and we see the three names and two URL links. Yay.
But this pop-up is ugly. Let's see if we can make it a little prettier...
Google Fusion Tables lets us define HTML snippets in a field called the 'Info Window'. This lets us control how the data is presented from each column of the CSV file onto the map. Manually create a simple HTML file on your computer, and paste-in the following text. Note the values between curly braces: Fusion Tables substitutes data from the named columns into these values.
<div class='googft-info-window' style='font-family: sans-serif'>
<b>{Location}</b><br>
<table>
<tr>
<td>{name0} <a href="{url0}" target="_blank">{url0}</a></td>
</tr>
<tr>
<td>{name1} <a href="{url1}" target="_blank">{url1}</a></td>
</tr>
<tr>
<td>{name2} <a href="{url2}" target="_blank">{url2}</a></td>
</tr>
<tr>
<td>{name3} <a href="{url3}" target="_blank">{url3}</a></td>
</tr>
<tr>
<td>{name4} <a href="{url4}" target="_blank">{url4}</a></td>
</tr>
<tr>
<td>{name5} <a href="{url5}" target="_blank">{url5}</a></td>
</tr>
<tr>
<td>{name6} <a href="{url6}" target="_blank">{url6}</a></td>
</tr>
<tr>
<td>{name7} <a href="{url7}" target="_blank">{url7}</a></td>
</tr>
</table>
</div>
Copy/paste the contents of this file to Fusion Tables. (Note: we don't upload this file, we just copy/paste the contents. The file is kept on our computer for safe-keeping.) Click on the tiny pulldown arrow adjacent to the map tab-> 'Change info window layout'
Click 'custom'-> delete all existing text, then paste-in our new text-> Save.
View the map, click on Washington DC again, and see the nice formatting. Say Yay again.
You're almost done.
Now that we know this technique works, we can finish copying the raw results data from the coffeeneuring results website to your CSV file. I did that for the total 51 participants in the Coffeeneuring contest. Then I uploaded the file again, did the optional tweaks, and set publish visibility to public, and shared the URL.
Here is my final map:
Long URL link: https://www.google.com/fusiontables/embedviz?viz=MAP&q=select+col0+from+1k8MIhiPN64k1CJ8lafFZ8KfDIrXCwJBQ7z0dGU8&h=false&lat=36.12012758978151&lng=-60.97412109375&z=3&t=1&l=col0&y=2&tmplt=2
Shortened link: http://goo.gl/m6BXq
What's next?
Ramp it up. For real mapping projects, you probably don't want to type the data manually like I did here. Write a program to automatically extract data from your source (perhaps from a database) and format it into a CSV file.
Create a static web page to reference your map. Google Fusion Tables seems to change the URL link to maps at random intervals. When that happens, the new link must sent to all viewers. This is a pain for everyone involved. Instead, I maintain a static web page whose URL does not change, and I hide the ever-changing link to the map on this page.
Fancy pop-ups. The text and URL links in the pop-ups in this article are pretty rudimentary. Figure out how to use fancy javascript in the info window HTML and make it look really nice.
Conclusion
Congrats. You are now an accomplished user of Google Fusion Tables. Impress your friends!
