top of page
Search
  • Peter

How do we do the stats?

Updated: Jun 5



We have been light on news from the crew over the last couple of days as they’ve just had their heads down grinding out the hours so I thought I would do a post on the tech we are using to track Goldie and the analytics I am doing as I’ve had a few people ask. You can download and analyse the data yourself using the instructions below if you feel so inclined!


The crew have with them a YB Tracker that uses the Iridium satellite network to communicate. As well as sending location data it can also detect collisions and be used in an emergency to communicate with the crew. At the start of the start of the trip we had a few teething issues with the collision alarm going off, the ping frequency going haywire, and the battery discharging much quicker than it should (which is why there are some gaps in the data), but Yellow Brick support were amazing, and these were resolved, and we have been getting reassuring pings every 2hrs since.


We can access the data via a web service and we use the free Microsoft Power BI Desktop app (PBI) to download and analyse the data.


As I import the data into PBI I filter out test data rows from prior to the crew setting off (and the extra pings from when the tracker was misbehaving). Here’s the MQuery:


let

Source = Xml.Tables(Web.Contents("https://yb.tl/XML/LincolnDews?n=10000")),

Table1 = Source{1}[Table],

Device = Table1{0}[Device],

#"Changed Type" = Table.TransformColumnTypes(Device,{{"Attribute:serial", Int64.Type}, {"Attribute:name", type text}, {"Attribute:marker", Int64.Type}}),

Position = #"Changed Type"{0}[Position],

#"Changed Type1" = Table.TransformColumnTypes(Position,{{"GpsAt", type datetime}, {"Latitude", type number}, {"Longitude", type number}, {"Altitude", Int64.Type}, {"SOG", type number}, {"COG", Int64.Type}, {"Battery", Int64.Type}, {"ID", Int64.Type}, {"Temperature", Int64.Type}, {"TxAt", type datetime}, {"Attribute:alert", type logical}, {"Attribute:type", type text}}),

#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"GpsAt", "ASWT Date/Time"}}),

#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"TxAt", "Attribute:alert", "Attribute:type"}),

#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [#"ASWT Date/Time"] > #datetime(2023, 5, 6, 3, 12, 54)),

#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Minute", each Time.Minute([#"ASWT Date/Time"])),

#"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([Minute] = 0 or [Minute] = 1 or [Minute] = 4 or [Minute] = 7)),

#"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows1",{"Minute"})

in

#"Removed Columns2"


Once we have the data in PBI we have a number of calculated columns:


The first, and most complicated, one is calculating the distance from the start in Geraldton (Latitude: 118.766449551098, Longitude: 114.612362307242) to the current position. As Earth is round this involves some clever maths and thanks to Steve from sponsor Maptaskr for helping me with this:

Miles from Start = ACOS(COS(RADIANS(118.766449551098))*COS(RADIANS(90-'YB Tracker Data'[Latitude]))+SIN(RADIANS(118.766449551098))*SIN(RADIANS(90-'YB Tracker Data'[Latitude]))*COS(RADIANS(114.612362307242-'YB Tracker Data'[Longitude])))*3959


To convert to kilometres we multiply by 1.609344. For nautical miles we multiply by 0.86897624.


The Miles to Destination is the same calculation but we use Grand Bay, Mauritius (Latitude: 110.009238174335, Longitude: 57.5779094633718) as the destination.


The other formulas are as follows (Knots is Nautical Miles/hour):


% Complete = 'YB Tracker Data'[Miles from Start]/('YB Tracker Data'[Miles from Start]+'YB Tracker Data'[Miles to Destination])


Trip Hours = DATEDIFF("11/05/23 05:30:00 AM",[ASWT Date/Time],HOUR)

Knots = 'YB Tracker Data'[Nautical Miles from Start]/'YB Tracker Data'[Trip Hours]


For the dashboard I report the MAX of distance from the start and MIN of distance to the destination.


Peter

36 views0 comments

Recent Posts

See All
bottom of page