Convert DEM elevation points from centimeters to meters

Question

I would like to convert a large DEM stored in PostGIS which contains elevation values in centimeters to elevation in meters.

I found an easy way by updating each point like this:

UPDATE mydem a
SET rast = ST_SetValue(rast, x, y, val / 100)
FROM (SELECT rid, x, y, val FROM (SELECT rid, (ST_PixelAsPoints(rast)).* FROM mydem) t) r
WHERE a.rid = r.rid;

It works but very slowly, much too slow to process large tables. Is there is any faster way to achieve this ?

Answer 1

BradHards comment is good, but it can be even simpler with the MapAlgebraExpression variant. For these simple uses-cases you don't need to add a callback function but you can just add an expression to the function. In your case:

UPDATE mydem a
SET rast =
    ST_MapAlgebra(rast, 1, NULL, '[rast.val] / 100')
FROM foo