How to get the number of polygons of a geometry in PostgreSQL

Question

How can I get the number of polygons that form a geometry in PostgreSQL?

For example U.S map has a lot of islands in addition to its main lands. I want to count these islands plus the main lands.

Answer 1

Use the geometry accessor ST_NumGeometries.

For example

SELECT ID, ST_NumGeometries(shape)
FROM myTable

If your geometries contain geometry types other than polygons, you will need to break them up and keep only the polygons before counting. But from your question this would not be required.

Answer 2

According to the documentation (chapter 7.3), there are several geometry accessors which might meet your criteria, depending on whether the geometry is a collection, a polygon, or a multi-polygon:

• ST_NumGeometries - If geometry is a GEOMETRYCOLLECTION (or MULTI*) return the number of geometries, otherwise return NULL.
• ST_NumInteriorRings - Return the number of interior rings of the first polygon in the geometry. This will work with both POLYGON and MULTIPOLYGON types but only looks at the first polygon. Return NULL if there is no polygon in the geometry.
• ST_NRings - If the geometry is a polygon or multi-polygon returns the number of rings.