2

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?

3
  • What's the output of ST_BandPixelType() in your original data? This may help. Commented Apr 4, 2020 at 13:07
  • @s.k it's '16BSI' - 16 bit signed integer Commented Apr 4, 2020 at 22:11
  • I've temporarily solved this problem with ST_Grayscale(rast, 1,1,1) - which does the remapping for me. Still, not a clean solution. Commented Apr 5, 2020 at 9:55

1 Answer 1

1

Replace

min-max:0-256

with

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.