# Logistique {% hint style="warning" %} Activer **Requête IoT** avant d’utiliser les données pour élaborer des analyses complètes. Si vous ne l’avez pas encore, contactez-nous pour obtenir les détails d’activation - {% endhint %} La logistique est un écosystème complexe qui implique 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 l’état du chargement, ce qui améliore considérablement la prise de décision et l’efficacité opérationnelle. Navixy **Requête IoT**, grâce à ses solides capacités d’ingestion de données et d’analyse de séries temporelles, soutient la transformation numérique des opérations logistiques en offrant une visibilité approfondie sur chaque étape du cycle de vie. Ses solides capacités d’ingestion télématique offrent une visibilité complète sur ces opérations. Les données GPS en temps réel, les diagnostics 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 / recettes couverts | | -------------------------------------- | ---------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **Gestion des itinéraires** | Optimiser les itinéraires des véhicules, assurer une répartition efficace et réduire les retards | Nombre de trajets par jour Nombre de kilomètres par véhicule et par jour (7 derniers jours) | | **Surveillance du chargement** | 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 du parc, 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’immobilisation des véhicules Suivi des actifs sans déplacement | | **Sécurité et sûreté des itinéraires** | Détecter les abus, les activités non autorisées et les infractions de sécurité | Détection des écarts d’itinéraire - Arrêts non autorisés (24 dernières heures) Détection de l’utilisation en dehors des heures autorisées | | **Gestion de la conformité** | Surveiller le comportement des conducteurs, faire respecter les politiques et la conformité opérationnelle | Résumé des heures moteur par véhicule / conducteur / jour (7 derniers jours) Détection de l’utilisation en dehors des heures autorisées | | **Analyse post-livraison** | Évaluer l’efficacité opérationnelle et les performances historiques | Rapport du journal des événements du véhicule Nombre de kilomètres par véhicule et par jour (7 derniers jours) Nombre de trajets par jour Suivi des actifs sans déplacement | ## **Suivi des actifs sans déplacement** Ce cas identifie les actifs (par exemple, des véhicules ou des remorques) dont le GPS n’a pas changé ; comparez **les coordonnées minimales et maximales** pendant la période. Si les deux valeurs se situent dans une plage très étroite (seuil de tolérance, par exemple ±0,01 degré), nous signalons l’actif comme immobile. La requête joint également les tables objects et vehicles dans raw\_business\_data afin de récupérer des libellés d’actifs pertinents pour le résultat. {% code expandable="true" %} ```sql 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 -- exclude devices with very sparse data AND((max_lat - min_lat) <= 2000 -- ~10 meters OR (max_lon - min_lon) <= 1000) -- ~10 meters ) 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; ``` {% endcode %} ## **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 hors service en raison de la maintenance, des pannes ou de l’inactivité. Les métriques d’immobilisation sont essentielles pour les opérations logistiques afin de surveiller l’état du parc, réduire le temps d’inactivité et améliorer l’utilisation globale ainsi que 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 consigne à 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 les **tâches de service terminées**, nous pouvons calculer la durée exacte pendant laquelle chaque véhicule a été hors service. La requête calcule l’immobilisation totale par véhicule en additionnant les durées de toutes ses tâches de service (en heures). Elle permet également de distinguer la maintenance planifiée de la maintenance non planifiée à l’aide du indicateur is\_unplanned. Pour rendre les résultats plus exploitables, elle joint la table vehicles afin d’inclure les libellés des véhicules, les numéros d’immatriculation et les informations sur le modèle. {% code expandable="true" %} ```sql 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; ``` {% endcode %} ## **Détection des écarts d’itinéraire** Ce cas identifie les situations où les véhicules s’écartent de leurs itinéraires assignés ou attendus — en particulier des zones de géorepérage ou des couloirs de livraison. Le suivi de ces écarts aide à garantir le respect 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 de la table zones dans raw\_business\_data. Ces zones représentent des itinéraires ou des segments d’itinéraires assignés. À l’aide de 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 à l’aide d’un **CROSS JOIN**, puis applique ST\_DWithin() pour vérifier si le véhicule se trouvait dans le couloir 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 des écarts. Le résultat final répertorie ces écarts, y compris l’appareil, l’horodatage, le libellé du véhicule et la distance entre le point et le centre de la zone la plus proche. {% code expandable="true" %} ```sql 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; ``` {% endcode %} ## **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 au quotidien, permettant aux gestionnaires de parc de suivre **l’utilisation**, d’identifier **la surutilisation ou la sous-utilisation**, et de corréler l’activité avec les affectations des conducteurs. Lorsqu’elle est liée aux conducteurs, elle 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 du moteur en série temporelle**, généralement avec un state\_name tel que 'ignition' et une valeur de 1 (marche) ou 0 (arrêt). Pour calculer les heures moteur, nous recherchons toutes les transitions horodatées pour chaque appareil et calculons les durées pendant lesquelles le moteur était allumé (1). Pour relier 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 actuel sur cet objet (via l’historique d’affectation des conducteurs) ainsi qu’au véhicule correspondant. Nous regroupons ensuite les données par jour, véhicule et conducteur, en additionnant le temps total d’activité du moteur (en heures). {% code expandable="true" %} ```sql 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; ``` {% endcode %} ## **É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 les seuils critiques pendant le transport. La surveillance de ces violations est essentielle pour les secteurs transportant des marchandises périssables (par exemple, l’alimentation, les produits pharmaceutiques) afin de garantir le respect des exigences de la chaîne du froid et d’éviter 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 mesure de capteur (par exemple, 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 de filtrage principale repose sur **les motifs des noms de capteurs** et sur la comparaison de leurs **valeurs numériques aux seuils** (par exemple, >25 °C pour la température, >80 % pour l’humidité). Comme value est stocké en texte, nous le convertissons en numérique avant d’appliquer les conditions de seuil. Pour enrichir les résultats, nous joignons la table objects afin de récupérer les libellés du véhicule ou de l’actif, ce qui améliore l’interprétation pour les gestionnaires de parc. {% code expandable="true" %} ```sql 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; ``` {% endcode %} ## **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 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é susceptibles d’affecter l’efficacité énergétique et les performances SLA. La requête analyse **les points de localisation à vitesse faible ou nulle** en utilisant La requête utilise la table tracking\_data\_core de raw\_telematics\_data pour extraire les données de localisation en série temporelle 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**. À l’aide des fonctions LAG et LEAD, la requête segmente ces périodes à faible vitesse afin de 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 positions qui se trouvent dans des **zones de géorepérage** connues (table zones) à l’aide de ST\_DWithin de PostGIS. Seuls les arrêts **en dehors de tout tampon de zone** sont signalés. Le résultat inclut l’identifiant du véhicule, le libellé de l’objet, l’immatriculation, les horodatages, la durée et les coordonnées de chaque arrêt. {% code expandable="true" %} ```sql 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 -- not inside any known zone 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; ``` {% endcode %} ## **Détection de l’utilisation en dehors des heures autorisées** Ce cas identifie les situations où les véhicules sont utilisés **en dehors des heures normales de bureau** — définies ici comme **du lundi au vendredi, de 09:00 à 18:00**. Ces détections sont essentielles pour signaler **l’utilisation non autorisée**, identifier d’éventuelles **utilisations abusives du véhicule**, et améliorer **la sécurité des actifs**. 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érivons le **jour de la semaine** et **heure d’utilisation** locales à partir de chaque entrée device\_time et filtrons les enregistrements **en dehors de la plage de travail 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 issues de raw\_business\_data (par exemple, libellé du véhicule, immatriculation, ID de l’objet). Pour des résumés plus significatifs, nous pouvons également agréger l’utilisation afin de compter **combien d’événements hors heures** se sont produits par véhicule et à quel moment. Cela peut aider à identifier des schémas ou des récidivistes. {% code expandable="true" %} ```sql WITH gps_events AS ( SELECT td.device_id, td.device_time, EXTRACT(DOW FROM td.device_time) AS day_of_week, -- 0=Sunday, 6=Saturday 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) -- Saturday or Sunday 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; ``` {% endcode %} ## **Nombre de trajets par jour** Ce cas mesure **combien de trajets** chaque véhicule effectue quotidiennement et la distance parcourue, aidant ainsi les équipes logistiques à évaluer **l’utilisation des véhicules**, à optimiser les itinéraires et à détecter des anomalies comme des trajets incomplets ou une utilisation non signalée. Pour définir un **trajet**, nous utilisons un changement d’ **état de mouvement du véhicule** — c’est-à-dire la transition de l’arrêt au déplacement puis au retour à l’arrêt. À l’aide des 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 continu** où la vitesse reste supérieure à un seuil (par exemple, >5 km/h). Chaque trajet comprend : * Un **horodatage et localisation de début** (premier point en mouvement) * Un **horodatage et localisation de fin** (dernier point en mouvement avant l’arrêt) * La **distance de Haversine** entre les localisations 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 par les libellés des véhicules issus de la table vehicles. {% code expandable="true" %} ```sql 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))::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; ``` {% endcode %} ## **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 sur les 7 derniers jours. Il est essentiel pour suivre l' **utilisation du véhicule**, surveiller l' **efficacité énergétique**, planifier la **maintenance** et détecter une sous-utilisation ou une surutilisation. Nous extrayons tous les enregistrements GPS de tracking\_data\_core des 7 derniers jours. Chaque point GPS comporte 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 des points consécutifs** à l'aide de la formule de Haversine. 3. **Additionnons les distances par jour et par appareil** pour obtenir le kilométrage total. Cette approche offre une grande précision sans recourir à des capteurs de compteur kilométrique externes. En option, la requête joint objects et vehicles pour enrichir les résultats avec des métadonnées d'actif. {% code expandable="true" %} ```sql 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; ``` {% endcode %} ## **Rapport du journal des événements du véhicule** Ce cas fournit un rapport complet de tous les **événements liés au véhicule** (par exemple, contact, porte ouverte, freinage brusque, etc.) dans l'ensemble de la flotte. Il inclut le **type d'événement**, **horodatage**, et le **contexte du véhicule**, ce qui permet aux équipes opérationnelles d'auditer le comportement, de suivre les activités anormales ou de piloter les alertes et les analyses. La source principale est la table states du schéma raw\_telematics\_data. Chaque ligne comprend : device\_id (source de l'événement), device\_time (horodatage), state\_name (libellé de l'événement) et value (état ou mesure). Pour créer un rapport exploitable : 1. Nous extrayons tous les enregistrements des 7 derniers jours. 2. Regroupez-les par **type d'événement**, **véhicule**, et le **date** afin de fournir un **compte de la fréquence de chaque événement** et **et du moment où il s'est produit**. 3. Enrichissez 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** à l'échelle de la flotte - essentielle pour le diagnostic, l'analyse comportementale et la maintenance proactive. {% code expandable="true" %} ```sql 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; ``` {% endcode %} --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://navixy.com/docs/analytics/fr/example-queries/logistics.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.