In "Stratoballoon – Recovery Update", I showed the trajectory that the balloon took, and tried to extrapolate where it would have landed. The data was not all clean – in fact there were dozens of entries that I had to piece to together one or more of the timestamp, altitude, latitude, or longitude. After I cleaned up as much as the data as I could, I did a quick assessment of how high the balloon was when we lost contact with it, how high it was when it was twice that height, and then projected where it would be when it finally reached land.
Over the last three days, I’ve gone back to the original telemetry, and done a more thorough job of cleaning up the data, tossing out data points that were questionable, and done the math to determine where it would have been, rather than just eye-balling it. I did this for two reasons. First, I wanted to confirm that my original quick and dirty assessment was correct about where the balloon landed, and second I wanted to see the data, and start to get a better picture of the balloon’s flight.
I made several passes through the data to clean it up. I then pulled it into Excel to work with it some more, and generate some outputs. All of these steps can be found as separate files in the StratoballoonTelemetry.zip file on my public OneDrive folder.
01 – Original
I started by pulling the original log captured by dl-fldigi. You can find this as the "01 – Original.log" file.
02 – Separate the Lines
Next, I removed the logging tags that dl-fldigi had placed, for example:
RX 2264 : RTTY (2014-08-20 13:49Z):
I then did a find-and-replace for my call sign, and replaced each with a carriage return and then my call sign:
Replace with: \nKD8VZA-KD8VZA
That worked to separate the majority of the lines, but there were still several that didn’t have two consecutive clean copies of my call sign. For those, I went through the file and located them manually.
I also removed the miscellaneous junk characters (e.g., "|C?o_~") at the beginning and ending of each line.
This work can be found in the "02 – Separate the Lines.log" file.
03 – Clean up the Lines, Second Pass
A good, clean data line would look like this:
This is comma-delimited data, and the individual points are as follows:
Three call signs, separated by dashes
Timestamp in "hhmmss" format. The hours are military time, UTC. For some reason, every timestamp would also include either ".34" or ".36" after it. That regularity would help me in this step locate the end of a given timestamp.
Altitude in meters, and in one of these three formats: "000.00", "0000.00", or "00000.00". Again, the regularity of having 2 digits of precision, even if they were zeros, helped me here to find the end of a given altitude.
Latitude in the format "ddmm.mmmm", where "dd" are the whole degrees, and "mm.mmmm" are the decimal minutes of arc, always with 4 digits of precision.
Longitude in the same format as Latitude. The longitudes reported here were always positive, but because we west of the prime meridian, to properly represent these on a map I need to make these negative, or add a "W" after it.
Closing Tag, which was an asterisk followed by 4 capitalized hexadecimal (0-9 and A-F) digits.
04 – Add in Missing Digits
For this pass, each piece of information was normalized as follows:
The callsigns were reduced from three (what was actually transmitted by the capsule) to two, separated by a dash.
In several cases, if the timestamp was only missing 1-2 digits I could frequently figure out what was missing and replace them. If the timestamps immediately before and immediately after the one in question were good, I would use them as a double-check. For the other timestamps that were corrupted, I left them as they were.
Similarly, I could frequently piece together the corrupted altitudes using the ones right before and after.
The latitude and longitude were usually easier to reconstruct – at least the first four digits. Those rarely changed from one reading to the next, so seeing "4156.1234" and "4156.5678", followed by "456.8765" or "4u56.8765", it was easy to replace the missing "1". The second four digits, however, were usually a lost cause because they could be anything. In those cases I just left them as they were.
The closing tag, being very regular – and for all intents and purposes, random – was the easiest to replace. If I could reconstruct the original by adding a missing asterisk back in, I would. Otherwise I would replace the original with "*blah".
05 – Import into Excel
Once I imported the initial data into Excel, I added two columns for latitude and longitude, and converted them into "decimal degree" format using this formula:
=TRUNC(D3/100) + (MOD(D3, 100) / 60)
Where “D3” in this case is one of the latitudes or longitudes. This splits off the first 2 digits to get the whole degrees, then divides the remaining digits by 60 to convert the whole and fractional minutes into fractional degrees. Then I add the two values back together.
I also carried the altitude and timestamps over just as they were, and removed the ones that I felt were too corrupt to use (anything I couldn’t reconstruct with some level of confidence).
The first sheet in this Excel document contains the imported data and the converted columns. The second sheet reformats these data slightly to become output for the map (see below). The third sheet looks at just the descent itself, and plots the altitude against time – more on this below.
06 – Data for a Map
The latitude and longitude were extracted to provide the input for GPS Visualizer. This site plots a series of lat/long values onto Google Maps, showing you the path. I added a "name" column that was really the altitude, and that shows up on the map as the tooltips for each pin.
Rate of Descent
One of my main reasons for all of this parsing was to get the data to the point where I could calculate the rate that the capsule was falling during the descent, and use that as a means to calculate where it should have landed.
I started with two points that were on the "straight" part of the third leg of the flight (the first leg had the balloon ascending in an easterly direction; the second leg started just about the time the balloon burst and it reversed direction; the third and final leg reversed again so that the capsule was drifting in a southeast direction). These two points had "reliable" data for the timestamp, lat/long, and altitude:
Timestamp Latitude Longitude Altitude
161544 41.85451667 84.87098 4759
160432 41.89799 85.02348667 9847
To simplify things I’ve dropped the decimal places on the timestamps.
If I take the difference between the altitude between these two times, I get the following:
Difference in time = (161544 – 160432) = 11 minutes 12 seconds, or 672s
Difference in altitude = 9847 – 4759 = 5088m
Rate of descent = 5088 m / 672 s = 7.57 m/s
So, from timestamp 161544, when it was 4759m off the ground, how long would it have taken to reach the ground falling at a rate of 7.57 m/s? First I had to figure out where "ground" actually started – in other words, how high above sea level was this part of Michigan. For that, I consulted Google. Reading, Michigan is listed as being 360 m above sea level (source: http://en.wikipedia.org/wiki/Reading,_Michigan ). Cub Lake, which is to the east of where we think the capsule came down, is 313 m above sea level (source: http://www.placekeeper.com/Michigan/Cub_Lake-624190.html ). I split the difference and used 340m.
So, how long would it take to fall 4759-340, or 4419 meters?
Time to reach ground = 4419m / 7.57 m/s = 584 s
If I assume a constant wind all the way to ground, where should it have landed? To figure that out, I need to determine how fast it was moving laterally.
At time 160432 it was at 41.89799, -85.02348667
At time 161544 it was at 41.85451667, -84.87098
Using http://www.nhc.noaa.gov/gccalc.shtml to calculate the distance between two lat/long pairs, this is a distance of 14,000m. Given the timestamps, it required 672 seconds to travel this distance. That is a lateral rate of 14000m/672s, or 20.8 m/s.
So, if it was moving southeast at a rate of 20.8 m/s, and it would have taken 584s to reach the ground, it would have gone another 12,166m, or 12.2km from the 160432 timestamp location.
Based on the trajectory, it’s in the same area that we were searching in on Saturday.
We’ll have to focus on that southwest corner of the search area next time (hopefully this weekend if the weather cooperates).