[postgis-users] Any Projected Straights Function?

[Fonseca Hespanha de Oliveira, Joao da]

Mark and Simon:

Many thanks for your suggestions, this is the right solution (check constraints with functions) to many of the spatial reference integrity problems showing up in the model (also thanks to give me the right technical term!).

However, from the method definitions for ST_Contains, I am not sure if it does the required projected straights operation if:

parcel p, building b

p.geometry ST_Contains(b.geometry)

but where p.geometry is 2D (projected to Datum reference plane) and b.geometry is 3D (same planimetric reference but with heights).

Regards,
João Paulo Hespanha
Technical University Delft
OTB Research Institute
PhD Student - GiST & GIGB Sections



-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net on behalf of Simon Greener
Sent: Fri 12-09-2008 5:37
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Any Projected Straights Function?
 
Mark,

> 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'));

Not a big expert on PostgreSQL but this (being able to use a function within a CHECK constraint) is very nice feature. Nice addition to my knowledge base on spatial referential integrity.

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

Good enough, though as it is clear what you are doing.

regards
Simon


-- 
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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 4654 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080912/aa0813d8/attachment.bin>