Perform intersection using a TRIGGER

Question

I've two geometry table inside my DB:

• buffer: id INTEGER SERIAL PRIMARY KEY, geom POLYGON
• polygons: geom MULTIPOLYGON

I need have the intersection between buffer and polygons and I will do this using this TRIGGER:

CREATE OR REPLACE FUNCTION intersection_function()
RETURNS TRIGGER AS
$$
BEGIN 
    SELECT
        a.id as idb,
        ST_Intersection(NEW.a.geom, NEW.b.geom) as geom
    FROM buffer as a, polygons as b
    WHERE ST_Intersects(a.geom, b.geom);
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER intersection_trigger
AFTER INSERT OR UPDATE ON test_intersection
FOR EACH ROW EXECUTE PROCEDURE intersection_function();

Table test_intersection has: id INTEGER SERIAL PRIMARY KEY, idb INTEGER and geom MULTIPOLYGON.

When I draw a polygon inside buffer table I expect to have the result of intersection inside test_intersection but that table is always empty.

It is my first trigger, what I've wrong?

Answer 1

First of all, the trigger should be on buffer table. Secondly, you should insert the intersection (now you claculate it but then leave it behind).

I used the following codes to create 3 tables:

create table buffer (id  serial primary key );
select addgeometrycolumn('buffer', 'geom' , 3979 , 
'POLYGON' , 2);

create table polygons (id  serial primary key );
select addgeometrycolumn('polygons', 'geom' , 3979 , 
'MULTIPOLYGON' , 2);

create table intersections (id  serial primary key );
select addgeometrycolumn('intersections', 'geom' , 
 3979 , 'POLYGON' , 2);

Then, It is time to create a trigger function. This function calculates the intersection between the new inserted row of buffer table and existing geometries of polygons table. It is very important that the return type of the ST_Dump(ST_intersection()) be the same as the type of the geometry column of the intersections table. In this example both are "POLYGON":

CREATE OR REPLACE FUNCTION insert_intersection() 
RETURNS TRIGGER AS
$BODY$
BEGIN
   INSERT INTO  intersections(geom)
        SELECT (ST_Dump(ST_Intersection(new.geom, 
  polygons.geom))).geom
         FROM polygons;

RETURN new;
END;
$BODY$
language plpgsql;

Then, we should say to PostgreSQL to run this function each time a new row is inserted into the buffer table:

CREATE TRIGGER trig_copy
 AFTER INSERT OR UPDATE ON buffer
 FOR EACH ROW
 EXECUTE PROCEDURE insert_intersection();

It works well. by each new insert in buffer table, the intersection of that polygon with polygons in polygons table is stored in the intersections table.