[postgis-users] Automated area calculating

[Johannes Sommer]

Hi Michael,

Michael Fuhr schrieb:
> What's the CREATE TRIGGER statement?
> CREATE OR REPLACE FUNCTION calc_area()RETURNS trigger AS
>   
> $BODY$
> BEGIN
>   NEW.area := st_area(NEW.geometry);
>   RETURN NEW;
> END;
> $BODY$
> LANGUAGE plpgsql;
>   
I tried this before - but no calculation happened. The important hint 
was the Trigger statement.
I did a "AFTER INSERT OR UPDATE" because I didn't know better. With

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


everything worked fine. Thank you for your help, Michael.
But I still don't know why you have to fire the trigger BEFORE an INSERT 
OR UPDATE and not AFTER this
happens.

greetings,
Johannes

Solution to automated area calculation:

--Start

CREATE OR REPLACE FUNCTION calc_area() RETURNS "trigger" AS 
$BODY$
BEGIN

-- Important: declare as 'CREATE TRIGGER BEFORE INSERT OR UPDATE' and not 

-- 'AFTER INSERT OR UPDATE'

  NEW.area_qm:=st_area(NEW.geometry);

  NEW.area_ha:=NEW.area_qm/10000.;

  RETURN NEW;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION calc_area() OWNER TO postgres;


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

--End