[postgis-users] PostGIS ST_MapAlgebra Assistance

[Pierre Racine]

Assuming your three rasters would be in three different tables and not tiled, it could look like this: 

WITH 
solandcov AS (SELECT ST_MapAlgebra(a.rast, b.rast, '([rast1] >= 1491 and [rast2] <= 70)::int', "4BUI") FROM sol_gain_clipped a, can_cov_01 b),
creek AS (SELECT ST_MapAlgebra(a.rast, b.rast, '([rast1] and [rast2] <= 10)::int', "4BUI") FROM solandcov a, imp_sur_10_french_creek b)
SELECT ST_AsTiff(rast) FROM creek;

I'm using the last variant of ST_MapAlgebra from this page:

http://postgis.refractions.net/documentation/manual-svn/RT_ST_MapAlgebra_expr.html

If your rasters ares tiled you have to add a WHERE clause to the two first statements to make sure only aligned tiles get involved in ST_MapAlgebra. I generally use 

WHERE ST_UpperLeftX(a.rast) = ST_UpperLeftX(b.rast) AND ST_UpperLeftY(a.rast) = ST_UpperLeftY(b.rast)

for that your tiles have to be well aligned. If not then just use ST_Intersects(a.rast, b.rast) but that should be slower.

Pierre

> -----Original Message-----
> From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-
> bounces at lists.osgeo.org] On Behalf Of Jason Coombs
> Sent: Monday, December 16, 2013 9:22 AM
> To: postgis-users at lists.osgeo.org
> Subject: [postgis-users] PostGIS ST_MapAlgebra Assistance
> 
> Good Morning,
> I would like to perform the equivalent of the below ArcGIS raster calculator
> equation in PostGIS using the ST_MapAlgebra function and return it as a tif
> using ST_AsTIFF.
> 
> Con(("Sol_gain_clipped.tif" >= 1491)  &  ("can_cov_01.tif" <= 70)  &
> ("imp_sur_10_French_Creek.tif" <= 10),1,0)
> 
> The equation uses three single band rasters with pixel value specifications
> for each one, and returns a single band raster with values of 1 for pixels
> meeting all three requirements, and 0 for pixels that do not. Any help in
> how to write the SQL, including PL/pgSQL, would be greatly appreciated.
> 
> Best, Jason
> 
> 
> 
> Jason A. Coombs
> 
> Department of Environmental Conservation/US Forest Service
> 
> 201 Holdsworth Hall
> 
> University of Massachusetts
> 
> Amherst, MA 01003
> 
> 
> 
> https://bcrc.bio.umass.edu/pedigreesoftware/
> <https://bcrc.bio.umass.edu/pedigreesoftware/>
> 
> 
> 
> Phone: 413-545-1845
> 
> Fax: 413-545-1860
> 
> Email: jcoombs at cns.umass.edu <mailto:jcoombs at cns.umass.edu>
> 
>