<html><head></head><body><div>Hi,</div><div><br></div><div>Yes, this does seem to be the solution. For completeness, the following did the same thing as my aggregate function:</div><div><br></div><div>SELECT ST_Union(rast,1,'SUM') FROM blah;</div><div> </div><div>This includes replacing 0's with NULL's - ST_Union had the same behavior at my aggregate function. </div><div><br></div><div>Can you explain this? For my sum, this isn't a big deal, but ST_Union(... 'MEAN') seems to be treating 0's at NULL values, thereby removing them from the mean calculation:</div><div><br></div><pre>db=# SELECT (ST_SummaryStats(ST_Union(rast,1,'SUM'))).sum FROM blah; sum </pre><pre>------</pre><pre> 2792</pre><pre>(1 row)</pre><pre><br></pre><pre>db=# SELECT (ST_SummaryStats(ST_Union(rast,1,'MEAN'))).sum FROM blah;</pre><pre> sum </pre><pre>------</pre><pre> 1346</pre><pre>(1 row)</pre><pre><br></pre><div></div><div>The table "blah" has 3 rasters with no null values, so I would expect the sum of the mean to be 1/3 the sum of the sum, but clearly this is not the case.</div><div><br></div><div>I tried playing around with the no data value for my rasters to see if this would change things. It does change things, but not in any way I can explain:</div><div><br></div><pre>fads=# SELECT (ST_SummaryStats(ST_Union(ST_SetBandNoDataValue(rast,1,0),1,'MEAN'))).sum FROM blah;</pre><pre> sum </pre><pre>------</pre><pre> 2025</pre><pre>(1 row)</pre><pre><br></pre><pre>fads=# SELECT (ST_SummaryStats(ST_Union(ST_SetBandNoDataValue(rast,1,-999),1,'MEAN'))).sum FROM blah;</pre><pre> sum </pre><pre>------</pre><pre> 2025</pre><pre>(1 row)</pre><pre><br></pre><div></div><div>Setting the no data value has no effect on the sum of the sum.</div><div><br></div><div>Does any of this make sense?</div><div><br></div><div>Thanks,</div><div>David</div><div><br></div><div></div><div><br></div><div>On Mon, 2018-03-05 at 12:00 -0800, postgis-users-request@lists.osgeo.org wrote:</div><blockquote type="cite" style="margin:0 0 0 .8ex; border-left:2px #729fcf solid;padding-left:1ex"><pre>Date: Mon, 5 Mar 2018 12:05:40 -0500
From: "Regina Obe" <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>>
To: "'PostGIS Users Discussion'" <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>>
Subject: Re: [postgis-users] Aggregating rasters by adding and other
confusions
Message-ID: <<a href="mailto:001f01d3b4a4$30d1fa20$9275ee60$@pcorp.us">001f01d3b4a4$30d1fa20$9275ee60$@pcorp.us</a>>
Content-Type: text/plain; charset="utf-8"
I think what you are looking for is ST_Union (.. SUM) note this has union types – FIRST, MIN, MAX, COUNT, SUM, MEAN, RANGE
<a href="http://postgis.net/docs/manual-2.4/RT_ST_Union.html">http://postgis.net/docs/manual-2.4/RT_ST_Union.html</a>
From: postgis-users [<a href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>] On Behalf Of David M. Kaplan
Sent: Monday, March 05, 2018 10:03 AM
To: <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
Subject: [postgis-users] Aggregating rasters by adding and other confusions
Hi,
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
$BODY$
SELECT ST_MapAlgebra($1,$2,'[rast1]+[rast2]');
$BODY$ LANGUAGE 'sql' IMMUTABLE STRICT;
CREATE AGGREGATE sum (raster)
(
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</pre></blockquote></body></html>