Hi,
I posted this on drone-discuss but without any answer, so I try here again:
In an objective to georeference images, I'd like to export from a .bin log file the CAM & GPS messages with corresponding timestamps (the time that is displayed in the second column of the mission planner "review log" screen). I convert the .bin file to a .log file from the mission planner log utilities. However when importing the produced log file into excel, the timestamp column was removed in the .bin to .log conversion (as if the first two columns of the .bin are removed when creating the .log version of the flight log).
Questions that I have are :
-the timestamp that appears in the second column in mission planner review log corresponds to what time ? GPS time or internal Pixhawk time or else ?
-How can I easily (without programming a utility to decode the .bin log) export ALL columns of the .bin log to a csv type of file ?
-Where are specifications of the .bin data structure & content ?
-Is the disapperance of two columns from .bin to .log conversion a known bug or should a new issue ticket be created (if yes, where to create it, I am lost in all of these forums used for various categories of topics ?)?
Thx
Hugues
Replies
hi carl,
i check you excel-file also . -tthanks for sharing.
i want also understand the values you use behind ..
most you descripe in the post, but one value i don´t see ..
you use in column K a value "1000"
=(C2*7*24*60*60)+(B2/1000)-(8*60*60)
could you tell me plos. what the "1000" means ?
thanks
regards
sascha
Carl Schaefer said:
Here is a simple example excel spreadsheet that has the calcs to display the local GPS time from the flight *.log file. This allows me to review the CAM messages and figure out which CAM message is linked to my photo timestamps. Sometimes the number of CAM messages and number of photos doesn't match. This is almost always a result of a CAM message being sent to the camera but the shutter not triggering, possibly due to the camera buffer time exceeding my trigger spacing. So, I find which CAM message needs to be deleted from the *.log file so I can then use Mission Planner to assign the proper coordinates for each photo based on the CAM message. A bit tedious but it all works.
Column M is the final result.
-Carl
CAM_GPSTimeConverted.xlsx
thnks for sharing!
Hi Carl,
After analyzing your formulas I wonder if you did not make a mistake in cell K2 : =(C2*7*24*60*60)+(B2/1000)-(8*60*60)
Shouldn't it be =(C2*7*24*60*60)+(B2/1000)-(6*60*60) ?
Indeed the UTC/GPS sync was done on 6 January 1980 (not on 8th January)
Hi Hughes,
That 8 is my time zone correction from UT. So, I'm in Alaska, which is -8 (or -9 hours depending on "daylight savings") .
Column K basically converts the GPS week and GPS Time columns to seconds, for my time zone.
Column L is a constant - Unix Time origin.
Column M add Column K and L together, all converted to days (86400 sec in a day), and add in the number of days between Unix Time (Jan 1, 1970) and GPS origin time (Jan 6, 1980) (which is the 315964800/86400) and finally subtract the leap seconds (converted to days). The value is then formatted in Excel to also display month day year and time down to tenths of a second.
A lot of this is to accommodate the way Excel handles times/dates. It took me a while to figure it all out again as I did this more than a year ago. So, I am going to document this in my spreadsheet better!
You can always verify things if you know when you flew, then check the calcs so they match. I often take a picture of my GPS or gps app in cell phone, that displays the current GPS time.
Carl
OK, you did take January 6th into the 315964800 seconds. Thx
Nice.Thx for sharing.
I had a look and it seems that the TimeUS column is the one you can use to change into Date/Time fields in Excedl. You need to do a few things though.
Firstly, I think that the FMT and PARAM messages are logged before there is a stable GPS time so I found that the first message that reliably gives a consistant GPS time is just after the PARAM messages finish. I think you should see three MSG entries which report the firmware and hardware versions.
What I do is look in the .bin file and copy the GPS time value from the first MSG entry.
Then I import the .log ile into excel. The second column should be the TimeUS file.
I add a column next to this and use it to crreate a full GPS time/date field by making the first MSG have a the time I copied from the .bin file.
Then I convert the TimeUS to an excel time to add the seconds to the GPS time. IT gets a bit trick next but you then convert the remainder of the TimeUS field into ecxel time and add it to the result of the above calulation.
I thried looking at the GPS time and you can do the same if you start with the first GPS message and take it's time value as a reference.
I also found that there seems to be an offset of 0.010s which is constatant and small so Idon't think it is really an issue. If you look at the GPS time in the .bin file and compare it to the TimeUS field you can see the difference. Dividing the TimeUS by 10^6 gives you how many seconds from the start of the log file. The remainder is microseconds and they are always 0.010 fifferent than the GPS Time ine the .bin file. Like I said, I dont think this is an issue.
I regularly use this to compare the .bin and .log files as the excel method does not rely on using the GPS time for every line.
This is the formula I use:
=T_STAMP+INT(B2/10^6)/(24*60*60)+((MOD(B2,10^6)/10^6)/(8.64*10^4))
where T_STAMP is a field containing the reference time (in excel time format) taken from the first MSG timestamp and B2 is the TIMEUS field. Just copy it into a cell and fill down all the way.
When I use this it matches perfectly the times reported in the .bin file and I can use Excel to manipulate my log data.
I tried using the GPS weeks and so on but I found this was the simplest and I can either use a manual reference from the .bin file or use the fist GPS message as a starting point. Either way it seems to work for me.
Hope that helps.
Excellent explanation. That helps as this is a non programming approach, thus very simple to implement. Thx.
Very short and precise explanation from wikipedia to understand GPS & UTC time, and how to convert from one to the other (should be put in the wiki I think):
While most clocks derive their time from Coordinated Universal Time (UTC), the atomic clocks on the satellites are set to GPS time (GPST; see the page of United States Naval Observatory). The difference is that GPS time is not corrected to match the rotation of the Earth, so it does not contain leap seconds or other corrections that are periodically added to UTC. GPS time was set to match UTC in 1980, but has since diverged. The lack of corrections means that GPS time remains at a constant offset with International Atomic Time (TAI) (TAI – GPS = 19 seconds). Periodic corrections are performed to the on-board clocks to keep them synchronized with ground clocks.
The GPS navigation message includes the difference between GPS time and UTC. As of July 2015, GPS time is 17 seconds ahead of UTC because of the leap second added to UTC June 30, 2015. Receivers subtract this offset from GPS time to calculate UTC and specific timezone values. New GPS units may not show the correct UTC time until after receiving the UTC offset message. The GPS-UTC offset field can accommodate 255 leap seconds (eight bits).
GPS time is theoretically accurate to about 14 nanoseconds. However, most receivers lose accuracy in the interpretation of the signals and are only accurate to 100 nanoseconds.
As opposed to the year, month, and day format of the Gregorian calendar, the GPS date is expressed as a week number and a seconds-into-week number. The week number is transmitted as a ten-bit field in the C/A and P(Y) navigation messages, and so it becomes zero again every 1,024 weeks (19.6 years). GPS week zero started at 00:00:00 UTC (00:00:19 TAI) on January 6, 1980, and the week number became zero again for the first time at 23:59:47 UTC on August 21, 1999 (00:00:19 TAI on August 22, 1999). To determine the current Gregorian date, a GPS receiver must be provided with the approximate date (to within 3,584 days) to correctly translate the GPS date signal. To address this concern the modernized GPS navigation message uses a 13-bit field that only repeats every 8,192 weeks (157 years), thus lasting until the year 2137 (157 years after GPS week zero).
-
1
-
2
-
3
-
4
of 4 Next