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
