Publication Date: 2018-08-22

Approval Date: 2018-07-10

Posted Date: 2018-05-31

Reference number of this document: OGC 17-093r1

Reference URL for this document: http://www.opengis.net/doc/PER/gpkg-rte-ie-er

Category: Public Engineering Report

Editor: Jeff Yutzler, Ashley Antonides

Title: OGC GeoPackage Related Tables Extension Interoperability Experiment

OGC Engineering Report

COPYRIGHT

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

WARNING

This document is not an OGC Standard. This document is an OGC Public Engineering Report created as a deliverable in an OGC Interoperability Initiative and is not an official position of the OGC membership. It is distributed for review and comment. It is subject to change without notice and may not be referred to as an OGC Standard. Further, any OGC Engineering Report should not be referenced as required or mandatory technology in procurements. However, the discussions in this document could very well lead to the definition of an OGC Standard.

LICENSE AGREEMENT

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

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

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

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

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

This Agreement is governed by the laws of the Commonwealth of Massachusetts. The application to this Agreement of the United Nations Convention on Contracts for the International Sale of Goods is hereby expressly excluded. In the event any provision of this Agreement shall be deemed unenforceable, void or invalid, such provision shall be modified so as to make it valid and enforceable, and as so modified the entire Agreement shall remain in full force and effect. No decision, action or inaction by LICENSOR shall be construed to be a waiver of any rights or remedies available to it.

None of the Intellectual Property or underlying information or technology may be downloaded or otherwise exported or reexported in violation of U.S. export laws and regulations. In addition, you are responsible for complying with any local laws in your jurisdiction which may impact your right to import, export or use the Intellectual Property, and you represent that you have complied with any regulations or registration procedures required by applicable law to make this license enforceable.

1. Summary

This OGC Engineering Report describes the results of the OGC GeoPackage (GPKG) Related Tables Extension Interoperability Experiment (GPKG-RTE IE). This IE tested a proposed extension to the OGC GeoPackage Encoding Standard (12-128r14). The GPKG-RTE defines the rules and requirements for associating tables with existing feature or attribute tables in a GeoPackage data store. As part of this IE, the participants performed Technology Integration Experiments (TIEs) where they produced GeoPackages that used this extension, loaded them into GPKG-compliant software systems, and observed the results. As a result of this work, the IE participants agree that the extension is fit for use and consideration as a standard by OGC.

1.1. Requirements & Research Motivation

The purpose of the GPKG-RTE is to define relationships between feature tables and tables that hold related content, including multimedia, simple attributes, and other features. One use case for this extension is to associate features with related multimedia content such as:

  • photographs;

  • audio or video files; or

  • PDFs.

For example, implementing this extension would provide the ability to associate pictures of a house with a specific parcel (land lot).

It is also possible to use the GPKG-RTE to associate features with simple attributes or features with other features. The GPKG-RTE supports many-to-many relationships, which allows a natural mapping from complex data models.

This extension, like all GeoPackage extensions, is intended to be transparent and to not interfere with GPKG-compliant, but non-supporting, software packages.

The goal of the IE was to verify that the extension was correctly designed to meet the design goals and to be transparent in this manner. This goal was achieved by building GeoPackages containing embedded multimedia content and sharing those GeoPackages with other software products, some compliant with the extension and others unaware of it.

1.2. Prior Work

Before this interoperability experiment, Compusult had produced a Related Tables Extension that it used internally. While Compusult was satisfied with the extension, they recognized that it would only achieve its full potential if it were standardized. Without standardization, there was the risk that other organizations would implement competing extensions and that there would not be interoperability between them.

1.3. Summary of Experiments and Results

Five RTE-aware and two non-RTE-aware software packages were tested using 13 samples from seven different providers. These integration experiments demonstrated that the Related Tables Extension works and is backward compatible (i.e., does not fail when loaded by non-RTE-aware software).

The experiments also highlighted some considerations for developers to be aware of and areas for future work. Producers of GeoPackages with the RTE need to register the extension (otherwise it might not be detected) and should test the files using the Executable Test Suite (ETS) to ensure conformance. The most common interoperability case for the RTE involved feature base tables and attributes as the related table; however, client software should be aware of other possible combinations (e.g., an attributes table as the base table or a features table as the related table). Finally, both producers and consumers should be aware of file sizes and complexity introduced by different media types, especially for mobile applications.

Now that this IE is complete, the participants are confident that the extension is ready to be standardized and adopted by OGC.

1.4. Future Recommendations

The GeoPackage SWG should finalize the GeoPackage Related Tables Extension and submit it to OGC for consideration as an adopted GeoPackage Extension.

1.5. Document contributor contact points

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

Table 1. Contacts
Name Organization

Jeff Yutzler

Image Matters

Tracey Birch

SOFWERX

Jason MacDonald

Compusult

Ashley Antonides

Radiant Solutions

Brad Hards

 — 

1.6. Foreword

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.

2. References

The following normative documents are referenced in this document.

3. Terms and Definitions

For the purposes of this report, the definitions specified in Clause 4 of the OWS Common Implementation Standard OGC 06-121r9 shall apply. In addition, the following terms and definitions apply.

  • 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/spec/#attributes.

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

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

  • geospatial data

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

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

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

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

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

  • related data

    Data that is linked in some way to 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.

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

  • user-defined mapping table

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

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

3.1. Abbreviated terms

  • GPKG GeoPackage

  • RTE Related Tables Extension

4. Overview

The purpose of the GPKG-RTE-IE is to demonstrate that it is possible to associate features, tiles, and attributes with other content within the GeoPackage. The goal of this Engineering Report (ER) is to present the work performed as part of the IE.

Section 5 presents a conceptual overview of the Related Tables Extension, as well as a detailed discussion of the requirements classes and use cases.

Section 6 presents the results of the individual Technology Integration Experiments (TIEs) that were performed as part of this IE.

Section 7 presents a recap of discussion topics that arose during the IE.

5. Design

5.1. Overview

The core of the Related Tables Extension is a mapping between existing table types defined by GPKG 1.2 - 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 three types of related data, which are separate conformance classes:

  • media;

  • simple attributes; and

  • features.

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 modelled data. See Figure 2.

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.

5.2. Requirements Classes

5.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; or

  • License information, usage restrictions, or security constraints.

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

5.2.3. Features

The Features conformance class is used for related tables that are GPKG 1.2 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.

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