[postgis-users] ST_SetValue problem

[Pierre Racine]

> The result is that I change raster values in only 1 of the 81 polygon cells that I have in
> schema.data_vector. What am I missing here? Thanks for your help! Keep
> up the great work!

UPDATE schema.data_raster SET rast = ST_SetValue(rast,1, geom, 100)
FROM schema.data_vector

I don't fully understand the behavior of UPDATE in that case but, or it join with only one row of schema.data_vector, or it join with all of them but only the last one is used to update schema.data_raster. Or actually probably schema.data_raster is updated many times but always from the original value. Not incrementally.

Hugues suggestion:

SELECT rid,  ST_SetValue(rast,1, geom, 100) AS RAST
FROM schema.data_raster, schema.data_vector

Would produce as many raster rows as you have geometries...

There are two better ways to do this...

The first one is to ST_Union the rasterizization of the geometries and to union it with the existing raster:

SELECT ST_Union(rast, 'LAST') rast
FROM 
(SELECT rast FROM schema.data_raster
UNION ALL
SELECT ST_Union(ST_AsRaster(geom, rast, '16BSI', elevation, -10000)) rast
FROM schema.data_raster, schema.data_vector
) foo

You can probably assign this single result as an UPDATE to your table..

The second one is to install the PostGIS Add-ons from https://github.com/pedrogit/postgisaddons/releases
and to do something like:

SELECT ST_ExtractToRaster(ST_AddBand(ST_MakeEmptyRaster(rast), '16BSI'), 'schema', 'geomtable', 'data_vector', 'elevation', 'MEAN_OF_VALUES_AT_PIXEL_CENTROID') rast FROM schema.data_raster;

You can probably also assign this single result as an UPDATE to your table...

Pierre