Geometry Index

Extension Title

Geometry Index

Introduction

This extension defines a SQLite version agnostic way to index user feature table geometries by their bounding envelopes for fast ranged searches. Mobile implementations, including Android and iOS, often use earlier versions of SQLite and may not be able to rely on the R*Tree Module implementation. Each geometry in a feature table is indexed by it's geometry id and x, y, z, and m value ranges. The geometry index can be queried for fast retrieval of only geometries overlapping a desired envelope bounds.

It is recommended to use the OGC GeoPackage RTree Spatial Indexes Extension over this extension whenever possible.

NGA Library RTree Support

GeoPackage iOS - read and write support in version 2.0.1, expanded support in version 3.1.0
GeoPackage Android - read support in version 3.1.0
GeoPackage Java - read and write support in version 2.0.1, expanded support in version 3.1.0

Extension Author

National Geospatial-Intelligence Agency, author_name nga

Extension Name or Template

nga_geometry_index

Extension Type

New Requirement

Applicability

This extension applies to the id column and geometry column (as specified in column_name in the gpkg_geometry_columns table) of feature user data tables (2.1.6). The envelope (range of x, y, z, and m values) of the geometry column is indexed by the id column in the new extensions tables.

Scope

Read-write

Requirements

GeoPackage

Extension Table Entry

The Geometry Index extension is registered into the gpkg_extensions table as follows:

`gpkg_extensions`
table_name	column_name	extension_name	definition	scope
user feature table name	user feature table geometry column name	`nga_geometry_index`	URL to this extension description	read-write

Table Index Table

The Table Index Table contains indexed feature table names and the timestamp of when they were last indexed. The last_indexed value can be compared against the the gpkg_contents table last_change value to check if the index is up to date.

`nga_table_index`
Column Name	Type	Description	Null	Default	Key
`table_name`	TEXT	Feature Table name, foreign key to `gpkg_extensions`	no		PK, FK
`last_indexed`	DATETIME	Timestamp value of the last time the table was indexed in ISO 8601 format as defined by the strftime function %Y-%m-%dT%H:%M:%fZ format	yes


CREATE TABLE nga_table_index (
  table_name TEXT NOT NULL PRIMARY KEY,
  last_indexed DATETIME
);

Geometry Index Table

The Geometry Index Table contains the min and max range of x, y, z, and m values for geometries in a feature user data table. The table_name column is a foreign key to the nga_table_index table. The geom_id column contains the id column value of the feature user data table. The z and m values are optional.

`nga_geometry_index`
Column Name	Type	Description	Null	Key
`table_name`	TEXT	Feature Table name, foreign key to `nga_table_index`	no	PK, FK
`geom_id`	INTEGER	Geometry Id, `id` column value in the feature user data table as specified in `column_name` in the `gpkg_geometry_columns` table	no	PK
`min_x`	DOUBLE	Min x value	no
`max_x`	DOUBLE	Max x value	no
`min_y`	DOUBLE	Min y value	no
`max_y`	DOUBLE	Max y value	no
`min_z`	DOUBLE	Min z value	yes
`max_z`	DOUBLE	Max z value	yes
`min_m`	DOUBLE	Min m value	yes
`max_m`	DOUBLE	Max m value	yes


CREATE TABLE nga_geometry_index (
  table_name TEXT NOT NULL,
  geom_id INTEGER NOT NULL,
  min_x DOUBLE NOT NULL,
  max_x DOUBLE NOT NULL,
  min_y DOUBLE NOT NULL,
  max_y DOUBLE NOT NULL,
  min_z DOUBLE,
  max_z DOUBLE,
  min_m DOUBLE,
  max_m DOUBLE,
  CONSTRAINT pk_ngi PRIMARY KEY (table_name, geom_id),
  CONSTRAINT fk_ngi_nti_tn FOREIGN KEY (table_name) REFERENCES nga_table_index(table_name)
);

GeoPackage SQLite Configuration

None

GeoPackage SQLite Extension

None

Examples

The rivers GeoPackage has a rivers feature table that is indexed.

The example GeoPackage has indexed feature tables.