2

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?

  • 2
    I think you should write an insert statement inside your "intersection_function" to insert the created "ST_Intersection(NEW.a.geom, NEW.b.geom)" geometry into "test_intersection" table. Now, you just have a select statement but you do not insert the result of it in "test_intersection " table. – milad Dec 27 '19 at 19:06
  • 1
    In addition, you should change your trigger. it should be on buffer table (the table where you draw your new polygon). "CREATE TRIGGER intersection_trigger AFTER INSERT OR UPDATE ON buffer" – milad Dec 27 '19 at 19:09
  • 1
    have a look at this: stackoverflow.com/questions/12343984/… – milad Dec 27 '19 at 19:11
  • @milad consider writing this up as an answer. – geozelot Dec 28 '19 at 6:42
  • @milad is not clear for me. Can you write an example? Follow the suggest of ThingumbaBob and write an answar please. – Massimiliano Moraca Dec 28 '19 at 11:18
2

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.

enter image description here

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.