Logistique

Étude de cas Logistique et Recueil de recettes SQL

Activer IoT Query avant d'utiliser les données pour construire des analyses complètes. Si vous ne l'avez pas encore, contactez-nous pour les détails d'activation - [email protected]

La logistique est un écosystème complexe impliquant la coordination du transport, des opérations d'entrepôt, des stocks et de l'exécution des livraisons. L'intégration de la télématique dans les processus logistiques permet aux entreprises de collecter des données temps réel sur les véhicules, les conducteurs, les itinéraires et les conditions de cargaison, ce qui améliore considérablement la prise de décision et l'efficacité opérationnelle.

Navixy IoT Query, avec ses capacités robustes d'ingestion de données et d'analyse de séries temporelles, soutient la transformation numérique des opérations logistiques en permettant une visibilité approfondie à chaque étape du cycle de vie. Ses capacités robustes d'ingestion télématique fournissent une visibilité complète sur ces opérations. Les données GPS en temps réel, le diagnostic des données des capteurs, le géorepérage et l'analyse des capteurs permettent aux opérateurs logistiques de numériser les flux de travail, d'automatiser les contrôles et de prendre des décisions éclairées.

Phase du cycle de vie	Objectifs	Cas d'utilisation couverts / Recettes
Gestion des itinéraires	Optimiser le routage des véhicules, assurer un dispatch efficace et réduire les retards	Nombre de trajets par jour Compte kilométrique par véhicule et par jour (7 derniers jours)
Surveillance de la cargaison	Garantir des conditions de transport appropriées pour les marchandises sensibles	Événements de violation de température (et d'humidité) au cours des 7 derniers jours
Exploitation des véhicules	Suivre l'utilisation de la flotte, assurer la maintenance et réduire les temps d'immobilisation	Résumé des heures moteur par véhicule / conducteur / jour (7 derniers jours) Analyse des temps d'immobilisation des véhicules Suivi des actifs sans mouvement
Sécurité et sûreté des itinéraires	Détecter les usages abusifs, les activités non autorisées et les violations de sécurité	Détection de déviation d'itinéraire - Arrêts non autorisés (24 dernières heures) Détection d'utilisation hors heures
Gestion de la conformité	Surveiller le comportement des conducteurs, appliquer les politiques et la conformité opérationnelle	Résumé des heures moteur par véhicule / conducteur / jour (7 derniers jours) Détection d'utilisation hors heures
Analyse post-livraison	Évaluer l'efficacité opérationnelle et la performance historique	Rapport de journal d'événements du véhicule Compte kilométrique par véhicule et par jour (7 derniers jours) Nombre de trajets par jour Suivi des actifs sans mouvement

Suivi des actifs sans mouvement

Ce cas identifie les actifs (par ex., véhicules ou remorques) qui n'ont pas modifié leurs GPS comparer le coordonnées minimales et maximales pendant la période. Si les deux valeurs se situent dans une fourchette très étroite (un seuil de tolérance, par ex. ±0,01 degré), nous signalons l'actif comme immobile. La requête effectue également une jointure avec les tables objects et vehicles dans raw_business_data pour récupérer des libellés d'actifs significatifs pour la sortie des résultats.

WITH gps_bounds AS (
    SELECT
        td.device_id,
        MIN(td.latitude) AS min_lat,
        MAX(td.latitude) AS max_lat,
        MIN(td.longitude) AS min_lon,
        MAX(td.longitude) AS max_lon,
        COUNT(*) AS location_records
    FROM raw_telematics_data.tracking_data_core td
    WHERE td.device_time >= now() - interval '48 hours'
    GROUP BY td.device_id
),
stationary_devices AS (
    SELECT
        device_id
    FROM gps_bounds
    WHERE location_records > 10 -- exclure les appareils avec des données très clairsemées
	AND((max_lat - min_lat) <= 2000 -- ~10 mètres
		OR
      	(max_lon - min_lon) <= 1000)  -- ~10 mètres
    		)
SELECT
    v.vehicle_id,
    v.vehicle_label,
    o.object_id,
    o.object_label,
    sd.device_id,
    gb.min_lat / 1e7 AS latitude,
    gb.min_lon / 1e7 AS longitude,
    gb.location_records
FROM stationary_devices sd
JOIN gps_bounds gb ON sd.device_id = gb.device_id
JOIN raw_business_data.objects o ON o.device_id = sd.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
ORDER BY gb.location_records DESC;

Analyse des temps d'immobilisation des véhicules

Ce cas se concentre sur l'analyse de la durée pendant laquelle les véhicules sont non opérationnels en raison de la maintenance, de pannes ou d'inactivité. Les indicateurs de temps d'immobilisation sont cruciaux pour les opérations logistiques afin de surveiller la santé de la flotte, réduire les temps d'arrêt et améliorer l'utilisation globale et l'efficacité de la planification.

Le cœur de l'analyse des temps d'immobilisation repose sur l'utilisation de la table vehicle_service_tasks de raw_business_data, qui enregistre à la fois les événements de maintenance planifiés et non planifiés. Chaque tâche contient une start_date et une end_date, représentant la période d'immobilisation. En filtrant pour les tâches de service terminées, nous pouvons calculer la durée exacte pendant laquelle chaque véhicule était hors service.

La requête calcule le temps d'immobilisation total par véhicule en sommant les durées de toutes ses tâches de service (en heures). Elle permet également une ventilation entre maintenance planifiée et non planifiée en utilisant le drapeau is_unplanned. Pour rendre les résultats plus exploitables, elle effectue une jointure avec la table vehicles afin d'inclure les libellés des véhicules, les numéros d'immatriculation et les informations de modèle.

WITH downtime_durations AS (
    SELECT
        vst.vehicle_id,
        vst.is_unplanned,
        vst.start_date,
        vst.end_date,
        EXTRACT(EPOCH FROM (vst.end_date - vst.start_date))/3600 AS downtime_hours
    FROM raw_business_data.vehicle_service_tasks vst
    WHERE vst.status = 'done'
      AND vst.start_date IS NOT NULL
      AND vst.end_date IS NOT NULL
)
SELECT
    v.vehicle_id,
    v.vehicle_label,
    v.registration_number,
    v.model,
    COUNT(dd.*) AS total_service_events,
    SUM(dd.downtime_hours) AS total_downtime_hours,
    SUM(dd.downtime_hours) FILTER (WHERE dd.is_unplanned = TRUE) AS unplanned_downtime_hours,
    SUM(dd.downtime_hours) FILTER (WHERE dd.is_unplanned = FALSE) AS planned_downtime_hours
FROM downtime_durations dd
JOIN raw_business_data.vehicles v ON v.vehicle_id = dd.vehicle_id
GROUP BY v.vehicle_id, v.vehicle_label, v.registration_number, v.model
ORDER BY total_downtime_hours DESC;

Détection de déviation d'itinéraire

Ce cas identifie les instances où les véhicules s'écartent de leurs itinéraires assignés ou attendus — en particulier des zones géorepérées ou des corridors de livraison. Le suivi de ces déviations aide à garantir la conformité des itinéraires, à réduire les retards, à détecter les comportements de conduite à risque et à maintenir les SLA de livraison.

Cette logique compare les positions GPS réelles du véhicule provenant de tracking_data_core (dans le schéma raw_telematics_data) aux zones géographiques prédéfinies de la table zones dans raw_business_data. Ces zones représentent des itinéraires assignés ou des segments d'itinéraire. En utilisant des comparaisons géométriques via ST_DWithin, nous déterminons si un point est à l'intérieur ou à l'extérieur de la zone tampon de l'itinéraire.

La requête associe chaque position GPS à chaque zone d'itinéraire connue en utilisant un CROSS JOIN, puis applique ST_DWithin() pour vérifier si le véhicule se trouvait à l'intérieur du corridor autorisé. Nous isolons les lignes où le véhicule était en dehors de toutes les routes géorepérées et les signalons comme des déviations. La sortie finale répertorie ces déviations, incluant l'appareil, l'horodatage, le libellé du véhicule et la distance entre le point et le centre de la zone la plus proche.

WITH positions AS (
    SELECT
        td.device_id,
        td.device_time,
        o.object_id,
        o.object_label,
        z.zone_id,
        z.zone_label,
        ST_SetSRID(ST_MakePoint(td.longitude / 1e7, td.latitude / 1e7), 4326)::geography AS gps_point,
        ST_Buffer(
            ST_SetSRID(ST_MakePoint(z.circle_center_longitude, z.circle_center_latitude), 4326)::geography,
            z.radius
        ) AS route_buffer
    FROM raw_telematics_data.tracking_data_core td
    JOIN raw_business_data.objects o ON td.device_id = o.device_id
    CROSS JOIN raw_business_data.zones z
    WHERE td.device_time >= now() - interval '2 days'
),
evaluated AS (
    SELECT
        device_id,
        device_time,
        object_id,
        object_label,
        zone_id,
        zone_label,
        NOT ST_DWithin(gps_point, route_buffer, 0) AS is_deviation,
        ST_Distance(gps_point, route_buffer) AS deviation_distance_meters
    FROM positions
),
deviations_only AS (
    SELECT *
    FROM evaluated
    WHERE is_deviation = true
)
SELECT
    device_id,
    object_label,
    zone_label,
    device_time,
    deviation_distance_meters
FROM deviations_only
ORDER BY device_time DESC;

Résumé des heures moteur par véhicule / conducteur / jour (7 derniers jours)

Ce cas mesure la durée pendant laquelle les moteurs ont été actifs pour chaque véhicule sur une base quotidienne, permettant aux gestionnaires de flotte de suivre l'utilisation, d'identifier la surutilisation ou la sous-utilisation, et de corréler l'activité avec les affectations de conducteurs. Lorsqu'il est lié aux conducteurs, il prend également en charge la validation des heures de travail et l'analyse de performance.

La table states dans raw_telematics_data enregistre des indicateurs d'état moteur en séries temporelles, généralement avec un state_name comme 'ignition' et une valeur de 1 (allumé) ou 0 (éteint). Pour calculer les heures moteur, nous trouvons toutes les transitions horodatées pour chaque appareil et calculons les durées pendant lesquelles le moteur était allumé (1).

Pour lier l'activité moteur à la fois aux véhicules et aux conducteurs, nous utilisons les tables objects, vehicles et driver_history de raw_business_data. Nous associons chaque enregistrement d'état au conducteur courant sur cet objet (via l'historique d'affectation des conducteurs) et au véhicule correspondant. Nous groupons ensuite les données par jour, véhicule et conducteur, en sommant le temps moteur actif total (en heures).

WITH inputs_core AS (
   SELECT
       i.device_id,
       i.device_time,
       i.event_id,
       i.record_added_at,
       i.sensor_name,
       i.value
   FROM raw_telematics_data.inputs i
   WHERE i.device_time >= now() - interval '7 days'
),
clear_inputs AS (
   SELECT
       i.device_id,
       i.device_time,
       i.event_id,
       i.record_added_at,
       i.sensor_name,
       i.value,
       LAG(i.device_time) OVER (PARTITION BY i.device_id ORDER BY i.device_time) AS prev_time,
       LAG(i.value::int) OVER (PARTITION BY i.device_id ORDER BY i.device_time) AS prev_status
   FROM inputs_core i
   LEFT JOIN raw_business_data.sensor_description sd
       ON sd.input_label = i.sensor_name
      AND sd.device_id = i.device_id
   WHERE sd.sensor_type = 'engine'
     AND i.value = '1'
),
engine_on_periods AS (
   SELECT
       device_id,
       prev_time AS engine_on_time,
       device_time AS engine_off_time,
       device_time::date AS activity_day,
       EXTRACT(EPOCH FROM (device_time - prev_time)) / 3600 AS engine_hours
   FROM clear_inputs
   WHERE value::int = 0 AND prev_status = 1  -- transitions de ON à OFF
),
enriched_with_objects AS (
   SELECT
       eop.*,
       o.object_id,
       o.object_label,
       v.vehicle_id,
       v.vehicle_label,
       v.registration_number
   FROM engine_on_periods eop
   JOIN raw_business_data.objects o ON o.device_id = eop.device_id
   LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
),
assigned_drivers AS (
   SELECT
       ewo.*,
       dh.new_employee_id as employee_id
   FROM enriched_with_objects ewo
   LEFT JOIN LATERAL (
       SELECT d.*
       FROM raw_business_data.driver_history d
       WHERE d.object_id = ewo.object_id
         AND d.changed_datetime <= ewo.engine_on_time
       ORDER BY d.changed_datetime DESC
       LIMIT 1
   ) dh ON true
)
SELECT
   activity_day,
   vehicle_label,
   registration_number,
   object_label,
   e.first_name || ' ' || e.last_name AS driver_name,
   SUM(engine_hours) AS total_engine_hours
FROM assigned_drivers ad
LEFT JOIN raw_business_data.employees e ON ad.employee_id = e.employee_id
GROUP BY activity_day, vehicle_label, registration_number, object_label, driver_name
ORDER BY activity_day DESC, vehicle_label;

Événements de violation de température (et d'humidité) au cours des 7 derniers jours

Ce cas identifie des relevés de capteurs — tels que température ou humidité — qui dépassent des seuils critiques pendant le transport. La surveillance de telles violations est vitale pour les industries transportant des produits périssables (par ex., alimentaire, pharmaceutique) afin d'assurer la conformité à la chaîne du froid et d'empêcher la détérioration.

Cette requête extrait les données d'entrée des capteurs de la table inputs dans le schéma raw_telematics_data. Chaque ligne représente une lecture de capteur (par ex., température, humidité) enregistrée à un horodatage spécifique par un appareil. Nous filtrons ces enregistrements pour n'inclure que ceux des 7 derniers jours.

La logique principale de filtrage est basée sur les motifs de nom de capteur et une comparaison de leurs valeurs numériques par rapport aux seuils (par ex., >25°C pour la température, >80% pour l'humidité). Parce que la valeur est stockée en tant que texte, nous la convertissons en numérique avant d'appliquer les conditions de seuil. Pour enrichir les résultats, nous effectuons une jointure avec la table objects afin de récupérer les libellés des véhicules ou des actifs, ce qui améliore l'interprétabilité pour les gestionnaires de flotte.

WITH recent_sensor_data AS (
   SELECT
       i.device_id,
       i.device_time,
       i.sensor_name,
       i.value::float AS value
   FROM raw_telematics_data.inputs i
   WHERE i.device_time >= now() - interval '1 hour'
),
sensor_meta AS (
   SELECT
       sd.device_id,
       sd.input_label,
       sd.sensor_id,
       sd.sensor_type,
       sd.calibration_data
   FROM raw_business_data.sensor_description sd
),
joined_data AS (
   SELECT
       rsd.*,
       sm.sensor_id,
       sm.sensor_type,
       sm.calibration_data
   FROM recent_sensor_data rsd
   LEFT JOIN sensor_meta sm
       ON rsd.device_id = sm.device_id
      AND rsd.sensor_name = sm.input_label
),
calibrated_data AS (
   SELECT
       jd.device_id,
       jd.device_time,
       jd.sensor_name,
       jd.value,
       jd.sensor_id,
       jd.sensor_type,
       CASE
           WHEN cd_low.cal_value IS NOT NULL AND cd_high.cal_value IS NOT NULL THEN
               CASE
                   WHEN cd_high.cal_value = cd_low.cal_value THEN cd_low.cal_volume
                   ELSE cd_low.cal_volume +
                       ((jd.value - cd_low.cal_value) / NULLIF(cd_high.cal_value - cd_low.cal_value, 0))
                       * (cd_high.cal_volume - cd_low.cal_volume)
               END
           ELSE jd.value
       END AS calibrated_value
   FROM joined_data jd
   LEFT JOIN LATERAL (
       SELECT
           (p->>'in')::float  AS cal_value,
           (p->>'out')::float AS cal_volume
       FROM jsonb_array_elements(jd.calibration_data) AS p
       WHERE (p->>'in')::float <= jd.value
       ORDER BY (p->>'in')::float DESC
       LIMIT 1
   ) cd_low ON TRUE
   LEFT JOIN LATERAL (
       SELECT
           (p->>'in')::float  AS cal_value,
           (p->>'out')::float AS cal_volume
       FROM jsonb_array_elements(jd.calibration_data) AS p
       WHERE (p->>'in')::float >= jd.value
       ORDER BY (p->>'in')::float ASC
       LIMIT 1
   ) cd_high ON TRUE
),
violations AS (
   SELECT
       cd.device_id,
       cd.device_time,
       cd.sensor_name,
       cd.calibrated_value,
       CASE
           WHEN cd.sensor_type = 'temperature' AND cd.calibrated_value > 25 THEN 'High Temperature'
           WHEN cd.sensor_type = 'temperature' AND cd.calibrated_value < 0 THEN 'Low Temperature'
           WHEN cd.sensor_type = 'humidity' AND cd.calibrated_value > 80 THEN 'High Humidity'
           ELSE NULL
       END AS violation_type
   FROM calibrated_data cd
   WHERE cd.sensor_type IN ('temperature', 'humidity')
)
SELECT
   v.vehicle_label,
   o.object_label,
   v.registration_number,
   v.model,
   vio.device_time,
   vio.sensor_name,
   vio.calibrated_value,
   vio.violation_type
FROM violations vio
JOIN raw_business_data.objects o
  ON o.device_id = vio.device_id
LEFT JOIN raw_business_data.vehicles v
  ON v.object_id = o.object_id
ORDER BY vio.device_time DESC;

Arrêts non autorisés (24 dernières heures)

Ce cas identifie les arrêts non autorisés ou non planifiés effectués par les véhicules au cours des 24 dernières heures. Il aide à détecter d'éventuelles violations des itinéraires de livraison, des pauses non autorisées ou des temps d'inactivité pouvant affecter l'efficacité énergétique et la performance des SLA.

La requête analyse les points de localisation avec une vitesse faible ou nulle en utilisant la requête La requête utilise la table tracking_data_core de raw_telematics_data pour extraire des données de localisation en séries temporelles et la vitesse. Un arrêt est détecté lorsque la vitesse descend en dessous de 3 km/h pendant une durée de plus de 2 minutes. En utilisant les fonctions LAG et LEAD, la requête segmente ces périodes de faible vitesse pour déterminer les horodatages de début et de fin de l'arrêt.

Pour détecter les arrêts non autorisés, elle filtre les emplacements qui se trouvent à l'intérieur des zones géorepérées connues (table zones) en utilisant ST_DWithin de PostGIS. Seuls les arrêts en dehors de tout tampon de zone sont signalés. Le résultat inclut l'ID du véhicule, le libellé de l'objet, l'immatriculation, les horodatages, la durée et les coordonnées pour chaque arrêt.

WITH speed_data AS (
    SELECT
        td.device_id,
        td.device_time,
        td.speed / 100.0 AS speed_kph,
        td.latitude / 1e7 AS lat,
        td.longitude / 1e7 AS lon
    FROM raw_telematics_data.tracking_data_core td
    WHERE td.device_time >= now() - interval '1 day'
),
low_speed_points AS (
    SELECT
        *,
        LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_time,
        LAG(speed_kph) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_speed
    FROM speed_data
),
stops_marked AS (
    SELECT *,
        CASE 
            WHEN speed_kph < 3 AND (prev_speed >= 3 OR prev_speed IS NULL) THEN 1 
            ELSE 0 
        END AS stop_start,
        CASE 
            WHEN speed_kph >= 3 AND prev_speed < 3 THEN 1 
            ELSE 0 
        END AS stop_end
    FROM low_speed_points
),
stop_segments AS (
    SELECT
        device_id,
        device_time AS stop_start_time,
        LEAD(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS stop_end_time,
        lat,
        lon
    FROM stops_marked
    WHERE stop_start = 1
),
unauthorized_stops AS (
    SELECT
        ss.*,
        EXTRACT(EPOCH FROM (ss.stop_end_time - ss.stop_start_time))/60 AS stop_duration_min
    FROM stop_segments ss
    LEFT JOIN raw_business_data.zones z ON
        ST_DWithin(
            ST_SetSRID(ST_MakePoint(ss.lon, ss.lat), 4326)::geography,
            ST_SetSRID(ST_MakePoint(z.circle_center_longitude, z.circle_center_latitude), 4326)::geography,
            z.radius
        )
    WHERE z.zone_id IS NULL  -- pas à l'intérieur d'une zone connue
      AND EXTRACT(EPOCH FROM (ss.stop_end_time - ss.stop_start_time)) > 120  -- minimum 2 minutes
),
with_metadata AS (
    SELECT
        us.*,
        o.object_label,
        v.vehicle_label,
        v.registration_number
    FROM unauthorized_stops us
    JOIN raw_business_data.objects o ON o.device_id = us.device_id
    LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
)
SELECT
    vehicle_label,
    registration_number,
    object_label,
    stop_start_time,
    stop_end_time,
    ROUND(stop_duration_min, 1) AS stop_duration_minutes,
    ROUND(lat, 6) AS latitude,
    ROUND(lon, 6) AS longitude
FROM with_metadata
ORDER BY stop_start_time DESC;

Détection d'utilisation hors heures

Ce cas identifie les instances où les véhicules sont exploités en dehors des heures normales de bureau — définies ici comme du lundi au vendredi, 09:00–18:00. De telles détections sont essentielles pour signaler l'utilisation non autorisée, identifier un potentiel mauvais usage du véhicule, et améliorer la sécurité des actifs.

La logique est basée sur la table tracking_data_core de raw_telematics_data, qui enregistre des événements GPS horodatés par appareil. Nous dérivons le jour de la semaine et local heure d'utilisation à partir de chaque entrée device_time et filtrons les enregistrements en dehors de la fenêtre d'activité définie

(c.-à-d. avant 9h, après 18h ou à tout moment le week-end). Pour plus de clarté, nous enrichissons les données GPS avec les métadonnées d'objet et de véhicule de raw_business_data (par ex., libellé du véhicule, immatriculation, ID d'objet). Pour des résumés plus pertinents, nous agrégons optionnellement l'utilisation pour compter combien d'événements hors heures

se sont produits par véhicule et quand ils ont eu lieu. Cela peut aider à identifier des schémas ou des récidivistes.
    SELECT
        td.device_id,
        td.device_time,
        WITH gps_events AS (
        EXTRACT(DOW FROM td.device_time) AS day_of_week, -- 0=Dimanche, 6=Samedi
        EXTRACT(HOUR FROM td.device_time) AS hour_of_day,
        td.latitude / 1e7 AS latitude,
    FROM raw_telematics_data.tracking_data_core td
    td.longitude / 1e7 AS longitude
),
WHERE td.device_time >= now() - interval '7 days'
    SELECT
        off_hour_events AS (
    ge.*
    FROM gps_events ge 
        WHERE
        day_of_week IN (0, 6)  -- Samedi ou Dimanche 
        OR hour_of_day < 9
),
with_metadata AS (
    SELECT
        o.object_label,
        v.vehicle_label,
        v.registration_number,
        e.first_name || ' ' || e.last_name AS driver_name,
        OR hour_of_day >= 18
        o.object_id,
        o.device_id,
        o.create_datetime,
        ge.device_time,
        ge.latitude,
        ge.longitude,
        ge.day_of_week,
    ge.hour_of_day
    FROM off_hour_events ge
    LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
    JOIN raw_business_data.objects o ON ge.device_id = o.device_id 
        LEFT JOIN raw_business_data.driver_history dh ON dh.object_id = o.object_id
    AND dh.changed_datetime <= ge.device_time
)
SELECT
    object_label,
    vehicle_label,
    registration_number,
    LEFT JOIN raw_business_data.employees e ON e.employee_id = dh.new_employee_id
    device_time,
    driver_name,
    TO_CHAR(device_time, 'Day') AS weekday,
    TO_CHAR(device_time, 'HH24:MI') AS time_of_event,
    ROUND(latitude::numeric, 6) AS lat,
FROM with_metadata
ORDER BY device_time DESC;

ROUND(longitude::numeric, 6) AS lon

Nombre de trajets par jour Ce cas mesure combien de trajets chaque véhicule effectue quotidiennement et quelle distance ils parcourent, aidant les équipes logistiques à évaluerl'utilisation des véhicules

, optimiser les itinéraires et détecter des anomalies comme des trajets incomplets ou des utilisations non signalées. Pour définir untrajet , nous utilisons un changement d'état de mouvement du véhicule — c.-à-d. transition de l'arrêt au mouvement puis retour à l'arrêt. En utilisant les valeurs de vitesse de la table tracking_data_core, la requête segmente les données en fonction de ces transitions. Un trajet est identifié comme une période de mouvement continue

où la vitesse reste au-dessus d'un seuil (par ex., >5 km/h).

• Chaque trajet inclut : A horodatage et localisation de départ (premier point en mouvement)

• Un horodatage et localisation de fin (dernier point en mouvement avant l'arrêt)

• Le distance de Haversine entre les positions de départ et d'arrivée

Nous calculons le nombre de trajets et la distance totale par jour et par véhicule, éventuellement enrichis avec les étiquettes de véhicule depuis la table vehicles.

WITH base_points AS (
    SELECT
        td.device_id,
        td.device_time,
        td.latitude / 1e7 AS lat,
        td.longitude / 1e7 AS lon,
        td.speed / 100.0 AS speed_kph,
        LEAD(td.speed / 100.0) OVER (PARTITION BY td.device_id ORDER BY td.device_time) AS next_speed,
        LAG(td.speed / 100.0) OVER (PARTITION BY td.device_id ORDER BY td.device_time) AS prev_speed
    FROM raw_telematics_data.tracking_data_core td
    td.longitude / 1e7 AS longitude
),
trip_segments AS (
    SELECT
        *,
        CASE 
            WHEN speed_kph >= 5 AND (prev_speed < 5 OR prev_speed IS NULL) THEN 'start'
            WHEN speed_kph < 5 AND prev_speed >= 5 THEN 'end'
        END AS trip_marker
    FROM base_points
),
trip_points AS (
    SELECT
        device_id,
        device_time AS trip_start_time,
        lat AS start_lat,
        lon AS start_lon,
        LEAD(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS trip_end_time,
        LEAD(lat) OVER (PARTITION BY device_id ORDER BY device_time) AS end_lat,
        LEAD(lon) OVER (PARTITION BY device_id ORDER BY device_time) AS end_lon
    FROM trip_segments
    WHERE trip_marker = 'start'
),
trip_metrics AS (
    SELECT
        tp.device_id,
        tp.trip_start_time,
        tp.trip_end_time,
        tp.start_lat,
        tp.start_lon,
        tp.end_lat,
        tp.end_lon,
        tp.trip_start_time::date AS trip_day,
        -- Distance approximative utilisant la formule de Haversine (en km)
        111 * SQRT(POWER(tp.end_lat - tp.start_lat, 2) + POWER((tp.end_lon - tp.start_lon) * COS(RADIANS(tp.start_lat)), 2))::numeric AS distance_km
    FROM trip_points tp
    WHERE tp.trip_end_time IS NOT NULL
)
SELECT
    v.vehicle_label,
    v.registration_number,
    tm.device_id,
    tm.trip_day,
    COUNT(*) AS trip_count,
    ROUND(SUM(tm.distance_km), 2) AS total_distance_km
FROM trip_metrics tm
JOIN raw_business_data.objects o ON o.device_id = tm.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
GROUP BY v.vehicle_label, v.registration_number, tm.device_id, tm.trip_day
ORDER BY tm.trip_day DESC, v.vehicle_label;

Nombre de kilomètres par véhicule et par jour (7 derniers jours)

Ce cas calcule le kilométrage quotidien (en kilomètres) pour chaque véhicule au cours des 7 derniers jours. Il est fondamental pour le suivi de l'utilisation des véhicules, la surveillance de l'efficacité énergétique, la planification de la maintenance, et la détection de sous-utilisation ou de surutilisation.

Nous extrayons tous les enregistrements GPS depuis tracking_data_core pour les 7 derniers jours. Chaque point GPS possède un horodatage, une latitude et une longitude. Pour chaque véhicule et chaque jour, nous :

1. Trions les points GPS chronologiquement par appareil.

2. Calculons la distance entre points consécutifs en utilisant la formule de Haversine.

3. Sommons les distances par jour et par appareil pour obtenir le kilométrage total.

Cette approche fournit une grande précision sans dépendre de capteurs d'odomètre externes. Optionnellement, la requête effectue une jointure avec objects et vehicles pour enrichir les résultats avec les métadonnées des actifs.

WITH gps_points AS (
    SELECT
        td.device_id,
        td.device_time,
        td.device_time::date AS trip_day,
        td.latitude / 1e7 AS lat,
        td.longitude / 1e7 AS lon,
        LAG(td.latitude / 1e7) OVER (PARTITION BY td.device_id, td.device_time::date ORDER BY td.device_time) AS prev_lat,
        LAG(td.longitude / 1e7) OVER (PARTITION BY td.device_id, td.device_time::date ORDER BY td.device_time) AS prev_lon
    FROM raw_telematics_data.tracking_data_core td
    td.longitude / 1e7 AS longitude
),
distances AS (
    SELECT
        device_id,
        trip_day,
        -- Approximation de la formule de Haversine en km
        111 * SQRT(POWER(lat - prev_lat, 2) + POWER((lon - prev_lon) * COS(RADIANS(lat)), 2)) AS segment_distance_km
    FROM gps_points
    WHERE prev_lat IS NOT NULL AND prev_lon IS NOT NULL
      AND ABS(lat - prev_lat) < 1 AND ABS(lon - prev_lon) < 1 -- exclure les valeurs aberrantes
)
SELECT
    v.vehicle_label,
    v.registration_number,
    o.object_label,
    d.device_id,
    d.trip_day,
    ROUND(SUM(d.segment_distance_km)::numeric, 2) AS mileage_km
FROM distances d
JOIN raw_business_data.objects o ON o.device_id = d.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
GROUP BY v.vehicle_label, v.registration_number, o.object_label, d.device_id, d.trip_day
ORDER BY d.trip_day DESC, v.vehicle_label;

Rapport de journal d'événements du véhicule

Ce cas fournit un rapport complet de tous les événements liés aux véhicules (par ex. : mise en marche, porte ouverte, freinage brusque, etc.) sur l'ensemble de la flotte. Il inclut le type d'événement, l'horodatage, et le contexte du véhicule, permettant aux équipes opérationnelles d'auditer les comportements, de suivre les activités anormales ou de générer des alertes et des analyses.

La source principale est la table states du schéma raw_telematics_data. Chaque ligne inclut : device_id (source de l'événement), device_time (horodatage), state_name (étiquette de l'événement), et value (statut ou mesure).

Pour créer un rapport exploitable :

1. Nous extrayons tous les enregistrements des 7 derniers jours.

2. Les grouper par le type d'événement, véhicule, et date pour fournir un compte du nombre de fois que chaque événement est survenu et quand il est survenu.

3. Enrichir les résultats avec les métadonnées du véhicule (vehicle_label, registration_number, object_label) via objects et vehicles.

Cela fournit une chronologie quotidienne des événements sur l'ensemble de la flotte - essentielle pour le diagnostic, l'analyse comportementale et la maintenance proactive.

WITH raw_events AS (
    SELECT
        s.device_id,
        s.device_time,
        s.device_time::date AS event_day,
        s.state_name,
        s.value
    FROM raw_telematics_data.states s
    WHERE s.device_time >= now() - interval '7 days'
),
with_vehicle_info AS (
    SELECT
        re.device_id,
        re.event_day,
        re.device_time,
        re.state_name,
        re.value,
        v.vehicle_label,
        v.registration_number,
        o.object_label
    FROM raw_events re
    JOIN raw_business_data.objects o ON o.device_id = re.device_id
    LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
),
event_summary AS (
    SELECT
        event_day,
        state_name,
        vehicle_label,
        registration_number,
        object_label,
        COUNT(*) AS event_count,
        MIN(device_time) AS first_occurred,
        MAX(device_time) AS last_occurred
    FROM with_vehicle_info
    GROUP BY event_day, state_name, vehicle_label, registration_number, object_label
)
SELECT
    event_day,
    vehicle_label,
    registration_number,
    object_label,
    state_name AS event_type,
    event_count,
    first_occurred,
    last_occurred
FROM event_summary
ORDER BY event_day DESC, vehicle_label, state_name;