As far as I can see, the idea of the query is, for each area:
- split the area into branches, then union all branches (some of which will correspond to outer and some to inner contours) back together, producing the area with all holes filled,
- subtract the original area from the filled area, producing just the holes,
- split the holes into their own branches, filter out branches that are too large or too small (whichever you want to remove), then union what remains back, producing a subset of the holes,
- subtract the subset of the holes from the filled area, producing a new geom with the holes that have been filtered out in the previous step filled.
Here's my take on it, using the example MAP file from the thread you are referencing:
DELETE FROM [holey object 2 table];
INSERT INTO [holey object 2 table] ([ID], [Geom (I)]) -- just two fields
SELECT [ID], [Geom (I)] FROM [holey object table];
FUNCTION AreaSubtract(@g GEOM, @holes GEOM) GEOM AS (
GeomClip(@g, @holes, FALSE, 0)
FUNCTION AreaBranchesBigger(@g GEOM, @t FLOAT64) GEOM AS (
SELECT GeomMergeAreas([value]) FROM CALL GeomToBranches(@g)
WHERE GeomArea([value], 0) > @t
FUNCTION AreaFillHoles(@g GEOM) GEOM AS (
SELECT GeomUnionAreas([value]) FROM CALL GeomToBranches(@g)
FUNCTION AreaFillHolesSmallerPrep(@gfill GEOM, @g GEOM, @t FLOAT64) GEOM AS (
AreaSubtract(@gfill, AreaBranchesBigger(AreaSubtract(@gfill, @g), @t))
FUNCTION AreaFillHolesSmaller(@g GEOM, @t FLOAT64) GEOM AS (
AreaFillHolesSmallerPrep(AreaFillHoles(@g), @g, @t) -- fill holes just once
UPDATE [holey object 2 table]
SET [Geom (I)] = AreaFillHolesSmaller([Geom (I)], 10000);
Here's what is going on:
The first DELETE / INSERT just make a copy of 'Holey object table' in 'Holey object 2 table' so we can do as many test runs as we want without harming original data.
Then we have a bunch of functions:
- AreaSubtract is just a simplification of GeomClip which allows you to subtract one area from the other without specifying any additional parameters,
- AreaFillHoles is a function that fills all holes in an area -- it takes an area, splits it into branches, then unions all branches back, filling all holes,
- AreaBranchesBigger is a helper function that I use to filter the holes -- it takes an area representing the holes, splits it into branches, then merges the branches that are bigger than the specified threshold - I am using GeomMergeAreas instead of GeomUnionAreas here because we know that this function is called on a geom whose branches are disjoint, each branch is a separate shape,
- AreaFillHolesSmaller / AreaFillHolesSmallerPrep are functions that fill holes smaller than the specified threshold in an area -- the code takes an area, fills all holes, computes just the holes by subtracting the original area from the filled area, passes the result to AreaBranchesBigger to remove holes that are too small, then reconstructs the area by subtracting remaining holes from the filled area, I am using two functions with one of them calling the other so that the filled area only has to be computed once despite being used for two separate purposes.
The final UPDATE takes 'Holey object 2 table' and fills holes smaller than 10,000 (I ran a SELECT that computed the area of each branch to check what value will fill some of the holes, but not others).
Hope this helps.