SQL statements for making gpkg-template for GE

[fhb1990]

In the Netherlands there has been interest in delivering Geology data in Geopackage format. As written in issue #9, we have tried to follow the encoding rules found here to create a template for GE data. This has resulted in a set of SQL statements which should result in a working empty Geopackage template file for the theme Geology (like the ones for AU and GN on this page).

By following the encoding rules specified by the link above, the added file has been created. The only thing that is missing are the association roles between the classes. These associations have to be added as related tables and views. This then has consequences for the tables gpkg_contents, gpkg_extensions, gpkg_data_columns and gpkgext_relations, as new rows need to be added in these tables.

geology gpkg 2021.zip

Some thoughts arose over making the file, some from ambiguity in the encoding rules. These are written below.

• The encoding rules specify that N:M association roles are to become related tables, and 1:M, N:1 and 1:1 relations should be views. However, some example files differ from this method.
• Some associations are modelled as an attribute, other through a related table without an attribute in the base table. As general rule 1:1, 1:N and N:1 can be implemented in SQL by adding the base key in the referenced table (there are some exceptions). In these cases the association is added as a column. When it is modelled as a table, the column is not needed.
• Do views need to be added to gpkg_contents?
• What to do with empty (user-)extensible codelists?
• Do the featureTypes from Geophysics that aggregate into the Geology featureType GeologicCollection need to be added?
• How to handle imported data types like Quantity and DirectPosition?

[heidivanparys]

Do views need to be added to gpkg_contents?

Yes, views should be added to gpkg_contents, see also https://www.geopackage.org/spec/#_contents . E.g.

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 a menu of geospatial data that is available for access and/or update.

and

The table_name column value in a gpkg_contents table row SHALL contain the name of a SQLite table or view.

If you do not add a view to the gpkg_contents table, it e.g. won't show up as a layer in QGIS, I noticed.