[postgis-users] Any Projected Straights Function?

[Simon Greener]

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