Poor performance with storing large rasters in PostGIS and visualising in QGIS

by user2109092   Last Updated June 20, 2015 01:09 AM

my question concerns the use and performance of several software tools in conjunction, namely PostgreSQL, PostGIS, QGIS, and GDAL.

I'm a long-time ArcGIS, Python, and R user who is interested in diversifying into the free open source GIS ecosystem and Linux as well. Recently I've been very interested in using QGIS (ver 2.8) together with PostgreSQL (ver 9.4) and PostGIS (ver 2.1), and I've installed the software on a computer with Windows 8.1 x64 (the computer specs in brief: ThinkPad X200s with a 2.1GHz Core 2, 8GB RAM, and a 240GB SSD). Once I learn how to manage my spatial data (~100GB worth), I'd like to run Ubuntu on this machine.

At the moment, I'm simply trying to reliably store and retrieve shapefiles and rasters. So far I've been successful in loading shapefiles into PostGIS, but rasters are proving more problematic. I have successfully completed single and batch imports of small geoTIFF and GRID files, but larger rasters (say, a 15619x14655 cell IMG or TIFF file 870MB in size on disk) take forever to load into PostGIS. I've read and configured the raster2pgsql tool to build spatial indices and load rasters by tiles using these parameters:

raster2pgsql -s 3161 -C -I D:\PostGIS_data\dem.img -t auto raster.dem | psql -h localhost -U postgres -p 5432 -d postgres

Performance in importing is still very poor, and the hardware is not the problem. Visualisation of PostGIS rasters in QGIS is even worse, slowly loading small rasters at best or freezing altogether. Large rasters like the one I mentioned are impossible to visualise in QGIS. From the documentation and forum discussions, this shortcoming appears to be due to GDAL's PostGIS raster driver and not QGIS itself. Forum discussions mention this problem briefly and some even suggest that rasters shouldn't be stored in PostGIS (what is the point in a spatial database that doesn't handle rasters smoothly?). Yet I routinely use ESRI's file geodatabase to store, visualise, and analyse quite large (~70GB) rasters quickly and easily, and ArcGIS 10.1 never freezes or slows due to such routine operations. These performance roadblocks are disappointing and leave me unimpressed with FOSS GIS.

Is there something I'm missing here, a bottleneck I haven't addressed? Does PostgreSQL need tuning to realise the performance benefits of PostGIS? Am I missing a version of GDAL that I need to hunt down and compile? How do I improve PostGIS performance and visualisation in QGIS of shapefiles and rasters especially? How can I enjoy the glory of comprehensive and speedy spatial data management via a Linux terminal? Any help on this issue would be welcome!

Answers 2

I am having the exact same issues with rendering rasters in QGIS from PostGIS (see my recent question) I found this post helpful and increasing the following improved raster rendering slightly:

shared_buffers = 5000MB work_mem = 100MB maintenance_work_mem = 100MB

However, with that said, I totally agree that the performance of PostGIS rasters in QGIS is not great. I am dealing with 608 compressed geotiffs that load great as a VRT but are essentially unusable in PostGIS. Try to increase the performance of the dbase server, but beyond that I can't be too helpful. I too might have to rely on the file system to serve up rasters within my organization.

August 14, 2015 20:14 PM

If you want to display large rasters in QGIS, you have to build pyramids, either for a tif image on the file system or for a image registered in Postgis.

The performance difference in QGIS rendering between a large raster in the file system or in Postgis is miminal. Users will not notice the difference. But - if and only if - you build the pyramids with the option -l.

If you simple import the image without the -l option, or with just -l 4 it will not work.

If you use, for example, -l 2,4,8,16, four levels of pyramids will be created, like in the layer below:

Pyramids generated with -l 2,4,8,16

If you want to have a better user experience, you should add more levels of pyramids, like -l 2,4,8,16,32,64,128,256. This will create eight levels of pyramids.

enter image description here

To summarize, the answer to this question is: import the raster with the option -l and use the same number of pyramid levels as you use for the same raster on the file system.

For example:

raster2pgsql -s 3161 -d -C -I -M -l 2,4,8,16,32,64,128,256 D:\PostGIS_data\dem.img -t 100x100 raster.dem100 | psql -h localhost -U postgres -p 5432 -d postgres
February 06, 2017 22:56 PM

Related Questions

Updated July 20, 2017 16:22 PM

Updated November 07, 2018 15:22 PM

Updated April 10, 2016 08:09 AM

Updated August 16, 2018 10:22 AM