[postgis-users] Any Projected Straights Function?

[Obe, Regina]

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: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] 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: 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
-----------------------------------------
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.