Tutorials‎ > ‎

3. Live-update map from large spreadsheet

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.

Related Demo script
Example of completed exercise

Topics covered
  • Preparing Location data for Fusion Tables
  • Import .csv
  • Attribution
  • Export massive KML file 
  • Create Network Link in Google Earth
  • Live changes
Preparation
  • Have a Google Account 
  • Have Google Earth open with the final KML 
  • Have Microsoft Excel

    Exercise


    Download data file

    Prepare the data for import

    Google Fusion Tables requires that point data be in Decimal Degrees. Like all Google geo tools, Google Fusion Tables assumes spatial data is in the WGS 84 datum. In this example, no adjustment is required. 

    When location information is descriptive, Google Fusion Tables requires that all of the 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. See our help center article for suggestions. 

    Both Excel (.xls, .xlsx) files and .csv files can be imported to Google Fusion Tables. Files larger than 1 MB need to be .csv, which applies in this case. 
      1. Open downloaded data file in Excel 
      2. Go to File > Save As and save the file as Comma Separated, a.k.a ".csv"

      Import data file to Fusion Tables

      1. Sign in to Google Fusion Tables
      2. Click "New Table" from the table list or File > New from a table to begin import
      3. Import From this Computer; Choose file; click Next twice.
      4. For Table Name and attribution, copy the relevant data from http://www.map.ox.ac.uk/milestones/8/ 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. 
      5. Click Finish.

      Specify Locations

      1. Click Edit > Modify Columns
      2. Select "Latitude"
      3. In the Type menu, select "Location"
      4. Check the box by "Two column location" and confirm that the columns are being interpreted correctly for Latitude and Longitude.
      5. Click Save. 

      Create Network Link

      1. Click Visualize > Map
        1. Tip: The Map will only show 200 points at a time. However, any export will use the entire current found set. 
      2. Optional: click "Export to KML" to save a file with all these points
      3. Make table public
        1. Click "Share" button 
        2. Check the box to "Make it public - anyone can view the data"
        3. Click [ x ] to close the dialog.
      4. Click "Get KML network link"
      5. Copy the text from the box
      6. In Google Earth, Go to Add > Network Link
      7. Give the Network Link a decent name
      8. Paste into the Link box
      9. Click OK
      10. 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.

      Demonstrate live link

      The key advantage of using a Network Link is that it always reflects the latest values in the underlying data table.
      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
      3. In the Google Fusion table, Click "Show options..." link or use View > Filter
      4. Create a filter to find the health facility your comment applies to. 
        1. For example: UNIQUE ID = 40259
      5. Click trashcan icon to delete the found row.
      6. In Google Earth, right-click on the Network Link and select Refresh
      7. Pin goes away!

      ċ
      Kenya_National_Health_Facility_Database_2008.csv
      (462k)
      Rebecca Shapley,
      Nov 2, 2009, 11:37 PM
      Comments