How to update a point table when a polygon table is modified

Question

I am trying to create a function that update table A (point) when table B (polygon) is modified. The reference to B must be changed in A. I can't get it to work. There are no overlaps in the polygons. It really is just to update the polygon ID in the points table. I was thinking of ST_Within to do it.

CREATE OR REPLACE FUNCTION maj_A()
    RETURNS trigger AS $foo$    
BEGIN
    UPDATE public.Points AS A
    SET A.a = B.a, 
        A.b = B.b
    FROM public.Polygons AS B
    WHERE ST_WITHIN(Points.geom, Polygons.geom) AND NEW.geom <> OLD.geom;
    RETURN NEW;
END;

$foo$ LANGUAGE plpgsql;

I am using postgre 10 and postgis 2.4. Any working function is welcome. The function is linked to a trigger.

Answer 1

Would make a comment, but don't have enough reputation, sorry.

Shouldn't NEW and OLD be B and ? B_new or something like this? Where is the new geometry stored to make the ST_Within check?

If I missed something and you want to compare geometries as in NEW.geom <> OLD.geom, consider ST_Equals or ST_OrderingEquals like ST_OrderingEquals(NEW.geom, OLD.geom) = 0

Answer 2

The following code answers my need.

https://gist.github.com/neogis-de/a1d08c38d8b9c5d316c7

It updates a points layer based on the modification of an overlapping polygons, wheter it's attibute or geometry. It only attributes a polygon reference to underlying points without changing anything to a point that was in it and isn't anymore.