Postgres - Convert bytea PNG column to GeoTiff

Question

I have loaded a massive set of tiles into a postgres database for use in a tile server. These were all loaded into bytea columns in PNG format.

I now find out that the tile server code needs these to be in GEOTiff format.

The command:-

gdal_translate -of GTiff -expand rgb -co COMPRESS=DEFLATE -co ZLEVEL=6

Works perfectly.

However, a massive amount of data is loaded already on a remote server. So is it possible for me to do the conversion within the database instead of retrieving each file and using gdal_translate on them individually? I understand that gdal is integrated with postgis 2.0 through the raster support which is installed on my server.

If not, any suggestions as to how to do this efficiently.

Answer 1

Is it possible to do in the database with an appropriate procedural language? I suppose. Additionally it is worth noting up front that gdal's support for Postgis goes rather one way.

To be honest the approach is likely to be "retrieve individual record, convert, restore using external image processing like you are doing." You might get some transaction benefit but this is likely to be offset by locks.

If you are going to go this route, you may find pl/java to be the most helpful approach since you can load any image processing library Java supports and use that.

I am, however, not convinced that this will be better than retrieve/transform/load.