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.


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.

Adapted from official docs...

FROM ST_BandMetadata(
        ST_MakeEmptyRaster(10, 10, 0, 0, 1, -1, 0, 0, 0),
            ROW(NULL, '8BUI', 255, 0),
            ROW(NULL, '8BUI', 255, 0),
            ROW(NULL, '8BUI', 255, 0),
            ROW(NULL, '8BUI', 255, 0)
| improve this answer | |
  • 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 :-) – Martin Perry Jan 21 '17 at 18:45
  • BUILDING MY OWN DATABASE IN ASSEMBLY WAS FASTER – Evan Carroll Jan 21 '17 at 18:49
  • 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 – Martin Perry Jan 21 '17 at 18:53
  • 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. – Evan Carroll Jan 21 '17 at 18:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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