OGC GeoPackage Related Tables Extension Interoperability Experiment

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:

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.

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.

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.

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

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

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.

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.

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:

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.

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.

In addition to feature geometry, an airport may have associated documents, such as terminal procedures. These are typically provided as PDF documents containing a mix of text and diagrams, as shown in Figure 4. These could be common to a range of airports in a close area (which is the case for that Arrival procedure), specific to a particular airport, or they could be specific to a particular runway, as shown in Figure 5.

This information can be represented using the user defined media table, either by incorporating the original PDF as the content of the data BLOB, or by rendering it to an image format such as PNG and using that as the content of the data BLOB. Mapping tables can relate the feature tables (e.g., airport geometry as points and runways as polygons, as described above) to the media table.

Airports may also have associated attributes that are not geospatial or media, such as the communications frequencies that are required. There are often multiple frequencies and they are often common to multiple airports in an area. The frequency information can be represented as an attributes table, with the mapping from airport to communication frequency through a simple attributes mapping. There could well be additional attribute information, such as a mapping from the terminal procedures media table to currency (validity dates, last change) or to the responsible information provider and associated contact details.

Thirteen samples were contributed by seven different providers, and covered a range of use cases and media types. All samples were assessed for alignment with the Abstract Test Suite (ATS) and tested with an Executable Test Suite (ETS) to verify conformance with the draft specification.

The existing GeoPackage 1.2 Executable Test Suite was extended to validate the four Related Tables Extension conformance classes. The additional code is believed to cover all ATS requirements, except for checks for UNIQUE constraints on tables. Not having verification of UNIQUE constraints was undesirable, but did not significantly affect the value of ETS testing. In particular, early access to the ETS allowed correction of minor issues in some samples.

Note that the ETS includes tests for extensions that were not part of this IE (e.g., schema extension).

The tests that faulted are likely a bug in the ETS or a support library. Those files can be opened in other GeoPackage readers, and in SQLite. The problem was reproduceable on the OGC TE2 (beta) test site, and is not directly related to the ETS extensions for RTE.

TIEs were conducted for five software packages that had implemented the RTE. Negative tests with non-compliant samples were also included, as it is useful to know whether particular non-compliant samples work or not.

[1a] Failed because software did not anticipate that related table could be a features table (was expecting an attributes table)

[1b] Failed because related data was an unknown type (aspatial)

[1c] Failed because software did not anticipate that base table could be a attributes table (was expecting a features table)

[2] Failed because software did not detect the extension - no gpkg_extensions entry

[3a] Success - Media not rendered, but referenced

[3b] Success - No errors, but software was incapacitated due to data processing

[4] Failed - Software crashed; file threw an exception due to its size

[5] Failed - Nothing to show

[6] Success - Related media was discovered but the file names were "null"

[7] Success - Related media was discovered but only image/jpeg content displayed

[8] Failed - Invalid WKT string reported

[9] Failed - Error during load: “attempt to write a readonly database”

In addition to TIEs for software that had implemented the RTE, two non-RTE-aware software packages were also tested to ensure that the extension is backward compatible. Negative tests with non-compliant samples were also included, as it is useful to know whether particular non-compliant samples work or not.

As part of the development of one software package, the developer defined a set of use cases. The API for the software package was designed to have a method for each use case.

One of the challenges the participants worked to address is the semantics of the related data. This can be handled in a few ways but the participants did not reach a consensus on a recommended approach.

Sponsors have identified a need to handle all possible cardinalities between base data and related data – one-to-many, many-to-one, and many-to-many. The use of a mapping table allows any of these relationships.

Participants agreed to make the mapping table mandatory as part of this extension. This would create a single code path for the software to follow, joining the base table, the mapping table, and the related data table. While a one-to-many relationship could be handled without this table structure (a foreign key relationship would suffice), this would require a foreign key in the related data table. This would restrict the related data to a single related data relationship.

Participants recognized that there was a potential need to relate multiple sets of related data to a single base table. The participants determined that this could be handled naturally by not constraining the number of relations that are registered with a particular base data table. Similarly, it would be possible to chain relationships together. For example, an airport feature could relate to multiple runways which could then relate to multiple media files.

One issue came up that was not resolved during the experiment. It would be useful to identify the type of relationship that is represented to clarify the expected output schema on export. However, the participants could not agree on a solution - each proposal seemed to cause more problems than it solved. For example, a cardinality column was proposed for the gpkgext_relations table but this was rejected because it would be too easy for a GeoPackage client to ignore the value and add relationships that were inconsistent with it. Adding constraints to compensate for this issue would have required extensive error handling which was determined to not be worth the effort.

The participants agreed that there was value to storing references to related content, as opposed to embedding the content as a BLOB inside the database. Some media files (particularly high resolution full motion video) can get quite large and SQLite databases do not scale very well past a few GB. In the interest of time, the participants elected to make this out of scope for the IE. However, the decision to have separate requirements classes for different relation types provides a potential way ahead in this area. An "external media" requirements class could potentially have a URI column instead of a BLOB column.

It was noted that in an operational setting, it is useful to separate large, static GeoPackages from smaller, dynamic GeoPackages because smaller files are easier to manage. This is consistent with other prior discussions.

Because the Media related attribute tables must contain a content_type that is not null, the participants discussed how to handle commonly-used media types that are not registered IANA MIME types (e.g., audio/wav is not a defined IANA media type, even though it is commonly used). The participants agreed to maintain the requirement that content_type cannot be null, as producers can fall back on the option to specify application/octet-stream for documents without a more specific, registered MIME type.

Future work could involve adjusting this requirement in a way that maintains testability and interoperability while enabling an improved user experience.

Because the extension requires adding rows for gpkgext_relations and the mapping table(s) to the gpkg_extensions table, the participants noted that could cause interoperability issues for non-conforming clients. For example, if the client deleted a row in the related table, it would result in a row in the mapping table where the target primary key did not exist.

This issue was not fully resolved; one suggested approach to address this would be to require that the rows for gpkgext_relations and the mapping table(s) have a scope of write-only. Alternately, implementors could be required to deal with unexpected disappearance/alteration of base table rows.

The columns (base_id and related_id) in a user-defined mapping table are required to be INTEGER values, which is consistent with primary key requirements for GeoPackage user tables. After running the TIEs, one participant provided feedback that this requirement may unnecessarily force additional logic to be implemented by applications that use text UUIDS as keys in the database, for example to support syncing GeoPackages between devices for mobile disconnected operations. They requested future consideration for changing the mapping table to require two TEXT fields instead of two INTEGER fields.

One participant noted that future work could include the ability to prevent data duplication for cases where the same content is linked to different features. This could be done by implementing a content reuse feature where the user could browse and select currently attached content, or a field for a checksum for the content, to enable an application to more easily verify if content being added already exists in the GeoPackage.