[postgis-users] Any Projected Straights Function?

[Mark Leslie]

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 <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
>>
>>
>>     
>
>
>
>