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?