[postgis-users] How to trim a GeometryCollection to get aMultiPolygon?

[Obe, Regina]

Mike,

This is actually kind of clever.  Never gave much thought to using
ST_Buffer(..,0.0) for that.

It appears to work because buffering any non-aerial geometry by 0.0
always returns an empty polygon which is empty and gets thrown out when
the geometry collection is collapsed.

So the only issues with using the ST_Buffer(...,0.0) is that it might be
slower than other approaches and also for some invalid geometries I have
run into occasions where that trick drops off some of the geometry so in
general I've been very cautious using it.  I don't recall having the
issue with valid geometries.  And I think ST_Intersection would throw a
topological error in those cases anyway.

Perhaps the safer approach (and possibly faster approach) would be to
dump out the geometrycollection into individual geometries and selective
pick out those that are polygons or multipolygons with a query like
below.

Note I put in the ST_Multi call to force POLYGONs into MULTIPOLYGONs so
it doesn't violate a multipolygon constraint.

SELECT ST_Multi(foo.the_geom)
FROM
 (SELECT (ST_Dump(ST_Intersection(
                GeomFromText('MULTIPOLYGON(((0 0,0 2,2 2,2 0,0 0)),((0
4,2 4,2 6,0 6,0 4)))', 4326), 
                GeomFromText('POLYGON((0 1,2 1,2 4,0 4,0 1))',
4326)))).geom As the_geom) As foo
 WHERE ST_GeometryType(foo.the_geom) ILIKE '%polygon%'
            
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
Michael Smedberg
Sent: Monday, August 04, 2008 1:29 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] How to trim a GeometryCollection to get
aMultiPolygon?

I have some SQL that intersects a number of multipolygons, and then
attempts to test whether a point is in the result.  In most cases it
works fine.  However, some intersections return a GeometryCollection,
which results in an error like:

ERROR:  Relate Operation called with a LWGEOMCOLLECTION type.  This is
unsupported

Is there a simple and generic way to simplify the collection to trim out
linestrings, etc.?  Through experimentation, I've found that doing an
ST_Buffer(XXX, 0.0) will clean up the GeometryCollection, but I don't
know if that's a safe thing to rely on.

Here's an example of a problematic SQL statement:

SELECT ST_Contains
(
        ST_Intersection
        (
                GeomFromText('MULTIPOLYGON(((0 0,0 2,2 2,2 0,0 0)),((0
4,2 4,2 6,0 6,0 4)))', 4326),
                GeomFromText('POLYGON((0 1,2 1,2 4,0 4,0 1))', 4326)
        ),
        GeomFromText('POINT(1 1.5)', 4326)
)

And here's an example of a "fixed" SQL statement:

SELECT ST_Contains
(
        ST_Buffer
        (
                ST_Intersection
                (
                        GeomFromText('MULTIPOLYGON(((0 0,0 2,2 2,2 0,0
0)),((0 4,2 4,2 6,0 6,0 4)))', 4326),
                        GeomFromText('POLYGON((0 1,2 1,2 4,0 4,0 1))',
4326)
                ),
                0.0
        ),
        GeomFromText('POINT(1 1.5)', 4326)
)

Is it OK to use ST_Buffer to clean up a GeometryCollection?  Or is there
a
better/safer way to do this?

Thanks so much!

_______________________________________________
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.