I have a layer that has holes in it and I would like to fill only the holes that are smaller than 1/10th of an acre

enter image description here

I know how to fill all the holes with this code

SELECT id, ST_Collect(ST_MakePolygon(geom)) As geom
    SELECT gid, ST_ExteriorRing((ST_Dump(geom)).geom) As geom
    FROM layer
    ) s

but how I was not able to figure out the acreage of the holes.

should I be utilizing https://postgis.net/docs/ST_DumpRings.html ?

not sure how to think this process through.

I was thinking use the above query to fill in all the holes than run an st_difference from the swiss cheese hole layer to the filled holed layer and the output would be those holes as polygons where I can calculate the acreage..but then what merge them back to the swiss cheese hole layer?

is there a simpler approach?

I ended up running this query

select geom,st_area(geom)/43560 acres from(
    select (st_dumprings((st_dump(shape)).geom)).geom geom 
    from agriculture.agricultural_grtr_250) t
where st_area(geom)/43560 > .1

but it did not yield the results I wanted. I wanted to keep the holes greater than .1 but it still filled them.

I guess I want the SQL code that will emulate the ArcGIS tool eliminate polygon part

1 Answer 1


I think you were getting close. This should do it:

WITH rings AS (
  SELECT id, (ST_DumpRings((st_dump(shape)).geom)).geom
    FROM agriculture.agricultural_grtr_250
SELECT id,ST_BuildArea(ST_Collect(geom)) geom
from rings
WHERE ST_Area(geom)/43560 > .1

Keep in mind that having the correct id (should be unique per geom) is important because this is what holds the rings together in the end.

  • cannot run because input is a multipolygon. table must be a polygon that is why I had st_dump initially
    – ziggy
    Aug 14, 2017 at 16:06
  • got it to work, just replace (ST_DumpRings(shape)).geom with (ST_DumpRings((st_dump(shape)).geom)).geom in the cte and I will accept the answer
    – ziggy
    Aug 14, 2017 at 16:07
  • 3
    so the key to this query is using st_buildarea() because of this "This function will assume all inner geometries represent holes"
    – ziggy
    Aug 14, 2017 at 17:39
  • 1
    Hi, I am using st_DumpRings and I keep getting the following error: Input is not a polygon - even after using ST_MakeValid on the layer - any ideas why this is? Oct 21, 2018 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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