Refresh ST_Area in QGIS

Question

I added a new column in my PostgreSQL/PostGIS database for getting surface area. I can open as a layer in QGIS, but when I draw a new polygon the area column is not updated, is there a way to have it updated as soon as I finish drawing the polygon?

This is the SQL I used:

ALTER TABLE public."EDIFICIO_2" ADD COLUMN area double precision;
UPDATE "EDIFICIO_2" SET area = round((ST_AREA(geom)) :: numeric,2);

Answer 1

It is not possible to automatically update the area when you are done drawing, but when you save the edit.

You would use a trigger to update the value:

CREATE OR REPLACE FUNCTION public.EDIFICIO_2_tg_fn() RETURNS trigger AS
$BODY$BEGIN

    IF NEW.geom ISNULL THEN
      NEW.area = 0;
    ELSE
      NEW.area = round((ST_AREA(geom)) :: numeric,2)
    END IF;

  RETURN NEW;
END;$BODY$ LANGUAGE plpgsql;



CREATE TRIGGER EDIFICIO_2_tg BEFORE INSERT OR UPDATE
   ON public."EDIFICIO_2" FOR EACH ROW
   EXECUTE PROCEDURE public.EDIFICIO_2_tg_fn();

Answer 2

You can update your area column with a trigger:

CREATE OR REPLACE FUNCTION update_area()
  RETURNS TRIGGER AS
    $$
    BEGIN
      NEW.area := ROUND(ST_Area(NEW.geom)::NUMERIC, 2);
      RETURN NEW;
    END;
    $$
  LANGUAGE plpgsql;

CREATE TRIGGER update_area
  BEFORE UPDATE OR INSERT ON public."EDIFICIO_2"
  FOR EACH ROW
  EXECUTE PROCEDURE update_area();

Note that you will need to save your edits in order to make the changes and fire the trigger.