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

2018-03-05 Thread Regina Obe 
I think what you are looking for is ST_Union (.. SUM)  note this has union 
types – FIRST, MIN, MAX, COUNT, SUM, MEAN, RANGE

 

http://postgis.net/docs/manual-2.4/RT_ST_Union.html

 

 

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
David M. Kaplan
Sent: Monday, March 05, 2018 10:03 AM
To: postgis-users@lists.osgeo.org
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

 

 

 

-- 

**

David M. Kaplan

Charge de Recherche 1

 

Institut de Recherche pour le Developpement (IRD)

UMR MARBEC (IRD/Ifremer/CNRS/UMII)

av. Jean Monnet

CS 30171

34203 Sete cedex

France

 

Email: david.kap...@ird.fr  

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

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

 

http://www.umr-marbec.fr/kaplan-david.html

http://www.davidmkaplan.fr/

**

___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

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

2018-03-05 Thread David M. Kaplan 
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




-- 
**David M. KaplanCharge de Recherche 1
Institut de Recherche pour le Developpement (IRD)UMR MARBEC 
(IRD/Ifremer/CNRS/UMII)av. Jean MonnetCS 3017134203 Sete cedexFrance
Email: david.kaplan@ird.frPhone: +33 (0)4 99 57 32 25Fax: +33 (0)4 99 57 32 95
http://www.umr-marbec.fr/kaplan-david.htmlhttp://www.davidmkaplan.fr/**___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users