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

Tom van Tilburg tom.van.tilburg at gmail.com
Wed Nov 23 06:55:30 PST 2011


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

On 23-11-2011 15:05, Pierre Racine wrote:
> Thanks for this feedback Tom.
>
> Could you tell us more on 1) What you do exactly? 2) The characteristics of your dataset (size, pixeltype) and the performance of ST_Union?
>
> You are the first on earth one to do raster aggregation of real rasters in a spatial database!
>
> I developed and tested ST_Union only on 100x100 rasters...
>
> Pierre
>
>> -----Original Message-----
>> From: postgis-devel-bounces at postgis.refractions.net [mailto:postgis-devel-
>> bounces at postgis.refractions.net] On Behalf Of Tom van Tilburg
>> Sent: Wednesday, November 23, 2011 8:54 AM
>> To: postgis-devel at postgis.refractions.net
>> Subject: Re: [postgis-devel] ST_MapAlgebraExpr(): Raster aggregates?
>>
>> Tested and working as expected.
>> This is a very usefull addition. Thanks for the effort all!
>>
>> Tom
>>
>>
>>




More information about the postgis-devel mailing list