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

[Michael Smedberg]

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
postgi...@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[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

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

[postgis.dom...@spamgourmet.com]

Obe Regina - robe...@cityofboston.gov a écrit :
> WHERE ST_GeometryType(foo.the_geom) ILIKE '%polygon%'

Would it be enough to check the dimension of the geomtry in that case ?
(i.e. ST_Dimension(foo.the_geom) = 2)

And, more important, do you think it be faster than checking its type
against a string ?

Regards
--
Arnaud

[Obe, Regina]

Arnaud,

Great idea. Yes I think that is better, less prone to error and probably faster since integer checks are faster than string checks. I'm assuming the cost of accessing the ST_Dimension is not any more (and probably less) than the cost of accessing the geometry type and both are fairly negligable.

Thanks,
Regina

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

_______________________________________________