get_network_to_vanne_ferme_from_point

DECLARE

    locate_before_point double precision = 0;

    locate_after_point double precision = 1;

    parcours public.geometry;

BEGIN

    SELECT raepa.get_vanne_before_point(cana_id, locate_point, false) into locate_before_point;

    SELECT raepa.get_vanne_after_point(cana_id, locate_point, false) into locate_after_point;

​

    IF locate_before_point IS NOT NULL AND locate_after_point IS NOT NULL THEN

        SELECT ST_LineSubstring(c.geom, locate_before_point, locate_after_point) into parcours AS geom FROM raepa.raepa_canalaep_l AS c WHERE c.idcana = cana_id;

        RETURN parcours;

    END IF;

​

    WITH RECURSIVE walk_network(idcana, all_parents, geom, stop, step) AS (

    SELECT idcana::text,

          array[idcana::text] as all_parents,

          ST_LineSubstring(geom,

        CASE WHEN raepa.get_vanne_before_point(cana_id, locate_point, false) IS NULL THEN 0 ELSE raepa.get_vanne_before_point(cana_id, locate_point, false) END,

        CASE WHEN raepa.get_vanne_after_point(cana_id, locate_point, false) IS NULL THEN 1 ELSE raepa.get_vanne_after_point(cana_id, locate_point, false) END) as geom,

          False as stop,

          0 as step

     FROM raepa.raepa_canalaep_l

     WHERE idcana = cana_id

      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(n.geom), st_endpoint(w.geom), 0.05) THEN ST_LineSubstring( n.geom, 0, raepa.get_vanne_after_point(n.idcana, 0, false) )::geometry(LineString,2154)

        WHEN  a.idappareil IS NOT NULL AND ST_Dwithin(st_startpoint(n.geom), st_startpoint(w.geom), 0.05) THEN ST_LineSubstring( n.geom, 0, raepa.get_vanne_after_point(n.idcana, 0, false) )::geometry(LineString,2154)

        WHEN  a.idappareil IS NOT NULL AND ST_Dwithin(st_endpoint(n.geom), st_startpoint(w.geom), 0.05) THEN ST_LineSubstring( n.geom, raepa.get_vanne_before_point(n.idcana, 1, false), 1 )::geometry(LineString,2154)

        WHEN  a.idappareil IS NOT NULL AND ST_Dwithin(st_endpoint(n.geom), st_endpoint(w.geom), 0.05) THEN ST_LineSubstring( n.geom, raepa.get_vanne_before_point(n.idcana, 1, false), 1 )::geometry(LineString,2154)

        ELSE n.geom::geometry(LineString,2154) END AS geom,

        a.idappareil IS NOT NULL OR o.idouvrage IS NOT NULL as stop,

        w.step + 1 as step

          FROM raepa.raepa_canalaep_l AS n

          LEFT OUTER JOIN raepa.raepa_apparaep_p a ON ( fnappaep = '03' AND _ferme AND ST_Dwithin(n.geom, a.geom, 0.05) )

          LEFT OUTER JOIN raepa.raepa_ouvraep_p o ON (

                (fnouvaep = '03' OR fnouvaep = '05' OR fnouvaep = '01')

                AND

                ST_Dwithin(n.geom, o.geom, 0.05)

            ),

             walk_network AS w

          WHERE TRUE AND (

               ST_Dwithin(st_startpoint(n.geom), st_endpoint(w.geom), 0.05) OR

               ST_Dwithin(st_startpoint(n.geom), st_startpoint(w.geom), 0.05) OR

               ST_Dwithin(st_endpoint(n.geom), st_startpoint(w.geom), 0.05) OR

               ST_Dwithin(st_endpoint(n.geom), st_endpoint(w.geom), 0.05))

          AND w.idcana <> n.idcana AND n.idcana <> ALL (w.all_parents)

          AND NOT w.stop

          AND w.step < 15

    )

    SELECT ST_Union(DISTINCT geom) into parcours AS geom FROM walk_network;

    RETURN parcours;

END;

​