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

[Etienne B. Racine]

Many thanks Pierre,

I can create a 100 x 200 x 1000 raster in about 30 seconds. That's quite a
lot of pixels organized !

For the archives, here's the complete code:
--
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, 100) as r
  CROSS JOIN LATERAL generate_series(1, 200) as c
  CROSS JOIN LATERAL generate_series(1, 1000) as b;

CREATE TABLE test.rasters AS
SELECT 1 as rid, ST_AddBand(ST_MakeEmptyRaster(200, 100, 1, 1, 1, 1, 0, 0,
0), --width, height
  ARRAY[ ROW(NULL, '32BF', -9999, -9999) -- 1 ]::addbandarg[]) as rast;

CREATE TABLE test.the_raster AS
WITH rw AS (
  SELECT band, rowy, array_agg(v ORDER BY colx) a
  FROM test.measure GROUP BY band, rowy ),
valarray AS (
  SELECT band, array_agg_mult(ARRAY[a] ORDER BY rowy) a
  FROM rw 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 rast FROM newraster;

Etienne

Le lun. 29 févr. 2016 à 10:31, Pierre Racine <Pierre.Racine at sbf.ulaval.ca>
a écrit :

> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160301/842258d2/attachment-0001.html>