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: [EMAIL PROTECTED] 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: [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
<<winmail.dat>>
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
