1

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);

2 Answers 2

2

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();
0
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.