I've had a go at this using constraints and plpgsql, which seems more intuitive to me:
alter table rbasin_point add constraint contained_within_basin CHECK (assertContains(the_geom, 'containing_table', 'containing_geom_column')); Where assertContains is: CREATE OR REPLACE FUNCTION assertContains(geom GEOMETRY, container VARCHAR, cont_geom VARCHAR) RETURNS bool AS $$ DECLARE geom_hex VARCHAR; sql VARCHAR; count INTEGER; BEGIN geom_hex := encode(ST_asEWKB(geom), 'hex'); sql := 'SELECT count(*) FROM ' || container || ' WHERE ST_Contains(' || cont_geom || ', ST_GeomFromEWKB(decode(''' || geom_hex || ''', ''hex'')))'; EXECUTE sql INTO count; IF count = 0 THEN RETURN 'f'; END IF; RETURN 't'; END; $$ LANGUAGE plpgsql; The function is a bit of a mess, since I didn't spend much time on it, but is this close to what you're looking for? -- Mark Leslie Geospatial Software Architect LISAsoft ------------------------------------------------------------- Ph: +61 2 8570 5000 Fax: +61 2 8570 5099 Mob: +61 Suite 112, Jones Bay Wharf 19-21 Pirrama Rd Pyrmont NSW 2009 ------------------------------------------------------------- LISAsoft is part of the A2end Group of Companies http://www.ardec.com.au http://www.lisasoft.com http://www.terrapages.com Simon Greener wrote: > Joao, > > There is no spatial declarative referential integrity in PostgreSQL/PostGIS > through which what you want to do can be done. > > SQL92 Assertions goes closest to what you want to do. For example: > > CREATE ASSERTION building_is_within_parcel > CHECK (EXISTS (SELECT 1 > FROM parcel p, > building b > WHERE contains(p.geometry,b.geometry)) > ); > > However, no commercial relational database today implemented this part of the > SQL92 standard. > > In the end you have to use triggers eg (not PostgreSQL): > > CREATE TRIGGER building_is_within_parcel > INSERT ON building > REFERENCING NEW AS new > FOR EACH ROW > WHEN ( > EXISTS (SELECT 1 > FROM parcel p > WHERE contains(p.geometry,:new.geometry) > ) > ) > SIGNAL SQLSTATE '70001' > ('Building is not inside any parcel!') > > I have hoped for years to be able to do this but the vendors have made their > decisions re SQL92 and it doesn't look like being changed. > > Anyway, I hope this helps > > regards > SImon > On Fri, 12 Sep 2008 02:32:09 +1000, Fonseca Hespanha de Oliveira, Joao da > <[EMAIL PROTECTED]> wrote: > > >> Hi! >> >> I am currently doing research on UML modeling of cadastre and land >> administration data. On a Object Diagram, there is a situation where a check >> should be done on the projected straights of a building outline (original >> building to be depicted in 3D), in order to see if it is contained within a >> land parcel (depicted on a 2D planar partition). >> >> I check both the ISO 13249-3 (SQL/MM Part3: Spatial) and PostGIS >> documentation and could not find any explicit reference. So, can PostGIS do >> this kind of check? >> >> João Paulo Hespanha >> Technical University Delft >> OTB Research Institute >> PhD Student - GiST & GIGB Sections >> >> >> > > > > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users