This article was previously published on GeoChalkboard, and has been reproduced here with permission.
In this article we take a look at the Spreadsheet Mapper 2.0 tool created by the Outreach team for creating Google Earth and Google Maps placemark layers using Google Docs. Google Docs is a great way to create, share and collaborate on documents, spreadsheets and presentations online. Spreadsheet Mapper takes advantage of this online, collaborative environment by allowing you to create placemark layers for display in Google Earth and Google Maps through a spreadsheet created in Google Docs. Because Google Docs is a collaborative tool, members of your team can simultaneously enter data and instantly publish updates to GE and GMaps. So, let’s take a look at how this is done through a basic example.
In this example we’re going to create a placemark KML file containing existing Starbucks locations. The Starbucks data contains a unique identifer for each location along with the latitude, longitude coordinate pair and a physical address. This data is contained within the “Starbucks No Linefeeds.csv” file which was last updated in January 2008. This file contains almost 9,000 Starbucks locations. Since there are so many locations I’m only going to use a sample area for this example. The download also contains a Starbucks.bmp image which we’ll use in our next post in the series which covers Spreadsheet Mapper templates for styling icons and information balloons. Let’s get started. By default, Spreadsheet Mapper will use various templates to create some sample data. We’ll do this first and then replace the sample data with our own Starbucks location data.
- Open the starter spreadsheet. If you haven’t already done so you’ll need to login to your Google account or create one if necessary.
- Select File –> Rename and give you spreadsheet a name (”Starbucks Locations”).
- Fill in the “Author’s Information” and “About your KML Document” sections.
- Optional Parameters:
- Enable “Google Maps Compatibility” if you want the layer to work in Google Maps
- Access the “Advanced/Optional Settings” by clicking the tab indicated on the left to un-hide rows
- Click the Publish tab and select Publish Now. This step will publish the document to the web at the URL listed.
- Copy the publisher URL and paste it into the white cell provided under “Publish spreadsheet”.
Copy the “Network Link KML” cell that you see below, open Google Earth, select My Places, then right-click and paste.
Network Links capability in Google Earth provides for the delivery of dynamic data to your users. We are using a Google Docs spreadsheet which can be edited by multiple users simultaneously. Network Links in Google Earth are a perfect complement to a Google Docs spreadsheet since they can automatically refresh the Google Earth display to reflect updated data from a spreadsheet. So, at this point we’ve copied the sample data contained in the template into Google Earth. You will notice a variety of folders, placemark icons and information balloons which have been created based on parameters found in the templates contained within Spreadsheet Mapper.
The Spreadsheet Mapper comes with six templates that can be used to control icon and balloon styles. Click any of the links at the bottom of the spreadsheet to get more information about each of these templates. We’ll cover detailed information about the templates in a coming slide, but for now you can get an idea of how they are structured.
- At this point you’d want to prepare your template values. However, as I mentioned above I’m going to save the details of altering the template values for another post since it really deserves a thorough explanation.
- Now let’s add in the Starbucks data. Open the “Starbucks No Linefeeds” Excel spreadsheet which contains the latitude,longitude, identification, and address values for each Starbucks location. Due to the large size of the file I’m only going to add in Starbucks locations for my city, San Antonio. Go to the PlacemarkData link at the bottom of the spreadsheet.
- The Folder Name is optional, but in this case we’re going to define the folder name as “Starbucks” for each placemark instance. What this will do is group the placemarks under the same Folder in Google Earth. This would be helpful if you’d like to group Starbucks locations by city (i.e. San Antonio, Dallas, Houston, Austin).
- For the Placemark Name column I’m going to add in the unique identifier for each location based on the information pulled from the Excel spreadsheet we downloaded. You should be able to copy and paste the data from MS Excel to the Google Docs spreadsheet to save time.
- We’ll also enter the latitude, longitude values for each Starbucks location, also pulled from the Excel spreadsheet we downloaded. If you don’t have coordinate values for each placemark you can enter an address which can be used to generate the placemark.
- Finally, we specify a template (#5 in this case) to define our icon and balloon styles. The spreadsheet should look something like you see below.
- Click Publish –> Re-publish document in your Google Docs spreadsheet. Then refresh your network link in Google Earth by right clicking on “Link to - Spreadsheet” in the Places panel and selecting Refresh. This should refresh the Google Earth display with the new Starbucks locations we entered in the Google Docs spreadsheet.
About Eric Pimpler
This article was written by Eric Pimpler at GeoSpatial Training Services. Geospatial Training Services provide a
range of geoweb courses.