21

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!


I followed this guide by a Duncan Golicher: https://duncanjg.wordpress.com/2012/11/20/the-basics-of-postgis-raster/

I was using tiles with an automatic setting originally, but I reset the tiling to 100x100 cells per row and then included the pyramids as shown in the guide like so:

raster2pgsql -s 3161 -d -C -I -M -l 4 D:\PostGIS_data\dem.img -t 100x100 raster.dem100 | psql -h localhost -U postgres -p 5432 -d postgres

I was able to successfully import the 870MB IMG raster in a good time and display it in QGIS without slowing or crashing the application. The rendering time is not as snappy as I'd expect, but it is acceptable. I will read further on the -l parameter to use it properly.

Incidentally, in importing the dem.img file as the dem100 table another raster table was created called "o_4_dem100". When I import it as a layer in QGIS, it has a value range of between 201 to 524, while the dem100 layer has a range of 36 to 524. Am I right in assuming that this extra table is the pyramid table that has a narrower value range as a result of being aggregated to a lower resolution?


I don't think inadequate hardware is the problem. Here's a brief summary of what I've found so far.

GDAL's PostGIS raster driver has had past performance issues (see here as well). Although these problems were noted in 2012, I wonder whether GDAL 1.11.2 found in QGIS 2.8 still has this problem. Surely there are others using QGIS and PostGIS for raster visualisation and storage?

On a possible related note, I have also had performance issues with opening PostGIS attribute tables in QGIS with tables of ~4.7m records. After a few suggestions in that thread and without fixing the problem, I ultimately filed a bug report with QGIS that was eventually closed and linked to the following similar bug report. Although the bug report is closed, it doesn't seem to be fixed...

To sum up my efforts so far:

  • I have optimised the PostgreSQL server for spatial data.
  • I have built spatial indices for geometry tables and performed a VACUUM.
  • QGIS behaviour for opening large attribute tables (~4.7m records) seems to try reading all records rather than returning a subset for instantaneous viewing. This leads to poor performance.
  • Performance in rendering large PostGIS geometry tables does not seem to be a problem.

  • With raster2pgsql, rasters were indexed, tiled, and imported as raster tables with pyramids in PostGIS.

  • Rasters of any reasonable size are still incredibly slow to import into PostGIS, let alone open and pan around in QGIS.

It's also worth noting that when importing large rasters or opening large attribute tables with PostGIS, memory consumption for raster2pgsql and qgis-bin are over 1GB. As @Michael and @Paul have mentioned in response to my initial question, it appears that PostGIS isn't meant to bring much if any benefits to storing rasters. However, at that point I question why I would run QGIS+PostGIS at all for my GIS needs, especially when ESRI fileGDBs enable raster attributes, mosaic datasets, and other raster operations facilitated by the geodatabase. So maybe either I am really missing something or QGIS and PostGIS do not meet my GIS needs. I find the latter hard to believe.

  • Does the rasters have to be in PostGIS? What benefits/extra functionality are you hoping to gain from this? I found that PostGis vector was acceptable, and offered mult-user editing but PostGis raster had no real benefits over file-based (server stored) raster. Good question though; it is quite possible that there are some benefits I've missed in my evaluation... – Michael Stimson Jun 19 '15 at 23:53
  • I thought that PostGIS rasters enabled faster raster calculations as well as better performance with raster/vector operations. That is in addition to the benefits of a spatial DB: reliability, accessibility, backup facilities, more compact storage, etc. In any case, a file/tile approach doesn't allow for search functions, pre-built pyramids, tiling, and other capabilities that improve how the raster is used and visualised. – mbcaradima Jun 20 '15 at 17:36
  • I've seen no metric that says PostGIS rasters are faster at raster calculations.. in either case with a 240GB SSD (nice choice BTW, faster than RAID at a fraction of the cost/effort) you will fill that very quickly with the rasters... ECW/JP2 for 8bit or GeoTIFF with LZW/Deflate compression ticks most of those boxes, pre-built pyramids, tiling (via VRT), backup as files, etc... the only benefit is the search function. I realize I'm getting slightly off topic but if PostGIS raster is not doing what you expect then why not stick with file raster for display? – Michael Stimson Jun 21 '15 at 21:46
  • 3
    Who ever said that PostGIS rasters were faster than anything? They can be more convenient (handy SQL access API) and they can be useful for analysis (raster and vector in the same bucket) but faster? Never. – Paul Ramsey Jun 22 '15 at 16:40
  • 1
    I'm working through a book on PostGIS (PostGIS in Action, 2nd ed) and it seemed natural to assume that the benefits of storing shapefiles in a spatial DB would extend to a raster as well. Of course, given their differing data models I can see this assumption was purely intuitive. Still, rasters are commonly stored in geodatabases with ArcGIS and allow for building pyramids, faster geoprocessing, and building mosaics. In a workflow with open source software, how is a GIS user supposed to work with rasters then? BTW, I will duly punch myself in the face. – mbcaradima Jun 22 '15 at 23:46
6

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
5

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.

  • Thanks for your comment, Cliff. I've applied some of your changes and will report any major performance improvements. Overall I have to say that QGIS performance is disappointing for visualising PostGIS rasters and loading/querying attribute tables. Raster performance in PostGIS is also disappointing. I have none of these problems with file geodatabases, so I'm wondering what is wrong? – mbcaradima Aug 16 '15 at 15:11
  • 1
    My sentiments exactly. I spent the week trying to get this going and simply could not get it running. I am now testing my VM (Ubuntu Server) with 10 processors and 10GB of ram. If that is still sluggish, I must be doing something else wrong. I am also perplexed why WMS layers in QGIS are basically unusable due to their slow rendering speed. We should connect more on this since we're both in the same boat. – Cliff Aug 16 '15 at 19:29
  • If they load great as a VRT, why didn't you stop there? What gain are you expecting from this great raster journey? – Paul Ramsey Aug 17 '15 at 22:14
  • I guess my answer to this, Paul, is exactly what OP said the next post: "However, at that point I question why I would run QGIS+PostGIS at all for my GIS needs, especially when ESRI fileGDBs enable raster attributes, mosaic datasets, and other raster operations facilitated by the geodatabase. So maybe either I am really missing something or QGIS and PostGIS do not meet my GIS needs. I find the latter hard to believe." – Cliff Aug 18 '15 at 1:30
  • 1
    Furthermore, I would say that about 70% of the analysis that I do is on rasters, and roughly 40% of the data I wish to serve to my organization via QGIS are raster data. It just makes sense to have all raster and vector data in one database so that users can set up one connection and have access to our entire organization's database. Instead, I would have to create creds for the dbase and creds for the file share. Alternately, I am seriously considering scrapping QGIS and building a web application with Geoserver (ps: always willing to collaborate on this with anyone interested). – Cliff Aug 18 '15 at 1:38
2

Not sure if it was your case, but I found out -I should not be used together with appending data -a.

I was importing many TIF files into a DB, and -I actually creates the index again and performs analyse on the table for each file, which takes 10x more time.

-I should only be used when creating the table, with -p option.

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.