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.

migrated from stackoverflow.com Dec 16 '13 at 23:26

This question came from our site for professional and enthusiast programmers.


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.

If your rasters are 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 this to work your tiles have to be well aligned. If not then just use ST_Intersects(a.rast, b.rast) but that should be slower.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy