3

I'am using QGIS and POSTGIS, for example to automatically calculate the area of a new created polygon. I do that with following code:

CREATE OR REPLACE FUNCTION calc_area()
RETURNS trigger AS
$BODY$
BEGIN
NEW.fla_ha := st_area(NEW.geom)/10000;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER area_calculate BEFORE INSERT OR UPDATE ON nutzungen
    FOR EACH ROW EXECUTE PROCEDURE calc_area();

Problem definition:

How do I change my trigger, so that editings at the geometry of a existing polygon (new vertex ...) will be recognized and the old area value will be overwritten.

If I use UPDATE in combination with an AFTER-TRIGGER it updates my whole fla_ha column and not the specific row:

CREATE OR REPLACE FUNCTION "area_new"()
RETURNS trigger AS
$BODY$BEGIN
UPDATE nutzungen SET fla_ha=ST_AREA(NEW.geom)/10000;
END;$BODY$
LANGUAGE plpgsql VOLATILE

ALTER FUNCTION "area_new"()
OWNER TO postgres;
  • 1
    The code looks correct for your expectations, but your question isn't clear. – Mike T Feb 2 '16 at 22:14
5

Your original trigger contains an error:

CREATE TRIGGER area_calculate **BEFORE** INSERT OR UPDATE ON nutzungen

This means that you're triggers calls the function on geom before the geometry is updated. Replace the trigger by calling the following:

DROP TRIGGER area_calculate;
CREATE TRIGGER area_calculate AFTER INSERT OR UPDATE ON nutzungen
    FOR EACH ROW EXECUTE PROCEDURE calc_area();

As for the calc_area() function, no need to give it a new name! CREATE OR REPLACE means that when you run the create script for the new function, it will replace the old one.

  • I have followed your instruction, with no success. Only if i use the argument BEFORE in 'CREATE TRIGGER area_calculate BEFORE INSERT OR UPDATE ON nutzungen' my new added polygon gets an area value. Argument AFTER affects no calculation for new added polygons and no recalculation for edited polygons. The field remains empty. – Rupicolous Feb 4 '16 at 9:34
  • Now I have changed it again according your hints and it runs. – Rupicolous Feb 4 '16 at 11:44
1

Your first statement is correct in what you're trying to achieve - at least testing on my PostgreSQL 9.3 server. Let me explain:

  • The trigger fires BEFORE the changes are applied to the table.
  • In this state, it already has information about the OLD content of the row and the NEW content of the row (i.e. how the row will look like after the changes implied in the UPDATE will have been applied), given it's fired during an UPDATE; for an INSERT, of course, only the NEW information exists.
  • The NEW content has not yet been written to the table, thus can be intercepted.
  • So, by setting NEW.fla_ha=st_area(NEW.geom)/10000, you fiddle inside the row information that's yet to be applied to the table.
  • By RETURN NEW, you give the entire, customized-by-your-function row back, which is then applied to the table - which means, including your st_area calculation

With an AFTER trigger, it doesn't work as you'd expect it because the changes have already been pyhsically applied to the table row. So any interception in the NEW row is simply disregarded. The NEW object exists in this context for further evaluation, but changes to it are not applied anymore to the table.

Hope this makes it quite clear.

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.