[postgis-users] Automated area calculating

[Michael Fuhr]

On Sat, Feb 02, 2008 at 09:01:06PM +0100, Johannes Sommer wrote:
> I'm trying to set up an automated area calculation for the column "Area".
> My trigger looks like this:

What's the CREATE TRIGGER statement?

>        IF (TG_OP = 'INSERT') THEN
>            UPDATE test
>            SET Area = area(geometry);

This will update every row in the table except the row you're
inserting (unless this is an AFTER trigger) and it will invoke any
UPDATE triggers on the table from within the INSERT trigger.  Are
you sure that's what you want?  Is there a reason you're not updating
the area for just this row?

>           ELSIF (TG_OP = 'UPDATE') THEN
>            NEW.Area = area(geometry);
>            RETURN NEW;

You probably want NEW.geometry instead of just geometry.  And why
are you returning here?

>        END IF;
>    RETURN NULL;

Why RETURN NULL here?

Perhaps this is what you want:

CREATE OR REPLACE FUNCTION calc_area()
RETURNS trigger AS
$BODY$
BEGIN
  NEW.area := st_area(NEW.geometry);
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

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

If that doesn't work then please explain what effect you're trying
to achieve and how this doesn't achieve it.

-- 
Michael Fuhr