July 23, 2012

A Slow, Yet 1000x Faster, Alternative to ST_Union(raster) in PostGIS!

As a first post in this new blog I'd like to share an alternative method to merge a tiled raster coverage into a single row raster. For those who followed PostGIS Raster development, you have seen how we now use the very generic ST_Union() aggregate function to merge rasters in a very identical way you would merge geometries:

SELECT ST_Union(rast) rast
FROM tiledrastertable

ST_Union(raster) is a PL/pgSQL aggregate using ST_MapAlgebraExpr(raster, raster, expression) as a state function. It is very flexible in that you can add a parameter to specify how to aggregate the pixel values when two or more pixels overlap. You can use 'FIRST', 'LAST', 'MAX', 'MIN' 'MEAN', 'SUM'. 'LAST' is the default.