In PostgreSQL, I want to use ST_Within from PostGIS to attribute a polygon reference to points.
Upon polygon modifications, I catch OLD.geom to erase the points feature in a before trigger. (Then, I catch NEW.geom to rename the new points after modifications.)
BEFORE UPDATE :
IF TG_OP = 'UPDATE' THEN UPDATE pointstable SET polyid = NULL WHERE pointstable.polyid = OLD.polyid AND ST_Within(pointstable.geom, OLD.geom);
AFTER UPDATE :
IF TG_OP = = 'UPDATE' THEN UPDATE pointstable SET polyid = NEW.polyid WHERE ST_Within(pointstable.geom, NEW.geom);
When it runs, it seems to catch the centroid of the modified points and sometime yield the wrong results. Some points polyid gets erased and not reassigned.
My main question therefore is : How can I iterate over the values in NEW or OLD in plpgsql. Must it be declared then defined (I am more or less new on the matter).
My second question is : Is there anyone familiar with the behavior of NEW and OLD, the SQL language and ST_Within that can explain to me what is happening exactly from the resulting query. As said, sometime it works, others half the points are renamed and the rest is not.
I am repeating almost the same modification over and over. It works 90% of the time but once in a while, the points don't get renamed, any one has an idea?
I have a weird case where half the points gets renamed but the rest does not. It seems to make no sense since the trigger is on polygons modifications. Therefore all points should be renamed or nothing. Because of that, I suspect it would have to do with ST_Within.