How can or should we handle many-to-many relationships in OGC standards?

[jyutzler]

Here is the issue as best I understand it. The participants of the GeoPackage Related Tables Extension Interoperability Experiment (GPKG-RTE IE) have identified a requirement to support many-to-many relationships between features (or potentially tiles) and the data it is related to (e.g., photos, videos, PDFs, etc.). For example, a video of an area may relate to a number of building features while a particular building may have multiple photos associated with it. While some scenarios only require one-to-many or many-to-one relationships, there are enough scenarios where many-to-many is required that the IE participants wanted to make sure we supported it.

What we know is that you need a join table to support the many-to-many relationships. What we haven't decided yet is whether to make the join table mandatory.

Pros:

• This creates a single code path, making it easier for developers to implement (if we have multiple code paths, we run the risk of developers not implementing them all, which will lead to interoperability issues down the line)
• It future-proofs the dataset so that even if the data management people don't anticipate the need for a many-to-many relationship, it is there if they need it.

Cons:

• This complicates data management, making things harder for the data management people to perform the initial load. Since most existing data is not many-to-many, someone or something is going to have to generate and populate the join table on the fly. At a minimum we will need to produce specific guidance on how to migrate existing data to this model. Better would be to produce tools to assist in the migration that could either be used directly or incorporated into other solutions. Fortunately the IE process gives us the flexibility to do this.

Since the GPKG-RTE IE has schedule constraints, we are going to make a decision on Monday 12/18. We hope to make this decision once but we understand that this will become a defacto standard for how to do many-to-many throughout the OGC baseline (at least for anything that is based on a relational data model).

[kevinbentley]

I think it would be a mistake to avoid the many-many capability because of the complexity it adds. GeoPackage is built on a relational database, and it would be a shame to not use a core capability of relational databases. If you are just adding 1-1 or 1-many relationships, it isn't very much extra work to use the join table.

I have been experimenting with GeoPackage as a container for terrain databases for simulation.We are experimenting with GeoPackage to use relationships like this to store terrain along side of the GIS data, and use relationships like this to link the two.

In simulation it is very common to have many-many relationships between features and their representation. I have been working with the Army on our GeoPackage for simulation experiments with the goal of fully conforming to the GeoPackage standard. We are working within the simulation community to expand the use of GeoPackage in simulation, but if the capabilities we need are not part of the standard there will be multiple groups doing things their own way, and I don't think that is good for GeoPackage.

[sraymie]

GeoPackage was designed for extensibility, a key strength built in from the start. While there are now only a few implementations using media attachments, there are well known requirements and use cases for many-to-many related tables. A more capable extended format will enable wider and faster adoption of the core GeoPackage and encourage innovative new standards-based implementations such as the use case Kevin suggested and the field survey use case driving the proposed RTE.

It seems better to deal with complexity now rather than limit extensibility and have implementers potentially create non-interoperable data packages in order to meet user needs. GeoPackages that don't work across applications will kill adoption. Agree with Jeff that it would be better to produce tools that may assist developers with migration from proprietary formats.

Given the tremendous volume and variety of geospatial data, a many-to-many solution would help position GeoPackage for automated interoperability required to ensure delivery of next-gen OGC services across products from different vendors.

[jyutzler]

The one wrinkle that was added today is that it is probably useful to have a 3-value cardinality flag. For now it will be informational only. The primary purpose is to inform exporting tools how the data is to be exported. It may also be beneficial to users who would be able to see what the source data was like. We do not currently believe it makes sense to try to build integrity constraints to ensure that the data matches the flag.