J'aime beaucoup datasette, un outil libre pour explorer, mettre en forme et partager des données.
J'utilise geovelo pour suivre mes trajets du quotidien (l'appli détecte les trajets à vélo automatiquement, ce qui est pratique). Du coup je me suis essayé à explorer ces données avec datasette, et à les enrichir un peu pour déterminer lesquels sont des trajets domicile-travail, et m'aider ainsi au moment de remplir ma demande de forfait mobilités durables.
Récupérer ses données
Dans l'interface web de geovelo, il faut aller sur son profil et, sur la droite, trouver un menu kebab offrant une option "Demander mes données".
Après avoir demandé cette récupération, une moulinette mouline et un email nous prévient de la disponibilité
d'une archive zip. L'archive en question contient notamment un fichier sent_traces.json qui regroupe les
trajets à vélo.
Importer les trajets à vélo dans une base de données sqlite
L'auteur de datasette a aussi développé la bibliothèque sqlite-utils, qui offre un import aisé de nombreux formats de données (CSV, JSON...) vers sqlite ainsi qu'un grand nombre d'outils pour manipuler les bases de données elles-mêmes.
Installons cette bibliothèque:
pip install sqlite-utils
Puis insérons dans une nouvelle base de données geovelo.db les traces
que nous avons précédemment extraites de l'archive:
sqlite-utils insert geovelo.db traces sent_traces.json --pk id --replace
(le --replace est là pour permettre des imports ultérieurs)
Explorer les trajets
Installons datasette ainsi que deux plugins qui vont nous être utiles.
pip install datasette
datasette install datasette-leaflet-geojson
datasette install datasette-sqlite-tg
Lançons maintenant datasette:
datasette -p 0 geovelo.db
Et ouvrons la page indiquée à l'aide d'un navigateur.
On a déjà pas mal d'infos ! Le plugin datasette-leaflet-geojson affiche une carte
pour représenter chaque colonne contenant une valeur GeoJSON, donc tout de go, on
peut déjà regarder nos trajets (dans la colonne geometry),
les trier par distance parcourue, vitesse moyenne, date etc.
L'interface utilisateur de datasette est super bien pensée pour ce travail exploratoire.
Déterminer les trajets domicile-travail
On peut se dire qu'un trajet domicile-travail est un trajet qui débute ou finit autour de son lieu de travail. Essayons de les sélectionner dans notre base de données.
D'abord, dessinons un joli carré ou rectangle autour de notre lieu de travail (mettons avec 100m de côté ?)
Pour ceci, on peut utiliser un outil comme geojson.io.
En traçant un rectangle autour d'une zone d'intérêt on obtient un résultat de la forme:
{
"type": "FeatureCollection",
"features": [
{
"type": "Feature",
"properties": {},
"geometry": {
"coordinates": [
[
[
-4.512467771437315,
48.3120255080224
],
[
-4.512467771437315,
48.30399688973441
],
[
-4.493710657652116,
48.30399688973441
],
[
-4.493710657652116,
48.3120255080224
],
[
-4.512467771437315,
48.3120255080224
]
]
],
"type": "Polygon"
}
}
]
}
On peut extraire les informations de géométrie et les mettre dans un fichier geom.json qui contiendra
le polygone:
{
"geometry": {
"coordinates": [ ... ],
"type": "Polygon"
}
}
Et insérer cet unique enregistrement dans une nouvelle table workplace_rect de notre base:
sqlite-utils create-table geovelo.db workplace_rect geometry text
sqlite-utils insert geovelo.db workplace_rect geom.json
On peut maintenant utiliser claude ou toute autre solution pour générer une requête qui considérera comme trajet domicile-travail un trajet dont le premier ou le dernier point est contenu dans notre polygone.
(note: sqlite a un bon support de JSON, l'extraction de la première coordonnée du
trajet et sa mise en forme sous forme de Point GeoJSON est ainsi aisée).
WITH points_extracted AS (
SELECT
t.id,
t.title,
t.geometry,
t.speeds,
t.elevations,
t.distance,
t.duration,
t.average_speed,
t.vertical_gain,
t.vertical_loss,
t.start_datetime,
t.end_datetime,
-- Create first_point GeoJSON
json_object(
'type', 'Point',
'coordinates', json_extract(t.geometry, '$.coordinates[0]')
) AS first_point,
-- Create last_point GeoJSON
json_object(
'type', 'Point',
'coordinates', json_extract(
t.geometry,
'$.coordinates[' || (json_array_length(json_extract(t.geometry, '$.coordinates')) - 1) || ']'
)
) AS last_point
FROM traces t
)
SELECT
id,
title,
geometry,
speeds,
elevations,
distance,
duration,
average_speed,
vertical_gain,
vertical_loss,
start_datetime,
end_datetime,
first_point,
last_point,
-- Determine if this is a commute
CASE
WHEN (
-- Check if date is weekday (1-5 represents Monday-Friday)
CAST(strftime('%w', start_datetime) AS INTEGER) BETWEEN 1 AND 5
AND
(
-- Check if workplace rectangle contains first point
tg_contains(
tg_geom((SELECT geometry FROM workplace_rect)),
tg_geom(first_point)
)
OR
-- Check if workplace rectangle contains last point
tg_contains(
tg_geom((SELECT geometry FROM workplace_rect)),
tg_geom(last_point)
)
)
) THEN 1 ELSE 0 END AS is_commute
FROM points_extracted
On peut transformer cette requête en vue:
sqlite-utils create-view geovelo.db traces_with_commute '
WITH points_extracted AS (
SELECT
t.id,
t.title,
t.geometry,
t.speeds,
t.elevations,
t.distance,
t.duration,
t.average_speed,
t.vertical_gain,
t.vertical_loss,
t.start_datetime,
t.end_datetime,
-- Create first_point GeoJSON
json_object(
"type", "Point",
"coordinates", json_extract(t.geometry, "$.coordinates[0]")
) AS first_point,
-- Create last_point GeoJSON
json_object(
"type", "Point",
"coordinates", json_extract(
t.geometry,
"$.coordinates[" || (json_array_length(json_extract(t.geometry, "$.coordinates")) - 1) || "]"
)
) AS last_point
FROM traces t
)
SELECT
id,
title,
geometry,
speeds,
elevations,
distance,
duration,
average_speed,
vertical_gain,
vertical_loss,
start_datetime,
end_datetime,
first_point,
last_point,
-- Determine if this is a commute
CASE
WHEN (
-- Check if date is weekday (1-5 represents Monday-Friday)
CAST(strftime("%w", start_datetime) AS INTEGER) BETWEEN 1 AND 5
AND
(
-- Check if workplace rectangle contains first point
tg_contains(
tg_geom((SELECT geometry FROM workplace_rect)),
tg_geom(first_point)
)
OR
-- Check if workplace rectangle contains last point
tg_contains(
tg_geom((SELECT geometry FROM workplace_rect)),
tg_geom(last_point)
)
)
) THEN 1 ELSE 0 END AS is_commute
FROM points_extracted
'
Puis chercher les trajets domicile-travail en 2025
select id, title, geometry, start_datetime, end_datetime, is_commute from traces_with_commute where
strftime('%Y', start_datetime) = '2025' and is_commute=1
ORDER BY start_datetime
Et compter le nombre de jours où un trajet domicile-travail (ou plus) a été effectué à vélo.
SELECT COUNT(DISTINCT date(start_datetime)) AS unique_commute_days
FROM traces_with_commute
WHERE
strftime('%Y', start_datetime) = '2025'
AND is_commute = 1