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

[Rémi Cura]

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 <pierigis at gmail.com>:

> 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
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20141014/6dd8262d/attachment.html>