gis
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
get_network_to_vanne_ferme_from_point
Parameters
Name
Type
Mode
cana_id
text
IN
locate_point
double precision
IN
Definition
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;