Getting inner holes in a multipolygon using PostGis

Question

Currently I have a database with soil data. That soil data is made of multipolygons which have holes in them. For example,

I need to select the outer most ring and all of the inner rings for the data in the area around a point. This is my select so far. It selects all polygons in an area around a point (example is around NY).

WITH point AS (
    SELECT ST_SetSRID(ST_MakePoint(-74.0060, 40.7128), 4326) AS geom
),
buffered_point AS (
    SELECT ST_Buffer(geom, 1.0) AS geom
    FROM point
)
SELECT s.geom AS geom
FROM soil s
JOIN buffered_point bp ON ST_Intersects(s.geom, bp.geom);

How can I get all of the inner holes in the multipolygon?

I tried using ST_Dump, ST_DumpRings, ST_InteriorRingN on the geom of the select without success.

Answer 1

You can either 1) use ST_DumpRings (https://postgis.net/docs/ST_DumpRings.html) and get a set of all the rings or 2) find the number of internal rings with ST_NumInteriorRings (https://postgis.net/docs/ST_NumInteriorRings.html) and iterate over each one in your code. It should be quite straightforward - if you need more help, let me know.

Answer 2

Thanks for the help. It was simple as using ST_DumpRings and the select looks like dis:

WITH point AS (
    SELECT ST_SetSRID(ST_MakePoint(-74.0060, 40.7128), 4326) AS geom
),
buffered_point AS (
    SELECT ST_Buffer(geom, 1.0) AS geom
    FROM point
)
SELECT (ST_DumpRings((ST_Dump(s.geom)).geom)).geom AS geom,s.snum AS soilNumber, s.faosoil AS soilType
FROM soil s
JOIN buffered_point bp ON ST_Intersects(s.geom, bp.geom)