Slight efficiency comment. Wouldn't it be better to do a LIMIT 1 since it
appears you don't really care about the count, just that there is at least one?
Also probably better to return true or false to prevent the needless casting.
So something like
sql := 'SELECT 1 FROM ' || container ||
' WHERE ST_Contains(' || cont_geom ||
', ST_GeomFromEWKB(decode(''' || geom_hex || ''', ''hex''))) LIMIT
1';
EXECUTE sql INTO count;
IF NOT FOUND THEN
RETURN false;
ELSE
RETURN true;
END IF;
I think you can probably shorten the last part to
RETURN FOUND;
(which looks shorter but a bit more intimidating)
Hope that helps,
Regina
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Simon Greener
Sent: Thursday, September 11, 2008 11:37 PM
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
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
