Анализ геоданных спортивных трекеров

В этом примере мы покажем, как с помощью Tengri можно анализировать геоданные из спортивных трекеров, таких как Garmin, Strava и других.

Наиболее популярные форматы записи и хранения геоданных в спортивных трекерах  — .gpx и .fit. Помимо GPS-координат точек (долгота, широта, высота над уровнем моря) в этих форматах сохраняется также время прохождения спортсменом данной точки и другая информация о спортсмене и его оборудовании (пульс, мощность, частота вращения педалей, скорость, температура и прочее).

В качестве дата-сета для этого примера мы возьмем выгрузку архива всех данных из одного профиля Strava по состоянию на 9 сентября 2025. Как сделать такую выгрузку, можно узнать здесь.

Выгрузка данных из Strava представляет собой архив, в котором среди прочего есть папка activities со всеми активностями данного пользователя в виде файлов .gpx или .fit (в зависимости от использованных для записи треков устройств) и файл activities.csv, в котором представлена дополнительная информация из сервиса Strava (название активности, тип активности, комментарии, различные вычисленные показатели и прочее).

Всего в нашем дата-сете 2190 активностей (треков), причем часть из них представлена в виде файлов .gpx, а другая часть — в виде файлов .fit.

Загрузка исходных данных

Для начала загрузим данные. Для этого в ячейке типа Python сделаем следующее:

  1. Загрузим файл из сервиса обмена файлами

  2. Распакуем архив .zip и файлы .gz внутри него

  3. Зададим имена колонок в двух создаваемых таблицах — для всех треков и для всех точек из всех треков

  4. Опишем вспомогательные функции

  5. Пройдем циклом по всем файлам активностей и сделаем парсинг данных из них, причем парсинг будет различаться для файлов .gpx и .fit. Для парсинга используем удобные модули Python gpxpy и fitdecode.

  6. Запишем сформированные DataFrame в таблицы с помощью функции tngri.sql

import os
import zipfile
import tngri
import gdown
import gpxpy
import pandas as pd
import gzip
import shutil
import fitdecode
import datetime
from geopy.geocoders import Nominatim
geolocator = Nominatim(user_agent="tengri")

start = datetime.datetime.now()

(1)
# Загружаем исходные файлы
google_drive_url = 'https://drive.google.com/uc?id=1yFycFAGv00WPPGvr65pCRSeHDJM9wol6'
output = 'data.zip'
gdown.download(google_drive_url, output, quiet=False)

(2)
# Распаковываем .zip
temp_dir = 'temp'
try:
    with zipfile.ZipFile('data.zip', 'r') as zObject:
        zObject.extractall(path=temp_dir)
except Exception as e:
    print(f'Error: {e}')

downloaded = len(os.listdir(temp_dir))
print(f'Downloaded files total: {downloaded}')

# Распаковываем .gz
unpacked = 0
for file in os.listdir(temp_dir):
    file_path = os.path.join(temp_dir, file)
    if file.endswith('.gz'):
        unpacked += 1
        with gzip.open(file_path, 'rb') as f_in:
            with open(file_path[:-3], 'wb') as f_out:
                shutil.copyfileobj(f_in, f_out)

print(f'Unpaсked .gz files total: {unpacked}')
print(f'Time from start: {str(datetime.datetime.now() - start)}')

(3)
# Колонки для таблицы треков
tracks_dict = {
    'track_id':[],
    'track_name':[],
    'track_length_km':[],
    'track_first_coord_lat':[],
    'track_first_coord_lon':[],
    'country':[]
    }
# Колонки для таблицы точек
points_dict = {
    'track_id':[],
    'latitude':[],
    'longitude':[],
    'elevation':[],
    'heart_rate':[],
    'temperature':[],
    'cadence':[],
    'power':[],
    'time':[],
    'speed':[]
    }

(4)
# Получение extension из точки для .gpx
def ext_from_point(point, type):
    res = pd.NA
    for ext in point.extensions:
        ext_list = [el.text for el in ext if type in el.tag]
        if len(ext_list) == 1:
            res = ext_list[0]
    return res

# Получение мощности из точки для .gpx
def power_from_point(point):
    res = pd.NA
    for ext in point.extensions:
        if ext.tag == 'power':
            res = ext.text
    return res

# Преобразование координат для .fit
def convert_coord(coord):
    if pd.isna(coord):
        return coord
    else:
        return coord/((2**32)/360)

# Преобразование скорости из м/с в км/ч
def convert_speed(speed):
    if pd.isna(speed):
        return speed
    else:
        return speed*3.6

# Определение страны по координатам
def get_country(coords):
    location = geolocator.reverse(f'{coords[0]}, {coords[1]}')
    if not location is None:
        return location.raw['address']['country']
    else:
        return 'Unknown'

(5)
# Начинаем парсинг gpx и fit
files_done = 0
for file in os.listdir(temp_dir):
    file_path = os.path.join(temp_dir, file)

    if file.endswith('.gpx'):
        files_done += 1
        with open(file_path) as f:
            gpx = gpxpy.parse(f)
            if not len(gpx.tracks) == 1:
                print(f'Not 1 track in file: {file_path}')

            cur_length_2d_km = gpx.tracks[0].segments[0].length_2d()/1000
            track_first_lat = gpx.tracks[0].segments[0].points[0].latitude
            track_first_lon = gpx.tracks[0].segments[0].points[0].longitude

            tracks_dict['track_first_coord_lat'].append(track_first_lat)
            tracks_dict['track_first_coord_lon'].append(track_first_lon)
            tracks_dict['track_id'].append(file)
            tracks_dict['track_name'].append(gpx.tracks[0].name)
            tracks_dict['track_length_km'].append(round(cur_length_2d_km, 2))
            tracks_dict['country'].append(get_country((track_first_lat, track_first_lon)))

            for point in gpx.tracks[0].segments[0].points:
                points_dict['track_id'].append(file)
                points_dict['latitude'].append(point.latitude)
                points_dict['longitude'].append(point.longitude)
                points_dict['elevation'].append(point.elevation)
                points_dict['heart_rate'].append(ext_from_point(point, 'hr'))
                points_dict['temperature'].append(ext_from_point(point, 'atemp'))
                points_dict['cadence'].append(ext_from_point(point, 'cad'))
                points_dict['power'].append(power_from_point(point))
                points_dict['time'].append(point.time)
                # В gpx нету моментальной скорости
                points_dict['speed'].append(pd.NA)

    elif file.endswith('.fit'):
        files_done += 1
        track_first_lat = 0
        track_first_lon = 0
        with fitdecode.FitReader(file_path) as fit_file:
            tracks_dict['track_id'].append(file)
            tracks_dict['track_name'].append(pd.NA)
            distance = 0
            for frame in fit_file:
                if not isinstance(frame, fitdecode.records.FitDataMessage):
                    continue
                if not frame.name == 'record':
                    continue
                if not (frame.has_field('position_lat') and frame.has_field('position_long')):
                    continue
                cur_lat = convert_coord(frame.get_value('position_lat', fallback=pd.NA))
                cur_lon = convert_coord(frame.get_value('position_long', fallback=pd.NA))

                points_dict['track_id'].append(file)
                points_dict['latitude'].append(cur_lat)
                points_dict['longitude'].append(cur_lon)
                points_dict['elevation'].append(frame.get_value('altitude', fallback=pd.NA))
                #points_dict['heart_rate'].append(frame.get_value('heart_rate', fallback=pd.NA))
                points_dict['heart_rate'].append(pd.NA)
                points_dict['temperature'].append(frame.get_value('temperature', fallback=pd.NA))
                #points_dict['cadence'].append(frame.get_value('cadence', fallback=pd.NA))
                points_dict['cadence'].append(pd.NA)
                points_dict['power'].append(frame.get_value('power', fallback=pd.NA))
                points_dict['time'].append(frame.get_value('timestamp'))
                points_dict['speed'].append(convert_speed(frame.get_value('enhanced_speed', fallback=pd.NA)))

                cur_dist = frame.get_value('distance', fallback=pd.NA)
                if type(cur_dist) == float:
                    distance = round((cur_dist/1000), 2)
                if track_first_lat == 0 and not pd.isna(cur_lat):
                    track_first_lat = cur_lat
                if track_first_lon == 0 and not pd.isna(cur_lon):
                    track_first_lon = cur_lon
            tracks_dict['track_length_km'].append(distance)
            tracks_dict['track_first_coord_lat'].append(track_first_lat)
            tracks_dict['track_first_coord_lon'].append(track_first_lon)
            tracks_dict['country'].append(get_country((track_first_lat, track_first_lon)))

    if files_done > 0 and files_done % 100 == 0 and file.endswith(('.fit', '.gpx')):
        print(f'Files done: {files_done}/{downloaded} \
        Points: {len(points_dict['track_id'])} \
        Time from start: {str(datetime.datetime.now() - start)}')

# Пишем собранные словари в DataFrame
df_tracks = pd.DataFrame.from_dict(tracks_dict)
df_points = pd.DataFrame.from_dict(points_dict)

df_points['time_text'] = df_points['time'].astype(str)
df_points = df_points.drop(columns=['time'])

# Имена таблиц для записи
table_names = ['demo_geo_tracks', 'demo_geo_points']

(6)
# Записываем сформированные DataFrame в таблицы
for frame, table_name in zip([df_tracks, df_points], table_names):
    print(f'Creating table: {table_name}')
    print(f'Size: {frame.shape}')

    file_name = tngri.upload_df(frame)
    tngri.sql(f"CREATE OR REPLACE TABLE {table_name} AS SELECT * FROM read_parquet('{file_name}')")

print(f'Finished. Time from start: {str(datetime.datetime.now() - start)}')
Downloading...
From (original): https://drive.google.com/uc?id=1yFycFAGv00WPPGvr65pCRSeHDJM9wol6
From (redirected): https://drive.google.com/uc?id=1yFycFAGv00WPPGvr65pCRSeHDJM9wol6&confirm=t&uuid=4e852907-5eeb-4ff6-ab4e-d166d6d45ecd
To: /home/python/data.zip

  0%|          | 0.00/243M [00:00<?, ?B/s]
  0%|          | 1.05M/243M [00:00<00:23, 10.5MB/s]
  2%|▏         | 4.72M/243M [00:00<00:09, 25.4MB/s]
  4%|▍         | 10.5M/243M [00:00<00:05, 38.9MB/s]
  6%|▌         | 14.7M/243M [00:00<00:07, 29.6MB/s]
  8%|▊         | 19.9M/243M [00:00<00:06, 35.6MB/s]
 10%|▉         | 24.1M/243M [00:00<00:05, 37.3MB/s]
 12%|█▏        | 28.8M/243M [00:00<00:05, 39.1MB/s]
 14%|█▍        | 33.6M/243M [00:00<00:05, 40.5MB/s]
 16%|█▌        | 37.7M/243M [00:01<00:05, 39.9MB/s]
 17%|█▋        | 41.9M/243M [00:01<00:04, 40.2MB/s]
 19%|█▉        | 47.2M/243M [00:01<00:04, 43.5MB/s]
 21%|██▏       | 51.9M/243M [00:01<00:04, 43.6MB/s]
 23%|██▎       | 56.6M/243M [00:01<00:04, 43.3MB/s]
 25%|██▌       | 61.3M/243M [00:01<00:04, 43.5MB/s]
 28%|██▊       | 67.1M/243M [00:01<00:03, 46.3MB/s]
 30%|██▉       | 71.8M/243M [00:01<00:03, 46.1MB/s]
 32%|███▏      | 78.6M/243M [00:01<00:03, 48.7MB/s]
 35%|███▍      | 84.4M/243M [00:02<00:03, 50.9MB/s]
 37%|███▋      | 89.7M/243M [00:02<00:03, 49.9MB/s]
 39%|███▉      | 94.9M/243M [00:02<00:02, 50.6MB/s]
 41%|████      | 100M/243M [00:02<00:02, 49.2MB/s]
 43%|████▎     | 105M/243M [00:02<00:02, 47.4MB/s]
 46%|████▌     | 111M/243M [00:02<00:02, 48.9MB/s]
 48%|████▊     | 116M/243M [00:02<00:02, 47.3MB/s]
 50%|█████     | 122M/243M [00:02<00:02, 46.5MB/s]
 52%|█████▏    | 126M/243M [00:02<00:02, 43.4MB/s]
 54%|█████▍    | 132M/243M [00:03<00:02, 43.6MB/s]
 57%|█████▋    | 137M/243M [00:03<00:02, 45.7MB/s]
 58%|█████▊    | 142M/243M [00:03<00:02, 43.9MB/s]
 60%|██████    | 147M/243M [00:03<00:02, 40.4MB/s]
 62%|██████▏   | 152M/243M [00:03<00:02, 42.0MB/s]
 64%|██████▍   | 156M/243M [00:03<00:02, 40.9MB/s]
 66%|██████▌   | 160M/243M [00:03<00:02, 40.4MB/s]
 68%|██████▊   | 165M/243M [00:03<00:01, 39.9MB/s]
 69%|██████▉   | 169M/243M [00:03<00:01, 39.6MB/s]
 71%|███████   | 173M/243M [00:04<00:01, 36.9MB/s]
 73%|███████▎  | 177M/243M [00:04<00:02, 29.7MB/s]
 75%|███████▍  | 181M/243M [00:04<00:01, 31.9MB/s]
 76%|███████▋  | 186M/243M [00:04<00:01, 33.8MB/s]
 78%|███████▊  | 190M/243M [00:04<00:01, 35.4MB/s]
 80%|███████▉  | 194M/243M [00:04<00:01, 36.6MB/s]
 82%|████████▏ | 198M/243M [00:04<00:01, 37.3MB/s]
 83%|████████▎ | 202M/243M [00:04<00:01, 38.0MB/s]
 85%|████████▍ | 207M/243M [00:05<00:00, 38.5MB/s]
 87%|████████▋ | 211M/243M [00:05<00:00, 38.8MB/s]
 88%|████████▊ | 215M/243M [00:05<00:00, 38.9MB/s]
 90%|█████████ | 219M/243M [00:05<00:00, 39.1MB/s]
 92%|█████████▏| 223M/243M [00:05<00:00, 39.2MB/s]
 94%|█████████▍| 228M/243M [00:05<00:00, 40.1MB/s]
 96%|█████████▌| 233M/243M [00:05<00:00, 42.2MB/s]
 98%|█████████▊| 239M/243M [00:05<00:00, 46.5MB/s]
100%|██████████| 243M/243M [00:05<00:00, 41.4MB/s]

Downloaded files total: 2190
Unpaсked .gz files total: 1765
Time from start: 0:00:11.331007
Files done: 100/2190         Points: 561928         Time from start: 0:01:57.949657
Files done: 200/2190         Points: 1055973         Time from start: 0:03:48.225765
Files done: 300/2190         Points: 1525239         Time from start: 0:05:38.334613
Files done: 400/2190         Points: 2125124         Time from start: 0:07:28.989251
Files done: 500/2190         Points: 2677727         Time from start: 0:09:25.163544
Files done: 600/2190         Points: 3190749         Time from start: 0:11:12.439539
Files done: 700/2190         Points: 3682379         Time from start: 0:13:02.326586
Files done: 800/2190         Points: 4241999         Time from start: 0:14:49.970186
Files done: 900/2190         Points: 4767488         Time from start: 0:16:37.493898
Files done: 1000/2190         Points: 5252788         Time from start: 0:18:24.054901
Files done: 1100/2190         Points: 5782689         Time from start: 0:20:14.398042
Files done: 1200/2190         Points: 6333129         Time from start: 0:22:03.400943
Files done: 1300/2190         Points: 6862458         Time from start: 0:23:51.433175
Files done: 1400/2190         Points: 7413209         Time from start: 0:25:41.723606
Files done: 1500/2190         Points: 7888312         Time from start: 0:27:28.314530
Files done: 1600/2190         Points: 8469405         Time from start: 0:29:22.235098
Files done: 1700/2190         Points: 8945913         Time from start: 0:31:10.916823
Files done: 1800/2190         Points: 9444734         Time from start: 0:33:01.630686
Files done: 1900/2190         Points: 9931590         Time from start: 0:34:49.403797
Files done: 2000/2190         Points: 10481654         Time from start: 0:36:37.313329
Files done: 2100/2190         Points: 11042971         Time from start: 0:38:29.835817
Creating table: demo_geo_tracks
Size: (2190, 6)
Creating table: demo_geo_points
Size: (11473061, 10)
Finished. Time from start: 0:40:59.713264

Обратим внимание на то, что мы записываем данные в две таблицы, которые связаны через ID трека (имя файла активности). Причем для удобства дальнейшей работы с данными, в таблицу треков мы сразу же записываем координаты первой точки трека (колонки track_first_coord_lat и track_first_coord_lon) и страну, вычисленную по этим координатам.

Выполнение этой ячейки занимает около 40 минут. Но примерно половина этого времени уходит на то, чтобы обратиться к сервису geopy и определить страну по координатам точки начала трека. Если есть необходимость ускорить начальную загрузку, то это обращение полезно отключить.

Проверка загруженных данных

Проверяем таблицу треков:

SELECT count(*) AS "Количество треков"
    FROM demo_geo_tracks
+-------------------+
| Количество треков |
+-------------------+
| 2190              |
+-------------------+
SELECT *
    FROM demo_geo_tracks
    LIMIT 10
Done in 8.2 sec.

+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+--------------------+---------------------+
| track_id        | track_name | track_length_km | track_first_coord_lat | track_first_coord_lon | country       | speed              | time_stamp          |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+--------------------+---------------------+
| 8205566762.fit  | null       | 51.04           | 52.47870256192982     | 13.336647050455213    | Deutschland   | 36.619200000000006 | 2020-06-26 13:44:05 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+--------------------+---------------------+
| 5758691664.fit  | null       | 3.92            | 55.7587356492877      | 37.745437659323215    | Россия        | 36.781200000000005 | 2020-06-26 13:44:06 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+--------------------+---------------------+
| 7562241822.fit  | null       | 21.69           | 36.594818104058504    | 30.56384850293398     | Türkiye       | 37.0512            | 2020-06-26 13:44:07 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+--------------------+---------------------+
| 5752363946.fit  | null       | 62.85           | 55.764476750046015    | 37.71281814202666     | Россия        | 37.3068            | 2020-06-26 13:44:08 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+--------------------+---------------------+
| 5774225138.fit  | null       | 114.24          | 55.99621960893273     | 36.27057650126517     | Россия        | 37.4652            | 2020-06-26 13:44:09 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+--------------------+---------------------+
| 3602018438.fit  | null       | 34.76           | 55.76445009559393     | 37.712875390425324    | Россия        | 37.7856            | 2020-06-26 13:44:10 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+--------------------+---------------------+
| 2665481253.fit  | null       | 43.88           | 57.06134635023773     | 23.311248868703842    | Latvija       | 37.980000000000004 | 2020-06-26 13:44:11 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+--------------------+---------------------+
| 16185006845.fit | null       | 66.47           | 36.71585462987423     | -4.2837147787213326   | España        | 38.037600000000005 | 2020-06-26 13:44:12 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+--------------------+---------------------+
| 5061483340.fit  | null       | 16.87           | 40.78166037797928     | -73.96295428276062    | United States | 38.037600000000005 | 2020-06-26 13:44:13 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+--------------------+---------------------+
| 1842519266.fit  | null       | 71.94           | 55.73788298293948     | 37.65193585306406     | Россия        | 38.419200000000004 | 2020-06-26 13:44:14 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+--------------------+---------------------+

Проверяем таблицу точек:

SELECT count(*) AS "Количество точек"
    FROM demo_geo_points
+------------------+
| Количество точек |
+------------------+
| 11473061         |
+------------------+
SELECT *
    FROM demo_geo_points
    LIMIT 10
Done in 8.3 sec.

+-----------------+--------------------+--------------------+-------------------+------------+-------------+---------+-------+--------------------+---------------------------+
| track_id        | latitude           | longitude          | elevation         | heart_rate | temperature | cadence | power | speed              | time_text                 |
+-----------------+--------------------+--------------------+-------------------+------------+-------------+---------+-------+--------------------+---------------------------+
| 11078368333.fit | 36.822746274992824 | -4.126659873872995 | 64.39999999999998 | null       | 18          | null    | 290   | 17.9784            | 2023-12-08 15:17:16+00:00 |
+-----------------+--------------------+--------------------+-------------------+------------+-------------+---------+-------+--------------------+---------------------------+
| 11078368333.fit | 36.82279204018414  | -4.126656521111727 | 64.79999999999995 | null       | 18          | null    | 227   | 18.576             | 2023-12-08 15:17:17+00:00 |
+-----------------+--------------------+--------------------+-------------------+------------+-------------+---------+-------+--------------------+---------------------------+
| 11078368333.fit | 36.822841661050916 | -4.126651743426919 | 65                | null       | 18          | null    | 148   | 18.4356            | 2023-12-08 15:17:18+00:00 |
+-----------------+--------------------+--------------------+-------------------+------------+-------------+---------+-------+--------------------+---------------------------+
| 11078368333.fit | 36.82288742624223  | -4.126646546646953 | 65.20000000000005 | null       | 18          | null    | 204   | 18.511200000000002 | 2023-12-08 15:17:19+00:00 |
+-----------------+--------------------+--------------------+-------------------+------------+-------------+---------+-------+--------------------+---------------------------+
| 11078368333.fit | 36.82293319143355  | -4.126641768962145 | 65.39999999999998 | null       | 18          | null    | 222   | 18.8244            | 2023-12-08 15:17:20+00:00 |
+-----------------+--------------------+--------------------+-------------------+------------+-------------+---------+-------+--------------------+---------------------------+
| 11078368333.fit | 36.822978956624866 | -4.126635566353798 | 65.60000000000002 | null       | 18          | null    | 213   | 18.9216            | 2023-12-08 15:17:21+00:00 |
+-----------------+--------------------+--------------------+-------------------+------------+-------------+---------+-------+--------------------+---------------------------+
| 11078368333.fit | 36.82302857749164  | -4.126623664051294 | 65.79999999999995 | null       | 18          | null    | 219   | 19.040399999999998 | 2023-12-08 15:17:22+00:00 |
+-----------------+--------------------+--------------------+-------------------+------------+-------------+---------+-------+--------------------+---------------------------+
| 11078368333.fit | 36.823078114539385 | -4.126606900244951 | 66                | null       | 18          | null    | 223   | 19.1484            | 2023-12-08 15:17:23+00:00 |
+-----------------+--------------------+--------------------+-------------------+------------+-------------+---------+-------+--------------------+---------------------------+
| 11078368333.fit | 36.82312773540616  | -4.126583598554134 | 66.20000000000005 | null       | 18          | null    | 223   | 19.332             | 2023-12-08 15:17:24+00:00 |
+-----------------+--------------------+--------------------+-------------------+------------+-------------+---------+-------+--------------------+---------------------------+
| 11078368333.fit | 36.82317350059748  | -4.126557866111398 | 66.39999999999998 | null       | 18          | null    | 216   | 19.6452            | 2023-12-08 15:17:25+00:00 |
+-----------------+--------------------+--------------------+-------------------+------------+-------------+---------+-------+--------------------+---------------------------+

Мы видим, что полученные через SQL-запросы данные о количестве строк в таблицах треков и точек совпадают с данными из лога нашей первой ячейки — 2.190 треков и 11.473.061 точек.

Теперь загрузим файл strava_activities.csv через Мастер загрузки и проверим данные в сформированной из него таблице.

CREATE OR REPLACE TABLE demo_geo_strava_activities AS
    SELECT *
        FROM read_csv(
            'abogdanov/5fe14b4c4110_strava_activities.csv'
        );

SELECT *
    FROM demo_geo_strava_activities
    LIMIT 10
    ORDER BY distance DESC;
Done in 9 sec.

+-------------+--------------------------+-----------------------------+---------------+------------------------------------------------------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| activity id | activity date            | activity name               | activity type | activity description                                                               | elapsed time | distance | max heart rate | relative effort | commute | activity private note | activity gear        | filename                      | athlete weight | bike weight | elapsed time_1 | moving time | distance_1 | max speed | average speed | elevation gain | elevation loss | elevation low | elevation high | max grade | average grade | average positive grade | average negative grade | max cadence | average cadence | max heart rate_1 | average heart rate | max watts | average watts | calories | max temperature | average temperature | relative effort_1 | total work | number of runs | uphill time | downhill time | other time | perceived exertion | type | start time | weighted average power | power count | prefer perceived exertion | perceived relative effort | commute_1 | total weight lifted | from upload | grade adjusted distance | weather observation time | weather condition | weather temperature | apparent temperature | dewpoint | humidity | weather pressure | wind speed | wind gust | wind bearing | precipitation intensity | sunrise time | sunset time | moon phase | bike     | gear | precipitation probability | precipitation type | cloud cover | weather visibility | uv index | weather ozone | jump count | total grit | average flow | flagged | average elapsed speed | dirt distance | newly explored distance | newly explored dirt distance | activity count | total steps | carbon saved | pool length | training load | intensity | average grade adjusted pace | timer time | total cycles | recovery | with pet | competition | long run | for a cause | media                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+-------------+--------------------------+-----------------------------+---------------+------------------------------------------------------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 8735873891  | Mar 18, 2023, 7:08:04 AM | La Primavera with RCC       | Ride          | null                                                                               | 31281        | 200.77   | 179            | 194             | false   | null                  | Giant TCR Advanced 1 | activities/9376119823.fit.gz  | null           | 8           | 31281          | 23034       | 200777.4   | 16.188    | 8.717         | 295            | 372            | 26.8          | 71.6           | 47.4      | 0             | null                   | null                   | 172         | 74              | null             | 131                | null      | 146           | 3248     | null            | 14                  | 194               | 3246159    | null           | null        | null          | null       | null               | null | null       | 176                    | 26231       | 0                         | null                      | 0         | null                | 1           | null                    | 1679122816               | 3                 | 7.5                 | 5.6                  | 4        | 0.78     | 1011.5           | 2.9        | 6.7       | 184          | 0                       | 1679116544   | 1679159808  | 0.89       | 4194513  | null | 0                         | null               | 1           | 16093              | 0        | 340.5         | null       | null       | null         | 0       | 6.419                 | 1138.9        | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | media/4cda8a70-bd40-4e47-9f16-f8cce644e23d.jpg|media/5e549f56-1cc5-414e-b1f6-70923b28c261.jpg|media/aaefa66b-e6e3-425a-bf68-265c1d9f6355.mp4|media/bbf17907-6990-458b-8c41-ccba2dd463e4.jpg|media/d90e8b35-f65d-4649-8fcc-37214ba3b428.jpg|media/054f30b2-d198-4ecd-b520-2338c670d509.jpg                                                                                                                                                                                                                                            |
+-------------+--------------------------+-----------------------------+---------------+------------------------------------------------------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2556833948  | Jul 23, 2019, 6:00:52 AM | Giro delle Dolomiti Stage 3 | Ride          | null                                                                               | 37818        | 180.47   | 177            | 184             | false   | null                  | Giant TCR Advanced 1 | activities/2713670395.fit.gz  | 83             | 8           | 37818          | 28854       | 180471     | 19.4      | null          | 3151           | 3175           | 225           | 2256           | 47.1      | 0             | null                   | null                   | 251         | 71              | 177              | 121                | null      | 149           | 5603     | null            | 29                  | 184               | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | null                      | null                      | null      | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | 4194513  | null | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | media/c21cda2a-7c14-47f7-a840-24c4cb412025.jpg|media/ae791d8b-e9a1-497c-9309-c49bbc318702.jpg                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+-------------+--------------------------+-----------------------------+---------------+------------------------------------------------------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 4242331242  | Oct 25, 2020, 5:34:56 AM | Alanya Camp Day 6           | Ride          | Queen stage                                                                        | 33077        | 180.46   | 157            | 198             | false   | null                  | Giant TCR Advanced 1 | activities/4538350403.fit.gz  | null           | 8           | 33077          | 25687       | 180461.1   | 22.8      | 7.025         | 3110           | 3119           | 22            | 1060.4         | 33.3      | 0             | null                   | null                   | 108         | 73              | null             | 128                | null      | 190           | 4997     | null            | 27                  | 198               | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | 0                         | null                      | 0         | null                | 1           | null                    | 1603602048               | 1                 | 25.2                | 25.2                 | 6.6      | 0.3      | 1013.4           | 1.6        | 1.9       | 40           | 0                       | 1603598976   | 1603638272  | 0.32       | 4194513  | null | 0                         | null               | 0           | 16093              | 0        | 264.3         | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | media/574b5612-10c1-49b3-b3e1-bc97896153b0.jpg|media/1944d2ba-2e4a-4d15-b7ae-56ede81c8db2.jpg|media/363ad0a2-1fb7-4552-ae66-a53ec25e6bea.jpg|media/fc1cbf56-0bc7-4a26-88d1-c2558db2f934.jpg|media/d3a84a2d-0d73-4452-9be3-e53ff1d952f8.jpg|media/6013cbeb-193b-46ba-873d-5120c26a7535.jpg                                                                                                                                                                                                                                            |
+-------------+--------------------------+-----------------------------+---------------+------------------------------------------------------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 5109982540  | Apr 11, 2021, 7:09:45 AM | Desperados camp Day 8       | Ride          | Queen stage                                                                        | 33941        | 179.42   | 156            | 232             | false   | null                  | Giant TCR Advanced 1 | activities/5447314715.fit.gz  | null           | 8           | 33941          | 26284       | 179424.8   | 19.7      | 6.826         | 3192           | 3196           | -16           | 1030.8         | 27        | 0             | null                   | null                   | 107         | 73              | null             | 128                | null      | 184           | 5125     | null            | 17                  | 232               | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | 0                         | null                      | 0         | null                | 1           | null                    | 1618124416               | 1                 | 12.6                | 12.6                 | -1.3     | 0.38     | 1015.5           | 2.1        | 2.3       | 151          | 0                       | 1618111488   | 1618158208  | 0.99       | 4194513  | null | 0                         | null               | 0           | 16093              | 3        | 374.3         | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | media/7cab3cd2-33d2-4c90-a3a7-9fe1ab10717a.jpg|media/6ed80c53-4d61-4ed7-b86c-08f85e81ef3d.jpg|media/90ab131a-463e-412c-89c8-cbf489112f6a.jpg|media/90caba00-d1d4-4b58-80f5-672797383a42.jpg|media/4486c339-d16d-4c33-bac4-c790117bb62b.jpg|media/b633df5d-3cb5-42cd-9ce8-166382ab7d08.jpg|media/24da2f65-2a38-49e6-aaad-4e1637465eee.jpg|media/72c68f98-acbd-4ba8-9f3c-1841147ee3ec.jpg                                                                                                                                              |
+-------------+--------------------------+-----------------------------+---------------+------------------------------------------------------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 9196092834  | Jun 3, 2023, 8:53:43 AM  | To Chartres and back        | Ride          | Эпик соло                                                                          | 31048        | 178.89   | null           | null            | false   | null                  | Giant TCR Advanced 1 | activities/9866271692.fit.gz  | null           | 8           | 31048          | 24765       | 178891.4   | 16.196    | 7.224         | 1273           | 1269           | 60.8          | 179.8          | 35.1      | 0             | null                   | null                   | 158         | 77              | null             | null               | null      | 139           | 3394     | null            | 24                  | null              | 3393782    | null           | null        | null          | null       | null               | null | null       | 164                    | 27934       | 0                         | null                      | 0         | null                | 1           | null                    | 1685779200               | 1                 | 16.4                | 15.8                 | 9.8      | 0.65     | 1018.3           | 3.5        | 7.3       | 30           | 0                       | 1685764224   | 1685821696  | 0.5        | 4194513  | null | 0                         | 1                  | 0           | 27927.8            | 3        | null          | null       | null       | null         | 0       | 5.762                 | 162.6         | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | media/6767f454-38a4-4e8f-9744-0224aa404404.jpg|media/9710109e-2167-4787-af1e-9bc0d48d8b37.jpg|media/f8bfef10-0af0-4f26-884d-2c39ba52e84b.jpg|media/734bba38-006a-46d8-8a3a-821a6664b02c.jpg|media/efe18288-537a-45af-9c8f-0753faee311b.jpg|media/44862277-2c3c-4076-a6c7-c0808d078bcb.jpg|media/7e33db5c-3484-4baf-bcaf-fe45b519cbef.jpg|media/ba09fc14-0114-4089-9d30-97a717a98f97.jpg|media/994a029a-6360-43a3-b2b1-f86bf4926cf0.jpg|media/197cd6db-63da-4dde-bd3b-f66ff65553c5.jpg|media/a69ff087-470a-43e1-aaf9-6514757a67f9.jpg |
+-------------+--------------------------+-----------------------------+---------------+------------------------------------------------------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 13705580758 | Feb 23, 2025, 8:14:47 AM | Morning Drop Ride           | Ride          | Was dropped on the way back and had to do 80km ITT                                 | 21668        | 168.59   | 180            | 424             | false   | null                  | Rose xlite 04        | activities/14626116429.fit.gz | null           | 7.8         | 21668          | 19470       | 168594.1   | 19.88     | 8.659         | 1288           | 1299           | 31.4          | 184.4          | 12.9      | 0             | null                   | null                   | 164         | 84              | 180              | 149                | null      | 193           | 3718     | null            | 18                  | 424               | 3711913    | null           | null        | null          | null       | null               | null | null       | 222                    | 20512       | 0                         | null                      | 0         | null                | 1           | null                    | 1740297600               | 1                 | 12.6                | 13.3                 | 7        | 0.69     | 1027             | 2.4        | 3.8       | 339          | 0                       | 1740293760   | 1740333952  | 0.875      | 13283968 | null | 0                         | 1                  | 0           | 34184              | 0        | null          | null       | null       | null         | 0       | 7.781                 | 0             | null                    | null                         | null           | null        | null         | null        | 293           | 72        | null                        | null       | null         | null     | null     | null        | null     | null        | media/ca9d74d0-33fc-4db2-bb39-2ffdaaf08d67.jpg|media/584a4f49-c9f9-4560-807d-72752f676e75.jpg                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+-------------+--------------------------+-----------------------------+---------------+------------------------------------------------------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 5084894611  | Apr 7, 2021, 7:07:29 AM  | Desperados camp Day 4       | Ride          | Pre-epic                                                                           | 25473        | 164.2    | 165            | 248             | false   | null                  | Giant TCR Advanced 1 | activities/5421087598.fit.gz  | null           | 8           | 25473          | 19934       | 164206.4   | 21.2      | 8.238         | 1736           | 1729           | -36.8         | 572.4          | 21.3      | 0             | null                   | null                   | 108         | 79              | null             | 135                | null      | 206           | 4253     | null            | 24                  | 248               | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | 0                         | null                      | 0         | null                | 1           | null                    | 1617778816               | 1                 | 20.1                | 20.1                 | 11.1     | 0.56     | 1011.8           | 1.7        | 2.9       | 86           | 0                       | 1617766144   | 1617812352  | 0.86       | 4194513  | null | 0                         | null               | 0.11        | 16093              | 4        | 345.5         | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | media/68f7fee3-f6ed-4285-9bd5-a64271b3876e.jpg                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+-------------+--------------------------+-----------------------------+---------------+------------------------------------------------------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 6178861995  | Oct 28, 2021, 6:09:52 AM | Cyprus Camp Day 5           | Ride          | null                                                                               | 30438        | 161.65   | 159            | 114             | false   | null                  | Giant TCR Advanced 1 | activities/6566187110.fit.gz  | null           | 8           | 30438          | 20881       | 161657.4   | 17.227    | 7.742         | 1484           | 1502           | -4            | 181.6          | 27.7      | 0             | null                   | null                   | 142         | 76              | null             | 121                | null      | 145           | 2956     | null            | 20                  | 114               | 2953912    | null           | null        | null          | null       | null               | null | null       | 178                    | 22443       | 0                         | null                      | 0         | null                | 1           | null                    | 1635400832               | 2                 | 22.3                | 22.3                 | 14.2     | 0.6      | 1016.9           | 1.8        | 1.9       | 73           | 0                       | 1635393920   | 1635433088  | 0.75       | 4194513  | null | 0                         | null               | 0.7         | 16093              | 1        | 285.2         | null       | null       | null         | 0       | 5.311                 | 102.7         | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | media/a74a5532-85bc-40fe-9a4c-8645bca02811.jpg|media/c335908c-72d8-4a09-ac2f-1719bb665973.jpg                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+-------------+--------------------------+-----------------------------+---------------+------------------------------------------------------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 9325531707  | Jun 24, 2023, 5:04:12 AM | Coup d'etat Ride            | Ride          | Wonderful ride with nice guys! Thank you [strava://athletes/71275936/10]Luis Chase | 31007        | 159.99   | 179            | 332             | false   | null                  | Giant TCR Advanced 1 | activities/10003255376.fit.gz | null           | null        | null           | null        | null       | null      | null          | null           | null           | null          | null           | null      | null          | null                   | null                   | null        | null            | null             | null               | null      | null          | null     | null            | null                | null              | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | null                      | null                      | null      | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | null     | null | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | media/0df135af-edfe-4148-af00-1d09287558b1.jpg|media/50663e37-4f88-407d-9fe2-b5c1187a2f69.jpg|media/c196fee3-6fa1-419d-aa9b-95050bead0b9.mp4|media/1e674155-6607-49fd-b28c-5bea672a8cd9.jpg|media/addc9782-4c14-43d5-a978-0c728442fc7c.jpg                                                                                                                                                                                                                                                                                           |
+-------------+--------------------------+-----------------------------+---------------+------------------------------------------------------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 11993808841 | Jul 27, 2024, 7:16:28 AM | Morning Ride                | Ride          | null                                                                               | 22167        | 157.8    | 177            | 262             | false   | null                  | Rose xlite 04        | activities/12786638321.fit.gz | null           | 7.8         | 22167          | 18925       | 157800.3   | 18.886    | 8.338         | 1451           | 1489           | -6.8          | 624.2          | 23.4      | 0             | null                   | null                   | 93          | 68              | 177              | 137                | null      | 0             | 4130     | null            | 28                  | 262               | 3204       | null           | null        | null          | null       | null               | null | null       | 5                      | 19179       | 0                         | null                      | 0         | null                | 1           | null                    | 1722063616               | 2                 | 23                  | 25.6                 | 20.7     | 0.87     | 1012.7           | 0.7        | 1.6       | 24           | 0                       | 1722057600   | 1722108416  | 0.625      | 13283968 | null | 0                         | 1                  | 0.41        | 20127              | 1        | null          | null       | null       | null         | 0       | 7.119                 | 0             | null                    | null                         | null           | null        | null         | null        | 0             | 2         | null                        | null       | null         | null     | null     | null        | null     | null        | media/db7a0b8e-b71f-4a38-902e-3435ae00f790.jpg                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+-------------+--------------------------+-----------------------------+---------------+------------------------------------------------------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

В этой таблице мы видим все параметры активностей, которые выгружены из самого сервиса Strava (а не из файлов треков). В них встречаются самые разнообразные параметры, в том числе такие экзотические как Moon Phase (фаза луны) и With Pet (с животным) .

Обратим внимание, что все скорости представлены в м/с, время — в секундах, а расстояние — в километрах.

Очистка и подготовка данных

Создадим таблицу серебряного слоя demo_geo_tracks_silver. Для этого преобразуем время из колонки activity date в тип TIMESTAMP используя функцию strptime, которая позволяет задать конкретный шаблон текстового описания момента времени. И объединим таблицу треков, полученную из файлов активностей, с таблицей, полученной из файла strava_activities.csv, проверяя, что track_id содержится в filename с помощью функции contains:

CREATE OR REPLACE TABLE demo_geo_tracks_silver AS
    SELECT *,
        strptime("activity date", '%b %d, %Y, %H:%M:%S %p') as time_stamp
        FROM demo_geo_tracks
        JOIN demo_geo_strava_activities ON (contains(filename, track_id));

SELECT * FROM demo_geo_tracks_silver
    LIMIT 10;
Done in 9.4 sec.

+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+-------------+--------------------------+--------------------------------+---------------+-----------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| track_id        | track_name | track_length_km | track_first_coord_lat | track_first_coord_lon | country       | activity id | activity date            | activity name                  | activity type | activity description                    | elapsed time | distance | max heart rate | relative effort | commute | activity private note | activity gear        | filename                      | athlete weight | bike weight | elapsed time_1 | moving time | distance_1 | max speed | average speed | elevation gain | elevation loss | elevation low | elevation high | max grade | average grade | average positive grade | average negative grade | max cadence | average cadence | max heart rate_1 | average heart rate | max watts | average watts | calories | max temperature | average temperature | relative effort_1 | total work | number of runs | uphill time | downhill time | other time | perceived exertion | type | start time | weighted average power | power count | prefer perceived exertion | perceived relative effort | commute_1 | total weight lifted | from upload | grade adjusted distance | weather observation time | weather condition | weather temperature | apparent temperature | dewpoint | humidity | weather pressure | wind speed | wind gust | wind bearing | precipitation intensity | sunrise time | sunset time | moon phase | bike     | gear | precipitation probability | precipitation type | cloud cover | weather visibility | uv index | weather ozone | jump count | total grit | average flow | flagged | average elapsed speed | dirt distance | newly explored distance | newly explored dirt distance | activity count | total steps | carbon saved | pool length | training load | intensity | average grade adjusted pace | timer time | total cycles | recovery | with pet | competition | long run | for a cause | media                                                                                                                                                                                                                                                                                     | time_stamp          |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+-------------+--------------------------+--------------------------------+---------------+-----------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| 8205566762.fit  | null       | 51.04           | 52.47870256192982     | 13.336647050455213    | Deutschland   | 7688162488  | Aug 23, 2022, 4:39:47 PM | Berlin Recon                   | Ride          | null                                    | 7740         | 51.04    | 174            | 73              | false   | null                  | Giant TCR Advanced 1 | activities/8205566762.fit.gz  | null           | 8           | 7740           | 6518        | 51044.1    | 16.012    | 7.831         | 174            | 188            | 18.6          | 68.4           | 9.3       | 0             | null                   | null                   | 116         | 73              | null             | 134                | null      | 168           | 1083     | null            | 22                  | 73                | 1082634    | null           | null        | null          | null       | null               | null | null       | 202                    | 7738        | 0                         | null                      | 0         | null                | 1           | null                    | 1661270400               | 2                 | 24.5                | 24.6                 | 15.6     | 0.58     | 1018.7           | 3.5        | 3.8       | 60           | 0                       | 1661227392   | 1661278592  | 0.89       | 4194513  | null | 0                         | null               | 0.34        | 16093              | 1        | 301.9         | null       | null       | null         | 0       | 6.595                 | 332.6         | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | media/f6509beb-c34d-4eb4-97c1-9c80b92308ec.jpg|media/f2fe2880-42a1-4794-bef0-b5d563437aca.jpg                                                                                                                                                                                             | 2022-08-23 16:39:47 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+-------------+--------------------------+--------------------------------+---------------+-----------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| 5758691664.fit  | null       | 3.92            | 55.7587356492877      | 37.745437659323215    | Россия        | 5408223435  | Jun 3, 2021, 6:35:24 PM  | Ночной заезд                   | Ride          | null                                    | 554          | 3.91     | 143            | 2               | false   | null                  | Giant TCR Advanced 1 | activities/5758691664.fit.gz  | null           | 8           | 554            | 554         | 3918.7     | 11.3      | 7.073         | 15             | 20             | 110.2         | 130.8          | 4.7       | -0.2          | null                   | null                   | 92          | 67              | null             | 122                | null      | 127           | 68       | null            | 18                  | 2                 | 68242      | null           | null        | null          | null       | null               | null | null       | 141                    | 555         | null                      | null                      | 0         | null                | 1           | null                    | 1622743168               | 1                 | 17.6                | 17.6                 | 6.2      | 0.47     | 1024.7           | 3.5        | 8.7       | 42           | 0                       | 1622681472   | 1622743552  | 0.8        | 4194513  | null | 0                         | null               | 0.28        | 16093              | 0        | 339           | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null                                                                                                                                                                                                                                                                                      | 2021-06-03 18:35:24 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+-------------+--------------------------+--------------------------------+---------------+-----------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| 7562241822.fit  | null       | 21.69           | 36.594818104058504    | 30.56384850293398     | Türkiye       | 7103414491  | May 7, 2022, 3:04:49 PM  | Вечерний велозаезд             | Ride          | Завтра будем страдать в длинную гору    | 3044         | 21.69    | 148            | 10              | false   | null                  | Giant TCR Advanced 1 | activities/7562241822.fit.gz  | null           | 8           | 3044           | 2786        | 21692.4    | 16.224    | 7.786         | 105            | 119            | 2             | 45             | 8.1       | 0             | null                   | null                   | 129         | 66              | null             | 108                | null      | 126           | 345      | null            | 21                  | 10                | 343778     | null           | null        | null          | null       | null               | null | null       | 175                    | 3045        | 0                         | null                      | 0         | null                | 1           | null                    | 1651935616               | 2                 | 22.5                | 22.5                 | 9.7      | 0.44     | 1013             | 3.5        | 4.6       | 196          | 0                       | 1651892352   | 1651942272  | 0.21       | 4194513  | null | 0                         | null               | 0.45        | 16093              | 1        | 352.8         | null       | null       | null         | 0       | 7.126                 | 645           | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | media/318cbe19-4ae0-4800-a873-349417c4df90.jpg                                                                                                                                                                                                                                            | 2022-05-07 15:04:49 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+-------------+--------------------------+--------------------------------+---------------+-----------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| 5752363946.fit  | null       | 62.85           | 55.764476750046015    | 37.71281814202666     | Россия        | 5402167712  | Jun 2, 2021, 3:55:33 PM  | Вечерний велозаезд             | Ride          | null                                    | 8485         | 62.85    | 169            | 125             | false   | null                  | Giant TCR Advanced 1 | activities/5752363946.fit.gz  | null           | 8           | 8485           | 7359        | 62849.2    | 14.5      | 8.54          | 220            | 255            | 154.2         | 226.2          | 10.7      | 0             | null                   | null                   | 168         | 81              | null             | 143                | null      | 208           | 1527     | null            | 16                  | 125               | 1530481    | null           | null        | null          | null       | null               | null | null       | 224                    | 7711        | null                      | null                      | 0         | null                | 1           | null                    | 1622646016               | 2                 | 19.7                | 19.7                 | 5.6      | 0.4      | 1023.4           | 4.3        | 6.3       | 38           | 0                       | 1622595200   | 1622657152  | 0.76       | 4194513  | null | 0                         | null               | 0.68        | 16093              | 1        | 360           | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null                                                                                                                                                                                                                                                                                      | 2021-06-02 15:55:33 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+-------------+--------------------------+--------------------------------+---------------+-----------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| 5774225138.fit  | null       | 114.24          | 55.99621960893273     | 36.27057650126517     | Россия        | 5423038640  | Jun 6, 2021, 8:24:11 AM  | Granfondo Волоколамск Группа Б | Ride          | 6 место в личном зачете и 1 в командном | 10566        | 114.24   | 189            | 284             | false   | null                  | Giant TCR Advanced 1 | activities/5774225138.fit.gz  | null           | 8           | 10566          | 10496       | 114243.9   | 22.1      | 10.885        | 917            | 894            | 152.8         | 262.8          | 9.6       | 0             | null                   | null                   | 173         | 80              | null             | 154                | null      | 199           | 2027     | null            | 26                  | 284               | 2026580    | null           | null        | null          | null       | null               | null | null       | 242                    | 10567       | 0                         | null                      | 0         | null                | 1           | null                    | 1622966400               | 2                 | 22.1                | 22.1                 | 10.2     | 0.47     | 1014.9           | 3.5        | 4         | 71           | 0.1                     | 1622940800   | 1623003392  | 0.89       | 4194513  | null | 0.01                      | 5                  | 0.45        | 16093              | 5        | 341.8         | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | media/946f24a2-cd5f-4b18-ae4b-822158a40244.jpg|media/7d11f14c-7845-489a-bb34-f71b882c47e6.jpg|media/08fe3d9d-2f68-4319-b5ce-00c6a6282a49.jpg|media/679c1351-1ac7-4a72-a525-c3bc1766c202.jpg|media/ba7f9fd0-a470-45d9-8083-00ab445251cd.jpg|media/df115e87-def4-46ee-9f7e-899fd370903e.jpg | 2021-06-06 08:24:11 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+-------------+--------------------------+--------------------------------+---------------+-----------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| 3602018438.fit  | null       | 34.76           | 55.76445009559393     | 37.712875390425324    | Россия        | 3370915442  | Apr 29, 2020, 2:03:19 PM | Дневной велозаезд              | Ride          | null                                    | 4075         | 34.76    | 174            | 127             | false   | null                  | Giant TCR Advanced 1 | activities/3602018438.fit.gz  | 83             | 8           | 4075           | 3946        | 34762.6    | 15        | 8.801         | 140            | 146            | 132           | 162.6          | 17.6      | 0             | null                   | null                   | 104         | 86              | 174              | 159                | null      | 191           | 1088     | null            | 11                  | 127               | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | null                      | null                      | 0         | null                | 1           | null                    | 1588168832               | 2                 | 11.5                | 11.5                 | 0        | 0.45     | 1000.3           | 7.4        | 12.1      | 301          | 0                       | 1588125056   | 1588179840  | 0.21       | 4194513  | null | 0                         | null               | 0.53        | 16093              | 1        | 367.2         | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null                                                                                                                                                                                                                                                                                      | 2020-04-29 14:03:19 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+-------------+--------------------------+--------------------------------+---------------+-----------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| 2665481253.fit  | null       | 43.88           | 57.06134635023773     | 23.311248868703842    | Latvija       | 2509891301  | Jul 6, 2019, 6:12:44 PM  | Night ride                     | Ride          | null                                    | 4958         | 43.88    | 163            | 85              | false   | null                  | Giant TCR Advanced 1 | activities/2665481253.fit.gz  | 83             | 8           | 4958           | 4958        | 43880      | 12.2      | null          | 51             | 43             | -1            | 6              | 7.7       | 0             | null                   | null                   | 101         | 89              | 163              | 143                | null      | 170           | 1395     | null            | 12                  | 85                | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | null                      | null                      | null      | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | 4194513  | null | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null                                                                                                                                                                                                                                                                                      | 2019-07-06 18:12:44 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+-------------+--------------------------+--------------------------------+---------------+-----------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| 5061483340.fit  | null       | 16.87           | 40.78166037797928     | -73.96295428276062    | United States | 4741945778  | Feb 6, 2021, 7:18:45 AM  | NYC                            | Virtual Ride  | null                                    | 1976         | 16.87    | 157            | 16              | false   | null                  | Giant TCR Advanced 1 | activities/5061483340.fit.gz  | null           | 8           | 1976           | 1953        | 16870.5    | 15.2      | 8.638         | 176            | 0              | 17            | 46.2           | 12.4      | -0.1          | null                   | null                   | 103         | 89              | null             | 133                | null      | 169           | 313      | null            | null                | 16                | null       | null           | null        | null          | null       | null               | null | null       | 175                    | 1977        | null                      | null                      | 0         | null                | 1           | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | 4194513  | null | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | media/9218c649-fc79-4378-a133-5d691bd50c38.jpg                                                                                                                                                                                                                                            | 2021-02-06 07:18:45 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+-------------+--------------------------+--------------------------------+---------------+-----------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| 1842519266.fit  | null       | 71.94           | 55.73788298293948     | 37.65193585306406     | Россия        | 1717234129  | Jul 21, 2018, 9:51:37 AM | Noon ride                      | Ride          | null                                    | 9707         | 71.94    | 170            | 186             | false   | null                  | Giant TCR Advanced 1 | activities/1842519266.fit.gz  | 83             | 8           | 9707           | 8353        | 71942      | 13.6      | null          | 313            | 299            | 110           | 193            | 33.3      | 0             | null                   | null                   | 101         | 86              | 170              | 148                | null      | 178           | 2298     | null            | 27                  | 186               | null       | null           | null        | null          | null       | null               | null | null       | null                   | null        | null                      | null                      | 0         | null                | null        | null                    | null                     | null              | null                | null                 | null     | null     | null             | null       | null      | null         | null                    | null         | null        | null       | 4194513  | null | null                      | null               | null        | null               | null     | null          | null       | null       | null         | null    | null                  | null          | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | null                                                                                                                                                                                                                                                                                      | 2018-07-21 09:51:37 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+-------------+--------------------------+--------------------------------+---------------+-----------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| 11417255201.fit | null       | 30.91           | 36.71511836349964     | -4.2892365250736475   | España        | 10674837301 | Feb 1, 2024, 2:24:07 PM  | Afternoon Ride                 | Ride          | From new apartment                      | 4793         | 30.91    | 166            | 67              | false   | null                  | Rose xlite 04        | activities/11417255201.fit.gz | null           | 7.8         | 4793           | 4571        | 30913.6    | 15.33     | 6.763         | 658            | 676            | 8.8           | 509.8          | 20.3      | 0             | null                   | null                   | 118         | 74              | null             | 137                | null      | 188           | 850      | null            | 15                  | 67                | 849803     | null           | null        | null          | null       | null               | null | null       | 227                    | 4794        | 0                         | null                      | 0         | null                | 1           | null                    | 1706796032               | 1                 | 16.9                | 16.4                 | 10.4     | 0.65     | 1033             | 2.7        | 5.9       | 128          | 0                       | 1706771968   | 1706809344  | 0.625      | 13283968 | null | 0                         | 1                  | 0.29        | 26463              | 2        | null          | null       | null       | null         | 0       | 6.45                  | 109.7         | null                    | null                         | null           | null        | null         | null        | null          | null      | null                        | null       | null         | null     | null     | null        | null     | null        | media/da2f0e86-c77e-42c2-b7c5-5d08d0ac7acf.jpg                                                                                                                                                                                                                                            | 2024-02-01 14:24:07 |
+-----------------+------------+-----------------+-----------------------+-----------------------+---------------+-------------+--------------------------+--------------------------------+---------------+-----------------------------------------+--------------+----------+----------------+-----------------+---------+-----------------------+----------------------+-------------------------------+----------------+-------------+----------------+-------------+------------+-----------+---------------+----------------+----------------+---------------+----------------+-----------+---------------+------------------------+------------------------+-------------+-----------------+------------------+--------------------+-----------+---------------+----------+-----------------+---------------------+-------------------+------------+----------------+-------------+---------------+------------+--------------------+------+------------+------------------------+-------------+---------------------------+---------------------------+-----------+---------------------+-------------+-------------------------+--------------------------+-------------------+---------------------+----------------------+----------+----------+------------------+------------+-----------+--------------+-------------------------+--------------+-------------+------------+----------+------+---------------------------+--------------------+-------------+--------------------+----------+---------------+------------+------------+--------------+---------+-----------------------+---------------+-------------------------+------------------------------+----------------+-------------+--------------+-------------+---------------+-----------+-----------------------------+------------+--------------+----------+----------+-------------+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+

Теперь создадим таблицу точек серебряного слоя — оставим в ней только нужные колонки, преобразуем данные в колонке time_text в тип TIMESTAMP (используя функции strptime и regexp_extract) и отфильтруем все точки, в которых значение latitude или longitude пустое:

CREATE OR REPLACE TABLE demo_geo_points_silver AS
    SELECT
        track_id,
        latitude,
        longitude,
        elevation,
        temperature,
        power,
        speed,
        strptime(regexp_extract(time_text, '^.{19}'), '%c') as time_stamp,
    FROM demo_geo_points
    WHERE latitude IS NOT NULL AND longitude IS NOT NULL;

SELECT *
    FROM demo_geo_points_silver
    LIMIT 10;
Done in 11.6 sec.

+----------------+--------------------+--------------------+--------------------+-------------+-------+--------------------+---------------------+
| track_id       | latitude           | longitude          | elevation          | temperature | power | speed              | time_stamp          |
+----------------+--------------------+--------------------+--------------------+-------------+-------+--------------------+---------------------+
| 3922792040.fit | 55.84256364963949  | 37.73899080231786  | 167.60000000000002 | 24          | null  | 36.619200000000006 | 2020-06-26 13:44:05 |
+----------------+--------------------+--------------------+--------------------+-------------+-------+--------------------+---------------------+
| 3922792040.fit | 55.842567421495914 | 37.73883054032922  | 167.60000000000002 | 24          | null  | 36.781200000000005 | 2020-06-26 13:44:06 |
+----------------+--------------------+--------------------+--------------------+-------------+-------+--------------------+---------------------+
| 3922792040.fit | 55.842567421495914 | 37.73866650648415  | 167.60000000000002 | 24          | null  | 37.0512            | 2020-06-26 13:44:07 |
+----------------+--------------------+--------------------+--------------------+-------------+-------+--------------------+---------------------+
| 3922792040.fit | 55.842567421495914 | 37.73849870078266  | 167.60000000000002 | 24          | null  | 37.3068            | 2020-06-26 13:44:08 |
+----------------+--------------------+--------------------+--------------------+-------------+-------+--------------------+---------------------+
| 3922792040.fit | 55.84256364963949  | 37.73833081126213  | 167.60000000000002 | 24          | null  | 37.4652            | 2020-06-26 13:44:09 |
+----------------+--------------------+--------------------+--------------------+-------------+-------+--------------------+---------------------+
| 3922792040.fit | 55.8425560221076   | 37.738166777417064 | 167.60000000000002 | 24          | null  | 37.7856            | 2020-06-26 13:44:10 |
+----------------+--------------------+--------------------+--------------------+-------------+-------+--------------------+---------------------+
| 3922792040.fit | 55.842548394575715 | 37.73799511604011  | 167.39999999999998 | 24          | null  | 37.980000000000004 | 2020-06-26 13:44:11 |
+----------------+--------------------+--------------------+--------------------+-------------+-------+--------------------+---------------------+
| 3922792040.fit | 55.842544538900256 | 37.7378349378705   | 167.20000000000005 | 24          | null  | 38.037600000000005 | 2020-06-26 13:44:12 |
+----------------+--------------------+--------------------+--------------------+-------------+-------+--------------------+---------------------+
| 3922792040.fit | 55.84254076704383  | 37.737670904025435 | 167                | 24          | null  | 38.037600000000005 | 2020-06-26 13:44:13 |
+----------------+--------------------+--------------------+--------------------+-------------+-------+--------------------+---------------------+
| 3922792040.fit | 55.84253691136837  | 37.73749924264848  | 166.79999999999995 | 24          | null  | 38.419200000000004 | 2020-06-26 13:44:14 |
+----------------+--------------------+--------------------+--------------------+-------------+-------+--------------------+---------------------+

Анализ

На основе сформированных выше данных можно получить самую разнообразную статистику. Ограничимся здесь только нескольким примерами.

Статистика по странам

Выведем суммарный пробег в километрах по всем трекам с началом в данной стране, исключая виртуальные заезды. Упорядочим результат по убыванию суммарного пробега. Используем функции round и sum.

SELECT
    country AS "Country",
    round(sum(distance)) AS "Total distance, km",
FROM demo_geo_tracks_silver
WHERE NOT "Activity Type" = 'Virtual Ride'
GROUP BY 1
ORDER BY 2 DESC
+--------------------------------+--------------------+
| Country                        | Total distance, km |
+--------------------------------+--------------------+
| Россия                         | 36947              |
+--------------------------------+--------------------+
| España                         | 30437              |
+--------------------------------+--------------------+
| Türkiye                        | 8255               |
+--------------------------------+--------------------+
| Deutschland                    | 6033               |
+--------------------------------+--------------------+
| Italia                         | 2681               |
+--------------------------------+--------------------+
| France                         | 1833               |
+--------------------------------+--------------------+
| Κύπρος - Kıbrıs                | 1452               |
+--------------------------------+--------------------+
| Latvija                        | 1191               |
+--------------------------------+--------------------+
| Schweiz/Suisse/Svizzera/Svizra | 708                |
+--------------------------------+--------------------+
| Sverige                        | 615                |
+--------------------------------+--------------------+
| Հայաստան                       | 595                |
+--------------------------------+--------------------+
| Magyarország                   | 421                |
+--------------------------------+--------------------+
| Nederland                      | 408                |
+--------------------------------+--------------------+
| Österreich                     | 352                |
+--------------------------------+--------------------+
| Slovensko                      | 223                |
+--------------------------------+--------------------+
| Unknown                        | 56                 |
+--------------------------------+--------------------+
| United Kingdom                 | 48                 |
+--------------------------------+--------------------+

Мы получили таблицу рейтинга стран в зависимости от суммарного пробега в каждой стране.

Анализ нескольких показателей внутри одной активности

Сформируем график, на котором будут одновременно показаны скорость, высота и мощность внутри одной заданной активности с поминутной сегментацией и дополнительным ограничением по времени начала. Используем функции avg, round и date_trunc с параметром minute.

SELECT
	round(avg(power)) AS "power, w",
	round(avg(elevation)) AS "elevation, m",
	round(avg(speed)) AS "speed, km/h",
	date_trunc('minute', time_stamp) as time,
FROM demo_geo_points_silver
WHERE track_id = '16627535673.fit' and time_stamp < (timestamp '2025-08-24 07:30:00')
GROUP BY TIME
ORDER BY TIME
Done in 8.5 sec.

+----------+--------------+-------------+---------------------+
| power, w | elevation, m | speed, km/h | time                |
+----------+--------------+-------------+---------------------+
| 181      | 108          | 21          | 2025-08-24 07:30:00 |
+----------+--------------+-------------+---------------------+
| 269      | 112          | 20          | 2025-08-24 07:31:00 |
+----------+--------------+-------------+---------------------+
| 130      | 121          | 18          | 2025-08-24 07:32:00 |
+----------+--------------+-------------+---------------------+
| 237      | 128          | 12          | 2025-08-24 07:33:00 |
+----------+--------------+-------------+---------------------+
| 278      | 143          | 12          | 2025-08-24 07:34:00 |
+----------+--------------+-------------+---------------------+
| ...      | ...          | ...         | ...                 |
+----------+--------------+-------------+---------------------+
201 rows

Для отображения графика воспользуемся вкладкой Chart в интерфейсе вывода ячейки:

img1

Мы получили график, на котором одновременно отображаются несколько разных показателей для данной активности — скорость, высота и мощность. При наведении мышкой на линию графика показывается значение в данной точке. На таких графиках пользователи могут изучать показатели в их зависимости друг от друга. Например, в данном случае можно заметить, что на спусках скорость возрастает, а мощность падает, на подъемах же — наоборот.

Распределение мощности внутри одной активности

Выведем график распределения мощности внутри той же активности с шагом по 25 ватт. Для этого воспользуемся функцией floor.

SELECT
	floor(power/25)*25 AS power,
	count(*) AS count
FROM demo_geo_points_silver
WHERE track_id = '16627535673.fit' AND power > 0
GROUP BY 1
ORDER BY 1
Done in 8.2 sec.

+-------+-------+
| power | count |
+-------+-------+
| 0     | 230   |
+-------+-------+
| 25    | 177   |
+-------+-------+
| 50    | 182   |
+-------+-------+
| 75    | 213   |
+-------+-------+
| 100   | 366   |
+-------+-------+
| ...   | ...   |
+-------+-------+
30 rows

Для отображения графика воспользуемся вкладкой Chart в интерфейсе вывода ячейки:

img2

Мы получили график распределения мощности внутри одной активности с шагом по 25 ватт.

Статистика по календарным периодам

Чтобы вывести подробную статистику по календарным периодам (месяцы, дни недели, часы внутри суток и прочее), создадим на основе demo_geo_tracks_silver новую таблицу, в которую добавим колонки, соответствующие разным периодам времени. Используем для этого функции concat и regexp_extract.

CREATE OR REPLACE TABLE demo_geo_tracks_silver_time AS
SELECT
    track_id,
    hour(time_stamp) AS hour,
    regexp_extract(string(time_stamp), '.{5} ') as day_of_year,
    month(time_stamp) AS month,
    monthname(time_stamp) AS month_name,
    dayname(time_stamp) AS day_name,
    concat(regexp_extract(string(day_of_year), '.{2} $') , ' ', month_name) as text_date,
FROM demo_geo_tracks_silver;

SELECT *
    FROM demo_geo_tracks_silver_time
    LIMIT 10;
+-----------------+------+-------------+------------+-------+------+------------+-----------+-------------+
| track_id        | hour | day_of_year | month_year | month | year | month_name | day_name  | text_date   |
+-----------------+------+-------------+------------+-------+------+------------+-----------+-------------+
| 8205566762.fit  | 16   | 08-23       | 2022-08    | 8     | 2022 | August     | Tuesday   | 23 August   |
+-----------------+------+-------------+------------+-------+------+------------+-----------+-------------+
| 5758691664.fit  | 18   | 06-03       | 2021-06    | 6     | 2021 | June       | Thursday  | 03 June     |
+-----------------+------+-------------+------------+-------+------+------------+-----------+-------------+
| 7562241822.fit  | 15   | 05-07       | 2022-05    | 5     | 2022 | May        | Saturday  | 07 May      |
+-----------------+------+-------------+------------+-------+------+------------+-----------+-------------+
| 5752363946.fit  | 15   | 06-02       | 2021-06    | 6     | 2021 | June       | Wednesday | 02 June     |
+-----------------+------+-------------+------------+-------+------+------------+-----------+-------------+
| 5774225138.fit  | 8    | 06-06       | 2021-06    | 6     | 2021 | June       | Sunday    | 06 June     |
+-----------------+------+-------------+------------+-------+------+------------+-----------+-------------+
| 3602018438.fit  | 14   | 04-29       | 2020-04    | 4     | 2020 | April      | Wednesday | 29 April    |
+-----------------+------+-------------+------------+-------+------+------------+-----------+-------------+
| 2665481253.fit  | 18   | 07-06       | 2019-07    | 7     | 2019 | July       | Saturday  | 06 July     |
+-----------------+------+-------------+------------+-------+------+------------+-----------+-------------+
| 5061483340.fit  | 7    | 02-06       | 2021-02    | 2     | 2021 | February   | Saturday  | 06 February |
+-----------------+------+-------------+------------+-------+------+------------+-----------+-------------+
| 1842519266.fit  | 9    | 07-21       | 2018-07    | 7     | 2018 | July       | Saturday  | 21 July     |
+-----------------+------+-------------+------------+-------+------+------------+-----------+-------------+
| 11417255201.fit | 14   | 02-01       | 2024-02    | 2     | 2024 | February   | Thursday  | 01 February |
+-----------------+------+-------------+------------+-------+------+------------+-----------+-------------+

Построим график, из которого будет видно, какова суммарная дистанция в километрах за все время в активностях, начинающихся в каждый час суток. Для объединения таблиц demo_geo_tracks_silver_time и demo_geo_tracks_silver по колонке track_id воспользуемся выражением JOIN с параметром USING.

Для заполнения строк с пустыми часами используем функции unnest и generate_series.

CREATE OR REPLACE TABLE demo_geo_tracks_hour AS
SELECT
    distance,
    hour,
    time_stamp
FROM demo_geo_tracks_silver_time
    JOIN demo_geo_tracks_silver
        USING (track_id);

-- Добавляем пустые часы
INSERT INTO demo_geo_tracks_hour (hour, distance)
    SELECT unnest(generate_series(23)), 0;

SELECT
    round(sum(distance)) AS "Total distance, km",
    hour AS Hour
FROM demo_geo_tracks_hour
GROUP BY 2
ORDER BY 2;
Done in 9.5 sec.

+--------------------+------+
| Total distance, km | Hour |
+--------------------+------+
| 0                  | 0    |
+--------------------+------+
| 0                  | 1    |
+--------------------+------+
| 0                  | 2    |
+--------------------+------+
| 0                  | 3    |
+--------------------+------+
| 250                | 4    |
+--------------------+------+
| 2332               | 5    |
+--------------------+------+
| 7448               | 6    |
+--------------------+------+
| 8365               | 7    |
+--------------------+------+
| 9719               | 8    |
+--------------------+------+
| 4949               | 9    |
+--------------------+------+
| ...                | ...  |
+--------------------+------+
24 rows

Для отображения графика воспользуемся вкладкой Chart в интерфейсе вывода ячейки:

img3

Теперь построим график, на котором покажем суммарную дистанцию в километрах для каждого месяца за все время.

SELECT
    round(sum(distance)) AS "Total distance, km",
    month,
FROM demo_geo_tracks_silver_time
    JOIN demo_geo_tracks_silver
        USING (track_id)
GROUP BY 2
ORDER BY 2
Done in 8.2 sec.

+--------------------+-------+
| total distance, km | month |
+--------------------+-------+
| 6580               | 1     |
+--------------------+-------+
| 4419               | 2     |
+--------------------+-------+
| 5380               | 3     |
+--------------------+-------+
| 9122               | 4     |
+--------------------+-------+
| 9514               | 5     |
+--------------------+-------+
| 10578              | 6     |
+--------------------+-------+
| 13225              | 7     |
+--------------------+-------+
| 12298              | 8     |
+--------------------+-------+
| 9250               | 9     |
+--------------------+-------+
| 9072               | 10    |
+--------------------+-------+
| 5537               | 11    |
+--------------------+-------+
| 4419               | 12    |
+--------------------+-------+

Для отображения графика воспользуемся вкладкой Chart в интерфейсе вывода ячейки:

img4

Ожидаемо в зимние месяцы суммарная дистанция значительно меньше, чем в летние.

Теперь выведем на одном графике суммарные дистанции в километрах для каждой даты в заданных месяцах. Для примера возьмем февраль и июль как месяцы с наименьшей и наибольшей суммарной дистанцией.

SELECT
    round(sum(distance)) AS "Total distance, km",
    day_of_year,
FROM demo_geo_tracks_silver_time
    JOIN demo_geo_tracks_silver
        USING (track_id)
WHERE month IN [2,7]
GROUP BY 2
ORDER BY 2
Done in 8.1 sec.

+--------------------+-------------+
| total distance, km | day_of_year |
+--------------------+-------------+
| 160                | 02-01       |
+--------------------+-------------+
| 194                | 02-02       |
+--------------------+-------------+
| 137                | 02-03       |
+--------------------+-------------+
| 194                | 02-04       |
+--------------------+-------------+
| 245                | 02-05       |
+--------------------+-------------+
| ...                | ...         |
+--------------------+-------------+
60 rows
img5

Теперь выведем рейтинг дней недели — упорядочим дни недели по суммарной дистанции в километрах за все время начиная с максимальных.

SELECT
    round(sum(distance)) AS "Total distance, km",
    day_name as "Day of week",
FROM demo_geo_tracks_silver_time
    JOIN demo_geo_tracks_silver
        USING track_id
GROUP BY 2
ORDER BY 1 DESC
Done in 8.4 sec.

+--------------------+-------------+
| total distance, km | day of week |
+--------------------+-------------+
| 21636              | Saturday    |
+--------------------+-------------+
| 19006              | Sunday      |
+--------------------+-------------+
| 13817              | Thursday    |
+--------------------+-------------+
| 12453              | Tuesday     |
+--------------------+-------------+
| 12400              | Wednesday   |
+--------------------+-------------+
| 10696              | Friday      |
+--------------------+-------------+
| 9386               | Monday      |
+--------------------+-------------+

Изменения показателей за весь период

Теперь отобразим на одном графике изменения показателей в активностях на протяжении всего времени с сегментацией по кварталам. Для этого используем функцию date_trunc с параметром quarter. Возьмем следующие показатели:

  • суммарная дистанция

  • среднее время одной активности

  • средний набор высоты за одну активность

  • среднее количество затраченных калорий за одну активность

SELECT
    round(avg("Elevation Gain")) AS "avg elevation gain, m",
    round(avg("Moving Time")/3.6) AS "avg moving time, h/10",
    round(avg(Calories)) AS "avg calories spent, cal",
    round(sum(distance)) AS "total distance, km",
    period
FROM (SELECT
        distance,
        "Elevation Gain",
        "Moving Time",
        Calories,
        date_trunc('quarter', time_stamp) AS period
        FROM demo_geo_tracks_silver
            JOIN demo_geo_tracks_silver_time
                USING (track_id)
        )
GROUP BY period
ORDER BY period
Done in 8.3 sec.

+-----------------------+-----------------------+-------------------------+--------------------+------------+
| avg elevation gain, m | avg moving time, h/10 | avg calories spent, cal | total distance, km | period     |
+-----------------------+-----------------------+-------------------------+--------------------+------------+
| 52                    | 485                   | 322                     | 423                | 2014-07-01 |
+-----------------------+-----------------------+-------------------------+--------------------+------------+
| 60                    | 597                   | 389                     | 438                | 2014-10-01 |
+-----------------------+-----------------------+-------------------------+--------------------+------------+
| 46                    | 623                   | null                    | 41                 | 2015-01-01 |
+-----------------------+-----------------------+-------------------------+--------------------+------------+
| 60                    | 676                   | 502                     | 390                | 2015-04-01 |
+-----------------------+-----------------------+-------------------------+--------------------+------------+
| 310                   | 1394                  | 816                     | 1426               | 2015-07-01 |
+-----------------------+-----------------------+-------------------------+--------------------+------------+
| ...                   | ...                   | ...                     | ...                | ...        |
+-----------------------+-----------------------+-------------------------+--------------------+------------+
45 rows

Так как изменения всех этих показателей в целом говорят об улучшении уровня подготовки спортсмена, то мы выберем тип графика Stacked Area, в котором значения показателей складываются:

img6

На этом графике видно, во-первых, рост всех показателей, а во-вторых — уменьшение сезонного фактора со временем.

Выведем на одном графике статистику по суммарному пробегу в России / вне России и в виртуальных заездах с сегментацией по кварталам.

SELECT
    round(sum(CASE WHEN country == 'Россия' AND NOT "Activity Type" = 'Virtual Ride'
        THEN distance END))
    AS "Russia total, km",
    round(sum(CASE WHEN NOT country == 'Россия' AND NOT "Activity Type" = 'Virtual Ride'
        THEN distance END))
    AS "Outside Russia total, km",
    round(sum(CASE WHEN "Activity Type" = 'Virtual Ride' THEN distance END))
    AS "Virtual Ride total, km",
    date_trunc('quarter', time_stamp) AS period
FROM demo.geo_tracks_silver
GROUP BY period
ORDER BY period
Done in 8.7 sec.

+------------------+--------------------------+------------------------+------------+
| russia total, km | outside russia total, km | virtual ride total, km | period     |
+------------------+--------------------------+------------------------+------------+
| 423              | null                     | null                   | 2014-07-01 |
+------------------+--------------------------+------------------------+------------+
| 438              | null                     | null                   | 2014-10-01 |
+------------------+--------------------------+------------------------+------------+
| 29               | 12                       | null                   | 2015-01-01 |
+------------------+--------------------------+------------------------+------------+
| 365              | 25                       | null                   | 2015-04-01 |
+------------------+--------------------------+------------------------+------------+
| 857              | 569                      | null                   | 2015-07-01 |
+------------------+--------------------------+------------------------+------------+
| ...              | ...                      | ...                    | ...        |
+------------------+--------------------------+------------------------+------------+
45 rows

Для такого графика хорошо подойдет тип Stacked Bar:

img11

На этом графике можно заметить разные зависимости: сезонный фактор влияет на количество виртуальных заездов — зимой их больше; активности вне России сначала есть только в летние периоды, а позже активности вне России остаются единственными. Видно также общее снижение сезонного фактора, как и на предыдущем графике.

Анализ точек по их расстоянию от заданной

Нам известно, что пользователь, чей архив активностей мы изучаем, жил в Москве, поэтому попробуем проанализировать его активности в зависимости от расстояния от Москвы. Для этого возьмем координаты нулевого километра Москвы: 55.75579845052788, 37.617679973467204 и создадим таблицу, в которой для каждой активности будет вычислено расстояние ее начальной точки от Москвы в километрах. Используем для этого функции ST_Point и ST_Distance_Sphere.

CREATE OR REPLACE TABLE demo_geo_tracks_by_moscow AS
SELECT
    ST_Distance_Sphere(ST_Point(track_first_coord_lat, track_first_coord_lon),
                       ST_Point(55.75579845052788, 37.617679973467204)
                       )/1000
    AS km_from_moscow,
    track_id,
FROM demo_geo_tracks_silver;

SELECT * FROM demo_geo_tracks_by_moscow
    LIMIT 10;
Done in 9.3 sec.

+--------------------+-----------------+
| km_from_moscow     | track_id        |
+--------------------+-----------------+
| 1614.8913738847054 | 8205566762.fit  |
+--------------------+-----------------+
| 8.000389153235107  | 5758691664.fit  |
+--------------------+-----------------+
| 2196.013494904038  | 7562241822.fit  |
+--------------------+-----------------+
| 6.0300128287340815 | 5752363946.fit  |
+--------------------+-----------------+
| 88.17899082303168  | 5774225138.fit  |
+--------------------+-----------------+
| 6.033077121995654  | 3602018438.fit  |
+--------------------+-----------------+
| 890.3163236663786  | 2665481253.fit  |
+--------------------+-----------------+
| 7501.918475014584  | 5061483340.fit  |
+--------------------+-----------------+
| 2.926600115019104  | 1842519266.fit  |
+--------------------+-----------------+
| 3770.6424243313804 | 11417255201.fit |
+--------------------+-----------------+

Посмотрим на распределение суммарной длины треков в зависимости от дальности начала трека от Москвы с шагом по 100 км. Для этого используем функцию floor.

SELECT
	floor(km_from_moscow/100)*100 AS "Distance from Moscow, km",
	round(sum(distance)) AS "Total tracks distance, km"
FROM demo_geo_tracks_by_moscow
    JOIN demo_geo_tracks_silver
        USING (track_id)
    WHERE NOT "Activity Type" = 'Virtual Ride'
GROUP BY 1
ORDER BY 1;
Done in 8.3 sec.

+--------------------------+---------------------------+
| distance from moscow, km | total tracks distance, km |
+--------------------------+---------------------------+
| 0                        | 35205                     |
+--------------------------+---------------------------+
| 100                      | 457                       |
+--------------------------+---------------------------+
| 200                      | 5                         |
+--------------------------+---------------------------+
| 800                      | 1191                      |
+--------------------------+---------------------------+
| 1200                     | 613                       |
+--------------------------+---------------------------+
| ...                      | ...                       |
+--------------------------+---------------------------+
29 rows
img7

На графике мы видим, что есть несколько столбцов заметно выше соседних. Они соответствуют разным городам, из которых пользователь совершал регулярные тренировки. Кроме Москвы это три города:

Вычислим для всех активностей расстояния до этих городов:

CREATE OR REPLACE TABLE demo_geo_tracks_by_cities AS
SELECT
    track_id,
    distance,
    ST_Distance_Sphere(ST_Point(track_first_coord_lat, track_first_coord_lon),
                        ST_Point(55.75579845052788, 37.617679973467204)
                        )/1000
        AS km_from_moscow,
    ST_Distance_Sphere(ST_Point(track_first_coord_lat, track_first_coord_lon),
                        ST_Point(36.71011042348239, -4.429510437480414)
                        )/1000
        AS km_from_malaga,
    ST_Distance_Sphere(ST_Point(track_first_coord_lat, track_first_coord_lon),
                        ST_Point(52.51622694136956, 13.377732359062023)
                        )/1000
        AS km_from_berlin,
    ST_Distance_Sphere(ST_Point(track_first_coord_lat, track_first_coord_lon),
                        ST_Point(36.544934513586334, 31.993451012738028)
                        )/1000
        AS km_from_alanya,
FROM demo_geo_tracks_silver;

SELECT * FROM demo_geo_tracks_by_cities
    LIMIT 10;
+-----------------+----------+--------------------+--------------------+--------------------+--------------------+
| track_id        | distance | km_from_moscow     | km_from_malaga     | km_from_berlin     | km_from_alanya     |
+-----------------+----------+--------------------+--------------------+--------------------+--------------------+
| 8205566762.fit  | 51.04    | 1614.8913738847054 | 2235.249319891205  | 5.0145197840760245 | 2294.093997594629  |
+-----------------+----------+--------------------+--------------------+--------------------+--------------------+
| 5758691664.fit  | 3.91     | 8.000389153235107  | 3787.2509319742358 | 1618.720214954235  | 2180.111417985302  |
+-----------------+----------+--------------------+--------------------+--------------------+--------------------+
| 7562241822.fit  | 21.69    | 2196.013494904038  | 3104.0466510427395 | 2221.451446627775  | 127.78858146201004 |
+-----------------+----------+--------------------+--------------------+--------------------+--------------------+
| 5752363946.fit  | 62.85    | 6.0300128287340815 | 3785.4665468428007 | 1616.7118606890685 | 2180.242964284234  |
+-----------------+----------+--------------------+--------------------+--------------------+--------------------+
| 5774225138.fit  | 114.24   | 88.17899082303168  | 3707.0000971542977 | 1528.712391035889  | 2186.697705370458  |
+-----------------+----------+--------------------+--------------------+--------------------+--------------------+
| 3602018438.fit  | 34.76    | 6.033077121995654  | 3785.4691666595877 | 1616.7152967306572 | 2180.24094049402   |
+-----------------+----------+--------------------+--------------------+--------------------+--------------------+
| 2665481253.fit  | 43.88    | 890.3163236663786  | 3051.4212916480724 | 811.7057240823138  | 2370.646300384682  |
+-----------------+----------+--------------------+--------------------+--------------------+--------------------+
| 5061483340.fit  | 16.87    | 7501.918475014584  | 5886.25300373306   | 6375.612772054101  | 8583.236350749687  |
+-----------------+----------+--------------------+--------------------+--------------------+--------------------+
| 1842519266.fit  | 71.94    | 2.926600115019104  | 3780.985953704283  | 1612.7664280078745 | 2176.465565328427  |
+-----------------+----------+--------------------+--------------------+--------------------+--------------------+
| 11417255201.fit | 30.91    | 3770.6424243313804 | 12.516233931245793 | 2232.9498881694144 | 3217.9897501282717 |
+-----------------+----------+--------------------+--------------------+--------------------+--------------------+

Выведем статистику по суммарной дистанции треков с началом в разных городах. В качестве порогового значения расстояния возьмем 100 км:

SELECT
    round(sum(CASE WHEN km_from_moscow < 100 THEN distance END))
        AS "Moscow total, km",
    round(sum(CASE WHEN km_from_malaga < 100 THEN distance END))
        AS "Malaga total, km",
    round(sum(CASE WHEN km_from_alanya < 100 THEN distance END))
        AS "Alanya total, km",
    round(sum(CASE WHEN km_from_berlin < 100 THEN distance END))
        AS "Berlin total, km",
    round(sum(CASE WHEN
        (
            km_from_moscow >= 100 AND
            km_from_malaga >= 100 AND
            km_from_alanya >= 100 AND
            km_from_berlin >= 100
        )
        THEN distance END))
        AS "Rest total, km",
    round(sum(distance)) AS "Total, km"
FROM demo_geo_tracks_by_cities
+------------------+------------------+------------------+------------------+----------------+-----------+
| moscow total, km | malaga total, km | alanya total, km | berlin total, km | rest total, km | total, km |
+------------------+------------------+------------------+------------------+----------------+-----------+
| 35155            | 28969            | 8136             | 5841             | 21242          | 99343     |
+------------------+------------------+------------------+------------------+----------------+-----------+

Отображение множества точек на карте

Для демонстрации отображения точек на карте создадим отдельную таблицу с точками одной активности в пределах Москвы с максимальной дистанцией за все время.

CREATE OR REPLACE TABLE demo_geo_points_for_map AS
    SELECT * FROM demo_geo_points_silver
        WHERE
        (track_id IN (
        SELECT track_id
            from demo_geo_tracks_by_distance
                JOIN demo_geo_tracks_silver
                    USING (track_id)
            WHERE km_from_moscow < 100
            ORDER BY distance DESC
            LIMIT 1
            )
    );

SELECT count(*) AS "Points for map total"
    FROM demo_geo_points_for_map;
Done in 9.7 sec.

+----------------------+
| points for map total |
+----------------------+
| 16458                |
+----------------------+

Теперь в ячейке типа Python запишем код, с помощью которого можно обратиться к созданной таблице с отфильтрованными точками и отобразить их на карте OSM, используя модуль Python TileMapBase:

import tngri
import pyarrow
import pyiceberg
from pyiceberg.catalog import load_catalog
from contextlib import suppress
import pandas as pd
import matplotlib.pyplot as plt
import tilemapbase
import warnings
warnings.filterwarnings("ignore")

def print_map(source_table_name):

    # Загружаем исходную таблицу
    source_table = catalog.load_table(source_table_name)

    # Преобразуем в DataFrame
    trip_df = source_table.scan().to_pandas()
    print(f'Размер исходной таблицы: {trip_df.shape}')

    tilemapbase.init(create=True)

    # Размер полей карты вокруг крайних точек трека
    expand=0.01
    extent = tilemapbase.Extent.from_lonlat(
        trip_df.longitude.min() - expand,
        trip_df.longitude.max() + expand,
        trip_df.latitude.min() - expand,
        trip_df.latitude.max() + expand,
    )

    track_names = trip_df['track_id'].unique()
    print(f'Количество треков: {len(track_names)}')

    # Выводим суммарную дистанцию треков в исходной таблице
    tracks_dist = tngri.sql('''
    select round(sum(distance)) as "Суммарная дистанция треков, км"
    from demo.geo_tracks_silver
    where track_id in (
            select track_id
            from demo.geo_points_for_map)
    ''')
    print(f'Суммарная дистанция треков: {tracks_dist[0,0]} км')

    track_list = []
    for track_name in track_names:
        track = trip_df.loc[trip_df['track_id'] == track_name].apply(
            lambda x: tilemapbase.project(x.longitude, x.latitude), axis=1
        ).apply(pd.Series)
        track.columns = ["x", "y"]
        track_list.append(track)

    tiles = tilemapbase.tiles.build_OSM()

    fig, ax = plt.subplots(figsize=(8, 8), dpi=300)

    # height -- детализация карты
    plotter = tilemapbase.Plotter(extent, tiles, height=600)

    # alpha -- замутнение карты
    plotter.plot(ax, tiles, alpha=0.6)

    # Если треков несколько, то толщину линии делаем меньше
    if len(track_names) == 1:
        width = 1
    else:
        width = 0.5
    for track in track_list:
        ax.plot(track.x, track.y, color='blue', linewidth=width)

    plt.axis('off')
    fig.get_figure()

print_map('demo_geo_points_for_map')
Done in 30 sec.

Размер исходной таблицы: (16458, 8)
Количество треков: 1
Суммарная дистанция треков: 135.0 км
img9

Обратите внимание, что для вывода суммарной дистанции отображаемых треков мы используем вызов запроса SELECT в ячейке Python через функцию tngri.sql.

Теперь перезапишем таблицу с координатами точек для отображения на карте, выбрав все активности с началом в Берлине, чтобы отобразить их все на одной карте.

CREATE OR REPLACE TABLE demo_geo_points_for_map AS
    SELECT
        latitude,
        longitude,
        track_id
    FROM demo_geo_points_silver
    WHERE
    (track_id IN (
        SELECT track_id
            FROM demo_geo_tracks_by_distance
            WHERE km_from_berlin < 100
        )
    );

SELECT * FROM demo_geo_points_for_map
    LIMIT 10;
Done in 9.6 sec.

+--------------------+--------------------+----------------+
| latitude           | longitude          | track_id       |
+--------------------+--------------------+----------------+
| 52.529693618416786 | 13.444848032668233 | 9440992383.fit |
+--------------------+--------------------+----------------+
| 52.52970124594867  | 13.44483470544219  | 9440992383.fit |
+--------------------+--------------------+----------------+
| 52.52970124594867  | 13.44481467269361  | 9440992383.fit |
+--------------------+--------------------+----------------+
| 52.52969739027321  | 13.444789862260222 | 9440992383.fit |
+--------------------+--------------------+----------------+
| 52.5296859908849   | 13.444773685187101 | 9440992383.fit |
+--------------------+--------------------+----------------+
| 52.52967827953398  | 13.4447565022856   | 9440992383.fit |
+--------------------+--------------------+----------------+
| 52.529674507677555 | 13.444727919995785 | 9440992383.fit |
+--------------------+--------------------+----------------+
| 52.529674507677555 | 13.44469740986824  | 9440992383.fit |
+--------------------+--------------------+----------------+
| 52.529670652002096 | 13.444674527272582 | 9440992383.fit |
+--------------------+--------------------+----------------+
| 52.529670652002096 | 13.444655416533351 | 9440992383.fit |
+--------------------+--------------------+----------------+

Выведем количество точек в созданной таблице:

SELECT count(*) AS "Points for map total"
    FROM demo_geo_points_for_map
+----------------------+
| points for map total |
+----------------------+
| 708616               |
+----------------------+

Запустим в ячейке Python выполнение созданной ранее функции print_map для обновленной таблицы с тем же именем и отобразим карту со всеми активностями в Берлине:

print_map('demo_geo_points_for_map')
Done in 1 min. 6 sec.

Размер исходной таблицы: (708616, 3)
Количество треков: 108
Суммарная дистанция треков: 5841.0 км
img8

Теперь отобразим на карте все треки в радиусе 3 км от Велотрека в Москве.

CREATE OR REPLACE TABLE demo.geo_points_for_map AS
SELECT
    latitude,
    longitude,
    track_id
FROM demo.geo_points_silver
WHERE track_id NOT IN
    (SELECT DISTINCT track_id
    FROM demo.geo_points_silver
    WHERE ST_Distance_Sphere(ST_Point(latitude, longitude),
                           ST_Point(55.76314006886495, 37.43303308055759)
                           )/1000 > 3);

SELECT count(*) AS "Points for map total"
    FROM demo.geo_points_for_map;
+----------------------+
| points for map total |
+----------------------+
| 215466               |
+----------------------+

Запустим отображение карты:

print_map('demo_geo_points_for_map')
Done in 29 sec.

Размер исходной таблицы: (215466, 3)
Количество треков: 43
Суммарная дистанция треков: 2063.0 км
img10