1_base.adoc

Base

The required capabilities specified in this clause serve as the base for options specified in clause [_options] and extensions specified in clause [registered_extensions]. All gpkg_* tables and views and all tiles user data tables specified in this standard SHALL have only the specified columns and table constraints. Any features user data tables MAY have columns in addition to those specified. All table, view, column, trigger, and constraint name values specified in this standard are lowercase.

Note

SQLite is more flexible with regards to identifiers than many other RDBMS systems. If GeoPackage data is to be used in other RDBMS systems, standard practices SHOULD be used to reduce potential interoperability risks. For maximum interoperability, all user-defined GeoPackage table, view, column, trigger, and constraint name values SHOULD start with a lowercase character and only include lowercase characters, numbers 0-9, and underscores (_). In addition, some RDBMS systems truncate identifier names that are longer than a fixed byte length which may be as short as 30 bytes. Therefore, long GeoPackage identifier values SHOULD be avoided and if long identifier values are used, then they SHOULD be unique for at least the first 30 bytes.

Core

The mandatory core capabilities defined in sub clauses and requirement statements of this clause SHALL be implemented by every GeoPackage and GeoPackage SQLite Configuration.

SQLite Container

The SQLite software library provides a self-contained, single-file, cross-platform, serverless, transactional, open source RDBMS container. The GeoPackage standard defines a SQL database schema designed for use with the SQLite software library. Using SQLite as the basis for GeoPackage simplifies production, distribution and use of GeoPackages and assists in guaranteeing the integrity of the data they contain.

"Self-contained" means that container software requires very minimal support from external libraries or from the operating system. "Single-file" means that a container not currently opened by any software application consists of a single file in a file system supported by a computing platform operating system. "Cross-platform" means that a container file MAY be created and loaded with data on one computing platform, and used and updated on another, even if they use different operating systems, file systems, and byte order (endian) conventions. "Serverless" means that the RDBMS container is implemented without any intermediary server process, and accessed directly by application software. "Transactional" means that RDBMS transactions guarantee that all changes to data in the container are Atomic, Consistent, Isolated, and Durable (ACID) despite program crashes, operating system crashes, and power failures.

Data

File Format

Requirement 1

A GeoPackage SHALL be a SQLite [B5] database file using version 3 of the SQLite file format [B6] [B7]. The first 16 bytes of a GeoPackage SHALL be the null-terminated ASCII [B4] string "SQLite format 3" ^{[K1] [K2]}

Requirement 2

A GeoPackage SHALL contain a value of 0x47504B47 ("GPKG" in ASCII) in the "application_id" field of the SQLite database header to indicate that it is a GeoPackage. ^[K3] A GeoPackage SHALL contain an appropriate value in "user_version" field of the SQLite database header to indicate its version. The value SHALL be a five-digit integer with a major version, two-digit minor version, and two-digit bug-fix. ^[K4]

The maximum size of a GeoPackage file is about 140TB ^[K4a]. In practice a lower size limit MAY be imposed by the filesystem to which the file is written. Many mobile devices require external memory cards to be formatted using the FAT32 file system which imposes a maximum size limit of 4GB.

Note	For example, for GeoPackage Version 1.3.0 this value is 0x0000283C (the hexadecimal value for 10300). The version can be retrieved using the SQLite command PRAGMA user_version.

File Extension Name

Requirement 3

A GeoPackage SHALL have the file extension name ".gpkg".

It is RECOMMENDED that Extended GeoPackages use the file extension ".gpkx", but this is NOT a GeoPackage requirement.

Note	GeoPackage now has a registered media type (formerly MIME type) of application/geopackage+sqlite3 [B27]. Previous statements that the media type is application/geopackage+vnd.sqlite3 are in error.

File Contents

Requirement 4

A GeoPackage SHALL only contain the data elements (tables, columns, or values) and SQL constructs (views, constraints, or triggers) specified in the core of this encoding standard.

A GeoPackage is expected to have content in at least one of the core options ([features], [tiles], and [attributes]).

Extended GeoPackages MAY contain additional data elements and SQL constructs as specified through the [extension_mechanism]. In an Extended GeoPackage, the extension mechanism is used to provide additional capabilities in a way that maintains interoperability as much as possible. Developers are encouraged to consider the implications of extensions when designing their applications. Best practices include the following designing extensions in a way that anticipates the presence of unexpected extensions, e.g., gracefully handling unexpected columns, values, or encodings.

Note

The GeoPackage designation was originally designed to indicate a schema with maximum interoperability. However, as the standard has evolved, it has become increasingly important for GeoPackages to contain certain extensions such as the following: the [extension_rtree] extension for GeoPackages containing a non-trivial amount of vector data, and the [extension_crs_wkt] extension, which is strongly recommended due to inherent weaknesses in the original standard for encoding coordinate reference systems. In light of this evolution, the difference between a GeoPackage and an Extended GeoPackage is no longer relevant.

Requirement 5

The columns of tables in a GeoPackage SHALL only be declared using one of the data types specified in table Table 1. Extended GeoPackages MAY contain additional data types as specified through the [extension_mechanism].

Warning

SQLite is designed to be flexible with regards to the range of a value. Therefore GeoPackage writers SHOULD validate the data as it is being inserted and GeoPackage readers SHOULD allow for the possibility that unexpected values are present. While data type mismatches could theoretically be checked for, such tests would scale poorly for large GeoPackages so they are not present in the GeoPackage Executable Test Suite at the time of writing. SQLite’s design prevents this requirement from being enforced for views.

Table 1. GeoPackage Data Types

Data Type	Size and Description
BOOLEAN	A boolean value representing true or false. Stored as SQLite INTEGER with value 0 for false or 1 for true.
TINYINT	8-bit signed two’s complement integer. Stored as SQLite INTEGER with values in the range [-128, 127].
SMALLINT	16-bit signed two’s complement integer. Stored as SQLite INTEGER with values in the range [-32768, 32767].
MEDIUMINT	32-bit signed two’s complement integer. Stored as SQLite INTEGER with values in the range [-2147483648, 2147483647].
INT, INTEGER	64-bit signed two’s complement integer. Stored as SQLite INTEGER with values in the range [-9223372036854775808, 9223372036854775807].
FLOAT	32-bit IEEE floating point number. Stored as SQLite REAL limited to values that can be represented as a 4-byte IEEE floating point number.
DOUBLE, REAL	64-bit IEEE floating point number. Stored as SQLite REAL.
TEXT{(maxchar_count)}	Variable length string encoded in either UTF-8 or UTF-16, determined by PRAGMA encoding; see http://www.sqlite.org/pragma.html#pragma_encoding. The optional maxchar_count defines the maximum number of characters in the string. If not specified, the length is unbounded. The count is provided for informational purposes, and applications MAY choose to truncate longer strings if encountered. When present, it is best practice for applications to adhere to the character count. Stored as SQLite TEXT.
BLOB{(max_size)}	Variable length binary data. The optional max_size defines the maximum number of bytes in the blob. If not specified, the length is unbounded. The size is provided for informational purposes. When present, it is best practice for applications adhere to the maximum blob size. Stored as SQLite BLOB.
<geometry_type_name>	Geometry encoded as per clause [gpb_format]. <geometry type_name> is one of the core geometry types listed in [geometry_types] encoded per clause 2.1.3 or a geometry type encoded per an extension such as [extension_geometry_types]. Geometry Types XY, XYZ, XYM and XYZM geometries use the same data type. Stored as SQLite BLOB.
DATE	ISO 8601 [I29] date string in the form YYYY-MM-DD encoded in either UTF-8 or UTF-16. Stored as SQLite TEXT (see TEXT above).
DATETIME	ISO-8601 date/time string in the form YYYY-MM-DDTHH:MM:SS.SSSZ with T separator character and Z suffix for coordinated universal time (UTC) encoded in either UTF-8 or UTF-16. Stored as SQLite TEXT (see TEXT above).

File Integrity

Requirement 6

The SQLite PRAGMA integrity_check SQL command SHALL return "ok" for a GeoPackage file. ^[K5]

Requirement 7

The SQLite PRAGMA foreign_key_check SQL with no parameter value SHALL return an empty result set indicating no invalid foreign key values for a GeoPackage file.

API

Structured Query Language (SQL)

Requirement 8

A GeoPackage SQLite Configuration SHALL provide SQL access to GeoPackage contents via SQLite version 3 [I6] software APIs. ^[K6]

Every GPKG SQLite Configuration

The SQLite [I8] library has many compile time and run time options that MAY be used to configure SQLite for different uses. Use of SQLITE_OMIT options is not recommended because certain elements of the GeoPackage standard depend on the availability of SQLite functionality at runtime.

Requirement 9

Every GeoPackage SQLite Configuration SHALL have the SQLite library compile time options specified in clause 1.1.1.2.2 table [every_gpkg_sqlite_config_table].

Spatial Reference Systems

Data

Table Definition

Requirement 10

A GeoPackage SHALL include a gpkg_spatial_ref_sys table per clause 1.1.2.1.1 Table Definition, Table Table 2 and Table [gpkg_spatial_ref_sys_sql].

A table named gpkg_spatial_ref_sys is the first component of the standard SQL schema for simple features described in clause [sfsql_intro] below. The spatial reference system (SRS) definitions it contains are referenced by the GeoPackage gpkg_contents and gpkg_geometry_columns tables to relate the vector and tile data in user tables to locations on the earth.

The gpkg_spatial_ref_sys table includes the columns specified in SQL/MM (ISO 13249-3) [I12] and shown in Table 2 below containing data that defines spatial reference systems. Views of this table MAY be used to provide compatibility with the SQL/MM [I12] (see [sqlmm_gpkg_spatial_ref_sys_sql]) and OGC Simple Features SQL [I9][I10][I11] (Table 21) standards.

Table 2. Spatial Ref Sys Table Definition

Column Name	Column Type	Column Description	NOT NULL flag	Key
srs_name	TEXT	Human readable name of this SRS	true
srs_id	INTEGER	Unique identifier for each Spatial Reference System within a GeoPackage	true	PK
organization	TEXT	Case-insensitive name of the defining organization e.g. EPSG or epsg	true
organization_coordsys_id	INTEGER	Numeric ID of the Spatial Reference System assigned by the organization	true
definition	TEXT	Well-known Text [I32] Representation of the Spatial Reference System	true
description	TEXT	Human readable description of this SRS	false

See [gpkg_spatial_ref_sys_sql].

Table Data Values

The srs_id column is a primary key for this table and this primary key is used as a foreign key throughout GeoPackage. The srd_id column values are also found in the srs_id attribute of the GeoPackage SQL Geometry Binary Format (see [r33]). For convenience, srs_id and organization_coordsys_id values are typically the same. However, there is no guarantee that SRS IDs will be globally unique across organizations.

Warning

GeoPackage clients should not make assumptions regarding the organization or definition of any SRS ID other than those defined in Requirement 11.

Definition column WKT values in the gpkg_spatial_ref_sys table define the Spatial Reference Systems used by feature geometries and tile images, unless these SRSs are unknown and therefore undefined as specified in Requirement 11. Values are constructed per the EBNF syntax in [I32] clause 7. EBNF name and number values may be obtained from any specified authority, e.g. [I13][I14]. For example, see the return value in [spatial_ref_sys_data_values_default] Test Method step (3) used to test the definition for WGS-84 per Requirement 11:

Requirement 11

The gpkg_spatial_ref_sys table SHALL contain at a minimum the records listed in Table 3. The record with an srs_id of 4326 SHALL correspond to WGS-84 [I15] as defined by EPSG [B3] in 4326 [I13][I14]. The record with an srs_id of -1 SHALL be used for undefined Cartesian coordinate reference systems. The record with an srs_id of 0 SHALL be used for undefined geographic coordinate reference systems.

Table 3. Spatial Ref Sys Table Records

srs_name	srs_id	organization	organization_coordsys_id	definition	description
any	4326	EPSG or epsg	4326	any	any
any	-1	NONE	-1	undefined	any
any	0	NONE	0	undefined	any

Requirement 12

The gpkg_spatial_ref_sys table in a GeoPackage SHALL contain records to define all spatial reference systems used by GeoPackage contents (features, tiles, etc.).

Contents

The gpkg_contents table is intended to provide a list of all geospatial contents (i.e., entity stores) in a GeoPackage. It provides identifying and descriptive information that an application can display to a user as a menu of geospatial data that is available for access and/or update.

Data

Table Definition

Requirement 13

A GeoPackage file SHALL include a gpkg_contents table per table Table 4 and [gpkg_contents_sql].

Table 4. Contents Table Definition

Column Name	Type	Description	Null	Default	Key
table_name	TEXT	The name of the actual content (e.g., tiles, features, or attributes) table or view	no		PK
data_type	TEXT	Type of data stored in the table or view	no
identifier	TEXT	A human-readable identifier (e.g. short name) for the table_name content	yes		UK
description	TEXT	A human-readable description for the table_name content	yes	''
last_change	DATETIME	timestamp of last change to content, in ISO 8601 format	no	strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
min_x	DOUBLE	Bounding box minimum easting or longitude for all content in table_name. If tiles, this is informational and the tile matrix set should be used for calculating tile coordinates.	yes
min_y	DOUBLE	Bounding box minimum northing or latitude for all content in table_name. If tiles, this is informational and the tile matrix set should be used for calculating tile coordinates.	yes
max_x	DOUBLE	Bounding box maximum easting or longitude for all content in table_name. If tiles, this is informational and the tile matrix set should be used for calculating tile coordinates.	yes
max_y	DOUBLE	Bounding box maximum northing or latitude for all content in table_name. If tiles, this is informational and the tile matrix set should be used for calculating tile coordinates.	yes
srs_id	INTEGER	Spatial Reference System ID: gpkg_spatial_ref_sys.srs_id	yes		FK

See [gpkg_contents_sql].

Table Data Values

Requirement 14

The table_name column value in a gpkg_contents table row SHALL contain the name of a SQLite table or view.

The data_type specifies the entity type of the table content, for example "features" per clause [features], "attributes" per clause [attributes], "tiles" per clause [tiles], or an implementer-defined value for other data tables per clause in an Extended GeoPackage.

The last_change SHOULD contain the timestamp of when the content in the referenced table was last updated, in ISO8601 format. Note that since it is not practical to ensure that this value is maintained properly in all cases, this value should be treated as informative.

Requirement 15

Values of the gpkg_contents table last_change column SHALL be in a DATETIME format as per Requirement 5.^[K7]

The bounding box (min_x, min_y, max_x, max_y) provides an informative bounding box of the content. Applications may use this bounding box as the extents of a default view but there are no requirements that this bounding box be exact or represent the minimum bounding box of the content. The values are in the units specified by that CRS.

Requirement 16

Values of the gpkg_contents table srs_id column SHALL reference values in the gpkg_spatial_ref_sys table srs_id column.

Note	When data_type is "features", the srs_id also matches gpkg_geometry_columns.srs_id (see [r146]). When data_type is "tiles", the srs_id also matches gpkg_tile_matrix_set.srs_id (see [r147]).