Le portail francophone de la géomatique

CREATE TABLE work.tia
(
  code_iti character varying, -- Code de l'itinéraire
  arret_deb character varying, -- Code de l'arrêt de début de tronçon
  arret_fin character varying, -- Code de l'arrêt de fin de tronçon
  arret_fin_plus character varying, -- Code de l'arrêt suivant l'arrêt de fin de tronçon
  ordre integer, -- Ordre d'apparition du tronçon dans l'itinéraire
  terminus integer, -- Egal à 1 si le tronçon est le dernier tronçon de l'itinéraire, sinon 0
  pos_deb numeric(7,6), -- Position sur l'itinéraire de l'arrêt de début du tronçon
  pos_fin numeric(7,6), -- Position sur l'itinéraire de l'arrêt de fin de tronçon
  pos_fin_plus numeric(7,6), -- Position, sur l'itinéraire, de l'arrêt qui suit l'arrêt de fin de tronçon
  length_iti numeric(13,6), -- Longueur de l'itinéraire
  length_deb_fin_plus numeric(13,6), -- Longueur du tronçon allant de l'arrêt de début à l'arrêt qui suit l'arrêt de fin de tronçon
  length_deb_fin_iti numeric(13,6), -- Longueur du tronçon allant de l'arrêt de début à la fin de l'itinéraire
  the_geom geometry(LineString,2154) -- Géométrie des tronçons
)
WITH (
  OIDS=FALSE
);
ALTER TABLE work.tia
  OWNER TO postgres;

-- FONCTION maj_tia() - Recherche des positions des arrêts deb, fin et fin + 1  sur l'itinéraire
CREATE OR REPLACE FUNCTION work.maj_tia() RETURNS integer AS $BODY$
 DECLARE
  i integer := 0;
  
 BEGIN
  -- Recherche du nombre maximum de tronçon
  EXECUTE 'SELECT max(ordre) from work.tia' INTO i;
  
  -- Pour le dernier tronçon, il n'existe pas d'arrêt fin de tronçon +1 => La position de [arrêt de début + 2] est la fin de l'itinéraire soit 1
  UPDATE work.tia
  SET pos_fin_plus= 1
  WHERE work.tia.terminus = 1;

  FOR j IN 1..i LOOP   
   -- Recherche des positions de [arrêt de début] : Récupération de la position de l'arrêt de fin de tronçon du tronçon précédent
   UPDATE work.tia 
   SET pos_deb = tia_moins.pos_fin
   FROM work.tia as tia_moins
   WHERE 
    tia_moins.code_iti = work.tia.code_iti
    AND tia_moins.ordre = work.tia.ordre -1
    AND tia_moins.arret_fin = work.tia.arret_deb
    AND work.tia.ordre =j;
    
   -- Dans le cas du 1er tronçon ou quand la position de l'arrêt de fin de tronçon du tronçon précédent est inconnue => Calcul de la position en se basant sur l'itinéraire en entier
   UPDATE work.tia
   SET pos_deb = st_Line_Locate_Point(carto.itineraire.the_geom,deb.the_geom)
   FROM carto.itineraire, cimm.arret as deb
   WHERE 
    carto.itineraire.code_iti = work.tia.code_iti 
    AND deb.code_arret = work.tia.arret_deb
    AND work.tia.pos_deb IS NULL
    AND work.tia.ordre =j;
  
   -- Calcul de la longueur du tronçon [[arrêt début] -> [fin de l'itinéraire]]
   UPDATE work.tia
   SET
    length_deb_fin_iti = st_length(st_Line_SubString(carto.itineraire.the_geom, work.tia.pos_deb, 1))
   FROM carto.itineraire
   WHERE 
    carto.itineraire.code_iti = work.tia.code_iti 
    AND work.tia.ordre = j;
 
   -- Recherche de la position de [arrêt de début + 2]
   UPDATE work.tia 
   SET 
    pos_fin_plus = 
    WHEN fin_plus.the_geom is NULL
    THEN 1
    ELSE st_Line_Locate_Point(st_Line_SubString(carto.itineraire.the_geom, pos_deb, 1),fin_plus.the_geom)*length_deb_fin_iti / length_iti + pos_deb
   FROM 
    carto.itineraire,  cimm.arret as fin_plus
   WHERE
    carto.itineraire.code_iti = work.tia.code_iti 
    AND fin_plus.code_arret = work.tia.arret_fin_plus
    AND work.tia.ordre =j
    AND work.tia.terminus IS NULL
    AND pos_deb <>1;
 
   -- Calcul de la longueur du tronçon [[arrêt début] -> [arrêt début + 2]]
   UPDATE work.tia 
   SET length_deb_fin_plus = 
   CASE
    WHEN work.tia.pos_deb < work.tia.pos_fin_plus AND work.tia.pos_fin_plus <= 1
    THEN st_length(st_Line_SubString(carto.itineraire.the_geom, work.tia.pos_deb, work.tia.pos_fin_plus))
    WHEN work.tia.pos_fin_plus < work.tia.pos_deb AND work.tia.pos_deb <= 1
    THEN st_length(st_Line_SubString(carto.itineraire.the_geom, work.tia.pos_deb, 1))
   END
   FROM carto.itineraire
   WHERE 
    carto.itineraire.code_iti = work.tia.code_iti 
    AND work.tia.ordre = j;
 
   -- Recherche de la position de [arrêt fin] du tronçon sur le tronçons [[arrêt début] -> [arrêt début + 2]]
   UPDATE work.tia 
   SET pos_fin =
   CASE
    WHEN pos_deb < pos_fin_plus AND pos_fin_plus <= 1 AND GeometryType(st_Line_SubString(carto.itineraire.the_geom, pos_deb, pos_fin_plus)) = 'LINESTRING'
    THEN st_Line_Locate_Point(st_Line_SubString(carto.itineraire.the_geom, pos_deb, pos_fin_plus),fin.the_geom) *length_deb_fin_plus/ length_iti + pos_deb
    WHEN pos_fin_plus < pos_deb AND pos_deb <= 1 AND GeometryType(st_Line_SubString(carto.itineraire.the_geom, pos_deb, 1)) = 'LINESTRING'
    THEN st_Line_Locate_Point(st_Line_SubString(carto.itineraire.the_geom, pos_deb, 1),fin.the_geom) *length_deb_fin_plus/ length_iti + pos_deb
   END
   FROM carto.itineraire, cimm.arret as fin
   WHERE 
    carto.itineraire.code_iti = work.tia.code_iti 
    AND fin.code_arret = work.tia.arret_fin
    AND work.tia.ordre =j;
 
  END LOOP;    
  RETURN i;
 END
$BODY$
LANGUAGE 'plpgsql';
-- FIN de maj_tia()
 
-- Execution de maj_tia() 
select work.maj_tia();

-- Découpage des itinéraires en tronçons
UPDATE work.tia 
SET the_geom = st_Line_SubString(carto.itineraire.the_geom,pos_deb, pos_fin)
FROM carto.itineraire
WHERE 
 carto.itineraire.code_iti = work.tia.code_iti 
 pos_deb < pos_fin;