QGIS – How QGIS 3.2.3 Handles or Creates Spatial Indexes in a Geopackage

gdalgeopackageqgissqlite

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

Best Answer

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)
Related Question