Open Geospatial Consortium

Submission Date: 2018-08-13

Approval Date:   2019-03-26

Publication Date:   2019-05-08

External identifier of this OGC® document: http://www.opengis.net/doc/IS/gpkg-rte/1.0

Additional Formats (informative):

Internal reference number of this OGC® document:    18-000

Version: 1.0

Category: OGC® Encoding Standard

Editor:   Jeff Yutzler

OGC GeoPackage Related Tables Extension

Copyright notice

Copyright © 2019 Open Geospatial Consortium

To obtain additional rights of use, visit http://www.opengeospatial.org/legal/

Warning

This document is an OGC Member approved international standard. This document is available on a 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.

Document type:    OGC® Standard

Document subtype:   

Document stage:    Approved for public release

Document language:  English

License Agreement

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

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

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

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

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

Except as contained in this notice, the name of LICENSOR or of any other holder of a copyright in all or part of the Intellectual Property shall not be used in advertising or otherwise to promote the sale, use or other dealings in this Intellectual Property without prior written authorization of LICENSOR or such copyright holder. LICENSOR is and shall at all times be the sole entity that may authorize you or any third party to use certification marks, trademarks or other special designations to indicate compliance with any LICENSOR standards or specifications. This Agreement is governed by the laws of the Commonwealth of Massachusetts. The application to this Agreement of the United Nations Convention on Contracts for the International Sale of Goods is hereby expressly excluded. In the event any provision of this Agreement shall be deemed unenforceable, void or invalid, such provision shall be modified so as to make it valid and enforceable, and as so modified the entire Agreement shall remain in full force and effect. No decision, action or inaction by LICENSOR shall be construed to be a waiver of any rights or remedies available to it.

Table of Contents

i. Abstract

A GeoPackage [geopackage] is a platform-independent SQLite [sqlite] database file that contains GeoPackage data and metadata tables. GeoPackages, as described by the GeoPackage Encoding Standard [GPKG1_2] are designed to be extensible, including support for additional data types. This document defines the Related Tables Extension (RTE) for the GeoPackage Encoding Standard.

The RTE defines the rules and requirements for creating relationships in a GeoPackage data store between geospatial data tables and other tables that contain or reference related content such as attributes or media. Geospatial data tables (such as features or tiles tables) contain location information and/or geometries. There are many examples of where the RTE can be used including relating parcel (land lot) features to pictures of that parcel or linking census boundaries to the related demographic census data.

ii. Keywords

The following are keywords to be used by search engines and document catalogues.

ogcdoc, OGC document, GeoPackage, extension, related tables, multimedia

iii. Preface

The GeoPackage Related Tables Extension (RTE) defines the rules and requirements for creating relationships in a GeoPackage [geopackage] data store between geospatial or attributes data tables and other tables that contain or reference related content such as attributes or media. Geospatial data tables (such as features or tiles tables) contain location information and/or geometries. Conceptually, this extension is similar to the OGC Table Joining Service Interface Standard [OGC-TJS-IS].

One use case for this extension is to associate features with related multimedia content such as:

  • photographs;

  • audio or video files; and

  • PDFs.

There are many examples of where the RTE can be used including relating parcel (land lot) features to pictures of that parcel or linking census boundaries to the related demographic census data. To support this capability, an ancillary data table (gpkgext_relations) is used in conjunction with user-defined mapping tables.

This extension, like all GeoPackage extensions, is intended to be transparent and to not interfere with GPKG compliant, but non-supporting, software packages. The OGC GeoPackage (GPKG) Related Tables Extension Interoperability Experiment (GPKG-RTE IE) [GPKG-RTE_IE] verified that the extension is correctly designed to meet the design goals and to be transparent in this manner. The goal of the IE was achieved by building GeoPackages containing embedded multimedia content and sharing those GeoPackages with other software products. This IE produced an OGC Engineering Report [GPKG-RTE_IE_ER] that discusses whether the extension is fit for use and adoption by the OGC.

The following information is provided in compliance with the GeoPackage Extension template as defined in Annex E: GeoPackage Extension Template of the GeoPackage Standard. These additional introductory clauses are not elements in the standard OGC document template.

  • Extension Author: GeoPackage SWG; author_name gpkg to be used upon adoption.

  • Extension Name or Template: related_tables; upon adoption the alias gpkg_related_tables MAY be used

  • Extension Type: This extension provides new requirements dependent on GeoPackage Clause 2.1, Clause 2.2, and Clause 2.4.

  • Applicability: This extension defines relationships between feature tables and tables that hold related content, including multimedia, simple attributes, and other features.

  • Scope: read-write

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

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

iv. Submitting organizations

The following organizations submitted this Document to the Open Geospatial Consortium (OGC):

  • Image Matters LLC

  • Compusult

  • Radiant Solutions

  • SOFWERX

v. Submitters

All questions regarding this submission should be directed to the editor or the submitters:

Name

Affiliation

Rob Cass

Compusult

Jeff Yutzler

Image Matters

Tracey Birch

SOFWERX

Jason MacDonald

Compusult

Ashley Antonides

Radiant Solutions

Brad Hards

 — 

1. Scope

A GeoPackage [geopackage] is a platform-independent SQLite [sqlite] database file that contains GeoPackage data and metadata tables. GeoPackage is designed to be extensible, including support for additional data types.

This document defines an extension that allows a GeoPackage to contain additional data that is related to geospatial (generally, but not exclusively, features) or attributes data. As an example, this can be used to establish a many-to-many relationship between features (e.g. points, lines, or areas) and multimedia files.

2. Conformance

This standard defines requirements for specialized attribute tables (containing information such as multimedia content to be stored in a GeoPackage, or simple attributes) and mapping tables to relate information in separate tables, along with metadata to describe those tables and the relationships between them.

Conformance with this standard shall be checked using all the relevant tests specified in Annex A (normative) of this document. The framework, concepts, and methodology for testing, and the criteria to be achieved to claim conformance are specified in the OGC Compliance Testing Policies and Procedures and the OGC Compliance Testing web site[1].

3. References

The following normative documents contain provisions that, through reference in this text, constitute provisions of this document. For dated references, subsequent amendments to, or revisions of, any of these publications do not apply. For undated references, the latest edition of the normative document referred to applies.

4. Terms and Definitions

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

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

4.1. attributes data

Non-spatial tabular data that is designed to be joined with geospatial data for analysis. In a GeoPackage, attributes data is stored in attributes tables as per http://www.geopackage.org/spec121/#attributes.

4.2. base data

Data that is linked in some way to related data (in other words, the left side of the A→B relationship). In this extension, base data is stored in geospatial or attributes data tables.

4.3. cardinality

The property of a relationship between two entities, specifying whether it is one-to-one, one-to-many, many-to-one, or many-to-many.

4.4. geospatial data

Data containing location information and/or geometries. In a GeoPackage, geospatial data may be stored in features or tiles tables.

4.5. one-to-many

A type of cardinality in which an element of A may be linked to zero or more elements of B, but an element of B is linked to one and only one element of A.

4.6. many-to-many

A type of cardinality in which an element of A may be linked to zero or more elements of B and an element of B may be linked to zero or more elements of A.

4.7. many-to-one

A type of cardinality in which an element of A is linked to one and only one element of B, but an element of B may be linked to zero or more elements of A.

4.8. related data

Data that is linked to in some way from base data (in other words, the right side of the A→B relationship). In this extension, related data is stored in a user-defined attributes table (of which user-defined media table is a special case) or feature table.

4.9. relationship

For the purposes of this extension, a link between two entities A and B. A refers to base data and B refers to related data.

4.10. user-defined attributes table

In this extension, a user-defined attributes table is a table that contains data that is related to existing geospatial data.

4.11. user-defined mapping table

In this extension, a user-defined mapping table is a join table that links geospatial data and related data.

4.12. user-defined media table

In this extension, a user-defined media table is a user-defined attributes table that is specifically designed to contain multimedia content (including, but not limited to, images, diagrams, formatted text, moving images or audio).

5. Conventions

This sections provides details and examples for any conventions used in the document. Examples of conventions are symbols, abbreviations, use of XML schema, or special notes regarding how to read the document.

5.1. Identifiers

The normative provisions in this specification are denoted by the URI prefix

http://www.opengis.net/spec/gpkg-rt/1.0/

All requirements and conformance tests that appear in this document are denoted by partial URIs which are relative to this base. For example, if the requirement identifier is shown as /req/table-defs/extensions-ger then the unique identifier for that requirement is http://www.opengis.net/spec/gpkg-rt/1.0/req/table-defs/extensions-ger.

6. Design (Informative)

6.1. Overview

The core of the Related Tables Extension is a mapping between existing table types defined by GPKG 1.2.1 - features, tiles, and attributes. The mapping is defined by a new kind of table defined by the Related Tables Extension. The mapping table links related rows in those tables of those types by reference to their primary keys. For example, to link a row in Table A to a row in Table B, the mapping table includes a row that has two values - the primary key of the row from Table A, and the primary key of the row from Table B.

The mapping table allows many-to-many relationships. For example, to relate another row in Table B to the same row in Table A, the mapping table would simply include another row with the appropriate primary keys. See Figure 1.

RelatedTablesConcept
Figure 1. Related Tables concept

Mapping tables are unique to each pair of tables. The appropriate mapping table for each table pair (if any) is identified in a new table gpkgext_relations, which also specifies the name of the primary key column and the type of related data. This version of the Related Tables Extension supports five types of related data, which are separate conformance classes:

  • media

  • simple attributes

  • features

  • attributes

  • tiles

The relationships can be considered directional, in that they relate primary keys of two tables in terms of base (the "left" or "from" side of the mapping) and related (the "right" or "to" side of the mapping). Since the related tables are valid GPKG 1.2 table types (potentially with some additional constraints), they can form the base side of another mapping. This allows chaining (directed graph) of relationships as appropriate to represent the modeled data. See Figure 2.

related tables uml
Figure 2. Related Tables UML Diagram

The Related Tables Extension makes no constraints on the base table; it can be any table type supported by GPKG 1.2 - tiles, attributes or features. The related ("right" / "to") table is constrained by defined values of relation_name which is a TEXT value in the gpkgext_relations table. The constraining of relationships serves two purposes - it allows clients to provide appropriate rendering of content, and it communicates the intent of the relationship. Since the relationship is text, values other than those defined by the Related Tables Extension document can be used, however this will not be interoperable without some other coordination mechanism.

6.2. Requirements Classes

6.2.1. Media

The Media conformance class is used for related tables that provide multimedia content. The GPKG table type is attributes. This was the original intent of the Related Tables Interoperability Experiment, and remains an important use. For example, using a relation_name of media provides the ability to link a set of photographs, line diagrams, documents, videos and/or audio files to a specific location (typically a point or polygon feature; but the Related Tables Extension does not prohibit some other kind of feature, or a row in an attribute table, or a row in a tile table being used as the base side of the mapping to the media table). The minimum content of the user defined media table is a primary key, a BLOB containing the media content (conceptually a byte array in the GeoPackage), and the IANA Media Type type for the media content (e.g., image/jpeg for a photograph).

An example of this is a land parcel (land lot) as the feature (base table), and photographs of the location (house, commercial property, etc.) as the related media.

Note that the related table does not need to include additional columns, although additional columns are permitted in the related table definition, so they can be added if desired. The Related Tables Extension does not constrain or codify what the additional columns can be. Specific communities of interest may wish to provide usage profiles of the Media conformance class to meet specific operational or business needs. Clients that intend to display GeoPackages that make use of the Media conformance class of the Related Tables Extension may wish to provide additional attribute display on a "best efforts" basis (e.g., view with the column names as labels for the text and numeric row values).

For example, additional column content might include:

  • An indicator of the size of the media content (although this can be determined using the SQLite length() function);

  • A title or description of the content of the media BLOB; and/or

  • License information, usage restrictions, or security constraints.

6.2.2. Simple Attributes

The Simple Attributes conformance class is used for related tables that include only "simple attributes" - those SQLite values that are part of the TEXT, INTEGER and REAL storage classes. The GPKG table type is attributes. This is intended to support data that would typically be represented in Comma Separated Value (CSV) or spreadsheet formats, such as reference tables or observations. The simple attributes related table is not permitted to contain BLOB data (such as multimedia content, or feature GEOMETRY - these are covered by other conformance classes).

Only two columns are required in the related attributes table - the primary key, and one other column (which can be of TEXT, INTEGER, REAL, or a type that maps to one of those storage classes). As for Media, the Simple Attributes related table does not need to include additional columns, although additional columns are permitted in the related table definition, so they can be added if desired. The Related Tables Extension does not constrain or codify what the additional columns can be. Specific communities of interest may wish to provide usage profiles of the Simple Attributes conformance class to meet specific operational or business needs. Clients that intend to display GeoPackages that make use of the Simple Attributes conformance class of the Related Tables Extension may wish to provide additional attribute display on a "best efforts" basis (e.g., view with the column names as labels for the text and numeric row values; or a spreadsheet-style table representation).

An example of this is a land parcel (land lot) as the feature (base table), and contact details for the managing agent as the related table. While this could be supported by embedding the contact details for each land parcel, this could be a lot of duplication and require update if a phone number or email address changes.

Note that the feature (base table) could link to many attribute table rows. An example of this would be for a set of valuations for the property, or records of property inspections or maintenance work conducted on the property.

6.2.3. Features

The Features conformance class is used for related tables that are GPKG 1.2.1 vector feature tables. The GPKG table type is features. This is intended to support defining relationships between feature types. No changes or constraints are made on the extant definition of the features tables.

An example of this is linking the location of a condominium (town house) or apartment with the locations of associated parking places or individual garden plots.

6.2.4. Attributes

The Attributes conformance class is used for related tables that comply with the GPKG attributes table type. This is intended to support additional relationships to data which may already be stored as GPKG metadata.

As with the Simple Attributes conformance class, only two columns are required in the related attributes table - the primary key, and one other column. The Related Tables Extension does not constrain or codify what additional columns can be. Unlike a Simple Attributes table, an Attributes table may include all data types allowed in a GPKG attributes metadata table (e.g. BLOB data types).

6.2.5. Tiles

The Tiles conformance class is used for related tables that are GPKG tiles tables, specifically tile pyramid tables.

6.3. Usage scenario

A single GeoPackage could include each of these relationships. For example, an airport can be considered as a point location with some attributes, which would be represented in GeoPackage as a features table. Similarly, the runways may be considered as polygons with attributes, which would be represented in GeoPackage as a different features table. See Figure 3. The mapping between those feature tables can be represented using the Related Tables Extension, so that a graphical user interface could identify and select the runways for a particular airport, including associated attributes and metadata.