[postgis-devel] 8BUI JPEG and PNG raster export

[Pierre Racine]

Hi Bborie,

>1. Does anyone mind if I implement this?

I certainly don't mind... I'm actually very happy. But we have to agree on the specs first.

>2. Should I create ST_Maximum and ST_Minimum as it doesn't look like
>the max and min values of a raster are cached?

Right. this is the main issue. The main purpose I see for having ST_Maximum and ST_Minimum is for computing the range from which we must map values to 8BUI. Do you see any other usage for the min and the max?

My concern is that on a tiles coverage a min and a max per tile would not allow you to build a meaningful colored JPEG coverage. You could do this just if you get the max of all the tile max and the min of all the tile min.

My other concern is that for this to be efficient you must cache them at the core level. You certainly don't want to compute them every time. 

I'm wondering if it is worth modifying the core to store those values... Maybe it would be more convenient to just have two functions to compute them and store them as two new columns in the coverage table. So cache them, but not in the core.

You could then pass those tile attributes to a ST_AsJPEG(rast, min, max) functions. In the case of a tiled coverage you could add two more columns "covmax" and "covmin" computed as the max of the tiles' maxs and the min of the tiles' mins representing the min and the max for the whole coverage and pass them instead. You would then get a meaningful tiled elevation coverage in JPEG.

By the way having ST_MinMax(rast) -> (int, int) would be much more efficient since you will  have to scan the raster only once. Not twice as if we have ST_Maximum(rast) and ST_Minimum(rast)

You would then access the min or the max like this:

SELECT (mm).min, (mm).max FROM (SELECT ST_MinMax(rast) mm FROM mycoverage) foo

So you would never do:

ST_AsJPEG(raster, quality, ST_Minimum(rast), ST_Maximum(rast)) 

but first two time consuming UPDATE queries:

UPDATE mytable SET (min, max) = SELECT ((mm).max, (mm).min)) FROM (SELECT ST_MinMax(rast) mm FROM mycoverage) foo
UPDATE mytable SET (covmin, covmax) = SELECT (max(max), min(min)) FROM mycoverage

(max and min here are PostgreSQL min and max aggregate functions) and then as many fast query as you like:

ST_AsJPEG(raster, quality, covmin, covmax) 

The colors would go from black to white... Maybe not that meaningful for a coverage. Should we have a way to define a custom color ramp?

>3. I'm thinking of using MapAlgebra to change the numerical range
>based upon the max and min pixel values and the convert the resulting
>raster to a GDAL raster.  Does this seem right?

Unfortunately I think MapAlgebra is too generic (and hence too slow) for your purpose. You could however implement a generic ST_Reclass() function being much faster than Mapalgebra (because you already know the expression to use and you can hardcode it. MapAlgebra is slow because it must parse the expression for every pixel).

ST_Reclass was originally planned as wrapper around MapAlgebra using a complex CASE WHEN expression. It should be much faster if you don't have to parse the expression or pass the values to a plpgsql custom function (using the new ST_MapAlgebraFct I'm supposed to implement soon based on David's work). Do you follow me?

And ST_Reclass would be so useful for so many other purposes... :-)

But maybe this would always anyway be too slow to reclass on the fly and you might want to first pregenerate a new table of three band rasters (R, G, B) with a complex ST_Reclass call allowing you to convert elevation raster first. This new flexible ST_Reclass could actually allow convert elevation rasters stored in any pixel type to... ...complex color ramps JPEGs!

Let say the signature for ST_Reclass would be something like:

ST_Reclass(rast raster, band int, reclassexpr text, pixeltype text, [band int, reclassexpr text, pixeltype text]...)

allowing to reclass one or many bands at the same time and that reclassexpr are strings looking like:

'rangefrom:rangeto, [rangefrom:rangeto]'

and ranges are 'int-int' or just 'int' (of float or double eventually)

then you could then reclass the first band to three new R, G and B band like this:

red: min(covmin, 0)-0:0,0-max(covmax, 0):0-255
green: min(covmin, 0)-0:200,0-max(covmax, 0):0-255
blue: min(covmin, 0)-0:255,0-max(covmax, 0)/2:0, max(covmax, 0)/2-max(covmax, 0):0-255

in SQL:

CREATE TABLE elevationcov AS
SELECT ST_Reclass(rast, 
                  1, LEAST(covmin, 0)::text || '-0:0,0-' || GREATEST(covmax, 0)::text || ':0-255', '8BUI',
                  1, LEAST(covmin, 0)::text || '-0:200,0-' || GREATEST(covmax, 0)::text' || ':0-255','8BUI',
                  1, LEAST(covmin, 0)::text || '-0:255,0-' || (GREATEST(covmax, 0)/2)::text' || ':0,' || (GREATEST(covmax, 0)/2)::text' || ':' || GREATEST(covmax, 0)::text || ':0-255', '8BUI')
FROM mycoverage

to get a blue-green-brown-white colored raster following the elevation and then 

SELECT ST_AsJPEG(rast) FROM elevationcov;

to quickly get a JPEG.

You could provide a wrapper around ST_Reclass providing predefined color ramps:

SELECT ST_Reclass(rast, 'elev-bgbw')

for the blue-green-brown-white example above.

In brief you don't want to implement ST_AsJPEG(rast, min, max) because generally it is not flexible enough to allow you to create a meaningful image. Reclass first to three nice bands and then pass it to ST_AsJPEG. This also means you don't want ST_AsJPEG to accept pixel types different than 8BUI. You HAVE to Reclass other pixel type raster first.

If you want to be able to reclass on the fly, ST_Reclass should be written in C (fast). 

If you prefer to have to create a table first, you could write ST_Reclass in plpgsql as a wrapper around Mapalgebra (slow).

I think this is a much better/useful option than the min and max parameters to AsJPEG.

Great work Bborie! and thanks again for your collaboration.

Pierre