ST_DumpRings
Prev  7.4. Geometry Accessors  Next

Name

ST_DumpRings — Returns a set of geometry_dump rows for the exterior and interior rings of a Polygon.

Synopsis

geometry_dump[] ST_DumpRings(geometry a_polygon);

Description

A set-returning function (SRF) that extracts the rings of a polygon. It returns a set of geometry_dump rows, each containing a geometry (geom field) and an array of integers (path field).

The geom field contains each ring as a POLYGON. The path field is an integer array of length 1 containing the polygon ring index. The exterior ring (shell) has index 0. The interior rings (holes) have indices of 1 and higher.

[Note]

This only works for POLYGON geometries, not MULTIPOLYGONs. Use ST_Dump to extract polygon elements from polygonal geometries: ST_DumpRings((ST_Dump(geom)).geom )

Availability: PostGIS 1.1.3. Requires PostgreSQL 7.3 or higher.

This function supports 3d and will not drop the z-index.

Examples

Extracting all rings as polygons.

SELECT polyTable.field1, polyTable.field1,
	  (ST_DumpRings(polyTable.geom)).geom As geom
FROM polyTable

Extracting shell and holes from a polygon.

SELECT path, ST_AsText(geom) As geom
	FROM ST_DumpRings('POLYGON ((1 9, 9 9, 9 1, 1 1, 1 9), (2 2, 2 3, 3 3, 3 2, 2 2), (4 2, 4 4, 6 4, 6 2, 4 2))');
 path |              geom
------+--------------------------------
 {0}  | POLYGON((1 9,9 9,9 1,1 1,1 9))
 {1}  | POLYGON((2 2,2 3,3 3,3 2,2 2))
 {2}  | POLYGON((4 2,4 4,6 4,6 2,4 2))

Finding interior rings which lie close to the exterior ring.

WITH poly AS (
  SELECT 'POLYGON ((
      0 0, 10 0, 10 10, 0 10, 0 0
    ), (
      1 1, 1 2, 2 2, 2 1, 1 1
    ), (
      8.5 1, 8.5 2, 9.5 2, 9.5 1, 8.5 1
    ))'::geometry AS geom
),
rings AS (
  SELECT d.path[1] AS ring_no,
         ST_Boundary(d.geom) AS ring_geom
  FROM poly
  CROSS JOIN LATERAL ST_DumpRings(poly.geom) AS d
),
shell AS (
  SELECT ring_geom
  FROM rings
  WHERE ring_no = 0
),
holes AS (
  SELECT ring_no, ring_geom
  FROM rings
  WHERE ring_no > 0
)
SELECT h.ring_no,
       ST_AsText(ST_Intersection(ST_Buffer(s.ring_geom, 1.0), h.ring_geom)) AS close_part
FROM shell AS s
JOIN holes AS h
  ON ST_DWithin(s.ring_geom, h.ring_geom, 1.0);

 ring_no |                   close_part
---------+-------------------------------------------------
       1 | MULTILINESTRING((1 1,2 1),(1 2,1 1))
       2 | MULTILINESTRING((8.5 1,9 1),(9 2,9.5 2,9.5 1,9 1))