gis
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
get_network_to_vanne_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, true) into locate_before_point; SELECT raepa.get_vanne_after_point(cana_id, locate_point, true) 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) AS ( SELECT idcana::text, array[idcana::text] as all_parents, ST_LineSubstring(geom, CASE WHEN raepa.get_vanne_before_point(cana_id, locate_point, true) IS NULL THEN 0 ELSE raepa.get_vanne_before_point(cana_id, locate_point, true) END, CASE WHEN raepa.get_vanne_after_point(cana_id, locate_point, true) IS NULL THEN 1 ELSE raepa.get_vanne_after_point(cana_id, locate_point, true) END) as geom, False as stop 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, true) )::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, true) )::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, true), 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, true), 1 )::geometry(LineString,2154) ELSE n.geom::geometry(LineString,2154) END AS geom, a.idappareil IS NOT NULL as stop 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(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 ) SELECT ST_Union(DISTINCT geom) into parcours AS geom FROM walk_network; RETURN parcours; END;