1

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.

How do I write the SQL, including PL/pgSQL?

Improve this question
0

1 Answer 1

Reset to default
7

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.

Improve this answer