I have three rasters covering the same area but with different pixel sizes (Sentinel 2 tiles if it matters). My goal is to "stack" them, such that pixel[i,j] in the resulting raster contains [bands from first raster, bands from second raster, bands from third raster]. This functionality is also accomplished using gdal_merge.py with the -separate flag, but I'd like to do it in PostGIS.
I seem to be getting some of the way doing the following:
CREATE TABLE results.tmp AS ( SELECT h.rast FROM results.hires h UNION ALL SELECT ST_Resample(m.rast, h.rast, 'bilinear') FROM results.midres m, results.hires h UNION ALL SELECT ST_Resample(l.rast, h.rast, 'bilinear') FROM results.lowres l, results.hires h)
The result seems to be an upsampling of the raster in results.midres, with all of the other data thrown out. What is the correct way of doing this?
(Needless to say I tried figuring out how to do it with ST_Union, but it's not straightforward to me how to get it to work with multiple raster files).