gis Database

Parameters

Name	Type	Mode

Definition

DECLARE
    table_canalisation text;
BEGIN

IF ( TG_OP = 'UPDATE' AND NOT ST_Equals(NEW.geom, OLD.geom) ) OR TG_OP = 'INSERT' THEN

-- Modification des idnini et idnterm des canalisations qui touchent aux extrémités
        table_canalisation = 'raepa.raepa_canalass_l';
        IF TG_TABLE_NAME = 'raepa_ouvraep_p' THEN
            table_canalisation = 'raepa.raepa_canalaep_l';
        END IF;

-- idnini
        EXECUTE format('
        UPDATE %s AS ca
        SET idnini = ''%s''
        WHERE idnini != ''%s''
        AND ST_DWithin(ST_StartPoint(ca.geom), ''%s''::geometry, 0.05)
        ', table_canalisation,
        NEW.idouvrage, NEW.idouvrage,
        NEW.geom
        )
        ;
        -- idnterm
        EXECUTE format('
        UPDATE %s ca
        SET idnterm = ''%s''
        WHERE idnterm != ''%s''
        AND ST_DWithin(ST_EndPoint(ca.geom), ''%s''::geometry, 0.05)
        ', table_canalisation,
        NEW.idouvrage, NEW.idouvrage,
        NEW.geom
        )
        ;

-- Modification des géométries des canalisations amont et aval liées par idnini et idnterm
        -- Cela permet de faire toucher les canalisations à l'ouvrage
        -- Canalisation dont l'ouvrage est à l'aval
        EXECUTE format('
        WITH
        aval AS (
            SELECT
            c.idcana,
            z.path[1] AS node_id, z.geom AS node_geom
            FROM %s AS c, ST_DumpPoints(geom) AS z
            WHERE c.idnterm = ''%s''
        )
        UPDATE %s c
        SET
            geom = foo.geom
        FROM (
            SELECT aval.idcana AS cid,
            ST_MakeLine(
                (array_agg(node_geom ORDER BY node_id))[1:count(node_id)-1] || ARRAY[''%s''::geometry]
            ) AS geom
            FROM aval
            GROUP BY aval.idcana
        ) AS foo
        WHERE c.idcana = foo.cid AND c.idnterm = ''%s'' AND NOT ST_Intersects(ST_EndPoint(c.geom), ''%s''::geometry)
        ',
        table_canalisation, NEW.idouvrage,
        table_canalisation, NEW.geom,
        NEW.idouvrage, NEW.geom
        );

-- Canalisation dont l'ouvrage est à l'amont
        EXECUTE format('
        WITH
        amont AS (
            SELECT
            c.idcana,
            z.path[1] AS node_id, z.geom AS node_geom
            FROM %s AS c, ST_DumpPoints(geom) AS z
            WHERE c.idnini = ''%s''
        )
        UPDATE %s c
        SET geom = foo.geom
        FROM (
            SELECT amont.idcana AS cid,
            ST_MakeLine(
                ARRAY[''%s''::geometry] || (array_agg(node_geom ORDER BY node_id))[2:count(node_id)]
            ) AS geom
            FROM amont
            GROUP BY amont.idcana
        ) AS foo
        WHERE c.idcana = foo.cid AND c.idnini = ''%s'' AND NOT ST_Intersects(ST_StartPoint(c.geom), ''%s''::geometry)
        ',
        table_canalisation, NEW.idouvrage,
        table_canalisation, NEW.geom,
        NEW.idouvrage, NEW.geom
        );

END IF;

RETURN NEW;
END;

trg_apres_modification_ouvrage

Parameters

Definition