[postgis-users] Better way of removing pixelation with postgis raster

[Tom van Tilburg]

Hi list,

Im trying to create a landuse map for a customer that requires the 
removal of pixelation (isolated islands of pixels, possible larger than 
1 pixel).
To make things complicated, different pixelclasses have different 
tresholds for removal.
For instance, class 1 is removed when the combined pixels are less than 
4, whereas class2 is removed when the combined pixels are less than 50. 
Furthermore, some classes count diagonal neighbours as 'touching' 
whereas other classes discard them.

My solution is given below.
As you can see there is an expensive buffer-union-buffer trio to make 
sure I connect the correct polygons before I can calculate their area.
This calculation is part of a big raster calculation. All of the 
calculations are going very fast, except this one.
Anyone with an idea to make it faster? In the end all I need is removal 
of isolated islands of pixels so any matrix calculation would be fine as 
well.

thanks, Tom

------------------------------------------
WITH
--Dump to polygons
polygons As
(
         SELECT (ST_DumpAsPolygons(rast)).geom geom, 
(ST_DumpAsPolygons(rast)).val val
         FROM out_landuse_rast_30
)
--Merge polygons
,polygons_dissolved As
(
         SELECT 
ST_Buffer((ST_Dump(ST_Union(ST_Buffer(geom,1)))).geom,-1) geom, val
         FROM polygons
         GROUP BY val
)

--Select polygons that should go out
,polygons_out As
(
         SELECT St_Collect(geom) geom FROM polygons_dissolved
         WHERE
                 (ST_Area(geom) >= ((30*30) * 4) --4 pixels
                 AND val IN (1)
                 )
         OR
                 (ST_Area(geom) >= ((30*30) * 50) --50 pixels
                 AND val IN (2)
                 )
)

--Create raster (mask) that should go out
SELECT 1 As rid, ST_AsRaster(a.geom, b.rast, '8BUI',1,0,false) rast
FROM polygons_out a, emptycanvas30 b;
------------------------------------