1

My trigger function consists of collecting the vertices of a polyline table "depart_bt", searching for the point entities of the table “support_bt” which coincide with the vertices of the depart_bt table, then retrieving the value of the "code_dep" attribute (unique identifier of the depart_bt table) of each entity found and report it in the "code_dep" attribute (foreign key in the support_bt table).

CREATE OR REPLACE FUNCTION vertex_troncon() RETURNS TRIGGER 
language plpgsql AS 
$$
DECLARE
    box_pt geometry(point);
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        IF NEW.code_dep IS NOT NULL THEN
            UPDATE support_bt SET code_dep = NEW.code_dep WHERE support_bt.geom_point IN (SELECT (ST_DumpPoints(NEW.geom_line)).geom);      
            RETURN NULL;
        END IF;
    ELSE
        RAISE WARNING 'Other action occurred: %, at %', TG_OP, now();
        RETURN NULL;
    END IF;
END;
$$
;       
            
-- creation du trigger
DROP TRIGGER IF EXISTS vertex_dep ON depart_bt;
CREATE TRIGGER vertex_dep AFTER INSERT OR UPDATE ON depart_bt FOR EACH ROW EXECUTE PROCEDURE vertex_troncon();

My first code works well, but it has the disadvantage of iterating through all points of "support_bt" which may affect the performance of the trigger. So, I added a bounding box to limit the search area. However, I noticed that the bounding box does not work fine and all points in the support_bt table are selected.

/*UPDATE support_bt SET code_dep = NEW.code_dep WHERE support_bt.geom_point IN (SELECT (ST_DumpPoints(NEW.geom_line)).geom);*/
            
SELECT geom_point INTO box_pt FROM support_bt WHERE ST_contains((SELECT ST_SetSRID(ST_Extent(NEW.geom_line),32632)), support_bt.geom_point);
UPDATE support_bt SET code_dep = NEW.code_dep WHERE box_pt IN (SELECT (ST_DumpPoints(NEW.geom_line)).geom);

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.