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

Tue Apr 5 09:41:15 PDT 2011

Hey Pierre,

Sorry about the belated response.  I had to reread your response several 
times to fully digest everything...

On 04/02/2011 08:35 PM, Pierre Racine wrote:
> 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.
>

So, how do we get the ball rolling on agreeing on a spec?

>> 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?
>

I don't see to many uses for ST_MinMax other than band reclassification.

Storing the min/max values in the user's coverage table or the 
raster_columns table sounds better that making changes to the core. 
But, the min/max values may change once ST_Accum and ST_Union are 
written and applied to overlapping rasters.

As for defining custom color ramps, your response below seems to 
adequately answer that question.

>> 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.
>

I think implementing ST_Reclass is the right approach for converting 
rasters to JPEGs and PNGs, in addition to other applications.  And since 
your example builds a custom color ramp, this should be able to provide 
all the flexibility that everyone would expect.

As I prefer things to be done on the fly, I'd rather see ST_Reclass 
written in C.

> Great work Bborie! and thanks again for your collaboration.
>
> Pierre
>

So, how do we get the ball rolling on ST_Reclass so that I can get 
coding?  I've already worked on ST_MinMax to test it out.

If it takes a while to get a consensus on a spec, I hope no one minds me 
working on ST_Transform(raster).

-bborie

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkpark at ucdavis.edu