Suivons le Père Noël avec PostGIS

Cette année pour Noël, j’ai eu l’idée de monter une application de suivi du Père Noël en temps réel. Google le fait déjà et le NORAD aussi alors pourquoi pas un frenchy ?

Comme j’ai « quelques notions » de SQL et que je travaille chez Business Geografic (Ciril GROUP) (éditeur français de solutions cartographiques web) qui a accepter de suivre ma démarche, j’ai tous les outils nécessaires à une telle application sous la main.

Pour la suite des explications, c’est juste en dessous de l’appli (que vous pouvez consulter en plein écran sur noel.cirilgroup.com).

Notez que cet article s’intéresse surtout à la partie donnée (PostgreSQL + PostGIS) et moins à la partie conception de l’application qui repose sur GEO Software de Business Geografic (Ciril GROUP).

---

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 de m’envoyer son itinéraire détaillé, un petit GPX aurait pourtant bien fait mon affaire.

Il faut donc un peu ruser pour suivre Santa à la trace, 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 que la terre tourne donc que l’heure de passage s’étale de GMT+12 à GMT-12 (d’est en ouest). Plus facile à dire qu’a faire mais essayons.

L’application repose sur PostgreSQL 11 + PostGIS 2.5 (je sais, c’est oldschool mais ça tourne bien) mais vous pouvez utiliser les requêtes suivantes dans PostgreSQL 15 + PostGIS 3.x.

Nous serons dans le schéma suivant :

CREATE SCHEMA IF NOT EXISTS santa_tracker;

Quelques variables

Pour les différents calculs que nous allons mener, je vais avoir besoin de variables. Pour cela, nous allons créer une vue dans laquelle nous stockerons des variables que nous pourrons utiliser dans nos différentes requêtes.

DROP VIEW IF EXISTS santa_tracker.v_variable CASCADE;

CREATE VIEW santa_tracker.v_variable as (
	SELECT 
		-- Le nombre de destinations total
		count(1) + 1 as nb_ville,
		-- La population totale des destination sélectionnées
		sum(population) as pop_ville_total,
		-- La largeur de découpage de la Terre (pour faire des bandes de passage)
		10 as degre_par_zone_longitude,
		-- Le nombre de cadeau par personnes
		1 as cadeau_par_pers,
		-- Le nombre total d'être humain sur Terre
		8000000000 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,
		-- L'heure exacte du moment de la consultation
		now() AS date_actuelle,
		-- L'heure exacte du début des préparatifs par le Père Noël
		(extract(year from now()) || '-01-01 00:00:00 +00')::timestamp with time zone as debut_preparatif_utc,
		-- L'heure exacte du début de la distribution
		(extract(year from now()) || '-12-24 17:00:00 +12')::timestamp with time zone as debut_distrib_utc,
		-- L'heure exacte de la fin de la distribution
		(extract(year from now()) || '-12-25 07:00:00 -12')::timestamp with time zone as fin_distrib_utc,
		-- Durée du trajet pour rejoindre la première destination
		'2 minutes'::interval as delai_premiere_dest,
		-- Le pourcentage de répartition entre temps de vol et temps de distribution (ici 10% de distribution - 90% de temps de vol)
		10::numeric as pourcent_distrib_par_etape,
		-- Les informations sur l'emplacement de la maison du Père Noël
		jsonb_build_object(
			'id', 0,
			'region', 'Svalbard',
			'ville', 'Maison de la famille Noël',
			'population', 2,
			'timezone', 0,
			'geom_4326', 'SRID=4326;POINT(24 80)'
		) as maison_pere_noel
	FROM 
		santa_tracker.destination 
)
;

Notez l’utilisation des heures avec timezone ainsi que la présence de fonctions pour que la vue soit valide tous les ans.

La vue repose en fait sur une table que nous allons découvrir dans la partie suivante ce qui me permet de faire des statistiques sur cette table.

Destination

A priori, le Père Noël visite toutes les maisons du monde. Ceci dit, toutes les maison 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 les déplacements. Simplifions encore et prenons environ 500 destinations parmi les plus connues dans le monde.

Pour récupérer les données, une petite recherche Google et voila 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 santa_tracker.destination (
	id_destination int8 PRIMARY KEY,
	region text,
	ville text,
	population int8,
	-- Timezone en minute par rapport à GMT
	timezone int8,
	geom public.geometry(point, 4326)
);

Problème, cette table ne possède pas l’emplacement du Père Noël… Créons une vue qui unit ces données avec l’emplacement de la maison du Père Noël décrit dans la vue v_variable.

DROP VIEW IF EXISTS santa_tracker.v_destination CASCADE;

CREATE OR REPLACE VIEW santa_tracker.v_destination as (
	SELECT 
		id_destination,
		region,
		ville,
		population ,
		timezone,
		geom
	FROM 
		santa_tracker.destination 
	UNION 
	SELECT 
		(maison_pere_noel ->> 'id')::int,
		maison_pere_noel ->> 'region',
		maison_pere_noel ->> 'ville',
		(maison_pere_noel ->> 'population')::int,
		(maison_pere_noel ->> 'timezone')::int,
		(maison_pere_noel ->> 'geom_4326')::geometry(point,4326)
	FROM 
		santa_tracker.v_variable
)
;

Trajet

Nous devons maintenant calculer le trajet.

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

• Passer par toutes les destinations
• Démarrer et finir par la maison du Père Noël
• Chaque destination doit être visitée la nuit

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.

Dans la vue v_variable j’avais indiqué un découpage en zone de 10 degrés mais notez que si aucune destination n’existe dans une zone, il ne faudra alors pas en tenir compte.

L’intervalle est définit par les dates de début et de fin de la tournée spécifiées dans la vue v_variable : du 24 décembre à 17h (+12) jusqu’au 25 décembre à 7h (-12) : 1j et 14h soit 38h.

Pour calculer le trajet et pouvoir relancer le calcul facilement, je vais me baser sur une vue matérialisée (accrochez-vous) :

DROP MATERIALIZED VIEW IF EXISTS santa_tracker.vm_trajet CASCADE;

CREATE MATERIALIZED VIEW santa_tracker.vm_trajet as (
WITH
	-- Récupération des destinations et calcul de la plage de longitude
	destination_plage_longitude as (
		SELECT 
			id_destination,
			region,
			ville,
			population,
			-- Conversion en valeur horaire
			(timezone/3600) as timezone,
			-- La maison du père Noël (id=0) passe en premier
			CASE 
				WHEN id_destination = 0 THEN 1
				ELSE 2
			END as ordre_primaire,
			-- On détermine un numéro de plage de longitude
			floor((-ST_X(geom) + 180)::numeric / var.degre_par_zone_longitude) + 1 as plage_longitude,
			0 as zone_latitude,
			geom
		FROM 
			santa_tracker.v_destination 
		CROSS JOIN 
			santa_tracker.v_variable as var
		UNION 
		-- On ajoute une seconde fois la maison du père noël pour le retour final
		SELECT 
			(maison_pere_noel ->> 'id')::int,
			maison_pere_noel ->> 'region',
			maison_pere_noel ->> 'ville',
			(maison_pere_noel ->> 'population')::int,
			(maison_pere_noel ->> 'timezone')::int,
			-- La maison du père Noël passe maintenant en dernier
			3 as ordre_primaire,
			-- On détermine un numéro de plage de longitude
			floor(
				(-ST_X(
					(maison_pere_noel ->> 'geom_4326')::geometry(point,4326)
				) + 180)::numeric 
				/ degre_par_zone_longitude
			) + 1 as plage_longitude,
			0 as zone_latitude,
			(maison_pere_noel ->> 'geom_4326')::geometry(point,4326)
		FROM 
			santa_tracker.v_variable
	),

	-- Calcul d'un numéro de zone de longitude
	destination_zone_longitude as (
		SELECT 
			id_destination,
			region,
			ville,
			population,
			timezone,
			ordre_primaire,
			plage_longitude,
			-- Le fenêtrage permet de récupérer le rang de chaque plage
			-- Cela permet d'éliminer des zones sans destination
			dense_rank() OVER (
				ORDER BY plage_longitude
				ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
			) as zone_longitude,
			geom
		FROM 
			destination_plage_longitude
	),

	-- Calcul d'une plage de latitude
	destination_plage_latitude as (
		SELECT 
			id_destination,
			region,
			ville,
			population,
			timezone,
			ordre_primaire,
			zone_longitude,
			-- Pour faire un effet "yoyo", on inverse l'ordonnancement des latitudes une zone de longitude sur deux
			CASE 
				WHEN zone_longitude % 2 = 0 THEN ST_Y(geom)
				ELSE -ST_Y(geom)
			END as plage_latitude,
			geom
		FROM 
			destination_zone_longitude
	),

	-- Calcul d'un numéro de zone de latitude
	destination_zone_latitude as (
		SELECT 
			id_destination,
			region,
			ville,
			population,
			timezone,
			ordre_primaire,
			zone_longitude,
			-- Le fenêtrage permet de récupérer le rang de chaque plage
			row_number() OVER (
				PARTITION BY zone_longitude
				ORDER BY zone_longitude, plage_latitude
				ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
			) as zone_latitude,
			geom
		FROM 
			destination_plage_latitude
	),

	-- Mise en forme de chaque étape
	etape as (
		SELECT 
			-- Numéro d'ordre qui servira d'identifiant
			row_number() OVER (
				ORDER BY t1.ordre_primaire, t1.zone_longitude, t1.zone_latitude
			) as id_etape,
			t1.id_destination,
			t1.region,
			t1.ville,
			-- Le fenêtrage permet de récupérer la prochaine destination
			first_value(t1.id_destination) OVER (
				ORDER BY t1.ordre_primaire, t1.zone_longitude, t1.zone_latitude
				ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
			) as id_destination_next,
			first_value(t1.region) OVER (
				ORDER BY t1.ordre_primaire, t1.zone_longitude, t1.zone_latitude
				ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
			) as region_next,
			first_value(t1.ville) OVER (
				ORDER BY t1.ordre_primaire, t1.zone_longitude, t1.zone_latitude
				ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
			) as ville_next,
			t1.population,
			t1.timezone,
			t1.ordre_primaire,
			t1.zone_longitude,
			t1.zone_latitude,
			-- On détermine le temps de passage dans chaque zone de longitude
			(var.fin_distrib_utc - var.debut_distrib_utc - var.delai_premiere_dest) / stat1.nb_zone_longitude as duree_zone,
			-- On détermine le temps de passage dans chaque destination (distribution + trajet pour la suivante)
			(var.fin_distrib_utc - var.debut_distrib_utc - var.delai_premiere_dest) / stat1.nb_zone_longitude / stat2.nb_destination as duree_etape,
			-- On reprojete les données (parce que c'est comme ça !)
			ST_Transform(t1.geom, 3857) as geom,
			-- Le fenêtrage permet de récupérer la géométrie de la prochaine destination
			ST_Transform(
				first_value(t1.geom) OVER (
				ORDER BY t1.ordre_primaire, t1.zone_longitude, t1.zone_latitude
				ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
				),
				3857
			) as geom_following
		FROM 
			destination_zone_latitude as t1
		CROSS JOIN
			santa_tracker.v_variable as var
		-- Nombre total de zones de longitude
		CROSS JOIN (
			SELECT 
				count(DISTINCT zone_longitude) as nb_zone_longitude
			FROM 
				destination_zone_latitude
			WHERE 
				id_destination <> 0
		) as stat1
		-- Nombre de destinations par zone de longitude
		LEFT JOIN (
			SELECT 
				zone_longitude,
				count(1) as nb_destination
			FROM 
				destination_zone_latitude
			WHERE 
				id_destination <> 0
			GROUP BY 
				zone_longitude
		) as stat2
			ON stat2.zone_longitude = t1.zone_longitude
	),

	--Calcul des étapes de distribution (sur la destination)
	trajet_distrib as (
		SELECT
			lpad(id_etape::text, 5, '0') || '-1' as id_etape,
			id_destination,
			region,
			ville,
			id_destination_next,
			region_next,
			ville_next,
			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( (population / var.pop_ville_total) * var.pop_total::numeric, 0) as pop_fictive,
			timezone,
			-- Durée de distribution
			(var.pourcent_distrib_par_etape / 100) * (duree_etape) as duree_trajet,
			'Distribution' as type_trajet,
			ST_Length(ST_MakeLine(geom, geom)) as distance,
			-- Azimuth du symbole sur la carte
			270 as azimuth,
			-- Orientation du symbole sur la carte
			'Ouest' as orientation,
			-- Trajet effectué (ici ce sera un point)
			ST_MakeLine(geom, geom) as geom
		FROM 
			etape
		CROSS JOIN
			santa_tracker.v_variable as var
		WHERE 
			id_destination <> 0
		ORDER BY 
			id_etape
	),

	--Calcul étape de voyage (de la destination vers la suivante)
	trajet_voyage as (
		SELECT
			lpad(id_etape::text, 5, '0') || '-2' as id_etape,
			id_destination,
			region,
			ville,
			id_destination_next,
			region_next,
			ville_next,
			population,
			0 as pop_fictive,
			timezone,
			-- Durée de voyage en l'air
			CASE 
				WHEN id_etape = 1 THEN var.delai_premiere_dest
				ELSE (1 - (var.pourcent_distrib_par_etape / 100)) * (duree_etape)
			END as duree_trajet,
			'Voyage' as type_trajet,
			ST_Length(ST_MakeLine(geom, geom_following)) as distance,
			-- Azimuth du symbole sur la carte
			Degrees(ST_Azimuth(geom, geom_following)) as azimuth,
			-- Orientation du symbole sur la carte
			CASE 
				WHEN Degrees(ST_Azimuth(geom, geom_following)) < 180 THEN 'Est' 
				ELSE 'Ouest'
			END as orientation,
			-- Trajet effectué (ici ce sera un point)
			ST_MakeLine(geom, geom_following) as geom
		FROM 
			etape
		CROSS JOIN
			santa_tracker.v_variable as var
		WHERE 
			region_next IS NOT NULL
		ORDER BY 
			id_etape
	),

	-- Agrégation de tous les trajets (distribution + voyage)
	trajet as (
		SELECT
			id_etape,
			id_destination,
			region,
			ville,
			id_destination_next,
			region_next,
			ville_next,
			population,
			pop_fictive,
			-- Le fenêtrage permet de récupérer le cumul de population visitée
			coalesce(
				sum(pop_fictive) OVER (
					ORDER BY id_etape
					ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
				),
				0
			) as pop_fictive_cumul,
			timezone,
			duree_trajet,
			-- Le fenêtrage permet de récupérer la durée totale du trajet
			coalesce(
				sum(duree_trajet) OVER (
					ORDER BY id_etape
					ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
				),
				'0'::interval
			) as duree_trajet_cumul,
			-- Le fenêtrage permet de récupérer l'heure de début de l'étape
			var.debut_distrib_utc + 
			coalesce(
				sum(duree_trajet) OVER (
					ORDER BY id_etape
					ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
				),
				'0'::interval
			)
			as date_debut,
			-- Le fenêtrage permet de récupérer l'heure de fin de l'étape
			var.debut_distrib_utc + duree_trajet + 
			coalesce(
				sum(duree_trajet) OVER (
					ORDER BY id_etape
					ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
				),
				'0'::interval
			)
			as date_fin,
			type_trajet,
			distance,
			-- Le fenêtrage permet de récupérer la distance cumulée parcourue
			coalesce(
				sum(distance) OVER (
					ORDER BY id_etape
					ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
				),
				0
			) as distance_cumul,
			azimuth,
			orientation,
			geom
		FROM (
			SELECT * FROM trajet_distrib
			UNION ALL
			SELECT * FROM trajet_voyage
		) as t1
		CROSS JOIN
			santa_tracker.v_variable as var
		ORDER BY 
			id_etape
	)
SELECT 
	id_etape,
	id_destination,
	id_destination_next,
	region,
	region_next,
	ville,
	ville_next,
	population,
	pop_fictive,
	pop_fictive_cumul,
	-- Nettoyage des décimales
	round( var.cadeau_par_pop * pop_fictive::numeric, 0) as cadeau_distribue,
	-- Nettoyage des décimales
	round( var.cadeau_par_pop * pop_fictive_cumul::numeric, 0) as cadeau_distribue_cumul,
	timezone,
	duree_trajet,
	duree_trajet_cumul,
	date_debut,
	date_fin,
	type_trajet,
	-- Nettoyage des décimales
	round(distance::numeric, 0) as distance,
	-- Nettoyage des décimales
	round(distance_cumul::numeric, 0) as distance_cumul,
	azimuth,
	orientation,
	-- Typage correcte de la colonne géométrique
	geom::geometry(linestring,3857)
FROM 
	trajet
CROSS JOIN
	santa_tracker.v_variable as var
)
;

Instant T

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

On va donc créer une nouvelle vue qui va se baser sur la vue matérialisée vm_trajet pour calculer la position et les paramètres au moment de la consultation de cette vue :

DROP VIEW IF EXISTS santa_tracker.v_instant_t CASCADE;

CREATE OR REPLACE VIEW santa_tracker.v_instant_t as (
WITH 
	trajet as (
		SELECT 
			id_etape,
			id_destination,
			region,
			region_next,
			ville,
			ville_next,
			population,
			pop_fictive,
			pop_fictive_cumul,
			cadeau_distribue,
			cadeau_distribue_cumul,
			timezone,
			duree_trajet,
			duree_trajet_cumul,
			-- On calcul les dates pour que la vue soit toujours valable, même dans 10 ans
			date_debut + (
				EXTRACT(YEAR FROM now()) - 
				EXTRACT(YEAR FROM date_debut) || ' years'
			)::interval as date_debut,
			date_fin + (
				EXTRACT(YEAR FROM now()) - 
				EXTRACT(YEAR FROM date_fin) || ' years'
			)::interval as date_fin,
			-- Pour la symbologie de la carte, j'ai besoin d'avoir le type de trajet et l'orientation
			CASE 
				WHEN type_trajet = 'Voyage' THEN type_trajet || ' - ' || orientation
				ELSE type_trajet
			END as type_trajet,
			distance,
			distance_cumul,
			var.date_actuelle as date_verification,
			-- Calcul du pourcentage d'avancement sur l'étape
			EXTRACT(
				EPOCH FROM (
					var.date_actuelle - 
					(
						date_debut + 
						(
							EXTRACT(YEAR FROM now()) - 
							EXTRACT(YEAR FROM date_debut) || ' years'
						)::interval
					)
				)
			)::numeric / 
			EXTRACT(
				EPOCH FROM (
					(
						date_fin + 
						(
							EXTRACT(YEAR FROM now()) - 
							EXTRACT(YEAR FROM date_fin) || ' years'
						)::interval
					) - 
					(
						date_debut + 
						(
							EXTRACT(YEAR FROM now()) - 
							EXTRACT(YEAR FROM date_debut) || ' years'
						)::interval
					)
				)
			)::numeric as pourcent_avancement,
			azimuth,
			orientation,
			geom 
		FROM 
			santa_tracker.vm_trajet as t
		CROSS JOIN 
			santa_tracker.v_variable as var
		WHERE 
			date_debut <= var.date_actuelle::timestamp
		AND 
			date_fin > var.date_actuelle::timestamp
		UNION ALL 
		-- Ajout d'une étape de préparation des cadeaux
		-- Cette étape sera visible avant le début de la tournée
		SELECT 
			'00000-0',
			id_destination,
			region,
			NULL as region_next,
			ville,
			NULL as ville_next,
			population,
			NULL as pop_fictive,
			NULL as pop_fictive_cumul,
			(var.pop_total * var.cadeau_par_pop) as cadeau_distribue,
			0 as cadeau_distribue_cumul,
			timezone,
			NULL as duree_trajet,
			NULL as duree_trajet_cumul,
			var.debut_preparatif_utc as date_debut,
			var.debut_distrib_utc as date_fin,
			'Préparatifs' as type_trajet,
			NULL as distance,
			NULL as distance_cumul,
			var.date_actuelle as date_verification,
			EXTRACT(
				EPOCH FROM (
					var.date_actuelle - var.debut_preparatif_utc
				)
			)::numeric / 
			EXTRACT(
				EPOCH FROM (
					var.debut_distrib_utc - var.debut_preparatif_utc
				)
			)::numeric as pourcent_avancement,
			0 as azimuth,
			NULL as orientation,
			ST_MakeLine(ST_Transform(geom, 3857) , ST_Transform(geom, 3857)) as geom
		FROM 
			santa_tracker.v_destination as t
		CROSS JOIN 
			santa_tracker.v_variable as var
		WHERE 
			id_destination = 0
		AND
			var.debut_distrib_utc > var.date_actuelle::timestamp
		UNION ALL
		-- Ajout d'une étape de repos
		-- Cette étape sera visible après la fin de la tournée 
		SELECT 
			'00000-2',
			id_destination,
			region,
			NULL as region_next,
			ville,
			NULL as ville_next,
			population,
			NULL as pop_fictive,
			var.pop_total as pop_fictive_cumul,
			NULL as cadeau_distribue,
			(var.pop_total * var.cadeau_par_pop) as cadeau_distribue_cumul,
			timezone,
			NULL as duree_trajet,
			NULL as duree_trajet_cumul,
			var.fin_distrib_utc as date_debut,
			(var.debut_preparatif_utc + interval '1 year') as date_fin,
			'Repos' as type_trajet,
			NULL as distance,
			NULL as distance_cumul,
			var.date_actuelle as date_verification,
			1 as pourcent_avancement,
			0 as azimuth,
			NULL as orientation,
			ST_MakeLine(ST_Transform(geom, 3857) , ST_Transform(geom, 3857)) as geom
		FROM 
			santa_tracker.v_destination as t
		CROSS JOIN 
			santa_tracker.v_variable as var
		WHERE 
			id_destination = 0
		AND
			var.fin_distrib_utc <= var.date_actuelle::timestamp
	),

	trajet_avancement as (
		SELECT 
			id_etape,
			id_destination,
			region,
			region_next,
			ville,
			ville_next,
			population,
			pop_fictive,
			pop_fictive_cumul,
			cadeau_distribue,
			cadeau_distribue_cumul,
			-- Calcul du nombre de cadeau distribué au moment de la visualisation
			round(
				cadeau_distribue_cumul + 
				(cadeau_distribue * pourcent_avancement)::numeric,
				0
			) as cadeau_cumul_actuel,
			timezone,
			duree_trajet,
			duree_trajet_cumul,
			-- Calcul de la durée du trajet au moment de la visualisation
			duree_trajet_cumul + (duree_trajet * pourcent_avancement) as duree_trajet_cumul_cumul_actuel,
			date_debut,
			date_fin,
			type_trajet,
			distance,
			distance_cumul,
			-- Calcul de la distance parcourue au moment de la visualisation
			round(
				distance_cumul + 
				(distance * pourcent_avancement)::numeric,
				0
			) as distance_cumul_actuel,
			date_verification,
			pourcent_avancement,
			azimuth,
			orientation,
			-- Calcul de l'emplacement au moment de la visualisation
			ST_LineInterpolatePoint(geom, pourcent_avancement)::geometry(point,3857) as geom
		FROM 
			trajet
	)

-- Requête finale
SELECT 
	-- On reprend toutes les colonnes
	* ,
	-- On génère un texte que le Père Noël dira lors de sa tournée et qui change toutes les 10 secondes
	CASE 
		WHEN type_trajet = 'Préparatifs' THEN 
			CASE 
				(
					(
						round(
							(EXTRACT(second from date_verification) / 10)::numeric,
							0
						)
					) * 10
				)
				WHEN 10 THEN 'Vive le vent d''hiver !'
				WHEN 20 THEN 'Et un cadeau de plus !'
				WHEN 30 THEN 'Hum ?!'
				WHEN 40 THEN 'Quel beau camion !'
				WHEN 50 THEN 'Ah, une belle poupée !'
				ELSE 'Oh Oh Oh !'
			END
		WHEN type_trajet = 'Distribution' THEN
			CASE 
				(
					(
						round(
							(EXTRACT(second from date_verification) / 10)::numeric,
							0
						)
					) * 10
				)
				WHEN 10 THEN 'Oh hisse !'
				WHEN 20 THEN 'Serrée cette cheminée !'
				WHEN 30 THEN 'Et un cadeau de plus !'
				WHEN 40 THEN 'Oh Oh Oh !'
				WHEN 50 THEN 'Hum un petit biscuit...'
				ELSE 'Oh Oh Oh !'
			END
		WHEN type_trajet LIKE 'Voyage%' THEN
			CASE 
				(
					(
						round(
							(EXTRACT(second from date_verification) / 10)::numeric,
							0
						)
					) * 10
				)
				WHEN 10 THEN 'Oh Oh Oh !'
				WHEN 20 THEN 'Joyeux Noël !'
				WHEN 30 THEN 'Mon beau sapin !'
				WHEN 40 THEN 'Hue tornade !'
				WHEN 50 THEN 'Allez comète !'
				ELSE 'Il fait frais cette nuit !'
			END
		WHEN type_trajet = 'Repos' THEN
			CASE 
				(
					(
						round(
							(EXTRACT(second from date_verification) / 10)::numeric,
							0
						)
					) * 10
				)
				WHEN 10 THEN 'ZZZ'
				WHEN 20 THEN 'ZZZ ZZZ'
				WHEN 30 THEN 'ZZZ'
				WHEN 40 THEN 'ZZZ ZZZ'
				WHEN 50 THEN 'ZZZ'
				ELSE 'ZZZ ZZZ'
			END
	END as parole
FROM
	trajet_avancement
)
;

Notez que dans la dernière requête, je n’ai pas mis la génération des statistiques en temps réel car il s’agit d’un énorme bloc de code HTML qui reprend les différentes informations fournies par la vue donc pas hyper intéressant pour l’article ici présent…

Voila ! J’espère que ça vous a plu et n’hésitez pas à suivre le Père Noël !!

noel.cirilgroup.com

🎄 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 :

PostGISPostgreSQL