Loading selected records in postgis raster table into QGIS using DB Manager

Question

I've recently started using (out-of-db) raster data within postgis.

The raster data is an output from a metocean forecast model, with new data available every hour.

I'm uploading each new raster to a database, using something like this.

raster2pgsql -a -s 4326 -I -F -R path/to/tif/latest_output.tif postgis_table | psql db

As you can see, the -a flag is appending each new raster to the table, so I end up with a table where each record is a raster (sorry about the table): +------+---------------+--------------------+ | rid | rast | filename | +------+---------------+--------------------+ | 1 | RASTER | raster_0000_hrs | | 2 | RASTER | raster_0100_hrs | | 3 | RASTER | raster_0200_hrs | | 4 | RASTER | raster_0300_hrs | +------+---------------+--------------------+

While this is really useful for running queries, I'm struggling to view the raster data in QGIS via the DB Manager.

Is it possible to "filter" raster tables in the DB Manager to load in only a select few rasters (e.g. raster_0000_hrs only), rather than adding the entire table, which invetibaly crashes QGIS? Are there any alternative plugins that I could use instead?

Answer 1

You can filter the raster using views as mentioned in the following post - How to view a postgis raster query in QGIS.

Here's an example for your table :

CREATE OR REPLACE VIEW your_schema.test_view AS
SELECT *
FROM your_schema.your_raster_table
WHERE filename IN ('raster_0000_hrs', 'raster_0100_hrs')