Open Geospatial Consortium

External identifier of this OGC® document: http://www.opengis.net/doc/IS/geopackage/1.4
URL for this OGC® document: http://www.geopackage.org/spec
Internal reference number of this OGC® document: OGC 12-128r19
Version: 1.4
Category: OGC® Encoding Standard
Editor: Jeff Yutzler
Editor Emeritus: Paul Daisey
Previous Version: http://www.geopackage.org/spec131/
Publication Date: TBD
Approval Date: TBD
Submission Date: TBD
Original Version: http://www.opengis.net/doc/IS/geopackage/1.0

OGC® GeoPackage Encoding Standard

Copyright © 2023 Open Geospatial Consortium.
To obtain additional rights of use, visit http://www.opengeospatial.org/legal/

This document is not an OGC Standard. This document is distributed for review and comment. This document is subject to change without notice and may not be referred to as an OGC Standard.

Recipients of this document are invited to submit, with their comments, notification of any relevant patent rights of which they are aware and to provide supporting documentation.

Document type: OGC® Standard
Document subtype: Request for Comment
Document stage: Approved
Document language: English

License Agreement

Permission is hereby granted by the Open Geospatial Consortium, ("Licensor"), free of charge and subject to the terms set forth below, to any person obtaining a copy of this Intellectual Property and any associated documentation, to deal in the Intellectual Property without restriction (except as set forth below), including without limitation the rights to implement, use, copy, modify, merge, publish, distribute, and/or sublicense copies of the Intellectual Property, and to permit persons to whom the Intellectual Property is furnished to do so, provided that all copyright notices on the intellectual property are retained intact and that each person to whom the Intellectual Property is furnished agrees to the terms of this Agreement.

If you modify the Intellectual Property, all copies of the modified Intellectual Property must include, in addition to the above copyright notice, a notice that the Intellectual Property includes modifications that have not been approved or adopted by LICENSOR.

THIS LICENSE IS A COPYRIGHT LICENSE ONLY, AND DOES NOT CONVEY ANY RIGHTS UNDER ANY PATENTS THAT MAY BE IN FORCE ANYWHERE IN THE WORLD.

THE INTELLECTUAL PROPERTY IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, AND NONINFRINGEMENT OF THIRD PARTY RIGHTS. THE COPYRIGHT HOLDER OR HOLDERS INCLUDED IN THIS NOTICE DO NOT WARRANT THAT THE FUNCTIONS CONTAINED IN THE INTELLECTUAL PROPERTY WILL MEET YOUR REQUIREMENTS OR THAT THE OPERATION OF THE INTELLECTUAL PROPERTY WILL BE UNINTERRUPTED OR ERROR FREE. ANY USE OF THE INTELLECTUAL PROPERTY SHALL BE MADE ENTIRELY AT THE USER’S OWN RISK. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR ANY CONTRIBUTOR OF INTELLECTUAL PROPERTY RIGHTS TO THE INTELLECTUAL PROPERTY BE LIABLE FOR ANY CLAIM, OR ANY DIRECT, SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES, OR ANY DAMAGES WHATSOEVER RESULTING FROM ANY ALLEGED INFRINGEMENT OR ANY LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR UNDER ANY OTHER LEGAL THEORY, ARISING OUT OF OR IN CONNECTION WITH THE IMPLEMENTATION, USE, COMMERCIALIZATION OR PERFORMANCE OF THIS INTELLECTUAL PROPERTY.

This license is effective until terminated. You may terminate it at any time by destroying the Intellectual Property together with all copies in any form. The license will also terminate if you fail to comply with any term or condition of this Agreement. Except as provided in the following sentence, no such termination of this license shall require the termination of any third party end-user sublicense to the Intellectual Property which is in force as of the date of notice of such termination. In addition, should the Intellectual Property, or the operation of the Intellectual Property, infringe, or in LICENSOR’s sole opinion be likely to infringe, any patent, copyright, trademark or other right of a third party, you agree that LICENSOR, in its sole discretion, may terminate this license without any compensation or liability to you, your licensees or any other party. You agree upon termination of any kind to destroy or cause to be destroyed the Intellectual Property together with all copies in any form, whether held by you or by any third party.

Except as contained in this notice, the name of LICENSOR or of any other holder of a copyright in all or part of the Intellectual Property shall not be used in advertising or otherwise to promote the sale, use or other dealings in this Intellectual Property without prior written authorization of LICENSOR or such copyright holder. LICENSOR is and shall at all times be the sole entity that may authorize you or any third party to use certification marks, trademarks or other special designations to indicate compliance with any LICENSOR standards or specifications.

This Agreement is governed by the laws of the Commonwealth of Massachusetts. The application to this Agreement of the United Nations Convention on Contracts for the International Sale of Goods is hereby expressly excluded. In the event any provision of this Agreement shall be deemed unenforceable, void or invalid, such provision shall be modified so as to make it valid and enforceable, and as so modified the entire Agreement shall remain in full force and effect. No decision, action or inaction by LICENSOR shall be construed to be a waiver of any rights or remedies available to it. None of the Intellectual Property or underlying information or technology may be downloaded or otherwise exported or reexported in violation of U.S. export laws and regulations. In addition, you are responsible for complying with any local laws in your jurisdiction which may impact your right to import, export or use the Intellectual Property, and you represent that you have complied with any regulations or registration procedures required by applicable law to make this license enforceable.

Patent Call

Attention is drawn to the possibility that some of the elements of this document may be the subject of patent rights. The Open Geospatial Consortium shall not be held responsible for identifying any or all such patent rights.

Recipients of this document are requested to submit, with their comments, notification of any relevant patent claims or other intellectual property rights of which they may be aware that might be infringed by any implementation of the standard set forth in this document, and to provide supporting documentation.

Table of Contents

Abstract

This OGC® Encoding Standard defines GeoPackages for exchange and GeoPackage SQLite Extensions for direct use of vector geospatial features and / or tile matrix sets of earth images and raster maps at various scales. Direct use means the ability to access and update data in a "native" storage format without intermediate format translations in an environment (e.g., through an API) that guarantees data model and data set integrity and identical access and update results in response to identical requests from different client applications. GeoPackages are interoperable across all enterprise and personal computing environments, and are particularly useful on mobile devices like cell phones and tablets in communications environments with limited connectivity and bandwidth.

Keywords

ogcdoc, geopackage, sqlite, raster, tiles, vector, feature, data, storage, exchange, mobile, smartphone, tablet

Introduction

A GeoPackage is an open, standards-based, platform-independent, portable, self-describing, compact format for transferring geospatial information. It is a platform-independent SQLite [I5] database file that contains the GeoPackage data and metadata tables shown in Figure 1 below.

The GeoPackage Encoding Standard (this document) describes a set of conventions for storing the following within an SQLite database:

  • vector features;

  • tile matrix sets of imagery and raster maps at various scales;

  • attributes (non-spatial data); and

  • extensions.

These conventions include table definitions, integrity assertions, format limitations, and content constraints. The required and supported content of a GeoPackage is entirely defined in the standard. These capabilities are built on a common base and the extension mechanism provides implementors a way to include additional functionality in their GeoPackages.

Since a GeoPackage is a database container, it supports direct use. This means that the data in a GeoPackage can be accessed and updated in a "native" storage format without intermediate format translations. GeoPackages that comply with the requirements in the standard and do not implement vendor-specific extensions are interoperable across all enterprise and personal computing environments. GeoPackages are particularly useful on mobile devices such as cell phones and tablets in communications environments where there is limited connectivity and bandwidth.Mobile device users who require map/geospatial application services and operate in disconnected or limited network connectivity environments are challenged by limited storage capacity and the lack of open format geospatial data to support these applications.

This standard is intended to facilitate widespread adoption and use of GeoPackages by both COTS and open-source software applications on enterprise production platforms as well as mobile hand-held devices [B1] [B2], given that mobile hand held devices do not yet have the processing power or battery life to effectively tackle difficult geospatial product production and analysis tasks.

An Extended GeoPackage is a GeoPackage that contains any additional data elements (tables or columns) or SQL constructs (views, data types, functions, indexes, constraints or triggers) that are not automatically maintained within the SQLite data file or that result in a change in behavior not specified in this encoding standard.

A GeoPackage MAY be "empty" (contain user data table(s) for vector features, non-spatial attributes, and/or tile matrix pyramids with no row record content) or contain one or many vector feature type records and /or one or many tile matrix pyramid tile images. GeoPackage metadata CAN describe GeoPackage data contents and identify external data synchronization sources and targets. A GeoPackage MAY contain spatial indexes on feature geometries and SQL triggers to maintain indexes and enforce content constraints.

A GeoPackage SQLite Configuration consists of the SQLite 3 software library and a set of compile- and runtime configurations options.

A GeoPackage SQLite Extension is a SQLite loadable extension that MAY provide SQL functions [I12] to support spatial indexes and SQL triggers linked to a SQLite library with specified configuration requirements to provide SQL API [I1] [I2] [I3] [I4] access to a GeoPackage file. This standard does not address the issues listed in the Potential Future Work (Informative) clause in Background and Context (Normative), which MAY be addressed in a subsequent version of this standard or by other specifications.

GeoPackage Tables
Figure 1. GeoPackage Tables Overview

1. 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 (Normative). 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.

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.

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

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

1.1.1.1. Data
1.1.1.1.1. 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.

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

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

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:

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.

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

  2. 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 Geometry Encoding. <geometry type_name> is one of the core geometry types listed in Geometry Types (Normative) encoded per clause 2.1.3 or a geometry type encoded per an extension such as GeoPackage Non-Linear 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. See TEXT. Stored as SQLite TEXT (see TEXT above).

DATETIME

ISO-8601 date/time string in the form YYYY-MM-DDTHH:MM[:SS.SSS]Z with T separator character, Z suffix for coordinated universal time (UTC), and encoded in either UTF-8 or UTF-16. Seconds and fractional seconds are OPTIONAL. Fractional seconds MAY have fewer or more than three digits. Stored as SQLite TEXT (see TEXT above).

The SQLite Date And Time Functions internally store a timestamp in a way that makes the maximum precision 1 millisecond. Therefore, those functions are unsuitable for dealing with timestamps having a higher precision than one millisecond.
1.1.1.1.4. 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.

1.1.1.2. API
1.1.1.2.1. 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]

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

1.1.2. Spatial Reference Systems

1.1.2.1. Data
1.1.2.1.1. 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 Table Definition SQL.

A table named gpkg_spatial_ref_sys is the first component of the standard SQL schema for simple features described in clause Simple Features SQL Introduction 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 SQL/MM View of gpkg_spatial_ref_sys Definition SQL (Informative)) 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 Table Definition SQL.

1.1.2.1.2. 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 Requirement 33). 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.

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

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

1.1.3.1. Data
1.1.3.1.1. Table Definition
Requirement 13

A GeoPackage file SHALL include a gpkg_contents table per table Table 4 and gpkg_contents Table Definition 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 Table Definition SQL.

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

When data_type is "features", the srs_id also matches gpkg_geometry_columns.srs_id (see Requirement 146). When data_type is "tiles", the srs_id also matches gpkg_tile_matrix_set.srs_id (see Requirement 147).

2. Options

The optional capabilities specified in this clause depend on the required capabilities specified in clause Base above. Each subclause of this clause defines an indivisible module of functionality that can be used in GeoPackages. These modules are referred to as options. GeoPackages MAY use one or more options defined in this section. GeoPackages MAY omit the tables for options that are not used. At a minimum, a GeoPackage SHOULD contain at least one user data table as defined by the Features, Tiles, or Attributes options in clauses Features, Tiles, and Attributes respectively.

Requirement 17

A GeoPackage or Extended GeoPackage SHALL contain features per clause Features and/or tiles per clause Tiles and row(s) in the gpkg_contents table with lowercase data_type column values of "features" and/or "tiles" describing the user data tables.

2.1. Features

2.1.1. Simple Features SQL Introduction

Vector feature data represents geolocated entities including conceptual ones such as districts, real world objects such as roads and rivers, and observations thereof. International standards [I9][I10][I11][I12] have standardized practices for the storage, access and use of vector geospatial features and geometries via SQL in relational databases. The first component of the SQL schema for vector features in a GeoPackage is the gpkg_spatial_ref_sys table defined in clause Spatial Reference Systems above. Other components are defined below.

In a GeoPackage, "simple" features are geolocated using a linear geometry subset of the SQL/MM (ISO 13249-3) [I12] geometry model shown in Figure 2 below.

Core geometry model
Figure 2. Core Geometry Model

The instantiable (not abstract) geometry types defined in this Standard are restricted to 0, 1 and 2-dimensional geometric objects that exist in 2, 3 or 4-dimensional coordinate space (R2, R3 or R4). Geometry values in R2 have points with coordinate values for x and y. Geometry values in R3 have points with coordinate values for x, y and z or for x, y and m. Geometry values in R4 have points with coordinate values for x, y, z and m. The interpretation of the coordinates is subject to the coordinate reference systems associated to the point. All coordinates within a geometry object should be in the same coordinate reference systems.

Geometries MAY include z coordinate values. The z coordinate value traditionally represents the third dimension (i.e. 3D). In a Geographic Information System (GIS) this may be height above or below sea level. For example: A map might have a point identifying the position of a mountain peak by its location on the earth, with the x and y coordinate values, and the height of the mountain, with the z coordinate value.

Geometries MAY include m coordinate values. The m coordinate value allows the application environment to associate some measure with the point values. For example: A stream network may be modeled as multilinestring value with the m coordinate values measuring the distance from the mouth of stream.

A brief description of each geometry type is provided below. A more detailed description can be found in ISO 13249-3 [I12].

  • Geometry: the root of the geometry type hierarchy.

  • Point: a single location in space. Each point has an X and Y coordinate. A point MAY optionally also have a Z and/or an M value.

  • Curve: the base type for all 1-dimensional geometry types. A 1-dimensional geometry is a geometry that has a length, but no area. A curve is considered simple if it does not intersect itself (except at the start and end point). A curve is considered closed its start and end point are coincident. A simple, closed curve is called a ring.

  • LineString: A Curve that connects two or more points in space.

  • Surface: the base type for all 2-dimensional geometry types. A 2-dimensional geometry is a geometry that has an area.

  • CurvePolygon: A planar surface defined by an exterior ring and zero or more interior ring. Each ring is defined by a Curve instance.

  • Polygon: A restricted form of CurvePolygon where each ring is defined as a simple, closed LineString.

  • GeometryCollection: A collection of zero or more Geometry instances. [K8]

  • MultiSurface: A restricted form of GeometryCollection where each Geometry in the collection must be of type Surface.

  • MultiPolygon: A restricted form of MultiSurface where each Surface in the collection must be of type Polygon.

  • MultiCurve: A restricted form of GeometryCollection where each Geometry in the collection must be of type Curve.

  • MultiLineString: A restricted form of MultiCurve where each Curve in the collection must be of type LineString.

  • MultiPoint: A restricted form of GeometryCollection where each Geometry in the collection must be of type Point.

2.1.2. Contents

2.1.2.1. Data
2.1.2.1.1. Contents Table – Features Row
Requirement 18

The gpkg_contents table SHALL contain a row with a lowercase data_type column value of "features" for each vector features user data table or view.

2.1.3. Geometry Encoding

2.1.3.1. Data
2.1.3.1.1. BLOB Format
Requirement 19

A GeoPackage SHALL store feature table geometries with or without optional elevation (Z) and/or measure (M) values in SQL BLOBs using the Standard GeoPackageBinary format specified in table GeoPackage SQL Geometry Binary Format and clause BLOB Format.

GeoPackage SQL Geometry Binary Format
GeoPackageBinaryHeader {
  byte[2] magic = 0x4750; (1)
  byte version;           (2)
  byte flags;             (3)
  int32 srs_id;           (4)
  double[] envelope;      (5)
}

StandardGeoPackageBinary {
  GeoPackageBinaryHeader header;
  WKBGeometry geometry;          (6)
}
1 'GP' in ASCII
2 8-bit unsigned integer, 0 = version 1
3 see bit layout of GeoPackageBinary flags byte
4 the SRS ID, with the endianness specified by the byte order flag
5 see envelope contents indicator code below, with the endianness specified by the byte order flag
6 per OGC 06-103r4 [I9] [K9] [K10]

The axis order in WKB stored in a GeoPackage follows the de facto standard for axis order in WKB and is therefore always (x,y{,z}{,m}) where x is easting or longitude, y is northing or latitude, z is optional elevation, and m is optional measure. This ordering explicitly overrides the axis order as specified in the SRS metadata, applying Case 4 from OGC 08-038r7, Revision to Axis Order Policy and Recommendations[K11]. This was done to maintain consistency with previous implementations of WKB that predated the OGC policy.
Table 5. bit layout of GeoPackageBinary flags byte

bit

7

6

5

4

3

2

1

0

use

R

R

X

Y

E

E

E

B

flag bits use:

  • R: reserved for future use; set to 0

  • X: GeoPackageBinary type

  • Y: empty geometry flag

    • 0: non-empty geometry

    • 1: empty geometry

  • E: envelope contents indicator code (3-bit unsigned integer)

    • 0: no envelope (space saving slower indexing option), 0 bytes

    • 1: envelope is [minx, maxx, miny, maxy], 32 bytes

    • 2: envelope is [minx, maxx, miny, maxy, minz, maxz], 48 bytes

    • 3: envelope is [minx, maxx, miny, maxy, minm, maxm], 48 bytes

    • 4: envelope is [minx, maxx, miny, maxy, minz, maxz, minm, maxm], 64 bytes

    • 5-7: invalid

  • B: byte order for SRS_ID and envelope values in header (1-bit Boolean)

    • 0: Big Endian (most significant byte first)

    • 1: Little Endian (least significant byte first)

The endianness specified by the B flag technically only pertains to header values, and not to the endianness of the WKBGeometry values themselves. The WKBGeometry has its own endianness flag as described in OGC 06-103r4. These values SHOULD be the same for consistency reasons.
Requirement 152

When the WKBGeometry in a GeoPackageBinary is empty, the following SHALL be done:

  1. The empty geometry flag SHALL be set.

  2. The envelope contents indicator code SHALL be 0 indicating no envelope.

  3. If the geometry is a Point, it SHALL be encoded with each coordinate value set to an IEEE-754 quiet NaN value. GeoPackages SHALL use big endian 0x7ff8000000000000 or little endian 0x000000000000f87f as the binary encoding of the NaN values. (This is because Well-Known Binary as defined in OGC 06-103r4 [I9] does not provide a standardized encoding for an empty point set, i.e., 'Point Empty' in Well-Known Text.)

In GeoPackage versions 1.2.1 and prior, the requirements for handling an empty geometry were underspecified. Some otherwise compliant GeoPackages before version 1.3.0 may not comply with all three sub-requirements. This may cause false negative results when querying for empty geometries.

2.1.4. SQL Geometry Types

2.1.4.1. Data
2.1.4.1.1. Core Types
Requirement 20

A GeoPackage SHALL store feature table geometries with the basic simple feature geometry types (Geometry, Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, GeometryCollection) in Geometry Types (Normative) Table 21 in the GeoPackageBinary geometry encoding format.

2.1.5. Geometry Columns

2.1.5.1. Data
2.1.5.1.1. Table Definition
Requirement 21

A GeoPackage with a gpkg_contents table row with a "features" data_type SHALL contain a gpkg_geometry_columns table per Table 5 and gpkg_geometry_columns Table Definition SQL.

The second component of the SQL schema for vector features in a GeoPackage is a gpkg_geometry_columns table that identifies the geometry columns and geometry types in tables that contain user data representing features.

Table 6. Geometry Columns Table Definition
Column Name Type Description Null Key

table_name

TEXT

Name of the table containing the geometry column

no

PK, FK

column_name

TEXT

Name of a column in the feature table that is a Geometry Column

no

PK

geometry_type_name

TEXT

Name from Table 21 or Table 22 in Geometry Types (Normative)

no

srs_id

INTEGER

Spatial Reference System ID: gpkg_spatial_ref_sys.srs_id

no

FK

z

TINYINT

0: z values prohibited; 1: z values mandatory; 2: z values optional

no

m

TINYINT

0: m values prohibited; 1: m values mandatory; 2: m values optional

no

The FK on gpkg_geometry_columns.srs_id references the PK on gpkg_spatial_ref_sys.srs_id to ensure that geometry columns are only defined in feature tables for defined spatial reference systems.

Views of this table MAY be used to provide compatibility with the SQL/MM [I12] SQL/MM View of gpkg_geometry_columns Definition SQL (Informative) and OGC Simple Features SQL [I9][I10][I11] SF/SQL VIEW of gpkg_geometry_columns Definition SQL (Informative) specifications.

See gpkg_geometry_columns Table Definition SQL.

2.1.5.1.2. Table Data Values
Requirement 22

The gpkg_geometry_columns table SHALL contain one row record for the geometry column in each vector feature data table (clause Vector Feature User Data Tables) in a GeoPackage.

Requirement 23

Values of the gpkg_geometry_columns table_name column SHALL reference values in the gpkg_contents table_name column for rows with a data_type of 'features'.

Requirement 24

The column_name column value in a gpkg_geometry_columns row SHALL be the name of a column in the table or view specified by the table_name column value for that row.

Requirement 25

The geometry_type_name value in a gpkg_geometry_columns row SHALL be one of the uppercase geometry type names specified in Geometry Types (Normative).

Requirement 26

The srs_id value in a gpkg_geometry_columns table row SHALL be an srs_id column value from the gpkg_spatial_ref_sys table.

Requirement 146

The srs_id value in a gpkg_geometry_columns table row SHALL match the srs_id column value from the corresponding row in the gpkg_contents table.

Requirement 27

The z value in a gpkg_geometry_columns table row SHALL be one of 0, 1, or 2.

Requirement 28

The m value in a gpkg_geometry_columns table row SHALL be one of 0, 1, or 2.

2.1.6. Vector Feature User Data Tables

2.1.6.1. Data
2.1.6.1.1. Table Definition

The third component of the SQL schema for vector features in a GeoPackage described in clause Simple Features SQL Introduction above are tables that contain user data representing features. Feature attributes are columns in a feature table, including geometries. Features are rows in a feature table. [K12]

Requirement 29

A GeoPackage MAY contain tables containing vector features. Every such feature table SHALL be structured consistently with Table 6 and sample_feature_table Table Definition SQL (Informative). A feature table SHALL have a primary key column of type INTEGER and that column SHALL act as a rowid alias.

The integer primary key of a feature table allows features to be linked to row level metadata records in the gpkg_metadata table by rowid [B5] values in the gpkg_metadata_reference table as described in clause Metadata Reference Table below.
Requirement 150

A GeoPackage MAY contain views containing vector features. Every such feature view SHALL have a first column of type INTEGER and that column SHALL contain unique values for each row.

Since the concept of primary keys does not exist for views in SQLite, this requirement provides a way to produce a compliant feature view with a discoverable key-like column[K17].
Requirement 30

A feature table or view SHALL have only one geometry column.

Feature data models [B23] from non-GeoPackage implementations that have multiple geometry columns per feature table MAY be transformed into GeoPackage implementations with a separate feature table for each geometry type whose rows have matching integer primary key values that allow them to be joined in a view with the same column definitions as the non-GeoPackage feature data model with multiple geometry columns.
Requirement 31

The declared SQL type of the geometry column in a vector feature user data table SHALL be specified by the geometry_type_name column for that column_name and table_name in the gpkg_geometry_columns table.

Table 7. Sample Feature Table Definition
Column Name Type Description Null Default Key

id

INTEGER

Autoincrement[K6a] primary key

N/A[K6b]

PK

geometry

GEOMETRY

GeoPackage Geometry

yes

text_attribute

TEXT

Text attribute of feature

yes

real_attribute

REAL

Real attribute of feature

yes

boolean_attribute

BOOLEAN

Boolean attribute of feature

yes

raster_or_photo

BLOB

Photograph of the area

yes

See sample_feature_table Table Definition SQL (Informative).

2.1.6.1.2. Table Data Values

A feature geometry is stored in a geometry column specified by the geometry_column value for the feature table in the gpkg_geometry_columns table defined in clause Geometry Columns above.

The geometry type of a feature geometry column specified in the gpkg_geometry_columns table geometry_type_name column is a name from Geometry Types (Normative).

Requirement 32

Feature table geometry columns SHALL contain geometries of the type or assignable for the type specified for the column by the gpkg_geometry_columns table geometry_type_name uppercase column value [K13].

Allowed geometry types are defined in Geometry Types (Normative) and shown in part in Figure 2. If the geometry type_name value is "GEOMETRY" then the feature table geometry column MAY contain geometries of any allowed geometry type. This type name is to be used when the geometry type is unknown or if multiple geometry types will be present in the same table (e.g., POLYGON and MULTIPOLYGON). If the geometry type_name value is "GEOMETRYCOLLECTION" then the feature table geometry column MAY contain geometries of type GeometryCollection containing zero or more geometries of any allowed geometry type.

The presence or absence of optional elevation (Z) and/or measure (M) values in a geometry does not change its type or assignability. The unit of measure for optional elevation(Z) values is determined by the CRS of the geometry; it is as-defined by a 3D CRS, and undefined for a 2D CRS. The unit of measure for optional measure (M) values is determined by the CRS of the geometry.

The spatial reference system type of a feature geometry column specified by a gpkg_geometry_columns table srs_id column value is a code from the gpkg_spatial_ref_sys table srs_id column.

Requirement 33

Feature table geometry columns SHALL contain geometries with the srs_id specified for the column by the gpkg_geometry_columns table srs_id column value.

2.2. Tiles

2.2.1. Tile Matrix Introduction

The Tiles option specifies a mechanism for storing raster data in tile pyramids. "Tile pyramid" refers to the concept of pyramid structure of tiles of different spatial extent and resolution at different zoom levels, and the tile data itself. "Tile" refers to an individual raster image such as a PNG or JPEG that covers a specific geographic area. "Tile matrix" refers to rows and columns of tiles that all have the same spatial extent and resolution at a particular zoom level[K14] "Tile matrix set" refers to the definition of a tile pyramid’s tiling structure. This mechanism is based on the model for tile matrix sets described in Section 6 of [I16].

The GeoPackage tile store data model MAY be implemented directly as SQL tables in a SQLite database for maximum performance, or as SQL views on top of tables in an existing SQLite tile store for maximum adaptability and loose coupling to enable widespread implementation. A GeoPackage CAN store multiple raster and tile pyramid data sets in different tables or views in the same container. The tables or views that implement the GeoPackage tile store data / metadata model are described and discussed individually in the following subsections.

The tile store data / metadata model and conventions described below support direct use of tiles in a GeoPackage in two ways. First, they specify how existing application MAY create SQL Views of the data / metadata model on top of existing application tables that that follow different interface conventions. Second, they include and expose enough metadata information at both the dataset and record level to allow applications that use GeoPackage data to discover its characteristics without having to parse all of the stored images. Applications that store GeoPackage tile data, which are presumed to have this information available, should store sufficient metadata to enable its intended use.

2.2.2. Contents

2.2.2.1. Data
2.2.2.1.1. Contents Table – Tiles Row
Requirement 34

The gpkg_contents table SHALL contain a row with a data_type column value of "tiles" for each tile pyramid user data table or view.

2.2.3. Zoom Levels

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 size 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 Zoom Times Two Example (Informative). Other "zoom other intervals" conventions use 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 Web Map Tile Service (WMTS) [I16] Annex E for additional examples of both conventions.

2.2.3.1. Data
2.2.3.1.1. Zoom Times Two
Requirement 35

In a GeoPackage that contains a tile pyramid user data table that contains tile data, by default [K15], zoom level pixel sizes for that table SHALL vary by a factor of 2 between adjacent zoom levels in the tile matrix metadata table.

2.2.4. Tile Encoding PNG

2.2.4.1. Data
2.2.4.1.1. MIME Type PNG
Requirement 36

In a GeoPackage that contains a tile pyramid user data table that contains tile data that is not MIME type image/jpeg [I17][I18][I19], by default SHALL store that tile data in MIME type image/png [I20][I21]. [K16]

2.2.5. Tile Encoding JPEG

2.2.5.1. Data
2.2.5.1.1. MIME Type JPEG
Requirement 37

In a GeoPackage that contains a tile pyramid user data table that contains tile data that is not MIME type image/png [I20][I21], by default SHALL store that tile data in MIME type image/jpeg [I17][I18][I19]. [K16]

Requirements 36 and 37 in combination allow a tile pyramid user data table to contain PNG or JPG tiles. They may be mixed and matched within the same table.

2.2.6. Tile Matrix Set

2.2.6.1. Data
2.2.6.1.1. Table Definition

The gpkg_tile_matrix_set table defines the spatial reference system (srs_id) and the maximum bounding box (min_x, min_y, max_x, max_y) for all possible tiles in a tile pyramid user data table.

Requirement 38

A GeoPackage that contains a tile pyramid user data table SHALL contain gpkg_tile_matrix_set table per Table Definition, Table 7 and gpkg_tile_matrix_set Table Creation SQL.

Table 8. Tile Matrix Set Table Definition
Column Name Column Type Column Description Null Default Key

table_name

TEXT

Tile Pyramid User Data Table Name

no

PK, FK

srs_id

INTEGER

Spatial Reference System ID: gpkg_spatial_ref_sys.srs_id

no

FK

min_x

DOUBLE

Bounding box minimum easting or longitude for the tile matrix set

no

min_y

DOUBLE

Bounding box minimum northing or latitude for the tile matrix set

no

max_x

DOUBLE

Bounding box maximum easting or longitude for the tile matrix set

no

max_y

DOUBLE

Bounding box maximum northing or latitude for the tile matrix set

no

See gpkg_tile_matrix_set Table Creation SQL.

2.2.6.1.2. Table Data Values
Requirement 144

The bounding box defined by min_x, max_x, min_y, and max_y SHALL be exact so that the bounding box coordinates for individual tiles in a tile pyramid MAY be calculated from those values. All tiles present in the tile pyramid SHALL fall within this bounding box.

Since GeoPackages use the upper left tile origin convention defined in clause Table Data Values below, the gpkg_tile_matrix_set (min_x, max_y) ordinate is the upper-left corner of tile (0,0) for all zoom levels in a table_name tile pyramid user data table.

A bounding box MAY be larger than the minimum bounding rectangle around the actual tiles in that pyramid. This allows tile matrix pyramids to be sparsely populated or even empty.

Requirement 39

Values of the gpkg_tile_matrix_set table_name column SHALL reference values in the gpkg_contents table_name column for rows with a data type of "tiles"[K18].

Requirement 40

The gpkg_tile_matrix_set table SHALL contain one row record for each tile pyramid user data table.

Requirement 41

Values of the gpkg_tile_matrix_set srs_id column SHALL reference values in the gpkg_spatial_ref_sys srs_id column.

Requirement 147

The srs_id value in a gpkg_tile_matrix_set table row SHALL match the srs_id column value from the corresponding row in the gpkg_contents table.

2.2.7. Tile Matrix

2.2.7.1. Data
2.2.7.1.1. Table Definition
Requirement 42

A GeoPackage that contains a tile pyramid user data table SHALL contain a gpkg_tile_matrix table per clause 2.2.7.1.1 Table Definition, Table Table 8 and Table gpkg_tile_matrix Table Creation SQL.

Table 9. Tile Matrix Metadata Table Definition
Column Name Column Type Column Description Null Key

table_name

TEXT

Tile Pyramid User Data Table Name

no

PK, FK

zoom_level

INTEGER

0 <= zoom_level <= max_level for table_name

no

PK

matrix_width

INTEGER

Number of columns (>= 1) in tile matrix at this zoom level

no

matrix_height

INTEGER

Number of rows (>= 1) in tile matrix at this zoom level

no

tile_width

INTEGER

Tile width in pixels (>= 1)for this zoom level

no

tile_height

INTEGER

Tile height in pixels (>= 1) for this zoom level

no

pixel_x_size

DOUBLE

In t_table_name srid units or default meters for srid 0 (>0)

no

pixel_y_size

DOUBLE

In t_table_name srid units or default meters for srid 0 (>0)

no

The gpkg_tile_matrix table documents the structure of the tile matrix at each zoom level in each tiles table. It allows GeoPackages to contain rectangular as well as square tiles (e.g., for better representation of polar regions). It allows tile pyramids with zoom levels that differ in resolution by factors of 2, irregular intervals, or regular intervals other than factors of 2.

See gpkg_tile_matrix Table Creation SQL

2.2.7.1.2. Table Data Values
Requirement 43

Values of the gpkg_tile_matrix table_name column SHALL reference values in the gpkg_contents table_name column for rows with a data_type of "tiles".[K19]

Requirement 44

The gpkg_tile_matrix table SHALL contain one row record for each zoom level that contains one or more tiles in each tile pyramid user data table or view.

Requirement 45

The width of a tile matrix (the difference between min_x and max_x in gpkg_tile_matrix_set) SHALL equal the product of matrix_width, tile_width, and pixel_x_size for that zoom level. Similarly, height of a tile matrix (the difference between min_y and max_y in gpkg_tile_matrix_set) SHALL equal the product of matrix_height, tile_height, and pixel_y_size for that zoom level.

Tile matrices are numbered from top left to bottom right (zero-indexed) so the top left tile is (0,0). (This follows the convention used by by WMTS [I16].) Tile matrices may be sparsely populated – no specific tile or even tile matrix must be present. If the global tile matrix set covers the whole earth, then zoom level 0, tile (0,0) is the whole world.

Requirement 46

The zoom_level column value in a gpkg_tile_matrix table row SHALL not be negative.

Requirement 47

The matrix_width column value in a gpkg_tile_matrix table row SHALL be greater than 0.

Requirement 48

The matrix_height column value in a gpkg_tile_matrix table row SHALL be greater than 0.

Requirement 49

The tile_width column value in a gpkg_tile_matrix table row SHALL be greater than 0.

Requirement 50

The tile_height column value in a gpkg_tile_matrix table row SHALL be greater than 0.

Requirement 51

The pixel_x_size column value in a gpkg_tile_matrix table row SHALL be greater than 0.

Requirement 52

The pixel_y_size column value in a gpkg_tile_matrix table row SHALL be greater than 0.

Requirement 53

When zoom_level column values in the gpkg_tile_matrix table are sorted in ascending order, the pixel_x_size and pixel_y_size column values in the gpkg_tile_matrix table SHALL appear sorted in descending order.

Tiles MAY or MAY NOT be provided for level 0 or any other particular zoom level. [K21] This means that a tile matrix set can be sparse, i.e. not contain a tile for any particular position at a certain tile zoom level. [K22] This does not affect the informative spatial extent stated by the min/max x/y columns values in the gpkg_contents record for the same table_name, the exact spatial extent stated by the min/max x/y columns values in the gpkg_tile_matrix_set record for the same table name, or the tile matrix width and height at that level. [K23]

2.2.8. Tile Pyramid User Data Tables

2.2.8.1. Data
2.2.8.1.1. Table Definition
Requirement 54

A GeoPackage MAY contain tables containing tile pyramid user data for a single tile matrix set. Every such tile pyramid user data table SHALL be structured consistently with Table Definition and EXAMPLE: tiles table Create Table SQL (Informative). A table SHALL have a primary key column of type INTEGER and that column SHALL act as a rowid alias.

The integer primary key of a tile pyramid user data table allows tiles to be linked to row level metadata records in the gpkg_metadata table by rowid [B5] values in the gpkg_metadata_reference table as described in clause Metadata Reference Table below.
Requirement 153

A GeoPackage MAY contain views containing tile pyramid user data for a single tile matrix set. Every such tile pyramid user data view SHALL have a first column of type INTEGER and that column SHALL contain unique values for each row.

Since the concept of primary keys does not exist for views in SQLite, this requirement provides a way to produce a compliant tile pyramid user data view with a discoverable key-like column[K17].
Table 10. Sample Tiles Table Definition
Column Name Column Type Column Description Null Key

id

INTEGER

Autoincrement[K6a] primary key

N/A[K6b]

PK

zoom_level

INTEGER

min(zoom_level) <= zoom_level <= max(zoom_level) for t_table_name

no

UK

tile_column

INTEGER

0 to tile_matrix matrix_width – 1

no

UK

tile_row

INTEGER

0 to tile_matrix matrix_height - 1

no

UK

tile_data

BLOB

Of an image MIME type specified in clauses Tile Encoding PNG, Tile Encoding JPEG, Tiles Encoding WebP

no

Though this standard does not technically mandate the use of an indexing mechanism in a tiles table, the lack of such an index, e.g., a UNIQUE clause as in EXAMPLE: tiles table Create Table SQL (Informative), is likely to significantly degrade the performance of queries.
2.2.8.1.2. Table Data Values

Each tile pyramid user data table or view [K24] MAY contain tile matrices at zero or more zoom levels of different spatial resolution (map scale).

Requirement 55

For each distinct table_name from the gpkg_tile_matrix (tm) table, the tile pyramid (tp) user data table zoom_level column value in a GeoPackage SHALL be in the range min(tm.zoom_level) <= tp.zoom_level <= max(tm.zoom_level).

Requirement 56

For each distinct table_name from the gpkg_tile_matrix (tm) table, the tile pyramid (tp) user data table tile_column column value in a GeoPackage SHALL be in the range 0 <= tp.tile_column <= tm.matrix_width – 1 where the tm and tp zoom_level column values are equal.

Requirement 57

For each distinct table_name from the gpkg_tile_matrix (tm) table, the tile pyramid (tp) user data table tile_row column value in a GeoPackage SHALL be in the range 0 <= tp.tile_row <= tm.matrix_height – 1 where the tm and tp zoom_level column values are equal.

All tiles at a particular zoom level have the same pixel_x_size and pixel_y_size values specified in the gpkg_tile_matrix row record for that tiles table and zoom level. [K25]

2.3. Extension Mechanism

2.3.1. Introduction

A GeoPackage extension is a set of one or more requirements clauses that either profiles / extends existing requirements clauses in the GeoPackage standard or adds new requirements clauses. Existing requirement clause extension examples include additional geometry types, additional SQL geometry functions, and additional tile image formats. New requirement clause extension examples include spatial indexes, triggers, additional tables, other BLOB column encodings, and other SQL functions. Files that use one or more extensions are by definition Extended GeoPackages. Extensions that have been already approved by OGC are presented in Registered Extensions (Normative). However, additional extensions MAY be approved by OGC outside of the release cycle of this document.

We acknowledge that there are use cases not covered by this standard. Implementers are welcome to use the extension mechanism defined here to develop their own extensions. The extension mechanism provides advantages including discoverability (the extensions in use can be discovered by scanning a single table) and uniformity (declaring that an extension is in use indicates that a defined set of requirements are being met). However, this is a decision that should be made carefully as custom extensions do introduce interoperability risks.

OGC is unable to endorse extensions developed externally. Therefore an Extended GeoPackage containing extensions not developed by OGC will fail Requirement 4. However, a community of interest MAY waive that requirement in its own GeoPackage profile, with the caveat that it must bear the responsibility of endorsing the new extension(s).

Implementers that are interested in developing their own extensions are encouraged to contact OGC to ensure that the extensions are developed in accordance with OGC policies and in a way that minimizes risks to interoperability. OGC will consider adopting externally developed extensions that address a clear use case, have a sound technical approach, and have a commitment to implementation by multiple implementers.

GeoPackage extensions are documented using the GeoPackage Extension Template in GeoPackage Extension Template (Informative). Extensions are identified by a name of the form <author>_<extension name> where <author> indicates the person or organization that developed and maintains the extension. The author value "gpkg" is reserved for extensions that are developed, maintained, and approved by OGC. Implementers must use their own author names to register other extensions used in Extended GeoPackages.

2.3.2. Extensions

2.3.2.1. Data
2.3.2.1.1. Table Definition
Requirement 58

A GeoPackage MAY contain a table named gpkg_extensions. If present this table SHALL be defined per clause 2.3.2.1.1 Table Definition, Table 10, and gpkg_extensions Table Definition SQL. An extension SHALL NOT modify the definition or semantics of existing columns. An extension MAY define additional tables or columns. An extension MAY allow new values or encodings for existing columns.

The gpkg_extensions table in a GeoPackage is used to indicate that a particular extension applies to a GeoPackage, a table in a GeoPackage, or a column of a table in a GeoPackage. An application that accesses a GeoPackage can query the gpkg_extensions table instead of the contents of all the user data tables to determine if it has the required capabilities to read or write to tables with extensions, and to "fail fast" and return an error message if it does not.

Table 11. GeoPackage Extensions Table Definition (Table Name: gpkg_extensions)
Column Name Col Type Column Description Null Key

table_name

TEXT

Name of the table that requires the extension. When NULL, the extension is required for the entire GeoPackage. SHALL NOT be NULL when the column_name is not NULL.

yes

Jointly Unique

column_name

TEXT

Name of the column that requires the extension. When NULL, the extension is required for the entire table.

yes

Jointly Unique

extension_name

TEXT

The case sensitive name of the extension that is required, in the form <author>_<extension_name>.

no

Jointly Unique

definition

TEXT

Permalink, URI, or reference to a document that defines the extension

no

scope

TEXT

Indicates scope of extension effects on readers / writers: 'read-write' or 'write-only' in lowercase.

no

See gpkg_extensions Table Definition SQL.

2.3.2.1.2. Table Data Values
Requirement 59

In an Extended GeoPackage, every extension SHALL be registered in a corresponding row in the gpkg_extensions table. An extension SHALL NOT modify the definition or semantics of existing columns. An extension MAY define additional tables or columns. An extension MAY allow new values or encodings for existing columns. Either the absence of a gpkg_extensions table or the absence of rows in the gpkg_extensions table SHALL indicate that the file is a GeoPackage (as opposed to an Extended GeoPackage).

Requirement 60

Values of the gpkg_extensions table_name column MAY reference values in the gpkg_contents table_name, reference new tables required by that extension, or be NULL (to indicate an extension that requires no new tables).

Implementers should be aware of the fact that SQLite table names are not case sensitive and that table names in sqlite_master and gpkg_extensions may not have the same case. When searching for table name references, it is recommended to transform table names to lower case with the lower() function. See the Abstract Test Suite for an example.
Requirement 61

The column_name column value in a gpkg_extensions row SHALL be the name of a column in the table specified by the table_name column value for that row, or be NULL.

Requirement 62

Each extension_name column value in a gpkg_extensions row SHALL be a unique case sensitive value of the form <author>_<extension_name> where <author> indicates the person or organization that developed and maintains the extension. The valid character set for <author> SHALL be [a-zA-Z0-9]. The valid character set for <extension_name> SHALL be [a-zA-Z0-9_]. An extension_name for the "gpkg" author name SHALL be one of those defined in this encoding standard or in an OGC document (e.g., Best Practices Document or Encoding Standard) that extends it.

The author value "gpkg" is reserved for GeoPackage extensions that are developed and maintained by OGC. GeoPackage implementers use their own author names to register other extensions.

Requirement 63

The definition column value in a gpkg_extensions row SHALL contain a permalink, URI [I23], or reference to a document defining the extension as per the GeoPackage Extension Template (Informative).

Examples of how to fill out the GeoPackage Extension Template in GeoPackage Extension Template (Informative) are provided in Annex F. This column is not unique because an extension may define multiple tables.

Requirement 64

The scope column value in a gpkg_extensions row SHALL be lowercase "read-write" for an extension that affects both readers and writers, or "write-only" for an extension that affects only writers.

Some extensions do not impose any additional requirements on software that accesses a GeoPackage in a read-only fashion. An example of this is an extension that defines an SQL trigger that uses a non-standard SQL function defined in a GeoPackage SQLite Extension. Triggers are only invoked when data is written to the GeoPackage, so usage of this type of extension can be safely ignored for read-only access. This is indicated by a gpkg_extensions.scope column value of "write-only".

2.4. Attributes

2.4.1. Introduction

Non-spatial attribute data are entities (e.g., tuples or rows) of observations that may not have an explicit geometry property. In GeoPackage, this data is stored in user-defined attribute tables. These tables may contain properties such as an ID or geo-locatable address that allow them to be relationally linkable to rows in other attribute, feature or tile tables.

Examples of attribute data include:

  • meteorological readings from a weather station

  • flow readings from a stream gauge

  • traffic volumes from embedded highway sensors

  • lists of customers

  • delivery stops

  • work orders

2.4.2. Contents

2.4.2.1. Data
2.4.2.1.1. Contents Table - Attributes Row
Requirement 118

The gpkg_contents table SHALL contain a row with a data_type column value of "attributes" for each attributes data table or view.

2.4.3. Attributes User Data Tables

2.4.3.1. Data
2.4.3.1.1. Table Definition

Non-spatial attribute data is stored in user-defined Attribute tables. Attribute sets are rows in an Attribute table. The attributes are columns in a Attribute table. (A GeoPackage is not required to contain any Attribute data tables. Attribute data tables in a GeoPackage may be empty.)

Requirement 119

A GeoPackage MAY contain tables containing attribute sets. Every such attribute table SHALL be structured consistently with Table 11 and EXAMPLE: Attributes table Create Table SQL (Informative). A table SHALL have a primary key column of type INTEGER and that column SHALL act as a rowid alias.

The integer primary key of an attributes table allows features to be linked to row level metadata records in the gpkg_metadata table by rowid [B5] values in the gpkg_metadata_reference table as described in clause Metadata Reference Table below.
Requirement 151

A GeoPackage MAY contain views containing attributes. Every such attributes view SHALL have a first column of type INTEGER and that column SHALL contain unique values for each row.

Since the concept of primary keys does not exist for views in SQLite, this requirement provides a way to produce a compliant attributes view with a discoverable key-like column[K17].
Table 12. Sample Attributes Table Definition
Column Name Col Type Column Description Null Key

id

INTEGER

Autoincrement[K6a] primary key

N/A[K6b]

PK

text_attribute

TEXT

Text attribute of feature

yes

real_attribute

REAL

Real attribute of feature

yes

boolean_attribute

BOOLEAN

Boolean attribute of feature

yes

raster_or_photo

BLOB

Photo of the area

yes

3. Security Considerations

Security considerations for implementations utilizing GeoPackages are in the domain of the implementing application, deployment platform, operating system and networking environment. The GeoPackage standard does not place any constraints on application, platform, operating system level or network security.

Since GeoPackage is dependent on SQLite, implementors should monitor for security alerts related to SQLite and respond accordingly.

Annex A: Conformance / Abstract Test Suite (Normative)

A.1. Base

A.1.1. Core

A.1.1.1. SQLite Container
A.1.1.1.1. Data

File Format

Test Case ID

/base/core/container/data/file_format

Test Purpose

Verify that the GeoPackage is an SQLite version_3 database

Test Method

Pass if the first 16 bytes of the file contain "SQLite format 3" in ASCII.

Reference

Clause 1.1.1.1.1 Req 1:

Test Type

Basic

Test Case ID

/base/core/container/data/file_format/application_id

Test Purpose

Verify that the SQLite database header application id field indicates GeoPackage version 1.x

Test Method

  1. Retrieve the bytes at the application id of the SQLite database header

  2. If the string is "GP10" in ASCII, fall back to the tests for GeoPackage 1.0.

  3. If the string is "GP11" in ASCII, fall back to the tests for GeoPackage 1.1.

  4. If the string is "GPKG" in ASCII

    1. PRAGMA user_version

    2. Fail if the integer representation of the user_version string is less than 10200.

    3. If the integer representation of the user_version string is less than 10300, fall back to the tests for GeoPackage 1.2.

  5. Fail if the string is not one of those values

Reference

Clause 1.1.1.1.1 Req 2:

Test Type

Basic

File Extension Name

Test Case ID

/base/core/container/data/file_extension_name

Test Purpose

Verify that the GeoPackage extension is ".gpkg"

Test Method

Pass if the GeoPackage file extension is ".gpkg"

Reference

Clause 1.1.1.1.2 Req 3:

Test Type

Basic

File Contents

Test Case ID

/base/core/container/data/table_data_types

Test Purpose

Verify that the data types of GeoPackage columns include only the types specified by Table 1.

Test Method

  1. SELECT table_name FROM gpkg_contents WHERE data_type IN ('tiles','features','attributes')

  2. Not testable if returns empty set

  3. For each row table name from step 1

    1. PRAGMA table_info(table_name)

    2. For each row type column value

      1. Fail if value is not one of the data type names specified by Table 1

  4. Pass if no fails

Reference

Table 1 Req 5:

Test Type

Basic

Integrity Check

Test Case ID

/base/core/container/data/file_integrity

Test Purpose

Verify that the GeoPackage passes the SQLite integrity check.

Test Method

Pass if "PRAGMA integrity_check" returns "ok"

Reference

Clause File Integrity Req 6:

Test Type

Capability

Test Case ID

/base/core/container/data/foreign_key_integrity

Test Purpose

Verify that the GeoPackage passes the SQLite foreign_key_check.

Test Method

Pass if "PRAGMA foreign_key_check" (with no parameter value) returns an empty result set

Reference

Clause File Integrity Req 7:

Test Type

Capability
A.1.1.1.2. API

Structured Query Language

Test Case ID

/base/core/container/api/sql

Test Purpose

Test that the GeoPackage SQLite Extension provides the SQLite SQL API interface.

Test Method

  1. sqlite3_exec('SELECT * FROM sqlite_master;)

  2. Fail if returns an SQL error.

  3. Pass otherwise

Reference

Clause 1.1.1.2.1 Req 8:

Test Type

Capability
A.1.1.2. Spatial Reference Systems
A.1.1.2.1. Data

Table Definition

Test Case ID

/base/core/gpkg_spatial_ref_sys/data/table_def

Test Purpose

Verify that the gpkg_spatial_ref_sys table exists and has the correct definition.

Test Method

  1. SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'gpkg_spatial_ref_sys'

  2. Fail if returns an empty result set

  3. Pass if column names and column definitions in the returned CREATE TABLE statement in the sql column value, including data type, nullability, and primary key constraints match all of those in the contents of C.1 Table 15. Column order, check constraint and trigger definitions, and other column definitions in the returned sql are irrelevant.

  4. Fail otherwise.

Reference

Clause 1.1.2.1.1 Req 10:

Test Type

Basic

Table Data Values

Test Case ID

/base/core/gpkg_spatial_ref_sys/data_values_default

Test Purpose

Verify that the spatial_ref_sys table contains the required default contents.

Test Method

  1. SELECT srs_id, organization, organization_coordsys_id, description FROM gpkg_spatial_ref_sys WHERE srs_id = -1 returns -1 "NONE" -1 "undefined", AND

  2. SELECT srs_id, organization, organization_coordsys_id, description FROM gpkg_spatial_ref_sys WHERE srs_id = 0 returns 0 "NONE" 0 "undefined", AND

  3. SELECT definition FROM gpkg_spatial_ref_sys WHERE organization IN ("epsg","EPSG") AND organization_coordsys_id 4326

    1. Confirm that this is a valid CRS

  4. Pass if tests 1-3 are met

  5. Fail otherwise

Reference

Clause 1.1.2.1.2 Requirement 11:

Test Type

Capability

Test Case ID

/base/core/spatial_ref_sys/data_values_required

Test Purpose

Verify that the spatial_ref_sys table contains rows to define all srs_id values used by features and tiles in a GeoPackage.

Test Method

  1. SELECT DISTINCT gc.srs_id, srs.srs_id FROM gpkg_contents AS gc LEFT OUTER JOIN gpkg_spatial_ref_sys AS srs ON srs.srs_id = gc.srs_id WHERE gc.data_type IN ('tiles', 'features')

  2. Pass if no returned srs.srs_id values are NULL.

  3. Fail otherwise

Reference

Clause Clause 1.1.2.1.2 Req 12:

Test Type

Capability
A.1.1.3. Contents
A.1.1.3.1. Data

Table Definition

Test Case ID

/base/core/contents/data/table_def

Test Purpose

Verify that the gpkg_contents table exists and has the correct definition.

Test Method

  1. SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'gpkg_contents'

  2. Fail if returns an empty result set.

  3. Pass if the column names and column definitions in the returned CREATE TABLE statement, including data type, nullability, default values and primary, foreign and unique key constraints match all of those in the contents of C.2 Table gpkg_contents Table Definition SQL. Column order, check constraint and trigger definitions, and other column definitions in the returned sql are irrelevant.

  4. Fail Otherwise

Reference

Clause 1.1.3.1.1 Req 13:

Test Type

Basic

Table Data Values

Test Case ID

/base/core/contents/data/data_values_table_name

Test Purpose

Verify that the table_name column values in the gpkg_contents table are valid.

Test Method

  1. SELECT DISTINCT table_name FROM gpkg_contents WHERE table_name NOT IN (SELECT name FROM sqlite_master)

  2. Fail if there are any results

  3. Pass otherwise.

Reference

Clause 1.1.3.1.2 Req 14:

Test Type

Capability

Test Case ID

/base/core/contents/data/data_values_last_change

Test Purpose

Verify that the gpkg_contents table last_change column values are in ISO 8601 [29] format as per Requirement 5.

Test Method

  1. SELECT last_change from gpkg_contents.

  2. Not testable if returns an empty result set.

  3. For each row from step 1

    1. Fail if format of returned value does not match yyyy-mm-ddThh:mm:ss.hhhZ

  4. Pass if no fails.

Reference

Clause 1.1.3.1.2 Req 15:

Test Type

Capability

Test Case ID

/base/core/contents/data/data_values_srs_id

Test Purpose

Verify that the gpkg_contents table srs_id column values reference gpkg_spatial_ref_sys srs_id column values.

Test Method

  1. PRAGMA foreign_key_check('gpkg_contents')

  2. Fail if does not return an empty result set

Reference

Clause 1.1.3.1.2 Req 16:

Test Type

Capability

A.2. Options

Test Case ID

/opt/valid_geopackage

Test Purpose

Verify that a GeoPackage contains a features or tiles table and gpkg_contents table row describing it.

Test Method

  1. SELECT COUNT(*) FROM gpkg_contents WHERE data_type IN ('tiles', 'features')

  2. Pass if result > 0

  3. Fail otherwise

Reference

Clause 2 Req 17:

Test Type

Capability

A.2.1. Features

Note: Some of these tests require a spatial engine or custom code beyond simple SQL. These tests are marked with a *.

A.2.1.1. Simple Features SQL Introduction
A.2.1.2. Contents
A.2.1.2.1. Data

Contents Table Feature Row

Test Case ID

/opt/features/contents/data/features_row

Test Purpose

Verify that the gpkg_contents table_name value table exists, and is apparently a feature table for every row with a data_type column value of "features"

Test Method

  1. Execute test /opt/features/vector_features/data/feature_table_integer_primary_key

Reference

Clause 2.1.2.1.1 Req 18:

Test Type

Capability
A.2.1.3. Geometry Encoding
A.2.1.3.1. Data

BLOB Format

Test Case ID

/opt/features/geometry_encoding/data/blob

Test Purpose

Verify that geometries stored in feature table geometry columns are encoded in the StandardGeoPackageBinary format.

Test Method

  1. 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')

  2. Not testable if returns an empty result set

  3. For each row from step 1

    1. SELECT cn FROM tn

    2. Not testable if none found

    3. For each cn value from step a

      1. Fail if the first two bytes of each gc are not "GP"

      2. Fail if gc.version_number is not 0

      3. Fail if gc.flags.GeopackageBinary type != 0

      4. Fail if cn.flags.E is not in the set {0, 1, 2, 3, 4}

  4. Pass if no fails

Reference

Clause 2.1.3.1.1 Req 19:

Test Type

Capability

Empty Geometries

Test Case ID

/opt/features/geometry_encoding/data/empty_geometry

Test Purpose

Verify that empty geometries are encoded consistently in the StandardGeoPackageBinary format.

Test Method

  1. 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')

  2. Not testable if returns an empty result set

  3. For each row from step 1

    1. SELECT cn FROM tn

    2. Not testable if none found

    3. For each cn value from step a

      1. Fail if gc.flags.Y == 1 and gc.flags.E != 0

      2. Fail if the geometry is empty but the envelope is not empty (gc.flags.E != 0 and envelope values are not NaN)

  4. Pass if no fails

Reference

Clause 2.1.3.1.1 Req 152:

Test Type

Basic
A.2.1.4. SQL Geometry Types
A.2.1.4.1. Data

Core Types

Test Case ID

/opt/features/geometry_encoding/data/core_types_existing_sparse_data

Test Purpose

Verify that existing basic simple feature geometries are stored in valid GeoPackageBinary format encodings.

Test Method

  1. SELECT table_name FROM gpkg_geometry_columns

  2. Not testable if returns an empty result set

  3. 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'),

  4. Fail if returns an empty result set

  5. For each row from step 3

    1. SELECT cn FROM tn;

    2. 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 42, then

      1. Log cn.header values, wkb endianness and geometry type

      2. *If cn.wkb is not correctly encoded per ISO 13249-3 clause 5.1.46 then log fail

      3. Otherwise log pass

  6. Pass if log contanins pass and no fails

Reference

Clause 2.1.4.1.1 Req 20:

Test Type

Capability
A.2.1.5. Geometry Columns
A.2.1.5.1. Data

Table Definition

Test Case ID

/opt/features/geometry_columns/data/table_def

Test Purpose

Verify that the gpkg_geometry_columns table exists and has the correct definition.

Test Method

  1. PRAGMA table_info('gpkg_geometry_columns')

  2. Fail if returns an empty result set.

  3. Fail if the columns described in Table 5 are missing or have non-matching definitions. Column order and other column definitions in the returned sql are irrelevant. Primary key constraints are as per gpkg_geometry_columns Table Definition SQL.

  4. Pass otherwise.

Reference

Clause 2.1.5.1.1 Req 21:

Test Type

Basic

Table Data Values

Test Case ID

/opt/features/geometry_columns/data/data_values_geometry_columns

Test Purpose

Verify that gpkg_geometry_columns contains one row record for each geometry column in each vector feature user data table.

Test Method

  1. SELECT table_name FROM gpkg_contents WHERE data_type = \'features'

  2. Not testable if returns an empty result set

  3. SELECT table_name FROM gpkg_contents WHERE data_type = \'features' AND table_name NOT IN (SELECT table_name FROM gpkg_geometry_columns)

  4. Fail if result set is not empty

Reference

Clause 2.1.5.1.2 Req 22:

Test Type

Capability

Test Case ID

/opt/features/geometry_columns/data/data_values_table_name

Test Purpose

Verify that the table_name column values in the gpkg_geometry_columns table are valid.

Test Method

  1. PRAGMA foreign_key_list('gpkg_geometry_columns');

  2. Fail if there is no row designating table_name as a foreign key to table_name in gpkg_contents

Reference

Clause 2.1.5.1.2 Req 23:

Test Type

Capability

Test Case ID

/opt/features/geometry_columns/data/data_values_column_name

Test Purpose

Verify that the column_name column values in the gpkg_geometry_columns table are valid.

Test Method

  1. SELECT table_name, column_name FROM gpkg_geometry_columns

  2. Not testable if returns an empty result set

  3. For each row from step 1

    1. PRAGMA table_info(table_name)

    2. Fail if gpkg_geometry_columns.column_name value does not equal a name column value returned by PRAGMA table_info.

  4. Pass if no fails.

Reference

Clause 2.1.5.1.2 Req 24:

Test Type

Capability

Test Case ID

/opt/features/geometry_columns/data/data_values_geometry_type_name

Test Purpose

Verify that the geometry_type_name column values in the gpkg_geometry_columns table are valid.

Test Method

  1. SELECT DISTINCT geometry_type_name from gpkg_geometry_columns

  2. Not testable if returns an empty result set

  3. For each row from step 1

    1. Fail if a returned geometry_type_name value is not in Table 28 in Annex G

  4. Pass if no fails.

Reference

Clause 2.1.5.1.2 Req 25:

Test Type

Capability

Test Case ID

/opt/features/geometry_columns/data/data_values_srs_id

Test Purpose

Verify that the gpkg_geometry_columns table srs_id column values are valid.

Test Method

  1. PRAGMA foreign_key_check('gpkg_geometry_columns')

  2. Fail if returns any rows with a fourth column foreign key index value of 0

Reference

Clause 2.1.5.1.2 Req 26:

Test Type

Capability

Test Case ID

/opt/features/geometry_columns/data/data_values_srs_id_match

Test Purpose

Verify that the srs_id column values are consistent between gpkg_geometry_columns and gpkg_contents.

Test Method

  1. SELECT a.srs_id, b.srs_id FROM gpkg_geometry_columns a, gpkg_contents b WHERE a.table_name = b.table_name

  2. Fail if returns any rows have non-matching SRS IDs

Reference

Clause 2.1.5.1.2 Req 146:

Test Type

Capability

Test Case ID

/opt/features/geometry_columns/data/data_values_z

Test Purpose

Verify that the gpkg_geometry_columns table z column values are valid.

Test Method

  1. SELECT z FROM gpkg_geometry_columns

  2. Not testable if returns an empty result set

  3. SELECT z FROM gpkg_geometry_columns WHERE z NOT IN (0,1,2)

  4. Fail if does not return an empty result set

  5. Pass otherwise.

Reference

Clause 2.1.5.1.2 Req 27:

Test Type

Capability

Test Case ID

/opt/features/geometry_columns/data/data_values_m

Test Purpose

Verify that the gpkg_geometry_columns table m column values are valid.

Test Method

  1. SELECT m FROM gpkg_geometry_columns

  2. Not testable if returns an empty result set

  3. SELECT m FROM gpkg_geometry_columns WHERE m NOT IN (0,1,2)

  4. Fail if does not return an empty result set

  5. Pass otherwise.

Reference

Clause 2.1.5.1.2 Req 28:

Test Type

Capability
A.2.1.6. Vector Features User Data Tables
A.2.1.6.1. Data

Table Definition

Test Case ID

/opt/features/vector_features/data/feature_table

Test Purpose

Verify that every vector feature table or view is present and that each has a discoverable integer primary key or key-like column.

Test Method

  1. SELECT table_name FROM gpkg_contents WHERE data_type = 'features'

  2. Not testable if returns an empty result set

  3. For each row from step 1

    1. PRAGMA table_info(table_name)

    2. Fail if returns an empty result set

    3. Identify the id column

      1. If the result set contains a row where the pk column value is 1 then it is the id column

      2. Otherwise the first column is the id column

    4. Fail if the id column is not of type "INTEGER"

    5. SELECT COUNT(*) - COUNT(DISTINCT id) from table_name

    6. Fail if result is nonzero

  4. Pass if no fails.

Reference

Clause 2.1.6.1.1 Req 29:

Reference

Clause 2.1.6.1.1 Req 150:

Test Type

Basic

Test Case ID

/opt/features/vector_features/data/feature_table_one_geometry_column

Test Purpose

Verify that every vector features user data table has one geometry column.

Test Method

  1. SELECT table_name FROM gpkg_contents WERE data_type = 'features'

  2. Not testable if returns an empty result set

  3. For each row table name from step 1

    1. SELECT column_name from gpkg_geometry_columns where table_name = row table name

    2. Fail if returns more than one column name

  4. Pass if no fails

Reference

Clause 2.1.6.1.1 Req 30:

Test Type

Capability

Test Case ID

/opt/features/vector_features/data/feature_table_geometry_column_type

Test Purpose

Verify that the declared SQL type of a feature table geometry column is the uppercase geometry type name from Annex G specified by the geometry_type_name column for that column_name and table_name in the gpkg_geometry_columns table.

Test Method

  1. SELECT table_name, column_name, geometry_type_name table_name FROM gpkg_geometry_columns WHERE table_name IN (SELECT table_name FROM gpkg_contents WHERE data_type = 'features')

  2. For each row selected in (1):

    1. PRAGMA table_info('{selected table_name}')

    2. Fail if declared type of column_name selected in (1) is not the geometry_type_name selected in (1)

  3. Pass if no fails

Reference

Clause 2.1.6.1.1 Req 31:

Test Type

Capability

Table Data Values

Test Case ID

/opt/features/vector_features/data/data_values_geometry_type

Test Purpose

Verify that the geometry type of feature geometries are of the type specified by the gpkg_geometry columns table geometry_type_name column value.

Test Method

  1. SELECT table_name AS tn, column_name AS cn, geometry_type_name AS gt_name FROM gpkg_geometry_columns WHERE table_name IN (SELECT table_name FROM gpkg_contents WHERE data_type = 'features')

  2. Not testable if returns an empty result set

  3. For each row from step 1

    1. Select the set of geometry types in use for the values in cn

    2. For each row actual_type_name from step a

      1. Determine if each geometry type matches the actual_type_name

      2. Fail if any geometries do not match

  4. Pass if no fails

Reference

Clause 2.1.6.1.2 Req 32:

Test Type

Capability

Test Case ID

/opt/features/vector_features/data/data_value_geometry_srs_id

Test Purpose

Verify the the srs_id of feature geometries are the srs_id specified for the gpkg_geometry_columns table srs_id column value.

Test Method

  1. SELECT table_name AS tn, column_name AS cn, srs_id AS gc_srs_id FROM gpkg_geometry_columns WHERE table_name IN (SELECT table_name FROM gpkg_contents where data_type = 'features')

  2. Not testable if returns an empty result set

  3. For each row from step 1

    1. *Select the set of SRIDs in use for the values in cn

    2. For each row from step a

      1. *Fail if any SRID is not equal to gc_srs_id

  4. Pass if no fails

Reference

Clause 2.1.6.1.2 Req 33:

Test Type

Capability

A.2.2. Tiles

A.2.2.1. Contents
A.2.2.1.1. Data

Contents Table – Tiles Row

Test Case ID

/opt/tiles/contents/data/tiles_row

Test Purpose

Verify that the gpkg_contents table_name value table exists and is apparently a tiles table for every row with a data_type column value of "tiles".

Test Method

  1. Execute test /opt/tiles/tile_pyramid/data/table_def

Reference

Clause 2.2.2.1.1 Req 34:

Test Type

Capability
A.2.2.2. Zoom Levels
A.2.2.2.1. Data

Zoom Times Two

Test Case ID

/opt/tiles/zoom_levels/data/zoom_times_two

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.

Test Method

  1. SELECT table_name FROM gpkg_contents WHERE data_type = 'tiles'

  2. Not testable if returns empty result set

  3. For each row table_name from step 1

    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

    2. Not testable if returns empty result set, or only one row

    3. Not testable if there are not two rows with adjacent zoom levels

    4. Fail if any pair of rows for adjacent zoom levels have pixel_x_size or pixel_y_size values that differ by other than factors of two

  4. Pass if no fails

Reference

Clause 2.2.3.1.1 Req 35:

Test Type

Capability
A.2.2.3. Tile Encoding PNG
A.2.2.3.1. Data

MIME Type PNG

Test Case ID

/opt/tiles/tiles_encoding/data/mime_type_png

Test Purpose

Verify that a tile matrix user data table that contains tile data that is not MIME type "image/jpeg" by default contains tile data in MIME type "image/png".

Test Method

  1. SELECT table_name AS tn FROM gpkg_contents WHERE data_type = \'tiles'

  2. For each row tbl_name from step 1

    1. WHEN (SELECT tbl_name FROM sqlite_master WHERE tbl_name = \'gpkg_extensions') = \'gpkg_extensions' THEN (SELECT extension_name FROM gpkg_extensions WHERE table_name = \'tn' AND column_name = \'tile_data') END;

      1. Not testable unless it returns empty result set

    2. SELECT tile_data FROM tn

    3. For each row tile_data from step a

      1. Pass if tile data in MIME type image/jpeg

      2. Pass if tile data in MIME type image/png

      3. Fail if no passes

Reference

Clause 2.2.4.1.1 Req 36:

Test Type

Capability
A.2.2.4. Tile Encoding JPEG
A.2.2.4.1. Data

MIME Type JPEG

Test Case ID

/opt/tiles/tiles_encoding/data/mime_type_jpeg

Test Purpose

Verify that a tile matrix user data table that contains tile data that is not MIME type "image/png" by default contains tile data in MIME type "image/jpeg".

Test Method

  1. SELECT table_name AS tn FROM gpkg_contents WHERE data_type = 'tiles'

  2. For each row tbl_name from step 1

    1. WHEN (SELECT tbl_name FROM sqlite_master WHERE tbl_name = 'gpkg_extensions') = 'gpkg_extensions' THEN (SELECT extension_name FROM gpkg_extensions WHERE table_name = 'tn' AND column_name = 'tile_data') END;

      1. Not testable unless it returns empty result set

    2. SELECT tile_data FROM tn

    3. For each row tile_data from step a

      1. Pass if tile data in MIME type image/jpeg

      2. Pass if tile data in MIME type image/png

      3. Fail if no passes

Reference

Clause 2.2.5.1.1 Req 37:

Test Type

Capability
A.2.2.5. Tile Matrix Set
A.2.2.5.1. Data

Table Definition

Test Case ID

/opt/tiles/gpkg_tile_matrix_set/data/table_def

Test Purpose

Verify that the gpkg_tile_matrix_set table exists and has the correct definition.

Test Method

  1. SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'gpkg_tile_matrix_set'

  2. Fail if returns an empty result set.

  3. 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 sample_feature_table Table Definition SQL (Informative). Column order, check constraint and trigger definitions, and other column definitions in the returned sql are irrelevant.

  4. Fail otherwise.

Reference

Clause 2.2.6.1.1 Req 38:

Test Type

Capability

Table Data Values

Test Case ID

/opt/tiles/gpkg_tile_matrix_set/data/data_values_table_name

Test Purpose

Verify that values of the gpkg_tile_matrix_set table_name column reference values in the gpkg_contents table_name column.

Test Method

  1. SELECT table_name FROM gpkg_tile_matrix_set

  2. Not testable if returns an empty result set

  3. SELECT table_name FROM gpkg_tile_matrix_set tms WHERE table_name NOT IN (SELECT table_name FROM gpkg_contents gc WHERE tms.table_name = gc.table_name)

  4. Fail if result set contains any rows

  5. Pass otherwise

Reference

Clause 2.2.6.1.2 Req 39:

Test Type

Capability

Test Case ID

/opt/tiles/gpkg_tile_matrix_set/data/data_values_row_record

Test Purpose

Verify that the gpkg_tile_matrix_set table contains a row record for each tile pyramid user data table.

Test Method

  1. SELECT table_name AS <user_data_tiles_table> from gpkg_contents where data_type = 'tiles'

  2. Not testable if returns an empty result set

  3. For each row from step 1

    1. SELECT sql FROM sqlite_master WHERE type='table' AND tbl_name = '<user_data_tiles_table>'

    2. Fail if returns an empty result set

  4. Pass if no fails

Reference

Clause 2.2.6.1.2 Req 40:

Test Type

Capability

Test Case ID

/opt/tiles/gpkg_tile_matrix_set/data/data_values_srs_id

Test Purpose

Verify that the gpkg_tile_matrix_set table srs_id column values reference gpkg_spatial_ref_sys srs_id column values.

Test Method

  1. PRAGMA foreign_key_check('gpkg_geometry_columns')

  2. Fail if returns any rows with a fourth column foreign key index value of 1 (gpkg_spatial_ref_sys)

Reference

Clause 2.2.6.1.2 Req 41:

Test Type

Capability

Test Case ID

/opt/tiles/gpkg_tile_matrix_set/data/data_values_srs_id_match

Test Purpose

Verify that the srs_id column values are consistent between gpkg_tile_matrix_set and gpkg_contents tables.

Test Method

  1. SELECT a.srs_id, b.srs_id FROM gpkg_tile_matrix_set a, gpkg_contents b WHERE a.table_name = b.table_name

  2. Fail if returns any rows have non-matching SRS IDs

Reference

Clause 2.2.6.1.2 Req 147:

Test Type

Capability
A.2.2.6. Tile Matrix
A.2.2.6.1. Data

Table Definition

Test Case ID

/opt/tiles/gpkg_tile_matrix/data/table_def

Test Purpose

Verify that the gpkg_tile_matrix table exists and has the correct definition.

Test Method

  1. SELECT sql FROM sqlite_master WHERE type = \'table' AND tbl_name = \'gpkg_tile_matrix'

  2. Fail if returns an empty result set.

  3. 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, primary, and foreign key constraints match all of those in the contents of Annex C Table 23.

  4. Fail otherwise.

Reference

Clause 2.2.7.1.1 Req 42:

Test Type

Basic

Table Data Values

Test Case ID

/opt/tiles/gpkg_tile_matrix/data/data_values_table_name

Test Purpose

Verify that values of the gpkg_tile_matrix table_name column reference values in the gpkg_contents table_name column.

Test Method

  1. SELECT table_name FROM gpkg_tile_matrix

  2. Not testable if returns an empty result set

  3. SELECT table_name FROM gpkg_tile_matrix tmm WHERE table_name NOT IN (SELECT table_name FROM gpkg_contents gc WHERE tmm.table_name = gc.table_name)

  4. Fail if result set contains any rows

  5. Pass otherwise

Reference

Clause 2.2.7.1.2 Req 43:

Test Type

Capability

Test Case ID

/opt/tiles/gpkg_tile_matrix/data/data_values_zoom_level_rows

Test Purpose

Verify that the gpkg_tile_matrix table contains a row record for each zoom level that contains one or more tiles in each tile pyramid user data table.

Test Method

  1. SELECT table_name AS <user_data_tiles_table> from gpkg_contents where data_type = 'tiles'

  2. Not testable if returns an empty result set

  3. For each row from step 1

    1. SELECT DISTINCT gtmm.zoom_level AS gtmm_zoom, udt.zoom_level AS udtt_zoom FROM gpkg_tile_matrix AS gtmm LEFT OUTER JOIN <user_data_tiles_table> AS udtt ON udtt.zoom_level = gtmm.zoom_level AND gtmm.t_table_name = '<user_data_tiles_table>'

    2. Fail if any gtmm_zoom column value in the result set is NULL

  4. Pass if no fails

Reference

Clause 2.2.7.1.2 Req 44:

Test Type

Capability

Test Case ID

/opt/tiles/gpkg_tile_matrix/data/data_values_width_height

Test Purpose

Verify that the tile matrix extents in gpkg_tile_matrix_set match the contents of the gpkg_tile_matrix table.

Test Method

  1. SELECT table_name AS <user_data_tiles_table> from gpkg_contents where data_type = 'tiles'

  2. Not testable if returns an empty result set

  3. For each row from step 1

    1. SELECT max_x - min_x from gpkg_tile_matrix_set where table_name = '<user_data_tiles_table>'

    2. SELECT zoom_level, matrix_width * tile_width * pixel_x_size from gpkg_tile_matrix where table_name = '<user_data_tiles_table>'

    3. SELECT max_y - min_y from gpkg_tile_matrix_set where table_name = '<user_data_tiles_table>'

    4. SELECT zoom_level, matrix_height * tile_height * pixel_y_size from gpkg_tile_matrix where table_name = '<user_data_tiles_table>'

    5. Fail if, for any zoom level, the difference for an axis does not equal the product for that axis at that zoom level

  4. Pass if no fails

Reference

Clause 2.2.7.1.2 Req 45:

Test Type

Capability

Test Case ID

/opt/tiles/gpkg_tile_matrix/data/data_values_zoom_level

Test Purpose

Verify that zoom level column values in the gpkg_tile_matrix table are not negative.

Test Method

  1. SELECT zoom_level FROM gpkg_tile_matrix

  2. Not testable if returns an empty result set

  3. SELECT min(zoom_level) FROM gpkg_tile_matrix_metadata.

  4. Fail if less than 0.

  5. Pass otherwise.

Reference

Clause 2.2.7.1.2 Req 46:

Test Type

Capability

Test Case ID

/opt/tiles/gpkg_tile_matrix/data/data_values_matrix_width

Test Purpose

Verify that the matrix_width values in the gpkg_tile_matrix table are valid.

Test Method

  1. SELECT matrix_width FROM gpkg_tile_matrix

  2. Not testable if returns an empty result set

  3. SELECT min(matrix_width) FROM gpkg_tile_matrix.

  4. Fail if less than 1.

  5. Pass otherwise.

Reference:

Clause 2.2.7.1.2 Req 47:

Test Type:

Capability

Test Case ID

/opt/tiles/gpkg_tile_matrix/data/data_values_matrix_height

Test Purpose

Verify that the matrix_height values in the gpkg_tile_matrix table are valid.

Test Method

  1. SELECT matrix_height FROM gpkg_tile_matrix

  2. Not testable if returns an empty result set

  3. SELECT min(matrix_height) FROM gpkg_tile_matrix.

  4. Fail if less than 1.

  5. Pass otherwise.

Reference

Clause 2.2.7.1.2 Req 48:

Test Type

Capability

Test Case ID

/opt/tiles/gpkg_tile_matrix/data/data_values_tile_width

Test Purpose

Verify that the tile_width values in the gpkg_tile_matrix table are valid.

Test Method

  1. SELECT tile_width FROM gpkg_tile_matrix

  2. Not testable if returns an empty result set

  3. SELECT min(tile_width) FROM gpkg_tile_matrix.

  4. Fail if less than 1.

  5. Pass otherwise.

Reference

Clause 2.2.7.1.2 Req 49:

Test Type

Capability

Test Case ID

/opt/tiles/gpkg_tile_matrix/data/data_values_tile_height

Test Purpose

Verify that the tile_height values in the gpkg_tile_matrix table are valid.

Test Method

  1. SELECT tile_height FROM gpkg_tile_matrix

  2. Not testable if returns an empty result set

  3. SELECT min(tile_height) FROM gpkg_tile_matrix.

  4. Fail if less than 1.

  5. Pass otherwise.

Reference

Clause 2.2.7.1.2 Req 50:

Test Type

Capability

Test Case ID

/opt/tiles/gpkg_tile_matrix/data/data_values_pixel_x_size

Test Purpose

Verify that the pixel_x_size values in the gpkg_tile_matrix table are valid.

Test Method

  1. SELECT pixel_x_size FROM gpkg_tile_matrix

  2. Not testable if returns an empty result set

  3. SELECT min(pixel_x_size) FROM gpkg_tile_matrix.

  4. Fail if less than 0.

  5. Pass otherwise.

Reference

Clause 2.2.7.1.2 Req 51:

Test Type

Capability

Test Case ID

/opt/tiles/gpkg_tile_matrix/data/data_values_pixel_y_size

Test Purpose

Verify that the pixel_y_size values in the gpkg_tile_matrix table are valid.

Test Method

  1. SELECT pixel_y_size FROM gpkg_tile_matrix

  2. Not testable if returns an empty result set

  3. SELECT min(pixel_y_size) FROM gpkg_tile_matrix.

  4. Fail if less than 0.

  5. Pass otherwise.

Reference

Clause 2.2.7.1.2 Req 52:

Test Type

Capability

Test Case ID

/opt/tiles/gpkg_tile_matrix/data/data_values_pixel_size_sort

Test Purpose

Verify that the pixel_x_size and pixel_y_size column values for zoom level column values in a gpkg_tile_matrix table sorted in ascending order are sorted in descending order, showing that lower zoom levels are zoomed "out".

Test Method

  1. SELECT table_name FROM gpkg_contents WHERE data_type = 'tiles'

  2. Not testable if returns empty result set

  3. For each row table_name from step 1

    1. SELECT zoom_level, pixel_x_size, pixel_y_size from gpkg_tile_matrix WHERE table_name = row table name ORDER BY zoom_level ASC

    2. Not testable if returns empty result set

    3. Fail if pixel_x_sizes are not sorted in descending order

    4. Fail if pixel_y_sizes are not sorted in descending order

  4. Pass if testable and no fails

Reference

Clause 2.2.7.1.2 Req 53:

Test Type

Capability
A.2.2.7. Tile Pyramid User Data
A.2.2.7.1. Data

Table Definition

Test Case ID

/opt/tiles/tile_pyramid/data/table_def

Test Purpose

Verify that the tile pyramids each have a table or view, that all required columns are present, and that the "id" column has unique values.

Test Method

  1. SELECT COUNT(table_name) FROM gpkg_contents WERE data_type = "tiles"

  2. Not testable if less than 1

  3. SELECT table_name FROM gpkg_contents WHERE data_type = "tiles"

  4. For each row from step 3

    1. PRAGMA table_info(table_name)

    2. Fail if returns an empty result set

    3. Fail if result set does not contain one row where the "type" column value is "INTEGER" and the "name" column value is "id"

    4. Fail if result set does not contain four other rows where the name column values are "zoom_level","tile_column","tile_row", and "tile_data".

    5. SELECT COUNT(*) - COUNT(DISTINCT id) from table_name

    6. Fail if result is nonzero

  5. Pass if no fails

Reference

Clause 2.2.8.1.1 Req 54:

Test Type

Basic

Table Data Values

Test Case ID

/opt/tiles/tile_pyramid/data/data_values_zoom_levels

Test Purpose

Verify that the zoom level column values in each tile pyramid user data table are within the range of zoom levels defined by rows in the gpkg_tile_matrix table.

Test Method

  1. SELECT DISTINCT table_name AS <user_data_tiles_table> FROM gpkg_tile_matrix

  2. Not testable if returns an empty result set

  3. For each row <user_data_tiles_table> from step 1

    1. SELECT zoom_level FROM <user_data_tiles_table>

    2. If result set not empty

      1. SELECT MIN(gtmm.zoom_level) AS min_gtmm_zoom, MAX(gtmm.zoom_level) AS max_gtmm_zoom FROM gpkg_tile_matrix WHERE table_name = <user_data_tiles_table>

      2. SELECT id FROM <user_data_tiles_table> WHERE zoom_level < min_gtmm_zoom

      3. Fail if result set not empty

      4. SELECT id FROM <user_data_tiles_table> WHERE zoom_level > max_gtmm_zoom

      5. Fail if result set not empty

      6. Log pass otherwise

  4. Pass if logged pas and no fails

Reference

Clause 2.2.8.1.2 Req 55:

Test Type

Capability

Test Case ID

/opt/tiles/tile_pyramid/data/data_values_tile_column

Test Purpose

Verify that the tile_column column values for each zoom level value in each tile pyramid user data table are within the range of columns defined by rows in the gpkg_tile_matrix table.

Test Method

  1. SELECT DISTINCT table_name AS <user_data_tiles_table> FROM gpkg_tile_matrix

  2. Not testable if returns an empty result set

  3. For each row <user_data_tiles_table> from step 1

    1. SELECT DISTINCT gtmm.zoom_level AS gtmm_zoom, gtmm.matrix_width AS gtmm_width, udt.zoom_level AS udt_zoom, udt.tile_column AS udt_column FROM gpkg_tile_matrix AS gtmm LEFT OUTER JOIN <user_data_tiles_table> AS udt ON udt.zoom_level = gtmm.zoom_level AND gtmm.t_table_name = '<user_data_tiles_table>' AND (udt_column < 0 OR udt_column > (gtmm_width - 1))

    2. Fail if any udt_column value in the result set is not NULL

    3. Log pass otherwise

  4. Pass if logged pass and no fails

Reference

Clause 2.2.8.1.2 Req 56:

Test Type

Capability

Test Case ID

/opt/tiles/tile_pyramid_data/data_values_tile_row

Test Purpose

Verify that the tile_row column values for each zoom level value in each tile pyramid user data table are within the range of rows defined by rows in the gpkg_tile_matrix table.

Test Method

  1. SELECT DISTINCT table_name AS <user_data_tiles_table> FROM gpkg_tile_matrix

  2. Not testable if returns an empty result set

  3. For each row <user_data_tiles_table> from step 1

    1. SELECT DISTINCT gtmm.zoom_level AS gtmm_zoom, gtmm.matrix_height AS gtmm_height, udt.zoom_level AS udt_zoom, udt.tile_row AS udt_row FROM gpkg_tile_matrix AS gtmm LEFT OUTER JOIN <user_data_tiles_table> AS udt ON udt.zoom_level = gtmm.zoom_level AND gtmm.t_table_name = '<user_data_tiles_table> ' AND (udt_row < 0 OR udt_row > (gtmm_height - 1))

    2. Fail if any udt_row value in the result set is not NULL

    3. Log pass otherwise

  4. Pass if logged pass and no fails

Reference

Clause 2.2.8.1.2 Req 57:

Test Type

Capability

A.2.3. Extension Mechanism

A.2.3.1. Extensions
A.2.3.1.1. Data

Table Definition

Test Case ID

/opt/extension_mechanism/data/table_def

Test Purpose

Verify that a gpkg_extensions table exists and has the correct definition.

Test Method

  1. SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'gpkg_extensions'

  2. Fail if returns an empty result set.

  3. 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 Definition. Column order, check constraint and trigger definitions, and other column definitions in the returned sql are irrelevant.

  4. Fail otherwise.

Reference

Clause 2.3.2.1.1 Req 58:

Test Type

Basic

Table Data Values

Test Case ID

/opt/extension_mechanism/data/data_values_for_extensions

Test Purpose

Verify that every extension of a GeoPackage is registered in a row in the gpkg_extensions table

Test Method

  1. Manual inspection

Reference

Clause 2.3.2.1.2 Req 59:

Test Type

Capability

Test Case ID

/opt/extension_mechanism/data/data_values_table_name

Test Purpose

Verify that the table_name column values in the gpkg_extensions table are valid.

Test Method

  1. SELECT lower(table_name) AS table_name, column_name FROM gpkg_extensions;

  2. Not testable if table does not exist or query returns an empty result set.

  3. For each row from step one

    1. "SELECT DISTINCT lower(ge.table_name) AS ge_table, lower(sm.tbl_name) AS tbl_name FROM gpkg_extensions AS ge LEFT OUTER JOIN sqlite_master AS sm ON lower(ge.table_name) = lower(sm.tbl_name);

    2. Fail if ge_table and tbl_name are not equal (or both null).

  4. Pass if no fails.

Reference

Clause 2.3.2.1.2 Req 60:

Test Type

Capability

Test Case ID

/opt/extension_mechanism/data/data_values_column_name

Test Purpose

Verify that the column_name column values in the gpkg_extensions table are valid.

Test Method

  1. SELECT table_name, column_name FROM gpkg_extensions WHERE column_name IS NOT NULL

  2. Pass if returns an empty result set

  3. For each row from step 3

    1. SELECT count(column_name) FROM table_name

      1. Fail if query is invalid, suggesting an invalid column name

    2. Log pass otherwise

  4. Pass if logged pass and no fails.

Reference

Clause 2.3.2.1.2 Req 61:

Test Type

Capability

Test Case ID

/opt/extension_mechanism/data/data_values_extension_name

Test Purpose

Verify that the extension_name column values in the gpkg_extensions table are valid.

Test Method

  1. SELECT extension_name FROM gpkg_extensions

  2. Not testable if returns an empty result set

  3. For each row returned from step 1

    1. Log pass if extension_name is one of those listed in Annex F.

    2. Separate extension_name into <author> and <extension> at the first "_"

    3. Fail if <author> is "gpkg"

    4. Fail if <author> contains characters other than [a-zA-Z0-9]

    5. Fail if <extension> contains characters other than [a-zA-Z0-9_]

    6. Log pass otherwise

  4. Pass if logged pass and no fails.

Reference

Clause 2.3.2.1.2 Req 62:

Test Type

Capability

Test Case ID

/opt/extension_mechanism/data/data_values_definition

Test Purpose

Verify that the definition column value contains or references extension documentation

Test Method

  1. SELECT definition FROM gpkg_extensions

  2. Not testable if returns an empty result set

  3. For each row returned from step 1

    1. Inspect if definition value is not like "Annex %", or "http%" or mailto:% or "Extension Title%"

    2. Fail if definition value does not contain or reference extension documentation

  4. Pass if no fails

Reference

Clause 2.3.2.1.2 Req 63:

Test Type

Capability

Test Case ID

/opt/extension_mechanism/data/data_values_scope

Test Purpose

Verify that the scope column value is "read-write" or "write-only"

Test Method

  1. SELECT scope FROM gpkg_extensions

  2. Not testable if returns an empty result set

  3. For each row returned from step 1

    1. Fail if value is not "read-write" or "write-only"

  4. Pass if no fails

Reference

Clause 2.3.2.1.2 Req 64:

Test Type

Capability

A.2.4. Attributes

A.2.4.1. Contents
A.2.4.1.1. Data

Contents Table – Attributes Row

Test Case ID

/opt/attributes/contents/data/attributes_row

Test Purpose

Verify that the gpkg_contents table_name value table exists and is apparently an attributes table for every row with a data_type column value of "attributes".

Test Method

  1. SELECT table_name FROM gpkg_contents WHERE data_type = "attributes"

  2. Not testable if returns an empty result set

  3. For each row from step 1

    1. PRAGMA table_info(table_name)

    2. Fail if returns an empty result set

    3. Identify the id column

      1. If the result set contains a row where the pk column value is 1 then it is the id column

      2. Otherwise the first column is the id column

    4. Fail if the id column is not of type "INTEGER"

    5. SELECT COUNT(*) - COUNT(DISTINCT id) from table_name

    6. Fail if result is nonzero

  4. Pass if no fails.

Reference

Clause 2.4.2.1.1 Req 118

Reference

Clause 2.4.3.1.1 Req 119

Reference

Clause 2.4.3.1.1 Req 151

Test Type

Capability

Annex B: Background and Context (Normative)

B.1. Background

An open standard non-proprietary platform-independent GeoPackage container for distribution and direct use of all kinds of geospatial data will increase the cross-platform interoperability of geospatial applications and web services. Standard APIs for access and management of GeoPackage data will provide consistent query and update results across such applications and services. Increased interoperability and result consistency will enlarge the potential market for such applications and services, particularly in resource-constrained mobile computing environments like cell phones and tablets. GeoPackages will become the standard containers for "MyGeoData" that are used as a transfer format by users and Geospatial Web Services and a storage format on personal and enterprise devices.

This OGC® GeoPackage Encoding Standard defines a GeoPackage as a self-contained, single-file, cross-platform, serverless, transactional, open source SQLite data container with table definitions, relational integrity constraints, an SQL API exposed via a "C" CLI and JDBC, and manifest tables that together act as an exchange and direct-use format for multiple types of geospatial data including vector features, features with raster attributes and tile matrix pyramids, especially on mobile / hand held devices in disconnected or limited network connectivity environments.

Table formats, definitions of geometry types and metadata tables, relational integrity constraints, and SQL API are interdependent specification facets of the SF-SQL [I9][I10][I11] and SQL-MM (Spatial) [I12] standards that serve as normative references for the vector feature portion of this standard.

This standard attempts to support and use relevant raster types, storage table definitions, and metadata from widely adopted implementations and existing standards such as WMTS [I16] and ISO metadata [I28], to integrate use of rasters as attributes of geospatial features, and to define relational integrity constraints and an SQL API thereon to provide a raster analogy to the SF-SQL and SF-MM data access and data quality assurance capabilities.

Conformance classes for this standard are classified as core (mandatory) and extension (optional). The simple core of an Empty GeoPackage contains two SQL tables.

Future versions of this standard may include requirements for elevation data and routes. Future enhancements to this standard, a future GeoPackage Web Service specification, and modifications to existing OGC Web Service (OWS) specifications to use GeoPackages as exchange formats may allow OWS to support provisioning of GeoPackages throughout an enterprise or information community.

B.2. Document terms and definitions

This document uses the standard terms defined in Subclause 5.3 of [OGC 06-121], which is based on the ISO/IEC Directives, Part 2. Rules for the structure and drafting of International Standards. In particular, the word "shall" (not "must") is the verb form used to indicate a requirement to be strictly followed to conform to this standard.

For the purposes of this document, the following terms and definitions apply.

Empty GeoPackage

A GeoPackage that contains a gpkg_spatial_ref_sys table, a gpkg_contents table with row record(s) with data_type column values of "features" or "tiles", and corresponding features tables per clause Features and/or tiles tables per clause Tiles where the user data tables per clauses 2.1.6. and 2.2.8 exist but contain no rows.

Extended GeoPackage

A GeoPackage that contains any additional data elements (tables or columns) or SQL constructs (data types, indexes, constraints or triggers) that are not specified in this encoding standard.

geolocate

identify a real-world geographic location

GeoPackage file

a platform-independent SQLite database file that contains GeoPackage data and metadata tables with specified definitions, integrity assertions, format limitations and content constraints.

GeoPackage SQLite Configuration

consists of the SQLite 3 software library and a set of compile- and runtime configurations options.

GeoPackage SQLite Extension

a SQLite loadable extension that MAY provide SQL functions to support spatial indexes and SQL triggers linked to a SQLite library with specified configuration requirements to provide SQL API access to a GeoPackage.

georectified

raster whose pixels have been regularly spaced in a geographic (i.e., latitude / longitude) or projected map coordinate system using ground control points so that any pixel can be geolocated given its grid coordinate and the grid origin, cell spacing, and orientation.

orthorectified

georectified raster that has also been corrected to remove image perspective (camera angle tilt), camera and lens induced distortions, and terrain induced distortions using camera calibration parameters and DEM elevation data to accurately align with real world coordinates, have constant scale, and support direct measurement of distances, angles, and areas.

tile

a rectangular pictorial representation of geographic data, often part of a set of such elements, covering a spatially contiguous extent and sharing similar information content and graphical styling, which can be uniquely defined by a pair of indexes for the column and row along with an identifier for the tile matrix.

tile matrix

a collection of tiles for a fixed scale

tile pyramid

a collection of tile matrices defined at different scales

Valid GeoPackage

A GeoPackage that contains features per clause Features and/or tiles per clause Tiles and row(s) in the gpkg_contents table with data_type column values of "features" and/or "tiles" describing the user data tables.

B.3. Conventions

Symbols (and abbreviated terms)

ACID

Atomic, Consistent, Isolated, and Durable

ASCII

American Standard Code for Information Interchange

API

Application Program Interface

BLOB

Binary Large OBject

CLI

Call-Level Interface

COTS

Commercial Off The Shelf

DEM

Digital Elevation Model

GPKG

GeoPackage

GRD

Ground Resolved Distance

EPSG

European Petroleum Survey Group

FK

Foreign Key

IETF

Internet Engineering Task Force

IIRS

Image Interpretability Rating Scale

IRARS

Imagery Resolution Assessments and Reporting Standards (Committee)

ISO

International Organization for Standardization

JDBC

Java Data Base Connectivity

JPEG

Joint Photographics Expert Group (image format)

MIME

Multipurpose Internet Mail Extensions

NIIRS

National Imagery Interpretability Rating Scale

OGC

Open Geospatial Consortium

PK

Primary Key

PNG

Portable Network Graphics (image format)

RDBMS

Relational Data Base Management System

RFC

Request For Comments

SQL

Structured Query Language

SRID

Spatial Reference (System) Identifier

UML

Unified Modeling Language

UK

Unique Key

UTC

Coordinated Universal Time

XML

eXtensible Markup Language

1D

One Dimensional

2D

Two Dimensional

3D

Three Dimensional

B.4. Submitting Organizations (Informative)

The following organizations submitted this Encoding Standard to the Open Geospatial Consortium as a Request For Comment (RFC).

  • Envitia

  • Luciad

  • Sigma Bravo

  • The Carbon Project

  • U.S. Army Geospatial Center

  • U.S. National Geospatial Intelligence Agency

B.5. Document contributor contact points (Informative)

All questions regarding this document should be directed to the editor or the contributors:

Table 13. Document contributors
Name Organization Email

Brett Antonides

LNM Solutions

brett.antonides<at>lmnsolutions.com

Kevin Backe

U.S. Army Geospatial Center GASD

Kevin.Backe<at>usace.army.mil

Roger Brackin

Envitia

roger.brackin<at>envitia.com

Chris Clark

Compusult

chrisc<at>compusult.net

Scott Clark

LNM Solutions

scott.clark<at>lmnsolutions.com

David Cray

U.S. Army Geospatial Center GASD

David.Cray<at>usace.army.mil

Paul Daisey

Image Matters

pauld<at>imagemattersllc.com

Rich Fecher

Radiant Solutions

richard.fecher<at>radiantsolutions.com

Nathan P. Frantz

U.S. Army Geospatial Center ERDC

Nathan.P.Frantz<at>usace.army.mil

Alessandro Furieri

Spatialite

a.furieri<at>lqt.it

Randy Gladish

Image Matters

randyg<at>imagemattersllc.com

Eric Gundersen

MapBox

eric<at>mapbox.com

Brad Hards

Sigma Bravo

bhards<at>sigmabravo.com

Jeff Harrison

The Carbon Project

jharrison<at>thecarbonproject.com

Chris Holmes

OpenGeo

cholmes<at>9eo.org

Frederic Houbie

Luciad

frederic.houbie<at>luciad.com

Sean Hogan

Compusult

sean<at>compusult.net

Kirk Jensen

Image Matters

kirkj<at>imagemattersllc.com

(chinese chars not working) Joshua

Feng China University

joshua<at>gis.tw

Terry A. Idol

U.S. National Geospatial Intelligence Agency

Terry.A.Idol<at>nga.mil

Drew Kurry

Digital Globe

dkurry<at>digitalglobe.com

Steven Lander

Reinventing Geospatial

steven.lander<at>rgi-corp.com

Tom MacWright

MapBox

tom<at>mapbox.com

Joan Maso Pau

Universitat Autònoma de Barcelona (CREAF)

joan.maso<at>uab.es

Kevin S. Mullane

U.S. Army Geospatial Center GASD

Kevin.S.Mullane<at>usace.army.mil

Brian Osborn

CACI

bosborn<at>caci.com

(chinese chars not working) Yi-Min Huang

Feng China University

niner<at>gis.tw

Andrea Peri

Regione Toscana Italy

andrea.peri<at>regione.toscana.it

Paul Ramsey

OpenGeo

pramsey<at>opengeo.org

Matthew L. Renner

U.S. Army Geospatial Center ERDC

Matthew.L.Renner<at>usace.army.mil

Even Rouault

Mines-Paris

even.rouault<at>mines-paris.org

Keith Ryden

Environmental Systems Research Institute

kryden<at>esri.com

Scott Simmons

CACI

scsimmons<at>caci.com

Ingo Simonis

International Geospatial Services Institute

ingo.simonis<at>igsi.eu

Raj Singh

Open Geospatial Consortium

rsingh<at>opengeospatial.org

Steve Smyth

Open Site Plan

steve<at>opensiteplan.org

Donald V. Sullivan

U.S. National Aeronautics and Space Administration

donald.v.sullivan<at>nasa.gov

Christopher Tucker

Mapstory

tucker<at>mapstory.org

Benjamin T. Tuttle

U.S. National Geospatial Intelligence Agency

Benjamin.T.Tuttle<at>nga.mil

Pepijn Van Eeckhoudt

Luciad

pepijn.vaneeckhoudt<at>luciad.com

David G. Wesloh

U.S. National Geospatial Intelligence Agency

David.G.Wesloh<at>nga.mil

Jeff Yutzler

Image Matters

jeffy<at>imagemattersllc.com

Eric Zimmerman

U.S. Army Geospatial Center ERDC

Eric.Zimmerman<at>usace.army.mil

B.6. Revision History (Informative)

Table 14. Revision History
Date Rel Editor Paragraph modified Description

2014-02-10

R10

Paul Daisey

All

1.0.0

2015-04-20

R11

Paul Daisey

All

1.0.1

2015-08-04

R12

Jeff Yutzler

All

1.1.0

2017-08-25

R14

Jeff Yutzler

All

1.2.0

2018-09-06

R15

Jeff Yutzler

All

1.2.1

2020-04-10

R16

Jeff Yutzler

All

Open Comment period for 1.3.0

2020-08-19

R17

Jeff Yutzler

All

1.3.0

2022-08-26

R18

Jeff Yutzler

All

1.3.1

Detailed release notes for this version of GeoPackage are available at: https://github.com/opengeospatial/geopackage/blob/master/spec/core/release_notes/1.4.0/rn.adoc.

B.7. Changes to the OGC® Abstract Specification

The OGC® Abstract Specification does not require changes to accommodate this OGC® standard.

B.8. Changes to OGC® Implementation Standards

None at present.

B.9. Potential Future Work (Informative)

Future versions of this standard MAY do the following.

  • Investigate GeoPackage implementation on SQLite version 4 [B25].

  • Include requirements for elevation data and routes.

  • Future enhancements to this standard, a future GeoPackage Web Service specification and modifications to existing OGC Web Service (OWS) specifications to use GeoPackages as exchange formats MAY allow OWS to support provisioning of GeoPackages throughout an enterprise.

  • Include additional raster / image formats, including fewer restrictions on the image/tiff format.

  • Include additional SQL API routines for interrogation and conversion of raster / image BLOBs.

  • Add infrastructure to the metadata tables such as a temporal_columns table that refers to the time properties of data records.

  • Specify a streaming synchronization protocol for GeoPackage as part of a future GeoPackage Web Service specification, and/or a future version of the GeoPackage and/or Web Synchronization Service specification(s).

  • Address symbology and styling information.

  • Include geographic / geodesic geometry types.

  • Create a GeoPackage Abstract Object Model to support data encodings other than SQL.

  • Add UTFGrid support.

Future versions of this standard and/or one for a GeoPackage Web Service MAY do the following.

  • Address utilities for importing and exporting vector, raster and tile data in various formats.

  • Address encryption of GeoPackages and/or individual tables or column values.

B.10. UML Notation

The diagrams that appear in this standard are presented using the Unified Modeling Language (UML) [B14] static structure diagrams. The UML notations used in this standard for RDBMS tables in a GeoPackage are described in Figure 3 below.

umlnotationforrdbmstables
Figure 3. UML Notation for RDBMS Tables

In this standard, the following two stereotypes of UML classes are used to represent RDBMS tables:

  1. <<table>> An instantiation of a UML class as an RDMBS table.

  2. <<column>> An instantiation of a UML attribute as an RDBMS table column.

In this standard, the following standard data types are used for RDBMS columns:

  1. NULL – The value is a NULL value.

  2. INTEGER – A signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value

  3. REAL – The value is a floating point value, stored as an 8-byte IEEE floating point number.

  4. TEXT – A sequence of characters, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

  5. BLOB – The value is a blob of data, stored exactly as it was input.

  6. NONE – The value is a Date / Time Timestamp

B.11. GeoPackage Tables Detailed Diagram

geopackage uml
Figure 4. GeoPackage Tables Details

B.12. GeoPackage Minimal Tables for Features Diagram

#img_gpkg_uml_featuresreftext='Figure 5'
Figure 5. GeoPackage Minimal Tables for Features

B.13. GeoPackage Minimal Tables for Tiles Diagram