Monday, May 6, 2019

Views and GeoPackage

From the beginning, the intent of GeoPackage was to allow views to be used (instead of tables) wherever appropriate. In general, "appropriate" means user-defined tables (features, tiles, or attributes). There is some confusion here because, for example, the sample feature table or view definition table (Table 7) and sample feature table definition SQL (Annex C.4) specifically call out primary keys and the notion of primary keys for view does not exist in SQLite.

In response, the GeoPackage SWG has approved some changes designed to clarify how views should be implemented. While implementers should use primary keys wherever as possible, Requirements 150 (for features) and 151 (for attributes) specify that if the database element lacks a defined primary key (i.e., it is a view), then the first column shall be primary-key-like (i.e., be a unique integer). There is a similar note for tiles but no new requirement was needed there because the schema for tiles tables already requires the first column to be the ID.

Currently, these changes are available on the working version of the standard. The working version may get published as another GeoPackage revision (1.2.2 or 1.3.0) but there is currently no timetable for doing so. One thing that we would need to do is update the Executable Test Suite (ETS) to have tests. (These tests would only apply to GeoPackages with a version after 1.2.1.) As always, if you have a GeoPackage that fails the ETS and you believe the ETS is in error, please let us know. 

P.S. Some of you in the community have asked about updatable views. It is theoretically possible to do this with a combination of tables and triggers but I don't recommend it.