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, de l'inventaire 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 en temps réel sur les véhicules, les conducteurs, les itinéraires et les conditions de la 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'analytique des 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, les diagnostics des données des capteurs, la géorepérage et l'analytique 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 Nombre de kilomètres par véhicule par jour (7 derniers jours)
Surveillance de la cargaison	Assurer 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
Fonctionnement du véhicule	Suivre l'utilisation de la flotte, assurer la maintenance et réduire les temps d'arrêt	Résumé des heures moteur par véhicule / conducteur / jour (7 derniers jours) Analyse des temps d'arrêt des véhicules Suivi des actifs sans mouvement
Sécurité & sûreté de l'itinéraire	Détecter les abus, 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 en dehors des 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 en dehors des heures
Analyse post-livraison	Évaluer l'efficacité opérationnelle et les performances historiques	Rapport du journal d'événements du véhicule Nombre de kilomètres par véhicule 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é leur GPS comparer les coordonnées minimales et maximales pendant la période. Si les deux valeurs se situent dans une plage très étroite (un seuil de tolérance, par ex. ±0,01 degré), nous signalons l'actif comme immobile. La requête fait é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'arrêt 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 métriques de temps d'arrêt sont cruciales pour les opérations logistiques afin de surveiller la santé de la flotte, réduire les temps d'inactivité et améliorer l'utilisation globale et l'efficacité de la planification.

Le cœur de l'analyse des temps d'arrêt consiste à exploiter 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 de temps d'arrêt. En filtrant sur 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'arrêt total par véhicule en additionnant 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 fait 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 situations où des véhicules dévient de leurs itinéraires assignés ou attendus — en particulier des zones géorepérées ou des couloirs de livraison. Le suivi de ces déviations aide à assurer 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 issues de tracking_data_core (dans le schéma raw_telematics_data) avec des zones géographiques prédéfinies provenant 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 se trouve à 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 tous les itinéraires géorepérés et les signalons comme déviations. La sortie finale répertorie ces déviations, y compris l'appareil, l'horodatage, le libellé du véhicule et la distance du point par rapport au 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 étaient actifs pour chaque véhicule sur une base journalière, 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 des conducteurs. Lorsqu'il est lié aux conducteurs, il prend également en charge la validation des heures de travail et l'analyse des performances.

La table states dans raw_telematics_data enregistre des indicateurs d'état moteur en séries temporelles, généralement avec un state_name tel que '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 où le moteur était allumé (1).

Pour lier l'activité du 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 actuel 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 additionnant 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 from ON to 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 les relevés de capteurs — tels que la température ou l'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., alimentation, produits pharmaceutiques) pour assurer la conformité aux exigences de la chaîne du froid et prévenir 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 provenant 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é). Étant donné que value est stocké en texte, nous le convertissons en numérique avant d'appliquer les conditions de seuil. Pour enrichir les résultats, nous faisons une jointure avec la table objects afin de récupérer les libellés du véhicule ou de l'actif, 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
   FROM raw_telematics_data.inputs i
   WHERE i.device_time >= now() - interval '1 hour'
),
calibration_data AS (
   SELECT
       sensor_id,
       value AS cal_value,
       volume AS cal_volume
   FROM raw_business_data.sensor_calibration_data
),
sensor_meta AS (
   SELECT
       sd.device_id,
       sd.input_label,
       sd.sensor_id
   FROM raw_business_data.sensor_description sd
),
joined_data AS (
   SELECT
       rsd.*,
       sm.sensor_id
   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,
       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 * FROM calibration_data
       WHERE sensor_id = jd.sensor_id AND cal_value <= jd.value
       ORDER BY cal_value DESC LIMIT 1
   ) cd_low ON TRUE
   LEFT JOIN LATERAL (
       SELECT * FROM calibration_data
       WHERE sensor_id = jd.sensor_id AND cal_value >= jd.value
       ORDER BY cal_value ASC LIMIT 1
   ) cd_high ON TRUE
),
violations AS (
   SELECT
       cd.device_id,
       cd.device_time,
       cd.sensor_name,
       cd.calibrated_value,
       CASE
           WHEN sd.sensor_type = 'temperature' AND cd.calibrated_value > 25 THEN 'High Temperature'
           WHEN sd.sensor_type = 'temperature' AND cd.calibrated_value < 0 THEN 'Low Temperature'
           WHEN sd.sensor_type = 'humidity' AND cd.calibrated_value > 80 THEN 'High Humidity'
           ELSE NULL
       END AS violation_type
   FROM calibrated_data cd
   left join raw_business_data.sensor_description sd on sd.device_id = cd.device_id and sd.input_label = cd.sensor_name
   WHERE 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 des véhicules au cours des dernières 24 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 les performances des SLA.

La requête analyse les points de localisation avec une vitesse faible ou nulle en utilisant la La requête utilise la table tracking_data_core de raw_telematics_data pour extraire les 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 basse vitesse pour déterminer les horodatages de début et de fin d'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 en dehors des heures

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

La logique repose sur la table tracking_data_core de raw_telematics_data, qui enregistre des événements GPS horodatés par appareil. Nous déduisons le jour de la semaine local et l'heure d'utilisation à partir de chaque entrée device_time et filtrons les enregistrements en dehors de la fenêtre commerciale définie (c.-à-d. avant 9 h, après 18 h, ou à tout moment le week-end).

Pour plus de clarté, nous enrichissons les données GPS avec les métadonnées des objets et des véhicules provenant de raw_business_data (par ex., libellé du véhicule, immatriculation, ID d'objet). Pour des résumés plus pertinents, nous agrégeons éventuellement l'utilisation pour compter combien d'événements en dehors des heures se sont produits par véhicule et quand ils ont eu lieu. Cela peut aider à identifier des motifs ou des récidivistes.

WITH gps_events AS (
    SELECT
        td.device_id,
        td.device_time,
        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,
        td.longitude / 1e7 AS longitude
    FROM raw_telematics_data.tracking_data_core td
    WHERE td.device_time >= now() - interval '7 days'
),
off_hour_events AS (
    SELECT
        ge.*
    FROM gps_events ge
    WHERE 
        day_of_week IN (0, 6)  -- Samedi ou Dimanche
        OR hour_of_day < 9 
        OR hour_of_day >= 18
),
with_metadata AS (
    SELECT
        o.object_label,
        v.vehicle_label,
        v.registration_number,
        e.first_name || ' ' || e.last_name AS driver_name,
        o.device_id,
        o.object_id,
        o.create_datetime,
        ge.device_time,
        ge.latitude,
        ge.longitude,
        ge.day_of_week,
        ge.hour_of_day
    FROM off_hour_events ge
    JOIN raw_business_data.objects o ON ge.device_id = o.device_id
    LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
    LEFT JOIN raw_business_data.driver_history dh ON dh.object_id = o.object_id 
        AND dh.changed_datetime <= ge.device_time
    LEFT JOIN raw_business_data.employees e ON e.employee_id = dh.new_employee_id
)
SELECT
    object_label,
    vehicle_label,
    registration_number,
    driver_name,
    device_time,
    TO_CHAR(device_time, 'Day') AS weekday,
    TO_CHAR(device_time, 'HH24:MI') AS time_of_event,
    ROUND(latitude::numeric, 6) AS lat,
    ROUND(longitude::numeric, 6) AS lon
FROM with_metadata
ORDER BY device_time DESC;

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 à évaluer l'utilisation du véhicule, optimiser les itinéraires et détecter des anomalies comme des trajets incomplets ou des usages non déclarés.

Pour définir un trajet, 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 :

• Un horodatage et emplacement de départ (premier point en mouvement)

• Un horodatage et emplacement 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 provenant de 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
    WHERE td.device_time >= now() - interval '7 days'
),
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)) 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étrage par véhicule et par jour (7 derniers jours)

Ce cas calcule la mileage quotidienne (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 la sous-utilisation ou de la surutilisation.

Nous extrayons tous les enregistrements GPS de 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. Trier les points GPS chronologiquement par appareil.

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

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

Cette approche offre une grande précision sans dépendre de capteurs d'odomètre externes. Optionnellement, la requête joint les tables objects et vehicles pour enrichir les résultats avec les métadonnées de l'actif.

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
    WHERE td.device_time >= now() - interval '7 days'
),
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 du journal des événements des véhicules

Ce cas fournit un rapport complet de tous les événements liés aux véhicules (par ex., allumage, porte ouverte, freinage brusque, etc.) à travers la flotte. Il inclut le type d'événement, l'horodatage, et le contexte du véhicule, permettant aux équipes opérationnelles d'auditer le comportement, 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 regrouper par le type d'événement, véhicule, et date pour fournir un compte du nombre de fois où chaque événement est survenu et quand il s'est produit.

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

Ceci fournit une chronologie d'événements quotidienne à travers 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;