[postgis-users] PostGIS 1.3.3 on Windows XP Unstable

Paragon Corporation lr at pcorp.us
Wed Aug 13 00:06:13 PDT 2008


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





More information about the postgis-users mailing list