[postgis-devel] ST_MapAlgebraExpr(): Raster aggregates?

[Bborie Park]

On Wed, Nov 23, 2011 at 6:55 AM, Tom van Tilburg
<tom.van.tilburg at gmail.com> wrote:
> Pierre,
>
> I feel honored about being the first and I'd be happy to share our
> experience.
>
> At the moment we are using the ST_Union(raster) for 2 reasons:
> 1) Vector maps with land-use to raster land-use map
> The fire-model 'farsite' runs with an input of several raster datasets.
> While we have a vector map with land-use covering all of the Netherlands,
> the model needs different raster sets on different occasions. Therefore we
> are looking for a way to automate the creation of custom raster datasets
> with land-use.
> With the help of ST_Union(raster) it is fairly easy to convert a subset of
> vector data to several raster layers with distinct values and press them
> together with St_Union into 1 layer.
>
> An image of just over 4 million pixels with 11 discrete raster values takes
> around 1 minute on my 2.3 Ghz, 6Gig. mem. laptop.
> I can sent the corresponding SQL if you like.
>
> 2) Glueing together tiled rasters
> Not sure if this is the right tool for it though, but we are calculating
> average pixel values within an overlaying polygon. When a polygon overlaps 2
> or more raster tiles, we need to union the tiles first to make a proper
> average.
> The complete calculation works as follows:
>
> (ST_SummaryStats(
>    (ST_Union(
>        ST_MapAlgebraExpr(
>            ST_AsRaster(a.geom, b.rast, '32BF')
>            ,b.rast
>            ,'rast2', '32BF','INTERSECTION','0','0',0
>        )
>    )).rast
> )).mean As avg_height
>
> Doing this calculation with 600,000 polygons over a 5m grid for all of the
> Netherlands takes over 8 hours.
>
> Hope this adds something to the experience. Let me know when you'd like to
> learn more.
>
> Cheers,
>  Tom
>

I'll have to explore turning the plpgsql version of ST_Union into a C
function.  Assuming 600,000 rasters (geometry -> rasters) are unioned,
C should be faster as that should be 599,999 deserializations and
serialiations of a probably growing "working" raster.

-bborie