gis
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
get_canalisation_ids_by_object_id
Parameters
Name
Type
Mode
p_id_object
text
IN
p_direction
text
IN
idcana
text
OUT
tablecana
text
OUT
Definition
DECLARE p_cana_id text; p_cana_table regclass; p_object_table regclass; p_object_id_column text; p_node_col text; p_function text; sql_text text; parcours public.geometry; BEGIN -- Get target canalisation table IF p_id_object similar to '%(ouvass|appass|canass)%' THEN p_cana_table:= 'raepa.raepa_canalass_l'; ELSE p_cana_table:= 'raepa.raepa_canalaep_l'; END IF; -- Get first canalisation depending on object id given -- ouvrage p_cana_id = ''; -- ouvrage / appareil IF p_id_object similar to '%(ouvass|ouvaep|appass|appaep)%' THEN -- Get source object table IF p_id_object LIKE '%ouvass%' THEN p_object_table:= 'raepa.raepa_ouvrass_p'; p_object_id_column:= 'idouvrage'; ELSEIF p_id_object LIKE '%ouvaep%' THEN p_object_table:= 'raepa.raepa_ouvraep_p'; p_object_id_column:= 'idouvrage'; ELSEIF p_id_object LIKE '%appass%' THEN p_object_table:= 'raepa.raepa_apparass_p'; p_object_id_column:= 'idappareil'; ELSEIF p_id_object LIKE '%appaep%' THEN p_object_table:= 'raepa.raepa_apparaep_p'; p_object_id_column:= 'idappareil'; END IF; -- Some parameters depend on given direction IF p_direction = 'up' THEN p_node_col = 'idnterm'; p_function = 'st_endpoint'; ELSE p_node_col = 'idnini'; p_function = 'st_startpoint'; END IF; -- Get canalisation id RETURN QUERY EXECUTE format(' SELECT idcana::text, ''%s'' AS tablecana FROM %s AS c WHERE %s = ''%s'' OR ST_Dwithin( %s(c.geom), (SELECT geom FROM %s WHERE "%s" = ''%s''), 0.05 ) ', p_cana_table, p_cana_table, p_node_col, p_id_object, p_function, p_object_table, p_object_id_column, p_id_object ); END IF; -- canalisation IF p_id_object similar to '%(canass|canaep)%' THEN p_cana_id:= p_id_object; RETURN QUERY EXECUTE format('SELECT ''%s''AS idcana, ''%s'' AS tablecana', p_cana_id, p_cana_table); END IF; END;