PostgreSQL - PostGIS ST_SetValues - int array cast to double and back

Question

I am using ST_SetValues with array defined as ARRAY[[9, 9], [9, 9]]::double precision[][].

If I store 32bit integer value in this array can I retrieve this value exactly after casting from double to integer?

Or is there some limited precission for integer part of the double. I want to store "packed" RGBA into a single 32bit integer there and "unpack" single color channels after output in my app.

As from documentation, double should be 64bit, so in my oppinion, this should be possible, but maybe I am missing something.

Answer 1

You're dong it wrong. I assume you're wanting a 32bit integer so you can bit shift around for RGBA (8bits per channel.) What you really want is a raster with 4 channels, 8 bits each. Because life will be easier.

• ST_MakeEmptyRaster
• ST_AddBand

Adapted from official docs...

SELECT
 *
FROM ST_BandMetadata(
 ST_AddBand(
 ST_MakeEmptyRaster(10, 10, 0, 0, 1, -1, 0, 0, 0),
 ARRAY[
 ROW(NULL, '8BUI', 255, 0),
 ROW(NULL, '8BUI', 255, 0),
 ROW(NULL, '8BUI', 255, 0),
 ROW(NULL, '8BUI', 255, 0)
 ]::addbandarg[]
 ),
 ARRAY[]::integer[]
);

• Martin Perry: Based on my tests, bands are slower, than "packing" four 8bit values into one 32bit number. I dont care abour "easy life", I need best possible performance :-)
• Evan Carroll: BUILDING MY OWN DATABASE IN ASSEMBLY WAS FASTER
• Martin Perry: Selecting data from (1) band in (N) band raster - 240 ms; Selecting data from (1) band in (1) band raster - 70 ms (N = 6, so not 4... but still, almost 3x times slower...) - Getting one random value 100x
• Evan Carroll: I have no idea how you're creating your benchmark. But, you could ask a question show how you created your benchmark and ask how to make it faster and if you're doing everything right.... OR, you could just continue down the path of rewriting everything thinking you can make it faster.