[postgis-users] Create raster from row-col table

[Pierre Racine]

Hi Etienne,

> Still, I can't figure how to load an arbitrary number of bands using your
> snippet. If I remove WHERE band=1 I still get a unique band.

This query will work for many bands and will aggregate all of them in a single raster:

WITH rows AS (
  SELECT band, colx, array_agg(v ORDER BY rowy) a
  FROM test.measure
  GROUP BY band, colx
), valarray AS (
  SELECT band, array_agg_mult(ARRAY[a] ORDER BY colx) a
  FROM rows
  GROUP BY band
), raster AS (
  SELECT rast FROM test.rasters WHERE rid=1
), newraster AS (
  SELECT ST_AddBand(NULL, array_agg(ST_SetValues(rast, 1, 1, 1, valarray.a) ORDER BY band), 1) As rast 
  FROM valarray, raster
)
SELECT band, (ST_PixelAsPolygons(rast, band)).*
FROM newraster, generate_series(1,2) band;

this will work only if they all have the same width/height.

Pierre