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

[Etienne B. Racine]

Thanks Pierre,

I didn't know that array_agg didn't work with arrays, however on I do get a
3d array using array_agg (PG 9.4), what's wrong exactly with that array ?
It seems to load fine with st_setvalues (if I use a single band).

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.

(I fixed the raster dimensions, row and col were inverted, so that might be
the reason why it looked odd to aggregate on col then row.)

Etienne

Le ven. 26 févr. 2016 à 11:04, Pierre Racine <Pierre.Racine at sbf.ulaval.ca>
a écrit :

> É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
> >
>
> _______________________________________________
> 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/20160226/236d9c21/attachment-0001.html>