gis Database

Parameters

Name	Type	Mode
p_id_object	text	IN

Definition

DECLARE
  p_cana_table regclass;
  get_cana text[];
  p_cana_id text;
  sql_text text;
  parcours public.geometry;
BEGIN

-- Get canalisation id
  SELECT raepa.get_canalisation_id_by_object_id(p_id_object, 'up')
  INTO get_cana;
  p_cana_id = get_cana[1];
  p_cana_table = get_cana[2];
  IF p_cana_id = '' THEN
    RETURN NULL;
  END IF;
  -- RAISE NOTICE 'Canalisation d''origine = %s', p_cana_id;

-- Build geometry
  EXECUTE format('
    WITH RECURSIVE walk_network(idcana, idnini, idnterm, all_parents, geom) AS (
      SELECT c.idcana::text,
      c.idnini, c.idnterm,
      array[c.idcana::text] as all_parents,
      c.geom
      FROM %s AS c
      JOIN raepa.get_canalisation_ids_by_object_id(''%s'', ''up'') AS s
          ON c.idcana = s.idcana
    UNION ALL
      SELECT n.idcana::text,
      n.idnini, n.idnterm,
      w.all_parents || n.idcana::text,
      n.geom
      FROM %s AS n, walk_network AS w
      WHERE TRUE
      AND ST_Dwithin(st_startpoint(w.geom), st_endpoint(n.geom), 0.05)
      AND n.idcana <> ALL (w.all_parents)
    )
    SELECT ST_Union(DISTINCT r.geom) AS geom
    FROM walk_network wn
    INNER JOIN %s r
    ON wn.idcana = r.idcana
    ',
    p_cana_table, p_id_object,
    p_cana_table,
    p_cana_table
  ) INTO parcours;

RETURN parcours;
END;

upstream_by_geom

Parameters

Definition