SpatiaLite index issue

[qgib]

Author Name: Reinhard Reiterer (Reinhard Reiterer)
Original Redmine Issue: 15082
Affected QGIS version: master
Redmine category:db_manager

---

Spatial indexes are not listed in 'Indexes' tab and the 'DB Manager' crashes when adding a index (see screencast).

---

• 15082.mp4 (Reinhard Reiterer)

[qgib]

Author Name: Reinhard Reiterer (Reinhard Reiterer)

---

• 10046 was configured as 15082.mp4

---

• 15082.mp4 (Reinhard Reiterer) - screencast

[qgib]

Author Name: Jukka Rahkonen (Jukka Rahkonen)

---

The issue is real but how to improve the current situation needs some thinking because spatial index in SpatiaLite/GeoPackage is not an index but a table with some well known triggers.

The list of true indexes from SQLite can be obtained with a PRAGMA command

@pragma index_list('table_name')@

I suppose that the Indexes tab in Layer properties makes exactly that request.

From SpatiaLite databases created with recent versions the existence of the spatial index is supposed to be checked from the geometry columns table which has a column "spatial_index_enabled".

@select ROWID, "f_table_name", "f_geometry_column", "type", "coord_dimension", "srid", "spatial_index_enabled"
FROM "geometry_columns"
ORDER BY ROWID@

If user is pressing "Create spatial index" button and spatial index already exists then the application should perhaps make a question:
"Table xxx has already a spatial index. Do you want to recover it?" If user says "Yes" then RecoverSpatialIndex function would be performed.

For the GeoPackage case I suppose that if spatial index exists or not should be checked with the test that is included in the standard, on page 75:

@A.3.1.3 Spatial Indexes
A.3.1.3.1 Data
A.3.1.3.1.1 Spatial Indexes Implementation

...snip...

3. For each row table_name, column_name from step 1
   a. SELECT sql FROM sqlite_master WHERE tbl_name = ‘rtree_’ || result_set_table_name || ‘’ || result_set_column_name
   b. Not testable if result set is empty
   c. Fail if returned sql != ‘CREATE VIRTUAL TABLE rtree’ ’ || result_set_table_name || ‘’ || result_set_column_name || USING rtree(id, minx, maxx, miny, maxy)
   d. SELECT sql FROM sqlite_master WHERE type = ‘trigger’ AND tname = ‘rtree’ || result_set_table_name || ‘’ || result_set_column_name || ‘insert’
   e. Fail if returned sql != result of populating insert triggers template in Annex L using result_set_table_name for and result_set_column_name for
   f. SELECT sql FROM sqlite_master WHERE type = ‘trigger’ AND name LIKE ‘rtree’ || result_set_table_name || ‘’ || result_set_column_name || ‘update%’
   g. Fail if returned sql != result of populating 4 update triggers templates in Annex L using result_set_table_name for and result_set_column_name for
   h. SELECT sql FROM sqlite_master WHERE type=’trigger’ AND name = ‘rtree’ || result_set_table_name || ‘_’ || result_set_column_name || ‘_delete’
   i. Fail if returned sql != result of populating delete trigger template in Annex L using result_set_table_name for and result_set_column_name for
   j. Log pass otherwise
4. Pass if logged pass and no fails@

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

• easy_fix was configured as 0
• regression was configured as 0

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

End of life notice: QGIS 2.18 LTR

Source:
http://blog.qgis.org/2019/03/09/end-of-life-notice-qgis-2-18-ltr/

---

• resolution was changed from to end of life
• status_id was changed from Open to Closed

[tschmetzer]

Just stumbling over this issue as I have created a sqlite database (point features) with a size of about 4.5 GB. Unfortunately I cannot use the layer and I severely suspect the indexing:

• /*The layer is extremely slow when displaying. Depending on the section to display it takes many minutes or hours to display some points*/ (couldn't confirm it)

• When trying to create a spatial index in the properties layer the button gets grey after a couple of seconds but when reopening the properties dialog the button is not grey anymore just as if no index has been created

• when performing a processing operation like "join by location" with this layer I am informed that no spatial index exists although I tried to create one
  

• when referring to the layer in a field calculator expression QGIS hangs like forever (haven't tested in detail how long for). The Geopackage file export of the same SpatiaLite data is way faster and an index is created.

I read that the spatialite format is quite mature, widely-used and possesses indexes. Its file size seems to be way smaller than GeoPackage (16GB for the same data)

Can anybody confirm the trouble with the SpatiaLite indexes at that advanced development stage of QGIS? Is my example file required and if so where to upload it (4.5GB) ?

[gioman]

Is my example file required and if so where to upload it (4.5GB) ?

@tschmetzer gdrive? wetransfer? dropbox?

[tschmetzer]

Is my example file required and if so where to upload it (4.5GB) ?

@tschmetzer gdrive? wetransfer? dropbox?

https://drive.google.com/file/d/1hK6qtymSjb32JiMR-5EnxqUbUMeER2xF/view?usp=sharing

[gioman]

https://drive.google.com/file/d/1hK6qtymSjb32JiMR-5EnxqUbUMeER2xF/view?usp=sharing

@tschmetzer very slow indeed, but I think is expected if it is a normal sqlite database

https://gdal.org/drivers/vector/sqlite.html

While the SQLite driver supports reading spatial data from records, there is no support for spatial indexing, so spatial queries will tend to be slow (use Spatialite for that).

You may want to convert to GPKG (it gave me a very large datasource but very snappy), or to Spatialite, i.e.

ogr2ogr -f SQLite -dsco SPATIALITE=YES output.sqlite input.sqlite -lco SPATIAL_INDEX=YES

[gioman]

ogr2ogr -f SQLite -dsco SPATIALITE=YES output.sqlite input.sqlite -lco SPATIAL_INDEX=YES

@tschmetzer this created a file that in size is very similar to the GPKG (so much bigger than your original one) but perfectly functional.

[tschmetzer]

@gioman Thank you so much for your hints!

OK, there seem to be various distinct topics in my report that I originally all attributed to a pure index problem. One major issue is for sure the distinction between SpatiaLight and SQLite which both use the same file extension (.sqlite). SpatiaLight is indexable but SQLite is not.

As we are discussing this with regards to help the QGIS project move forward there might be some things worth discussing for potential improvements:

• I got to this point of a non-indexable SQLite file using the following dialog depicted below.
  -> Why is the user not offered to create a(n) (indexed) SpatiaLite file?
  
  Don't know what you think about putting this into a distinct feature request as a new issue?

• Why is the user not informed about the impossibility to create an index when trying to create it but communicated about it's seeming/apparent success and greying the "Create Spatial Index" button? This looks clearly like a bug to me.
  

  I could think of the following options to handle this:

  • When the button "Create Spatial Index" is pressed raise an info box "Spatial Indexing not possible with this format"
  • Instead of the button "Create Spatial Index" put a grey button "Format allows no Spatial Index"
  • Offer to convert the file from SQLite to SpatiaLite

Regarding the very slow field calculator (seems to hang but will probably finish after hours) when using an aggregate expression with a location filter by geometry the slow behaviour shows off with small shape files as well (evaluation of 5,000 points in 3 polygons took about 25 minutes). So I guess this is just due to a not optimised implementation of the aggregate function that I used on a polygon layer:

aggregate(
layer:= '<point_layer>',
aggregate:='sum',
expression:=dni,
filter:=intersects($geometry, geometry(@parent))
)

If that very slow behaviour shouldn't exist I can create a new issue as it is off-topic from SpatiaLite index.

[gioman]

* -> Why is the user not offered to create a(n) (indexed) SpatiaLite file?

@tschmetzer good point, please file a feature request.

Note: I checked with

ogrinfo db.sqlite --debug on

and as a fact that option generates a plain SQLite db ("SQLITE: OGR style SQLite DB found !") rather than a Spatialite enabled ("SQLITE: SpatiaLite v4 DB found !").

* Why is the user not informed about the impossibility to create an index when trying to create it but communicated about it's seeming/apparent success and greying the "Create Spatial Index" button? This looks clearly like a bug to me.

I agree. On a plain SQLite db that option has no effect. And in fact if the datasource is removed and re-added the button is enabled again.

Regarding the very slow field calculator (seems to hang but will probably finish after hours) when using an aggregate expression with a location filter by geometry the slow behaviour shows off with small shape files as well (evaluation of 5,000 points in 3 polygons took about 25 minutes). So I guess this is just due to a not optimised implementation of the aggregate function that I used on a polygon layer:

file a ticket with a sample dataset.

[tschmetzer]

• Why is the user not informed about the impossibility to create an index when trying to create it but communicated about it's seeming/apparent success and greying the "Create Spatial Index" button? This looks clearly like a bug to me.

I agree. On a plain SQLite db that option has no effect. And in fact if the datasource is removed and re-added the button is enabled again.

So if I get it right the focus of this ticket remains on the misleading and impossible intent to index a SQLite DB

[gioman]

So if I get it right the focus of this ticket remains on the misleading and impossible intent to index a SQLite DB

I would open a new ticket, this is about DB Manager. @tschmetzer