OGC® GeoPackage EncodingStandard
Copyright © 2017 Open Geospatial Consortium.
To obtain additional rights of use, visit http://www.opengeospatial.org/legal/
This document is an OGC Member approved international standard. This document is available ona royalty free, non-discriminatory basis. 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. This is a revision to OGC 12-128r12.
Note that the normative text for this standard is located on the OGC website:

https://portal.opengeospatial.org/files/12-128r14. If there are any differences fromthis HTML document and the normative document, the normative document takes
precedence.

Document type: OGC® Standard
Document subtype: Encoding Standard
Document stage: Approved
Document language: EnglishiOpen Geospatial Consortium
External identifier of this OGC® document: http://www.opengis.net/doc/IS/geopackage/1.2
URL for this OGC® document: http://www.geopackage.org/spec
Internal reference number of this OGC® document: OGC 12-128r14
Version: 1.2
Category: OGC® Encoding Standard
Editor: Jeff Yutzler
Editor Emeritus: Paul Daisey
Previous Version: http://www.opengis.net/doc/IS/geopackage/1.1.0
Publication Date: 2017-08-25
Approval Date: 2017-08-06
Submission Date: 2017-06-07
Original Version: http://www.opengis.net/doc/IS/geopackage/1.0

ii

License Agreement
Permission is hereby granted by the Open Geospatial Consortium, ("Licensor"), free of charge andsubject to the terms set forth below, to any person obtaining a copy of this Intellectual Property andany 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 towhom the Intellectual Property is furnished to do so, provided that all copyright notices on theintellectual 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,
EXPRESSORIMPLIED,INCLUDING

BUT

NOT

LIMITEDTOTHE

WARRANTIESOFMERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, AND NONINFRINGEMENT OF THIRDPARTY RIGHTS. THE COPYRIGHT HOLDER OR HOLDERS INCLUDED IN THIS NOTICE DO NOTWARRANT THAT THE FUNCTIONS CONTAINED IN THE INTELLECTUAL PROPERTY WILL MEETYOUR 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 ANYOTHER 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 theIntellectual 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 enduser sublicense to the Intellectual Property which is in force as of the date of notice of suchtermination. 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 otherparty. 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 allor 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

iii

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 toindicate compliance with any LICENSOR standards or specifications.
This Agreement is governed by the laws of the Commonwealth of Massachusetts. The application tothis Agreement of the United Nations Convention on Contracts for the International Sale of Goods ishereby 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 andenforceable, 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 orremedies 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 andregulations. 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.

iv

Patent Call
Attention is drawn to the possibility that some of the elements of this document may be the subject ofpatent 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.

v

Table of Contents
OGC® GeoPackage Encoding Standard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . i
License Agreement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .iii
Patent Call . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . v
Abstract. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Keywords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
1. Base . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
1.1. Core . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
1.1.1. SQLite Container. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
1.1.2. Spatial Reference Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
1.1.3. Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
2. Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
2.1. Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
2.1.1. Simple Features SQL Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
2.1.2. Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
2.1.3. Geometry Encoding . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
2.1.4. SQL Geometry Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
2.1.5. Geometry Columns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
2.1.6. Vector Feature User Data Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
2.2. Tiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
2.2.1. Tile Matrix Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
2.2.2. Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
2.2.3. Zoom Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
2.2.4. Tile Encoding PNG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
2.2.5. Tile Encoding JPEG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
2.2.6. Tile Matrix Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
2.2.7. Tile Matrix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
2.2.8. Tile Pyramid User Data Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
2.3. Extension Mechanism. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
2.3.1. Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
2.3.2. Extensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
2.4. Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
2.4.1. Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
2.4.2. Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
2.4.3. Attributes User Data Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
3. Security Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Annex A: Conformance / Abstract Test Suite (Normative) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
A.1. Base . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42

A.1.1. Core . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
A.2. Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
A.2.1. Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
A.2.2. Tiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
A.2.3. Extension Mechanism. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
A.2.4. Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Annex B: Background and Context (Normative) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
B.1. Background . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
B.2. Document terms and definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
B.3. Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
B.4. Submitting Organizations (Informative) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
B.5. Document contributor contact points (Informative) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
B.6. Revision History (Informative). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
B.7. Changes to the OGC® Abstract Specification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
B.8. Changes to OGC® Implementation Standards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
B.9. Potential Future Work (Informative) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
B.10. UML Notation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
B.11. GeoPackage Tables Detailed Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
B.12. GeoPackage Minimal Tables for Features Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
B.13. GeoPackage Minimal Tables for Tiles Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Annex C: Table Definition SQL (Normative) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
C.1. gpkg_spatial_ref_sys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
C.2. gpkg_contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
C.3. gpkg_geometry_columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
C.4. sample_feature_table (Informative) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
C.5. gpkg_tile_matrix_set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
C.6. gpkg_tile_matrix. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
C.7. sample_tile_pyramid (Informative). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
C.8. gpkg_extensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
C.9. sample_attributes_table (Informative) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Annex D: Trigger Definition SQL (Informative) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
D.1. gpkg_tile_matrix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
D.2. sample_feature_table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
D.3. sample_tile_pyramid . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Annex E: GeoPackage Extension Template (Informative) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Annex F: Registered Extensions (Normative) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
F.1. GeoPackage Non-Linear Geometry Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
F.2. User Defined Geometry Types Extension of GeoPackageBinary Geometry Encoding . . . . 113
F.3. RTree Spatial Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
F.4. Geometry Type Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
F.5. Geometry SRS ID Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120

F.6. Zoom Other Intervals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
F.7. Tiles Encoding WebP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
F.8. Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
F.9. Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
F.10. WKT for Coordinate Reference Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
F.11. Tiled Gridded Elevation Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Annex G: Geometry Types (Normative) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
Annex H: Tiles Zoom Times Two Example (Informative) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Annex I: Normative References (Normative) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Annex J: Bibliography (Informative). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Annex K: Endnotes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175

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 andraster 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 andbandwidth.

6

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

7

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 [5]database file that contains the GeoPackage data and metadata tables shown in GeoPackage Tables
Overview below.
The GeoPackage Encoding Standard (this document) describes a set of conventions for storing thefollowing within an SQLite database:• vector features• tile matrix sets of imagery and raster maps at various scales• attributes (non-spatial data)• 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 thestandard. 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 andbandwidth.Mobile device users who require map/geospatial application services and operate indisconnected 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 handheld devices [B1] [B2], given that mobile hand held devices do not yet have the processing power orbattery life to effectively tackle difficult geospatial product production and analysis tasks.
An Extended GeoPackage is a GeoPackage that contains any additional data elements (tables orcolumns) 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 andtargets. A GeoPackage MAY contain spatial indexes on feature geometries and SQL triggers tomaintain indexes and enforce content constraints.
A GeoPackage SQLite Configuration consists of the SQLite 3 software library and a set of compile-8

and runtime configurations options.
A GeoPackage SQLite Extension is a SQLite loadable extension that MAY provide SQL functions[12] to support spatial indexes and SQL triggers linked to a SQLite library with specified
configuration requirements to provide SQL API [1] [2] [3] [4] access to a GeoPackage file. This
standard does not address the issues listed in the [_potential_future_work] clause in Backgroundand Context (Normative), which MAY be addressed in a subsequent version of this standard or byother specifications.

Figure 1. GeoPackage Tables Overview

9Chapter 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 andviews 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 thosespecified. All specified table, view, column, trigger, and constraint name values SHALL belowercase.

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 integrityof 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 byany 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 withdata 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 programcrashes, operating system crashes, and power failures.
Data
File Format

Requirement 1

A GeoPackage SHALL be a SQLite [5] database file using version 3 of the SQLite file format [6][7]. The first 16 bytes of a GeoPackage SHALL be the null-terminated ASCII [B4] string "SQLite
format 3"

10[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 in integer with a major version, two-digit minor version, andtwo-digit bug-fix. For GeoPackage Version 1.2 this value is 0x000027D8 (the hexadecimal value
for 10200).

[K4]

The maximum size of a GeoPackage file is about 140TB. 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.
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.
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(Features, Tiles, and Attributes). Extended GeoPackages MAY contain additional data elements
and SQL constructs as specified through the Extension Mechanism.
The GeoPackage designation is designed to provide maximum interoperability between
applications. In an Extended GeoPackage, the extension mechanism is used to provide additional
capabilities in a way that maintains interoperability as much as possible. Developers areencouraged to consider the implications of extensions when designing their applications. Bestpractices include the following:• Designing in a way that anticipates the presence of unexpected extensions, e.g., gracefully
handling unexpected columns, values, or encodings.
• Using the RTree Spatial Indexes extension for GeoPackages containing a non-trivial amount ofvector data.
• Using the WKT for Coordinate Reference Systems extension, which is strongly recommended
due to inherent weaknesses in the original standard for encoding coordinate reference systems.

11Requirement 5

The columns of tables in a GeoPackage SHALL only be declared using one of the data types
specified in table GeoPackage Data Types. Extended GeoPackages MAY contain additional datatypes as specified through the Extension Mechanism.
Table 1. GeoPackage Data Types

Data Type

Size and Description

BOOLEANA 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_countdefines the maximum number of characters inthe string. If not specified, the length isunbounded. 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.

12

Data Type

Size and Description

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 isunbounded. The size is provided for
informational purposes. When present, it is bestpractice 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 thecore geometry types listed in Geometry Types(Normative) encoded per clause 2.1.3 or ageometry 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 date string in the form YYYY-MM-DD
encoded in either UTF-8 or UTF-16. See TEXT.
Stored as SQLite TEXT.

DATETIME

ISO-8601 date/time string in the form YYYY-MMDDTHH:MM:SS.SSSZ with T separator character
and Z suffix for coordinated universal time(UTC) encoded in either UTF-8 or UTF-16. See
TEXT. Stored as SQLite TEXT.

File Integrity

Requirement 6

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

Requirement 7

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

API
Structured Query Language (SQL)

Requirement 8

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

[K6]

13

Every GPKG SQLite Configuration

The SQLite [8] 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
Data
Table Definition

Requirement 10

A GeoPackage SHALL include a gpkg_spatial_ref_sys table per clause 1.1.2.1.1 Table Definition,
Table Spatial Ref Sys Table Definition 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 coordinate reference
systemdefinitions

itcontains

arereferenced

by

the

GeoPackage

gpkg_contents

and

gpkg_geometry_columns tables to relate the vector and tile data in user tables to locations on theearth.
The gpkg_spatial_ref_sys table includes the columns specified in SQL/MM (ISO 13249-3) [12] andshown in Spatial Ref Sys Table Definition below containing data that defines spatial reference
systems. Views of this table MAY be used to provide compatibility with the SQL/MM [12] (seeSQL/MM View of gpkg_spatial_ref_sys Definition SQL (Informative)) and OGC Simple Features SQL[9][10][11] (Table 21) standards.
Table 2. Spatial Ref Sys Table Definition

Column Name

Column Type

ColumnDescription

Null

srs_name

TEXT

Human readable
name of this SRSnosrs_idINTEGER

Unique identifier
for each Spatial
Reference System
within a
GeoPackage

no

14

Key

PK

Column Name

Column Type

ColumnDescription

Nullorganization

TEXT

Case-insensitive
name of thedefining
organization e.g.
EPSG or epsg

noorganization_coord INTEGER
sys_idNumeric ID of theSpatial Reference
System assignedby theorganizationnodefinition

TEXT

Well-known Text
[32]
Representation of
the Spatial
Reference Systemnodescription

TEXT

Human readable yes
description of this
SRS

Key

See gpkg_spatial_ref_sys Table Definition SQL.
Table Data Values

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 [32]clause 7. EBNF name and number values may be obtained from any specified authority, e.g. [13][14]. 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 Spatial Ref
Sys Table Records. The record with an srs_id of 4326 SHALL correspond to WGS-84 [15] asdefined by EPSG [B3] in 4326 [13][14]. 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 beused for undefined geographic coordinate reference systems.
Table 3. Spatial Ref Sys Table Records

srs_name

srs_idorganization

organization_c definitionoordsys_id

description

any

4326

EPSG or epsg

4326

any

any

any

-1

NONE

-1

undefined

any

any

0

NONE

0

undefinedany

15

Requirement 12

The gpkg_spatial_ref_sys table in a GeoPackage SHALL contain records to define all spatialreference systems used by features and tiles in a GeoPackage.

1.1.3. Contents
The gpkg_contents table is intended to provide a list of all geospatial contents in a GeoPackage. It
provides identifying and descriptive information that an application can display to a user as amenu of geospatial data that is available for access and/or update.
Data
Table Definition

Requirement 13

A GeoPackage file SHALL include a gpkg_contents table per table Contents Table Definition and
gpkg_contents Table Definition SQL.
Table 4. Contents Table Definition

Column Name Type

Description

Nulltable_name

TEXT

The name of
the tiles, orfeature tablenodata_type

TEXT

Type of datastored in the
table

no

identifier

TEXT

A humanreadable
identifier (e.g.
short name)for thetable_namecontent

yesdescription

TEXT

A humanyes
readable
description for
the table_namecontent''

last_change

DATETIME

timestamp ofnolast change tocontent, in ISO
8601 format

strftime('%Y%m%dT%H:%M:%fZ','now')

16

Default

Key
PK

UNIQUE

Column Name Type

Description

Null

min_x

DOUBLE

Bounding box
minimum
easting or
longitude forall content in
table_name

yes

min_y

DOUBLE

Bounding box
minimum
northing or
latitude for allcontent in
table_name

yes

max_x

DOUBLE

Bounding box
maximum
easting or
longitude forall content in
table_name

yes

max_y

DOUBLE

Bounding box
maximum
northing or
latitude for allcontent in
table_nameyes

srs_idINTEGER

Spatialyes
Reference
System ID:
gpkg_spatial_r
ef_sys.srs_id;
when data_type
is features,
SHALL also
match
gpkg_geometry_columns.srs_id;
Whendata_type istiles, SHALL
also match
gpkg_tile_matr
ix_set.srs_id

Default

KeyFK

See gpkg_contents Table Definition SQL.
Table Data Values

17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 type of content contained in the table, for example "features" per clauseFeatures, "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 waslast updated, in ISO8601 format. Note that since it is not practical to ensure that this value ismaintained 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 ISO 8601 [29] format
containing a complete date plus UTC hours, minutes, seconds and a decimal fraction of asecond, with a 'Z' ('zulu') suffix indicating UTC. The ISO8601 format is as defined by the strftime
function '%Y-%m-%dT%H:%M:%fZ' format string applied to the current time.

[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 thecontent. 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.

18Chapter 2. Options
The optional capabilities specified in this clause depend on the required capabilities specified inclause Base above. Each subclause of this clause defines an indivisible module of functionality thatcan be used in GeoPackages. These modules are referred to as options. GeoPackages MAY use one ormore options defined in this section. GeoPackages MAY omit the tables for options that are notused. 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, realworld objects such as roads and rivers, and observations thereof. International standards [9][10][11][12] have standardized practices for the storage, access and use of vector geospatial featuresand 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) [12] geometry model shown in Core Geometry Model below.

19Figure 2. Core Geometry Model

The instantiable (not abstract) geometry types defined in this Standard are restricted to 0, 1 and 2dimensional 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 havepoints 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 thecoordinate 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 thethird 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 peakby 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 themouth of stream.
A brief description of each geometry type is provided below. A more detailed description can befound in ISO 13249-3 [12].
• 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 optionallyalso have a Z and/or an M value.
• Curve: the base type for all 1-dimensional geometry types. A 1-dimensional geometry is ageometry 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 arecoincident. A simple, closed curve is called a ring.

20

• 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 ageometry that has an area.
• CurvePolygon: A planar surface defined by an exterior ring and zero or more interior ring. Eachring 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 oftype 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 oftype LineString.
• MultiPoint: A restricted form of GeometryCollection where each Geometry in the collection
must be of type Point.

2.1.2. Contents
Data
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
Data
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.

21

GeoPackage SQL Geometry Binary Format

GeoPackageBinaryHeader {byte[2] magic = 0x4750; ①
byte version;②
byte flags;③
int32 srs_id;
double[] envelope;④}
StandardGeoPackageBinary {GeoPackageBinaryHeader header; ⑤
WKBGeometry geometry;⑥}① 'GP' in ASCII② 8-bit unsigned integer, 0 = version 1③ see bit layout of GeoPackageBinary flags byte
④ see flags envelope contents indicator code below⑤ The X bit in the header flags field must be set to 0.
⑥ per OGC 06-103r4 [9][K9] [K10] [K11]

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

Types

Extensionflag bits use:
• R: reserved for future use; set to 0
• X: GeoPackageBinary type
◦ 0: StandardGeoPackageBinary. See below◦ 1:

ExtendedGeoPackageBinary.

See

User

DefinedGeometry

GeoPackageBinary Geometry Encoding.
• 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

22of◦ 5-7: invalid• B: byte order for header values (1-bit Boolean)◦ 0: Big Endian (most significant byte first)◦ 1: Little Endian (least significant byte first)
Well-Known Binary as defined in OGC 06-103r4 [9] does not provide a standardized encoding for an
empty point set (i.e., 'Point Empty' in Well-Known Text). In GeoPackages these points SHALL be
encoded as a Point where each coordinate value is 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.
When the WKBGeometry in a GeoPackageBinary is empty, either the envelope contents indicator
code SHALL be 0 indicating no envelope, or the envelope SHALL have its values set to NaN as
defined for an empty point.

2.1.4. SQL Geometry Types
Data
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,
GeomCollection) in Geometry Types (Normative) Geometry Type Codes (Core) in the
GeoPackageBinary geometry encoding format.

2.1.5. Geometry Columns
Data
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

Geometry

Columns

Table

Definition

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

23

Column NameType

Description

Null

Key

table_name

TEXT

Name of the tablecontaining thegeometry column

noPK, FK

column_name

TEXT

Name of a column noin the featuretable that is aGeometry Column

geometry_type_name TEXTName fromnoGeometry Type
Codes (Core) or
Geometry Type
Codes (Extension)in Geometry Types(Normative)

srs_idINTEGER

Spatial Reference no
System ID:
gpkg_spatial_ref_sys.srs_idz

TINYINT

0: z valuesnoprohibited; 1: zvalues mandatory;
2: z values
optionalm

TINYINT

0: m valuesnoprohibited; 1: mvalues mandatory;
2: m values
optionalPKFK

The FK on gpkg_geometry_columns.srs_id references the PK on gpkg_spatial_ref_sys.srs_id toensure 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 [12] SQL/MM View of
gpkg_geometry_columns Definition SQL (Informative) and OGC Simple Features SQL [9][10][11]SF/SQL VIEW of gpkg_geometry_columns Definition SQL (Informative) specifications.
See gpkg_geometry_columns Table Definition SQL.
Table Data Values

Requirement 22

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

24

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 fromthe gpkg_spatial_ref_sys 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
Data
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 afeature table.

[K12]

25

Requirement 29

A GeoPackage MAY contain tables or updateable views containing vector features. Every suchfeature table or view in a GeoPackage SHALL have a column with column type INTEGER and'PRIMARY KEY AUTOINCREMENT' column constraints per EXAMPLE : Sample Feature Table orView Definition and sample_feature_table Table Definition SQL (Informative).
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 30

A feature table 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 thatallow 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. EXAMPLE : Sample Feature Table or View Definition

Column Name Type

Description

Null

id

INTEGER

Autoincrement noprimary key

geometry

GEOMETRY

GeoPackage
Geometry

yes

text_attribute TEXTText attributeof feature

yes

real_attribute REAL

Real attributeof featureyesboolean_attrib BOOLEAN
ute

Booleanattribute offeatureyesraster_or_phot BLOBoPhotograph of
the areayes

See sample_feature_table Table Definition SQL (Informative).

26

Default

Key
PK

Table Data Values

A feature geometry is stored in a geometry column specified by the geometry_column value for thefeature 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 tablegeometry_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 thetype specified for the column by the gpkg_geometry_columns table geometry_type_name uppercase
column value[K13]

Geometry subtypes are assignable as defined in Geometry Types (Normative) and shown in part inCore Geometry Model. For example, if the geometry_type_name value in the gpkg_geometry_columns
table is for a geometry type like POINT that has no subtypes, then the feature table geometry
column MAY only contain geometries of that type. If the geometry type_name value in the
gpkg_geometry_columns table is for a geometry type like GEOMCOLLECTION that has subtypes, thenthe feature table geometry column MAY only contain geometries of that type or any of its direct orindirect subtypes. If the geometry type_name is GEOMETRY (the root of the geometry type hierarchy)
then the feature table geometry column MAY contain geometries of any geometry type.
The presence or absence of optional elevation (Z) and/or measure (M) values in a geometry does notchange its type or assignability. The unit of measure for optional elevation(Z) values is determinedby the CRS of the geometry; it is as-defined by a 3D CRS, and undefined for a 2D CRS. The unit ofmeasure for optional measure (M) values is determined by the CRS of the geometry.
The

spatialreferencesystem

type

of

a

featuregeometrycolumnspecifiedbyagpkg_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 thecolumn 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[K14]

that all have the same spatial extent and resolution at a particular zoom level"Tile matrix set"refers to the definition of a tile pyramid’s tiling structure. This mechanism is based on the model fortile matrix sets described in Section 6 of [16].

27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 thesame 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 ina GeoPackage in two ways. First, they specify how existing application MAY create SQL Views of thedata / 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
Data
Contents Table – Tiles Row

Requirement 34

The gpkg_contents table SHALL contain a row with a data_type column value of "tiles" for eachtile 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 ina tile matrix set that contain tiles of decreasing spatial extent and finer spatial resolution. Adjacentzoom 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 thedimensions of the real world area represented by one pixel. Pixel size MAY vary by a constantfactor or by different factors or intervals between some or all adjacent zoom levels in a tile matrix
set. In the commonly used "zoom times two" convention, pixel sizes vary by a factor of 2 between
all adjacent zoom levels, as shown in the example in [tiles_factor2_example_appendix]. 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) [16] Annex E for
additional examples of both conventions.
Data
Zoom Times Two28

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
Data
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 [17][18][19], by default SHALL store that tile data in MIME typeimage/png [20][21].

[K16]

2.2.5. Tile Encoding JPEG
Data
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 [20][21], by default SHALL store that tile data in MIME type image/jpeg[17][18][19].



[K17]

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

2.2.6. Tile Matrix Set
Data
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.

29Requirement 38

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

Column Name Column Type

ColumnDescription

Null
no

table_name

TEXT

Tile Pyramid
User Data
Table Name

srs_idINTEGER

SpatialnoReference
System ID:
gpkg_spatial_ref_sys.srs_id

min_x

DOUBLE

Bounding box
minimum
easting or
longitude forthe tile matrix
set

min_y

DOUBLE

Bounding box nominimum
northing or
latitude for thetile matrix set

max_x

DOUBLE

Bounding box
maximum
easting or
longitude forthe tile matrix
set

max_y

DOUBLE

Bounding box no
maximum
northing or
latitude for thetile matrix set

See gpkg_tile_matrix_set Table Creation SQL.
Table Data Values

30

no

no

Default

Key
PK, FKFK

Requirement 144

The bounding box defined by min_x, max_x, min_y, and max_y SHALL be exact so that thebounding box coordinates for individual tiles in a tile pyramid MAY be calculated from thosevalues. 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 allzoom 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 inthat 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[K18]

gpkg_contents table_name column for rows with a data type of "tiles".

Requirement 40

The gpkg_tile_matrix_set table SHALL contain one row record for each tile pyramid user datatable.
Requirement 41

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

2.2.7. Tile Matrix
Data
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 Tile Matrix Metadata Table Definition and
Table gpkg_tile_matrix Table Creation SQL.
Table 9. Tile Matrix Metadata Table Definition

Column Name

Column Type

ColumnDescription

Nulltable_name

TEXT

Tile Pyramid User no
Data Table NameKey
PK, FK31

Column Name

Column Type

ColumnDescription

Nullzoom_level

INTEGER0 <= zoom_level <= nomax_level fortable_namematrix_width

INTEGER

Number ofcolumns (>= 1) intile matrix at thiszoom levelnomatrix_height

INTEGER

Number of rows(>= 1) in tile
matrix at thiszoom levelnotile_width

INTEGER

Tile width inpixels (>= 1)for
this zoom levelnotile_height

INTEGER

Tile height inpixels (>= 1) for
this zoom levelnopixel_x_size

DOUBLEIn t_table_namenosrid units ordefault meters forsrid 0 (>0)

pixel_y_size

DOUBLEIn t_table_namenosrid units ordefault meters forsrid 0 (>0)

Key
PK

The gpkg_tile_matrix table documents the structure of the tile matrix at each zoom level in eachtiles 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
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.

32

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(thedifference

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.
The gpkg_tile_matrix table MAY contain row records for zoom levels in a tile pyramid user datatable that do not contain tiles.
GeoPackages follow the most frequently used conventions of a tile origin at the upper left and azoom-out-level of 0 for the smallest map scale "whole world" zoom level view

[K20]

, as specified by

WMTS [16]. The tile coordinate (0,0) always refers to the tile in the upper left corner of the tile
matrix at any zoom level, regardless of the actual availability of that tile.
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.

33

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 meansthat 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/ycolumns values in the gpkg_contents record for the same table_name, the exact spatial extent statedby 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
Data
Table Definition

Requirement 54

Each tile matrix set in a GeoPackage SHALL be stored in a different tile pyramid user datatable or updateable view with a unique name that SHALL have a column named "id" withcolumn type INTEGER and 'PRIMARY KEY AUTOINCREMENT' column constraints per Clause
2.2.8.1.1 Table Definition, Tiles Table or View Definition and EXAMPLE: tiles table Create Table
SQL (Informative).
Table 10. Tiles Table or View Definition

Column Name Column Type

ColumnDescription

id

INTEGER

Autoincrement noprimary key

PKzoom_level

INTEGERmin(zoom_leve nol) <= zoom_level<=max(zoom_level) fort_table_nameUK

tile_column

INTEGER

0 to
tile_matrix
matrix_width –
1

no

UK

tile_row

INTEGER

0 to
notile_matrix
matrix_height 1

UK

34

Null

Default

KeyColumn Name Column Type
tile_data

BLOB

ColumnDescription

Null

Default

Key

Of an image
noMIME typespecified inclauses Tile
Encoding PNG,
Tile Encoding
JPEG,[tile_enc_webp]

See EXAMPLE: tiles table Create Table SQL (Informative).
Table Data Values

Each tile pyramid user data table or view

[K24]

MAY contain tile matrices at zero or more zoom levelsof 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 areequal.
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.

35

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, andother 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 cycleof 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 discoveredby scanning a single table) and uniformity (declaring that an extension is in use indicates that adefined set of requirements are being met). However, this is a decision that should be madecarefully 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 ofinterest MAY waive that requirement in its own GeoPackage profile, with the caveat that it mustbear 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 thatminimizes 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 toimplementation by multiple implementers.
GeoPackage extensions are documented using the GeoPackage Extension Template in GeoPackage
Extension

Template(Informative).

Extensions

areidentified

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
Data
Table Definition

Requirement 58

A GeoPackage MAY contain a table or updateable view named gpkg_extensions. If present thistable SHALL be defined per clause 2.3.2.1.1 Table Definition, GeoPackage Extensions Table orView Definition (Table or View Name: gpkg_extensions), 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 or updateable view in a GeoPackage is used to indicate that a particular36

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 thecontents of all the user data tables to determine if it has the required capabilities to read or write totables with extensions, and to "fail fast" and return an error message if it does not.
Table 11. GeoPackage Extensions Table or View Definition (Table or View Name: gpkg_extensions)

Column Name

Col Type

ColumnDescription

Null

Key

table_name

TEXT

Name of the tablethat requires theextension. When
NULL, theextension isrequired for theentireGeoPackage.
SHALL NOT be
NULL when thecolumn_name is
not NULL.

yes

Jointly Uniquecolumn_name

TEXT

Name of thecolumn that
requires theextension. When
NULL, theextension isrequired for theentire table.

yes

Jointly Uniqueextension_name

TEXT

The case sensitive noname of theextension that isrequired, in theform<author>_<extensi
on_name>.

Jointly Uniquedefinition

TEXT

Permalink, URI, or noreference to a
document thatdefines theextension

scope

TEXT

Indicates scope of noextension effects
on readers /writers: 'readwrite' or 'writeonly' in lowercase.

See gpkg_extensions Table Definition SQL.

37

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 ofexisting 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 havethe same case. When searching for table name references, it is recommended totransform 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 thetable 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 ororganization 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 maintainedby OGC. GeoPackage implementers use their own author names to register other extensions.

38Requirement 63

The definition column value in a gpkg_extensions row SHALL contain a permalink, URI [23], orreference 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 sets (or 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 relationallylinkable 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

39

Data
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
Data
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 tocontain any Attribute data tables. Attribute data tables in a GeoPackage may be empty.)
Requirement 119

A GeoPackage MAY contain tables or updatable views containing attribute sets. Every such
Attribute table or view in a GeoPackage SHALL have a column with column type INTEGER and
PRIMARY KEY AUTOINCREMENT column constraints per GeoPackage Attributes Example
Table or View Definition and EXAMPLE: Attributes table Create Table SQL (Informative).
The integer primary key of an Attribute table allows attribute sets to be linked to row level
metadata records in the gpkg_metadata table by rowid [B5] values in the gpkg_metadata_referencetable as described in clause Metadata below.
Table 12. GeoPackage Attributes Example Table or View Definition

Column Name

Col Type

ColumnDescription

Null

Key

id

INTEGER

Autoincrement
primary key

noPK

text_attribute

TEXT

Text attribute offeatureyes

real_attribute

REAL

Real attribute offeatureyesboolean_attribute

BOOLEAN

Boolean attributeof featureyesraster_or_photo

BLOB

Photo of the area

yes

40

Chapter 3. Security Considerations
Security considerations for implementations utilizing GeoPackages are in the domain of theimplementing application, deployment platform, operating system and networking environment.
The GeoPackage standard does not place any constraints on application, platform, operating systemlevel or network security.
Since GeoPackage is dependent on SQLite, implementors should monitor for security alerts related
to SQLite and respond accordingly.

41

Annex A: Conformance / Abstract Test Suite(Normative)
A.1. Base
A.1.1. Core
SQLite Container
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.0

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 ASCIIa. PRAGMA user_version
b. Fail if the integer representation of the user_version string is less than
10200.
5. Fail if the string is not one of those values

Reference

Clause 1.1.1.1.1 Req 2:

Test Type

Basic

42

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/file_contents

Test Purpose

Verify that the GeoPackage only contains specified contents

Test Method

1. SELECT COUNT(*) from gpkg_extensions;
2. Not testable if table exists and count > 0
3. For each gpkg_* table_namea. PRAGMA table_info(table_name)b. Continue if returns an empty result set
c. Fail if column definitions returned by "PRAGMA table_info" do not matchcolumn definitions for the table in Annex C.
4. Pass if no fails

Reference

Clause 1.1.1.1.3 Req 4:

Test Type

Basic

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 GeoPackage Data Types.

43Test Method

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

WHERE

data_typeIN

2. Not testable if returns empty set
3. For each row table name from step 1
a. PRAGMA table_info(table_name)b. For each row type column value
i. Fail if value is not one of the data type names specified by GeoPackage
Data Types
4. Pass if no fails
Reference

GeoPackage Data Types 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 TypeCapability

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 TypeCapability

API

Structured Query Language

44

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 TypeCapability

Spatial Reference Systems
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, andprimary 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.

45

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("epsg","EPSG") AND organization_coordsys_id 4326

organizationIN

a. 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 TypeCapability

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 TypeCapability

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

46

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 CREATETABLE 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 TypeCapability

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 containing a complete date plus UTC hours, minutes, seconds and adecimal fraction of a second, with a 'Z' ("zulu") suffix indicating UTC.

47

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
a. Fail

ifformatofreturned

value

does

not

match

yyyy-mm-columnvaluesreference

ddThh:mm:ss.hhhZ
4. Pass if no fails.
Reference

Clause 1.1.3.1.2 Req 15:

Test TypeCapability

Test Case ID/base/core/contents/data/data_values_srs_id

Test Purpose

Verifythatthegpkg_contents

table

srs_idgpkg_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 TypeCapability

A.2. Options
Test Case ID/opt/valid_geopackage

Test Purpose

Verify that a GeoPackage contains a features or tiles table and gpkg_contents tablerow 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 TypeCapability

48

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 *.
Simple Features SQL Introduction
Contents
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 afeature 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 TypeCapability

Geometry Encoding
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.

49

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
a. SELECT cn FROM tn
b. Not testable if none foundc. For each cn value from step ai. Fail if the first two bytes of each gc are not "GP"ii. Fail if gc.version_number is not 0iii. Fail if gc.flags.GeopackageBinary type != 0
iv. Fail if cn.flags.E is 5-7
v. *Fail if the geometry is empty but the envelope is not empty(gc.flags.envelope != 0 and envelope values are not NaN)
4. Pass if no fails
Reference

Clause 2.1.3.1.1 Req 19:

Test TypeCapability

SQL Geometry Types
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.

50

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
a. SELECT cn FROM tn;
b. 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, theni. Log cn.header values, wkb endianness and geometry typeii. *If cn.wkb is not correctly encoded per ISO 13249-3 clause 5.1.46 thenlog failiii. Otherwise log pass
6. Pass if log contanins pass and no fails
Reference

Clause 2.1.4.1.1 Req 20:

Test TypeCapability

Geometry Columns
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 Geometry Columns Table Definition 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:

51

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' ANDtable_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 TypeCapability

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

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 TypeCapability

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

52

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
a. PRAGMA table_info(table_name)b. Fail if gpkg_geometry_columns.column_name value does not equal a namecolumn value returned by PRAGMA table_info.
4. Pass if no fails.

Reference

Clause 2.1.5.1.2 Req 24:

Test TypeCapability

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
a. Fail if a returned geometry_type_name value is not in Table 31 in Annex G
4. Pass if no fails.

Reference

Clause 2.1.5.1.2 Req 25:

Test TypeCapability

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 TypeCapability

53

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 TypeCapability

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 TypeCapability

Vector Features User Data Tables
Data

Table Definition
Test Case ID/opt/features/vector_features/data/feature_table_integer_primary_key

Test Purpose

Verify that every vector features user data table has an integer primary key.

54

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
a. PRAGMA table_info(table_name)b. Fail if returns an empty result set
c. Fail if result set does not contain one row where the pk column value is 1
and the not null column value is 1 and the type column value is "INTEGER"
d. SELECT COUNT(*) - COUNT(DISTINCT id) from table_namee. Fail if result is nonzero
4. Pass if no fails.

Reference

Clause 2.1.6.1.1 Req 29:

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
a. SELECT column_name from gpkg_geometry_columns where table_name =row table nameb. Fail if returns more than one column name
4. Pass if no fails

Reference

Clause 2.1.6.1.1 Req 30:

Test TypeCapability

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 theuppercase geometry type name from Annex G specified by the geometry_type_namecolumn for that column_name and table_name in the gpkg_geometry_columns table.

55

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):a. PRAGMA table_info('{selected table_name}')b. Fail if declared type of column_name selected in (1) is not thegeometry_type_name selected in (1)3. Pass if no fails

Reference

Clause 2.1.6.1.1 Req 31:

Test TypeCapability

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 or are
assignable for the geometry type specified by the gpkg_geometry columns tablegeometry_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
a. *Select the set of geometry types in use for the values in cn
b. For each row actual_type_name from step ai. *Determine

if

each

geometry

type

isassignableactual_type_nameii. Fail if any are not assignable
4. Pass if no fails
Reference

Clause 2.1.6.1.2 Req 32:

Test TypeCapability

Test Case ID/opt/features/vector_features/data/data_value_geometry_srs_id

56

to

the

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
a. *Select the set of SRIDs in use for the values in cn
b. For each row from step ai. *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 TypeCapability

A.2.2. Tiles
Contents
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 TypeCapability

Zoom Levels
Data

Zoom Times Two57

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
a. SELECT zoom_level, pixel_x_size, pixel_y_size FROM gpkg_tile_matrix
WHERE table_name = selected table name ORDER BY zoom_level ASC
b. Not testable if returns empty result set, or only one row
c. Not testable if there are not two rows with adjacent zoom levels
d. 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 TypeCapability

Tile Encoding PNG
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".

58

Test Method

1. SELECT table_name AS tn FROM gpkg_contents WHERE data_type = \'tiles'
2. For each row tbl_name from step 1
a. 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;
i. Not testable unless it returns empty result set
b. SELECT tile_data FROM tn
c. For each row tile_data from step ai. Pass if tile data in MIME type image/jpeg
ii. Pass if tile data in MIME type image/png
iii. Fail if no passes

Reference

Clause 2.2.4.1.1 Req 36:

Test TypeCapability

Tile Encoding JPEG
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".

59

Test Method

1. SELECT table_name AS tn FROM gpkg_contents WHERE data_type = 'tiles'
2. For each row tbl_name from step 1
a. 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;
i. Not testable unless it returns empty result set
b. SELECT tile_data FROM tn
c. For each row tile_data from step ai. Pass if tile data in MIME type image/jpeg
ii. Pass if tile data in MIME type image/png
iii. Fail if no passes

Reference

Clause 2.2.5.1.1 Req 37:

Test TypeCapability

Tile Matrix Set
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 CREATETABLE 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, andother column definitions in the returned sql are irrelevant.
4. Fail otherwise.

Reference

Clause 2.2.6.1.1 Req 38:

Test TypeCapability

60

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 TypeCapability

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 tilepyramid 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
a. SELECT sql FROM sqlite_master WHERE type='table' AND tbl_name ='<user_data_tiles_table>'b. Fail if returns an empty result set
4. Pass if no fails

Reference

Clause 2.2.6.1.2 Req 40:

Test TypeCapability

Test Case ID/opt/tiles/gpkg_tile_matrix_set/data/data_values_srs_id61

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 TypeCapability

Tile Matrix
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 CREATETABLE statement in the sql column value, including data type, nullability,
default values, primary, and foreign key constraints match all of those in thecontents 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.

62

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 TypeCapability

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 levelthat 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
a. 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>'b. 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 TypeCapability

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 ofthe gpkg_tile_matrix table.

63

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
a. SELECT max_x - min_x from gpkg_tile_matrix_set where table_name ='<user_data_tiles_table>'b. SELECT zoom_level, matrix_width * tile_width * pixel_x_size from
gpkg_tile_matrix where table_name = '<user_data_tiles_table>'c. SELECT max_y - min_y from gpkg_tile_matrix_set where table_name ='<user_data_tiles_table>'d. SELECT zoom_level, matrix_height * tile_height * pixel_y_size from
gpkg_tile_matrix where table_name = '<user_data_tiles_table>'e. 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 TypeCapability

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

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 TypeCapability

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.

64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 TypeCapability

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 TypeCapability

65

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 TypeCapability

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 TypeCapability

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.

66

Reference

Clause 2.2.7.1.2 Req 52:

Test TypeCapability

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 indescending 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
a. SELECT zoom_level, pixel_x_size, pixel_y_size from gpkg_tile_matrix
WHERE table_name = row table name ORDER BY zoom_level ASC
b. Not testable if returns empty result set
c. Fail if pixel_x_sizes are not sorted in descending orderd. 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 TypeCapability

Tile Pyramid User Data
Data

Table Definition
Test Case ID/opt/tiles/tile_pyramid/data/table_def

Test Purpose

Verify that multiple tile pyramids are stored in different tiles tables with unique
names containing the required columns.

67

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
a. PRAGMA table_info(table_name)b. Fail if returns an empty result set
c. Fail if result set does not contain one row where the pk column value is 1
and the not null column value is 1 and the type column value is"INTEGER"and the name column value is "id"d. 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. 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 arewithin the range of zoom levels defined by rows in the gpkg_tile_matrix table.

68Test Method

1. SELECTDISTINCT

table_nameAS<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
a. SELECT zoom_level FROM <user_data_tiles_table>b. If result set not empty
i. SELECTMIN(gtmm.zoom_level)ASmin_gtmm_zoom,

MAX(gtmm.zoom_level) AS max_gtmm_zoom FROM gpkg_tile_matrix
WHERE table_name = <user_data_tiles_table>
ii. SELECT id FROM <user_data_tiles_table> WHERE zoom_level <min_gtmm_zoomiii. Fail if result set not empty
iv. SELECT id FROM <user_data_tiles_table> WHERE zoom_level >max_gtmm_zoom
v. Fail if result set not empty
vi. Log pass otherwise
4. Pass if logged pas and no fails
Reference

Clause 2.2.8.1.2 Req 55:

Test TypeCapability

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 tilepyramid user data table are within the range of columns defined by rows in the
gpkg_tile_matrix table.

69

Test Method

1. SELECTDISTINCT

table_nameAS<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
a. SELECT DISTINCT gtmm.zoom_level AS gtmm_zoom, gtmm.matrix_widthAS

gtmm_width,

udt_column

udt.zoom_level

FROM

AS

gpkg_tile_matrix

udt_zoom,

udt.tile_column

AS

LEFT

gtmm

OUTER

AS
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))
b. Fail if any udt_column value in the result set is not NULL
c. Log pass otherwise
4. Pass if logged pass and no fails
Reference

Clause 2.2.8.1.2 Req 56:

Test TypeCapability

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 tilepyramid user data table are within the range of rows defined by rows in the
gpkg_tile_matrix table.

Test Method

1. SELECTDISTINCT

table_nameAS<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
a. SELECT DISTINCT gtmm.zoom_level AS gtmm_zoom, gtmm.matrix_heightAS 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))
b. Fail if any udt_row value in the result set is not NULL
c. Log pass otherwise
4. Pass if logged pass and no fails
Reference

70

Clause 2.2.8.1.2 Req 57:

Test TypeCapability

A.2.3. Extension Mechanism
Extensions
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 andtrigger 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 TypeCapability

71

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)AStable_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 onea. "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_masterAS sm ON lower(ge.table_name) = lower(sm.tbl_name);b. 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 TypeCapability

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
a. SELECT count(column_name) FROM table_namei. Fail if query is invalid, suggesting an invalid column nameb. Log pass otherwise
4. Pass if logged pass and no fails.
Reference

Clause 2.3.2.1.2 Req 61:

Test TypeCapability

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

72

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
a. Log pass if extension_name is one of those listed in Annex F.
b. Separate extension_name into <author> and <extension> at the first "_"c. Fail if <author> is "gpkg"
d. Fail if <author> contains characters other than [a-zA-Z0-9]
e. Fail if <extension> contains characters other than [a-zA-Z0-9_]
f. Log pass otherwise
4. Pass if logged pass and no fails.

Reference

Clause 2.3.2.1.2 Req 62:

Test TypeCapability

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
a. Inspect if definition value is not like "Annex %", or "http%" or mailto:% or"Extension Title%"b. 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 TypeCapability

Test Case ID/opt/extension_mechanism/data/data_values_scope

Test Purpose

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

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
a. 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 TypeCapability

A.2.4. Attributes
Contents
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 empty result set
3. For each row from step 1
a. PRAGMA table_info(table_name)b. Fail if returns an empty result set
c. Fail if result set does not contain one row where the pk column value is 1
and the notnull column value is 1 and the type column value is "INTEGER"
and the name column value is "id"4. Pass if no fails.

Reference

Clause 2.4.2.1.1 Req 64a

Reference

Clause 2.4.3.1.1 Req 64b

Test TypeCapability

74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 ofgeospatial 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 suchapplications 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 thattogether 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 [9][10][11] and SQL-MM (Spatial)[12] 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, andmetadata from widely adopted implementations and existing standards such as WMTS [16] and ISO
metadata [28], 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. Futureenhancements 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 be75

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 filea platform-independent SQLite database file that contains GeoPackage data and metadata tables
with specified definitions, integrity assertions, format limitations and content constraints.
GeoPackage SQLite Configurationconsists 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 begeolocated 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, haveconstant 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 graphicalstyling, which can be uniquely defined by a pair of indices for the column and row along with an
identifier for the tile matrix.
tile matrix

76

a collection of tiles for a fixed scale
tile pyramida 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 ModelGPKG
GeoPackage
GRD
Ground Resolved Distance
EPSG
European Petroleum Survey GroupFK
Foreign Key
IETF

77

Internet Engineering Task ForceIIRS
Image Interpretability Rating Scale
IRARS
Imagery Resolution Assessments and Reporting Standards (Committee)
ISO
International Organization for Standardization
JDBCJava 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
PNGPortable 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
UTC78

Coordinated Universal TimeXML
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

Envitiaroger.brackin<at>envitia.com

Chris Clark

Compusultchrisc<at>compusult.net

Scott Clark

LNM Solutions

scott.clark<at>lmnsolutions.com

79

Name

Organization

EmailDavid Cray

U.S. Army Geospatial Center GASDDavid.Cray<at>usace.army.mil

Paul Daisey

Image Matterspauld<at>imagemattersllc.com

Nathan P. Frantz

U.S. Army Geospatial Center ERDCNathan.P.Frantz<at>usace.army.mil

Alessandro Furieri Spatialite

a.furieri<at>lqt.it

Randy Gladish

Image Matters

randyg<at>imagemattersllc.com

Eric Gundersen

MapBoxeric<at>mapbox.com

Brad Hards

Sigma Bravo

bhards<at>sigmabravo.com

Jeff Harrison

The Carbon Projectjharrison<at>thecarbonproject.com

Chris Holmes

OpenGeocholmes<at>9eo.org

Frederic Houbie

Luciadfrederic.houbie<at>luciad.com

Sean Hogan

Compusultsean<at>compusult.net

Kirk Jensen

Image Matters

kirkj<at>imagemattersllc.com(chinese chars not Feng China University
working) Joshua

joshua<at>gis.tw

Terry A. Idol

U.S. National Geospatial Intelligence
AgencyTerry.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

MapBoxtom<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 Feng China University
working) Yi-Min
Huang

80

niner<at>gis.twName

Organization

Email

Andrea Peri

Regione Toscana Italy

andrea.peri<at>regione.toscana.it

Paul Ramsey

OpenGeopramsey<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 ResearchInstitutekryden<at>esri.com

Scott Simmons

CACI

scsimmons<at>caci.com

Ingo SimonisInternational Geospatial Services
Instituteingo.simonis<at>igsi.eu

Raj SinghOpen 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
Tuckertucker<at>mapstory.org

Mapstory

Benjamin T. Tuttle U.S. National Geospatial Intelligence
AgencyBenjamin.T.Tuttle<at>nga.mil

Pepijn Van
Eeckhoudt

Luciadpepijn.vaneeckhoudt<at>luciad.com

David G. Wesloh

U.S. National Geospatial Intelligence
AgencyDavid.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

EditorParagraphmodified

Description

2014-07-14

R11Paul Daisey

1.1.2.1.1

Remove"at"includes"

in

a
2nd

minimum"paragraph,

after
1st

sentence; conflicts with Clause 1

81

Date

Rel

EditorParagraphmodified

Description

2014-07-14

R11Paul Daisey

2.1.4.1.1

Req 20 42in insert space between 42
and in2014-07-14

R11Paul Daisey

2.2.8.1.1

Change Table 30 to Table 29

2014-07-14

R11Paul Daisey

2.5.2.1.1

Changeaccesstoaccessesin

1stparagraph, 2nd sentence
2014-07-14

2014-07-14

2014-07-14

R11

R11

R11

Paul Daisey

Paul Daisey

Paul Daisey

Annex A,
A.3.1.1.1

inAnnex E insert space between in andAnnex A,
A.3.1.1.1

43without insert space between 43 andAnnex B, B.5

ChangeAnnex

without
pepijn.vaneeckhoudt

emailfrom gmail to Luciad
2014-07-14

R11Paul Daisey

Annex B, B.5

Change all "@" to "<at>"2014-07-14

R11Paul Daisey

Annex C, C.10

Remove UNIQUE from PK constraint

2014-12-12

R11Paul Daisey

2.1.1

Add a footnote to "GeometryCollection"
description

2014-12-12

R11Paul Daisey

2.1.6.1.1

Add new Req 30b

2014-12-12

R11Paul Daisey

Annex A,
A.2.1.6.1.1

Addfeature_table_geometry_column_type
test2014-12-12

R11Paul Daisey

2.1.5.1.1

Add

"and

geometry

types"

to1stparagraph 1st sentence
2014-12-12

R11Paul Daisey

1.1.1.1.4

Add footnote to Req 6

2014-12-12

R11Paul Daisey

2.1.3.1.1

replace ISO 13249-3 with OGC 06-103r4

2014-12-12

R11Paul Daisey

2.1.3.1.1

Correct references in footnote 1: [13]becomes [9] and [16] becomes [12]2014-12-12

R11Paul Daisey

1.1.1.2.2

Remove "and run" from clause and Req
9

82

Date

Rel

EditorParagraphmodified

Description

2014-12-12

R11Paul Daisey

1.1.1.2.2

Remove PRAGMA foreign_keys runtime
option from Table 2

2014-12-12

2014-12-12

R11

R11

Paul Daisey

Paul Daisey

Annex A,
A.2.2.7.1.1

Change step 2 to "Not testable if lessAnnex A,
A.2.2.6.1.1

Remove obsolete provisions (unique,than 1"column order, other columns) from step3

2014-12-12

R11Paul Daisey

2.1.6.1.2

Add

sentences

specifying

unit

of

measure determination for geometry Z
and M values.
2014-12-12

2014-12-12

2014-12-12

R11

R11

R11

Paul Daisey

Paul Daisey

Paul Daisey

1.1.3.1.1, Table
4

ChangeAnnex C, C.7,
Table 29

Remove spurious ")" from tile_data

Annex D, D.3,
Table 39

Correct

description

columndefault

value to ''column definition
ISO

8601

timestamp

GLOB

expressions

2015-03-16

R11

Scott Simmons entire
document

Minor format corrections

2015-04-27

R11

Joe Brumleyentiredocument

Minor

formatcorrectionsfor

corrigendum

2015-06-10

R12

Brad Hards

2.2.1

Fix typos

2015-06-17

R12

Jeff Yutzler

2.2.6.1.1

Clarify role of bounding box in Tile
MatrixSet

table

https://github.com/

opengeospatial/geopackage/issues/102
2015-06-17

R12

Jeff Yutzler

2.2.7.1.2

Add new Req 44b https://github.com/
opengeospatial/geopackage/issues/102

2015-06-24

R12

Jeff Yutzler

1.1.2.1.2

ClarifyReq

11

https://github.com/

opengeospatial/geopackage/issues/110
2015-08-04

R12

Jeff Yutzler

Figures 4, 5, 6

Correct multiplicity https://github.com/
opengeospatial/geopackage/issues/117

83

Date

Rel

EditorParagraphmodified

Description

2015-08-04

R12

Jeff Yutzler

Annex I

Update Footnote #18 (JFIF) to T.871
https://github.com/opengeospatial/
geopackage/issues/104

2015-08-25

R12

Jeff Yutzler

Wholedocument

Removerevisionmarkuphttps://github.com/opengeospatial/
geopackage/issues/135

2015-08-25

R12

Jeff Yutzler

Annex B, B.6

Remove old changes https://github.com/
opengeospatial/geopackage/issues/133

2015-08-26

R12

Jeff Yutzler

Annex B, B.6

FixcasingofmaxIsInclusive,

minIsInclusive,anddescription

https://github.com/opengeospatial/
geopackage/issues/130
2015-08-25

R12

Jeff Yutzler

Annex B, B.6

Clarify rules for case sensitivity forviews,

triggers,

constraints

https://github.com/opengeospatial/
geopackage/issues/131
2015-09-09

R12

Brad Hards

Intro

Fix typos

2015-09-29

R12

Brad Hards

1.1.3.1.1

Fix typos

2015-09-29

R12

Jeff Yutzler

1.1.3.1.1

User-defined tables do not need to havelowercasecolumnnames

https://github.com/opengeospatial/
geopackage/issues/144
2015-10-19

R12Jeff Yutzler

Annex F, F.4

Fix

typos

https://github.com/

opengeospatial/geopackage/issues/152
2015-10-29

R12

Brad Hards

1.1.3.1.1

Fix typos

2015-11-09

R12

Jeff Yutzler

Multiple

Fix typos

2015-11-19

R12Jeff Yutzler

Multiple

Numerous

administrative

https://github.com/opengeospatial/
geopackage/issues/160

84

edits

Date

Rel

EditorParagraphmodified

Description

2015-11-26

R12Jeff Yutzler

Annex F, F.9

Define

GLOB

https://github.com/

opengeospatial/geopackage/issues/156
2015-11-26

R12Jeff Yutzler

Annex F

Collapse all extensions into a single
annex

https://github.com/

opengeospatial/geopackage/issues/132
2015-11-26

R12Jeff Yutzler

Annex F, F.8,
F.9

Demote Metadata and Schema sections
toextensions

https://github.com/

opengeospatial/geopackage/issues/147
2015-11-26

R12Jeff Yutzler

Annex E

Updateextension

template

https://github.com/opengeospatial/
geopackage/issues/165
2015-11-26

R12Jeff Yutzler

Annex F, F.10

Create new extension for CRS WKThttps://github.com/opengeospatial/
geopackage/issues/137

2015-12-02

2015-12-04

R12

R12

Jeff Yutzler

Jeff Yutzler

Annex A,
Annex F

Clean ATS references https://github.com/

2.3.2.1.2

Update rules for listing extensions

opengeospatial/geopackage/issues/169

https://github.com/opengeospatial/
geopackage/issues/175
2015-12-04

R12Jeff Yutzler

All Annexes

Scrub

annex

references

https://github.com/opengeospatial/
geopackage/issues/176
2015-12-26

R12Brad Hards

2.3.2.1

Fix typos

2015-12-26

R12Jeff Yutzler

Annex F.9

Make "identifier" unique in Table 42,
Table

45

https://github.com/

opengeospatial/geopackage/issues/183
2015-12-28

R12Jeff Yutzler

Annex A.1.1.2

Fix column names in ATS tests

2016-01-20

R12

Jeff Yutzler

1.1.1.1.1

Bump

versionnumber

https://github.com/opengeospatial/
geopackage/issues/188

85

Date

Rel

EditorParagraphmodified

Description

2016-05-08

R13

Jeff Yutzler

1.1.1.1.1

Clarify that GeoPackage header is nullterminatedhttps://github.com/

opengeospatial/geopackage/pull/209
2016-05-17

R13

Jeff Yutzler

Annex B.11-13

UpdateUML

diagrams

DATETIME

toshow

https://github.com/

opengeospatial/geopackage/issues/214
2016-07-12

R13

Jeff Yutzler

1.1.3

Rewrite description of gpkg_contents
https://github.com/opengeospatial/
geopackage/issues/221

2016-07-18

R13

Frederic
HoubieAnnex B.2

Add

new

terms

and

definitions

https://github.com/opengeospatial/
geopackage/issues/213

2016-08-02

R13

Chris Clark

Annex F.9

Correct

wording

in

Table

42

https://github.com/opengeospatial/
geopackage/issues/231
2016-08-02

R13

Chris Clark

Annex F.10

Correct column name for CRS WKTextension

https://github.com/

opengeospatial/geopackage/issues/258
2016-08-23

R13

Jeff Yutzler

Annex F.1

Deprecate

Requirement69

https://github.com/opengeospatial/
geopackage/issues/234
2016-08-23

R13

Jeff Yutzler

Annex F.2,4,5Deprecateextensions

F.2,

F.4,

F.5

https://github.com/opengeospatial/
geopackage/issues/235
2016-08-23

R13

Chris Clark

2.2.7.1.2

RewriteRequirement53

https://github.com/opengeospatial/
geopackage/issues/237
2016-08-23

R13

Chris Clark

Multiple

Fix

Typos

https://github.com/

opengeospatial/geopackage/issues/238
2016-09-06

R13

Chris Clark

Annex F.8

Simplify

triggers

to

avoid

failureshttps://github.com/opengeospatial/
geopackage/issues/228

86

Date

Rel

EditorParagraphmodified

Description

2016-09-13

R13

Jeff Yutzler

Annex F.8

Add

default

to

SQL

DDL

https://github.com/opengeospatial/
geopackage/issues/243
2016-09-20

R13

Jeff Yutzler

2.3.2.1.1

Updatedescription

columnfor"definition"

https://github.com/

opengeospatial/geopackage/issues/241
2016-10-04

R13

Jeff Yutzler

2.4

Adding "attributes" section to describeuseofnon-spatial

data

https://github.com/opengeospatial/
geopackage/issues/221
2016-10-17

R13

Jeff Yutzler

Multiple

Fix

typos

https://github.com/

opengeospatial/geopackage/issues/249
2016-10-17

R13

Jeff Yutzler

1.1.1.1.1, Annex Allow for version increments in SQLite
Aheader
https://github.com/
opengeospatial/geopackage/issues/255

2016-10-17

R13

Jeff Yutzler

1.1.1.1.2

Eliminate.gpkx

optionhttps://github.com/opengeospatial/
geopackage/issues/261
2016-10-17

R13

Jeff Yutzler

Annex F.9

Correct

min/max_is_inclusive

abstract

tests

in

https://github.com/

opengeospatial/geopackage/issues/260
2016-10-19

R13

Brian Osborn

1.1.1.1.1

Update application ID

2016-10-19

R13

Jeff Yutzler

Annex F.11

Add elevation extension to standard
https://github.com/opengeospatial/
geopackage/issues/242

2016-10-20

R13

Jeff Yutzler

2.3.2.1.1

Add to Requirement 58 to clarify rules
for extensions

2016-10-24

R13

Jake Molnar

2.2.6.1.2

Fix typo

2016-12-07

R13

Jeff Yutzler

1.1.1.1.1

Rewrite Requirement 2 to use GPKG
application_id + user_version

87

Date

Rel

EditorParagraphmodified

Description

2017-02-27

R14

Jeff Yutzler

Annex A.1.1.1.1 Rewrite

ATS

for

Requirement5

https://github.com/opengeospatial/
geopackage/issues/279
2017-02-27

R14

Jeff Yutzler

Annex A.2.1.6.1 Rewrite

ATS

for

Requirement29

https://github.com/opengeospatial/
geopackage/issues/282
2017-02-27

R14

Jeff Yutzler

Annex A.2.1.3.1 Rewrite ATS for Requirements 19 and20(geometry

BLOB

format)https://github.com/opengeospatial/
geopackage/issues/285
2017-02-27

R14

Jeff Yutzler

2.1.5.1.1, Annex Add "null" column to Table 6, Rewrite
A.2.1.5.1
ATSfor
Requirements
21,
23
https://github.com/opengeospatial/
geopackage/issues/286

2017-02-27

R14

Jeff Yutzler

Annex A

Removing

explicit

dependencies

Spatialiteinhttps://github.com/

ATS

on

opengeospatial/geopackage/issues/288
2017-02-27

R14

Jeff Yutzler

2.2

Removing'tiles'Requirements

stipulation
39

from

and

43

https://github.com/opengeospatial/
geopackage/issues/295
2017-03-02

R14

Jeff Yutzler

Multiple

Varioustypographicalissueshttps://github.com/opengeospatial/
geopackage/issues/290
2017-03-06

R14

Jeff Yutzler

1.1.1.2.2

Removing

Requirement9

https://github.com/opengeospatial/
geopackage/issues/309
2017-03-13

R14

Jeff Yutzler

Annex A.1.1.2.1 Rewrite

ATS

for

Requirement11

https://github.com/opengeospatial/
geopackage/issues/300
2017-03-13

R14

Jeff Yutzler

Annex A

Rewrite ATS for Requirements 2, 12, 14,15,17https://github.com/

opengeospatial/geopackage/issues/308

88

Date

Rel

EditorParagraphmodified

Description

2017-03-17

R14

Jeff Yutzler

Multiple

Switchingtonumbers

fixed

(not

requirements
auto-generated)

https://github.com/opengeospatial/
geopackage/issues/314
2017-03-17

R14

Jeff Yutzler

Annex F.10

Rewording

Requirement

115

https://github.com/opengeospatial/
geopackage/issues/258
2017-03-21

R14

Jeff Yutzler

Multiple

Scrubbing document for inappropriate
useof"view"

https://github.com/

opengeospatial/geopackage/issues/301
2017-03-22

R14

Jeff Yutzler

Multiple

Varioustypographicalissueshttps://github.com/opengeospatial/
geopackage/issues/290
2017-04-17

R14

Jeff Yutzler

Annex F.1

Clean up Non-Linear Geometry Types
extension

https://github.com/

opengeospatial/geopackage/issues/347
2017-04-17

R14

Jeff Yutzler

Annex F.7

Clean

up

WebP

extension

https://github.com/opengeospatial/
geopackage/issues/345
2017-04-17

R14

Jeff Yutzler

Annex F.3

Clean

up

RTree

Index

extension

https://github.com/opengeospatial/
geopackage/issues/341
2017-04-17

R14

Jeff Yutzler

Multiple

Clarifying

language

on

use

of

extensions, removing Requirement 17
https://github.com/opengeospatial/
geopackage/issues/323
2017-04-17

R14

Jeff Yutzler

2.2.1

Updating

Tile

Matrixintroduction

https://github.com/opengeospatial/
geopackage/issues/351
2017-04-26

R14

Jeff Yutzler

Annex I

Updating

reference

to

ISO

19115

https://github.com/opengeospatial/
geopackage/issues/356

89

Date

Rel

EditorParagraphmodified

Description

2017-06-02

R14

Jeff Yutzler

Annex F.7

clarifying whether you can mix or
match

JPG/PNG/WebP

https://github.com/opengeospatial/
geopackage/issues/362
2017-06-02

R14

Jeff Yutzler

Annex F.10

adding R145 for CRS WKT extension
https://github.com/opengeospatial/
geopackage/issues/363

2017-06-02

R14

Jeff Yutzler

1.1.3.1.2, 2.2.6.1 clarifying tile matrix set extents, adding
R144 https://github.com/opengeospatial/
geopackage/issues/366

2017-06-27

R14

Jeff Yutzler

2.2.8.1.1

removing

improperdefaults

https://github.com/opengeospatial/
geopackage/issues/358
2017-07-18

R14

Jeff Yutzler

3

addingnew

security

consideration

related to SQLite
2017-08-01

R14

Jeff Yutzler

2.3.2.1.2, Annex adding explanatory text regarding table
A.2.3.1.1
namecase
sensitivity;
updating
corresponding ATS test

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. * Futureenhancements to this standard, a future GeoPackage Web Service specification and modifications toexisting 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 themetadata 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
90

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 ina GeoPackage are described in UML Notation for RDBMS Tables below.

91

Figure 3. UML Notation for RDBMS Tables

In this standard, the following two stereotypes of UML classes are used to represent RDBMS tables:a. <<table>> An instantiation of a UML class as an RDMBS table.
b. <<column>> An instantiation of a UML attribute as an RDBMS table column.

92

In this standard, the following standard data types are used for RDBMS columns:
a. NULL – The value is a NULL value.
b. INTEGER – A signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of thevalue
c. REAL – The value is a floating point value, stored as an 8-byte IEEE floating point number.
d. TEXT – A sequence of characters, stored using the database encoding (UTF-8, UTF-16BE or UTF16LE).
e. BLOB – The value is a blob of data, stored exactly as it was input.
f. NONE – The value is a Date / Time Timestamp

B.11. GeoPackage Tables Detailed Diagram

93

Figure 4. GeoPackage Tables Details

B.12. GeoPackage Minimal Tables for Features
Diagram94

Figure 5. GeoPackage Minimal Tables for Features

B.13. GeoPackage Minimal Tables for Tiles Diagram

95

Figure 6. GeoPackage Minimal Tables for Tiles

96

Annex C: Table Definition SQL (Normative)
C.1. gpkg_spatial_ref_sys
gpkg_spatial_ref_sys Table Definition SQL

CREATE TABLE gpkg_spatial_ref_sys (srs_name TEXT NOT NULL,
srs_id INTEGER NOT NULL PRIMARY KEY,
organization TEXT NOT NULL,
organization_coordsys_id INTEGER NOT NULL,
definition TEXT NOT NULL,
description TEXT);SQL/MM View of gpkg_spatial_ref_sys Definition SQL (Informative)

CREATE VIEW st_spatial_ref_sys AS
SELECT
srs_name,
srs_id,organization,organization_coordsys_id,
definition,
description
FROM gpkg_spatial_ref_sys;
SF/SQL View of gpkg_spatial_ref_sys Definition SQL (Informative)

CREATE VIEW spatial_ref_sys AS
SELECT
srs_id AS srid,organization AS auth_name,organization_coordsys_id AS auth_srid,
definition AS srtext
FROM gpkg_spatial_ref_sys;

C.2. gpkg_contents

97

gpkg_contents Table Definition SQL

CREATE TABLE gpkg_contents (table_name TEXT NOT NULL PRIMARY KEY,
data_type TEXT NOT NULL,
identifier TEXT UNIQUE,
description TEXT DEFAULT '',last_change DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
min_x DOUBLE,
min_y DOUBLE,
max_x DOUBLE,
max_y DOUBLE,
srs_id INTEGER,CONSTRAINT fk_gc_r_srs_id FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys(srs_id));C.3. gpkg_geometry_columns
gpkg_geometry_columns Table Definition SQL

CREATE TABLE gpkg_geometry_columns (table_name TEXT NOT NULL,
column_name TEXT NOT NULL,
geometry_type_name TEXT NOT NULL,
srs_id INTEGER NOT NULL,z TINYINT NOT NULL,
m TINYINT NOT NULL,CONSTRAINT pk_geom_cols PRIMARY KEY (table_name, column_name),
CONSTRAINT uk_gc_table_name UNIQUE (table_name),
CONSTRAINT fk_gc_tn FOREIGN KEY (table_name) REFERENCES gpkg_contents(table_name),
CONSTRAINT fk_gc_srs FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys (srs_id));
SQL/MM View of gpkg_geometry_columns Definition SQL (Informative)

CREATE VIEW st_geometry_columns AS
SELECT
table_name,column_name,"ST_" || geometry_type_name,g.srs_id,
srs_name
FROM gpkg_geometry_columns as g JOIN gpkg_spatial_ref_sys AS s
WHERE g.srs_id = s.srs_id;98SF/SQL VIEW of gpkg_geometry_columns Definition SQL (Informative)

CREATE VIEW geometry_columns AS
SELECT
table_name AS f_table_name,column_name AS f_geometry_column,
code4name (geometry_type_name) AS geometry_type,
2 + (CASE z WHEN 1 THEN 1 WHEN 2 THEN 1 ELSE 0 END) + (CASE m WHEN 1 THEN 1 WHEN 2
THEN 1 ELSE 0 END) AS coord_dimension,
srs_id AS srid
FROM gpkg_geometry_columns;

Implementer must provide code4name(geometry_type_name) SQL function

C.4. sample_feature_table (Informative)
sample_feature_table Table Definition SQL (Informative)

CREATE TABLE sample_feature_table (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
geometry GEOMETRY,
text_attribute TEXT,
real_attribute REAL,
boolean_attribute BOOLEAN,
raster_or_photo BLOB);

C.5. gpkg_tile_matrix_set
gpkg_tile_matrix_set Table Creation SQL

CREATE TABLE gpkg_tile_matrix_set (table_name TEXT NOT NULL PRIMARY KEY,
srs_id INTEGER NOT NULL,
min_x DOUBLE NOT NULL,
min_y DOUBLE NOT NULL,
max_x DOUBLE NOT NULL,
max_y DOUBLE NOT NULL,CONSTRAINT fk_gtms_table_name FOREIGN KEY (table_name) REFERENCES gpkg_contents(table_name),
CONSTRAINT fk_gtms_srs FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys (srs_id));

C.6. gpkg_tile_matrix

99

gpkg_tile_matrix Table Creation SQL

CREATE TABLE gpkg_tile_matrix (table_name TEXT NOT NULL,
zoom_level INTEGER NOT NULL,
matrix_width INTEGER NOT NULL,
matrix_height INTEGER NOT NULL,
tile_width INTEGER NOT NULL,
tile_height INTEGER NOT NULL,
pixel_x_size DOUBLE NOT NULL,
pixel_y_size DOUBLE NOT NULL,CONSTRAINT pk_ttm PRIMARY KEY (table_name, zoom_level),
CONSTRAINT fk_tmm_table_name FOREIGN KEY (table_name) REFERENCES gpkg_contents(table_name));
EXAMPLE: gpkg_tile_matrix Insert Statement (Informative)

INSERT INTO gpkg_tile_matrix VALUES ("sample_tile_pyramid",
0,
1,
1,
512,
512,
2.0,
2.0
);

C.7. sample_tile_pyramid (Informative)
EXAMPLE: tiles table Create Table SQL (Informative)

CREATE TABLE sample_tile_pyramid (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
zoom_level INTEGER NOT NULL,
tile_column INTEGER NOT NULL,
tile_row INTEGER NOT NULL,
tile_data BLOB NOT NULL,UNIQUE (zoom_level, tile_column, tile_row))

100

EXAMPLE: tiles table Insert Statement (Informative)

INSERT INTO sample_matrix_pyramid VALUES (
1,
1,
1,
1,
"BLOB VALUE")

C.8. gpkg_extensions
gpkg_extensions Table Definition SQL

CREATE TABLE gpkg_extensions (table_name TEXT,
column_name TEXT,
extension_name TEXT NOT NULL,
definition TEXT NOT NULL,
scope TEXT NOT NULL,CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name));

C.9. sample_attributes_table (Informative)
EXAMPLE: Attributes table Create Table SQL (Informative)

CREATE TABLE sample_attributes (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
text_attribute TEXT,
real_attribute REAL,
boolean_attribute BOOLEAN,
raster_or_photo BLOB)
EXAMPLE: attributes table Insert Statement (Informative)

INSERT INTO sample_attributes(text_attribute, real_attribute, boolean_attribute,raster_or_photo) VALUES ("place",1,
true,"BLOB VALUE")101

Annex D: Trigger Definition SQL(Informative)
D.1. gpkg_tile_matrix
Table 15. gpkg_tile_matrix Trigger Definition SQL

CREATE TRIGGER 'gpkg_tile_matrix_zoom_level_insert'
BEFORE INSERT ON 'gpkg_tile_matrix'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'insert on table ''gpkg_tile_matrix'' violates constraint:
zoom_level cannot be less than 0')
WHERE (NEW.zoom_level < 0);
END
CREATE TRIGGER 'gpkg_tile_matrix_zoom_level_update'
BEFORE UPDATE of zoom_level ON 'gpkg_tile_matrix'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'update on table ''gpkg_tile_matrix'' violates constraint:
zoom_level cannot be less than 0')
WHERE (NEW.zoom_level < 0);
END
CREATE TRIGGER 'gpkg_tile_matrix_matrix_width_insert'
BEFORE INSERT ON 'gpkg_tile_matrix'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'insert on table ''gpkg_tile_matrix'' violates constraint:
matrix_width cannot be less than 1')
WHERE (NEW.matrix_width < 1);END
CREATE TRIGGER 'gpkg_tile_matrix_matrix_width_update'
BEFORE UPDATE OF matrix_width ON 'gpkg_tile_matrix'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'update on table ''gpkg_tile_matrix'' violates constraint:
matrix_width cannot be less than 1')
WHERE (NEW.matrix_width < 1);END
CREATE TRIGGER 'gpkg_tile_matrix_matrix_height_insert'
BEFORE INSERT ON 'gpkg_tile_matrix'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'insert on table ''gpkg_tile_matrix'' violates constraint:
matrix_height cannot be less than 1')
WHERE (NEW.matrix_height < 1);
END

102

CREATE TRIGGER 'gpkg_tile_matrix_matrix_height_update'
BEFORE UPDATE OF matrix_height ON 'gpkg_tile_matrix'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'update on table ''gpkg_tile_matrix'' violates constraint:
matrix_height cannot be less than 1')
WHERE (NEW.matrix_height < 1);END
CREATE TRIGGER 'gpkg_tile_matrix_pixel_x_size_insert'
BEFORE INSERT ON 'gpkg_tile_matrix'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'insert on table ''gpkg_tile_matrix'' violates constraint:
pixel_x_size must be greater than 0')
WHERE NOT (NEW.pixel_x_size > 0);
END
CREATE TRIGGER 'gpkg_tile_matrix_pixel_x_size_update'
BEFORE UPDATE OF pixel_x_size ON 'gpkg_tile_matrix'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'update on table ''gpkg_tile_matrix'' violates constraint:
pixel_x_size must be greater than 0')
WHERE NOT (NEW.pixel_x_size > 0);
END
CREATE TRIGGER 'gpkg_tile_matrix_pixel_y_size_insert'
BEFORE INSERT ON 'gpkg_tile_matrix'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'insert on table ''gpkg_tile_matrix'' violates constraint:
pixel_y_size must be greater than 0')
WHERE NOT (NEW.pixel_y_size > 0);
END
CREATE TRIGGER 'gpkg_tile_matrix_pixel_y_size_update'
BEFORE UPDATE OF pixel_y_size ON 'gpkg_tile_matrix'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'update on table ''gpkg_tile_matrix'' violates constraint:
pixel_y_size must be greater than 0')
WHERE NOT (NEW.pixel_y_size > 0);
END

D.2. sample_feature_table
Table 16. EXAMPLE: features table Trigger Definition SQL

103

CREATE TRIGGER "sample_feature_table_real_insert"
BEFORE INSERT ON "sample_feature_table"
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'insert on table ''sample_feature_table''
violates constraint: real_attribute must be greater than 0')
WHERE NOT (NEW.real_attribute > 0);
END
CREATE TRIGGER "sample_feature_table_real_update"
BEFORE UPDATE OF "real_attribute" ON "sample_feature_table"
FOR EACH ROW BEGIN
SELECT RAISE (ABORT, 'update of ''real_attribute'' on table''sample_feature_table'' violates constraint: real_attribute value
must be > 0')
WHERE NOT (NEW.real_attribute > 0);
END
where <t> and <c> are replaced with the names of the feature table and geometry column being
inserted or updated.

D.3. sample_tile_pyramid
Table 17. tiles table Trigger Definition SQL

CREATE TRIGGER "sample_tile_pyramid_zoom_insert"
BEFORE INSERT ON "sample_tile_pyramid"
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'insert on table ''sample_tile_pyramid'' violates constraint:
zoom_level not specified for table in gpkg_tile_matrix')
WHERE NOT (NEW.zoom_level IN (SELECT zoom_level FROM gpkg_tile_matrix WHERE table_name= 'sample_tile_pyramid')) ;END
CREATE TRIGGER "sample_tile_pyramid_zoom_update"
BEFORE UPDATE OF zoom_level ON "sample_tile_pyramid"
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'update on table ''sample_tile_pyramid'' violates constraint:
zoom_level not specified for table in gpkg_tile_matrix')
WHERE NOT (NEW.zoom_level IN (SELECT zoom_level FROM gpkg_tile_matrix WHERE table_name= 'sample_tile_pyramid')) ;END
CREATE TRIGGER "sample_tile_pyramid_tile_column_insert"
BEFORE INSERT ON "sample_tile_pyramid"
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'insert on table ''sample_tile_pyramid'' violates constraint:
tile_column cannot be < 0')
WHERE (NEW.tile_column < 0) ;104

SELECT RAISE(ABORT, 'insert on table ''sample_tile_pyramid'' violates constraint:
tile_column must by < matrix_width specified for table and zoom level in
gpkg_tile_matrix')
WHERE NOT (NEW.tile_column < (SELECT matrix_width FROM gpkg_tile_matrix WHERE
table_name = 'sample_tile_pyramid' AND zoom_level = NEW.zoom_level));
END
CREATE TRIGGER "sample_tile_pyramid_tile_column_update"
BEFORE UPDATE OF tile_column ON "sample_tile_pyramid"
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'update on table ''sample_tile_pyramid'' violates constraint:
tile_column cannot be < 0')
WHERE (NEW.tile_column < 0) ;SELECT RAISE(ABORT, 'update on table ''sample_tile_pyramid'' violates constraint:
tile_column must by < matrix_width specified for table and zoom level in
gpkg_tile_matrix')
WHERE NOT (NEW.tile_column < (SELECT matrix_width FROM gpkg_tile_matrix WHERE
table_name = 'sample_tile_pyramid' AND zoom_level = NEW.zoom_level));
END
CREATE TRIGGER "sample_tile_pyramid_tile_row_insert"
BEFORE INSERT ON "sample_tile_pyramid"
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'insert on table ''sample_tile_pyramid'' violates constraint:
tile_row cannot be < 0')
WHERE (NEW.tile_row < 0) ;SELECT RAISE(ABORT, 'insert on table ''sample_tile_pyramid'' violates constraint:
tile_row must by < matrix_height specified for table and zoom level in
gpkg_tile_matrix')
WHERE NOT (NEW.tile_row < (SELECT matrix_height FROM gpkg_tile_matrix WHERE table_name= 'sample_tile_pyramid' AND zoom_level = NEW.zoom_level));
END
CREATE TRIGGER "sample_tile_pyramid_tile_row_update"
BEFORE UPDATE OF tile_row ON "sample_tile_pyramid"
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'update on table ''sample_tile_pyramid'' violates constraint:
tile_row cannot be < 0')
WHERE (NEW.tile_row < 0) ;SELECT RAISE(ABORT, 'update on table ''sample_tile_pyramid'' violates constraint:
tile_row must by < matrix_height specified for table and zoom level in
gpkg_tile_matrix')
WHERE NOT (NEW.tile_row < (SELECT matrix_height FROM gpkg_tile_matrix WHERE table_name= 'sample_tile_pyramid' AND zoom_level = NEW.zoom_level));
END

105

Annex E: GeoPackage Extension Template(Informative)
Extension Title
Title of the Extension

Introduction
Description of extension

Extension Author
Author of extension, author_name.

Extension Name or Template
Name of the extension or definition of the template to create the name of extensions that should be
used in gpkg_extensions

Extension Type
"Extension of Existing Requirement in Clause(s) XXX" or "New Requirement Dependent on Clause(s)YYY"Applicability
Tables and/or columns on which this extension may be applied

Scope
Read-write or write-only with clarification if necessary

Requirements
Definition of extension and interdependencies with other extensions if any.

GeoPackage
Definition of extension data or MIME type(s)Definition of extension tables or table templates
Definition of triggers or trigger templates

106

GeoPackage SQLite Configuration
Definition of SQLite configuration settings
Setting compile or
runtimeOptionShall / Not (Value)

Discussion

GeoPackage SQLite Extension
Definition of SQL functions
SQL Function

Descriptionfoo(bar, baz) : datatype

Returns r when w

Use

Abstract Test Suite
All test cases required to verify conformance to this extension.

Examples (Informative)
Any example or samples demonstrating the extension in use.

107Annex F: Registered Extensions (Normative)
This clause specifies requirements for GeoPackage extensions. Definitions of those extensions arein the form specified by the template in GeoPackage Extension Template (Informative).
Extension Name

Content Type

GeoPackage Non-Linear Geometry Types

features

RTree Spatial Indexes

features

Zoom Other Intervals

tiles

Tiles Encoding WebP

tiles

Metadata

generalSchemafeatures

WKT for Coordinate Reference Systems

spatial reference systems

F.1. GeoPackage Non-Linear Geometry Types
Introduction
This extension of clause SQL Geometry Types defines additional geometry types.
Clause 2.1.4 of the GeoPackage Version 1 Encoding Standard specifies support for the Geometry,
Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeomCollection
geometry types in the GeoPackageBinary geometry encoding format specified in clause 2.1.3. Thisextension specifies support for the additional CircularString, CompoundCurve, CurvePolygon,
MultiCurve, MultiSurface, Curve, and Surface geometry types in the GeoPackage Binary geometry
encoding format using the codes from Geometry Type Codes (Extension).

Extension Author
GeoPackage SWG, author_name gpkg

Extension Name or Template
Extension names are constructed from the gpkg_geom_<gname> template where <gname> is theuppercase name of the extension geometry type from Geometry Type Codes (Extension).

Extension Type
Extension of Existing Requirement in clause SQL Geometry Types

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

Scope

108

Read-write

Requirements
GeoPackage
Requirement 65(extends GPKG-25) The geometry_type_name value in a gpkg_geometry_columns row MAY be one of
the uppercase extended non-linear geometry type names specified in Geometry Types(Normative).
Requirement 66

The GeoPackageBinary geometry encoding format specified in clause Geometry Encoding
SHALL be used to encode non-linear geometry types using the type codes in Geometry Types(Normative) table Geometry Type Codes (Extension).
Requirement 67

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

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

GeoPackage SQLite Configuration
None
GeoPackage SQLite Extension
Requirement 69

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

109

Abstract Test Suite
GeoPackage Extension Types
Test Case ID/extensions/geometry_types/data_values_geometry_type_name

Test Purpose

Verify that only allowed geometry types (including extended non-linear geometry
types) are in use.

Test Method

1. Runtest/opt/features/geometry_columns/data/data_values_geometry_type_name,but

the values in 3a may also include the values from Table 32 in Annex G.
Reference

Annex F.1 Req 65:

Test TypeCapability

Test Case ID/extensions/geometry_types/all_types_test_data

Test Purpose

Verifythat

geometries

non-linear

GeoPackageBinary format encodings.

110

geometry

types

are

storedinvalid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
a. SELECT cn FROM tn;
b. For each row from step a, log fail if GeoPackageBinary "X" type flag is 1
c. 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
43, thend. Log cn.header values, wkb endianness and geometry type ii. If cn.wkb is
not correctly encoded per ISO 13249-3 clause 5.1.46 then log fail iii. If
cn.flags.E is 1 - 4 and some cn.wkbx is outside of cn.envelope.minx,maxx
then log fail iv. If cn.flags.E is 1 - 4 and some gc.wkby is outside ofcn.envelope.miny,maxy then log faile. If cn.flags.E is 2,4 and some gc.wkb.z is outside of cnenvelope.minz,maxz
then log fail vi. If cn.flags.E is 3,4 and some gc.wkb.m is outside ofcn.envelope.minm,maxm then log fail vii. If cn.flags.E is 5-7 then log failviii. Otherwise log pass
6. Log pass if log contains pass and no fails
Reference

Annex F.1 Req 66:

Test TypeCapability

Extensions Name
Test Case ID/extensions/geometry_types/extension_name

Test Purpose

Verify that an extension name in the form gpkg_geom_<gname> is defined foreach <gname> extension geometry type from Annex G used in a GeoPackage.

111

Test Method

1. SELECT table_name, column_name FROM gpkg_geometry_columns WHERE
table_name IN (SELECT table_name FROM gpkg_contents WHERE data_type== 'features'))
2. Not testable if result set is empty
3. For each row result set table_name, column_name from step 3
a. SELECT result_set_column_name FROM result_set_table_nameb. For each geometry column value from step ai. If the first two bytes of each geometry column value are "GP", thenA. /opt/extension_mechanism/extensions/data/table_def
B. Fail if failed
C. SELECT ST_GeometryType(geometry column value) AS <gtype>;
D. SELECT extension_name FROM gpkg_extensions WERE table_name=result_set_table_nameANDcolumn_name=result_set_column_name AND extension_name = \'gpkg_geom_' ||<gtype>I. Fail if result set is empty
II. Log pass otherwise
4. Pass if logged pass and no fails
Reference

Annex F.1 Req 67:

Test Type

Basic

Extensions Row
Test Case ID/extensions/geometry_types/extension_row

Test Purpose

Verify that the gpkg_extensions table contains a row with an extension_name in
the form gpkg_geom_<gname> for each table_name and column_name in the
gpkg_geometry_columns table with a <gname> geometry_type_name.

112Test Method

1. SELECT

table_name,column_name,geometry_type_name

FROM

gpkg_geometry_columns
2. Not testable if no results
3. For each result
a. If geometry_type_name is an extended geometry typei. SELECT extension_name FROM gpkg_extensions WHERE table_name ='{table_name}' AND column_name = '{column_name}"ii. Fail if result set does not contain a row with an extension_name of
gpkg_geom_{geometry_type_name}
4. Pass if no fails
Reference

Annex F.1 Req 68:

Test TypeCapability

F.2. User Defined Geometry Types Extension of
GeoPackageBinary Geometry Encoding

On August 15, 2016 the GeoPackage SWG voted to remove this extension from thestandard due to interoperability concerns. For more information see the release
notes.

F.3. RTree Spatial Indexes
Introduction
This extension adds a new capability for spatially indexing columns with geometries encoded per
clause Geometry Encoding and User Defined Geometry Types Extension of GeoPackageBinary
Geometry Encoding.
The RTree index extension provides a means to encode an RTree index for geometry values in a
GeoPackage. An RTree index provides a significant performance advantage for searches with basic
envelope spatial criteria that return subsets of the rows in a feature table with a non-trivial number(thousands or more) of rows.
Spatial indexes provide a significant performance advantage for searches with basic envelope
spatial criteria that return subsets of the rows in a feature table with a non-trivial number[K26](thousands or more) of rows.

Extension Author
GeoPackage SWG, author_name gpkg.

113

Extension Name or Template
gpkg_rtree_index

Extension TypeNew Requirement dependent on clauses Geometry Encoding and User Defined Geometry Types
Extension of GeoPackageBinary Geometry Encoding.

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

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

Requirements
This extension uses the rtree implementation provided by the SQLite R*Tree Module extension
documented at http://www.sqlite.org/rtree.html.
GeoPackage
Requirement 75

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

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

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

114

Create Virtual Table

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

CREATE VIRTUAL TABLE rtree_<t>_<c> USING rtree(id, minx, maxx, miny, maxy)
where <t> and <c> are replaced with the names of the feature table and geometry column being
indexed. The rtree function id parameter becomes the virtual table 64-bit signed integer primary
key id column, and the min/max x/y parameters are min- and max-value pairs (stored as 32-bit
floating point numbers) for each dimension that become the virtual table data columns that are
populated to create the spatial rtree index.
Load Spatial Index Values

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

INSERT OR REPLACE INTO rtree_<t>_<c>
SELECT <i>, st_minx(<c>), st_maxx(<c>), st_miny(<c>), st_maxy(<c>) FROM <t>;where <t> and <c> are replaced with the names of the feature table and geometry column being
indexed and <i> is replaced with the name of the feature table integer primary key column.
Define Triggers to Maintain Spatial Index Values

For each spatial index in a GeoPackage, corresponding insert, update and delete triggers that
update the spatial index SHALL be present on the indexed geometry column. These spatial index
triggers SHALL be defined as follows:/* Conditions: Insertion of non-empty geometry
Actions: Insert record into rtree */CREATE TRIGGER rtree_<t>_<c>_insert AFTER INSERT ON <t>
WHEN (new.<c> NOT NULL AND NOT ST_IsEmpty(NEW.<c>))
BEGIN
INSERT OR REPLACE INTO rtree_<t>_<c> VALUES (NEW.<i>,
ST_MinX(NEW.<c>), ST_MaxX(NEW.<c>),
ST_MinY(NEW.<c>), ST_MaxY(NEW.<c>));
END;/* Conditions: Update of geometry column to non-empty geometry
No row ID change
Actions: Update record in rtree */CREATE TRIGGER rtree_<t>_<c>_update1 AFTER UPDATE OF <c> ON <t>115

WHEN OLD.<i> = NEW.<i> AND(NEW.<c> NOTNULL AND NOT ST_IsEmpty(NEW.<c>))
BEGIN
INSERT OR REPLACE INTO rtree_<t>_<c> VALUES (NEW.<i>,
ST_MinX(NEW.<c>), ST_MaxX(NEW.<c>),
ST_MinY(NEW.<c>), ST_MaxY(NEW.<c>));
END;/* Conditions: Update of geometry column to empty geometry
No row ID change
Actions: Remove record from rtree */CREATE TRIGGER rtree_<t>_<c>_update2 AFTER UPDATE OF <c> ON <t>
WHEN OLD.<i> = NEW.<i> AND(NEW.<c> ISNULL OR ST_IsEmpty(NEW.<c>))
BEGIN
DELETE FROM rtree_<t>_<c> WHERE id = OLD.<i>;
END;/* Conditions: Update of any column
Row ID change
Non-empty geometry
Actions: Remove record from rtree for old <i>
Insert record into rtree for new <i> */CREATE TRIGGER rtree_<t>_<c>_update3 AFTER UPDATE OF <c> ON <t>
WHEN OLD.<i> != NEW.<i> AND(NEW.<c> NOTNULL AND NOT ST_IsEmpty(NEW.<c>))
BEGIN
DELETE FROM rtree_<t>_<c> WHERE id = OLD.<i>;INSERT OR REPLACE INTO rtree_<t>_<c> VALUES (NEW.<i>,
ST_MinX(NEW.<c>), ST_MaxX(NEW.<c>),
ST_MinY(NEW.<c>), ST_MaxY(NEW.<c>));
END;/* Conditions: Update of any column
Row ID change
Empty geometry
Actions: Remove record from rtree for old and new <i> */CREATE TRIGGER rtree_<t>_<c>_update4 AFTER UPDATE ON <t>
WHEN OLD.<i> != NEW.<i> AND(NEW.<c> ISNULL OR ST_IsEmpty(NEW.<c>))
BEGIN
DELETE FROM rtree_<t>_<c> WHERE id IN (OLD.<i>, NEW.<i>);
END;/* Conditions: Row deleted
Actions: Remove record from rtree for old <i> */CREATE TRIGGER rtree_<t>_<c>_delete AFTER DELETE ON <t>116

WHEN old.<c> NOT NULL
BEGIN
DELETE FROM rtree_<t>_<c> WHERE id = OLD.<i>;
END;
where <t> and <c> are replaced with the names of the feature table and geometry column being
indexed and <i> is replaced with the name of the feature table integer primary key column.
GeoPackage SQLite Configuration
Definition of SQLite configuration settings
Setting compile or
runtimeOptionShall / Not (Value)

Discussion

compileSQLITE_ENABLE_RTRE ShallE

RTrees ares used for
GeoPackage Spatial
IndexescompileSQLITE_RTREE_INT_ON Not
LY

RTrees with floating
point values are used
for GeoPackage spatial
indexes

GeoPackage SQLite Extension
Definition of SQL functions
SQL Function

Description

Use

ST_IsEmpty(geom Geometry):
integer

Returns 1 if geometry value isempty, 0 if not empty, NULL if
geometry value is NULL

Test if a geometry value
corresponds to the empty set

ST_MinX(geom Geometry): real

Returns the minimum X valueof the bounding envelope of ageometry

Update the spatial index on ageometry column in a feature
table

ST_MaxX(geom Geometry): real Returns the maximum Y valueof the bounding envelope of ageometry

Update the spatial index on ageometry column in a featuretable

ST_MinY(geom Geometry): real

Returns the minimum X valueof the bounding envelope of ageometry

Update the spatial index on ageometry column in a feature
table

ST_MaxY(geom Geometry): real Returns the maximum Y valueof the bounding envelope of ageometry

Update the spatial index on ageometry column in a feature
table

117

Requirement 78

The SQL functions on geometries in this SQLite Extension SHALL operate correctly on
extended geometry types specified by User Defined Geometry Types Extension of
GeoPackageBinary Geometry Encoding and/or GeoPackage Non-Linear Geometry Types whenthose extensions are also implemented.

Abstract Test Suite
Extension Name
Test Case ID/extensions/rtree/extension_name

Test Purpose

Verify that spatial index extensions are registered using the "gpkg_rtree_index"
name in the gpkg_extensions table.

Test Method

1. SELECTCOUNT(*)

FROM

gpkg_extensions

WHERE

extension_name='gpkg_rtree_index';
2. Extension not testable if count = 0
Reference

Annex F.3 Req 75

Test TypeCapability

Extensions Row
Test Case ID/extensions/rtree/extension_row

Test Purpose

Verify that the "gpkg_rtree_index" extension name is used to register spatial index
extensions.

Test Method

1. SELECT table_name, column_name, scope FROM gpkg_extensions WHERE
extension_name = 'gpkg_rtree_index'
a. Not testable if result set is empty
b. Fail if any column_name is NULL
c. Fail if any scope is not 'write-only'
d. Fail if any column_name is not a column in table_name
2. Pass otherwise

Reference

Annex F.3 Req 76

Test Type

Basic

118

Implementation
Test Case ID/reg_ext/features/spatial_indexes/implementation

Test Purpose

Verify the correct implementation of spatial indexes on feature table geometry
columns.

Test Method

1. SELECT table_name, column_name FROM gpkg_geometry_columns WHERE
table_name

IN(SELECTtable_name

FROM

gpkg_extensions

WHERE

extension_name == 'gpkg_rtree_index')
2. Not testable if result set is empty
3. For each row table_name, column_name from step 1
a. SELECT

sql

FROM

sqlite_master

WHERE

tbl_name='rtree_'||result_set_table_name || '_' || result_set_column_namei. Fail if returned sql != 'CREATE VIRTUAL TABLE "rtree_' ||result_set_table_name || '_' || result_set_column_name ||'" USING
rtree(id, minx, maxx, miny, maxy)'b. SELECT sql FROM sqlite_master WHERE type = 'trigger' AND name ='rtree_' || result_set_table_name || '_' || result_set_column_name ||'_insert'
i. Fail if returned sql != result of populating insert triggers templateusing result_set_table_name for <t> and result_set_column_name for<c>c. SELECT sql FROM sqlite_master WHERE type = 'trigger' AND name LIKE
'rtree_' || result_set_table_name || '_' || result_set_column_name ||'_update%' ORDER BY name ASC
i. Fail if returned sql != result of populating 4 update triggers templates
using result_set_table_name for <t> and result_set_column_name for<c>d. SELECT sql FROM sqlite_master WHERE type='trigger' AND name = 'rtree_'|| result_set_table_name || '_' || result_set_column_name || '_delete'
i. Fail if returned sql != result of populating delete trigger template using
result_set_table_name for <t> and result_set_column_name for <c>
4. Pass if no fails
Reference

Annex F.3 Req 77

Test TypeCapability

Test Case ID/reg_ext/features/spatial_indexes/implementation/sql_functions

119Test Purpose

Verify the correct implementation of sql functions used in spatial indexes onfeature table geometry columns.

Test Method

1. Open Geometry Test Data Set GeoPackage with GeoPackage SQLite Extension
2. For each Geometry Test Data Set <gtype_test> data table row for eachgeometry type in Annex G, for an assortment of srs_ids, for an assortment of
coordinate values including empty geometries, without and with z and / or mvalues, in both big and little endian encodings:
a. SELECT 'Fail' FROM <gtype_test> WHERE ST_IsEmpty(geom.) != empty
b. SELECT 'Fail' FROM <gtype_test> WHERE ST_MinX(geom) != minx
c. SELECT 'Fail' FROM <gtype_test> WHERE ST_MaxX(geom) != maxx
d. SELECT 'Fail' FROM <gtype_test> WHERE ST_MinY(geom) != minye. SELECT 'Fail' FROM <gtype_test> WHERE ST_MaxY(geom) != maxy
3. Pass if no 'Fail' selected from step 2

Reference

Annex F.3 Req 78

Test TypeCapability

F.4. Geometry Type Triggers



On August 15, 2016 the GeoPackage SWG voted to remote this extension from thestandard due to interoperability concerns. For more information see the release
notes.

F.5. Geometry SRS ID Triggers



On August 15, 2016 the GeoPackage SWG voted to remote this extension from thestandard due to interoperability concerns. For more information see the release
notes.

F.6. Zoom Other Intervals
Introduction
This extension of clause Zoom Levels allows zoom level intervals other than a factor of two.
In a GeoPackage, zoom levels are integers in sequence from 0 to n that identify tile matrix layers ina tile matrix set that contain tiles of decreasing spatial extent and finer spatial resolution. Adjacentzoom 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 thedimensions of the real world area represented by one pixel. Pixel sizes MAY vary by a constant120

factor or by different factors or intervals between some or all adjacent zoom levels in a tile matrix
set. In the commonly used "zoom times two" convention, pixel sizes vary by a factor of 2 between
all adjacent zoom levels, as shown in the example in [tiles_factor2_example_appendix].
This extension enables use of "zoom other intervals" conventions with different factors or irregularintervals with pixel sizes chosen for intuitive cartographic representation of raster data, or tocoincide with the original pixel size of commonly used global image products. See WMTS [16]Annex E for additional examples of both conventions.

Extension Author
GeoPackage SWG, author_name gpkg

Extension Name or Template
gpkg_zoom_other

Extension Type
Extension of Existing Requirement in clause 2.2.3.

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

Scope
Read-write

Requirements
GeoPackage
Requirement 87

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

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

121Requirement 89

Tile pyramid user data tables MAY have pixel sizes that vary by irregular intervals or by
regular intervals other than a factor of two (the default) between adjacent zoom levels.
Extends Requirement 35.
The pixel_x_size and / or pixel_y_size column values in the gpkg_tile_matrix table vary byirregular intervals or by regular intervals other than a factor of two (the default) between adjacentzoom levels for a particular tile matrix set pyramid table.
GeoPackage SQLite Configuration
None
GeoPackage SQLite Extension
None

Abstract Test Suite
Extensions Name
Test Case ID/reg_ext/tiles/zoom_levels/data/zoom_other_ext_name

Test Purpose

Verify that the "gpkg_zoom_other" extension name is used to register tiles tables
with other than factors of two zoom intervals.

122

Test Method

1. SELECT table_name FROM gpkg_contents WHERE data_type = \'tiles'
2. Not testable if empty result set
3. For each row table_name from step 1
a. SELECT zoom_level, pixel_x_size, pixel_y_size FROM gpkg_tile_matrix
WHERE table_name = selected table name ORDER BY zoom_level ASC
b. Not testable if returns empty result set
c. Not testable if there are not two rows with adjacent zoom levels
d. Not testable if no pair of rows for adjacent zoom levels have pixel_x_size
or pixel_y_size values that differ by other than factors of two
e. /opt/extension_mechanism/extensions/data/table_def
f. Fail if failed
g. SELECT * FROM gpkg_extensions WHERE table_name = selected tablename AND extension_name = \'gpkg_zoom_other'h. Fail if returns an empty result set
i. Log pass otherwise
4. Pass if logged pass and no fails

Reference

Annex F.6 Req 87

Test Type

Basic

Extensions Row
Test Case ID/reg_ext/tiles/zoom_levels/data/zoom_other_ext_row

Test Purpose

Verify that tiles tables with other than factors of two zoom intervals areregistered using the "gpkg_zoom_other" extension name.

Test Method/reg_ext/tiles/zoom_levels/data/zoom_other_ext_name

Reference

Annex F.6 Req 88

Test Type:Capability

Zoom Interval
Test Case ID/reg_ext/tiles/zoom_levels/data/zoom_intervals

123

Test Purpose

Verify that zoom level pixel sizes for tile matrix user data tables vary by factors of
2 between adjacent zoom levels in the tile matrix metadata table only for tile
matrix sets that this extension does not apply to.

Test Method

1. Override test /opt/tiles/zoom_levels/data/zoom_times_two
2. SELECT table_name AS tn FROM gpkg_contents WHERE data_type = \'tiles'
3. For each row tn from step 2
a. WHEN (SELECT tbl_name FROM sqlite_master WHERE tbl_name =\'gpkg_extensions') = \'gpkg_extensions' THEN (SELECT table_name from
gpkg_extensions WHERE extension_name = \'gpkg_zoom_other' ANDtable_name = \'tn') END;
b. If

returnsempty

resultset,

executetest/opt/tiles/zoom_levels/data/zoom_times_two
4. Pass if no fails
Reference

Annex F.6 Req 89

Test TypeCapability

F.7. Tiles Encoding WebP
Introduction
This extension of clauses Tile Encoding PNG and Tile Encoding JPEG allows encoding of tile images
in WebP format.
PNG and JPEG are the default MIME types for encoding images in tile pyramid user data tables. Thisextension allows the use of image/x-webp as an additional encoding type.

Extension Author
GeoPackage SWG, author_name gpkg.

Extension Name or Template
gpkg_webp

Extension Type
Extension of Existing Requirement in clauses Tile Encoding PNG and Tile Encoding JPEG.

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

124

ScopeRead-write

Requirements
GeoPackage
Requirement 90

GeoPackages with one or more rows in the gpkg_extensions table with an extension_name of"gpkg_webp" SHALL comply with this extension.
Requirement 91

A GeoPackage that contains tile pyramid user data tables with tile_data columns that containimages in WebP format SHALL contain a gpkg_extensions table that contains row records withtable_name values for each such table, column_name values of "tile_data", extension_name column
values of "gpkg_webp", and scope column values of "read-write".
Requirement 92(extends GPKG-36 and GPKG-37) A GeoPackage that contains a tile pyramid user data table that
contains tile data MAY store tile_data in the WebP format[22]. Files complying with the WebP
format SHALL have the MIME type image/x-webp.



Requirements 36 and 37 allow a tile pyramid user data table to contain PNG or JPG
tiles. This requirement allows for WebP tiles as well.

GeoPackage SQLite Configuration
None
GeoPackage SQLite Extension
None

Abstract Test Suite
Extensions Name
Test Case ID/extensions/tile_encoding_webp/data/webp_ext_name

Test Purpose:

Verify that the "gpkg_webp" extensions name is used to register WEBP tile
encoding implementations.

125

Test Method:

1. SELECTCOUNT(*)

FROM

gpkg_extensions

WHERE

extension_name='gpkg_webp';
2. Extension not in use if count is empty
Reference

Annex F.7 Req 90

Test Type

Basic

Extensions Row
Test Case ID/extensions/tile_encoding_webp/data/webp_ext_row

Test Purpose:

Verify that this extension is registered using proper rows in the gpkg_extensions
table.

Test Method:

1. /opt/extension_mechanism/data/data_values_table_name will test whether thetable_name values are valid.
2. SELECT column_name, scope FROM gpkg_extensions where extension_name ='gpkg_webp';
3. For each row table_name from step 1
a. Fail if column_name is not "tile_data"
b. Fail if scope is not "read-write"
4. Pass if no fails

Reference:

Annex F.7 Req 91

Test TypeCapability

Extensions Mime Type
Test Case ID/extensions/tiles_encoding_webp/data/mime_type_webp

Test Purpose

Verify that a tile matrix user data table that conforms to this extension contains avalid image type, including images of MIME type image/x-webp.

126

Test Method

1. SELECT table_name FROM gpkg_extensions WHERE extension_name ='gpkg_webp'
2. For each table_name from step 1
a. Run test /opt/tiles/tiles_encoding/data/mime_type_jpeg step 2 but allow an
extra MIME type (step 2c) of "image/x-webp"
3. Pass if no fails

Reference

Annex F.7 Req 92

Test TypeCapability

F.8. Metadata
Introduction
Two tables in a GeoPackage provide a means of storing metadata in MIME [21] encodings that are
defined in accordance with any authoritative metadata specifications, and relating it to thefeatures, rasters, and tiles data in a GeoPackage. These tables are intended to provide the support
necessary to implement the hierarchical metadata models as defined in ISO 19115 [28] andillustrated in Hierarchical Metadata Example One - ISO19115. and Raster or Tile Metadata Example.
As GeoPackage data is captured and updated, the most local and specific detailed metadata changes
associated with the new or modified data MAY be captured separately, and referenced to existing
global and general metadata.
The gpkg_metadata table that contains metadata is described in clause Metadata Table, and the
gpkg_metadata_reference table that relates gpkg_metadata to GeoPackage data is described in clause
Metadata Reference Table. There is no GeoPackage requirement that such metadata be provided orthat defined metadata be structured in a hierarchical fashion[K27]. This extension simply provides amechanism for storing this information. If this extension is used, such metadata[K28]

and data that

relates it to GeoPackage contents should not be stored in other tables.

Extension Author
GeoPackage SWG, author_name gpkg

Extension Name or Template
gpkg_metadata

Extension TypeNew Requirement

Applicability
This extension applies to any content in the GeoPackage.
127

ScopeRead-write

Requirements
Table Definitions
Metadata Table

Requirement 93

A GeoPackage A GeoPackage MAY contain a table named gpkg_metadata. If present it SHALL
be defined per clauses Metadata Table, Metadata Table Definition, and gpkg_metadata Table
Definition SQL.
The first component of GeoPackage metadata is the gpkg_metadata table that MAY contain metadata
in MIME [21] encodings structured in accordance with any authoritative metadata specification,such as ISO 19115 [28], ISO 19115-2 [B6], ISO 19139 [B7], Dublin Core [B8], CSDGM [B10], DDMS
[B12], NMF/NMIS [B13], etc. The GeoPackage interpretation of what constitutes "metadata" is abroad one that includes UML models [B14] encoded in XMI [B15], GML Application Schemas [30],
ISO 19110 feature catalogues [B18], OWL [B20] and SKOS [B21] taxonomies, etc.
Table 18. Metadata Table Definition

Column Name Column Type

ColumnDescription

Null

Default

id

INTEGER

Metadata
primary keynomd_scope

TEXT

Case sensitive
name of thedata scope towhich thismetadata
applies; see
Metadata
Scopes belowno'dataset'

md_standard_ur TEXTi

URI [23]
reference tothe metadata
structuredefinition[K29]
authoritynoany

mime_typeTEXT

MIME [21]encoding of
metadatano'text/xml' [24]metadata

TEXT

metadata

no

''

Key
PK

The md_standard_uri data value provides an identifier for the metadata structure (schema)128specified by its definition authority. The structure (schema) information could be in whateverencoding is used by the definition authority, e.g. UML [B14], or IDEF1x [B16], or XML/Schema[25][26][27], or RDF/S [B19].
See gpkg_metadata Table Definition SQL.
Metadata Reference Table

Requirement 95

A GeoPackage that contains a gpkg_metadata table SHALL contain a gpkg_metadata_referencetable per clauses Metadata Reference Table, Metadata Reference Table Definition (Table Name:
gpkg_metadata_reference), and gpkg_metadata_reference Table Definition SQL.
The second component of GeoPackage metadata is the gpkg_metadata_reference table that links
metadata in the gpkg_metadata table to data in the feature, and tiles tables defined in clauses 2.1.6
and 2.2.7. The gpkg_metadata_reference table is not required to contain any rows.
Table 19. Metadata Reference Table Definition (Table Name: gpkg_metadata_reference)

Column Name Col Type

ColumnDescription

reference_scop TEXTe

Lowercasenometadata
referencescope; one of'geopackage','table','column','row', 'row/col'

TEXT

Name of theyestable to which
this metadata
reference
applies, or
NULL forreference_scop
e of'geopackage'.

table_name

Null

DefaultKey

129

Column Name Col Type

ColumnDescription

Nullcolumn_name

TEXT

Name of theyescolumn towhich thismetadata
reference
applies; NULL
forreference_scop
e of'geopackage','table' or 'row', orthe name of acolumn in thetable_nametable forreference_scop
e of 'column' or'row/col'row_id_value[K30]

INTEGERNULL foryes
reference_scop
e of'geopackage','table' or'column', or the
rowed of a row
record in thetable_nametable forreference_scop
e of 'row' or'row/col'timestamp

DATETIME

timestampnovalue in ISO
8601 format as
defined by thestrftimefunction \'%Y%m%dT%H:%M:%fZ' format
string applied
to the current
time

130

Default

strftime(\'%Y%m%dT%H:%M:%
fZ', \'now')

KeyColumn Name Col Type

ColumnDescription

Null

DefaultKey

md_file_id

INTEGER

gpkg_metadata notable id column
value for the
metadata towhich this
gpkg_metadata_
reference
appliesFK

md_parent_id

INTEGER

gpkg_metadata yestable id column
value for thehierarchicalparentgpkg_metadata
for the
gpkg_metadata
to which this
gpkg_metadata_
reference
applies, or
NULL if
md_file_id
forms the rootof a metadatahierarchy

FK

Every row in gpkg_metadata_reference that has null value as md_parent_id forms the root of a
[K31]

metadata hierarchy.

See Table Definition SQL (Normative) clause gpkg_metadata_reference Table Definition SQL.
Table Data Values
gpkg_extensions

Requirement 140

GeoPackages with a row in the gpkg_extensions table with an extension_name of"gpkg_metadata" SHALL comply with this extension. The row SHALL have a scope of "readwrite".

gpkg_metadata

The md_scope column in the gpkg_metadata table is the name of the applicable scope for the contentsof the metadata column for a given row. The list of valid scope names and their definitions isprovided in Metadata Scopes below. The initial contents of this table were obtained from the ISO
19115 [28], Annex B B.5.25 MD_ScopeCode code list, which was extended[K32]

for use in the

GeoPackage specification by addition of entries with "NA" as the scope code column in Metadata131

Table Definition.
Table 20. Metadata Scopes

Name (md_scope)

Scope CodeDefinition

undefined

NA

Metadata information scope is
undefined

fieldSession

012

Information applies to the fieldsessioncollectionSession

004

Information applies to thecollection session

series006

Information applies to the[K33](dataset) series

dataset005

Information applies to the(geographic feature) dataset

featureType010

Information applies to a feature
type (class)feature009

Information applies to a feature(instance)

attributeType002

Information applies to theattribute classattribute

001

Information applies to thecharacteristic of a feature(instance)

tile

016

Information applies to a tile, aspatial subset of geographic
data

model

015

Information applies to a copy or
imitation of an existing or
hypothetical object

catalog

NA

Metadata applies to a feature[K34]
catalog

schemaNA

Metadata applies to an[K35]
application schemataxonomyNA

Metadata applies to a taxonomy[K36]
or knowledge system

software013

Information applies to acomputer program or routine

service

014

Information applies to acapability which a service
provider entity makes available
to a service user entity througha set of interfaces that define abehavior, such as a use casecollectionHardware

003

Information applies to thecollection hardware class132

Name (md_scope)

Scope CodeDefinition

nonGeographicDataset

007

Information applies to nongeographic data

dimensionGroup008

Information applies to adimension group

Requirement 94

Each md_scope column value in a gpkg_metadata table or updateable view SHALL be one of thename column values from Metadata Scopes.

gpkg_metadata_reference

Requirement 96

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

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

Every

gpkg_metadata_reference

table

row

with

a

reference_scopecolumnvalue

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

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

Every gpkg_metadata_reference table row timestamp column value SHALL be in ISO 8601 [29]format containing a complete date plus UTC hours, minutes, seconds and a decimal fraction of
a second, with a 'Z' ('zulu') suffix indicating UTC.

[K37]

133

Requirement 101

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

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

Abstract Test Suite
Table Definition
Metadata Table

Test Case ID/extensions/metadata/metadata/table_def

Test Purpose

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

Test Method

1. PRAGMA TABLE_INFO(gpkg_metadata)
2. Fail if returns an empty result set.
3. Pass if the column names, types, nullability, default values, and primary,foreign and unique key constraints match all of those in the contents of Table
18. Column order, check constraint and trigger definitions, and other column
definitions in the returned sql are irrelevant.
4. Fail otherwise.

Reference

Annex F.8 Req 93

Test Type

Basic

Metadata Reference Table

Test Case ID/extensions/metadata/metadata_reference/table_def

Test Purpose

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

134

Test Method

1. SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name ='gpkg_metadata_reference'
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 33. Column order, check constraint and trigger
definitions, and other column definitions in the returned sql are irrelevant.
4. Fail otherwise.

Reference

Annex F.8 Req 95

Test Type

Basic

Table Data Values
gpkg_extensions

Test Case ID/extensions/metadata/extensions/data_values_scope

Test Purpose

Verify that the gpkg_extensions table has the required row.

Test Method

1. SELECT

scope

FROM

gpkg_extensions

WHERE

extension_name='gpkg_metadata';
2. Not testable if returns an empty result set
3. For each row returned from step 1
a. Fail if scope is not "read-write".
4. Pass if no fails
Reference

Annex F.8 Req 140

Test Type:Capabilities

gpkg_metadata

Test Case ID/extensions/metadata/metadata/data_values_md_scope

Test Purpose

Verify that each of the md_scope column values in a gpkg_metadata table is one of
the name column values from Metadata Scopes.

135

Test Method

1. SELECT md_scope FROM gpkg_metadata
2. Not testable if returns an empty result set
3. For each row returned from step 1
a. Fail if md_scope value not one of the name column values from Metadata
Scopes.
4. Pass if no fails

Reference

Annex F.8 Req 94

Test Type:Capabilities

gpkg_metadata_reference

Test Case ID/extensions/metadata/metadata_reference/reference_scope

Test Purpose

Verify that gpkg_metadata_reference table reference_scope column values arevalid.

Test Method

1. SELECT reference_scope FROM gpkg_metadata_reference
2. Not testable if returns an empty result set
3. SELECT

reference_scope

FROM

gpkg_metadata_reference

WHERE

reference_scope NOT IN ('geopackage','table','column','row','row/col')4. Fail if does not return an empty result set
5. Pass otherwise.
Reference

Annex F.8 Req 96

Test TypeCapability

Test Case ID/extensions/metadata/metadata_reference/table_name

Test Purpose

Verify that gpkg_metadata_reference table_name column values are NULL forrows with reference_scope values of 'geopackage', and reference gpkg_contents
table_name values for all other reference_scope values.

136

Test Method

1. SELECT table_name FROM gpkg_metadata_reference
2. Not testable if returns an empty result set
3. SELECT table_name FROM gpkg_metadata_reference WHERE reference_scope= \'geopackage'
4. Fail if result set contains any non-NULL values
5. SELECT table_name FROM metadata_reference WHERE reference_scope !=\'geopackage'AND

table_name

NOT

IN

(SELECTtable_name

FROM

gpkg_contents)
6. Fail if result set is not empty
7. Pass otherwise.
Reference

Annex F.8 Req 97

Test TypeCapability

Test Case ID/extensions/metadata/metadata_reference/column_name

Test Purpose

Verify that gpkg_metadata_reference column_name column values are NULL forrows with reference scope values of 'geopackage', 'table', or 'row', and contain thename of a column in table_name table for other reference scope values.

Test Method

1. SELECT column_name FROM gpkg_metadata_reference
2. Not testable if returns an empty result set
3. SELECT

column_name

FROM

gpkg_metadata_reference

WHERE

reference_scope IN ('geopackage', 'table', 'row')
4. Fail if result set contains any non-NULL values
5. SELECT <table_name>, <column_name> FROM metadata_reference WHERE
reference_scope NOT IN ('geopackage', 'table', 'row')
6. For each row from step 5
a. SELECT sql FROM sqlite_master WHERE type = \'table' AND tbl_name =\'<table_name>'b. Fail if returns an empty result set.
c. Fail if the one of the column names in the returned sql Create TABLE
statement is not <column_name>d. Log pass otherwise
7. Pass if logged pass and no fails.
Reference

Annex F.8 Req 98

137

Test TypeCapability

Test Case ID/extensions/metadata/metadata_reference/row_id_value

Test Purpose

Verify that gpkg_metadata_reference row_id_value column values are NULL forrows with reference scope values of 'geopackage', 'table', or 'row', and contain the
ROWID of a row in the table_name for other reference scope values.

Test Method

1. SELECT row_id_value FROM gpkg_metadata_reference
2. Not testable if returns an empty result set
3. SELECT

row_id_value

FROM

gpkg_metadata_reference

WHERE

reference_scope IN ('geopackage', 'table', 'row')
4. Fail if result set contains any non-NULL values
5. Foreach

SELECT<table_name>,<row_id_value>

FROM

gpkg_metadata_reference WHERE reference_scope NOT IN ('geopackage','table', 'row')
6. For each row from step 5
a. SELECT * FROM <table_name> WHERE ROWID = <row_id_value>b. Fail if result set is empty
c. Log pass otherwise
7. Pass if logged pass and no fails.
Reference

Annex F.8 Req 99

Test TypeCapability

Test Case ID/extensions/metadata/metadata_reference/timestamp

Test Purpose

Verify that every gpkg_metadata_reference table row timestamp column value isin ISO 8601 UTC format.

Test Method

1. SELECT timestamp from gpkg_metadata_reference.
2. Not testable if returns an empty result set
3. For each row from step 1
a. Fail

ifformatofreturned

ddThh:mm:ss.hhhZ
b. Log pass otherwise
4. Pass if logged pass and no fails.

138

value

does

not

match

yyyy-mm-Reference

Annex F.8 Req 100

Test TypeCapability

Test Case ID/extensions/metadata/metadata_reference/md_file_id

Test Purpose

Verify that every gpkg_metadata_reference table row md_file_id column valuereferences a gpkg_metadata id column value.

Test Method

1. PRAGMA foreign_key_check('geometry_columns')
2. Fail if returns any rows with a fourth column foreign key index value of 0

Reference

Annex F.8 Req 101

Test TypeCapability

Test Case ID/extensions/metadata/metadata_reference/md_parent_id

Test Purpose

Verify that every gpkg_metadata_reference table row md_parent_id column valuethat is not null is an id column value from the gpkg_metadata_table that is notequal to the md_file_id column value for that row.

Test Method

1. SELECT md_file_id FROM gpkg_metadata_reference
2. Not testable if returns an empty result set
3. SELECT gmr.md_file_id, gmr.md_parent_id FROM gpkg_metadata_reference
AS gmr WHERE gmr.md_file_id == gmr.md_parent_id
4. Fail if result set is not empty
5. SELECT

gmr.md_file_id,

gmr.md_parent_id,

gm.id

FROM

gpkg_metadata_reference AS gmr LEFT OUTER JOIN gpkg_metadata gm ON
gmr.md_parent_id =gm.id
6. Fail if any result set gm.id values are NULL
7. Pass otherwise
Reference

Annex F.8 Req 102

Test TypeCapability

Table Definition SQL
gpkg_metadata139

gpkg_metadata Table Definition SQL

CREATE TABLE gpkg_metadata (id INTEGER CONSTRAINT m_pk PRIMARY KEY ASC NOT NULL,
md_scope TEXT NOT NULL DEFAULT 'dataset',
md_standard_uri TEXT NOT NULL,
mime_type TEXT NOT NULL DEFAULT 'text/xml',
metadata TEXT NOT NULL DEFAULT '');gpkg_metadata_reference
gpkg_metadata_reference Table Definition SQL

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

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

Trigger Definition SQL (Informative)
metadata

140

metadata Trigger Definition SQL

CREATE TRIGGER 'gpkg_metadata_md_scope_insert'
BEFORE INSERT ON 'gpkg_metadata'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'insert on table gpkg_metadata violates
constraint: md_scope must be one of undefined | fieldSession |collectionSession | series | dataset | featureType | feature |attributeType | attribute | tile | model | catalog | schema |taxonomy software | service | collectionHardware |nonGeographicDataset | dimensionGroup')
WHERE NOT(NEW.md_scope IN('undefined','fieldSession','collectionSession','series','dataset','featureType','feature','attributeType','attribute','tile','model','catalog','schema','taxonomy','software','service','collectionHardware','nonGeographicDataset','dimensionGroup'));END
CREATE TRIGGER 'gpkg_metadata_md_scope_update'
BEFORE UPDATE OF 'md_scope' ON 'gpkg_metadata'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'update on table gpkg_metadata violates
constraint: md_scope must be one of undefined | fieldSession |collectionSession | series | dataset | featureType | feature |attributeType | attribute | tile | model | catalog | schema |taxonomy software | service | collectionHardware |nonGeographicDataset | dimensionGroup')
WHERE NOT(NEW.md_scope IN('undefined','fieldSession','collectionSession','series','dataset','featureType','feature','attributeType','attribute','tile','model','catalog','schema','taxonomy','software','service','collectionHardware','nonGeographicDataset','dimensionGroup'));END

metadata_reference
gpkg_metadata_reference Trigger Definition SQL

CREATE TRIGGER 'gpkg_metadata_reference_reference_scope_insert'
BEFORE INSERT ON 'gpkg_metadata_reference'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'insert on table gpkg_metadata_reference
violates constraint: reference_scope must be one of "geopackage",table", "column", "row", "row/col"')
WHERE NOT NEW.reference_scope IN('geopackage','table','column','row','row/col');
END
CREATE TRIGGER 'gpkg_metadata_reference_reference_scope_update'
BEFORE UPDATE OF 'reference_scope' ON 'gpkg_metadata_reference'141

FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'update on table gpkg_metadata_reference
violates constraint: referrence_scope must be one of "geopackage","table", "column", "row", "row/col"')
WHERE NOT NEW.reference_scope IN('geopackage','table','column','row','row/col');
END
CREATE TRIGGER 'gpkg_metadata_reference_column_name_insert'
BEFORE INSERT ON 'gpkg_metadata_reference'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'insert on table gpkg_metadata_reference
violates constraint: column name must be NULL when reference_scopeis "geopackage", "table" or "row"')
WHERE (NEW.reference_scope IN ('geopackage','table','row')
AND NEW.column_name IS NOT NULL);SELECT RAISE(ABORT, 'insert on table gpkg_metadata_reference
violates constraint: column name must be defined for the specified
table when reference_scope is "column" or "row/col"')
WHERE (NEW.reference_scope IN ('column','row/col')AND NOT NEW.table_name IN (SELECT name FROM SQLITE_MASTER WHERE type = 'table'
AND name = NEW.table_name
AND sql LIKE ('%' || NEW.column_name || '%')));END
CREATE TRIGGER 'gpkg_metadata_reference_column_name_update'
BEFORE UPDATE OF column_name ON 'gpkg_metadata_reference'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'update on table gpkg_metadata_reference
violates constraint: column name must be NULL when reference_scopeis "geopackage", "table" or "row"')
WHERE (NEW.reference_scope IN ('geopackage','table','row')
AND NEW.column_nameIS NOT NULL);SELECT RAISE(ABORT, 'update on table gpkg_metadata_reference
violates constraint: column name must be defined for the specified
table when reference_scope is "column" or "row/col"')
WHERE (NEW.reference_scope IN ('column','row/col')AND NOT NEW.table_name IN (SELECT name FROM SQLITE_MASTER WHERE type = 'table'
AND name = NEW.table_name
AND sql LIKE ('%' || NEW.column_name || '%')));END
CREATE TRIGGER 'gpkg_metadata_reference_row_id_value_insert'
BEFORE INSERT ON 'gpkg_metadata_reference'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'insert on table gpkg_metadata_reference
violates constraint: row_id_value must be NULL when reference_scopeis "geopackage", "table" or "column"')
WHERE NEW.reference_scope IN ('geopackage','table','column')

142AND NEW.row_id_value IS NOT NULL;
END
CREATE TRIGGER 'gpkg_metadata_reference_row_id_value_update'
BEFORE UPDATE OF 'row_id_value' ON 'gpkg_metadata_reference'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'update on table gpkg_metadata_reference
violates constraint: row_id_value must be NULL when reference_scopeis "geopackage", "table" or "column"')
WHERE NEW.reference_scope IN ('geopackage','table','column')
AND NEW.row_id_value IS NOT NULL;
END
CREATE TRIGGER 'gpkg_metadata_reference_timestamp_insert'
BEFORE INSERT ON 'gpkg_metadata_reference'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'insert on table gpkg_metadata_reference
violates constraint: timestamp must be a valid time in ISO 8601
"yyyy-mm-ddThh:mm:ss.cccZ" form')
WHERE NOT (NEW.timestamp GLOB'[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]T[0-2][0-9]:[0-5][09]:[0-5][0-9].[0-9][0-9][0-9]Z'
AND strftime('%s',NEW.timestamp) NOT NULL);END
CREATE TRIGGER 'gpkg_metadata_reference_timestamp_update'
BEFORE UPDATE OF 'timestamp' ON 'gpkg_metadata_reference'
FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'update on table gpkg_metadata_reference
violates constraint: timestamp must be a valid time in ISO 8601
"yyyy-mm-ddThh:mm:ss.cccZ" form')
WHERE NOT (NEW.timestamp GLOB'[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]T[0-2][0-9]:[0-5][09]:[0-5][0-9].[0-9][0-9][0-9]Z'
AND strftime('%s',NEW.timestamp) NOT NULL);
END

Examples (Informative)
Hierarchical Metadata Example One - ISO19115.
Suppose we have this metadata:

CREATE TABLE gpkg_metadata (id INTEGER NOT NULL PRIMARY KEY,
md_scope TEXT NOT NULL DEFAULT 'undefined',
md_standard_uri TEXT NOT NULL,
metadata TEXT NOT NULL
)

143

id

md_scope

md_standard_uri

metadata

0

undefined

http://www.isotc211.org TEXT
/2005/gmd

3

serieshttp://www.isotc211.org TEXT
/2005/gmd

4

datasethttp://www.isotc211.org TEXT
/2005/gmd

5

featureType

http://www.isotc211.org TEXT
/2005/gmd

6

featurehttp://www.isotc211.org TEXT
/2005/gmd

7

attributeType

http://www.isotc211.org TEXT
/2005/gmd

8

attribute

http://www.isotc211.org TEXT
/2005/gmd

and this reference table definition:

CREATE TABLE gpkg_metadata_reference (reference_scope TEXT NOT NULL,
table_name TEXT,
column_name TEXT,
row_id_value INTEGER,
timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),md_file_id INTEGER NOT NULL,
md_parent_id INTEGER,CONSTRAINT crmr_mfi_fk FOREIGN KEY (md_file_id) REFERENCES gpkg_metadata(id),CONSTRAINT crmr_mpi_fk FOREIGN KEY (md_parent_id) REFERENCES gpkg_metadata(id))1) Consider a geographic data provider generating vector mapping data for three Administrative
areas(A, B and C). … The metadata could be carried exclusively at Dataset Series level.
Then we need a record for each layer table for the three admin areas, like this:INSERT INTO gpkg_metadata_reference VALUES ('table', /* reference type */'roads', /* table name */'undefined', /* column_name */-1, /* row_id_value */(datetime('now')),
3, /* md_file_id */0 /* md_parent_id */)2) After some time alternate vector mapping of Administrative area A becomes available. The

144

metadata would then be extended for Administrative area A, to describe the new quality datevalues. These values would supersede those given for the Dataset series, but only for
Administrative area A. The metadata for B and C would remain unchanged. This new metadatawould be recorded at Dataset level.
Then we need a record for each layer table in "A" like this:INSERT INTO gpkg_metadata_reference VALUES ('table', /* reference type */'roads', /* table name */'undefined', /* column_name */-1, /* row_id_value */(datetime('now')),
4, /* md_file_id */3 /* md_parent_id */)3) Eventually further data becomes available for Administrative area A, with a complete re-surveyof the road network. Again this implies new metadata for the affected feature types. This metadatawould be carried at Feature type level for Administrative area A. All other metadata relating toother feature types remains unaffected. Only the metadata for roads in Administrative area A is
modified. This road metadata is recorded at Feature type level.
Then we need a record for each layer table for the roads network, like this:INSERT INTO gpkg_metadata_reference VALUES ('table', /* reference type */'roads', /* table name */'undefined', /* column_name */-1, /* row_id_value */(datetime('now')),
5, /* md_file_id */4 /* md_parent_id */)4) An anomaly in the road survey is identified, in that all Overhead clearances for the
Administrative area A have been surveyed to the nearest metre. These are re-surveyed to thenearest decimetre. This re-survey implies new metadata for the affected attribute type 'Overhead
Clearance'. All other metadata for Administrative area A remains unaffected. This 'Overhead
Clearance' metadata is recorded at Attribute Type level.
Then we need a record for each layer table in the roads network with attribute type 'Overhead
Clearance', like this;145

INSERT INTO gpkg_metadata_reference VALUES ('column', /* reference type */'roads', /* table name */'overhead_clearance', /* column_name */-1, /* row_id_value */(datetime('now')),
7, /* md_file_id */4 /* md_parent_id */)5) A new bridge is constructed in Administrative area A. This new data is reflected in thegeographic data for Administrative area A, and new metadata is required to record this newfeature. All other metadata for Administrative area A remains unaffected. This new feature
metadata is recorded at Feature instance level.
Then we need a record for the bridge layer table row for the new bridge, like this:INSERT INTO gpkg_metadata_reference VALUES ('row', /* reference type */'bridge', /* table name */'undefined', /* column_name */987, /* row_id_value */(datetime('now')),
6, /* md_file_id */4 /* md_parent_id */)6) The overhead clearance attribute of the new bridge was wrongly recorded, and is modified.
Again this new attribute requires new metadata to describe the modification. All other metadata
for Administrative area A remains unaffected. This new attribute metadata is recorded at Attributeinstance level.
Then we need a record for the clearance attribute value, like this:INSERT INTO gpkg_metadata_reference VALUES ('row/col', /* reference type */'bridge', /* table name */'overhead_clearance', /* column_name */987, /* row_id_value */(datetime('now')),
8, /* md_file_id */4 /* md_parent_id */)Hierarchical Metadata Example Two - Field Data Collection
This use case demonstrates a mechanism to indicate which data in a GeoPackage that was146originally loaded with data from one or more services has been collected or updated since theinitial load, and therefore MAY need to be uploaded to update the original services (e.g. WFS, WCS,
WMTS).
Suppose a user with a mobile handheld device goes out in the field and collects observations of anew "Point of Interest" (POI) feature type, and associated metadata about the field session, the newfeature type, some POI instances and some of their attributes (e.g. spatial accuracy, attributeaccuracy) that results in the following additional metadata:
idmd_scope

md_standard_uri

metadata

1

fieldSession

http://schemas.opengis. TEXT
net/iso/19139/

10

featureType

http://schemas.opengis. TEXT
net/iso/19139/

11

featurehttp://schemas.opengis. TEXT
net/iso/19139/

12

attribute

http://schemas.opengis. TEXT
net/iso/19139/

13

attribute

http://schemas.opengis. TEXT
net/iso/19139/

14

featurehttp://schemas.opengis. TEXT
net/iso/19139/

15

attribute

http://schemas.opengis. TEXT
net/iso/19139/

16

attribute

http://schemas.opengis. TEXT
net/iso/19139/

17

featurehttp://schemas.opengis. TEXT
net/iso/19139/

18

attribute

http://schemas.opengis. TEXT
net/iso/19139/

19

attribute

http://schemas.opengis. TEXT
net/iso/19139/(This example assumes that the field session data is still considered "raw" and won’t be considereda data set or part of a data series until it has been verified and cleaned, but if that is wrong thenadditional series and data set metadata could be added.)
Then we need a gpkg_metadata_reference record for the field session for the new POI table, whose
md_parent_id is undefined:

147

INSERT INTO gpkg_metadata_reference VALUES ('table', /* reference type */'poi', /* table name */'undefined', /* column_name */-1, /* row_id_value */(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
1, /* md_file_id */0 /* md_parent_id */)Then we need a gpkg_metadata_reference record for the feature type for the new POI table, whose
md_parent_id is that of the field session:

INSERT INTO gpkg_metadata_reference VALUES ('table', /* reference type */'poi', /* table name */'undefined', /* column_name */-1, /* row_id_value */(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
10, /* md_file_id */1 /* md_parent_id */)Then we need gpkg_metadata_reference records for the poi feature instance rows, whose
md_parent_id is that of the field session:

148

INSERT INTO gpkg_metadata_reference VALUES ('row', /* reference type */'poi', /* table name */'undefined', /* column_name */1, /* row_id_value */(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
11, /* md_file_id */1 /* md_parent_id */)INSERT INTO gpkg_metadata_reference VALUES ('row', /* reference type */'poi', /* table name */'undefined', /* column_name */2, /* row_id_value */14, /* md_file_id */1 /* md_parent_id */)INSERT INTO gpkg_metadata_reference VALUES ('row', /* reference type */'poi', /* table name */'undefined', /* column_name */3, /* row_id_value */(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
17, /* md_file_id */1 /* md_parent_id */)And finally we need gpkg_metadata_reference records for the poi attribute instance metadata ,whose md_parent_id is that of the field session:

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

149

1
)

/* md_parent_id*/

INSERT INTO gpkg_metadata_reference VALUES ('row/col', /* reference type */'poi', /* table name */'point', /* column_name */3, /* row_id_value */(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
18, /* md_file_id */1 /* md_parent_id */)INSERT INTO gpkg_metadata_reference VALUES ('row/col', /* reference type */'poi', /* table name */'category', /* column_name */1, /* row_id_value */(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
13, /* md_file_id */1 /* md_parent_id */)INSERT INTO gpkg_metadata_reference VALUES ('row/col', /* reference type */'poi', /* table name */'category', /* column_name */2, /* row_id_value */(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
16, /* md_file_id */1 /* md_parent_id */)INSERT INTO gpkg_metadata_reference VALUES ('row/col', /* reference type */'poi', /* table name */'category', /* column_name */3, /* row_id_value */(strftime('%Y-%m-%dT%H:%M:%fZ','now')),
19, /* md_file_id */1 /* md_parent_id */)As long as all metadata collected in the field session either directly (as above) or indirectly (suppose
there were a data set level metadata_reference record intermediary) refers to the field session
metadata via md_parent_id values, then this chain of metadata references identifies the newly
collected information, as Joan requested, in addition to the metadata.
So here is the data after both examples:
Table 21. xml_metadata

150

id

md_scope

md_standard_uri

metadata

0

undefined

http://www.isotc211.org TEXT
/2005/gmd

1fieldSession

http://www.isotc211.org TEXT
/2005/gmd

2

collectionSession

http://www.isotc211.org TEXT
/2005/gmd

3

serieshttp://www.isotc211.org TEXT
/2005/gmd

4

datasethttp://www.isotc211.org TEXT
/2005/gmd

5

featureType

http://www.isotc211.org TEXT
/2005/gmd

6

featurehttp://www.isotc211.org TEXT
/2005/gmd

7

attributeType

http://www.isotc211.org TEXT
/2005/gmd

8

attribute

http://www.isotc211.org TEXT
/2005/gmd

10

featureType

http://www.isotc211.org TEXT
/2005/gmd

11

featurehttp://www.isotc211.org TEXT
/2005/gmd

12

attribute

http://www.isotc211.org TEXT
/2005/gmd

13

attribute

http://www.isotc211.org TEXT
/2005/gmd

14

featurehttp://www.isotc211.org TEXT
/2005/gmd

15

attribute

http://www.isotc211.org TEXT
/2005/gmd

16

attribute

http://www.isotc211.org TEXT
/2005/gmd

17

featurehttp://www.isotc211.org TEXT
/2005/gmd

18

attribute

http://www.isotc211.org TEXT
/2005/gmd

19

attribute

http://www.isotc211.org TEXT
/2005/gmd

Table 22. gpkg_metadata_reference151reference_t
ypetable_name column_namerow_id_valu timestampemd_file_id

md_parent_i
d

table

roads

undefined

0

ts

3

0

table

roads

undefined

0

ts

4

3

table

roads

undefined

0

ts

5

4

columnroads

overhead_cle 0
arance

ts

7

4

row

bridgeundefined

987

ts

6

4

row/col

bridgeoverhead_cle 987
arance

ts

8

4

table

poi

undefined

0

ts

1

0

row

poi

undefined

0

ts

10

1

row

poi

undefined

1

ts

11

1

row

poi

undefined

2

ts

14

1

row/colpoi

undefined

3

ts

17

1

row/col

poi

point

1

ts

12

1

row/col

poi

point

2

ts

15

1

row/col

poi

point

3

ts

18

1

row/colpoi

category

1

ts

13

1

row/colpoi

category

2

ts

16

1

row/colpoi

category

3

ts

19

1

Raster or Tile Metadata Example
A number of raster image processing problems MAY require the support of more metadata that iscontained in the image itself. Applications MAY use the gpkg_metadata and gpkg_metadata_referencetables defined in clause metadata to store raster image metadata defined according to standard
authoritative or application or vendor specific metadata models. An example of the data items insuch a model is shown in the following table.
• Rational Polynomial Coefficient• Photometric Interpretation• No Data Value• Compression Quality Factor• Georectification• NIIRS• Min X
• Min Y
• Max X
• Max Y152

F.9. Schema
Introduction
The schema option provides a means to describe the columns of tables in a GeoPackage with moredetail than can be captured by the SQL table definition directly. The information provided by thisoption can be used by applications to, for instance, present data contained in a GeoPackage in amore user-friendly fashion or implement data validation logic.

Extension Author
GeoPackage SWG, author_name gpkg

Extension Name
gpkg_schema

Extension TypeNew Requirement Dependent on Clause Features

Applicability
This extension may apply to any Vector Feature User Data Tables.

Scope
Read-write

Requirements
Table Definitions
Data Columns

Requirement 103

A GeoPackage MAY contain a table or updateable view named gpkg_data_columns. If present it
SHALL be defined per Data Columns Table or View Definition and gpkg_data_columns Table
Definition SQL.
Table 23. Data Columns Table or View Definition

Column Name

Column Type

ColumnDescription

Null

Key

table_name

TEXT

Name of the tiles
or feature table

no

PK

153

Column Name

Column Type

ColumnDescription

Null

Key

column_name

TEXT

Name of the table
column

no

PK

name

TEXT

A human-readable yes
identifier (e.g.
short name) forthe column_namecontent

title

TEXT

A human-readable yes
formal title for thecolumn_namecontent

description

TEXT

A human-readable yes
description for thecolumn_namecontent

mime_typeTEXT

MIME [21] type ofcolumn_name if
BLOB type, or
NULL for othertypes

constraint_name

TEXT

Column value
yes
constraint name(lowercase)specified byreference to
gpkg_data_column_c
onstraints.constra
int name

GeoPackage applications MAY[K38]

UNIQUEyes

use the gpkg_data_columns table to store minimal application

schema identifying, descriptive and MIME [21] type[K39]

information about columns in user vector

feature and tile matrix data tables that supplements the data available from the SQLite
sqlite_master table and pragma table_info(table_name) SQL function. The gpkg_data_columns data
CAN be used to provide more specific column data types and value ranges and application specificstructural and semantic information to enable more informative user menu displays and more
effective user decisions on the suitability of GeoPackage contents for specific purposes.
See gpkg_data_columns Table Definition SQL.
Data Column Constraints

Requirement 107

A GeoPackage MAY contain a table or updateable view named gpkg_data_column_constraints.
If present it SHALL be defined per Data Column Constraints Table or View Definition and
gpkg_data_columns Table Definition SQL.

154

The gpkg_data_column_constraints table contains data to specify restrictions on basic data typecolumn values. The constraint_name column is referenced by the constraint_name column in the
gpkg_data_columns table defined in Data Columns Table or View Definition.
Table 24. Data Column Constraints Table or View Definition

Column Name

Column Type

ColumnDescription

Null

Key

constraint_name

TEXT

Name ofconstraint(lowercase)noUniqueconstraint_type

TEXT

Type name ofnoconstraint: 'range'| 'enum' | 'glob'

Unique

value

TEXT

Specified caseyessensitive value for'enum' or 'glob' or
NULL for 'range'
constraint_type

Uniquemin

NUMERIC

Minimum value
for 'range' or
NULL for 'enum'
or 'glob'
constraint_type

min_is_inclusive

BOOLEAN0 (false) if min
yesvalue is exclusive,or 1 (true) if minvalue is inclusive

max

NUMERIC

Maximum value
for 'range' or
NULL for 'enum'
or 'glob'
constraint_type

max_is_inclusive

BOOLEAN0 (false) if max
yesvalue is exclusive,or 1 (true) if max
value is inclusive

description

TEXT

For ranges andyesglobs, describes
the constraint; forenums, describes
the enum value.

yes

yes

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

155

Table Data Values
gpkg_extensions

Requirement 141

GeoPackages with a row in the gpkg_extensions table with an extension_name of "gpkg_schema"
SHALL comply with this extension. The row SHALL have a scope of "read-write".

Data Columns

Requirement 104

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

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

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

Data Column Constraints

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

constraint_
nameconstraint_t valueypesampleRang range
e

156

NULL

min

min_is_incl
usive

max

max_is_incl
usive

1

true

10

true

constraint_
nameconstraint_t value
ype

min

min_is_incl
usive

max

max_is_incl
usivesampleEnum enum

1

NULL

NULL

NULL

NULL

sampleEnum enum

3

NULL

NULL

NULL

NULL

sampleEnum enum

5

NULL

NULL

NULL

NULL

sampleEnum enum

7

NULL

NULL

NULL

NULL

sampleEnum enum

9

NULL

NULL

NULL

NULL

sampleGlob[1-2][0-9][09][0-9]

NULL

NULL

NULL

NULL

glob

Requirement 108

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

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

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

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

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

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

157

Requirement 114

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

Abstract Test Suite
Table Definition
Data Columns

Test Case ID/extensions/schema/data_columns/table_def

Test Purpose

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

Test Method

1. PRAGMA table_info(gpkg_data_columns)
2. Fail if returns an empty result set
3. Fail if column names and column definitions in the returned table_info do notmatch those of Table 23, including data type, nullability, default values.
Column order, check constraint and trigger definitions, and other column
definitions in the returned sql are irrelevant.
4. Pass if no failures.

Reference

Annex F.9 Req 103

Test TypeBasic

Data Column Constraints
Test Case ID/extensions/schema/data_column_constraints/table_def

Test Purpose

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

Test Method

1. PRAGMA table_info(gpkg_data_column_constraints)
2. Fail if returns an empty result set
3. Fail if column names and column definitions in the returned table_info do notmatch those of Table 23, including data type, nullability, default values.
Column order, check constraint and trigger definitions, and other column
definitions in the returned sql are irrelevant.
4. Pass if no failures.

158

Reference

Annex F.9 Req 107

Test TypeBasic

Data Values
gpkg_extensions

Test Case ID/extensions/schema/extensions/data_values_scope

Test Purpose

Verify that the gpkg_extensions table has the required row.

Test Method

1. SELECT

scope

FROM

gpkg_extensions

WHERE

extension_name='gpkg_schema';
2. Not testable if returns an empty result set
3. For each row returned from step 1
a. Fail if scope is not "read-write".
4. Pass if no fails
Reference

Annex F.9 Req 141

Test Type:Capabilities

Test Case ID/extensions/schema/data_columns/table_name

Test Purpose

Verify that for each gpkg_data_columns row, the table_name value matches a row
in gpkg_contents.

Test Method

1. SELECT DISTINCT gdc.table_name AS gdc_table, gc.table_name AS gc_table
FROM gpkg_data_columns AS gdc LEFT OUTER JOIN gpkg_contents AS gc ON
gdc.table_name = gc.tbl_name;
2. Not testable if returns an empty result set
3. For each row from step 1
a. Fail if gc_table is NULL.
4. Pass if no fails.

Reference

Annex F.9 Req 104

Test TypeCapability

159

Test Case ID/extensions/schema/data_columns/column_name

Test Purpose

Verify that for each gpkg_data_columns row, the column_name matches a column
in the table or view identified by the table_name column value.

Test Method

1. SELECT table_name, column_name FROM gpkg_data_columns
2. Not testable if returns an empty result set
3. For each row from step 1
a. PRAGMA table_info(table_name)b. Fail if table_name does not contain a column matching column_name
4. Pass if no fails

Reference

Annex F.9 Req 105

Test TypeCapability

Test Case ID/extensions/schema/data_columns/constraint_name

Test Purpose

Verify that for each gpkg_data_columns row, if the constraint_name value is NOTNULL then the constraint_type column value contains a constraint_type column
value from the gpkg_data_column_constraints table for a row with a matching
constraint_name value.

Test Method

1. SELECT

constraint_name

AS

cn,

constraint_type

AS

ct

FROM

gpkg_data_columns
2. Not testable if returns an empty result set
3. For each NOT NULL cn value from step 1
a. Fail if ct is NULL
b. If

ct

NOT

NULL,

gpkg_data_column_constraints

SELECT
WHERE

constraint_type = ct
c. Fail if returns an empty result set
4. Pass if no fails
Reference

Annex F.9 Req 106

Test TypeCapability

Data Column Constraints

160

constraint_type
constraint_name

=

FROM
cn

AND

Test Case ID/extensions/schema/data_column_constraints/constraint_type

Test Purpose

Verify that the gpkg_data_column_constraints constraint_type column values are
one of "range", "enum", or "glob".

Test Method

1. SELECT DISTINCT constraint_type FROM gpkg_data_column_constraints
2. Not testable if returns an empty result set
3. For each constraint_type value returned by step 1
a. Fail if constraint_type NOT IN ("range", "enum", "glob").
4. Pass if no fails.

Reference

Annex F.9 Req 108

Test TypeCapability

Test Case ID/extensions/schema/data_column_constraints/constraint_names_unique

Test Purpose

Verify that the gpkg_data_column_constraints constraint_name column values forconstraint_type values of "range", or "glob" are unique.

Test Method

1. SELECT DISTINCT constraint_name FROM gpkg_data_column_constraints
WHERE constraint_type IN ('range', 'glob')a. For each returned constraint_name cn
b. SELECT

count(*)

FROM

gpkg_data

column_constraints

WHERE

constraint_name = cn
c. Fail if count > 1
2. Pass if no fails.
Reference

Annex F.9 Req 109

Test TypeCapability

Test Case ID/extensions/schema/data_column_constraints/value_for_range

Test Purpose

Verify that the gpkg_data_column_constraints value column values are NULL forrows with a constraint_type value of "range".

161

Test Method

1. SELECT constraint_name, value FROM gpkg_data_column_constraints WHERE
constraint_type = 'range'
2. Not testable if returns an empty result set
3. For each value returned by step 1
a. Fail if value IS NOT NULL
4. Pass if no fails.

Reference

Annex F.9 Req 110

Test TypeCapability

Test Case ID/extensions/schema/data_column_constraints/min_max_for_range

Test Purpose

Verify that the gpkg_data_column_constraints min column values are NOT NULL
and less than the max column values for rows with a constraint_type value of"range".

Test Method

1. SELECT constraint_name, min, max FROM gpkg_data_column_constraints
WHERE constraint_type = 'range'
2. Not testable if returns an empty result set
3. For each set of min and max values returned by step 1
a. Fail if min IS NULL
b. Fail if max IS NULL
c. Fail if min >= max
4. Pass if no fails.

Reference

Annex F.9 Req 111

Test TypeCapability

Test Case ID/extensions/schema/data_column_constraints/inclusive_for_range

Test Purpose

Verifythatthegpkg_data_column_constraints

min_is_inclusiveand

max_is_inclusive column values are NOT NULL and either 0 or 1 for rows with aconstraint_type value of "range".

162

Test Method

1. SELECT

constraint_name,min_is_inclusive,

max_is_inclusive

FROM

gpkg_data_column_constraints WHERE constraint_type = 'range'
2. Not testable if returns an empty result set
3. For each set of values returned by step 1
a. Fail if min_is_inclusive IS NULL
b. Fail if max_is_inclusive IS NULL
c. Fail if min_is_inclusive is NOT IN (0,1)
d. Fail if max_is_inclusive is NOT IN (0,1)
4. Pass if no fails.
Reference

Annex F.9 Req 112

Test TypeCapability

Test Case ID:/extensions/schema/data_column_constraints/min_max_inclusive_for_enum_glob

Test Purpose:

Verify that the gpkg_data_column_constraints min, max, min_is_inclusive andmax_is_inclusive column values are NULL for rows with a constraint_type valueof "enum" or "glob".

Test Method:

1. SELECT constraint_name, min, max, min_is_inclusive, max_is_inclusive FROM
gpkg_data_column_constraints WHERE constraint_type IN ('enum','glob')
2. Not testable if returns an empty result set
3. For each set of values returned by step 1
a. Fail if min IS NOT NULL
b. Fail if max IS NOT NULL
c. Fail if min_is_inclusive IS NOT NULL
d. Fail if max_is_inclusive IS NOT NULL
4. Pass if no fails.

Reference

Annex F.9 Req 113

Test TypeCapability

Test Case ID:/extensions/schema/data_column_constraints/value_for_enum_glob

Test Purpose:

Verify that the gpkg_data_column_constraints value column values are NOT NULL
for rows with a constraint_type value of "enum" or "glob".

163

Test Method:

1. SELECT value FROM gpkg_data_column_constraints WHERE constraint_type
IN ('enum','glob')
2. Not testable if returns an empty result set
3. For each value returned by step 1
a. Fail if value IS NULL
4. Pass if no fails.

Reference

Annex F.9 Req 114

Test TypeCapability

Table Definition SQL
gpkg_data_columns
gpkg_data_columns Table Definition SQL

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

gpkg_data_column_constraints
gpkg_data_columns Table Definition SQL

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

F.10. WKT for Coordinate Reference Systems
Introduction
The OGC GeoPackage standard was adopted prior to the adoption of "OGC Well known text
representation of Coordinate Reference Systems" [34], in 13 August, 2014. As a result, the OGC
GeoPackage standard references an older document [32] which has known ambiguities that are
being encountered in the field. This extension establishes a new column to contain values that
conform to the new standard.

Extension Author
GeoPackage SWG, author_name gpkg.

Extension Name or Template
gpkg_crs_wkt

Extension Type
Extension of Existing Requirement in clause Table Definition.

Applicability
Applies to the gpkg_spatial_ref_sys table.

Scope
Read-write

Requirements
Table Definition
gpkg_spatial_ref_sys

Requirement 115

For GeoPackages conforming to this extension, the gpkg_spatial_ref_sys table SHALL have anadditional column called definition_12_063 as per Spatial Ref Sys Table Definition and
gpkg_spatial_ref_sys Table Definition SQL (CRS WKT Extension).
Table 26. Spatial Ref Sys Table Definition

Column Name Column Type

ColumnDescription

srs_nameHumanyesreadable nameof this SRS

TEXTNot NullDefaultKey

165

Column Name Column Type

ColumnDescription

Not Null

srs_idINTEGER

Uniqueidentifier foreach Spatial
Reference
System withina GeoPackageyesorganization

TEXT

Caseinsensitive
name of thedefining
organization
e.g. EPSG or
epsg

yes

organization_c INTEGER
oordsys_id

Numeric ID of yes
the Spatial
Reference
Systemassigned by theorganization

definition

TEXT

Well-known
yes
Text [32]Representationof the Spatial
Reference
Systemdescription

TEXT

Human
readable
description ofthis SRS

definition_12_ TEXT
063

Default

Key
PK'undefined'noWell-known
yes
Text [34]Representationof the Spatial
Reference
System'undefined'

Table Data Values
gpkg_extensions

Requirement 145

GeoPackages with a row in the gpkg_extensions table with an extension_name of "gpkg_crs_wkt"
SHALL comply with this extension. The row SHALL have a scope of "read-write".

gpkg_spatial_ref_sys

166Requirement 116

Values of the definition_12_063 column SHALL be constructed per the WKT syntax in [34].
Requirement 117

At least one definition column SHALL be defined with a valid definition unless the value of thesrs_id column is 0 or -1. Both columns SHOULD be defined. If it is not possible to produce a
valid [32] definition then the value of the definition column MAY be undefined. If it is notpossible to produce a valid [34] definition then the value of the definition_12_063 column MAY
be undefined.
If, for a particular row, both the definition and definition_12_063 columns are populated, the value
in the definition_12_063 column takes priority.

Abstract Test Suite
Table Definition
Table Definition
Test Case ID/extension_crs_wkt/table_def

Test Purpose

Verify that the gpkg_spatial_ref_sys table exists and has the correct definition.
Extends /base/core/gpkg_spatial_ref_sys/data/table_def.

Test Method

1. PRAGMA table_info('gpkg_spatial_ref_sys')
2. Fail if returns an empty result set
3. Fail if result set does not include a column named 'definition_12_063' or if thecolumn is not of type 'TEXT', notnull 1, and dflt_value 'undefined'.
4. Pass if no failures.

Reference

Annex F.10 Req 115

Test Type

Basic

Table Data Values
Test Case ID/extension_crs_wkt/data_values_default

Test Purpose

Verify that the gpkg_spatial_ref_sys table contains the required default contents.
Extends /base/core/gpkg_spatial_ref_sys/data_values_default.

167

Test Methodorganization,organization_coordsys_id,
1. SELECT
definition_12_063 FROM gpkg_spatial_ref_sys WHERE srs_id = -1

definition,a. Confirm that this returns "NONE" -1 "undefined" "undefined"
srs_id,organization,organization_coordsys_id,
2. SELECT
definition_12_063 FROM gpkg_spatial_ref_sys WHERE srs_id = 0

definition,a. Confirm that this returns "NONE" 0 "undefined" "undefined"
3. SELECT definition FROM gpkg_spatial_ref_sys WHERE("epsg","EPSG") AND organization_coordsys_id 4326

organizationIN

a. Confirm that this is a valid CRS
4. SELECT definition_12_063 FROM gpkg_spatial_ref_sys WHERE organization IN("epsg","EPSG") AND organization_coordsys_id 4326
a. Confirm that this is a valid 12-063 CRS
5. Pass if tests 1-4 are met
6. Fail otherwise
Reference

Annex F.10 Req 116

Test TypeCapability

Test Case ID/extension_crs_wkt/data_values_required

Test Purpose

Verify that the spatial_ref_sys table contains rows to define all srs_id values usedbyfeatures

and

tiles

in

a

GeoPackage.

Extends/base/core/gpkg_spatial_ref_sys/data_values_required.
Test Method

1. SELECT definition, definition_12_063
srs_id NOT IN (0, -1)

FROM

2. For each result
a. Fail if both definition values are 'undefined'
3. Pass if no failures
Reference

Annex F.10 Req 117

Test TypeCapability

Table Definition SQL
gpkg_spatial_ref_sys

168

gpkg_spatial_ref_sys

WHEREgpkg_spatial_ref_sys Table Definition SQL (CRS WKT Extension)

CREATE TABLE gpkg_spatial_ref_sys (srs_name TEXT NOT NULL,
srs_id INTEGER NOT NULL PRIMARY KEY,
organization TEXT NOT NULL,
organization_coordsys_id INTEGER NOT NULL,
definition TEXT NOT NULL DEFAULT 'undefined',
description TEXT,
definition_12_063 TEXT NOT NULL DEFAULT 'undefined');

F.11. Tiled Gridded Elevation DataOn May 2, 2017 the OGC Architecture Board (OAB) voted to remove this extension
from the this draft standard document so that it can be worked on separately. See
geopackage.org for more information on the separate document.

169

Annex G: Geometry Types (Normative)
Table 27. Geometry Type Codes (Core)

Code

Name

0

GEOMETRY

1

POINT

2

LINESTRING

3

POLYGON

4

MULTIPOINT

5

MULTILINESTRING

6

MULTIPOLYGON

7

GEOMETRYCOLLECTION

Table 28. Geometry Type Codes (Extension)

Code

Name

8

CIRCULARSTRING

9COMPOUNDCURVE

10CURVEPOLYGON

11

MULTICURVE

12

MULTISURFACE

13

CURVE

14

SURFACE

GEOMETRY subtypes are POINT, CURVE, SURFACE and GEOMCOLLECTION.
CURVE subtypes are LINESTRING, CIRCULARSTRING and COMPOUNDCURVE.
SURFACE subtype is CURVEPOLYGON.
CURVEPOLYGON subtype is POLYGON.
GEOMETRYCOLLECTION subtypes are MULTIPOINT, MULTICURVE and MULTISURFACE.
MULTICURVE subtype is MULTILINESTRING.
MULTISURFACE subtype is MULTIPOLYGON.

170

Annex H: Tiles Zoom Times Two Example(Informative)
Table 29. Zoom Times Two Example

table_nam zoom_leve matrix_wi matrix_he tile_width tile_height pixel_x_si pixel_y_siz
e
l
dth
ight
ze
e
MyTiles

0

8

8

512

512

69237.2

68412.1

MyTiles

1

16

16

512

512

34618.6

34206.0

MyTiles

2

32

32

512

512

17309.3

17103.0

MyTiles

3

64

64

512

512

8654.648654.64

MyTiles

4

128

128

512

512

4327.324275.75

MyTiles

5

256

256

512

512

2163.66

2137.87

MyTiles

6

512

512

512

512

1081.83

1068.93

MyTiles

7

1024

1024

512

512

540.915543.469

MyTiles

8

2048

2048

512

512

270.457

267.234

171

Annex I: Normative References (Normative)
The following normative documents contain provisions which, through reference in this text,
constitute provisions of OGC 12-128. For dated references, subsequent amendments to, or revisionsof, any of these publications do not apply. However, parties to agreements based on this part of OGC
12-128 are encouraged to investigate the possibility of applying the most recent editions of thenormative documents indicated below. For undated references, the latest edition of the normative
document referred to applies.
▪ [1] ISO/IEC 9075:1992 Information Technology - Database Language SQL (SQL92)▪ [2] ISO/IEC 9075-1:2011 Information Technology - Database Language SQL - Part 1: Framework▪ [3] ISO/IEC 9075-2:2011 Information Technology - Database Language SQL - Part 2: Foundation▪ [4] ISO/IEC 9075-3:2008 Information Technology - Database Language SQL - Part 3: Call- Level
Interface (SQL/CLI)▪ [5] SQLite (all parts) http://www.sqlite.org/ (online) http://www.sqlite.org/sqlite-doc-3071300.zip(offline)▪ [6] http://sqlite.org/fileformat2.html▪ [7] http://www.sqlite.org/formatchng.html▪ [8] http://www.sqlite.org/download.html▪ [9] OGC 06-103r4 OpenGIS® Implementation Standard for Geographic information - Simple
feature access - Part 1: Common architecture Version: 1.2.1 2011-05-28
http://portal.opengeospatial.org/files/?artifact_id=25355 (also ISO/TC211 19125 Part 1)▪ [10] OGC 06-104r4 OpenGIS® Implementation Standard for Geographic information - Simple
feature access - Part 2: SQL option Version: 1.2.1 2010-08-04 http://portal.opengeospatial.org/
files/?artifact_id=25354 (also ISO/TC211 19125 Part 2)▪ [11] OGC 99-049 OpenGIS® Simple Features Specification for SQL Revision 1.1 5, 1999, Clause
2.3.8 http://portal.opengeospatial.org/files/?artifact_id=829 May▪ [12] ISO/IEC 13249-3:2011 Information technology — SQL Multimedia and Application Packages- Part 3: Spatial (SQL/MM)▪ [13] http://www.epsg.org/Geodetic.html▪ [14] http://www.epsg-registry.org/▪ [15] MIL_STD_2401 DoD World Geodetic System 84 (WGS84), 11 January 1994
▪ [16] OGC 07-057r7 OpenGIS® Web Map Tile Service Implementation Standard Version 1.0.0
2010-04-06 (WMTS) http://portal.opengeospatial.org/files/?artifact_id=35326▪ [17] ITU-T Recommendation T.81 (09/92) with Corrigendum (JPEG)▪ [18] T.871 : Information technology - Digital compression and coding of continuous-tone still
images: JPEG File Interchange Format (JFIF), September 11, 2012 http://www.itu.int/rec/T-RECT.871-201105-I/en▪ [19] IETF RFC 2046 Multipurpose Internet Mail Extensions (MIME) Part Two: Media Types
http://www.ietf.org/rfc/rfc2046.txt

172▪ [20] Portable Network Graphics http://libpng.org/pub/png/▪ [21] MIME Media Types http://www.iana.org/assignments/media-types/index.html▪ [22] WebP https://developers.google.com/speed/webp/▪ [23] IETF RFC 3986 Uniform Resource Identifier (URI): Generic Syntax http://www.ietf.org/rfc/
rfc3986.txt▪ [24] W3C Recommendation 26 November 2008 Extensible Markup Language (XML) 1.0 (Fifth
Edition) http://www.w3.org/TR/xml/▪ [25] W3C Recommendation 28 October 2004 XML Schema Part 0: Primer Second Edition
http://www.w3.org/TR/xmlschema-0/▪ [26] W3C Recommendation 28 October 2004 XML Schema Part 1: Structures Second Edition
http://www.w3.org/TR/xmlschema-1/▪ [27] W3C Recommendation 28 October 2004 XML Schema Part 2: Datatypes Second Edition
http://www.w3.org/TR/xmlschema-2/▪ [28] ISO: 19115-1:2014 Geographic information — Metadata — Part 1: Fundamentals (2014)
https://www.iso.org/standard/53798.html▪ [29] ISO 8601 Representation of dates and times http://www.iso.org/iso/catalogue_detail?
csnumber=40874▪ [30] OGC® 10-100r3 Geography Markup Language (GML) simple features profile (with technicalnote) http://portal.opengeospatial.org/files/?artifact_id=42729▪ [31] SQLite R*Tree Module http://www.sqlite.org/rtree.html▪ [32] OpenGIS® 01-009 Implementation Specification: Coordinate Transformation Services
Revision 1.0 http://portal.opengeospatial.org/files/?artifact_id=999▪ [33] SQLite GLOB operator https://www.sqlite.org/lang_expr.html#glob▪ [34] OGC® 12-063r5 Geographic information - Well-known text representation of coordinate
reference systems http://docs.opengeospatial.org/is/12-063r5/12-063r5.html▪ [35] TIFF ™ Revision 6.0 Final — June 3, 1992 ftp://download.osgeo.org/libtiff/doc/TIFF6.pdf,©1986-1988, 1992 by Adobe® Systems Incorporated

173

Annex J: Bibliography (Informative)▪ [B1] http://developer.android.com/guide/topics/data/data-storage.html#db▪ [B2] https://developer.apple.com/technologies/ios/data-management.html▪ [B3] http://www.epsg.org/guides/docs/G7-1.pdf▪ [B4] http://en.wikipedia.org/wiki/ASCII▪ [B5] http://www.sqlite.org/lang_createtable.html#rowid▪ [B6] ISO 19115-2 Geographic information - - Metadata - Part 2: Metadata for imagery andgridded data
▪ [B7] ISO 19139: Geographic information — Metadata — XML schema implementation▪ [B8] Dublin Core Metadata Initiative http://dublincore.org/ IETF RFC 5013
▪ [B9] ISO 15836:2009 http://www.iso.org/iso/home/store/catalogue_tc/catalogue_detail.htm?
csnumber=52142▪ [B10] Content Standard for Digital Geospatial Metadata (CSDGM)▪ [B11] http://www.fgdc.gov/standards/projects/FGDC-standards-projects/metadata/base-metadata/index_html▪ [B12] Department of Defense Discovery Metadata Specification (DDMS) http://metadata.ces.mil/
mdr/irs/DDMS/▪ [B13] NMF NGA.STND.0012_2.0 / NMIS NGA.STND.0018_1.0▪ [B14] Unified Modeling Language (UML) http://www.uml.org/▪ [B15] XML for Metadata Interchange (XMI) http://www.omg.org/spec/XMI/▪ [B16] IDEF1x Data Modeling Method http://www.idef.com/IDEF1x.htm▪ [B17] Geography Markup Language (GML) ISO 19136:2007▪ [B18] ISO 19110 Geographic information – Methodology for feature cataloguing▪ [B19] RDF Vocabulary Description Language 1.0: RDF Schema http://www.w3.org/TR/rdf-schema/▪ [B20] Web Ontology Language (OWL) http://www.w3.org/TR/2009/REC-owl2-xml-serialization20091027/▪ [B21] Simple Knowledge Organization System (SKOS) http://www.w3.org/TR/skos-reference/▪ [B22] STANAG 7074 Digital Geographic Information Exchange Standard (DIGEST) - AGeoP-3A,
edition 1, 19 October 1994 http://www.dgiwg.org/dgiwg/htm/documents/
historical_documents.htm▪ [B23] ISO 19109 Geographic information - Rules for application schema▪ [B24] http://www.sqlite.org/changes.html▪ [B25] http://sqlite.org/src4/doc/trunk/www/design.wiki▪ [B26] OGC® 16-094r3 GeoPackage Elevation Extension Interoperability Experiment Engineering
Report, Draft Revision 3, 21 June 2016174

Annex K: Endnotes▪ [K1] SQLite version 4 (reference B25), which will be an alternative to version 3, not a
replacement thereof, was not available when this standard was written. See Future Work clausein Annex B.
▪ [K2] SQLite is in the public domain (see http://www.sqlite.org/copyright.html)▪ [K3] With SQLite versions 3.7.17 and later this value MAY be set with the "PRAGMA
application_id=1196444487;" SQL statement, where 1196444487 is the 32-bit integer value of
0x47504B47. With earlier versions of SQLite the application id can be set by writing the byte
sequence 0x47, 0x50, 0x4B, 0x47 at offset 68 in the SQLite database file (seehttp://www.sqlite.org/fileformat2.html#database_header for details).
▪ [K4] Older GeoPackages use a different versioning mechanism. Instead of using theuser_version, they have an application ID of "GP10" (for GeoPackage 1.0 and 1.0.1) or "GP11" (forGeoPackage 1.1).
▪ [K5] The SQLite PRAGMA integrity_check SQL command does a full database scan that can takea long time to complete on a large GeoPackage file.
▪ [K6] New applications should use the latest available SQLite version software [8]▪ [K7] The following statement selects an ISO 8601 timestamp value using the SQLite strftime
function: SELECT (strftime('%Y-%m-%dT%H:%M:%fZ','now')).
▪ [K8] GeometryCollection is a generic term for the ST_GeomCollection type defined in [12], whichuses it for the definition of Well Known Text (WKT) and Well Known Binary (WKB) encodings.
The SQL type name GEOMCOLLECTION defined in [10] and used in Clause 1.1.2.1.1 and Annex G
below refers to the SQL BLOB encoding of a GeometryCollection.
▪ [K9] OGC WKB simple feature geometry types specified in [9] are a subset of the ISO WKB
geometry types specified in [12]▪ [K10] WKB geometry types are are restricted to 0, 1 and 2-dimensional geometric objects thatexist in 2, 3 or 4-dimensional coordinate space; they are not geographic or geodesic geometry
types.
▪ [K11] The axis order in WKB is always (x,y{,z}{,m}) where x is easting or longitude, y is northingor latitude, z is optional elevation and m is optional measure.
▪ [K12] A GeoPackage is not required to contain any feature data tables. Feature data tables in a
GeoPackage MAY be empty.
▪ [K13] GeoPackage applications MAY use SQL triggers or tests in application code to meet thisrequirement▪ [K14] Images of multiple MIME types MAY be stored in given table. For example, in a tiles table,
image/png format tiles COULD be used for transparency where there is no data on the tile edges,
and image/jpeg format tiles COULD be used for storage efficiency where there is image data for
all pixels. Images of multiple bit depths of the same MIME type MAY also be stored in a given
table, for example image/png tiles in both 8 and 24 bit depths.
▪ [K15] See Zoom Other Intervals for use of other zoom levels as a registered extensions.
▪ [K16] See Tiles Encoding WebP regarding use of the WebP alternative tile MIME type as a

175

registered extension.
▪ [K17] See Tiles Encoding WebP regarding use of the WebP alternative tile MIME type as aregistered extension.
▪ [K18] The "tiles" stipulation was removed because it prevented the use of the tile matrix
mechanism by extensions for other data types.
▪ [K19] The "tiles" stipulation was removed because it prevented the use of the tile matrix
mechanism by extensions for other data types.
▪ [K20] GeoPackage applications MAY query the gpkg_tile_matrix table or the tile pyramid user
data table to determine the minimum and maximum zoom levels for a given tile pyramid table.
▪ [K21] GeoPackage applications MAY query a tile pyramid user data table to determine whichtiles are available at each zoom level.
▪ [K22] GeoPackage applications that insert, update, or delete tile pyramid user data table tiles
row records are responsible for maintaining the corresponding descriptive contents of the
gpkg_tile_matrix_metadata table.
▪ [K23] The gpkg_tile_matrix_set table contains coordinates that define a bounding box as theexact stated spatial extent for all tiles in a tile (matrix set) table. If the geographic extent of theimage data contained in tiles at a particular zoom level is within but not equal to this boundingbox, then the non-image area of matrix edge tiles must be padded with no-data values,
preferably transparent ones.
▪ [K24] A GeoPackage is not required to contain any tile pyramid user data tables. Tile pyramiduser data tables in a GeoPackage MAY be empty.
▪ [K25] The zoom_level / tile_column / tile_row unique key is automatically indexed, and allows
tiles to be selected and accessed by "z, x, y", a common convention used by some
implementations. This table / view definition MAY also allow tiles to be selected based on a
spatially indexed bounding box in a separate metadata table.
▪ [K26] If an application process will make many updates, it is often faster to drop the indexes, dothe updates, and then recreate the indexes.
▪ [K27] Informative examples of hierarchical metadata are provided in Hierarchical Metadata
Example One - ISO19115.
▪ [K28] An informative example of raster image metadata is provided in Tiles Zoom Times Two
Example (Informative)▪ [K29] For example, for ISO 19139 metadata the URI value should be the metadata schema
namespace http://www.isotc211.org/2005/gmd▪ [K30] In SQLite, the rowid value is always equal to the value of a single-column primary key on
an integer column [B30] and is not changed by a database reorganization performed by the
VACUUM SQL command.
▪ [K31] Such a metadata hierarchy MAY have only one level of defined metadata▪ [K32] The scope codes in Metadata Scopes include a very wide set of descriptive information
types as "metadata" to describe data.
▪ [K33] ISO 19139 format metadata (B32) is recommended for general-purpose description of
geospatial data at the series and dataset metadata scopes.

176

▪ [K34] The "catalog" md_scope MAY be used for Feature Catalog (B40) information stored as XML
metadata that is linked to features stored in a GeoPackage.
▪ [K35] The "schema" md_scope MAY be used for Application Schema (B37)(B38)(B39)(B44)
information stored as XML metadata that is linked to features stored in a GeoPackage.
▪ [K36] The "taxonomy" md_scope MAY be used for taxonomy or knowledge system (B41)(B42)"linked data" information stored as XML metadata that is linked to features stored in a
GeoPackage.
▪ [K37] The following statement selects an ISO 8601timestamp value using the SQLite strftime
function: SELECT (strftime('%Y-%m-%dT%H:%M:%fZ','now')).
▪ [K38] A GeoPackage is not required to contain a gpkg_data_columns table. The
gpkg_data_columns table in a GeoPackage MAY be empty.
▪ [K39] GeoPackages MAY contain MIME types other than the raster image types specified inclauses 2.2.4, 2.2.5, and 3.2.2 as feature attributes, but they are not required to do so.

177