In PostGSI, is there anyway to turn a GEOMETRYCOLLECTION into a MULTIPOLYGON by dropping all non-POLYGONs/non-MULTIPOLYGONs?

I'm doing a query that cuts a bunch of geometries (using ST_Intersection) and I want to group them together. I'm using ST_Collect to turn this into a GEOMETRYCOLLECTION. I could use ST_Union, but that's slower. Is there an alternative approach where I can drop all the non-POLYGONs/non-MULTIPOLYGONs from a GEOMETRY collection and just return a MULTIPOLYGON?

I know would be less accurate than ST_Union, but it should be faster. For what I'm doing a small hit in accuracy is worth it if it speeds it up.

2 Answers 2


ST_CollectionExtract is what you are looking for:



Basically the st_intersection will generate several types of geometries: polygons, linestring, points.

The problem is that most GIS systems usually use the concept of layers and each layer must be a SET( not a COLLECTION ) of features of same kind.

Sure, just dump the geometry collection into his components and filter those that are not polygons.

You will need the st_dump function in conjuntion with st_geometrytype.

  • 1
    Are you able to provide a few more details about the steps involved in doing this? I won't be following them personally but this Answer flagged up for review due to its brevity.
    – PolyGeo
    May 18, 2013 at 8:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.