Adding styles for a raster in PostGIS

Question

I have a PostgreSQL/PostGIS database located on a server. I have added shapefiles (with the "PostGIS shapefile import/export manager") and raster (with "raster2pgsql"). It works well.

However, when I try to save layer styles in this database (through QGIS) it works well for shapefiles but for raster it is only possible to save the style on local machine in a *.qml or *.sld file (and therefore not in PostgreSQL as it can be done for shapefiles and stored in layer_styles).

Is there a way to save style for rasters in PostgreSQL/PostGIS ? (I am a rookie with PostgreSQL/PostGIS).

Answer 1

It is not possible like the ease of vector styles saved in the postgres layer_styles table. But there are some alternatives - testet in QGIS 3.20.3:

1. Saving the raster style in QGIS as default (Layer properties > Style button > Save As Default) will save the style as a QML string in a sqlite database placed here: C:\Users\XXX\AppData\Roaming\QGIS\QGIS3\profiles\default\qgis.qmldb. Loading the raster from PostGIS will automatically load the style. This will only work on the computer saving the style.

2. Save the QGIS project into Postgres (Project > Save To > PostgreSQL...). Loading the project from the db will load the raster style for any raster layer.

UPDATE

If your raster has one band you can convert it to RGB (three bands). This will render the raster with the correct style when loaded from PostGIS to QGIS:

Step 1: Save the 1-band raster as rgb using QGIS export

Step 2: Load to PostGIS using raster2pgsql