[postgis-users] function trigger to populate values based on spatial relationships

[Pietro Rossin]

Hi all
I'm trying to write a trigger function to automatically populate data for
some fields based on spatial relationships
I want to control if the new point falls inside a set of polygons
(monitoraggio_acque.corpi_idrici_cw_tw) and if positive I want to take a
value to be written in "cod_ci_sup" from there..

If the added point falls externally to the previous polygons I want to take
values from other postgis tables, based on distance and overlap relationship


This is the trigger:
*******************
CREATE TRIGGER settavaloriautomaticiscarichi
  BEFORE INSERT
  ON catasto_scarichi.aa_impianti_pn
  FOR EACH ROW
  EXECUTE PROCEDURE
catasto_scarichi.setta_valoriautomaticiscarichi_function();
*******************

And this is the relative funcion:

*******************
CREATE OR REPLACE FUNCTION
catasto_scarichi.setta_valoriautomaticiscarichi_function()
  RETURNS trigger AS
$BODY$
BEGIN
	IF (SELECT NEW.geom&&b.geom from (select geom from
monitoraggio_acque.corpi_idrici_cw_tw) b where st_contains(b.geom,
NEW.geom))= 't' THEN
	NEW.cod_ci_sup = (SELECT cod_corpo FROM
monitoraggio_acque.corpi_idrici_cw_tw b WHERE NEW.geom&&b.geom and
st_contains(b.geom, NEW.geom));
	RETURN NEW;
	
	ELSE
	NEW.cod_asta_reg = (SELECT codice_fvg FROM
idrologia.idrfvg_reteidrografica_l a WHERE st_dwithin (NEW.geom, a.geom,
1000) ORDER BY st_distance(NEW.geom, a.geom) asc limit 1);
	RETURN NEW;
	end if;
	
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
*******************

If I try to add values from QGis 2.4 I don't get any message but no value is
added..
Where is my mistake??

Thanks
Pietro



--
View this message in context: http://postgis.17.x6.nabble.com/function-trigger-to-populate-values-based-on-spatial-relationships-tp5007143.html
Sent from the PostGIS - User mailing list archive at Nabble.com.