1

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?

  • have you tried running a SQL query from DbManager? (Afraid I don't have any similar raster databases to test on at the moment, but it's worth a try) – Steven Kay Apr 6 '17 at 21:11
  • Thanks for the suggestion - that almost worked but not quite. I can create the SQL statement to select just a single raster, but when I "load to canvas" it creates an empty layer. It doesn't seem to bring through the geometry - in layer properties it says No geometry (WKB type: "NoGeometry") – Rob Burgess Apr 11 '17 at 14:29
0

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')

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.