3. Live-update map from large spreadsheet

Demo: A live-update map of Kenya's thousands of Health Facilities

Please tell us if the details on these pages are no longer correct: googletables-feedback@google.com.

Topics covered
  • Preparing Location data for Fusion Tables
  • Import .csv
  • Attribution
  • Export massive KML file 
  • Create network link in Google Earth
  • Live changes


Show: Kenya National Health Facilities KML in Google Earth  

"This map of thousands of health facilities in Kenya came from a spreadsheet. After importing it into Google Fusion Tables, it takes only two clicks to make this KML. I'm going to show you how I did it."

"To get a little fancier, I'm also going to show to make a version that will update to always reflect the latest data in the underlying data table, using a Network Link in Google Earth. "

Show: Browser Tab 1: MAP | Noor et al. 2009 

"As a part of the Malaria Atlas Project, a data set was brought together with about 5,000 health facility locations in Kenya. It's been published and made available to the public here on this website."

"I'm going to download it and bring it into Google Fusion Tables."
"Google Fusion Tables requires that all of the descriptive location information be combined together in one column, separated by spaces. For example, if you have an address - street, city, state, zip - these need to be combined into one column, with each part separated by a space. 

"But if you have point data like Latitude and Longitude, like in this data file, you're fine."

"Google Fusion Tables accepts spreadsheet files up to 1 MB in size, and .csv files up to 100 MB. I'm going to save this as .csv to take advantage of the larger file allowance."

Show: Data file in Excel
  1. Open downloaded data file in Excel 
  2. Save the file as .csv
Show: Browser Tab 2: Google Fusion Tables
  1. In Fusion Tables, click "New Table" to begin import
  2. For Table Name and attribution, copy the relevant data from Browser Tab 1: MAP | Noor et. al to demonstrate a high-quality attribution. 
    1. Tip: include "test" or "exercise" in the Table Name text, so you'll know which one to delete later. 
"Here's the data, in Fusion Tables. I'm going to specify which columns have the lat-long."
  1. Click Edit > Modify Columns
  2. Set "Latitude" to Location
  3. Check "Two column location" and verify column selection
  4. Click Save. 
"Now it's ready to map."
  1. Click Visualize > Map
  2. Click "Share" button and make table public
  3. Click "Get KML network link"
  4. Copy the text from the box

Show: Google Earth
  1. Go to Add > Network Link
  2. Give the Network Link a decent name
  3. Paste into the Link box
  4. Click OK
  5. The new Network Link will appear in My Places; give it a moment to load, and then explore in amazement the thousands of points in your new KML.
"Now we have all over 5 thousand locations in Google Earth. We can zoom around and see where they appear."
"The bubbles show the all the values from that record by default."

"The key advantage of using a Network Link is that it always reflects the latest values in the underlying data table. Let me show you..."
  1. Zoom in to one place, so that only a few pins are visible
  2. Open bubble on one pin and write down the UNIQUE ID

Show: Browser Tab 2: your imported Kenya Health Facilities test table
  1. Click "Show options..." link or use View > Filter
  2. Create a filter to find the health facility your comment applies to. 
    1. For example: UNIQUE ID = 40259
  3. Click trashcan icon to delete the found row.
"This is a pretty dramatic change to our data set, but it's OK because we brought it in to test & play with."

Show: Google Earth
"OK, so let's refresh our Network Link. We should see that pin disappear."

  1. Right-click on the Network Link and select Refresh
  2. Pin goes away!