<EXTRUDE>0</EXTRUDE>. If the zero between the
<EXTRUDE> elements is changed to a one, the flight path
looks like that in Figure 3.
Now it’s time to add the coordinates of the balloon’s
path. There’s only one coordinate per line of text and each
coordinate is a point in three-dimensional space with the
following format: longitude, latitude, altitude. Since the
United States is west longitude, longitudes here are written
as negative numbers. If you forget to add a negative in
front of the longitudes, your flight path will be drawn above
western China. The longitude and latitude are written in
decimal degrees, so you need to convert minutes of latitude
and longitude in decimal degrees. Don’t forget, the altitude
is in units of meters.
I get my balloon positions over APRS, a form of digital
communications. The position reports are generated by a
Tiny Trak 3* and after I clean up the text file, it has a format
that looks like this:
124845, 38, 23. 55,098,09.34,044,022,018837
After writing the three equations into three neighboring
cells in the first row, copy and paste them all the way
down the columns. You need three more pieces of
information before calling it quits with Excel: the midpoints
of the ground path (latitude and longitude) and
altitude. Those three values become the <LOOKAT>
coordinates. I use Excel’s AVERAGE command to calculate
the midpoint of the latitude and longitude. However,
since the ascent is slower than the descent, this calculated
midpoint is weighted a bit to the ascent portion of the
flight. If you really want to find the midpoint of the ground
track, then you need to find the minimum and maximum
values of latitude and longitude, add the values together,
and then divide by two. The equation to find the
midpoint in Excel is (this is assuming the last row of data
is number 300):
The fields are time (in UTC), latitude, longitude,
heading, speed, and altitude. Only the fields that are
bolded in my example are required to create the rest of
the KML file.
It’s easiest to load the entire APRS report into Excel as
a comma delimited text file and then let the spreadsheet do
the formatting work for you. Because I need the time of day
in my data analysis, before opening the file in Excel, I first
break the time field (which is the first field in my APRS
report) into three fields (hours, minutes, and seconds). In
Excel, I insert a fourth column called Mission Elapsed Time
(that’s calculated from the previous three columns). That’s
why my equations for longitude begin in column G rather
than column D.
Here are the equations I use to convert the longitude
into negative decimal degrees, the latitude into decimal
degrees, and the altitude into meters:
If you really want to be correct, then the midpoint
For simplicity, I just use the first equation.
Since it’s easy to visually find the highest value in the
altitude column, I use the following Excel equation to
calculate the midpoint of the maximum altitude (assuming
the maximum value is located in cell I203):
■ FIGURE 4
■ FIGURE 5
Begin Notepad and open your generic KML file (a
sample is available on the Nuts & Volts website; www.
nutsvolts.com). Replace the <LOOKAT> elements of
<LATITUDE>, <LONGITUDE>, <ALTITUDE>, and <RANGE>.
Now highlight all the cells in the three new columns (be
careful, the first column won’t be highlighted just after a
paste) and cut them out by clicking EDIT > CUT. Start a new
workbook by clicking FILE > NEW. Select Blank Workbook
because Excel doesn’t assume it’s a new workbook. PASTE
the three columns in the new
workbook and save the workbook in a
comma delimited format, by clicking,
FILE > SAVE AS. Under the Save As
Type option, select, CSV (Comma
Delimited). Don’t forget what
directory the file is saved in.
You should save your Excel file in
case you discover an error in the
positions reported in your KML file.
It’s easier to doctor the old Excel file
than to create a new one.
Under Windows Explorer, right
click on the comma delimited file