Suivons le Père Noël avec PostGIS – v2

Depuis quelques années, je développe un système de suivi du Père Noël. Comme j’ai « quelques notions » de SQL et de développement web SIG, j’ai tous les outils nécessaires à une telle application sous la main.

J’avais déjà conçu une première version détaillée dans cet article dédié, nous voila reparti pour un nouveau Noël avec un suivi amélioré.

---

Pour le suivi, j’ai un peu étudier la chose et il s’avère qu’un traineau magique, ça n’apparait pas sur les radars. Il est donc compliqué de suivre le bonhomme.

Malgré mes nombreux courriers, le Père Noël refuse également de m’envoyer son itinéraire détaillé, un petit GPX aurait pourtant bien fait mon affaire mais il semble qu’il n’utilise pas de GPS…

Il faut donc un peu ruser pour suivre Santa à la trace et voici comment j’ai procédé.

Le principe est de partir d’une liste de destinations que le Père Noël va visiter. On calcule ensuite le trajet pour rejoindre toutes ces destinations en tenant compte du fait que le passage doit s’effectuer durant la nuit du 24 au 25 décembre, et entre minuit et 1h du matin en tenant compte de chaque fuseau horaire. Plus facile à dire qu’a faire mais essayons.

L’application repose sur PostgreSQL 15 + PostGIS 3.5.

---

Notez que cet article s’intéresse à la partie donnée (PostgreSQL + PostGIS) et non à la partie conception de l’application web SIG.

Destinations

A priori, le Père Noël visite toutes les maisons du monde. Ceci dit, « toutes les maisons du monde » c’est complexe à cartographier : un igloo doit il être considéré comme une maison même s’il n’a pas de cheminée ?

Alors simplifions un peu la chose et basons nous sur l’ensemble des villes du monde. Bon là aussi c’est complexe car il y a vraiment beaucoup de villes et ça risque d’être difficile de suivre tous les déplacements.

Simplifions encore et prenons environ 1000 destinations parmi les plus connues dans le monde (avec peut être une sur représentation des villes de l’hexagone ;-).

Pour récupérer ces données, une petite recherche sur internet et nous voila avec un magnifique jeu de données du monde entier dont la population est supérieure à 1000 habitants.

Quelques traitements plus tard (rien de bien sorcier, juste des formules magiques pour récupérer les villes qui m’intéressent) nous voici avec la table suivante :

CREATE TABLE IF NOT EXISTS santa.destination (
	-- Un identifiant numérique
	id_destination int8 PRIMARY KEY,
	-- Le pays de la destination
	pays text,
	-- La région de la destination
	region text,
	-- Le nom de la destination
	ville text,
	-- Le nombre d'habitant dans cette destination
	population int8,
	-- L'interval de temps entre le fuseau horaire de la destination et GMT
	utc_offset interval,
	-- Nom de la timezone
	timezone text,
	-- L'emplacement de la destination
	geom public.geometry(point, 4326)
);

Problème, cette table ne possède pas l’emplacement du Père Noël… Nous allons rusé pour cela.

Quelques variables

Pour les différents calculs que nous allons mener, je vais avoir besoin de définir certains paramètres.

Parmi ces variables les éléments suivants :

• La population totale
• Le nombre de cadeaux par personne
• Le pourcentage de personnes recevant un cadeau (et oui, tout le monde n’est pas sage…)
• L’emplacement du Père Noël
• La date et l’heure du début de la tournée : le 25 décembre à minuit dans le premier fuseau horaire (GMT +13)
• L’instant présent : transaction_timestamp(), cela permet d’effectuer des test en faisant varier cette valeur.

Pour stocker des paramètres, j’utilise souvent une vue effectuant un simple SELECT que j’utilise ensuite dans mes différentes requêtes via un CROSS JOIN.

CREATE OR REPLACE VIEW santa.v_variable as (
	SELECT 
		-- Population
		-- Le nombre total d'être humain sur Terre
		8142000000 as pop_total,
		-- Le pourcentage de personne recevant des cadeaux (oui, certains ont été méchants cette année)
		0.95 as cadeau_par_pop,
		-- Le nombre de cadeau par personnes
		1 as cadeau_par_pers,
		
		-- Autres données
		-- Le pourcentage de temps total passé à voler de maison en maison (ici 40% de temps de vol donc 60% du temps passé dans les maisons)
		40::numeric as pourcent_temps_de_vol,
		-- Les informations sur l'emplacement de la maison du Père Noël
		jsonb_build_object(
			'id', 0,
			'pays', 'Svalbard',
			'region', 'Longyearbyen',
			'ville', 'Maison de la famille Noël',
			'population', 2,
			'timezone', 'Europe/Paris',
			'utc_offset', '01:00:00',
			'geom_4326', 'SRID=4326;POINT(24 80)'
		) as maison_pere_noel,

		-- Paroles du Père Noël
		$${
			"Préparatifs":[
				"Vive le vent d'hiver !",
				"Et un cadeau de plus !",
				"Hum ?!",
				"Quel beau camion !",
				"Ah, une belle poupée !",
				"Oh Oh Oh !",
				"Vive le vent d'hiver !",
				"Et un cadeau de plus !",
				"Hum ?!",
				"Quel beau camion !",
				"Ah, une belle poupée !",
				"Oh Oh Oh !"
			],
			"Démarrage tournée":[
				"Oh Oh Oh !",
				"Joyeux Noël !",
				"Mon beau sapin !",
				"Hue tornade !",
				"Allez comète !",
				"Il fait frais cette nuit !",
				"Oh Oh Oh !",
				"Joyeux Noël !",
				"Mon beau sapin !",
				"Hue tornade !",
				"Allez comète !",
				"Il fait frais cette nuit !"
			],
			"Trajet":[
				"Oh Oh Oh !",
				"Joyeux Noël !",
				"Mon beau sapin !",
				"Hue tornade !",
				"Allez comète !",
				"Il fait frais cette nuit !",
				"Oh Oh Oh !",
				"Joyeux Noël !",
				"Mon beau sapin !",
				"Hue tornade !",
				"Allez comète !",
				"Il fait frais cette nuit !"
			],
			"Distribution":[
				"Oh hisse !",
				"Serrée cette cheminée !",
				"Et un cadeau de plus !",
				"Chut, pas un bruit !",
				"Hum un petit biscuit...",
				"Oh Oh Oh !",
				"Oh hisse !",
				"Serrée cette cheminée !",
				"Et un cadeau de plus !",
				"Chut, pas un bruit !",
				"Hum un petit biscuit...",
				"Oh Oh Oh !"
			],
			"Fin tournée":[
				"Oh Oh Oh !",
				"Joyeux Noël !",
				"Mon beau sapin !",
				"Hue tornade !",
				"Allez comète !",
				"Il fait frais cette nuit !",
				"Oh Oh Oh !",
				"Joyeux Noël !",
				"Mon beau sapin !",
				"Hue tornade !",
				"Allez comète !",
				"Il fait frais cette nuit !"
			],
			"Repos":[
				"ZZZ",
				"ZZZ ZZZ",
				"ZZZ ZZZ ZZZ",
				"ZZZ ZZZ",
				"ZZZ",
				"RONFFFFFFFLE",
				"ZZZ",
				"ZZZ ZZZ",
				"ZZZ ZZZ ZZZ",
				"ZZZ ZZZ",
				"ZZZ",
				"RONFFFFFFFLE"
			]
		}$$::jsonb as parole_pere_noel,
		
		-- Dates et horaire
		-- Les dates contiennent toujours les heures
		-- L'heure exacte du début de la distribution (le 25 décembre à minuit sur le premier fuseau horaire)
		-- Le Père Noël met 1h pour distribuer les cadeau par fuseau horaire sauf si un fuseau intermédiaire existe
		(extract(year from now()) || '-12-25 00:00:00 +13')::timestamp with time zone as debut_distrib,

		-- Durée du trajet pour rejoindre la première destination depuis la maison du Père Noël
		-- Durée également pour rejoindre la maison du Père Noël depuis la dernière maison
		'2 minutes'::interval as delai_dest_santa_house,

		-- Heure du début de la transaction
		transaction_timestamp() as "now"	-- Maintenant
)
;

Trajet

Nous devons maintenant calculer le trajet.

Ce trajet doit répondre aux critères suivants :

• Passer par toutes les destinations
• Démarrer et finir par la maison du Père Noël
• Chaque destination doit être entre minuit et 1h du matin pour le fuseau horaire considéré

Nous allons donc devoir découper la Terre en zones de passage dans lesquelles le Père Noël aura un timing bien spécifique à respecter. Le principe est de déterminer l’intervalle de temps pendant lequel le Père Noël effectue sa tournée puis de diviser cet intervalle par le nombre de zones de passage.

Pour calculer le trajet et pouvoir relancer le calcul facilement, je vais me baser sur plusieurs vues.

Calcul des zones horaires

Le principe est d’attribuer à chaque destination un numéro de rang et une durée de passage en fonction de son fuseau horaire.

Pour cela on se base sur la valeur de la colonne utc_offset qui contient le décalage horaire avec GMT ainsi que plusieurs fonction de fenêtrage.

CREATE OR REPLACE VIEW santa.v_destination_zone_horaire as (
	-- Clause CTE pour récupérer une liste d'offset UTC accompagné de l'offset suivant
	WITH utc_offset_list AS (
		SELECT
			dest.utc_offset,
			-- Récupération de l'offset suivant
			lead(utc_offset) OVER (ORDER BY utc_offset) AS next_utc_offset
		FROM 
			santa.destination as dest
		GROUP BY 
			dest.utc_offset
	)
	SELECT 
		dense_rank() OVER zone_globale as rang_zone_horaire,
		dest.pays,
		dest.region,
		dest.ville,
		dest.timezone,
		dest.utc_offset,
		-- Durée de passage dans la zone par soustraction de la valeur de l'offset suivant
		CASE 
			WHEN uol.next_utc_offset IS NULL THEN '01:00:00'::INTERVAL
			ELSE uol.next_utc_offset - dest.utc_offset
		END duree_zone,
		-- Valeur par défaut pour la population
		CASE 
			WHEN dest.population IS NULL OR dest.population < 10 THEN 10
			ELSE dest.population
		END as population,
		-- Nombre de destination dans la zone horaire
		count(id_destination) OVER zone_horaire as nb_dest_zone_horaire,
		-- Coordnnées Y minimum et maximum de chaque pays (pour l'ordonnancement)
		first_value(ST_Y(dest.geom)) OVER pays as miny_pays,
		last_value(ST_Y(dest.geom)) OVER pays as maxy_pays,
		geom
	FROM 
		santa.destination as dest
	CROSS JOIN 
		santa.v_variable as var
	LEFT JOIN 
		utc_offset_list AS uol
		ON uol.utc_offset = dest.utc_offset 
	WINDOW 
		zone_globale AS (
			ORDER BY 
				dest.utc_offset DESC
			ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
		),
		zone_horaire AS (
			PARTITION BY 
				dest.utc_offset
			ORDER BY 
				dest.utc_offset DESC
			ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
		),
		pays AS (
			PARTITION BY 
				dest.utc_offset,
				dest.pays
			ORDER BY 
				ST_Y(dest.geom)
			ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
		)
)
;

Ordonnancement par latitude

Au sein de chaque zone horaire, les destinations sont ordonnancées afin d’avoir un trajet cohérent allant du nord au sud dans une zone puis du sud au nord dans la zone suivante dans un mouvement à l’effet « yoyo ». Les destinations sont également regroupées par pays : on commence par le pays le plus Nord puis celui juste après…

La génération d’un identifiant spécifique permettra d’ordonnancer les destination.

CREATE OR REPLACE VIEW santa.v_destination_ordonnees as (
	SELECT 
		-- Récupération du rang de chaque destination au sein de sa zone de longitude
		-- Le rang est déterminé par la latitude de l'objet
		-- Pour faire un effet "yoyo", on inverse l'ordonnancement des latitudes une zone horaire sur deux
		concat_ws(
			'-',
			lpad((vdzh.rang_zone_horaire)::text, 2, '0'),
			lpad((row_number() OVER zone_horaire)::text, 3, '0')
		) as id,
		vdzh.pays,
		vdzh.region,
		vdzh.ville,
		vdzh.timezone,
		vdzh.utc_offset,
		vdzh.duree_zone,
		vdzh.population,
		-- Création d'une valeur de population fictive
		-- pour qu'une fois toutes destination visitées, le Père Noël ait vu toute la population mondiale
		round( 
			(
				(vdzh.population / sum(vdzh.population) OVER ()) 
				* var.pop_total::NUMERIC
			),
			0 
		) as pop_fictive,
		vdzh.nb_dest_zone_horaire,
		ST_Transform(vdzh.geom, 3857) as geom
	FROM
		santa.v_destination_zone_horaire as vdzh
	CROSS JOIN 
		santa.v_variable as var
	WINDOW
		zone_horaire AS (
			PARTITION BY 
				vdzh.utc_offset
			ORDER BY
				CASE 
					WHEN rang_zone_horaire % 2 = 0 THEN miny_pays
					ELSE -maxy_pays
				END,
				CASE 
					WHEN rang_zone_horaire % 2 = 0 THEN ST_Y(vdzh.geom)
					ELSE -ST_Y(vdzh.geom)
				END
			ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
		)
)
;

Consolidation des destinations

Pour chaque destination, on va calculer plusieurs valeurs, notamment les valeurs des destinations suivantes.

Cette vue est une sorte de version améliorée de la table destination

CREATE OR REPLACE VIEW santa.v_destination_globale as (
	SELECT 
		vdo.id,
		vdo.pays,
		vdo.region,
		vdo.ville,
		-- Le fenêtrage permet de récupérer la prochaine destination
		lead (vdo.pays) OVER all_etape as pays_next,
		lead (vdo.region) OVER all_etape as region_next,
		lead (vdo.ville) OVER all_etape as ville_next,
		vdo.timezone,
		vdo.utc_offset,
		lead (vdo.utc_offset) OVER all_etape as utc_offset_next,
		vdo.duree_zone,
		(vdo.duree_zone / vdo.nb_dest_zone_horaire) AS duree_destination,
		vdo.population,
		vdo.pop_fictive,
		vdo.nb_dest_zone_horaire,
		vdo.geom,
		-- Le fenêtrage permet de récupérer la géométrie de la prochaine destination
		lead(vdo.geom) OVER all_etape as geom_next
	FROM 
		santa.v_destination_ordonnees as vdo
	CROSS JOIN 
		santa.v_variable as var
	WINDOW 
		all_etape AS (
			ORDER BY 
				vdo.id
		)
)
;

Étapes de distribution

A partir des données des destination, on génère des étapes de distribution des cadeaux.

Pendant ces étapes, le Père Noël s’arrête dans la destination et distribue les présents.

CREATE OR REPLACE VIEW santa.v_etape_distribution as (
	SELECT 
		concat_ws(
			'-',
			vdg.id,
			'd'
		) as id,
		'Distribution' as type_etape,
		vdg.pays,
		vdg.region,
		vdg.ville,
		-- S'il n'y a pas de prochaine destination, alors on retourne chez le Père Noël
		COALESCE(
			vdg.pays_next,
			var.maison_pere_noel ->> 'pays'
		) as pays_next,
		COALESCE(
			vdg.region_next,
			var.maison_pere_noel ->> 'region'
		) as region_next,
		COALESCE(
			vdg.ville_next,
			var.maison_pere_noel ->> 'ville'
		) as ville_next,
		vdg.timezone,
		vdg.utc_offset,
		vdg.utc_offset_next,
		vdg.duree_destination,
		vdg.duree_destination * (1 - (var.pourcent_temps_de_vol / 100)) AS duree_etape,
		vdg.population,
		vdg.pop_fictive,
		-- Nombre de cadeaux distribués
		round( var.cadeau_par_pop * vdg.pop_fictive::numeric * var.cadeau_par_pers, 0) AS cadeau_distribue,
		vdg.geom
	FROM 
		santa.v_destination_globale as vdg
	CROSS JOIN 
		santa.v_variable as var
)
;

Étapes de trajet

A partir des données des destination, on génère également des étapes de trajet.

Pendant ces étapes, le Père Noël vol à bord de son traineau de destination en destination.

CREATE OR REPLACE VIEW santa.v_etape_trajet as (
	SELECT 
		concat_ws(
			'-',
			vdg.id,
			't'
		) as id,
		'Trajet' as type_etape,
		vdg.pays,
		vdg.region,
		vdg.ville,
		vdg.pays_next,
		vdg.region_next,
		vdg.ville_next,
		vdg.timezone,
		vdg.utc_offset,
		vdg.utc_offset_next,
		vdg.duree_destination,
		-- La durée du vol dépend de la distance entre la destination et la suivante
		-- ainsi, la vitesse de vol est constante dans une zone horaire
		(
			vdg.duree_zone 
			* (var.pourcent_temps_de_vol / 100) 
			* (
				ST_Length(ST_MakeLine(vdg.geom, vdg.geom_next)) 
				/ sum(ST_Length(ST_MakeLine(vdg.geom, vdg.geom_next))) OVER zone_horaire
			)
		 ) as duree_etape,
		ST_Length(ST_MakeLine(vdg.geom, vdg.geom_next)) as distance,
		-- Trajet effectué
		ST_MakeLine(vdg.geom, vdg.geom_next) as geom
	FROM 
		santa.v_destination_globale as vdg
	CROSS JOIN 
		santa.v_variable as var
	WHERE 
		vdg.ville_next IS NOT NULL
	WINDOW 
		zone_horaire AS (
			PARTITION BY 
				vdg.utc_offset
		)
)
;

Étapes spécifiques au Père Noël

Le Père Noël possède des étapes spécifiques ne dépendant pas des destinations : lorsqu’il fabrique les cadeaux, lorsqu’il démarre sa tournée pour rejoindre la première destination…

Pour cela on effectue plusieurs requête que l’on va unir avec un UNION ALL.

CREATE OR REPLACE VIEW santa.v_etape_santa as (
	-- Préparatifs
	SELECT 
		'1' as id,
		'Préparatifs' as type_etape,
		var.maison_pere_noel ->> 'pays' as pays,
		var.maison_pere_noel ->> 'region' as region,
		var.maison_pere_noel ->> 'ville' as ville,
		vdg.pays as pays_next,
		vdg.region as region_next,
		vdg.ville as ville_next,
		var.maison_pere_noel ->> 'timezone' as timezone,
		(var.maison_pere_noel ->> 'utc_offset')::interval as utc_offset,
		vdg.utc_offset as utc_offset_next,
		-- Durée : 1er janvier au 25 décembre minuit dans le premier fuseau horaire moins 2 minutes de trajet pour rejoindre la première destination
		var.debut_distrib - (extract(year from now()) || '-01-01 00:00:00 +1')::timestamp with time zone - var.delai_dest_santa_house as duree_etape,
		-- Nombre de cadeaux fabriqués
		var.pop_total * var.cadeau_par_pop * var.cadeau_par_pers AS cadeau_fabrique,
		0 as distance,
		-- Emplacement
		ST_Transform(
			(var.maison_pere_noel ->> 'geom_4326')::geometry(point,4326),
			3857
		) as geom
	FROM 
		santa.v_destination_globale as vdg
	CROSS JOIN 
		santa.v_variable as var
	WHERE 
		-- Première étape
		vdg.id = '01-001'
	
	UNION ALL
	-- Voyage vers 1ère destination
	SELECT 
		'2' as id,
		'Démarrage tournée' as type_etape,
		var.maison_pere_noel ->> 'pays' as pays,
		var.maison_pere_noel ->> 'region' as region,
		var.maison_pere_noel ->> 'ville' as ville,
		vdg.pays as pays_next,
		vdg.region as region_next,
		vdg.ville as ville_next,
		var.maison_pere_noel ->> 'timezone' as timezone,
		(var.maison_pere_noel ->> 'utc_offset')::interval as utc_offset,
		vdg.utc_offset as utc_offset_next,
		var.delai_dest_santa_house as duree_etape,
		0 as cadeau_fabrique,
		ST_Length(ST_MakeLine(
			ST_Transform(
				(var.maison_pere_noel ->> 'geom_4326')::geometry(point,4326),
				3857
			),
			vdg.geom
		)) as distance,
		-- Trajet effectué
		ST_MakeLine(
			ST_Transform(
				(var.maison_pere_noel ->> 'geom_4326')::geometry(point,4326),
				3857
			),
			vdg.geom
		) as geom
	FROM 
		santa.v_destination_globale as vdg
	CROSS JOIN 
		santa.v_variable as var
	WHERE 
		-- Première étape
		vdg.id = '01-001'

	UNION ALL
	-- Voyage retour dernière destination
	SELECT 
		'4' as id,
		'Fin tournée' as type_etape,
		vdg.pays,
		vdg.region,
		vdg.ville,
		var.maison_pere_noel ->> 'pays' as pays_next,
		var.maison_pere_noel ->> 'region' as region_next,
		var.maison_pere_noel ->> 'ville' as ville_next,
		vdg.timezone,
		vdg.utc_offset,
		(var.maison_pere_noel ->> 'utc_offset')::interval as utc_offset_next,
		var.delai_dest_santa_house as duree_etape,
		0 as cadeau_fabrique,
		ST_Length(ST_MakeLine(
			vdg.geom,
			ST_Transform(
				(var.maison_pere_noel ->> 'geom_4326')::geometry(point,4326),
				3857
			)
		)) as distance,
		-- Trajet effectué
		ST_MakeLine(
			vdg.geom,
			ST_Transform(
				(var.maison_pere_noel ->> 'geom_4326')::geometry(point,4326),
				3857
			)
		) as geom
	FROM 
		santa.v_destination_globale as vdg
	CROSS JOIN 
		santa.v_variable as var
	WHERE 
		-- Dernière étape
		vdg.ville_next IS NULL

	UNION ALL
	-- Repos
	SELECT 
		'5' as id,
		'Repos' as type_etape,
		var.maison_pere_noel ->> 'pays' as pays,
		var.maison_pere_noel ->> 'region' as region,
		var.maison_pere_noel ->> 'ville' as ville,
		NULL as pays_next,
		NULL as region_next,
		NULL as ville_next,
		var.maison_pere_noel ->> 'timezone' as timezone,
		(var.maison_pere_noel ->> 'utc_offset')::interval as utc_offset,
		NULL::interval as utc_offset_next,
		-- Durée : 1er janvier au 25 décembre minuit dans le premier fuseau horaire moins 2 minutes de trajet pour rejoindre la première destination
		(extract(year from now()) || '-12-31 23:59:59 +1')::timestamp with time zone - (extract(year from now()) || '-12-25 01:00:00 -12')::timestamp with time zone - var.delai_dest_santa_house as duree_etape,
		0 AS cadeau_fabrique,
		0 as distance,
		-- Emplacement
		ST_Transform(
			(var.maison_pere_noel ->> 'geom_4326')::geometry(point,4326),
			3857
		) as geom
	FROM 
		santa.v_variable as var
)
;

Construction du voyage

Pour créer le voyage, les étapes suivantes sont associées (via des UNION ALL) :

• Les étapes de distribution des cadeaux.
• Les étapes de trajet entre destinations.
• Les étapes spécifiques au Père Noël.

Les éléments sont ensuite réagencés selon l’ordre de passage afin de pouvoir calculer certains cumuls (population visitée, cadeaux distribués, distance parcourue…).

Les données du voyage sont fixées dans une vue matérialisée pour améliorer les performance lors de la consultation :

CREATE MATERIALIZED VIEW santa.vm_voyage as (
	WITH voyage_complet as (
		-- Etapes de distribution
		SELECT 
			concat_ws(
				'-',
				'3',
				id
			) as id_voyage,
			type_etape,
			pays,
			region,
			ville,
			pays_next,
			region_next,
			ville_next,
			timezone,
			utc_offset,
			utc_offset_next,
			duree_etape,
			population,
			pop_fictive,
			cadeau_distribue,
			0 as cadeau_fabrique,
			0 as distance,
			geom
		FROM 
			santa.v_etape_distribution
		
		UNION ALL 
		-- Etapes de trajet
		SELECT 
			concat_ws(
				'-',
				'3',
				id
			) as id_voyage,
			type_etape,
			pays,
			region,
			ville,
			pays_next,
			region_next,
			ville_next,
			timezone,
			utc_offset,
			utc_offset_next,
			duree_etape,
			0 as population,
			0 as pop_fictive,
			0 as cadeau_distribue,
			0 as cadeau_fabrique,
			distance,
			geom
		FROM 
			santa.v_etape_trajet
		
		UNION ALL 
		-- Etapes spécifiques au Père Noël
		SELECT
			id as id_voyage, 
			type_etape,
			pays,
			region,
			ville,
			pays_next,
			region_next,
			ville_next,
			timezone,
			utc_offset,
			utc_offset_next,
			duree_etape,
			0 as population,
			0 as pop_fictive,
			0 as cadeau_distribue,
			cadeau_fabrique,
			distance,
			geom
		FROM 
			santa.v_etape_santa
	)
	SELECT
		id_voyage, 
		type_etape,
		pays,
		region,
		ville,
		pays_next,
		region_next,
		ville_next,
		timezone,
		utc_offset,
		utc_offset_next,
		duree_etape,
		-- Calcul de l'horaire du début :
		-- 01/01 de l'année en cours + durée des étapes précédentes
		((extract(year from now()) || '-01-01 00:00:00 +1')::timestamp with time zone
		+ coalesce(sum(duree_etape) OVER all_etape, '0')) as horaire_debut,
		to_char(
			((extract(year from now()) || '-01-01 00:00:00 +1')::timestamp with time zone
			+ coalesce(sum(duree_etape) OVER all_etape, '0'))
			AT TIME ZONE utc_offset,
			'DD TMmon YYYY'
		) AS date_debut_local,
		to_char(
			((extract(year from now()) || '-01-01 00:00:00 +1')::timestamp with time zone
			+ coalesce(sum(duree_etape) OVER all_etape, '0'))
			AT TIME ZONE utc_offset,
			'HH24:MI:SS'
		) AS heure_debut_local,
		-- Calcul de l'horaire de fin :
		-- 01/01 de l'année en cours + durée des étapes précédentes + durée de l'étape
		((extract(year from now()) || '-01-01 00:00:00 +1')::timestamp with time zone
		+ coalesce(sum(duree_etape) OVER all_etape, '0')
		+ duree_etape) as horaire_fin,
		to_char(
			((extract(year from now()) || '-01-01 00:00:00 +1')::timestamp with time zone
			+ coalesce(sum(duree_etape) OVER all_etape, '0')
			+ duree_etape) 
			AT TIME ZONE utc_offset_next,
			'DD TMmon YYYY'
		) AS date_fin_local,
		to_char(
			((extract(year from now()) || '-01-01 00:00:00 +1')::timestamp with time zone
			+ coalesce(sum(duree_etape) OVER all_etape, '0')
			+ duree_etape) 
			AT TIME ZONE utc_offset_next,
			'HH24:MI:SS'
		) AS heure_fin_local,
		population,
		pop_fictive,
		coalesce(sum(pop_fictive) OVER all_etape, 0) as pop_fictive_cumul,
		cadeau_distribue,
		coalesce(sum(cadeau_distribue) OVER all_etape, 0) as cadeau_distribue_cumul,
		cadeau_fabrique,
		distance,
		coalesce(sum(distance) OVER all_etape, 0) as distance_cumul,
		(distance) / (EXTRACT(EPOCH FROM duree_etape)) as vitesse_ms,
		-- Vitesse en km/h
		trim(to_char(((distance) / (EXTRACT(EPOCH FROM duree_etape)))/3.6, '999 999 km/h')) AS vitesse,
		-- Azimuth du symbole sur la carte
		CASE 
			WHEN GeometryType(geom) = 'LINESTRING' THEN 
				Degrees(ST_Azimuth(ST_StartPoint(geom),ST_EndPoint(geom)))
			ELSE 0
		END as azimuth,
		-- Orientation du symbole sur la carte
		CASE 
			WHEN GeometryType(geom) = 'LINESTRING' AND Degrees(ST_Azimuth(ST_StartPoint(geom),ST_EndPoint(geom))) < 180 THEN 'Est' 
			WHEN GeometryType(geom) = 'LINESTRING' AND Degrees(ST_Azimuth(ST_StartPoint(geom),ST_EndPoint(geom))) > 180 THEN 'Ouest'
			ELSE '-'
		END as orientation,
		GeometryType(geom) as type_geom,
		geom
	FROM voyage_complet
	WINDOW 
		all_etape AS (
			ORDER BY 
				id_voyage
			ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
		)
)
;

On oublie pas de calculer quelques index :

CREATE UNIQUE INDEX unique_idx_vm_voyage ON santa.vm_voyage USING BTREE (id_voyage);
CREATE UNIQUE INDEX horaire_debut_idx_vm_voyage ON santa.vm_voyage (horaire_debut);
CREATE UNIQUE INDEX horaire_fin_idx_vm_voyage ON santa.vm_voyage (horaire_fin);
CREATE INDEX geom_idx_vm_voyage ON santa.vm_voyage USING GIST (geom);

Nous avons maintenant un trajet qui ressemble a ceci :

Suivi en live

Avoir un trajet c’est bien mais connaitre toutes les infos sur le trajet à un « instant t », c’est mieux.

Pour cela, nous allons utiliser une vue se basant sur la vue matérialisée précédente et qui ne va extraire qu’une seule ligne correspondant à l’étape du voyage lors de l’instant présent. Nous allons également utiliser diverses astuces pour générer des paroles prononcées par le Père Noël ainsi que du HTML pour mettre en forme le rendu final.

On va utiliser une requête CTE pour calculer les valeurs dont nous avons besoin, puis pour générer le HTML.

CREATE OR REPLACE VIEW santa.v_suivi_santa_live as (
	WITH 
		info_suivi as (
			SELECT
				id_voyage, 
				type_etape,
				-- On génère des paroles du Père Noël.
				-- Le texte varie selon le moment de l'année et change toutes les 10 secondes
				jsonb_extract_path_text(
					parole_pere_noel,
					type_etape,
					FLOOR(EXTRACT(second from var.now) / 5)::text
				) as parole,
				-- Génération d'un titre
				CASE 
					WHEN type_etape = 'Préparatifs' THEN 'Le Père Noël prépare les cadeaux'
					WHEN type_etape = 'Démarrage tournée' THEN 'Le Père Noël est parti vers son premier lieu de distribution'
					WHEN type_etape = 'Trajet' THEN 'Le Père Noël voyage vers un nouveau lieu de distribution'
					WHEN type_etape = 'Distribution' THEN 'Le Père Noël est en cours de distribution'
					WHEN type_etape = 'Fin tournée' THEN 'Le Père Noël a fini sa tournée et rentre chez lui'
					WHEN type_etape = 'Repos' THEN 'Le Père Noël a terminé et prend un repos bien mérité'
				END AS titre,
				pays,
				region,
				ville,
				pays_next,
				region_next,
				ville_next,
				timezone,
				utc_offset,
				utc_offset_next,
				duree_etape::text,
				EXTRACT(EPOCH FROM duree_etape) as duree_etape_seconde,
				horaire_debut,
				horaire_fin,
				-- Timer pour connaitre le prochain départ
				concat(
					NULLIF(extract(day from (horaire_fin - var.now)), 0) || ' jours, ',
					NULLIF(extract(hour from (horaire_fin - var.now)), 0) || ' heures, ',
					NULLIF(extract(minute from (horaire_fin - var.now)), 0) || ' minutes et ',
					round(extract(second from (horaire_fin - var.now))::numeric, 0) || ' secondes'
				) AS timer,
				date_debut_local,
				heure_debut_local,
				date_fin_local,
				heure_fin_local,
				population,
				-- Variation de la population durant l'étape
				trim(to_char( 
					CASE 
						WHEN type_etape = 'Distribution' THEN 
							temps_passe_etape.pourcent * population
						ELSE 0
					END + pop_fictive_cumul,
					'999 999 999 999'
				)) AS pop_cumul,
				cadeau_distribue,
				-- Variation du nombre de cadeau distribués durant l'étape
				trim(to_char( 
					CASE 
						WHEN type_etape = 'Distribution' THEN 
							temps_passe_etape.pourcent * cadeau_distribue
						ELSE 0
					END + cadeau_distribue_cumul,
					'999 999 999 999'
				)) AS cadeau_distribue_cumul,
				trim(to_char(cadeau_fabrique, '999 999 999 999')) as cadeau_fabrique,
				-- Variation du nombre de cadeau fabriqué durant l'étape (uniquement lors de la fabrication)
				trim(to_char(
					round(
						temps_passe_etape.pourcent * cadeau_fabrique,
						0
					),
					'999 999 999 999'
				)) as cadeau_fabrique_cumul,
				-- Distance parcourue depuis le début
				-- il faut tenir compte des étapes de distribution qui sont des points
				trim(
					to_char(
						(
							(
								CASE 
									WHEN type_geom = 'LINESTRING' THEN 
										ST_Length(
											ST_LineSubstring(
												geom,
												0,
												temps_passe_etape.pourcent
											)
										)
									ELSE 0
								END +
								distance_cumul
							)
							/1000
						),
						'999 999 999 999 km'
					)
				) AS distance_cumul,
				vitesse_ms,
				vitesse,
				azimuth,
				orientation,
				CASE 
					WHEN type_geom = 'LINESTRING' THEN 
						-- Calcul de l'emplacement
						ST_LineInterpolatePoint(
							geom,
							temps_passe_etape.pourcent
						)
					ELSE geom
				END::geometry(point,3857) as geom
			FROM vm_voyage
			CROSS JOIN 
				santa.v_variable as var
			-- Calcul du pourcentage de temps passé depuis le début de l'étape 
			CROSS JOIN LATERAL (
				SELECT EXTRACT(EPOCH FROM (var.now - horaire_debut)) / EXTRACT(EPOCH FROM (horaire_fin - horaire_debut)) as pourcent
			) as temps_passe_etape
			-- On ne récupère que l'étape en cours 
			WHERE horaire_debut <= var.now
			AND horaire_fin > var.now
		)
	-- Génération du HTML final
	SELECT 
		*,
		CASE
			WHEN type_etape = 'Préparatifs' THEN
				concat(
					'<p class="titre">', titre, '</p>',
					'<div>',
						...
					'</div>'
				)
			WHEN type_etape IN ('Trajet', 'Démarrage tournée', 'Fin tournée') THEN 
				concat(
					'<p class="titre">', titre, '</p>',
					'<div>',
						...
					'</div>'
				)
			WHEN type_etape = 'Distribution' THEN 
				concat(
					'<p class="titre">', titre, '</p>',
					'<div>',
						...
					'</div>'
				)
			WHEN type_etape = 'Repos' THEN
				concat(
					'<p class="titre">', titre, '</p>',
					'<div>',
						...
					'</div>'
				)
		END ||
		'</div>' as informations
	FROM info_suivi
)
;

Voila un exemple de réponse :

L’état des lieux

Pour chaque destination, il est possible de voir en temps réel si le Père Noël est passé ou non. Pour cela nous allons nous appuyer sur la table des destinations ainsi que la vue matérialisée vm_voyage.

CREATE MATERIALIZED VIEW santa.vm_info_destination as (
	SELECT
		min(id_voyage) as id_voyage,
		pays,
		region,
		ville,
		timezone,
		utc_offset,
		max(utc_offset_next) as utc_offset_next,
		max(horaire_debut) as horaire_debut,
		to_char(max(horaire_debut) AT TIME ZONE utc_offset,'DD TMmon YYYY') AS date_debut_local,
		to_char(max(horaire_debut) AT TIME ZONE utc_offset,'HH24:MI:SS') AS heure_debut_local,
		min(horaire_fin) as horaire_fin,
		to_char(min(horaire_fin) AT TIME ZONE utc_offset_next,'DD TMmon YYYY') AS date_fin_local,
		to_char(min(horaire_fin) AT TIME ZONE utc_offset_next,'HH24:MI:SS') AS heure_fin_local,
		trim(to_char(population, '999 999 999 999')) AS population,
		geom::geometry(point,3857) as geom
	FROM vm_voyage
	WHERE type_geom = 'POINT'
	GROUP BY 
		pays,
		region,
		ville,
		timezone,
		utc_offset,
		utc_offset_next,
		population,
		geom
)
;

A partir de ces informations, il est possible de calculer différente informations en temps réelle :

CREATE OR REPLACE VIEW santa.v_suivi_destination_live as (
	SELECT
		id_voyage,
		pays,
		region,
		ville,
		timezone,
		utc_offset,
		utc_offset_next,
		horaire_debut,
		horaire_fin,
		concat(
			NULLIF(extract(day from (horaire_fin - var.now)), 0) || ' jours, ',
			NULLIF(extract(hour from (horaire_fin - var.now)), 0) || ' heures, ',
			NULLIF(extract(minute from (horaire_fin - var.now)), 0) || ' minutes et ',
			round(extract(second from (horaire_fin - var.now))::numeric, 0) || ' secondes'
		) AS timer,
		date_debut_local,
		heure_debut_local,
		date_fin_local,
		heure_fin_local,
		population,
		-- Génération d'un message d'information sur l'état de passage
		CASE 
			-- Pour la maison du Père Noël
			WHEN id_voyage = '1' THEN 
				CASE 
					WHEN var."now" < horaire_fin THEN 'Le Père Noël prépare les cadeaux'
					WHEN var."now" < horaire_debut THEN 'Le Père Noël est absent'
					ELSE 'Le Père Noël se repose'
				END
			-- Sinon on détermine si la visite est réalisée en fonction de l'heure de passage
			WHEN var."now" < horaire_debut  THEN 'Attend la visite du Père Noël'
			WHEN var."now" > horaire_fin  THEN 'A reçu la visite du Père Noël'
			ELSE 'Le Père Noël est ici !'
		END AS etat,
		-- Quelques détails supplémentaires
		CASE
			WHEN id_voyage = '1' THEN 
				CASE 
					WHEN var."now" < horaire_fin THEN '<p>Le Père Noël est ici, vous le trouverez dans sa remise</p>'
					WHEN var."now" < horaire_debut THEN '<p>Le Père Noël est en tournée</p>'
					ELSE '<p>Le Père Noël est ici, vous le trouverez dans son lit</p>'
				END
			WHEN var."now" > horaire_fin THEN
				'<p>Le Père Noël est déjà passé 😉</p>'
			WHEN var."now" < horaire_debut THEN
				'<p>Le Père Noël arrive dans ' || 
				concat(
					NULLIF(extract(day from (horaire_fin - var.now)), 0) || ' jours, ',
					NULLIF(extract(hour from (horaire_fin - var.now)), 0) || ' heures, ',
					NULLIF(extract(minute from (horaire_fin - var.now)), 0) || ' minutes et ',
					round(extract(second from (horaire_fin - var.now))::numeric, 0) || ' secondes'
				)
				|| '</p>'
			ELSE
				'<p>Le Père Noël est en cours de distribution vas dans ton lit !</p>'
		END as informations,
		geom::geometry(point,3857) as geom
	FROM vm_info_destination
	CROSS JOIN 
		santa.v_variable as var
)
;

---

Voila ! J’espère que ça vous a plu.

🎄 Joyeux Noël !! 🎄

Cet article vous a plu ?

N'hésitez pas à le partager, il interessera surement certains de vos contacts.

Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :

PostGISPostgreSQLSQL fenetragelateral joinwindow