7

My frame of reference is:

Does QGIS automatically use spatialite spatial indexes? This explain that QGIS uses QgsSpatiaLiteFeatureIterator for Spatialite Data Provider

Spatial indexes in GeoPackage files This explain the use of Query that is utilizing r-tree index with sub-query:

Creating Spatial Index for Geopackage using QGIS?

This last one explain that It's possible in QGIS3 to create spatial index for Geopackage. You can click "Create Spatial index" from the Layer Properties Dialog and that's it.

My doubt when reading all this, is what is the current state of the use of spatial index within a geopackage?.

Because I can use the DBmanager to create non-spatial indexes within a geopackage per column, and they are visible inside the DBmanager information window, however when using "Create Spatial index from the Layer Properties" QGIS seems to create the index -very fast by the way-, but the geopackage is not altered with any new table related to the layer.

And I can not see information in the DBmanager that the index was created.

In fact, when creating an index within the geopackage, only the option to create non-spatial indexes is possible, the option to create a spatial index appears gray/disabled.

When one creates the index from the properties of the layer, QGIS saves it inside the project but not inside the geopackage? Is this how it works?

Why is it not possible to create spatial indexes for geopackages using the DBmanager?

I'm just trying to understand the operation of the gpkg within QGIS

use QGIS 3.2.3
Data: geopackage with 2 tables
table to nonspatial: 4 million records
spatial b table: polygons approx 40k elements

Improve this question

1 Answer 1

Reset to default
8

When you create a new GeoPackage layer you can select if spatial index is created or not from the advanced options

enter image description here

If you want to add the index later goto layer properties and press the button

enter image description here

I could not find any way to check with QGIS if spatial index exists or not. The index is stored into virtual layer that is named as [rtree][tablename][geometry column] and you can use any SQLite capable application and see if the table is there.

enter image description here

Ogrinfo belongs to SQLite capable applications and spatial index exists if this command prints some ogrfeatures on screen

ogrinfo indextest.gpkg -sql "select * from sqlite_master where type='table' and name like 'rtree_indextest%' LIMIT 1"

Result:

Layer name: SELECT Geometry: None Feature Count: 1 Layer SRS WKT: (unknown) type: String (0.0) name: String (0.0) tbl_name: String (0.0) rootpage: Integer64 (0.0) sql: String (0.0) OGRFeature(SELECT):0 type (String) = table name (String) = rtree_indextest_geometry tbl_name (String) = rtree_indextest_geometry rootpage (Integer64) = 0 sql (String) = CREATE VIRTUAL TABLE "rtree_indextest_geometry" USING rtree(id, minx, maxx, miny, m axy)

EDIT GDAL supports nowadays also a more simple SQL for checking if the spatial index exists or not. Documentation:

> Starting with GDAL 2.2, the > “HasSpatialIndex(‘table_name’,’geom_col_name’)” statement can be used > for checking if the table has spatial index on the named geometry > column. Usage example: ogrinfo indextest.gpkg -sql "select HasSpatialIndex('test_table','geom')" ... Layer name: SELECT Geometry: Unknown (any) Feature Count: 1 Layer SRS WKT: (unknown) HasSpatialIndex: Integer (0.0) OGRFeature(SELECT):0 HasSpatialIndex (Integer) = 1

Value 0 means no/false and value 1 yes/true.

Improve this answer
5
  • There is a bug/feature request which - if I am reading correctly - has disabled the 'Create Spatial Index' buttons from DB Manager and Properties GUI, and those will only be available if its a SpatiaLite database. github.com/qgis/QGIS/issues/44513 Commented Jan 20, 2023 at 5:57
  • I am not sure what is the current situation. The GPKG driver gdal.org/drivers/vector/gpkg.html has that support nowadays "The following functions, with identical syntax and semantics as in Spatialite, are also available : CreateSpatialIndex(table_name String, geom_column_name String): creates a spatial index (RTree) on the specified table/geometry column" Commented Jan 20, 2023 at 8:39
  • Yer its a discussion for another thread. Just for now, i think its worthwhile mentioning that the 'Create Spatial Index' button is greyed out on a gpkg. Also in DB Manager, the Create Spatial Index button is not available for gpkg. (QGIS 3.22) Commented Jan 22, 2023 at 11:43
  • 1
    I made a test with QGIS 3.28.2 and for me the Create Spatial Index button is greyed out if the table already has a spatial index. The button is active if the table does not have spatial index. Commented Jan 22, 2023 at 13:39
  • 1
    I also added an easier ogrinfo syntax for checking if spatial index exists. Commented Jan 22, 2023 at 13:47

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.