Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

ST_CollectionExtract is what you are looking for:

share|improve this answer

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.

share|improve this answer
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 '13 at 8:29

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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