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?