[postgis-users] Any Projected Straights Function?

[Simon Greener]

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 <J.P.daFonsecaHespanhadeOliveira at tudelft.nl> 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: simon at spatialdbadvisor.com
  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