2c_schema.adoc

Schema

Schema Introduction

The schema option 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 option 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.

Data Columns

Data

Table Definition

A GeoPackage MAY contain a table or updateable view named gpkg_data_columns. If present it SHALL be defined per clause 2.3.2.1.1 Table Definition, Data Columns Table or View Definition and [gpkg_data_columns_sql].

Table 1. Data Columns Table or View Definition

Column Name	Column Type	Column Description	Null	Key
table_name	TEXT	Name of the tiles or feature table	no	PK
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
title	TEXT	A human-readable formal title for the column_name content	yes
description	TEXT	A human-readable description for the table_name content	yes
mime_type	TEXT	MIME [21] 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 ^[1] use the gpkg_data_columns table to store minimal application schema identifying, descriptive and MIME [21] type ^[2] 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.

See [gpkg_data_columns_sql].

Table Data Values

Values of the gpkg_data_columns table table_name column value SHALL reference values in the gpkg_contents table_name column.

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.

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

Data

Table Definition

A GeoPackage MAY contain a table or updateable view named gpkg_data_column_constraints. If present it SHALL be defined per clause 2.3.3.1.1 Table Definition, Data Column Constraints Table or View Definition and [gpkg_data_column_constraints_sql].

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 Data Columns Table or View Definition.

Table 2. Data Column Constraints Table or View Definition

Column Name	Column Type	Column Description	Null	Key
constraint_name	TEXT	Name of constraint (lowercase)	no	Unique
constraint_type	TEXT	Type name of constraint: 'range' | 'enum' | 'glob'	no	Unique
value	TEXT	Specified case sensitive value for 'enum' or 'glob' or NULL for 'range' constraint_type	yes	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.

See [gpkg_data_column_constraints_sql].

Table Data Values

The lowercase gpkg_data_column_constraints constraint_type column value specifies the type of constraint: "range", "enum", or "glob" (text pattern match). 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 minim 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 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 3. 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

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

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

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.

The gpkg_data_column_constraints table MAY be empty. If it 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.

The gpkg_data_column_constraints table MAY be empty. If it 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.

The gpkg_data_column_constraints table MAY be empty. If it 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.

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

---

1. A GeoPackage is not required to contain a gpkg_data_columns table. The gpkg_data_columns table in a GeoPackage MAY be empty.

2. GeoPackages MAY contain MIME types other than the raster image types specified in clauses 2.2.4, 2.2.5, and 3.2.2 as feature attributes, but they are not required to do so.