1

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.

0

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

0

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.

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.