4

I'm trying to add an extremely large table with a points geometry field to QGIS and QGIS appears to hang. After some investigating in the postgresql database, I tracked it down to a st_extent on everything in the table.

Is it correct to assume that the reason QGIS does this is in order to obtain a full extent for the layer? And if so, is there a way to bypass this? Maybe either use the spatial reference extent or manually provide the extent?

QGIS 2.6 64-bit
POSTGIS="2.1.3 r12547" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER
PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit

4

Have a look at the settings of PostGIS connection. There is a selection "Use estimated metadata" that is unselected by default. Check it and QGIS will use ST_Estimated_Extent http://postgis.net/docs/manual-2.0/ST_Estimated_Extent.html. Remember to run VACUUM ANALYZE before.

3

The "use estimated metadata" option on the connection works for newly created layers, but it doesn't change it on existing layers that connect to that database, nor did I find any way to change it in the layer properties (I'm using 3.8.3). I found it relatively quick to change all my existing layers by editing the QGS file to add estimatedmetadata=true to all PostGIS data source descriptions.

Here are the steps I followed using the Linux terminal. Similar steps can be done in any OS, as long as you have a way to search and replace text.

  1. Extract QGS file from the QGZ:

    $ unzip map.qgz map.qgs
    
  2. Add estimatedmetadata=true to all PostGIS data source descriptions in the QGS. In my particular file, I found these in XML <datasource> and <layer-tree-layer> tags.

    For my particular file, I checked and saw that all such lines had no estimatedmetadata=, so rather than switching estimatedmetadata=false to estimatedmetadata=true, I had to add in a new estimatedmetadata=true. I looked for a string that was in the middle of all of the data source descriptions, so I could do the equivalent of a search and insert by using a text replace. The string I found in my file was checkPrimaryKeyUnicity=, so I ran the following command:

    $ sed -i 's/checkPrimaryKeyUnicity/estimatedmetadata=true checkPrimaryKeyUnicity/g' map.qgs
    

    You may have to adapt this to the details of your specific file, make the changes in a more manual way, or use a tool with XML-specific editing features.

  3. Replace the QGS file in the QGZ:

    $ zip -u map.qgz map.qgs
    
  • I tried searching for that but seems like new QGIS formats the checkPrimaryKeyUnicity= different from what you wrote ? – Luffydude Nov 22 '19 at 10:29
  • @Luffydude I edited to explain my process better; maybe if you look in your file there might be a similar way to accomplish the same thing? I just looked for a string that would indicate it was in the middle of one of those data source descriptions. – Dan Getz Nov 22 '19 at 21:13

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.