extension_metadata.adoc

Metadata

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 [metadata_example_appendix] and [raster_or_tile_metadata_example_appendix]. 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

gpkg_metadata

Extension Type

New Requirement

Applicability

This extension applies to any content in the GeoPackage.

Scope

Read-write

Requirements

Table Definitions

Metadata Table

Requirement 93

A GeoPackage MAY contain a table named gpkg_metadata. If present it SHALL be defined per clauses Metadata Table, Table 1, and gpkg_metadata Table Definition SQL.

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.

Table 1. Metadata Table Definition

Column Name	Column Type	Column Description	Null	Default	Key
id	INTEGER	Autoincrement[K6a] primary key	no		PK
md_scope	TEXT	Case sensitive name of the data scope to which this metadata applies; see Table 4 below	no	'dataset'
md_standard_uri	TEXT	URI [I23] reference to the metadata structure definition authority [K29]	no	any
mime_type	TEXT	MIME [I21] encoding of metadata	no	'text/xml' [I24]
metadata	TEXT	metadata	no	''

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

See gpkg_metadata Table Definition SQL.

Metadata Reference Table

Requirement 95

A GeoPackage that contains a gpkg_metadata table SHALL contain a gpkg_metadata_reference table per clauses Metadata Reference Table, Table 2, and gpkg_metadata_reference Table Definition SQL.

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.

Table 2. Metadata Reference Table Definition (Table Name: gpkg_metadata_reference)

Column Name	Col Type	Column Description	Null	Default	Key
reference_scope	TEXT	Lowercase metadata reference scope; one of 'geopackage', 'table', 'column', 'row', 'row/col'	no
table_name	TEXT	Name of the table to which this metadata reference applies, or NULL for reference_scope of 'geopackage'	yes
column_name	TEXT	For reference_scope of 'column' or 'row/col', name of the column to which this metadata reference applies (NULL otherwise)	yes
row_id_value [K30]	INTEGER	For reference_scope of 'row' or 'row/col', the rowid of a row record in the table referenced by table_name (NULL otherwise)	yes
timestamp	DATETIME	Timestamp value in ISO 8601 format as defined by the strftime function \'%Y-%m-%dT%H:%M:%fZ' format string applied to the current time	no	strftime(\'%Y-%m-%dT%H:%M:%fZ', \'now')
md_file_id	INTEGER	gpkg_metadata table id column value for the metadata to which this gpkg_metadata_reference applies	no		FK
md_parent_id	INTEGER	gpkg_metadata table id column value for the hierarchical parent gpkg_metadata for the gpkg_metadata to which this gpkg_metadata_reference applies, or NULL if md_file_id forms the root of a metadata hierarchy	yes		FK

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

See [table_definition_sql] clause gpkg_metadata_reference Table Definition SQL.

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 3 (below).

Warning

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.

Table 3. Extension Table Records

table_name	column_name	extension_name	definition	scope
gpkg_metadata	null	gpkg_metadata	see note below	read-write
gpkg_metadata_reference	null	gpkg_metadata	see note below	read-write

Note

For the definition column, use a hyperlink that describes the current implementation of this extension. While a URL like http://www.geopackage.org/spec/#extension_metadata is acceptable, permalinks to specific versions are provided upon publication using the URL pattern http://www.geopackage.org/specMmP/#extension_metadata where M is the major version, m is the minor version, and P is the patch. For example http://www.geopackage.org/spec121/#extension_metadata is the permalink for this extension for GeoPackage 1.2.1.

gpkg_metadata

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 4 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 1.

Table 4. Metadata Scopes

Name (md_scope)	Scope Code	Definition
undefined	NA	Metadata information scope is undefined
fieldSession	012	Information applies to the field session
collectionSession	004	Information applies to the collection session
series	006	Information applies to the (dataset) series [K33]
dataset	005	Information applies to the (geographic feature) dataset
featureType	010	Information applies to a feature type (class)
feature	009	Information applies to a feature (instance)
attributeType	002	Information applies to the attribute class
attribute	001	Information applies to the characteristic of a feature (instance)
tile	016	Information applies to a tile, a spatial subset of geographic data
model	015	Information applies to a copy or imitation of an existing or hypothetical object
catalog	NA	Metadata applies to a feature catalog [K34]
schema	NA	Metadata applies to an application schema [K35]
taxonomy	NA	Metadata applies to a taxonomy or knowledge system [K36]
software	013	Information applies to a computer program or routine
service	014	Information applies to a capability which a service provider entity makes available to a service user entity through a set of interfaces that define a behavior, such as a use case
collectionHardware	003	Information applies to the collection hardware class
nonGeographicDataset	007	Information applies to non-geographic data
dimensionGroup	008	Information applies to a dimension group
style	NA	Information applies to a specific style

Requirement 94

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

Warning

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

gpkg_metadata_reference

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 [r5].^[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

Metadata Table

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	Annex F.8 Req 93
Test Type	Basic

Metadata Reference Table

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	Annex F.8 Req 95
Test Type	Basic

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	Annex F.8 Req 140
Test Type:	Capabilities

gpkg_metadata

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 4.
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 4. . Pass if no fails
Reference	Annex F.8 Req 94
Test Type:	Capabilities

gpkg_metadata_reference

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	Annex F.8 Req 96
Test Type	Capability

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	Annex F.8 Req 97
Test Type	Capability

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	Annex F.8 Req 98
Test Type	Capability

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	Annex F.8 Req 99
Test Type	Capability

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 [r5] Log pass otherwise Pass if logged pass and no fails.
Reference	Annex F.8 Req 100
Test Type	Capability

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	Annex F.8 Req 101
Test Type	Capability

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	Annex F.8 Req 102
Test Type	Capability

Table Definition SQL

gpkg_metadata

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

gpkg_metadata Table Definition SQL

CREATE TABLE gpkg_metadata (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  md_scope TEXT NOT NULL DEFAULT 'dataset',
  md_standard_uri TEXT NOT NULL,
  mime_type TEXT NOT NULL DEFAULT 'text/xml',
  metadata TEXT NOT NULL DEFAULT ''
);

gpkg_metadata_reference

gpkg_metadata_reference Table Definition SQL

CREATE TABLE gpkg_metadata_reference (
  reference_scope TEXT NOT NULL,
  table_name TEXT,
  column_name TEXT,
  row_id_value INTEGER,
  timestamp DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  md_file_id INTEGER NOT NULL,
  md_parent_id INTEGER,
  CONSTRAINT crmr_mfi_fk FOREIGN KEY (md_file_id) REFERENCES gpkg_metadata(id),
  CONSTRAINT crmr_mpi_fk FOREIGN KEY (md_parent_id) REFERENCES gpkg_metadata(id)
);

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)

metadataexample.adoc

rasterortilemetadataexample.adoc