2

Working with PostGIS database and QGIS, I have two polygons layers, an external with land lots (lotes) and an internal with building area (areaconstruida). In the "lotes" layer, I need a field in attribute table that calculate the building area inside the land lot.

I'm tying to create a trigger function that re-calculate the building area inside affected land lots every time the buildings layer is changed.

Before run the trigger, I tried some select in SQL to ensure-me that my code was correct.

     select *
            from
            (select lotes.cod_lote, sum(st_area) from public.lotes,

(select lotes.cod_lote, ST_area(st_intersection(lotes.geom,areaconstruida.geom))
            from public.areaconstruida, public.lotes
            WHERE ST_intersects(lotes.geom, 
    (select geom from public.areaconstruida where public.areaconstruida.id=3789)))
    AS TAB1 
    -- TAB 1 returns, for all features in lotes that intersects an especific building 
feature, the area of intersection with all buildings in project

                WHERE lotes.cod_lote=TAB1.cod_lote
                group by lotes.cod_lote) as TAB2 
    -- TAB2 returns, for all features in lotes that intersects an especific 
building feature, the sum of interception areas with the buildings layer
                WHERE TAB2.cod_lote='00309SE00006' 
-- Trying the result of an specific parcel lot

from this code I got expected result, with the total building area inside the land lot tested.

But I'm unsuccessful trying to activate the trigger

For the Trigger, I replaced the "public.areaconstruida.id=3789" for "NEW.geom", once I want to change the values of land lots affected by new or modified features in the buildings layers and replaced the WHERE TAB2.cod_lote='00309SE00006' for WHERE TAB2.cod_lote=lotes.cod_lote

Resulting in this code:

CREATE OR REPLACE FUNCTION area_constr2()
RETURNS trigger AS
$BODY$
BEGIN
UPDATE public.lotes
SET lotes.areaconstr = TAB2.sum
from
(select lotes.cod_lote, sum(st_area) from public.lotes, (select lotes.cod_lote, ST_area(st_intersection(lotes.geom,areaconstruida.geom))
from public.areaconstruida, public.lotes, NEW
WHERE ST_intersects(lotes.geom, (select geom from NEW.geom))) AS TAB1
WHERE lotes.cod_lote=TAB1.cod_lote
group by lotes.cod_lote) as TAB2
where TAB2.cod_lote=lotes.cod_lote;
END;


$BODY$
LANGUAGE plpgsql;

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

The query runs normally, but when I try to change something in buildings geometry on QGIS, results in errors.

This question is similar to this topic: (Trigger - Calculate area of a layer inside feature) from yesterday, but now I'm changing the inner layer, and not the outer layer.

Edit: I tried another code to re-calculate the building area of all land lots, instead of only affected ones. Of course, it is expected that it demands long time to run.

CREATE OR REPLACE FUNCTION area_constr2()
RETURNS trigger AS
$BODY$
BEGIN
UPDATE public.lotes
SET areaconstr = (SELECT T2.SUM
FROM (select TAB1.cod_lote, sum(st_area) 
from public.lotes,
(select public.lotes.cod_lote, ST_area(st_intersection(public.lotes.geom,public.areaconstruida.geom))
            from public.areaconstruida, public.lotes
            WHERE ST_intersects(public.lotes.geom, public.areaconstruida.geom))
    AS TAB1 
                WHERE lotes.cod_lote=TAB1.cod_lote
group by TAB1.cod_lote) AS T2
WHERE T2.cod_lote=lotes.cod_lote);

RETURN public.lotes;
END;


$BODY$
LANGUAGE plpgsql;

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

It runs in QGIS and takes about 15 seconds, but no changes at all occours.

  • I'm not an expert on triggers, but I think you need AFTER INSERT not BEFORE INSERT? – John Powell Dec 13 '18 at 17:47

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.