OGC® GeoPackage Encoding Standard

F.1. GeoPackage Non-Linear Geometry Types

Introduction

Clause 2.1.4 of the GeoPackage Version 1 Encoding Standard specifies support for the Geometry, Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection geometry types in the GeoPackageBinary geometry encoding format specified in clause 2.1.3. This extension specifies support for the additional CircularString, CompoundCurve, CurvePolygon, MultiCurve, MultiSurface, Curve, and Surface geometry types in the GeoPackage Binary geometry encoding format using the codes from Table 22.

Extension Author

GeoPackage SWG, author_name gpkg

Extension Name or Template

Extension names are constructed from the gpkg_geom_<gname> template where <gname> is the uppercase name of the extension geometry type from Table 22.

Extension Type

Applicability

This extension applies to any column specified in the gpkg_geometry_columns table.

Scope

Requirements

GeoPackage

Requirement 67

An extension name to specify a feature geometry extension type SHALL be defined for the "gpkg" author name using the "gpkg_geom_<gname>" template where <gname> is the uppercase name of the extension geometry type from Geometry Types (Normative) used in a GeoPackage.

Requirement 68

A GeoPackage that contains a gpkg_geometry_columns table or view with row records that specify extension geometry_type_name column values SHALL contain a gpkg_extensions table that contains row records with table_name and column_name values from the gpkg_geometry_columns row records that identify extension type uses, and extension_name column values for each of those geometry types constructed per the previous requirement [extension_geometry_types_extensions_name].

GeoPackage SQLite Configuration

GeoPackage SQLite Extension

Requirement 69

SQL functions that operate on GeoPackageBinary geometries as specified in other extensions SHALL operate correctly on the non-linear geometries specified in this extension.

Abstract Test Suite

GeoPackage Extension Types

Test Case ID	/extensions/geometry_types/data_values_geometry_type_name
Test Purpose	Verify that only allowed geometry types (including extended non-linear geometry types) are in use.
Test Method	Run test /opt/features/geometry_columns/data/data_values_geometry_type_name, but the values in 3a may also include the values from Table 28 in Annex G.
Reference
Test Type

Test Case ID	/extensions/geometry_types/all_types_test_data
Test Purpose	Verify that geometries non-linear geometry types are stored in valid GeoPackageBinary format encodings.
Test Method	SELECT table_name FROM gpkg_geometry_columns Not testable if returns an empty result set SELECT table_name AS tn, column_name AS cn FROM gpkg_geometry_columns WHERE table_name IN (SELECT table_name FROM gpkg_contents WHERE data_type = 'features'), Fail if returns an empty result set For each row from step 3 SELECT cn FROM tn; For each row from step a, log fail if GeoPackageBinary "X" type flag is 1 For each row from step a, if bytes 2-5 of cn.wkb as uint32 in endianness of gc.wkb byte 1of cn from #1 are a geometry type value from Annex G Table 28, then Log cn.header values, wkb endianness and geometry type ii. If cn.wkb is not correctly encoded per ISO 13249-3 clause 5.1.46 then log fail iii. If cn.flags.E is 1 - 4 and some cn.wkbx is outside of cn.envelope.minx,maxx then log fail iv. If cn.flags.E is 1 - 4 and some gc.wkby is outside of cn.envelope.miny,maxy then log fail If cn.flags.E is 2,4 and some gc.wkb.z is outside of cnenvelope.minz,maxz then log fail vi. If cn.flags.E is 3,4 and some gc.wkb.m is outside of cn.envelope.minm,maxm then log fail vii. If cn.flags.E is 5-7 then log fail viii. Otherwise log pass Log pass if log contains pass and no fails
Reference
Test Type

Extensions Name

Test Case ID	/extensions/geometry_types/extension_name
Test Purpose	Verify that an extension name in the form gpkg_geom_<gname> is defined for each <gname> extension geometry type from Annex G used in a GeoPackage.
Test Method	SELECT table_name, column_name FROM gpkg_geometry_columns WHERE table_name IN (SELECT table_name FROM gpkg_contents WHERE data_type == 'features')) Not testable if result set is empty For each row result set table_name, column_name from step 3 SELECT result_set_column_name FROM result_set_table_name For each geometry column value from step a If the first two bytes of each geometry column value are "GP", then /opt/extension_mechanism/data/table_def Fail if failed SELECT ST_GeometryType(geometry column value) AS <gtype>; SELECT extension_name FROM gpkg_extensions WERE table_name = result_set_table_name AND column_name = result_set_column_name AND extension_name = \'gpkg_geom_' \|\| <gtype> Fail if result set is empty Log pass otherwise Pass if logged pass and no fails
Reference
Test Type

Extensions Row

Test Case ID	/extensions/geometry_types/extension_row
Test Purpose	Verify that the gpkg_extensions table contains a row with an extension_name in the form gpkg_geom_<gname> for each table_name and column_name in the gpkg_geometry_columns table with a <gname> geometry_type_name.
Test Method	SELECT table_name, column_name, geometry_type_name FROM gpkg_geometry_columns Not testable if no results For each result If geometry_type_name is an extended geometry type SELECT extension_name FROM gpkg_extensions WHERE table_name = '{table_name}' AND column_name = '{column_name}" Fail if result set does not contain a row with an extension_name of gpkg_geom_{geometry_type_name} Pass if no fails
Reference
Test Type

F.3. R-tree Spatial Indexes

Introduction

The R-tree Spatial Indexes extension provides a means to encode an R-tree index for geometry values in a GeoPackage. An R-tree index [B28] provides a significant performance advantage for searches with basic envelope spatial criteria that return subsets of the rows in a feature table with a non-trivial number (thousands or more) of rows.^[K26]

Extension Author

GeoPackage SWG, author_name gpkg.

Extension Name or Template

Extension Type

Applicability

This extension applies to any column specified in the gpkg_geometry_columns table.

Scope

Write-only, because it does not change the result of reads, although it may improve their performance.

Requirements

GeoPackage

Requirement 75

The "gpkg_rtree_index" extension name SHALL be used as a gpkg_extensions table extension_name column value to specify implementation of spatial indexes on a geometry column.

Requirement 76

A GeoPackage that implements spatial indexes SHALL have a gpkg_extensions table that contains a row for each spatially indexed column with extension_name "gpkg_rtree_index", the table_name of the table with a spatially indexed column, the column_name of the spatially indexed column, and a scope of "write-only".

Requirement 77

A GeoPackage SHALL implement spatial indexes on feature table geometry columns using the SQLite Virtual Table R-trees and triggers specified below. The tables below contain SQL templates with variables. Replace the following template variables with the specified values to create the required SQL statements:
<t>: The name of the feature table containing the geometry column
<c>: The name of the geometry column in <t> that is being indexed
<i>: The name of the integer primary key column in <t> as specified in Requirement 29

Create Virtual Table

R-tree spatial indexes on geometry columns SHALL be created using the SQLite Virtual Table R-tree extension. An application that creates a spatial index SHALL create it using the following SQL statement template:

CREATE VIRTUAL TABLE rtree_<t>_<c> USING rtree(id, minx, maxx, miny, maxy)

where <t> and <c> are replaced with the names of the feature table and geometry column being indexed. The R-tree function id parameter becomes the virtual table 64-bit signed integer primary key id column, and the min/max x/y parameters are min- and max-value pairs (stored as 32-bit floating point numbers) for each dimension that become the virtual table data columns that are populated to create the spatial R-tree index.

Load Spatial Index Values

The indexes provided by the SQLite Virtual Table R-tree extension are not automatic indexes. This means the index data structure needs to be manually populated, updated and queried. Each newly created spatial index SHALL be populated using the following SQL statement

INSERT OR REPLACE INTO rtree_<t>_<c>
  SELECT <i>, ST_MinX(<c>), ST_MaxX(<c>), ST_MinY(<c>), ST_MaxY(<c>) FROM <t> WHERE <c> NOT NULL AND NOT ST_IsEmpty(<c>);

where <t> and <c> are replaced with the names of the feature table and geometry column being indexed and <i> is replaced with the name of the feature table integer primary key column.

Define Triggers to Maintain Spatial Index Values

For each spatial index in a GeoPackage, corresponding insert, update and delete triggers that update the spatial index SHALL be present on the indexed geometry column. These spatial index triggers SHALL be defined as follows:

/* Conditions: Insertion of non-empty geometry
   Actions   : Insert record into R-tree */
CREATE TRIGGER rtree_<t>_<c>_insert AFTER INSERT ON <t>
  WHEN (new.<c> NOT NULL AND NOT ST_IsEmpty(NEW.<c>))
BEGIN
  INSERT OR REPLACE INTO rtree_<t>_<c> VALUES (
    NEW.<i>,
    ST_MinX(NEW.<c>), ST_MaxX(NEW.<c>),
    ST_MinY(NEW.<c>), ST_MaxY(NEW.<c>)
  );
END;

/* rtree_<t>_<c>_update1 is deprecated and is replaced by
    rtree_<t>_<c>_update6 and rtree_<t>_<c>_update7 */

/* Conditions: Update of geometry column to empty geometry
               No row ID change
   Actions   : Remove record from R-tree */
CREATE TRIGGER rtree_<t>_<c>_update2 AFTER UPDATE OF <c> ON <t>
  WHEN OLD.<i> = NEW.<i> AND
       (NEW.<c> ISNULL OR ST_IsEmpty(NEW.<c>))
BEGIN
  DELETE FROM rtree_<t>_<c> WHERE id = OLD.<i>;
END;

/* rtree_<t>_<c>_update3 is deprecated and is replaced by
    rtree_<t>_<c>_update5 */

/* Conditions: Update of any column
               Row ID change
               Empty geometry
   Actions   : Remove record from R-tree for old and new <i> */
CREATE TRIGGER rtree_<t>_<c>_update4 AFTER UPDATE ON <t>
  WHEN OLD.<i> != NEW.<i> AND
       (NEW.<c> ISNULL OR ST_IsEmpty(NEW.<c>))
BEGIN
  DELETE FROM rtree_<t>_<c> WHERE id IN (OLD.<i>, NEW.<i>);
END;

/* Conditions: Update of any column
               Row ID change
               Non-empty geometry
   Actions   : Remove record from R-tree for old <i>
               Insert record into R-tree for new <i> */
CREATE TRIGGER rtree_<t>_<c>_update5 AFTER UPDATE ON <t>
  WHEN OLD.<i> != NEW.<i> AND
       (NEW.<c> NOTNULL AND NOT ST_IsEmpty(NEW.<c>))
BEGIN
  DELETE FROM rtree_<t>_<c> WHERE id = OLD.<i>;
  INSERT OR REPLACE INTO rtree_<t>_<c> VALUES (
    NEW.<i>,
    ST_MinX(NEW.<c>), ST_MaxX(NEW.<c>),
    ST_MinY(NEW.<c>), ST_MaxY(NEW.<c>)
  );
END;

/* Conditions: Update a non-empty geometry with another non-empty geometry
   Actions   : Replace record from R-tree for <i> */
CREATE TRIGGER rtree_<t>_<c>_update6 AFTER UPDATE OF <c> ON <t>
  WHEN OLD.<i> = NEW.<i> AND
       (NEW.<c> NOTNULL AND NOT ST_IsEmpty(NEW.<c>)) AND
       (OLD.<c> NOTNULL AND NOT ST_IsEmpty(OLD.<c>))
BEGIN
  UPDATE rtree_<t>_<c> SET
    minx = ST_MinX(NEW.<c>),
    maxx = ST_MaxX(NEW.<c>),
    miny = ST_MinY(NEW.<c>),
    maxy = ST_MaxY(NEW.<c>)
  WHERE id = NEW.<i>;
END;

/* Conditions: Update a null/empty geometry with a non-empty geometry
   Actions   : Insert record into R-tree for new <i> */
CREATE TRIGGER rtree_<t>_<c>_update7 AFTER UPDATE OF <c> ON <t>
  WHEN OLD.<i> = NEW.<i> AND
       (NEW.<c> NOTNULL AND NOT ST_IsEmpty(NEW.<c>)) AND
       (OLD.<c> ISNULL OR ST_IsEmpty(OLD.<c>))
BEGIN
  INSERT INTO rtree_<t>_<c> VALUES (
    NEW.<i>,
    ST_MinX(NEW.<c>), ST_MaxX(NEW.<c>),
    ST_MinY(NEW.<c>), ST_MaxY(NEW.<c>)
  );
END;

/* Conditions: Row deleted
   Actions   : Remove record from R-tree for old <i> */
CREATE TRIGGER rtree_<t>_<c>_delete AFTER DELETE ON <t>
  WHEN old.<c> NOT NULL
BEGIN
  DELETE FROM rtree_<t>_<c> WHERE id = OLD.<i>;
END;

where <t> and <c> are replaced with the names of the feature table and geometry column being indexed and <i> is replaced with the name of the feature table integer primary key column.

GeoPackage Versions 1.2.0 and prior have an incorrect update3 trigger that will fail in certain circumstances. GeoPackage Version 1.2.1 fixes this issue by replacing the update3 trigger. In GeoPackage 1.4.0, the decision was made to deprecate the update3 trigger and replace it with the new update5 trigger. This will allow clients to easily detect whether the correct trigger is in place. It is strongly recommended to update older GeoPackages by dropping the update3 trigger and adding the update5 trigger. The GeoPackage Executable Test Suite has been updated to accept either version of the trigger in older versions and to mandate the corrected version in versions after 1.2.0.

In addition, GeoPackage Versions 1.3.1 and prior have a single update1 trigger that is incompatible with upsert statements. GeoPackage Version 1.4.0 fixes this issue by replacing the update1 trigger with the new update6 and update7 triggers. It is strongly recommended to update older GeoPackages by dropping the update1 trigger and adding the update6 and update7 triggers. The GeoPackage Executable Test Suite will be updated to accept either version of the trigger in older versions and to mandate the corrected version in versions after 1.3.1.

If present, the deprecated triggers can be dropped with a DROP TRIGGER command.

DROP TRIGGER rtree_<t>_<c>_update1;
DROP TRIGGER rtree_<t>_<c>_update3;

GeoPackage SQLite Configuration

Definition of SQLite configuration settings

Setting compile or runtime	Option	Shall / Not (Value)	Discussion
compile	SQLITE_ENABLE_RTREE	Shall	R-trees are used for GeoPackage Spatial Indexes
compile	SQLITE_RTREE_INT_ONLY	Not	R-trees with floating point values are used for GeoPackage spatial indexes

GeoPackage SQLite Extension

Definition of SQL functions

SQL Function	Description	Use
ST_IsEmpty(geom Geometry): integer	Returns 1 if geometry value is empty, 0 if not empty, NULL if geometry value is NULL	Test if a geometry value corresponds to the empty set
ST_MinX(geom Geometry): real	Returns the minimum X value of the bounding envelope of a geometry	Update the spatial index on a geometry column in a feature table
ST_MaxX(geom Geometry): real	Returns the maximum Y value of the bounding envelope of a geometry	Update the spatial index on a geometry column in a feature table
ST_MinY(geom Geometry): real	Returns the minimum X value of the bounding envelope of a geometry	Update the spatial index on a geometry column in a feature table
ST_MaxY(geom Geometry): real	Returns the maximum Y value of the bounding envelope of a geometry	Update the spatial index on a geometry column in a feature table

The minimum bounding indexes created within the R-tree Extension for GeoPackage should reflect the appropriate bounding area for the indexed feature. However, due to varying precision implementations, it is not practical to assert this practice through a requirement or test. Clients should exercise care when using these indexes during queries because the SQLite R-tree module might round bounding boxes slightly outward (up to 0.000012%). Queries using spatial indexes should contain slightly expanded bounding boxes to guard against this.

Abstract Test Suite

Extension Name

Test Case ID	/extensions/rtree/extension_name
Test Purpose	Verify that spatial index extensions are registered using the "gpkg_rtree_index" name in the gpkg_extensions table.
Test Method	SELECT COUNT(*) FROM gpkg_extensions WHERE extension_name = 'gpkg_rtree_index'; Extension not testable if count = 0
Reference
Test Type

Extensions Row

Test Case ID	/extensions/rtree/extension_row
Test Purpose	Verify that the "gpkg_rtree_index" extension name is used to register spatial index extensions.
Test Method	SELECT table_name, column_name, scope FROM gpkg_extensions WHERE extension_name = 'gpkg_rtree_index' Not testable if result set is empty Fail if any column_name is NULL Fail if any scope is not 'write-only' Fail if any column_name is not a column in table_name Pass otherwise
Reference
Test Type

Implementation

Test Case ID	/reg_ext/features/spatial_indexes/implementation
Test Purpose	Verify the correct implementation of spatial indexes on feature table geometry columns.
Test Method	SELECT table_name, column_name FROM gpkg_geometry_columns WHERE table_name IN (SELECT table_name FROM gpkg_extensions WHERE extension_name == 'gpkg_rtree_index') Not testable if result set is empty For each row table_name, column_name from step 1 SELECT sql FROM sqlite_master WHERE tbl_name = 'rtree_' \|\| result_set_table_name \|\| '_' \|\| result_set_column_name Fail if returned sql != 'CREATE VIRTUAL TABLE "rtree_' \|\| result_set_table_name \|\| '_' \|\| result_set_column_name \|\|'" USING rtree(id, minx, maxx, miny, maxy)' SELECT sql FROM sqlite_master WHERE type = 'trigger' AND name = 'rtree_' \|\| result_set_table_name \|\| '_' \|\| result_set_column_name \|\| '_insert' Fail if returned sql != result of populating insert trigger template using result_set_table_name for <t> and result_set_column_name for <c> SELECT sql FROM sqlite_master WHERE type = 'trigger' AND name LIKE 'rtree_' \|\| result_set_table_name \|\| '_' \|\| result_set_column_name \|\| '_update%' ORDER BY name ASC Fail if returned sql != result of populating 4 5 update triggers templates using result_set_table_name for <t> and result_set_column_name for <c> Fail if the result set contains the deprecated `update1` and/or `update3` triggers. SELECT sql FROM sqlite_master WHERE type='trigger' AND name = 'rtree_' \|\| result_set_table_name \|\| '_' \|\| result_set_column_name \|\| '_delete' Fail if returned sql != result of populating delete trigger template using result_set_table_name for <t> and result_set_column_name for <c> Pass if no fails
Reference
Test Type

Test Case ID	/reg_ext/features/spatial_indexes/implementation/sql_functions
Test Purpose	Verify the correct implementation of sql functions used in spatial indexes on feature table geometry columns.
Test Method	Open Geometry Test Data Set GeoPackage with GeoPackage SQLite Extension For each Geometry Test Data Set <gtype_test> data table row for each geometry type in Annex G, for an assortment of srs_ids, for an assortment of coordinate values including empty geometries, without and with z and / or m values, in both big and little endian encodings: SELECT 'Fail' FROM <gtype_test> WHERE ST_IsEmpty(geom.) != empty SELECT 'Fail' FROM <gtype_test> WHERE ST_MinX(geom) != minx SELECT 'Fail' FROM <gtype_test> WHERE ST_MaxX(geom) != maxx SELECT 'Fail' FROM <gtype_test> WHERE ST_MinY(geom) != miny SELECT 'Fail' FROM <gtype_test> WHERE ST_MaxY(geom) != maxy Pass if no 'Fail' selected from step 2
Reference
Test Type

F.6. Zoom Other Intervals

Introduction

This extension of clause Zoom Levels allows zoom level intervals other than a factor of two.

In a GeoPackage, zoom levels are integers in sequence from 0 to n that identify tile matrix layers in a tile matrix set that contain tiles of decreasing spatial extent and finer spatial resolution. Adjacent zoom levels immediately precede or follow each other and differ by a value of 1. Pixel sizes are real numbers in the terrain units of the spatial reference system of a tile image specifying the dimensions of the real world area represented by one pixel. Pixel sizes MAY vary by a constant factor or by different factors or intervals between some or all adjacent zoom levels in a tile matrix set. In the commonly used "zoom times two" convention, pixel sizes vary by a factor of 2 between all adjacent zoom levels, as shown in the example in [tiles_factor2_example_appendix].

This extension enables use of "zoom other intervals" conventions with different factors or irregular intervals with pixel sizes chosen for intuitive cartographic representation of raster data, or to coincide with the original pixel size of commonly used global image products. See WMTS [I16] Annex E for additional examples of both conventions.

Extension Author

GeoPackage SWG, author_name gpkg

Extension Name or Template

Extension Type

Extension of Existing Requirement in clause 2.2.3.

Applicability

This extension applies to any table listed in the gpkg_contents table with a data_type of tiles.

Scope

Requirements

GeoPackage

Requirement 87

The "gpkg_zoom_other" extension name SHALL be used as a gpkg_extensions table extension name column value to specify implementation of other zoom intervals on a tile pyramid user data table as specified in Zoom Other Intervals.

Requirement 88

A GeoPackage that implements other zoom intervals SHALL have a gpkg_extensions table that contains a row for each tile pyramid user data table with other zoom intervals with extension_name "gpkg_zoom_other", the table_name of the table with other zoom intervals, and the "tile_data" column_name.

Requirement 89

Tile pyramid user data tables MAY have pixel sizes that vary by irregular intervals or by regular intervals other than a factor of two (the default) between adjacent zoom levels. Extends Requirement 35.

The pixel_x_size and / or pixel_y_size column values in the gpkg_tile_matrix table vary by irregular intervals or by regular intervals other than a factor of two (the default) between adjacent zoom levels for a particular tile matrix set pyramid table.

GeoPackage SQLite Configuration

GeoPackage SQLite Extension

Abstract Test Suite

Extensions Name

Test Case ID	/reg_ext/tiles/zoom_levels/data/zoom_other_ext_name
Test Purpose	Verify that the "gpkg_zoom_other" extension name is used to register tiles tables with other than factors of two zoom intervals.
Test Method	SELECT table_name FROM gpkg_contents WHERE data_type = \'tiles' Not testable if empty result set For each row table_name from step 1 SELECT zoom_level, pixel_x_size, pixel_y_size FROM gpkg_tile_matrix WHERE table_name = selected table name ORDER BY zoom_level ASC Not testable if returns empty result set Not testable if there are not two rows with adjacent zoom levels Not testable if no pair of rows for adjacent zoom levels have pixel_x_size or pixel_y_size values that differ by other than factors of two /opt/extension_mechanism/data/table_def Fail if failed SELECT * FROM gpkg_extensions WHERE table_name = selected table name AND extension_name = \'gpkg_zoom_other' Fail if returns an empty result set Log pass otherwise Pass if logged pass and no fails
Reference
Test Type

Extensions Row

Test Case ID	/reg_ext/tiles/zoom_levels/data/zoom_other_ext_row
Test Purpose	Verify that tiles tables with other than factors of two zoom intervals are registered using the "gpkg_zoom_other" extension name.
Test Method	/reg_ext/tiles/zoom_levels/data/zoom_other_ext_name
Reference
Test Type:

Zoom Interval

Test Case ID	/reg_ext/tiles/zoom_levels/data/zoom_intervals
Test Purpose	Verify that zoom level pixel sizes for tile matrix user data tables vary by factors of 2 between adjacent zoom levels in the tile matrix metadata table only for tile matrix sets that this extension does not apply to.
Test Method	Override test /opt/tiles/zoom_levels/data/zoom_times_two SELECT table_name AS tn FROM gpkg_contents WHERE data_type = \'tiles' For each row tn from step 2 WHEN (SELECT tbl_name FROM sqlite_master WHERE tbl_name = \'gpkg_extensions') = \'gpkg_extensions' THEN (SELECT table_name from gpkg_extensions WHERE extension_name = \'gpkg_zoom_other' AND table_name = \'tn') END; If returns empty result set, execute test /opt/tiles/zoom_levels/data/zoom_times_two Pass if no fails
Reference
Test Type

Introduction

Two tables in a GeoPackage provide a means of storing metadata in MIME [I21] encodings that are defined in accordance with any authoritative metadata specifications, and relating it to the entity stores (i.e., features, rasters, and tiles data) in a GeoPackage. These tables are intended to provide the support necessary to implement the hierarchical metadata models as defined in ISO 19115 [I28] and illustrated in Hierarchical Metadata Example One - ISO19115. and Raster or Tile Metadata Example. As GeoPackage data is captured and updated, the most local and specific detailed metadata changes associated with the new or modified data MAY be captured separately, and referenced to existing global and general metadata.

The gpkg_metadata table that contains metadata is described in clause Metadata Table, and the gpkg_metadata_reference table that relates gpkg_metadata to GeoPackage data is described in clause Metadata Reference Table. There is no GeoPackage requirement that such metadata be provided or that defined metadata be structured in a hierarchical fashion ^[K27]. This extension simply provides a mechanism for storing this information. If this extension is used, such metadata ^[K28] and data that relates it to GeoPackage contents should not be stored in other tables.

Extension Author

GeoPackage SWG, author_name gpkg

Extension Name or Template

Extension Type

Applicability

This extension applies to any content in the GeoPackage.

Scope

Requirements

Table Definitions

The first component of GeoPackage metadata is the gpkg_metadata table that MAY contain metadata in MIME [I21] encodings structured in accordance with any authoritative metadata specification, such as ISO 19115 [I28], ISO 19115-2 [B6], ISO 19139 [B7], Dublin Core [B8], CSDGM [B10], DDMS [B12], NMF/NMIS [B13], etc. The GeoPackage interpretation of what constitutes "metadata" is a broad one that includes UML models [B14] encoded in XMI [B15], GML Application Schemas [I30], ISO 19110 feature catalogues [B18], OWL [B20] and SKOS [B21] taxonomies, etc.

The md_standard_uri data value provides an identifier for the metadata structure (schema) specified by its definition authority. The structure (schema) information could be in whatever encoding is used by the definition authority, e.g. UML [B14], or IDEF1x [B16], or XML/Schema [I25][I26][I27], or RDF/S [B19].

The second component of GeoPackage metadata is the gpkg_metadata_reference table that links metadata in the gpkg_metadata table to data in the feature, and tiles tables defined in clauses 2.1.6 and 2.2.7. The gpkg_metadata_reference table is not required to contain any rows.

Every row in gpkg_metadata_reference that has a NULL value as md_parent_id forms the root of a metadata hierarchy.^[K31]

Table Data Values

gpkg_extensions

Requirement 140

GeoPackages with rows in the gpkg_extensions table with an extension_name of "gpkg_metadata" SHALL comply with this extension. GeoPackages complying with this extension SHALL have rows in the gpkg_extensions table as described in Table 14 (below).

Requirement 140 was updated as part of GeoPackage 1.2.1. In 1.1.0 and 1.2.0, the details of required gpkg_extensions rows were inadvertently left unspecified. While the executable test suite running on an older GeoPackage version will not generate a failure due to missing gpkg_extensions rows, it is recommended to update these rows to comply with the updated requirement on older versions as well.

The md_scope column in the gpkg_metadata table is the name of the applicable scope for the contents of the metadata column for a given row. The list of valid scope names and their definitions is provided in Table 15 below. The initial contents of this table were obtained from the ISO 19115 [I28], Annex B B.5.25 MD_ScopeCode code list, which was extended ^[K32] for use in the GeoPackage specification by addition of entries with "NA" as the scope code column in Table 12.

Requirement 94

Each md_scope column value in a gpkg_metadata table SHALL be one of the name column values from Table 15.

Each md_scope column value in a gpkg_metadata table SHOULD be one of the name column values from Table 15. However, this list is not exhaustive; new scopes are permitted.

Requirement 96

Every gpkg_metadata_reference table reference scope column value SHALL be one of 'geopackage', 'table', 'column', 'row', 'row/col' in lowercase.

Requirement 97

Every gpkg_metadata_reference table row with a reference_scope column value of 'geopackage' SHALL have a table_name column value that is NULL. Every other gpkg_metadata_reference table row SHALL have a table_name column value that references a value in the gpkg_contents table_name column.

Requirement 98

Every gpkg_metadata_reference table row with a reference_scope column value of 'geopackage','table' or 'row' SHALL have a column_name column value that is NULL. Every other gpkg_metadata_reference table row SHALL have a column_name column value that contains the name of a column in the SQLite table or view identified by the table_name column value.

Requirement 99

Every gpkg_metadata_reference table row with a reference_scope column value of 'geopackage', 'table' or 'column' SHALL have a row_id_value column value that is NULL. Every other gpkg_metadata_reference table row SHALL have a row_id_value column value that contains the ROWID of a row in the SQLite table or view identified by the table_name column value.

Requirement 100

Every gpkg_metadata_reference table row timestamp column value SHALL be in a DATETIME format as per Requirement 5.^[K37]

Requirement 101

Every gpkg_metadata_reference table row md_file_id column value SHALL be an id column value from the gpkg_metadata table.

Requirement 102

Every gpkg_metadata_reference table row md_parent_id column value that is NOT NULL SHALL be an id column value from the gpkg_metadata table that is not equal to the md_file_id column value for that row.

Abstract Test Suite

Table Definition

Test Case ID	/extensions/metadata/metadata/table_def
Test Purpose	Verify that the gpkg_metadata table exists and has the correct definition.
Test Method	PRAGMA TABLE_INFO(gpkg_metadata) Fail if returns an empty result set. Pass if the column names, types, nullability, default values, and primary, foreign and unique key constraints match all of those in the contents of Table 18. Column order, check constraint and trigger definitions, and other column definitions in the returned sql are irrelevant. Fail otherwise.
Reference
Test Type

Test Case ID	/extensions/metadata/metadata_reference/table_def
Test Purpose	Verify that the gpkg_metadata_reference table exists and has the correct definition.
Test Method	SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'gpkg_metadata_reference' Fail if returns an empty result set. Pass if the column names and column definitions in the returned Create TABLE statement in the sql column value, including data type, nullability, default values and primary, foreign and unique key constraints match all of those in the contents of Table 33. Column order, check constraint and trigger definitions, and other column definitions in the returned sql are irrelevant. Fail otherwise.
Reference
Test Type

Table Data Values

gpkg_extensions

Test Case ID	/extensions/metadata/extensions/data_values
Test Purpose	Verify that the gpkg_extensions table has the required rows.
Test Method	SELECT table_name, column_name, scope FROM gpkg_extensions WHERE extension_name = 'gpkg_metadata'; Not testable if returns an empty result set Fail if there are not exactly two rows For each row returned from step 1 Fail if scope is not "read-write" Fail if column_name is not NULL Fail if either table_name entry is not present Pass if no fails
Reference
Test Type:

Test Case ID	/extensions/metadata/metadata/data_values_md_scope
Test Purpose	Verify that each of the md_scope column values in a gpkg_metadata table is one of the name column values from Table 15.
Test Method	. SELECT md_scope FROM gpkg_metadata . Not testable if returns an empty result set . For each row returned from step 1 .. Fail if md_scope value not one of the name column values from Table 15. . Pass if no fails
Reference
Test Type:

Test Case ID	/extensions/metadata/metadata_reference/reference_scope
Test Purpose	Verify that gpkg_metadata_reference table reference_scope column values are valid.
Test Method	SELECT reference_scope FROM gpkg_metadata_reference Not testable if returns an empty result set SELECT reference_scope FROM gpkg_metadata_reference WHERE reference_scope NOT IN ('geopackage','table','column','row','row/col') Fail if does not return an empty result set Pass otherwise.
Reference
Test Type

Test Case ID	/extensions/metadata/metadata_reference/table_name
Test Purpose	Verify that gpkg_metadata_reference table_name column values are NULL for rows with reference_scope values of 'geopackage', and reference gpkg_contents table_name values for all other reference_scope values.
Test Method	SELECT table_name FROM gpkg_metadata_reference Not testable if returns an empty result set SELECT table_name FROM gpkg_metadata_reference WHERE reference_scope = \'geopackage' Fail if result set contains any non-NULL values SELECT table_name FROM metadata_reference WHERE reference_scope != \'geopackage' AND table_name NOT IN (SELECT table_name FROM gpkg_contents) Fail if result set is not empty Pass otherwise.
Reference
Test Type

Test Case ID	/extensions/metadata/metadata_reference/column_name
Test Purpose	Verify that gpkg_metadata_reference column_name column values are NULL for rows with reference scope values of 'geopackage', 'table', or 'row', and contain the name of a column in table_name table for other reference scope values.
Test Method	SELECT column_name FROM gpkg_metadata_reference Not testable if returns an empty result set SELECT column_name FROM gpkg_metadata_reference WHERE reference_scope IN ('geopackage', 'table', 'row') Fail if result set contains any non-NULL values SELECT <table_name>, <column_name> FROM metadata_reference WHERE reference_scope NOT IN ('geopackage', 'table', 'row') For each row from step 5 SELECT sql FROM sqlite_master WHERE type = \'table' AND tbl_name = \'<table_name>' Fail if returns an empty result set. Fail if the one of the column names in the returned sql Create TABLE statement is not <column_name> Log pass otherwise Pass if logged pass and no fails.
Reference
Test Type

Test Case ID	/extensions/metadata/metadata_reference/row_id_value
Test Purpose	Verify that gpkg_metadata_reference row_id_value column values are NULL for rows with reference scope values of 'geopackage', 'table', or 'row', and contain the ROWID of a row in the table_name for other reference scope values.
Test Method	SELECT row_id_value FROM gpkg_metadata_reference Not testable if returns an empty result set SELECT row_id_value FROM gpkg_metadata_reference WHERE reference_scope IN ('geopackage', 'table', 'row') Fail if result set contains any non-NULL values For each SELECT <table_name>, <row_id_value> FROM gpkg_metadata_reference WHERE reference_scope NOT IN ('geopackage', 'table', 'row') For each row from step 5 SELECT * FROM <table_name> WHERE ROWID = <row_id_value> Fail if result set is empty Log pass otherwise Pass if logged pass and no fails.
Reference
Test Type

Test Case ID	/extensions/metadata/metadata_reference/timestamp
Test Purpose	Verify that every gpkg_metadata_reference table row timestamp column value is in ISO 8601 UTC format.
Test Method	SELECT timestamp from gpkg_metadata_reference. Not testable if returns an empty result set For each row from step 1 Fail if format of returned value does not match the format as per Requirement 5 Log pass otherwise Pass if logged pass and no fails.
Reference
Test Type

Test Case ID	/extensions/metadata/metadata_reference/md_file_id
Test Purpose	Verify that every gpkg_metadata_reference table row md_file_id column value references a gpkg_metadata id column value.
Test Method	PRAGMA foreign_key_check('geometry_columns') Fail if returns any rows with a fourth column foreign key index value of 0
Reference
Test Type

Test Case ID	/extensions/metadata/metadata_reference/md_parent_id
Test Purpose	Verify that every gpkg_metadata_reference table row md_parent_id column value that is not null is an id column value from the gpkg_metadata_table that is not equal to the md_file_id column value for that row.
Test Method	SELECT md_file_id FROM gpkg_metadata_reference Not testable if returns an empty result set SELECT gmr.md_file_id, gmr.md_parent_id FROM gpkg_metadata_reference AS gmr WHERE gmr.md_file_id == gmr.md_parent_id Fail if result set is not empty SELECT gmr.md_file_id, gmr.md_parent_id, gm.id FROM gpkg_metadata_reference AS gmr LEFT OUTER JOIN gpkg_metadata gm ON gmr.md_parent_id =gm.id Fail if any result set gm.id values are NULL Pass otherwise
Reference
Test Type

Table Definition SQL

GeoPackage versions 1.2.1 and prior had an optional validation triggers gpkg_metadata and gpkg_metadata_reference. These triggers were determined to be excessively strict and were not enabling interoperability in a meaningful way so they have been removed in version 1.3.0.

Example: gpkg_metadata_reference SQL insert statement (Informative)

INSERT INTO gpkg_metadata_reference VALUES (
  'table',
  'sample_rasters',
  NULL,
  NULL,
  '2012-08-17T14:49:32.932Z',
  98,
  99
)

Examples (Informative)

Suppose we have this metadata:

and this reference table definition:

1) Consider a geographic data provider generating vector mapping data for three Administrative areas(A, B and C). … The metadata could be carried exclusively at Dataset Series level.

Then we need a record for each layer table for the three admin areas, like this:

INSERT INTO gpkg_metadata_reference VALUES (
'table', /* reference type */
'roads', /* table name */
'undefined', /* column_name */
-1, /* row_id_value  */
(datetime('now')),
3,  /* md_file_id  */
0  /* md_parent_id  */
)

2) After some time alternate vector mapping of Administrative area A becomes available. The metadata would then be extended for Administrative area A, to describe the new quality date values. These values would supersede those given for the Dataset series, but only for Administrative area A. The metadata for B and C would remain unchanged. This new metadata would be recorded at Dataset level.

Then we need a record for each layer table in "A" like this:

INSERT INTO gpkg_metadata_reference VALUES (
'table', /* reference type */
'roads', /* table name */
'undefined', /* column_name */
-1, /* row_id_value  */
(datetime('now')),
 4,  /* md_file_id  */
3  /* md_parent_id  */
)

3) Eventually further data becomes available for Administrative area A, with a complete re-survey of the road network. Again this implies new metadata for the affected feature types. This metadata would be carried at Feature type level for Administrative area A. All other metadata relating to other feature types remains unaffected. Only the metadata for roads in Administrative area A is modified. This road metadata is recorded at Feature type level.

Then we need a record for each layer table for the roads network, like this:

INSERT INTO gpkg_metadata_reference VALUES (
'table', /* reference type */
'roads', /* table name */
'undefined', /* column_name */
-1, /* row_id_value  */
(datetime('now')),
5,  /* md_file_id  */
4  /* md_parent_id  */
)

4) An anomaly in the road survey is identified, in that all Overhead clearances for the Administrative area A have been surveyed to the nearest metre. These are re-surveyed to the nearest decimetre. This re-survey implies new metadata for the affected attribute type 'Overhead Clearance'. All other metadata for Administrative area A remains unaffected. This 'Overhead Clearance' metadata is recorded at Attribute Type level.

Then we need a record for each layer table in the roads network with attribute type 'Overhead Clearance', like this;

INSERT INTO gpkg_metadata_reference VALUES (
'column', /* reference type */
'roads', /* table name */
'overhead_clearance', /* column_name */
-1, /* row_id_value  */
(datetime('now')),
7,  /* md_file_id  */
4  /* md_parent_id  */
)

5) A new bridge is constructed in Administrative area A. This new data is reflected in the geographic data for Administrative area A, and new metadata is required to record this new feature. All other metadata for Administrative area A remains unaffected. This new feature metadata is recorded at Feature instance level.

Then we need a record for the bridge layer table row for the new bridge, like this:

INSERT INTO gpkg_metadata_reference VALUES (
'row', /* reference type */
'bridge', /* table name */
'undefined', /* column_name */
987, /* row_id_value  */
(datetime('now')),
6,  /* md_file_id  */
4  /* md_parent_id  */
)

6) The overhead clearance attribute of the new bridge was wrongly recorded, and is modified. Again this new attribute requires new metadata to describe the modification. All other metadata for Administrative area A remains unaffected. This new attribute metadata is recorded at Attribute instance level.

Then we need a record for the clearance attribute value, like this:

INSERT INTO gpkg_metadata_reference VALUES (
'row/col', /* reference type */
'bridge', /* table name */
'overhead_clearance', /* column_name */
987, /* row_id_value  */
(datetime('now')),
8,  /* md_file_id  */
4  /* md_parent_id  */
)

This use case demonstrates a mechanism to indicate which data in a GeoPackage that was originally loaded with data from one or more services has been collected or updated since the initial load, and therefore MAY need to be uploaded to update the original services (e.g., WFS, WCS, WMTS).

Suppose a user with a mobile handheld device goes out in the field and collects observations of a new "Point of Interest" (POI) feature type, and associated metadata about the field session, the new feature type, some POI instances and some of their attributes (e.g., spatial accuracy, attribute accuracy) that results in the following additional metadata:

(This example assumes that the field session data is still considered "raw" and won’t be considered a data set or part of a data series until it has been verified and cleaned, but if that is wrong then additional series and data set metadata could be added.)

Then we need a gpkg_metadata_reference record for the field session for the new POI table, whose md_parent_id is undefined:

INSERT INTO gpkg_metadata_reference VALUES (
'table', /* reference type */
'poi', /* table name */
'undefined', /* column_name */
-1, /* row_id_value  */
(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
1,  /* md_file_id  */
0  /* md_parent_id  */
)

Then we need a gpkg_metadata_reference record for the feature type for the new POI table, whose md_parent_id is that of the field session:

INSERT INTO gpkg_metadata_reference VALUES (
'table', /* reference type */
'poi', /* table name */
'undefined', /* column_name */
-1, /* row_id_value  */
(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
10,  /* md_file_id  */
1  /* md_parent_id  */
)

Then we need gpkg_metadata_reference records for the poi feature instance rows, whose md_parent_id is that of the field session:

INSERT INTO gpkg_metadata_reference VALUES (
'row', /* reference type */
'poi', /* table name */
'undefined', /* column_name */
1, /* row_id_value  */
(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
11,  /* md_file_id  */
1  /* md_parent_id  */
)

INSERT INTO gpkg_metadata_reference VALUES (
'row', /* reference type */
'poi', /* table name */
'undefined', /* column_name */
2, /* row_id_value  */
14,  /* md_file_id  */
1  /* md_parent_id  */
)

INSERT INTO gpkg_metadata_reference VALUES (
'row', /* reference type */
'poi', /* table name */
'undefined', /* column_name */
3, /* row_id_value  */
(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
17,  /* md_file_id  */
1  /* md_parent_id  */
)

And finally we need gpkg_metadata_reference records for the poi attribute instance metadata , whose md_parent_id is that of the field session:

INSERT INTO gpkg_metadata_reference VALUES (
'row/col', /* reference type */
'poi', /* table name */
'point', /* column_name */
1, /* row_id_value  */
(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
12,  /* md_file_id  */
1  /* md_parent_id  */
)

INSERT INTO gpkg_metadata_reference VALUES (
'row/col', /* reference type */
'poi', /* table name */
'point', /* column_name */
2, /* row_id_value  */
(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
15,  /* md_file_id  */
1  /* md_parent_id  */
)

INSERT INTO gpkg_metadata_reference VALUES (
'row/col', /* reference type */
'poi', /* table name */
'point', /* column_name */
3, /* row_id_value  */
(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
18,  /* md_file_id  */
1  /* md_parent_id  */
)

INSERT INTO gpkg_metadata_reference VALUES (
'row/col', /* reference type */
'poi', /* table name */
'category', /* column_name */
1, /* row_id_value  */
(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
13,  /* md_file_id  */
1  /* md_parent_id  */
)

INSERT INTO gpkg_metadata_reference VALUES (
'row/col', /* reference type */
'poi', /* table name */
'category', /* column_name */
2, /* row_id_value  */
(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
16,  /* md_file_id  */
1  /* md_parent_id  */
)

INSERT INTO gpkg_metadata_reference VALUES (
'row/col', /* reference type */
'poi', /* table name */
'category', /* column_name */
3, /* row_id_value  */
(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
19,  /* md_file_id  */
1  /* md_parent_id  */
)

As long as all metadata collected in the field session either directly (as above) or indirectly (suppose there were a data set level metadata_reference record intermediary) refers to the field session metadata via md_parent_id values, then this chain of metadata references identifies the newly collected information, as Joan requested, in addition to the metadata.

So here is the data after both examples:

Table 23. gpkg_metadata_reference
reference_type	table_name	column_name	row_id_value	timestamp	md_file_id	md_parent_id
table	roads	undefined	0	ts	3	0
table	roads	undefined	0	ts	4	3
table	roads	undefined	0	ts	5	4
column	roads	overhead_clearance	0	ts	7	4
row	bridge	undefined	987	ts	6	4
row/col	bridge	overhead_clearance	987	ts	8	4
table	poi	undefined	0	ts	1	0
row	poi	undefined	0	ts	10	1
row	poi	undefined	1	ts	11	1
row	poi	undefined	2	ts	14	1
row/col	poi	undefined	3	ts	17	1
row/col	poi	point	1	ts	12	1
row/col	poi	point	2	ts	15	1
row/col	poi	point	3	ts	18	1
row/col	poi	category	1	ts	13	1
row/col	poi	category	2	ts	16	1
row/col	poi	category	3	ts	19	1

A number of raster image processing problems MAY require the support of more metadata that is contained in the image itself. Applications MAY use the gpkg_metadata and gpkg_metadata_reference tables defined in clause [_metadata] to store raster image metadata defined according to standard authoritative or application or vendor specific metadata models. An example of the data items in such a model is shown in the following table.

Rational Polynomial Coefficient
Photometric Interpretation
No Data Value
Compression Quality Factor
Georectification
NIIRS
Min X
Min Y
Max X
Max Y

F.9. Schema

Introduction

The schema extension provides a means to describe the columns of tables in a GeoPackage with more detail than can be captured by the SQL table definition directly. The information provided by this extension can be used by applications to, for instance, present data contained in a GeoPackage in a more user-friendly fashion or implement data validation logic.

Extension Author

GeoPackage SWG, author_name gpkg

Extension Name

Extension Type

Applicability

Scope

Requirements

Table Definitions

Data Columns

Table 24. Data Columns Table Definition
Column Name	Column Type	Column Description	Null	Key
`table_name`	TEXT	Name of a table specified in `gpkg_contents.table_name` or `gpkg_extensions.table_name`	no	PK, UK
`column_name`	TEXT	Name of the table column	no	PK
`name`	TEXT	A human-readable identifier (e.g. short name) for the column_name content	yes	UK
`title`	TEXT	A human-readable formal title for the column_name content	yes
`description`	TEXT	A human-readable description for the column_name content	yes
`mime_type`	TEXT	MIME [I21] type of column_name if BLOB type, or NULL for other types	yes
`constraint_name`	TEXT	Column value constraint name (lowercase) specified by reference to `gpkg_data_column_constraints.constraint_name`	yes

GeoPackage applications MAY ^[K38] use the gpkg_data_columns table to store minimal application schema identifying, descriptive and MIME [I21] type ^[K39] information about columns in user vector feature and tile matrix data tables that supplements the data available from the SQLite sqlite_master table and pragma table_info(table_name) SQL function. The gpkg_data_columns data CAN be used to provide more specific column data types and value ranges and application specific structural and semantic information to enable more informative user menu displays and more effective user decisions on the suitability of GeoPackage contents for specific purposes.

In versions 1.2.1 and earlier, the table_name column had a foreign key to gpkg_contents.table_name. This constraint has been relaxed but software that edits GeoPackages should be aware that this constraint will exist in many existing files.

Data Column Constraints

The gpkg_data_column_constraints table contains data to specify restrictions on basic data type column values. The constraint_name column is referenced by the constraint_name column in the gpkg_data_columns table defined in Table 16.

Table 25. Data Column Constraints Table Definition
Column Name	Column Type	Column Description	Null	Key
`constraint_name`	TEXT	Name of constraint (lowercase)	no	Jointly Unique
`constraint_type`	TEXT	Type name of constraint: 'range' \| 'enum' \| 'glob'	no	Jointly Unique
`value`	TEXT	Specified case sensitive value for 'enum' or 'glob' or NULL for 'range' constraint_type	yes	Jointly Unique
`min`	NUMERIC	Minimum value for 'range' or NULL for 'enum' or 'glob' constraint_type	yes
`min_is_inclusive`	BOOLEAN	0 (false) if min value is exclusive, or 1 (true) if min value is inclusive	yes
`max`	NUMERIC	Maximum value for 'range' or NULL for 'enum' or 'glob' constraint_type	yes
`max_is_inclusive`	BOOLEAN	0 (false) if max value is exclusive, or 1 (true) if max value is inclusive	yes
`description`	TEXT	For ranges and globs, describes the constraint; for enums, describes the enum value.	yes

The min and max columns are defined as NUMERIC to be able to contain range values for any numeric data column defined with a data type from Table 1. These are the only exceptions to the data type rule stated in Req 5.

In GeoPackage 1.0, this table had column names minIsInclusive and maxIsInclusive instead of min_is_inclusive and max_is_inclusive. This was corrected in GeoPackage 1.1 but it is possible that some older GeoPackages may have rows in this table and use the incorrect column names.

Table Data Values

gpkg_extensions

Requirement 141

GeoPackages with rows in the gpkg_extensions table with an extension_name of "gpkg_schema" SHALL comply with this extension. GeoPackages complying with this extension SHALL have rows in the gpkg_extensions table as described in Table 18 (below).

Requirement 141 was updated as part of GeoPackage 1.2.1. In 1.1.0 and 1.2.0, the details of required gpkg_extensions rows were inadvertently left unspecified. While the executable test suite running on an older GeoPackage version will not generate a failure due to missing gpkg_extensions rows, it is recommended to update these rows to comply with the updated requirement on older versions as well.

Table 26. Extension Table Records
table_name	column_name	extension_name	definition	scope
`gpkg_data_columns`	null	`gpkg_schema`	see note below	`read-write`
`gpkg_data_column_constraints`	null	`gpkg_schema`	see note below	`read-write`

Data Columns

Requirement 104

Values of the gpkg_data_columns table table_name column value SHALL reference values in the table_name column from either gpkg_contents or gpkg_extensions.

Requirement 105

The column_name column value in a gpkg_data_columns table row SHALL contain the name of a column in the SQLite table or view identified by the table_name column value.

Requirement 106

The constraint_name column value in a gpkg_data_columns table MAY be NULL. If it is not NULL, it SHALL contain a constraint_name column value (which SHALL be lowercase) from the gpkg_data_column_constraints table.

Data Column Constraints

The lowercase gpkg_data_column_constraints constraint_type column value specifies the type of constraint: "range", "enum", or "glob" (GLOB is a text pattern match - see [I33]). The case sensitive value column contains an enumerated legal value for constraint_type "enum", a pattern match string for constraint_type "glob", or NULL for constraint_type "range". The set of value column values in rows of constraint_type "enum" with the same constraint_name contains all possible enumerated values for the constraint name. The min and max column values specify the minimum and maximum valid values for constraint_type "range", or are NULL for constraint_type "enum" or "glob". The min_is_inclusive and max_is_inclusive column values contain 1 if the min and max values (respectively) are inclusive, 0 if they are exclusive, or are NULL for constraint_type "enum" or "glob". These restrictions MAY be enforced by SQL triggers or by code in applications that update GeoPackage data values.

Table 27. Sample Data Column Constraints
constraint_name	constraint_type	value	min	min_is_inclusive	max	max_is_inclusive
sampleRange	range	NULL	1	true	10	true
sampleEnum	enum	1	NULL	NULL	NULL	NULL
sampleEnum	enum	3	NULL	NULL	NULL	NULL
sampleEnum	enum	5	NULL	NULL	NULL	NULL
sampleEnum	enum	7	NULL	NULL	NULL	NULL
sampleEnum	enum	9	NULL	NULL	NULL	NULL
sampleGlob	glob	[1-2][0-9][0-9][0-9]	NULL	NULL	NULL	NULL

Requirement 108

The gpkg_data_column_constraints table MAY be empty. If it contains data, the lowercase constraint_type column values SHALL be one of "range", "enum", or "glob".

Requirement 109

The gpkg_data_column_constraint constraint_name values for rows with constraint_type values of "range" and "glob" SHALL be unique.

Requirement 110

The gpkg_data_column_constraints table MAY be empty. If it contains rows with constraint_type column values of "range", the value column values for those rows SHALL be NULL.

Requirement 111

If the gpkg_data_column_constraints table contains rows with constraint_type column values of "range", the min column values for those rows SHALL be NOT NULL and less than the max column value which shall be NOT NULL.

Requirement 112

If the gpkg_data_column_constraints table contains rows with constraint_type column values of "range", the min_is_inclusive and max_is_inclusive column values for those rows SHALL be 0 or 1.

Requirement 113

If the gpkg_data_column_constraints table contains rows with constraint_type column values of "enum" or "glob", the min, max, min_is_inclusive and max_is_inclusive column values for those rows SHALL be NULL.

Requirement 114

If the gpkg_data_column_constraints table contains rows with constraint_type column values of "enum" or "glob", the value column SHALL NOT be NULL.

Abstract Test Suite

Table Definition

Data Columns

Test Case ID	/extensions/schema/data_columns/table_def
Test Purpose	Verify that the `gpkg_data_columns` table exists and has the correct definition.
Test Method	PRAGMA table_info(gpkg_data_columns) Fail if returns an empty result set Fail if column names and column definitions in the returned table_info do not match those of Table 23, including data type, nullability, default values. Column order, check constraint and trigger definitions, and other column definitions in the returned sql are irrelevant. Pass if no failures.
Reference
Test Type

Data Column Constraints

Test Case ID	/extensions/schema/data_column_constraints/table_def
Test Purpose	Verify that the `gpkg_data_column_constraints table` exists and has the correct definition.
Test Method	PRAGMA table_info(gpkg_data_column_constraints) Fail if returns an empty result set Fail if column names and column definitions in the returned table_info do not match those of Table 23, including data type, nullability, default values. Column order, check constraint and trigger definitions, and other column definitions in the returned sql are irrelevant. Pass if no failures.
Reference
Test Type

Data Values

gpkg_extensions

Test Case ID	/extensions/schema/extensions/data_values
Test Purpose	Verify that the `gpkg_extensions` table has the required rows.
Test Method	SELECT table_name, column_name, scope FROM gpkg_extensions WHERE extension_name = 'gpkg_schema'; Not testable if returns an empty result set Fail if there are not exactly two rows For each row returned from step 1 Fail if scope is not "read-write" Fail if column_name is not NULL Fail if either table_name entry is not present Pass if no fails
Reference
Test Type:

Test Case ID	/extensions/schema/data_columns/table_name
Test Purpose	Verify that for each `gpkg_data_columns` row, the table_name value matches a row in `gpkg_contents` or `gpkg_extensions`.
Test Method	SELECT DISTINCT gdc.table_name AS gdc_table, ge.table_name AS joined_table FROM gpkg_data_columns AS gdc LEFT OUTER JOIN gpkg_contents AS gc ON gdc.table_name = gc.table_name LEFT OUTER JOIN gpkg_extensions AS ge ON gdc.table_name = ge.table_name; Not testable if returns an empty result set For each row from step 1 Fail if joined_table is NULL. Pass if no fails.
Reference
Test Type

Test Case ID	/extensions/schema/data_columns/column_name
Test Purpose	Verify that for each `gpkg_data_columns` row, the `column_name` value matches a column in the table or view identified by the `table_name` column value.
Test Method	SELECT table_name, column_name FROM gpkg_data_columns Not testable if returns an empty result set For each row from step 1 PRAGMA table_info(table_name) Fail if table_name does not contain a column matching column_name Pass if no fails
Reference
Test Type

Test Case ID	/extensions/schema/data_columns/constraint_name
Test Purpose	Verify that for each `gpkg_data_columns` row, if the `constraint_name` value is NOT NULL then the `gpkg_data_column_constraints` table contains at least a row with a matching `constraint_name` value.
Test Method	SELECT constraint_name AS cn FROM gpkg_data_columns Not testable if returns an empty result set For each NOT NULL cn value from step 1 SELECT 1 FROM gpkg_data_column_constraints WHERE constraint_name = cn Fail if returns an empty result set Pass if no fails
Reference
Test Type

Data Column Constraints

Test Case ID	/extensions/schema/data_column_constraints/constraint_type
Test Purpose	Verify that the `gpkg_data_column_constraints` `constraint_type` column values are one of "range", "enum", or "glob".
Test Method	SELECT DISTINCT constraint_type FROM gpkg_data_column_constraints Not testable if returns an empty result set For each constraint_type value returned by step 1 Fail if constraint_type NOT IN ("range", "enum", "glob"). Pass if no fails.
Reference
Test Type

Test Case ID	/extensions/schema/data_column_constraints/constraint_names_unique
Test Purpose	Verify that the `gpkg_data_column_constraints` `constraint_name` column values for `constraint_type` values of "range", or "glob" are unique.
Test Method	SELECT DISTINCT constraint_name FROM gpkg_data_column_constraints WHERE constraint_type IN ('range', 'glob') For each returned constraint_name cn SELECT count(*) FROM gpkg_data column_constraints WHERE constraint_name = cn Fail if count > 1 Pass if no fails.
Reference
Test Type

Test Case ID	/extensions/schema/data_column_constraints/value_for_range
Test Purpose	Verify that the `gpkg_data_column_constraints` `value` column values are NULL for rows with a `constraint_type` value of "range".
Test Method	SELECT constraint_name, value FROM gpkg_data_column_constraints WHERE constraint_type = 'range' Not testable if returns an empty result set For each value returned by step 1 Fail if value IS NOT NULL Pass if no fails.
Reference
Test Type

Test Case ID	/extensions/schema/data_column_constraints/min_max_for_range
Test Purpose	Verify that the `gpkg_data_column_constraints` `min` column values are NOT NULL and less than the `max` column values for rows with a `constraint_type` value of "range".
Test Method	SELECT constraint_name, min, max FROM gpkg_data_column_constraints WHERE constraint_type = 'range' Not testable if returns an empty result set For each set of min and max values returned by step 1 Fail if min IS NULL Fail if max IS NULL Fail if min >= max Pass if no fails.
Reference
Test Type

Test Case ID	/extensions/schema/data_column_constraints/inclusive_for_range
Test Purpose	Verify that the `gpkg_data_column_constraints` `min_is_inclusive` and `max_is_inclusive` column values are NOT NULL and either 0 or 1 for rows with a `constraint_type` value of "range".
Test Method	SELECT constraint_name, min_is_inclusive, max_is_inclusive FROM gpkg_data_column_constraints WHERE constraint_type = 'range' Not testable if returns an empty result set For each set of values returned by step 1 Fail if min_is_inclusive IS NULL Fail if max_is_inclusive IS NULL Fail if min_is_inclusive is NOT IN (0,1) Fail if max_is_inclusive is NOT IN (0,1) Pass if no fails.
Reference
Test Type

Test Case ID:	/extensions/schema/data_column_constraints/min_max_inclusive_for_enum_glob
Test Purpose:	Verify that the `gpkg_data_column_constraints` `min`, `max`, `min_is_inclusive` and `max_is_inclusive` column values are NULL for rows with a `constraint_type` value of "enum" or "glob".
Test Method:	SELECT constraint_name, min, max, min_is_inclusive, max_is_inclusive FROM gpkg_data_column_constraints WHERE constraint_type IN ('enum','glob') Not testable if returns an empty result set For each set of values returned by step 1 Fail if min IS NOT NULL Fail if max IS NOT NULL Fail if min_is_inclusive IS NOT NULL Fail if max_is_inclusive IS NOT NULL Pass if no fails.
Reference
Test Type

Test Case ID:	/extensions/schema/data_column_constraints/value_for_enum_glob
Test Purpose:	Verify that the `gpkg_data_column_constraints` `value` column values are NOT NULL for rows with a `constraint_type` value of "enum" or "glob".
Test Method:	SELECT value FROM gpkg_data_column_constraints WHERE constraint_type IN ('enum','glob') Not testable if returns an empty result set For each value returned by step 1 Pass if no fails.
Reference
Test Type

Table Definition SQL

gpkg_data_columns

gpkg_data_columns Table Definition SQL

CREATE TABLE gpkg_data_columns (
  table_name TEXT NOT NULL,
  column_name TEXT NOT NULL,
  name TEXT,
  title TEXT,
  description TEXT,
  mime_type TEXT,
  constraint_name TEXT,
  CONSTRAINT pk_gdc PRIMARY KEY (table_name, column_name),
  CONSTRAINT gdc_tn UNIQUE (table_name, name)
);

gpkg_data_column_constraints

gpkg_data_columns Table Definition SQL

CREATE TABLE gpkg_data_column_constraints (
  constraint_name TEXT NOT NULL,
  constraint_type TEXT NOT NULL, // 'range' | 'enum' | 'glob'
  value TEXT,
  min NUMERIC,
  min_is_inclusive BOOLEAN, // 0 = false, 1 = true
  max NUMERIC,
  max_is_inclusive BOOLEAN, // 0 = false, 1 = true
  description TEXT,
  CONSTRAINT gdcc_ntv UNIQUE (constraint_name, constraint_type, value)
)