PostGIS: ST_Clip problem with area which oversteps the raster

Question

Hello all the PostGIS wizards!

I want to programmatically clip raster with bounding box which is overstepping the border of raster. Ok, i can do it with ST_Clip, but the problem is that ST_Clip only returns the area where is raster data. I want the data from the whole bounding box area and if there is no data it should be same as nodata value (in my case it would be just zero).

Ok here is a really simple example picture i made with MS Paint just for this post:

In the st_clip i just use ST_MakePolygon for the use of clipping the raster with geometry:

ST_Clip(rast,ST_MakePolygon(ST_GeomFromText('LINESTRING(4920 70964,4920 71476,5432 71476,5432 70964,4920 70964)')),0)

So... Is there any way i can get also the 'empty area' into the clipped raster for example as a nodata value? Or is there a any way how i can get the wanted result?

I've been searching answer for this with no success so far.

Answer 1

This is not a clip. This is an overlay which can be done with ST_MapAlgebra().

Something like:

WITH targetrast AS (
    SELECT ST_AddBand(ST_MakeEmptyRaster(w, h, ulx, uly, pixelsize), '32BSI', -9999, -9999) rast
)
SELECT ST_MapAlgebra(a.rast, b.rast, '[rast1]', '32BSI', 'SECOND', NULL, '[rast1]') FROM yourrastertable a, targetrast b