1

I'm trying to create a trigger to auto-fill a field of a table in postGIS based on two polygon layers geometry. I'm using QGIS for geometry updates.

The first layer (lotes) has bigger polygons and the second one (areaconstruida) has smaller polygons completaly contained in one polygon of first layer.

CREATE OR REPLACE FUNCTION area_constr()
RETURNS trigger AS
$BODY$
BEGIN
NEW.areaconstr := sum(ST_area(st_intersection(NEW.geom,public.areaconstruida.geom)))
from public.areaconstruida, public.lotes
where ST_intersects(NEW.geom,public.areaconstruida.geom);
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

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

But the results I'm receiving are much bigger than expected. Anyone can help-me to find where is the error in my code?

2

Your query is making a cross-product between the two tables.

Since you are not using the table public.lotes but only the NEW geometry, you can simply remove , public.lotes from the query.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

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