Build a multiband raster from row-col data

rowcol-raster.sql

DROP SCHEMA test CASCADE;
CREATE SCHEMA test;

CREATE TABLE test.measure AS
SELECT 
	r as rowy, c as colx, b as band,
	round(random() * 100)::float as v
FROM 
	generate_series(1, 6) as r
	CROSS JOIN LATERAL 
		generate_series(1, 5) as c
	CROSS JOIN LATERAL 
		generate_series(1, 2) as b;

--DROP TABLE test.rasters;
CREATE TABLE test.rasters AS
SELECT 1 as rid, ST_AddBand(ST_MakeEmptyRaster(5, 6, 1, 1, 1, 1, 0, 0, 0),
				ARRAY[
			ROW(NULL, '32BF', -9999, -9999) -- 1
				]::addbandarg[]) as rast;
-- add a two-band raster
INSERT INTO test.rasters(rid, rast)
SELECT 2 as rid, ST_AddBand(ST_MakeEmptyRaster(5, 6, 1, 1, 1, 1, 0, 0, 0),
				ARRAY[
			ROW(NULL, '32BF', -9999, -9999), -- 1
			ROW(NULL, '32BF', -9999, -9999) -- 2
				]::addbandarg[]) as rast;

-- this would be my prefered way of generating a raster
-- sadly it doesn't work
SELECT ST_SetValues(rast, band, --bandnum, 
			  colx, --columnx, 
			  rowy, --rowy, 
			  v) --newvalue
			  as rast
	 FROM test.measure,
		(SELECT * FROM test.rasters WHERE rid=1) as r;
	
--build multi-band array
CREATE VIEW test.mbarray AS
SELECT array_agg(the_rows.ra) as matrix, band
FROM (	SELECT array_agg(v) as ra, rowy, band
	FROM (select * from test.measure order by band, colx) as m
	GROUP BY rowy, band
	ORDER BY band, rowy) as the_rows
GROUP BY band
ORDER BY band;


-- one band at the time 
-- seems to work, but doesn't scale for large number of bands
SELECT st_dumpvalues(st_addband(band1.rast, band2.rast)) as rast
FROM
	(SELECT ST_SetValues(r.rast, 	1, --bandnum, 
					1, --columnx, 
					1, --rowy, 
					a.matrix) --newvalue
					as rast
	 FROM 
		(SELECT * FROM test.rasters WHERE rid=1) as r, 
		(SELECT * FROM test.mbarray WHERE band=1) as a) as band1,
	(SELECT ST_SetValues(r.rast, 	1, --bandnum, 
					1, --columnx, 
					1, --rowy, 
					a.matrix) --newvalue
					as rast
	 FROM 
		(SELECT * FROM test.rasters WHERE rid=1) as r, 
		(SELECT * FROM test.mbarray WHERE band=2) as a) as band2;


-- feed multiband array to addband
SELECT ST_DumpValues(rast)
FROM
	(SELECT ST_SetValues(r.rast, 	1, --bandnum, 
					1, --columnx, 
					1, --rowy, 
					a.matrix) --newvalue
					as rast
	 FROM 
		(SELECT * FROM test.rasters WHERE rid=2) as r, 
		(SELECT array_agg(matrix) as matrix FROM 
			(SELECT * FROM test.mbarray ORDER BY band) as m) as a) as bands;
-- NOTICE:  New values array must be of 1 or 2 dimensions.  Returning original raster