[postgis-users] Validating geometry with trigger function

[Johannes Sommer]

Hi again,

I solved my problem. :-)
The point is that the subquery
"SELECT st_within(NEW.geometry, a.geometry) FROM Area1 a WHERE a.fl_id = 
NEW.fl_fk"
returns a boolean as 't' or 'f' and not as 0 or 1.
It works with the following:

CREATE OR REPLACE FUNCTION check_geom()
 RETURNS "trigger" AS
$BODY$

BEGIN
IF (SELECT st_within(NEW.geometry, a.geometry) FROM Area1 a WHERE 
a.fl_id = NEW.fl_fk) = 'f' THEN
   RAISE EXCEPTION 'Geometry is not valid!';
   RETURN NULL;
ELSE
RETURN NEW;
END IF;
END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION check_geom() OWNER TO postgres;

bye,
Johannes



Johannes Sommer schrieb:
> Hi all,
>
> I'm facing the following problem:
>
> Area1 has got an 1:n relationship to Area2.
>
> Area1, fl_id (PK) <----- Area2, fl_fk (FK)
>              1         :                 n
>
> Now I want to ensure that this relationship will also be spatially.
> I only want to have new geometry objects in table Area2, which are 
> within Area1 including the 1:n relationship.
> (if that sounds bad, sorry for my poor english ;-)).
>
> Example: Area2, fl_fk = 1 must be within Area1, fl_id = 1
>
>                  -------------------
>                 | Area1, fl_id = 1    |
>                 |                             |
>                 |       -----------      |
>                 |       | Area2,    |     |
>                 |       | fl_fk = 1 |      |
>                 |         ----------     |
>                 |                             |
>                 -------------------
>
> The following trigger won't work.
>
> CREATE OR REPLACE FUNCTION check_geom()
>  RETURNS "trigger" AS
> $BODY$
>
> BEGIN
> IF (SELECT st_within(NEW.geometry, a.geometry) FROM Area1 a WHERE 
> a.fl_id = NEW.fl_fk) <> 1 THEN
>    RAISE EXCEPTION 'Geometry is not valid!';
> RETURN NEW;
> END IF;
> END;
> $BODY$
>  LANGUAGE 'plpgsql' VOLATILE;
> ALTER FUNCTION check_geom() OWNER TO postgres;
>
> CREATE TRIGGER trig_check_geom
>  BEFORE INSERT OR UPDATE
>  ON Area2
>  FOR EACH ROW
>  EXECUTE PROCEDURE check_geom();
>
> Everytime I insert a geometry object into table Area2, regardless 
> wether it's within an polygon of Area1 or not, I get the exception.
>
> Any help would be appreciated.
>
> bye,
> Johannes
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>