gis Database

Parameters

Name	Type	Mode
cana	text	IN

Definition

DECLARE
  parcours public.geometry;
BEGIN

WITH RECURSIVE walk_network(idcana, all_parents, geom) AS (
      SELECT idcana::text,
      array[idcana::text] as all_parents,
      geom
      FROM raepa.raepa_canalaep_l
      WHERE idcana = cana --sans vanne
    UNION ALL
      SELECT
         n.idcana::text AS idcana,
    w.all_parents || n.idcana::text AS all_parents,
    CASE
      WHEN  a.idappareil IS NOT NULL AND ST_Dwithin(st_startpoint(w.geom), st_endpoint(n.geom), 0.05) THEN ST_Line_Substring( n.geom, raepa.get_vanne_cana(n.idcana, false), 1 )::geometry(LineString,2154)
      WHEN  a.idappareil IS NOT NULL AND ST_Dwithin(st_startpoint(n.geom), st_endpoint(w.geom), 0.05) THEN ST_Line_Substring( n.geom, 0, raepa.get_vanne_cana(n.idcana, true) )::geometry(LineString,2154)
      ELSE n.geom::geometry(LineString,2154) END AS geom
      FROM raepa.raepa_canalaep_l AS n
        LEFT OUTER JOIN raepa.raepa_apparaep_p a ON ( fnappaep = '03' AND ST_Dwithin(n.geom, a.geom, 0.05) ),
        walk_network AS w
      WHERE TRUE
      AND (ST_Dwithin(st_startpoint(w.geom), st_endpoint(n.geom), 0.05) OR ST_Dwithin(st_startpoint(n.geom), st_endpoint(w.geom), 0.05))
      AND n.idcana <> ALL (w.all_parents)
)
SELECT ST_Union(DISTINCT geom)  INTO parcours AS geom FROM walk_network;

RETURN parcours;
END;

network_to_vanne

Description

Parameters

Definition