I'm fiddling around trying to create a thumbnail of a fairly large (originally 27MB) raster, chunked into 64x64 blocks. I've got the following and it works, quite well, but I'm wondering if there is a way to make it more efficient, or if it is just totally mental and shouldn't be attempted.
It suffers from various flaws - that it is not really so reusable, because it is quite specific, for example.
SELECT ST_AsPNG(ST_Reclass(ST_Union(rastnxn), 1, '[0-10]:1-65535', '16BUI', 0)) FROM ( SELECT filename, ST_Resample(rast, 8, 8) as rastnxn FROM rasters WHERE filename = 'someraster.tif' ) as subset GROUP BY filename;
What do you think? Are there other (better) ways to quickly get the original raster back out in a 'thumbnail' style?