Garmin.kql

Garmin.kql

#connect @MyFreeCluster/MyDatabase

let data = dynamic(
```
<?xml version="1.0" encoding="UTF-8"?>
<duration>
    <value>118.0</value>
    <count>5.0</count>
    <min>100.0</min>
    <max>150.0</max>
    <stdDev>0.0</stdDev>
    <sampledValue>118.0</sampledValue>
    <sum>118.0</sum>
</duration>
```);
print x=data
| project d=parse_xml(x).min


// Prof. Smoke //
.set rawGarmin <| 
let data = 
dynamic(```<?xml version="1.0" encoding="UTF-8"?>
<TrainingCenterDatabase
xsi:schemaLocation="http://www.garmin.com/xmlschemas/TrainingCenterDatabase/v2 http://www.garmin.com/xmlschemas/TrainingCenterDatabasev2.xsd"
xmlns:ns5="http://www.garmin.com/xmlschemas/ActivityGoals/v1"
xmlns:ns3="http://www.garmin.com/xmlschemas/ActivityExtension/v2"
xmlns:ns2="http://www.garmin.com/xmlschemas/UserProfile/v2"
xmlns="http://www.garmin.com/xmlschemas/TrainingCenterDatabase/v2"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns4="http://www.garmin.com/xmlschemas/ProfileExtension/v1">
<Activities>
<Activity Sport="Other">
<Id>2019-07-23T14:38:09.000Z</Id>
<Lap StartTime="2019-07-23T14:38:09.000Z">
<TotalTimeSeconds>6.0</TotalTimeSeconds>
<DistanceMeters>0.0</DistanceMeters>
<Calories>0</Calories>
<AverageHeartRateBpm>
<Value>77</Value>
</AverageHeartRateBpm>
<MaximumHeartRateBpm>
<Value>79</Value>
</MaximumHeartRateBpm>
<Intensity>Active</Intensity>
<TriggerMethod>Manual</TriggerMethod>
<Track>
<Trackpoint>
<Time>2019-07-23T14:38:09.000Z</Time>
<AltitudeMeters>13.199999809265137</AltitudeMeters>
<DistanceMeters>0.0</DistanceMeters>
<HeartRateBpm>
<Value>74</Value>
</HeartRateBpm>
<Extensions>
<ns3:TPX/>
</Extensions>
</Trackpoint>
<Trackpoint>
<Time>2019-07-23T14:38:10.000Z</Time>
<AltitudeMeters>13.199999809265137</AltitudeMeters>
<DistanceMeters>0.0</DistanceMeters>
<HeartRateBpm>
<Value>76</Value>
</HeartRateBpm>
<Extensions>
<ns3:TPX/>
</Extensions>
</Trackpoint>
<Trackpoint>
<Time>2019-07-23T14:38:14.000Z</Time>
<AltitudeMeters>13.199999809265137</AltitudeMeters>
<DistanceMeters>0.0</DistanceMeters>
<HeartRateBpm>
<Value>79</Value>
</HeartRateBpm>
<Extensions>
<ns3:TPX/>
</Extensions>
</Trackpoint>
<Trackpoint>
<Time>2019-07-23T14:38:15.000Z</Time>
<AltitudeMeters>13.199999809265137</AltitudeMeters>
<DistanceMeters>0.0</DistanceMeters>
<HeartRateBpm>
<Value>79</Value>
</HeartRateBpm>
<Extensions>
<ns3:TPX/>
</Extensions>
</Trackpoint>
</Track>
<Extensions>
<ns3:LX/>
</Extensions>
</Lap>
<Creator xsi:type="Device_t">
<Name>fēnix 5</Name>
<UnitId>3947265454</UnitId>
<ProductID>2697</ProductID>
<Version>
<VersionMajor>13</VersionMajor>
<VersionMinor>30</VersionMinor>
<BuildMajor>0</BuildMajor>
<BuildMinor>0</BuildMinor>
</Version>
</Creator>
</Activity>
</Activities>
<Author xsi:type="Application_t">
<Name>Connect Api</Name>
<Build>
<Version>
<VersionMajor>0</VersionMajor>
<VersionMinor>0</VersionMinor>
<BuildMajor>0</BuildMajor>
<BuildMinor>0</BuildMinor>
</Version>
</Build>
<LangID>en</LangID>
<PartNumber>006-D2449-00</PartNumber>
</Author>
</TrainingCenterDatabase>
```);
print data=data

rawGarmin
| where strlen(tostring(data)) < 4000
| take 1

.set-or-append activity <|

rawGarmin
| project d=parse_xml(data).TrainingCenterDatabase

| project activity=d.Activities.Activity
| extend device=tostring(activity.Creator.Name), typeOfActivity=tostring(activity.['@Sport']), activityId = tostring(activity.Id)
| extend activityId = strcat(device, ",", typeOfActivity, ",", activityId)
| mv-expand lap=activity.Lap
| project-away activity
| mv-expand trackPoint=lap.Track.Trackpoint
| project-away lap
| extend ts=todatetime(trackPoint.Time), lat=toreal(trackPoint.Position.LatitudeDegrees), lon=toreal(trackPoint.Position.LongitudeDegrees), altitude=toreal(trackPoint.AltitudeMeters), 
    speed=toreal(trackPoint.Extensions.['ns3:TPX'].['ns3:Speed']), distance=toreal(trackPoint.DistanceMeters), heartrate=toint(trackPoint.HeartRateBpm.Value), cadence=toint(trackPoint.Cadence)
| project-away trackPoint
| extend h3HighRes=iff(isnotempty( lat) and isnotempty( lon), geo_point_to_h3cell(lon, lat, 11), "")
| extend h3LowRes=iff(isnotempty( lat) and isnotempty( lon), geo_point_to_h3cell(lon, lat, 9), "")

activity
| where isnotempty( h3HighRes)
| summarize 
    CountOfActivities = dcount(activityId), 
    CountOfMeasurements = count(), 
    percentiles(heartrate, 5, 50, 95), 
    percentiles(speed, 5, 50, 95),
    altitude = avg(altitude)
    by h3HighRes
| project h3_hash_polygon = geo_h3cell_to_polygon(h3HighRes), telemetry = pack_all(), h3HighRes
| project feature=pack(
    "type", "Feature",
    "geometry", h3_hash_polygon,
    "properties", telemetry)
| summarize features = make_list(feature)
| project pack(
    "type", "FeatureCollection",
    "features", features)