6

I'm learning how to manage a local PostGIS (ver 2.1) database, with spatial indexing enabled on the shapefiles that I've imported (using the PostGIS Shapefile Import/Export Manager). I have imported a large shapefile with 4.7 million polygons and a 2.1GB attribute table into a schema.

However, using QGIS 2.8.2 to load, render, and open the attribute table of this table has been rather disappointing. Adding the dataset to the QGIS canvas freezes the application for a short period. While the polygons are quickly rendered, opening the attribute table produces a small dialogue window saying '____ features loaded' until all 4.7 million features are loaded. While QGIS takes a long time to open the attribute table, it also appears to consume a great deal of memory (~1.1GB) as well. I've avoided making queries to subset rows of the attribute table due to poor performance as well.

I've heard of other QGIS+PostGIS users reporting excellent performance with tables containing >500,000 or even millions features, but maybe my expectations are unreasonable. So while I'd like to enjoy the performance benefits of PostGIS, I'm not sure what the problem is. Here's what I've tried:

  • I've created a spatial index on the table using the using the PostGIS Shapefile Import/Export Manager and the following SQL code:

    CREATE INDEX parcels_geom_idx ON parcels USING gist (geom);

  • In QGIS under the Data Sources tab, I have the 'Attribute table row cache' set to 10,000 (the default)

  • The database is on the local host machine which runs on a Core 2 Duo, 8GB RAM, and a 240GB SSD

Eventually I'd like to store up to 100GB of spatial data from a collection of geodatabases in a PostGIS database, but with performance problems on just a few data sets I'm not confident in moving away from ESRI's platform towards an open source ecosystem of GIS software.

  • It's a little difficult to troubleshoot ATM. Some details about your postgresql.conf would help to see if there's been any attempts to optimize there. Do you have any indexes generated for the columns you want to use for querying/subsetting? Sometimes it helps if you place a checkmark for the Use estimated table metadata option when making a new PostGIS connection (or edit an existing one) inside QGIS. After adding the large shapefile into the database did you get a chance to run VACCUM ANALYZE; or REINDEX;? – SaultDon Aug 11 '15 at 16:39
  • I did change my postgresql.conf according to the following optimisation guide: suite.opengeo.org/4.1/dataadmin/pgDBAdmin/tuning.html – mbcaradima Aug 11 '15 at 17:39
  • Within pgAdmin I also ran a REINDEX query and a VACUUM ANALYZE on the table of interest. After making these changes, the same dialogue box comes up for opening the attribute table and QGIS still attempts to load the attributes. After checking the table properties using the DB Manager in QGIS, I found that there is an index built on the geom column... do I need other indices built? Would clustering the table help? – mbcaradima Aug 11 '15 at 17:47
  • Clustering won't make a difference unless your tables have been "partitioned" to exist on more than one hard-drive/partition. Indexes on the columns being used in any queries would speed up those queries considerably. – SaultDon Aug 11 '15 at 18:15
  • The slow attribute table loading is another issue that might have to be filed as either a bug or enhancement request for QGIS to allow quick loading of attribute tables wtih lots of records. – SaultDon Aug 11 '15 at 18:17
2

It seems that QGIS is loading things (almost) entirely to memory whereas PostGIS/PostgreSQL as a database engine will make a more efficient use of memory. I'd recommend the following modifications to your workflow, which can be summarised with "only load samples of data in QGIS to get a sense of the data, and leave the heavy processing to PostGIS".

  1. Unless you need every element of a layer loaded, only load a limited number of records with a LIMIT clause on your query such as LIMIT 500000
  2. Unless you need every attribute of a layer loaded, only load those you want to look at or map, with a query like:

    SELECT gid, geom, col1, col2
    FROM table1
    LIMIT 500000 ORDER BY importance DESC

  3. With your sample data, test operations you want to perform in QGIS to see the results (JOINs, INTERSECTIONs, etc.), and then perform using SQL queries in PGAdmin (QGIS can lock up on big SQL queries, so its better to run them in a program designed to administer SQL).

  • raphael, thanks for taking the time to look at the issue and for your suggestion. I think I will avoid using QGIS together with PostGIS for the time being. I have encountered problems with the PostGIS gdal driver in dealing with rasters as well here: gis.stackexchange.com/questions/151632/…. All these issues are problems with QGIS that are not found in ArcGIS. Perhaps the new arrival in GIS needs more time to mature. – mbcaradima Dec 26 '15 at 21:13
  • I'm afraid I haven't really dealt much with rasters in my work so I'm not of much help in that respect. Good luck! – raphael Dec 26 '15 at 22:00

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.