How do I view the contents of spatial indexes of a GPKG file?

Question

I create an example GPKG file, example.gpkg, with layer our_layer:

import geopandas as gpd
import shapely.geometry

gpd.GeoSeries(
    [
        shapely.geometry.Polygon([[0, 0], [1, 0], [1, 1]]),
        shapely.geometry.Polygon([[1, 2], [3, 4], [5, 0]]),
    ]
).to_file("example.gpkg", layer="our_layer")

Using this answer, I check if our_layer has a spatial index:

ogrinfo -sql "SELECT HasSpatialIndex('our_layer', 'geom')" example.gpkg

Output:

INFO: Open of `example.gpkg'
      using driver `GPKG' successful.

Layer name: SELECT
Geometry: Unknown (any)
Feature Count: 1
Layer SRS WKT:
(unknown)
HasSpatialIndex: Integer (0.0)
OGRFeature(SELECT):0
  HasSpatialIndex (Integer) = 1

So our_layer's geometry column, geom has a spatial index. I would like to view the contents of this index. About SpatialIndex, this says:

SpatialIndex simply is a generic virtual table wrapping any Spatial Index defined into the current DB-file.

I would like to view the contents of the SpatialIndex on our_layer. Naively, I try:

ogrinfo -al -so example.gpkg SpatialIndex

Output:

INFO: Open of `example.gpkg'
      using driver `GPKG' successful.
ERROR 1: Couldn't fetch requested layer SpatialIndex.

and:

ogrinfo -sql "SELECT * FROM SpatialIndex" example.gpkg

Output:

INFO: Open of `example.gpkg'
      using driver `GPKG' successful.
ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(SELECT * FROM SpatialIndex):
  no such table: SpatialIndex

Which raises the question:

How do I view the contents of spatial indexes within a GPKG file?

Answer 1

The SpatialIndex virtual table is a spatialite feature, and GeoPackages are not spatialite files. These are both sqlite data base files with different spatial engines on top. They are mostly compatible at the SQL level but there are differences, and you've hit one.

If I convert a GeoPackage to Spatialite:

$ ogr2ogr -f SQLite out.sqlite gems_vienna.gpkg -dsco SPATIALITE=YES

I can get the SpatialIndex table (although its empty here, there's no error):

$ ogrinfo -sql "SELECT * from SpatialIndex" out.sqlite 
INFO: Open of `out.sqlite'
      using driver `SQLite' successful.

Layer name: SELECT
Geometry: None
Feature Count: 0
Layer SRS WKT:
(unknown)
f_table_name: String (0.0)
f_geometry_column: String (0.0)
search_frame: Binary (0.0)

In a GeoPackage, spatial index information is held in separate tables from the data table. These tables typically start rtree_{table name}_thing:

You can view all the tables in a geopackage using sqlite3 tools:

% sqlite3 study_polygons.gpkg
sqlite> .tables %rtree_%
rtree_chikwawa_geom         rtree_chileka_geom_parent 
rtree_chikwawa_geom_node    rtree_chileka_geom_rowid  
rtree_chikwawa_geom_parent  rtree_ndirande_geom       
rtree_chikwawa_geom_rowid   rtree_ndirande_geom_node  
rtree_chileka_geom          rtree_ndirande_geom_parent
rtree_chileka_geom_node     rtree_ndirande_geom_rowid 

Answer 2

In the GeoPackage standard the name of the rtree virtual table is fixed to follow the template

[rtree]_[table_name]_[geometry_column_name]

So for example for a Natural Earth gpkg run

  ogrinfo ne.gpkg -sql "select * from rtree_ne_10m_admin_0_countries_geom"
    ...
  OGRFeature(SELECT):14
  id (Integer64) = 117
  minx (Real) = -90.1147918701172
  maxx (Real) = -87.6931915283203
  miny (Real) = 13.1586351394653
  maxy (Real) = 14.4453744888306

OGRFeature(SELECT):15
  id (Integer64) = 136
  minx (Real) = -89.3637924194336
  maxx (Real) = -83.1304321289062
  miny (Real) = 12.979775428772
  maxy (Real) = 17.4186477661133
    ...

With SpatiaLite the situation is slightly different. SpatiaLite has another virtual table "SpatialIndex" in front of the rtree virtual tables. "SpatialIndex" can only be queried with fixed parameters:

• f_table_name
• f_geometry_column (optional in query)
• search_frame

The query "select * from SpatialIndex" without giving search_frame returns just an empty resultset. However, in the background there are the same SQLite rtree virtual tables and user can query them directly if they just know the name of the rtree table.

For the next test I converted the Natural Earth gpkg into Spatialite as

ogr2ogr -f SQLite -dsco spatialite=yes ne.sqlite ne.gpkg

The direct ogrinfo query of the rtree in the SpatiaLite db is

ogrinfo ne.sqlite -sql "select * FROM "idx_ne_10m_admin_0_countries_geom"
...
OGRFeature(SELECT):252
  pkid (Integer) = 249
  xmin (Real) = 144.902099609375
  xmax (Real) = 145.868911743164
  ymin (Real) = 14.1106615066528
  ymax (Real) = 20.5554065704346

OGRFeature(SELECT):253
  pkid (Integer) = 251
  xmin (Real) = 154.388519287109
  xmax (Real) = 154.391311645508
  ymin (Real) = -21.0300464630127
  ymax (Real) = -21.0287380218506
...