Hey, it is tricky to use qgis value directly, because depending on the version of qgis (and gdal?) you may have errors with (multi , 3D, M ). I would advise to strongly enforce a type to any input from qgis. (for instance ST_SnapToGrid( ST_Force2D( ST_GeometryN(ST_CollectionExtract(geom,1),1) ) , 0.1) for a 2D point precision 0.1 .
Cheers, Rémi-C 2014-10-14 13:39 GMT+02:00 Pietro Rossin <[email protected]>: > 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. > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
