See MIT's Exhibit 2.0 for Alternative to KML Files

Google Mapping Tools

Google provides two mapping tools that let you navigate from a global view of the earth down to country, state, city, neighborhood, or individuals house levels. The table below shows comparable views prepared with Google Earth and Google Maps.

Google Earth View
Global View	Washington DC Area	White House Neighborhood	White House
Google Map  View

Mapping your Excel data in Google Earth and/or Google Maps offer several advantages:

• GE and GM are free and widely available on the Internet

• GE and GM are easy to use so that your data viewers will be able to concentrate on your data, not learning the mapping tool

• GE and GM users have added an extensive set of data overlays. Your users will be able to mix and match your data with that from other GE/ GM users

Users can add their Excel based data to these mapping tools by exporting the data to Keyhole Markup Language (KML) files and viewing the KML file in the desired Google tool.

Google Earth lets users view KML files stored on their own PC, Google Maps lets users open a KML file that is stored on an accessible web page.

Return to Top

Keyhole Markup Language (KML) Files

See MIT's Exhibit 2.0 for Alternative to KML Files

GE and GM uses Keyhole Markup Language (KML) files to import data for display as overlays to the base map. KML files,  similar to XML type files, are specialized for map display. Excel users can map their Excel based data points by creating a KML file of their Excel data and opening that KML file in either GE or GM.

Users can obtain additional information on KML file specifications at this link.

Here's an example KML file and the resulting Google Earth map that shows placemarks for Yahoo and JuiceAnalytics. Notice the HTML like format. The  placemark data includes name, style, point coordinates and description.

Example KML File and Google Earth Map

GE users open KML files by pressing the Files button, selecting Open and selecting the KML file to be opened.

Return to  Top

See MIT's Exhibit 2.0 for Alternative to KML Files

Making KML Files with Excel VBA

Users often have Excel data with geographic codes like address, Zip code, area code, City, state or country. The data locations can be mapped in GE or GM be exporting the data to KML files.

A Simon, a frequent poster to the Google Earth Community blog, has developed a useful approach to building KML code with VBA and KML fragments.

MapExcelData.XLS uses a modified version of A Simon's Excel2KML.XLS workbook VBA code to write a KML file suitable for import by GE. The VBA code is reproduced below.

Return to Top

Excel and Google Earth (GE) Example (Click to download MapExcelData.XLS)

Download GE here. There are tutorials and advice on how to use GE at the GE Community site.

JuiceAnalytics has a number of posts on GE and Excel, it's a great site to start your exploration of Excel - GE data analysis capabilities.

Here's an example of a Excel data based custom map showing the major IT companies: Microsoft, Google, Yahoo, IBM and Juice Analytics.  The red markers are called placemarks in GE terminology.

MapExcelData.XLS uses VBA code from A Simon's Excel2KML/XLS workbook to write a KML file suitable for import by GE

Mapping these Excel based locations in GE, requires two actions:

1. Determine latitude and longitude coordinates for each IT company. I had corporate addresses which I converted to latitude and longitude with JuiceAnalytics geocoding VBA and Yahao's geocoding service.
2. Exported the Excel data to a GE compatible file format. I used  A. Simon's Excel VBA code to generate the KML file.

To get started mapping your Excel data in Google Earth, you'll need the following:

1. Software & Registration
   1. Google Earth - free, just download here
   2. Geocoding Service - free from Yahoo. You just need to be a registered Yahoo user
2. MapExcelData.XLS
3. Configure MapExcelData
   1. Complete Geocode configuration sheet
   2. Complete KML Details sheet
   3. Enter address based data

Excel users can display Excel based data points in GE by creating a KML file of their Excel data and opening that KML file in GE.

Excel and Google Map (GM) Example

GM can use the same KML file that GE uses, however, access to the KML file differs between the two systems:

1. GE accesses KML file on user's PC
2. GM accesses KML fie stored on Internet accessible web page

Production of KML files is identical for GE and GM use, the only difference is the location of the file.

As an example, I have prepared a KML file of police incident reports in a local area using MapExcelData.XLS. Viewers can view this KML file overlay in three steps:

1. Copy the KML file location text inside the " "s,  do not include the quotes themselves. ("http://processtrends.com/crime.txt" )
2. Open GM by pressing this link
3. Paste copied file location text in the GM Search window, as shown below.

Return to Top

Converting Addresses to Latitude and Longitude

Do you have lists of data by City, Zip Code, or address that you would like to map? Geocoding is the process of assigning latitude and longitude to geographic identifiers like address or zip code.

There are a number of free on-line services that geocode your data, including Yahoo! Maps Web Service. 

All you need to use Yahoo's service is a User id.

MapExcelData.XLS uses VBA code developed by JuiceAnalytics to geocode addresses with latitude and longitude. This workbook demonstrates how to get the latitude and longitude of Excel address locations.

Return to Top