Bug report #20233

Issue: Large polygon vector layer stored in SpatialLite db not rendering properly

Added by Brett Carlock about 2 years ago. Updated about 2 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/SpatiaLite
Affected QGIS version:3.2.3 Regression?:No
Operating System:Win10 x64 Pro Version 1809 (OS Build 18267.1001) Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:28054

Description

Issue summary:
Relatively large polygon dataset stored in QGIS-created SpatialLite database will not draw properly/fully within QGIS. Previewed from SpatialLite-GUI, it appears fine.
Exported to ESRI Shapefile & displayed/loaded, and the layer draws fully.

Queries/filters/etc can be done against the SpatialLite layer, though they do not change the display.
For instance, if I filter on STATEFP to 50 (Vermont), it'll not show ANY geometries, since all the ones in Vermont do not draw when it is in this error state. From this I can conclude that it isn't an issue with the number of geometries being loaded/drawn at any given time, but something else entirely.

I've verified the integrity of the database multiple times in SQLite Studio as well as SpatialLite-GUI, and they report good consistency/validity.

Screencap of the behavior:
https://www.youtube.com/watch?v=vkAHcdlzz3A

History

#1 Updated by Giovanni Manghi about 2 years ago

  • Status changed from Open to Feedback

Can you link/attach the dataset?

#2 Updated by Brett Carlock about 2 years ago

Giovanni Manghi wrote:

Can you link/attach the dataset?

File size: 104 MB, 7z
https://1drv.ms/u/s!AvMZEGXuAwQzpelUzr_JJ6ubh3RWBw

FWIW, I did not have the issue with partial display of features when I used (Processing Toolbox -> Database -> Package layers) to make a GeoPackage of the same data.

#3 Updated by Nyall Dawson about 2 years ago

Maybe an issue with the spatial index in the database - can you recreate this?

#4 Updated by Brett Carlock about 2 years ago

Nyall Dawson wrote:

Maybe an issue with the spatial index in the database - can you recreate this?

Yep, I'm an idiot.

I assumed that since the database integrity/vacuum was valid, and geometries were valid, and I could add indexes to the layers that all was good and healthy.
You were correct: Upon calling the Check Spatial Index function in spatialite-gui against the GEOMETRY object for cousub it warned me about it being inconsistent and to recover it immediately. I did so, and upon doing so, the layer draws fine in QGIS now.

Is there any way for these management tasks to be brought into the QGIS Database Manager? How about warnings/sanity checks upon load of data from attached databases?

Or alternatively, I just need to improve my database-Fu so that I would have known to check things like this.

Sorry for the waste of time.

#5 Updated by Saber Razmjooei about 2 years ago

  • Status changed from Feedback to Closed

Feel free to open a ticket related to the index and warnings.

Also available in: Atom PDF