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

[Pierre Racine]

Étienne,

The columnx and rowy ST_SetValues() parameters are not references to each pixel values. They are the coordinates only of the first upperleft pixel to be set. Other pixels coordinates are automatically derived from the size of the passed two dimensional array (newvalueset). That means you must first aggregate your values into a two dimensional array. Try this:

-- Create an array of array aggregator because array_agg does not support arrays...
CREATE AGGREGATE array_agg_mult(anyarray) (
    SFUNC = array_cat,
    STYPE = anyarray,
    INITCOND = '{}'
);

-- Aggregate the test.measure values into a two dimensional array and build the raster
WITH rows AS (
  SELECT colx, array_agg(v ORDER BY rowy) a
  FROM test.measure
  WHERE band = 1
  GROUP BY colx
), valarray AS (
  SELECT array_agg_mult(ARRAY[a] ORDER BY colx) a
  FROM rows
), raster AS (
  SELECT rast FROM test.rasters WHERE rid=1
), newraster AS (
SELECT ST_SetValues(rast, 1, 1, 1, valarray.a) rast
FROM valarray, raster
)
SELECT (ST_PixelAsPolygons(rast)).*
FROM newraster;

You might have to play a bit with the col and row ordering to get exactly what you want.

Pierre

> -----Original Message-----
> From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On
> Behalf Of Etienne B. Racine
> Sent: Friday, February 26, 2016 8:34 AM
> To: PostGIS Users Discussion
> Subject: [postgis-users] Create raster from row-col table
> 
> Hi list,
> 
> 
> I have raster data stored in a table row-col format as measure(rowy integer,
> colx integer, band integer, value float).
> 
> 
> I've tried loading a multidimensional array (row x col x bands) with no
> success, however building a 2d array (row x col) works but it seems
> contrived, especially once I need to stack the bands. I'm still figuring the
> best way to store the rasters, but I might end up storing 39 bands in a
> single raster. For now building an array isn't very fast, so I'm looking for
> speed improvement as well.
> 
> I wonder what's the most efficient way to populate a raster with data of
> this shape ? My actual method wouldn't work with sparse data and would
> require to pad the array with nodata values.
> 
> 
> I've put out a gist that explains my solution with sample data :
> https://gist.github.com/etiennebr/370a773029160c30d165#file-rowcol-
> raster-sql
> 
> 
> Thanks for your help,
> 
> Etienne
>