1

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.

enter image description here

Edit :

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.

enter image description here

  • Do you have overlapping OLD polygons? – JGH Mar 14 '18 at 14:18
  • The NEW polygon can overlap another OLD . – Boodoo Mar 14 '18 at 14:21
2

Having multiple overlaps is likely the source of the issue, as you are not controlling the order of the update (and you really can't).

ex: point X is located under polygon P1. After update, it is not under P1 anymore but under P2.

Case 1:
P1 is processed first: X id is cleared and not updated (as it is not under P1 anymore).
P2 is processed, X id is not cleared (as it is not under OLD P2) and is updated as it is now under P2. --> all is good

Case 2:
P2 is processed first: X id is not cleared (as it is not under OLD P2) and is updated as it is now under P2.
P1 is processed and X id is cleared and not updated (as it is not under P1 anymore) --> the point ID is null

To overcome this, you could record when a point ID is updated. You would add a boolean column isUpdated that you set to false before processing.

The trigger that clears the ID does it only for not-processed points

IF TG_OP = 'UPDATE'
THEN

   UPDATE pointstable 
   SET polyid = NULL 
   WHERE pointstable.polyid = OLD.polyid 
      AND ST_Within(pointstable.geom, OLD.geom) 
      AND isupdated = false; 

Then when you set the ID to the new polygon you also flag the entry as processed

IF TG_OP = 'UPDATE'
THEN
  UPDATE pointstable 
  SET polyid = NEW.polyid,
      isupdated = true
  WHERE ST_Within(pointstable.geom, NEW.geom);
  • I tought that the update was ordered based on gid. Do you have any idea the ordering works? – Boodoo Mar 14 '18 at 14:51
  • It is an arbitrary order - unless you specified it to be otherwise. But it doesn't really matter as in your case you would need to update points loosing the ID (shrinking polygons) before they get a new ID (expanding polygons), which is not possible is any polygon is both loosing some parts and gaining new ones. – JGH Mar 14 '18 at 15:02
  • Ok, thanks. Do you have any idea of how I can grab new or old? SELECT * FROM NEW cannot be done... – Boodoo Mar 14 '18 at 15:05
  • You just have access to the OLD and NEW for the current row (the one being updated) – JGH Mar 14 '18 at 15:10
  • Ok, I had trouble finding any information about that. – Boodoo Mar 14 '18 at 15:13

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.