Suivons le Père Noël avec PostGIS (Nouvelle version)

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 é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 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;

Destination

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 500 destinations parmi les plus connues dans le monde (avec 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 santa_tracker.destination (
	-- Un identifiant numérique
	id_destination int8 PRIMARY KEY,
	-- 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,
	-- 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.

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_00_variable CASCADE;

CREATE OR REPLACE VIEW santa_tracker.v_00_variable as (

	SELECT 
		-- Population
		-- 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,
		-- Le nombre de cadeau par personnes
		1 as cadeau_par_pers,
		
		-- Autres données
		-- Largeur d'une zone de longitude (ce qui permet de diviser le globe en secteurs de passage)
		10 as largeur_zone_longitude,
		-- Le pourcentage de temps total passé à voler de maison en maison (ici 90% de temps de vol donc 10% du temps passé dans les maisons)
		90::numeric as pourcent_temps_de_vol,
		-- 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,
			'utc_offset', 1,
			'geom_4326', 'SRID=4326;POINT(24 80)'
		) as maison_pere_noel,
		
		-- Dates et horaire
		-- Les dates contiennent toujours les heures
		-- Le temps est exprimé en temps UTC
		-- L'heure exacte du début de la distribution (le 24 décembre à 19h sur le premier fuseau horaire)
		(extract(year from now()) || '-12-24 19:00:00 +12')::timestamp with time zone as debut_distrib,
		-- L'heure exacte de la fin de la distribution (le 25 décembre à 5h sur le dernier fuseau horaire)
		(extract(year from now()) || '-12-25 05:00:00 -12')::timestamp with time zone as fin_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

)
;

Notez l’utilisation des heures avec fuseau horaire car il faut tenir compte des différents emplacements sur Terre.

Pour le calcul des dates, on utilise une extraction de l’année depuis la fonction transaction_timestamp() afin de rendre la vue valide tous les ans.

On définit également une variable now que l’on utilisera lors des calculs à un instant t car cela permet de faire des tests en fixant cette valeur. Cette dernière peut être modifiée pour vos tests, voila quelques moments possibles :

-- Heure du début de la transaction
transaction_timestamp() as "now"	-- Maintenant
'2023-07-03 08:00:00 +0100'::timestamp WITH time zone as "now" -- Fabrication
'2023-12-24 07:59:00 +0100'::timestamp WITH time zone as "now" -- Voyage aller
'2023-12-24 19:00:10 +1200'::timestamp WITH time zone as "now" -- Distribution 1ère destination
'2023-12-24 19:00:45 +1200'::timestamp WITH time zone as "now" -- Voyage depuis 1ère destination
'2023-12-25 00:29:25 +0100'::timestamp WITH time zone as "now" -- Distribution à Genève
'2023-12-25 00:30:00 +0100'::timestamp WITH time zone as "now" -- Voyage depuis Genève
'2023-12-25 05:01:00 -1200'::timestamp WITH time zone as "now" -- Voyage retour
'2023-12-27 08:00:00 +0100'::timestamp WITH time zone as "now" -- Repos

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_00_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_00_variable : du 24 décembre à 19h (+12) jusqu’au 25 décembre à 5h (-12) : 1j et 10h soit 34h.

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

Calcul du rang longitudinal

Le principe est d’attribuer à chaque destination un numéro de rang correspondant au numéro d’ordre de la zone de longitude à laquelle appartient la destination.

Une zone de longitude est une portion de la surface terrestre comprise entre deux bornes de longitudes. Ces bornes sont définies par rapport à un découpage du globe tous les x degrés ; x étant défini dans la vue v_00_variable.

DROP VIEW IF EXISTS santa_tracker.v_01_destination_rang_longitudeCASCADE;

CREATE OR REPLACE VIEW santa_tracker.v_01_destination_rang_longitude as (

	SELECT 
		id_destination,
		region,
		ville,
		population,
		utc_offset,
		-- Récupération du rang de chaque zone de longitude (plage de x degré définit dans la vue v_00_variable)
		-- Le fenêtrage permet d'éliminer les zones sans destination
		dense_rank() OVER zone_longitude as rang_longitude,
		geom
	FROM 
		santa_tracker.destination 
	CROSS JOIN 
		santa_tracker.v_00_variable as var
	WINDOW 
		zone_longitude AS (
			ORDER BY 
				floor(
					(-ST_X(geom) + 180)::numeric 
					/ var.largeur_zone_longitude
				)
				+ 1
			ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
		)

)
;

Ordonnancement par latitude

Au sein de chaque zone de longitude, 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 ».

DROP VIEW IF EXISTS santa_tracker.v_02_destination_rang_latitude CASCADE;

CREATE VIEW santa_tracker.v_02_destination_rang_latitude as (

	SELECT 
		id_destination,
		region,
		ville,
		population,
		utc_offset,
		rang_longitude,
		-- 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 de longitude sur deux
		row_number() OVER zone_latitude as rang_latitude,
		last_value(rang_longitude) OVER all_zone_longitude as nb_zone_longitude,
		count(id_destination) OVER part_by_zone_longitude as nb_destination_in_zone,
		geom
	FROM 
		santa_tracker.v_01_destination_rang_longitude
	WINDOW 
		zone_latitude AS (
			PARTITION BY 
				rang_longitude
			ORDER BY 
				rang_longitude,
				CASE 
					WHEN rang_longitude % 2 = 0 THEN ST_Y(geom)
					ELSE -ST_Y(geom)
				END
			ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
		),
		part_by_zone_longitude AS (
			PARTITION BY rang_longitude
			ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
		),
		all_zone_longitude AS (
			ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
		)

)
;

Consolidation des destinations

Pour chaque destination, on va calculer plusieurs valeurs, notamment des cumuls par rapport aux destination précédentes.

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

DROP VIEW IF EXISTS santa_tracker.v_03_destination CASCADE;

CREATE OR REPLACE VIEW santa_tracker.v_03_destination as (

	SELECT 
		id_destination,
		region,
		ville,
		-- Le fenêtrage permet de récupérer la prochaine destination
		first_value(id_destination) OVER next_etape as id_destination_next,
		first_value(region) OVER next_etape as region_next,
		first_value(ville) OVER next_etape as 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 / sum(population) OVER ()) 
				* var.pop_total::NUMERIC
			),
			0
		) as pop_fictive,
		utc_offset,
		rang_longitude,
		rang_latitude,
		-- Rang de l'étape
		row_number() OVER all_etape as rang_etape,
		nb_zone_longitude,
		nb_destination_in_zone,
		-- Temps de passage dans chaque zone
		(
			(var.fin_distrib - var.debut_distrib) 
			/ nb_zone_longitude 
		) AS duree_zone,
		-- Temps de passage dans chaque destination (distribution + trajet pour la suivante)
		-- Durée totale / nb zone longitude = durée par zone
		-- Durée par zone / nb étape dans zone = temps par étape
		-- Pour la dernière zone, on retire le temps du dernier trajet (retour à la maison du Père Noël)
		CASE
			WHEN rang_longitude = nb_zone_longitude THEN 
				(
					(var.fin_distrib - var.debut_distrib) 
					/ nb_zone_longitude 
				)
				/ (
					((nb_destination_in_zone - 1) * (var.pourcent_temps_de_vol/100))
					+ (nb_destination_in_zone * ((100 - var.pourcent_temps_de_vol)/100))
				)
			ELSE 
				(
					(var.fin_distrib - var.debut_distrib) 
					/ nb_zone_longitude 
				)
				/ nb_destination_in_zone
		END as duree_destination,
		-- On reprojete les données pour travailler en coordonnées projetées
		ST_Transform(geom, 3857) as geom,
		-- Le fenêtrage permet de récupérer la géométrie de la prochaine destination
		ST_Transform(
			first_value(geom) OVER next_etape,
			3857
		) as geom_next
	FROM 
		santa_tracker.v_02_destination_rang_latitude
	CROSS JOIN 
		santa_tracker.v_00_variable as var
	WINDOW 
		all_etape AS (
			ORDER BY 
				rang_longitude, 
				rang_latitude
		),
		next_etape AS (
			ORDER BY
				rang_longitude, 
				rang_latitude
			ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
		)

)
;

É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.

DROP VIEW IF EXISTS santa_tracker.v_04_etape_distribution CASCADE;

CREATE OR REPLACE VIEW santa_tracker.v_04_etape_distribution as (

	SELECT 
		id_destination,
		region,
		ville,
		-- Le fenêtrage permet de récupérer la prochaine destination
		-- S'il n'y a pas de prochaine destination, alors on retourne chez le Père Noël
		COALESCE(
			first_value(id_destination) OVER next_etape,
			(maison_pere_noel ->> 'id')::int
		) as id_destination_next,
		COALESCE(
			first_value(region) OVER next_etape,
			var.maison_pere_noel ->> 'region'
		) as region_next,
		COALESCE(
			first_value(ville) OVER next_etape,
			var.maison_pere_noel ->> 'ville'
		) as 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_total::NUMERIC
				/ sum(population) OVER ()
			),
			0
		) as pop_fictive,
		utc_offset,
		COALESCE(
			first_value(utc_offset) OVER next_etape,
			(var.maison_pere_noel ->> 'utc_offset')::interval
		) as utc_offset_next,
		rang_longitude,
		rang_latitude,
		rang_etape,
		nb_zone_longitude,
		nb_destination_in_zone,
		duree_destination,
		(1 - (var.pourcent_temps_de_vol / 100)) * (duree_destination) AS duree_etape,
		-- Le fenêtrage permet de récupérer l'heure de début de l'étape
		var.debut_distrib + 
		coalesce(
			sum(duree_destination) OVER all_preceding_step,
			'0'::interval
		)
		as date_debut,
		-- Le fenêtrage permet de récupérer l'heure de fin de l'étape
		var.debut_distrib 
		+ (1 - (var.pourcent_temps_de_vol / 100)) * (duree_destination) 
		+ coalesce(
			sum(duree_destination) OVER all_preceding_step,
			'0'::interval
		)
		as date_fin,
		-- Trajet effectué (ici ce sera un point)
		-- On utilise une géométrie de type ligne pour homogénéïser les types de géométrie avec les trajets
		ST_MakeLine(geom, geom) as geom,
		geom AS geom_etape,
		-- Le fenêtrage permet de récupérer la géométrie de la prochaine destination
		-- S'il n'y a pas de prochaine destination, alors on retourne chez le Père Noël
		ST_Transform(
			COALESCE(
				first_value(geom) OVER next_etape,
				(var.maison_pere_noel ->> 'geom_4326')::geometry(point,4326)
			),
			3857
		) as geom_etape_next
	FROM 
		santa_tracker.v_03_destination
	CROSS JOIN 
		santa_tracker.v_00_variable as var
	WINDOW 
		next_etape AS (
			ORDER BY rang_etape
			ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
		),
		all_preceding_step AS (
			ORDER BY rang_etape
			ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
		)

)
;

É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.

DROP VIEW IF EXISTS santa_tracker.v_05_trajet CASCADE;

CREATE OR REPLACE VIEW santa_tracker.v_05_trajet as (

	SELECT 
		id_destination,
		region,
		ville,
		id_destination_next,
		region_next,
		ville_next,
		-- Il n'y a aucune population lors du vol
		0 AS population,
		0 AS pop_fictive,
		utc_offset,
		utc_offset_next,
		rang_longitude,
		rang_latitude,
		rang_etape,
		nb_zone_longitude,
		nb_destination_in_zone,
		duree_destination,
		-- La durée du trajet est égale à la durée totale de la destination moins la durée de l'étape
		duree_destination - duree_etape AS duree_trajet,
		-- La date de début du trajet est identique à la date de fin de l'étape
		date_fin AS date_debut,
		-- La date de fin correspond à la date de début de l'étape + la durée sur la destination
		date_debut + duree_destination AS date_fin,
		-- Trajet effectué
		ST_MakeLine(geom_etape, geom_etape_next) as geom
	FROM 
		santa_tracker.v_04_etape_distribution
	CROSS JOIN 
		santa_tracker.v_00_variable as var
	-- On ne calcul pas de trajet pour le retour à la maison du Père Noël
	WHERE 
		id_destination_next <> 0

)
;

Construction du voyage

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

• Le trajet de la maison du Père Noël jusqu’à la première destination (créé dans la requête).
• Les étapes de distribution des cadeaux.
• Les étapes de trajet entre destinations.
• Le trajet de dernière destination jusqu’à la la maison du Père Noël (créé dans la requête).

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…)

DROP VIEW IF EXISTS santa_tracker.v_06_voyage CASCADE;

CREATE OR REPLACE VIEW santa_tracker.v_06_voyage as (

WITH 
	all_steps AS (
		-- Trajet de la maison du Père Noël à la première destination
		SELECT 
			(var.maison_pere_noel ->> 'id')::int AS id_destination,
			var.maison_pere_noel ->> 'region' AS region,
			var.maison_pere_noel ->> 'ville' AS ville,
			etp.id_destination AS id_destination_next,
			etp.region AS region_next,
			etp.ville AS ville_next,
			-- Il n'y a aucune population lors du vol
			0 AS population,
			0 AS pop_fictive,
			0 AS cadeau_distribue,
			(var.maison_pere_noel ->> 'utc_offset')::interval AS utc_offset,
			etp.utc_offset AS utc_offset_next,
			0 AS rang_longitude,
			0 AS rang_latitude,
			0 AS rang_etape,
			1 AS rang_voyage,
			'Trajet' AS type_etape,
			0 AS nb_zone_longitude,
			0 AS nb_destination_in_zone,
			-- La durée totale pour la destination est uniquement la durée du trajet (pas de distribution)
			var.delai_dest_santa_house AS duree_destination,
			-- La durée du trajet est fixée dans les variables
			var.delai_dest_santa_house AS duree_etape,
			-- La date de début du trajet est calculée par rapport aux variables
			var.debut_distrib - var.delai_dest_santa_house AS date_debut,
			-- La date de fin est fixée dans les variables
			var.debut_distrib AS date_fin,
			-- Trajet effectué
			ST_MakeLine(
				ST_Transform(
					(var.maison_pere_noel ->> 'geom_4326')::geometry(point,4326),
					3857
				),
				etp.geom_etape
			) AS geom	
		FROM 
			santa_tracker.v_00_variable AS var
		LEFT JOIN 
			santa_tracker.v_04_etape_distribution AS etp
			ON etp.rang_etape = 1
		UNION ALL 
		-- Etapes de distribution des cadeaux
		SELECT 
			id_destination,
			region,
			ville,
			id_destination_next,
			region_next,
			ville_next,
			population,
			pop_fictive,
			-- Nombre de cadeau distribué
			round( var.cadeau_par_pop * pop_fictive::numeric, 0) AS cadeau_distribue,
			utc_offset,
			utc_offset_next,
			rang_longitude,
			rang_latitude,
			rang_etape,
			2 AS rang_voyage,
			'Distribution' AS type_etape,
			nb_zone_longitude,
			nb_destination_in_zone,
			duree_destination,
			duree_etape,
			date_debut,
			date_fin,
			geom
		FROM 
			santa_tracker.v_04_etape_distribution
		CROSS JOIN 
			santa_tracker.v_00_variable as var
		UNION ALL 
		-- Trajets entre les étapes de distribution
		SELECT 
			id_destination,
			region,
			ville,
			id_destination_next,
			region_next,
			ville_next,
			population,
			pop_fictive,
			0 AS cadeau_distribue,
			utc_offset,
			utc_offset_next,
			rang_longitude,
			rang_latitude,
			rang_etape,
			2 AS rang_voyage,
			'Trajet' AS type_etape,
			nb_zone_longitude,
			nb_destination_in_zone,
			duree_destination,
			duree_trajet AS duree_etape,
			date_debut,
			date_fin,
			geom
		FROM 
			santa_tracker.v_05_trajet
		UNION ALL
		-- Trajet de la dernière destination à la maison du Père Noël
		SELECT 
			etp.id_destination AS id_destination,
			etp.region AS region,
			etp.ville AS ville,
			(var.maison_pere_noel ->> 'id')::int AS id_destination_next,
			var.maison_pere_noel ->> 'region' AS region_next,
			var.maison_pere_noel ->> 'ville' AS ville_next,
			-- Il n'y a aucune population lors du vol
			0 AS population,
			0 AS pop_fictive,
			0 AS cadeau_distribue,
			etp.utc_offset AS utc_offset,
			(var.maison_pere_noel ->> 'utc_offset')::interval AS utc_offset_next,
			0 AS rang_longitude,
			0 AS rang_latitude,
			0 AS rang_etape,
			3 AS rang_voyage,
			'Trajet' AS type_etape,
			0 AS nb_zone_longitude,
			0 AS nb_destination_in_zone,
			-- La durée totale pour la destination est uniquement la durée du trajet (pas de distribution)
			var.delai_dest_santa_house AS duree_destination,
			-- La durée du trajet est fixée dans les variables
			var.delai_dest_santa_house AS duree_etape,
			-- La date de début du trajet est calculée par rapport aux variables
			var.fin_distrib AS date_debut,
			-- La date de fin est fixée dans les variables
			var.fin_distrib + var.delai_dest_santa_house AS date_fin,
			-- Trajet effectué
			ST_MakeLine(
				etp.geom_etape,
				ST_Transform(
					(var.maison_pere_noel ->> 'geom_4326')::geometry(point,4326),
					3857
				)
			) AS geom	
		FROM 
			santa_tracker.v_00_variable AS var
		LEFT JOIN 
			santa_tracker.v_04_etape_distribution AS etp
			ON etp.rang_etape = 440
	)
	SELECT 
		concat_ws(
			'-',
			rang_voyage::text,
			rang_longitude::text,
			rang_latitude::text,
			CASE
				WHEN type_etape = 'Distribution' THEN 'd'
				ELSE 't'
			END
		) AS id_voyage,
		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 all_preceding_step,
			0
		) as pop_fictive_cumul,
		cadeau_distribue,
		-- Le fenêtrage permet de récupérer le cumul de population visitée
		coalesce(
			sum(cadeau_distribue) OVER all_preceding_step,
			0
		) as cadeau_distribue_cumul,
		utc_offset,
		utc_offset_next,
		rang_longitude,
		rang_latitude,
		rang_etape,
		rang_voyage,
		type_etape,
		nb_zone_longitude,
		nb_destination_in_zone,
		duree_destination,
		duree_etape,
		-- Le fenêtrage permet de récupérer le cumul de temps de voyage
		coalesce(
			sum(duree_etape) OVER all_preceding_step,
			'0 second'::interval
		) as duree_etape_cumul,
		date_debut,
		date_fin,
		-- Distance parcourue durant le trajet
		round(
			ST_Length(geom)::NUMERIC,
			0
		) as distance_to_next,
		-- Le fenêtrage permet de récupérer le cumul de distance parcourue
		coalesce(
			round(
				(sum(ST_Length(geom)) OVER all_preceding_step)::NUMERIC,
				0
			),
			0
		) as distance_cumul,
		-- Azimuth du symbole sur la carte
		Degrees(ST_Azimuth(ST_StartPoint(geom),ST_EndPoint(geom))) as azimuth,
		-- Orientation du symbole sur la carte
		CASE 
			WHEN Degrees(ST_Azimuth(ST_StartPoint(geom),ST_EndPoint(geom))) < 180 THEN 'Est' 
			ELSE 'Ouest'
		END as orientation,
		geom::geometry(linestring,3857)
	FROM 
		all_steps
	WINDOW 
		all_preceding_step AS (
			ORDER BY 
				rang_voyage,
				rang_etape,
				type_etape
			ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
		)

)
;

On fixe ensuite les données du voyage dans une vue matérialisée pour améliorer les performance lors de la consultation :

DROP MATERIALIZED VIEW IF EXISTS santa_tracker.vm_06_voyage CASCADE;

CREATE MATERIALIZED VIEW santa_tracker.vm_06_voyage as (
	SELECT * 
	FROM santa_tracker.v_06_voyage
)
WITH DATA
;

L’ »instant t »

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

Données

Nous allons créer une nouvelle vue qui va se baser sur la vue matérialisée vm_06_voyage pour calculer la position du Père Noël et divers les paramètres au moment de la consultation de cette vue.

Cette vue définies trois grands temps dans l’année :

• Du 1er janvier au 24 décembre : le Père Noël se trouve chez lui et fabrique les cadeaux
• Du 24 au 25 décembre : le Père Noël distribue les cadeaux selon les données présentes dans la vue matérialisée vm_06_voyage.
• A partir du 25 décembre : le Père Noël se repose chez lui après tout ce travail.

DROP VIEW IF EXISTS santa_tracker.v_instant_t CASCADE;

CREATE OR REPLACE VIEW santa_tracker.v_instant_t as (

WITH 
	voyage_instant_t as (
		-- Etape de préparation des cadeaux
		-- Cette étape sera visible du 1er janvier au début de la tournée
		SELECT 
			'Préparatifs' as type_trajet,
			'0-0-0-p' AS id_voyage,
			(var.maison_pere_noel ->> 'id')::int AS id_destination,
			NULL AS id_destination_next,
			var.maison_pere_noel ->> 'region' AS region,
			NULL as region_next,
			var.maison_pere_noel ->> 'ville' AS ville,
			NULL as ville_next,
			(var.maison_pere_noel ->> 'population')::integer AS population,
			NULL as pop_fictive,
			NULL as pop_fictive_cumul,
			-- Nombre total de cadeau total à fabriquer
			(var.pop_total * var.cadeau_par_pop) as cadeau_distribue,
			0 as cadeau_distribue_cumul,
			(var.maison_pere_noel ->> 'utc_offset')::interval AS utc_offset,
			NULL AS utc_offset_next,
			((extract(year from now()) || '-01-01 00:00:00 +01')::timestamp with time ZONE - var.debut_distrib)::interval as duree_etape,
			'0 second'::interval as duree_etape_cumul,
			-- Début de fabrication le 1er janvier (fuseau horaire du Père Noël)
			(extract(year from now()) || '-01-01 00:00:00 +01')::timestamp with time zone as date_debut,
			-- Fin de fabrication lors du début de la distribution
			var.debut_distrib as date_fin,
			-- Calcul du pourcentage d'avancement sur l'étape
			(
				(
					extract('epoch' from var."now")
					- extract('epoch' from ((extract(year from now()) || '-01-01 00:00:00 +01')::timestamp with time zone))
				) / (
					extract('epoch' from var.debut_distrib)
					- extract('epoch' from ((extract(year from now()) || '-01-01 00:00:00 +01')::timestamp with time zone))
				)
			)::NUMERIC as avancement,
			NULL as distance_to_next,
			NULL as distance_cumul,
			0 AS azimuth,
			NULL AS orientation,
			ST_MakeLine(
				ST_Transform(
					(var.maison_pere_noel ->> 'geom_4326')::geometry(point,4326), 
					3857
				),
				ST_Transform(
					(var.maison_pere_noel ->> 'geom_4326')::geometry(point,4326),
					3857
				)
			) as geom 
		FROM 
			santa_tracker.v_00_variable as var
		WHERE
			var."now" < (var.debut_distrib - var.delai_dest_santa_house)
		UNION ALL
		-- Récupération des étapes du voyage
		SELECT 
			type_etape,
			id_voyage,
			id_destination,
			id_destination_next,
			region,
			region_next,
			ville,
			ville_next,
			population,
			pop_fictive,
			pop_fictive_cumul,
			cadeau_distribue,
			cadeau_distribue_cumul,
			utc_offset,
			utc_offset_next,
			duree_etape,
			duree_etape_cumul,
			date_debut,
			date_fin,
			-- Calcul du pourcentage d'avancement sur l'étape
			(
				(
					extract('epoch' from var."now")
					- extract('epoch' from date_debut)
				) / (
					extract('epoch' from date_fin)
					- extract('epoch' from date_debut)
				)
			)::NUMERIC as avancement,
			distance_to_next,
			distance_cumul,
			azimuth,
			orientation,
			geom 
		FROM 
			santa_tracker.vm_06_voyage as t
		CROSS JOIN 
			santa_tracker.v_00_variable as var
		WHERE 
			var."now" >= date_debut
		AND 
			var."now" < date_fin
		UNION ALL
		-- Ajout d'une étape de repos
		-- Cette étape sera visible après la fin de la tournée 
		SELECT 
			'Repos' as type_trajet,
			'0-0-0-r' AS id_voyage,
			(var.maison_pere_noel ->> 'id')::int AS id_destination,
			NULL AS id_destination_next,
			var.maison_pere_noel ->> 'region' AS region,
			NULL as region_next,
			var.maison_pere_noel ->> 'ville' AS ville,
			NULL as ville_next,
			(var.maison_pere_noel ->> 'population')::integer AS population,
			NULL as pop_fictive,
			var.pop_total as pop_fictive_cumul,
			(var.pop_total * var.cadeau_par_pop) as cadeau_distribue,
			(var.pop_total * var.cadeau_par_pop) as cadeau_distribue_cumul,
			(var.maison_pere_noel ->> 'utc_offset')::interval AS utc_offset,
			NULL AS utc_offset_next,
			((extract(year from now()) || '-12-31 23:59:59 +01')::timestamp with time ZONE - var.fin_distrib)::interval as duree_etape,
			'0 second'::interval as duree_etape_cumul,
			-- Début du repos à la fin de la distribution
			var.fin_distrib as date_debut,
			-- Fin du repos le 1er janvier à 00h00 (fuseau horaire du Père Noël)
			((extract(year from now()) + 1)::text || '-01-01 00:00:00 +01')::timestamp with time zone as date_fin,
			-- Calcul du pourcentage d'avancement sur l'étape
			(
				(
					extract('epoch' from var."now")
					- extract('epoch' from var.fin_distrib)
				) / (
					extract('epoch' from var.fin_distrib)
					- extract('epoch' from ((extract(year from now()) || '-01-01 00:00:00 +01')::timestamp with time zone))
				)
			)::NUMERIC as avancement,
			NULL as distance_to_next,
			NULL as distance_cumul,
			0 AS azimuth,
			NULL AS orientation,
			ST_MakeLine(
				ST_Transform(
					(var.maison_pere_noel ->> 'geom_4326')::geometry(point,4326), 
					3857
				),
				ST_Transform(
					(var.maison_pere_noel ->> 'geom_4326')::geometry(point,4326),
					3857
				)
			) as geom 
		FROM 
			santa_tracker.v_00_variable as var
		WHERE
			var."now" > (var.fin_distrib + var.delai_dest_santa_house)
	)
SELECT 
	type_trajet,
	id_voyage,
	id_destination,
	id_destination_next,
	region,
	region_next,
	ville,
	ville_next,
	population,
	pop_fictive,
	pop_fictive_cumul,
	-- Calcul du nombre de personnes visitées au moment de la visualisation
	round(
		pop_fictive_cumul + 
		(pop_fictive * avancement)::numeric,
		0
	) as pop_fictive_cumul_actuel,
	cadeau_distribue,
	cadeau_distribue_cumul,
	-- Calcul du nombre de cadeau distribué au moment de la visualisation
	round(
		cadeau_distribue_cumul + 
		(cadeau_distribue * avancement)::numeric,
		0
	) as cadeau_cumul_actuel,
	utc_offset,
	utc_offset_next,
	duree_etape,
	date_debut,
	date_fin,
	avancement,
	distance_to_next,
	distance_cumul,
	-- Calcul de la distance parcourue au moment de la visualisation
	round(
		distance_cumul + 
		(distance_to_next * avancement)::numeric,
		0
	) as distance_cumul_actuel,
	azimuth,
	orientation,
	-- Calcul de l'emplacement au moment de la visualisation
	ST_LineInterpolatePoint(geom, avancement)::geometry(point,3857) as geom
FROM 
	voyage_instant_t

)
;

Affichage

Selon le moment de l’année, nous n’allons pas afficher les mêmes données. Dans le cadre de l’application, j’ai mis en forme les données avec beaucoup de HTML, c’est peu lisible et nous allons donc ici générer quelque chose de beaucoup plus simple mais avec les mêmes informations.

Créons une vue qui récupère les données et les mets en forme :

DROP VIEW IF EXISTS santa_tracker.v_instant_t_affichage CASCADE;

CREATE OR REPLACE VIEW santa_tracker.v_instant_t_affichage as (

SELECT 
	id_voyage,
	type_trajet,
	-- 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(
		$${
			"Préparatifs":[
				"Vive le vent d","hiver !",
				"Et un cadeau de plus !",
				"Hum ?!",
				"Quel beau camion !",
				"Ah, une belle poupée !",
				"Oh Oh Oh !"
			],
			"Trajet":[
				"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 !"
			],
			"Repos":[
				"ZZZ",
				"ZZZ ZZZ",
				"ZZZ",
				"ZZZ ZZZ",
				"ZZZ",
				"RONFFFFFFFLE"
			]
		}$$::jsonb,
		type_trajet,
		FLOOR(EXTRACT(second from transaction_timestamp()) / 10)::text
	) as parole,
	CASE 
		WHEN type_trajet = 'Préparatifs' THEN 'Le Père Noël prépare les cadeaux'
		WHEN type_trajet = 'Trajet' THEN 'Le Père Noël voyage vers un nouveau lieu de distribution'
		WHEN type_trajet = 'Distribution' THEN 'Le Père Noël est en cours de distribution'
		WHEN type_trajet = 'Repos' THEN 'Le Père Noël a terminé et prend un repos bien mérité'
	END AS titre,
	ville || ' (' || region || ')' AS ville,
	ville_next || ' (' || region_next || ')' AS ville_next,
	concat(
		NULLIF(extract(day from (date_fin - var."now")), 0) || ' jours, ',
		NULLIF(extract(hour from (date_fin - var."now")), 0) || ' heures, ',
		NULLIF(extract(minute from (date_fin - var."now")), 0) || ' minutes et ',
		NULLIF(round(extract(second from (date_fin - var."now"))::numeric, 0), 0) || ' secondes'
	) AS timer,
	to_char(date_debut,'DD TMmon YYYY') AS date_debut,
	to_char(date_debut,'HH24:MI:SS') AS heure_debut,
	to_char(date_debut AT TIME ZONE utc_offset,'DD TMmon YYYY') AS date_debut_local,
	to_char(date_debut AT TIME ZONE utc_offset,'HH24:MI:SS') AS heure_debut_local,
	to_char(date_fin,'DD TMmon YYYY') AS date_fin,
	to_char(date_fin,'HH24:MI:SS') AS heure_fin,
	to_char(date_fin AT TIME ZONE utc_offset,'DD TMmon YYYY') AS date_fin_local,
	to_char(date_fin AT TIME ZONE utc_offset,'HH24:MI:SS') AS heure_fin_local,
	trim(to_char(cadeau_cumul_actuel, '999 999 999 999')) AS cadeau,
	trim(to_char(pop_fictive_cumul_actuel, '999 999 999 999')) AS population,
	trim(to_char(distance_cumul_actuel/1000, '999 999 999 999 km')) AS distance
FROM
	santa_tracker.v_instant_t
CROSS JOIN
	santa_tracker.v_00_variable as var 
	
)
;

Il est maintenant possible d’appeler la vue avec un SELECT qui finalise la mise en forme. N’hésitez pas à utiliser du HTML.

SELECT 
	id_voyage,
	CASE 
		WHEN type_trajet = 'Préparatifs' THEN 
			concat_ws(
				chr(10),
				titre,
				'Petit mot du Père Noël : ' || parole,
				'Emplacement actuel : ' || ville ,
				'Départ prévu dans ' || timer,
				'	le ' || date_fin || ' à ' || heure_fin,
				'Cadeaux fabriqués : ' || cadeau
			)
		WHEN type_trajet = 'Trajet' THEN
			concat_ws(
				chr(10),
				titre,
				'Petit mot du Père Noël : ' || parole,
				'Décollage : ' || ville ,
				'	le ' || date_debut || ' à ' || heure_debut,
				'	(' || date_debut_local || ' à ' || heure_debut_local || ' heure locale)',
				'Arrivée prévue dans ' || timer,
				'Atterissage : ' || ville_next ,
				'	le ' || date_fin || ' à ' || heure_fin,
				'	(' || date_fin_local || ' à ' || heure_fin_local || ' heure locale)',
				'Cadeaux distribués : ' || cadeau,
				'Population visitée : ' || population,
				'Distance parcourue : ' || distance
			)
		WHEN type_trajet = 'Distribution' THEN
			concat_ws(
				chr(10),
				titre,
				'Petit mot du Père Noël : ' || parole,
				'Emplacement actuel : ' || ville ,
				'Arrivé sur place le ' || date_debut || ' à ' || heure_debut,
				'	(' || date_debut_local || ' à ' || heure_debut_local || ' heure locale)',
				'Prochain départ le ' || date_fin || ' à ' || heure_fin,
				'	(' || date_fin_local || ' à ' || heure_fin_local || ' heure locale)',
				'Dans ' || timer,
				'Prochaine destination : ' || ville_next ,
				'Cadeaux distribués : ' || cadeau,
				'Population visitée : ' || population,
				'Distance parcourue : ' || distance
			)
		WHEN type_trajet = 'Repos' THEN
			concat_ws(
				chr(10),
				titre,
				'Petit mot du Père Noël : ' || parole,
				'Emplacement actuel : ' || ville ,
				'Prochaine fabrication de cadeau dans ' || timer,
				'Le ' || date_fin || ' à ' || heure_fin,
				'Cadeaux distribués : ' || cadeau,
				'Population visitée : ' || population
			)
	END as informations
FROM
	santa_tracker.v_instant_t_affichage
;

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_06_voyage.

-- Vue contenant toutes les destinations
DROP VIEW IF EXISTS santa_tracker.v_destination CASCADE;

CREATE OR REPLACE VIEW santa_tracker.v_destination AS 

WITH 
	all_destinations AS (
		-- Récupération de toutes les destinations
		SELECT 
			id_destination,
			region,
			ville,
			population,
			utc_offset,
			geom
		FROM 
			santa_tracker.destination
		UNION
		-- Ajout de la maison du Père Noël
		SELECT 
			(maison_pere_noel ->> 'id')::integer AS id_destination,
			maison_pere_noel ->> 'region' AS region,
			maison_pere_noel ->> 'ville' AS ville,
			(maison_pere_noel ->> 'population')::integer AS population,
			(maison_pere_noel ->> 'utc_offset')::interval AS utc_offset,
			(maison_pere_noel ->> 'geom_4326')::geometry(Point,4326) AS geom
		FROM 
			santa_tracker.v_00_variable
	)
	SELECT 
		t1.id_destination,
		t1.region,
		t1.ville,
		t1.population,
		t1.utc_offset,
		-- Génération d'un message d'information sur l'état de passage
		CASE 
			-- S'il n'y a pas de date de début (pas de distribution) alors il s'agit de la maison du Père Noël
			WHEN voy.date_debut IS NULL THEN 
				CASE 
					WHEN var."now" < var.debut_distrib THEN 'Le Père Noël prépare les cadeaux'
					WHEN var."now" < var.fin_distrib 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" > voy.date_fin  THEN 'A reçu la visite du Père Noël'
			WHEN var."now" > voy.date_debut  THEN 'Le Père Noël est ici, Youpi !'
			ELSE 'Attend la visite du Père Noël'
		END AS informations,
		t1.geom
	FROM 
		all_destinations AS t1
	CROSS JOIN 
		santa_tracker.v_00_variable AS var
	LEFT JOIN 
		santa_tracker.vm_06_voyage as voy
		ON voy.id_destination = t1.id_destination
		AND voy.type_etape = 'Distribution'
;

---

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