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 agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.