1

I have 2 raster tables with one band. What I want to do is clip the rasters with a polygon and multiply pixel values with different values and combine those multiplied rasters into a new one. The main goal is to analyze DEM data for the specified areas. I can do those things with separate queries which you can find below. But I was wondering if it is possible to achieve all those things in one query with ST_MapAlgebra

--create tables for clipping rasters

CREATE TABLE schema.t1(
    rid integer primary key,
    rast raster,
    new text
);  
CREATE TABLE schema.t2(
    rid integer primary key,
    rast raster,
    new text
);  

--clip the data with attribute-based polygon and insert into table

INSERT INTO schema.t1(rast, new, rid)   
SELECT (ST_Clip(rast, sf.geom, TRUE)) as new, rast, rid   
FROM schema.rast1
JOIN schema.polygon sf ON ST_Intersects(rast1.rast,sf.geom)

WHERE sf.name_1 = 'Van'
;

--clip the data and insert into a different table for other algebra operation

INSERT INTO schema.t2(rast, new, rid)  
SELECT (ST_Clip(rast1.rast,sf.geom, TRUE)) as new, rast, rid
FROM schema.rast
JOIN schema.polygon sf ON ST_Intersects(rast1.rast,sf.geom)
WHERE sf.name_1 = 'Van'
;

-- now update the pixels with multiplication 

UPDATE schema.t1
    set rast = ST_MapAlgebra(rast, 1,'32BF'::text, '[rast.val] * 3'::text, -1);
                                    
UPDATE schema.t2
    set rast = ST_MapAlgebra(rast, 1,'32BF'::text, '[rast.val] * 5'::text, -1);


-- now create another table and insert multiplied tables
CREATE TABLE schema.t3(
  rid SERIAL primary key, rast raster
);

INSERT INTO schema.t3(rast)
SELECT st_mapalgebra(b1.rast,1, b2.rast, 1,'([rast2] + [rast1.val])') 
AS rast

FROM schema.t1 AS b1 cross join schema.t2  AS b2;
  • I cannot see how you could squeeze all this into one ST_MapAlgebra. If you build a function with your code, it might be easier for reuse. – Jakob Jul 7 at 11:51
  • Thank you for your reply. I'm not familiar with building a function in PostGIS, but I'll definitely look into it. – mr omer Jul 8 at 9:49

Your Answer

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

Browse other questions tagged or ask your own question.