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

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 Default 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.