How to remap a raster to its min / max values in PostGIS?

Question

I have a table of 16-bit signed (!) integer heightmaps (SRTM tiles) loaded into PostGIS. This website showed me how to export a raster as a PNG to a file:

COPY (SELECT encode(ST_AsPNG(raster), ‘hex’) AS png FROM table_with_raster) TO ‘/tmp/myimage.hex’;

xxd -p -r /tmp/sf_colors.hex > /tmp/sf_colors.png

However, because my image data is in signed integers, ST_AsPng(rast) fails because it's signed integers, but ST_AsPng() needs values from 0 to 256 (8-bit, unsigned). So I want to reclassify the image, so that 0 = minimum value and 256 = maximum value.

For that reason, I created a new table and calculated the statistics of the image with ST_SummaryStatsAgg:

CREATE TABLE heightmap_data_stats as  (
    SELECT rast_id, (stats).min, (stats).max, rast 
    FROM (
        SELECT ST_SummaryStatsAgg(rast, 1, true) AS stats, rast_id, rast 
        FROM heightmap_data GROUP BY rast_id
    ) AS foo
);

Now I have something like this:

id      min     max   rast
-----------------------------
0       85      1232  [data]
1       37      677   [data]
3       95      625   [data]
2       173     977   [data]

Now I tried:

SELECT encode(ST_AsPng(
    ST_Reclass(rast, 1, 'min-max:0-256', '8BUI', 0)
), 'hex') FROM extra_data.heightmap_data_stats;

... in order to map the image data from 85..1232 -> 0..256 for example. However, it doesn't work - I don't know the syntax to tell PostGIS to input the actual "min" and "max" values in the ST_Reclass argument, so I only get an error "invalid reclassarg" and the original raster is returned.

How do I write a query, that, for each raster in the table, reclassifies it to an 0-256 range?

Answer 1

Replace

min-max:0-256

with

((ST_SummaryStats(rast, 1, true)).min)::text || '-' || ((ST_SummaryStats(rast, 1, true)).max)::text || ':0-256'