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

[qgib]

Author Name: Brett Carlock (@Saijin-Naib)
Original Redmine Issue: 20233
Affected QGIS version: 3.2.3
Redmine category:data_provider/spatialite

---

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

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

Can you link/attach the dataset?

---

• status_id was changed from Open to Feedback

[qgib]

Author Name: Brett Carlock (@Saijin-Naib)

---

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.

[qgib]

Author Name: Nyall Dawson (@nyalldawson)

---

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

[qgib]

Author Name: Brett Carlock (@Saijin-Naib)

---

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.

[qgib]

Author Name: Saber Razmjooei (@saberraz)

---

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

---

• status_id was changed from Feedback to Closed