Excel to KML - a VBA program
Written by Sue Gee   
Tuesday, 21 September 2010
Article Index
Excel to KML - a VBA program
Multiple Placemarks
Polygon function
Generating markers

 

Generating the marker

Now we can use the ability to calculate the position of the points in a polygon we can write a version of the MakePlacemark subroutine that marks locations with triangles (or any polygon) with a "radius" determined by the magnitude.

First we have to ouput the tags that define a drawing style for the polygon - the color set is red but this could easily be changed or be set by another parameter in the subroutine call:

Call outputLine("<Style><PolyStyle>
       <color>ff0000ff</color>
       <fill>1</fill>
       <outline>1</outline>
       </PolyStyle></Style>")

Next we output the tags to define the polygon graphics object:

Call outputLine("<MultiGeometry>
    <Polygon> <outerBoundaryIs>
      <LinearRing><coordinates>")

Now we make use of the polygon function to output the coordinates:

Call outputLine(Polygon(lon, lat, 
                           mag/500, 3))

Finally we write the closing tags:

 Call outputLine("</coordinates>
       </LinearRing></outerBoundaryIs>
            </Polygon></MultiGeometry>")

 Call outputLine("&lt;/Placemark>")
End Sub

This is all that is required and if the program is run on the data a KML file with one symbol per line of data is generated in the large textbox.

 

output

 

Using the KML with Google 

To actually make use of the KML generated you have to load it into a suitable mapping program - either by creating a KML file or by copy and paste.

To create a file all you have to do is select the data in the textbox (Ctrl+A), copying (Ctrl+C) and pasting (Ctrl+V) into Notepad allows you to save the KML in a file called earthquake.kml.

This can be viewed in Google Maps by entering

http://www.i-programmer.info/maps/earthquake.kml

into the search box.

maps

Microtremors in the Alborán Norte 4-10 July 2010

 

An alternative is to simply copy the KML from the text box and paste it into Google Earth. You can also use the File,Open command and enter the URL:

http://www.i-programmer.info/maps/earthquake.kml

 

earth

Microtremors in the Alborán Norte 4-10 July 2010

 

Of the two, Google Earth does a better job of displaying custom KML as Google Maps only supports a subset of the KML specification.

If  you are interested in this seismic sequence the IGN has produced a report on it.

The program can easily be modified to generate more complex symbols or to use additional data to control the color of symbols for example.

To access the spreadsheet - data and macro - for this project, once you have registered,  click on CodeBin.

 

If you would like to be informed about new articles on I Programmer you can either follow us on Twitter, on Facebook , on Digg or you can subscribe to our weekly newsletter.

 

Banner

<ASIN:0789743647>

<ASIN:1430218290>

<ASIN:0321525590>

 



Last Updated ( Monday, 05 May 2014 )