2

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.

5

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.

4

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.
  • I think you will find ST_NumInteriorRings will return the number of holes in a polygon. ST_NRings will return the number of polygons and holes in a polygon. You could do ST_NRings - ST_NumInteriorRings, but there is a possibility it would be wrong. ... but only looks at the first polygon ... – MickyT Feb 27 '15 at 19:37
  • These are the tools we are given. If you know the number of total rings, and the number of interior rings for each exterior ring (extracted separately), you can eventually deduce the number of exterior rings. I'd have included a NumParts operator to expose the number of exterior rings, but I didn't sit on that committee. – Vince Feb 27 '15 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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