[raster]: ST_Union that takes 2 rasters

[robe]

I'm finding myself constantly needing to do a union of just two rasters. So I often end up doing ugly things like

SELECT ST_Union(f.rast)
FROM (SELECT rast
FROM table1 INNER JOIN table2 ON ST_Intersects(table1.rast,1,table2.geom)
WHERE table2.gid =1
UNION ALL 
SELECT ST_AsRaster(somegeom, rast,100)
FROM ST_Intersects(table1.rast,1,table2.geom)
WHERE table2.gid = 1) As f

When all I want to do is this

SELECT ST_Union(rast, ST_AsRaster(somegeom,rast) )
FROM table1 INNER JOIN table2
 ON ST_Intersects(rast,1, table2.geom)
WHERE table2.gid = 1

In theory I could use MapAlgebra but MapAlgebra blows my mind with all the permutations of it.

Note geometry has an ST_Union(geom1,geom2) so it would be in line with what we do with geometry already except we'd have an optional expression type

So I'm think the proto would look like

ST_Union(rast1,rast2, expressiontype = 'LAST')

[Bborie Park]

Based on the ST_Union signature, would the function be unioning all bands of rast1 and rast2?

[robe]

thinking about this more ST_Union geometry has a ST_Union(geometry[]) array version. ​http://postgis.net/docs/manual-dev/ST_Union.html

That might be more valuable than ST_Union(rast,rast) if we had a ST_Union(rast[],..)

Because I could do crazy stuff like

ST_Union(ST_Union(ARRAY[rast1,rast2,rast3], 'SUM'), 'MEAN')

Such a case would be useful if I want to convert multiple bands (different themes of same space) to a single band and then group by some division.

[robe]

Milestone renamed