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
>
>
--
SpatialDB Advice and Design, Solutions Architecture and Programming,
Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL
Certified Professional
Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, Radius
Topology and Studio Specialist.
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
Email: [EMAIL PROTECTED]
Voice: +613 9016 3910
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
NAC:W80CK 7SWP3
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
