[postgis-users] Merge rasters with multiple bands

[Alexander Rumanovsk]

Made it after some effort.
Here is the final query, exporting to a .hex file:

COPY (
select encode(ST_AsPNG(r, ARRAY[1,2,3]), 'hex')
FROM (

WITH original AS (
SELECT 1 AS rid,
ST_AddBand(ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(100,100,0,0,1),
1, '8BUI', 16, 999), 2, '8BUI', 46, 999), 3, '8BUI', 184, 999) AS rast
), --0,0,255
empty AS (
SELECT 1 AS rid,
ST_AddBand(ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(256,256,0,0,1),
1, '8BUI', 0, 999), 2, '8BUI', 0, 999), 3, '8BUI', 0, 999) AS rast
)--255,255,0

select
ST_AddBand(
ST_MapAlgebra(original.rast,1,empty.rast,1,'([rast1.val] +
[rast2.val])','8BUI','UNION'),
ARRAY[ST_MapAlgebra(original.rast,2,empty.rast,2,'([rast1.val] +
[rast2.val])','8BUI','UNION'), ST_MapAlgebra(original.rast,
3,empty.rast,3,'([rast1.val] + [rast2.val])','8BUI','UNION')]
)
as r
FROM original,empty

) AS t
) TO '/tmp/test.hex';

after that, I just run:
xxd -p -r test.hex > test.png

And voilà.

--Rumanovsk

On Mon, Aug 15, 2016 at 1:15 PM, Pierre Racine <Pierre.Racine at sbf.ulaval.ca>
wrote:

> ST_Union works on overlapping rasters as well but maybe you have to
> provide two 3 bands rasters. If your rasters are tiled, you also have to
> GROUP BY tiles having the same alignment:
>
> GROUP BY ST_UpperleftX(rast), ST_UpperleftY(rast)
>
> ST_MapAlgebra() should work also. The expression should be equal to the
> raster with values and the extenttype to the 256x256 pixel one. They shoulp
> overlap.
>
> Pierre
>
>
> > -----Original Message-----
> > From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On
> Behalf
> > Of Alexander Rumanovsk
> > Sent: Wednesday, August 10, 2016 12:40 PM
> > To: postgis-users at lists.osgeo.org
> > Subject: [postgis-users] Merge rasters with multiple bands
> >
> > Hello all,
> >
> > I'm trying to merge a raster with 3 bands to an empty raster. The empty
> raster is
> > created with the query bellow:
> >
> > ST_AddBand(ST_MakeEmptyRaster(256,256,0,0,1), '8BUI'::text,200)
> >
> >
> > This empty raster is larger than the filled one (which is 100x100). I
> just need to
> > create a raster of 256x256 with the original raster (100x100)
> information and
> > the rest filled with empty information.
> > My first idea was to use ST_Union, but found out it just merges tiles,
> instead of
> > rasters.
> > Anyone have an idea how can I do that?
> >
> > thanks in advance
> > Rumanovsk
> _______________________________________________
> 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/20160815/5860059f/attachment.html>