Mike, This looks like a good start. A quick cursory glance I can see where there can be improvements - ironically by using the more basic SQL constructs. E.g. the following
geom := ScrubPolygon(ST_GeometryN(input, i)); if output is null then output := geom; else output := ST_Collect(output, geom); end if; Would probably be more efficiently done with SELECT gid, ST_Collect(ScrubPolygon(geom)) As scrubbedgeom FROM (SELECT gid, (ST_Dump(the_geom)).geom FROM tabletoscrub) As foo GROUP By gid ; The reason being is that ST_GeometryN is quite inefficient when dealing with large numbers of geometries. So if you plan to dump out all geometries use ST_Dump instead. Also would be nice if you posted this to the wiki when you are done. Hope that helps, Regina -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bresnahan, Mike Sent: Tuesday, August 12, 2008 8:42 PM To: PostGIS Users Discussion Subject: RE: [postgis-users] PostGIS 1.3.3 on Windows XP Unstable -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256 Martin Davis said at Monday, August 11, 2008 6:07 PM: > This is a known design flaw in the SimplifyTP algorithm. The problem is > that with large tolerance values small holes and shells can wind up on > the "wrong side" of a containing geometry. It shouldn't be too hard to > fix, just by checking whether holes/shells are outside/inside another > shell, and deleting them if they are. I had no previous experience with PL/pgSQL, but I put together a function that seems to work. I'm not at all sure if it is the most efficient way to do this. In particular, I'm unsure that using ST_Collect() to append to a geometry collection is efficient as I fear it might be making a copy of the collection each time. Thoughts anyone? CREATE FUNCTION ScrubPolygon(input geometry) RETURNS geometry AS $$ declare outerRing geometry; innerRing geometry; innerRingArray geometry[]; count int; begin outerRing := ST_ExteriorRing(input); RAISE NOTICE 'outerRing = %', ST_AsText(outerRing); count := NumInteriorRings(input); for i in 1..count loop innerRing := InteriorRingN(input,i); if ST_Contains(ST_MakePolygon(outerRing), ST_MakePolygon(innerRing)) = 't' then innerRingArray := array_append(innerRingArray,innerRing); end if; end loop; return MakePolygon(outerRing, innerRingArray); end; $$ LANGUAGE plpgsql; create function ScrubMultiPolygon(input geometry) returns geometry as $$ declare count int; geom geometry; output geometry; begin count := ST_NumGeometries(input); for i in 1..count loop geom := ScrubPolygon(ST_GeometryN(input, i)); if output is null then output := geom; else output := ST_Collect(output, geom); end if; end loop; return output; end; $$ LANGUAGE plpgsql; create function Scrub(input geometry) returns geometry as $$ declare geom geometry; output geometry; begin if ST_GeometryType(input) = 'ST_MultiPolygon' then return ScrubMultiPolygon(input); elsif ST_GeometryType(input) = 'ST_Polygon' then return ScrubPolygon(input); else return null; end if; end; $$ LANGUAGE plpgsql; -----BEGIN PGP SIGNATURE----- Version: PGP Universal 2.8.3 Charset: us-ascii wsBVAwUBSKItDB6WPRoYuvd0AQhjOAgAipUUzfR20cmAqAiqHZUWM82KNq3rSURO 7ByKvkkgiF3+kW7NhzR60keamycDbCT0lo8iQCiHjiemYsHRNyMlWwVBivIPVp2R Za55hJPDWFlwAZSgs0R+ZTjZWLyUYg41HMBlCz1e/K7xW71MUEKaEJkIOMfM0YY9 xtu0+z+YgIMFkCVPjeI4eJ13XhhWQlUUE9b49ykbKc8+6nBeyfUf3vso/3Q4TJu6 V8JpHYKmPbW7Hzy3HNvAjc714gCEOQz0DdBSdByXHvDayZZxUwpFwEJO1XJAVk5z X6knCDJ4IWa8PvcE6YPLrcSuoE1kDyx//Wchv1OmxGMID8/XKajpuA== =TiqW -----END PGP SIGNATURE----- _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users