[postgis-users] Aggregating rasters by adding and other confusions

Regina Obe lr at pcorp.us
Mon Mar 5 09:05:40 PST 2018

I think what you are looking for is ST_Union (.. SUM)  note this has union types – FIRST, MIN, MAX, COUNT, SUM, MEAN, RANGE






From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of David M. Kaplan
Sent: Monday, March 05, 2018 10:03 AM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] Aggregating rasters by adding and other confusions




I have recently started working with the postgis raster functionality. In general, I have found this really useful and have been able to do some neat things fairly simply with this raster functionality. Nevertheless, there are a few basic things that I am confused about and I was hoping someone could give me a hand.


(1) First of all, I have a table with a bunch of rasters that have the same extent, alignment, scale, etc. and I want to aggregate them together into a single raster using pixel-by-pixel addition. It seems like there should be a function to do this, but I can't find one. Is there an aggregate "ST_MapAlgebra" function? 


Given that I couldn't find one, I defined an aggregate function as follows:


CREATE OR REPLACE FUNCTION AddRasters(r1 raster, r2 raster)
       RETURNS raster AS
SELECT ST_MapAlgebra($1,$2,'[rast1]+[rast2]');
    sfunc = AddRasters,
    stype = raster

(2) This seems to work, but it has the unexpected behavior that it replaces 0 values with NULL. In my case, this is fine, but I am wondering why it does this? I can't find anything that indicates that it should be replacing zeros with NULL. Here is the metadata associated with one of my rasters (the others are similar):


# SELECT ST_BandMetadata(rast), ST_Metadata(rast), ST_SummaryStats(rast) FROM blah;
-[ RECORD 1 ]---+-------------------------------------------------------
st_bandmetadata | (16BUI,,f,)
st_metadata     | (-180,90,360,180,1,-1,0,0,4326,1)
st_summarystats | (64800,417,0.00643518518518519,0.223617719977485,0,46)

I have not defined a nodataval for these layers and the original layers have no NULL values.


(3) Is there a postgis command to turn all the NULL values back into zeros?


(4) I was also considering just defining the '+' operator for raster + raster to be pixel-by-pixel addition. Is there any reason that I wouldn't want to do this?


(5) Finally, I have been visualizing results with QGIS using the DB Manager. However, I don't see how to select a row from a raster table and incorporate just that row into the canvas. Is there a way to do this?


Thanks for the assistance,







David M. Kaplan

Charge de Recherche 1


Institut de Recherche pour le Developpement (IRD)


av. Jean Monnet

CS 30171

34203 Sete cedex



Email: david.kaplan at ird.fr <mailto:david.kaplan at ird.fr> 

Phone: +33 (0)4 99 57 32 25

Fax: +33 (0)4 99 57 32 95





-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20180305/4cc330bf/attachment.html>

More information about the postgis-users mailing list