[GIS] Filling holes by acres threshold using PostGIS

postgis

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
FROM (
    SELECT gid, ST_ExteriorRing((ST_Dump(geom)).geom) As geom
    FROM layer
    ) s
GROUP BY id

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

Best Answer

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
GROUP BY id;

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.

Related Question