[postgis-users] Validating geometry with trigger function

[Johannes Sommer]

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