6

According to geopackage.org's "Getting Started With GeoPackage" guide, features are stored in user-defined data tables. It seems like it says that these tables require a primary key:

Other than the geometry column and a primary key, the schema of a features table is up to the implementer.

(Emphasis mine)

Is that a hard requirement? The underlying SQLite probably doesn't require a primary key, but does the GeoPackage specification mandate each layer to have one?


Motivation:

I'm asking because QGIS 3.4.4 seems to handle this inconsistently. If you intersect two vector layers the resulting layer will often not have any unique attributes (except for maybe the geometry column if the input layers were topologic).

  • If I try to store the result directly to a GeoPackage layer, features that would lead to UNIQUE constraint violations on the fid column (which corresponds to the fid of the first input layer and is apparently automatically chosen as the new GeoPackage layer's PK) are simply omitted, turning the resulting layer incomplete.
  • If I store the result in a "temporary layer", the result is complete. fid of the first input layer becomes fid column of the temporary layer and fid of the second input layer becomes fid_2 there.
    • If I naïvely try to persist it in a GeoPackage layer using "make permanent", that fails due to UNIQUE constraint violations.
    • If I use "make permanent" but in the dialog remove fid from the "FID" field, I can save to a GeoPackage layer fine. (And loading from it confirms that all the features are still there and that no new unique column for a PK has been added.)
  • If I start from a layer loaded from a GeoPackage which apparently has a PK (field fid has "Not Null" and "Unique" checked as restrictions in Layer properties > Attribute form) and in remove the fid field in Layer properties > Source Fields, QGIS refuses to save that layer when trying to leave "editing mode" for that layer.

(Not tagging with , , etc., as the question itself is about the file format and should be independent of what Software is being used.)

2 Answers 2

6

Every feature table in GeoPackage must have a primary key of type INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL. See section 2.1.6 in the standard or this part in the getting started document http://www.geopackage.org/guidance/getting-started.html#features

User-defined Data Tables

Features are stored in user-defined data tables. Each features table has exactly one geometry column, a BLOB. (The structure of this BLOB is described here.) The OGC Simple Features geometry types are the supported geometry types. Other than the geometry column and a primary key, the schema of a features table is up to the implementer. Properties (text, integer, or real) provide additional information about each feature. The GeoPackage standard has an example schema.

The user interface of QGIS made you to believe that you created a table without primary key when you removed text fid from the "FID" field in the dialog. However, even if you do that the table will still be created with a primary key but the PK field will just have no name. You can see the whole CREATE TABLE sentense from the internal tables of SQLITE with a query

SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'test_table'

The result is like this:

CREATE TABLE "test_table" ( "" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
"geom" MULTISURFACE, "ogr_pkid" TEXT(255), "id" TEXT(255));

QGIS creates the table in this way so that the mandatory part "INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL" will be found from the schema of the table as GeoPackage standard requires. However, SQLite creates such PK field in any case if a user does not explicitly deny it. Internally SQLite knows that field as ROWID https://www.sqlite.org/rowidtable.html.

QGIS does not show the ROWID field that really exists for the SQLite engine. Therefore it might be better if you do not leave the name of the FID field empty but give it an alias like "foo" or "fid2". Then the table will be created as CREATE TABLE "test_table" ( "foo" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,... and the "foo" field will be visible in QGIS.

7
  • Thanks for these insights. I wasn't aware of that at all! Didn't expect SQLite to have such non-standard subtleties. (Though looking at other relational/SQL DBMS, it's kinda unsurprising in retrospect.)
    – das-g
    Mar 15, 2019 at 10:16
  • So to answer my question, GeoPackage does require a PK for each layer table (Or does it not? See this comment) but applications don't have to declare one when creating a GeoPackage layer as a new SQLite table through the SQLite engine, as the engine will create a rowid anyway, unless told not to with WITHOUT ROWID. Correct?
    – das-g
    Mar 15, 2019 at 10:17
  • 1
    PK is required. See for example geopackage.org/guidance/getting-started.html#features User-defined Data Tables Features are stored in user-defined data tables. Each features table has exactly one geometry column, a BLOB. ... Other than the geometry column and a primary key, the schema of a features table is up to the implementer. Application does not need to declare PK but it may be useful to do so. For example if fids in your temporary layer were unique, re-using them as fids in a new table would allow easy foreign key relations.
    – user30184
    Mar 15, 2019 at 14:49
  • Thanks. Can you incorporate that into your answer (insofar it isn't already covered there)? If that's included I can mark it as accepted, I think.
    – das-g
    Mar 15, 2019 at 15:28
  • I just checked with the maintainer of the GeoPackage spec.: Primary keys are not strictly required on features tables! It seems that part of the reason for this is that they want to support views (see footnote geopackage.org/spec/#K17 ). And the empty column name in the observed CREATE TABLE is an abuse of SQLite capabalities and bad practice. QGIS should change that.
    – Stefan
    Mar 21, 2019 at 21:32
3

See GeoPackage Encoding Standard Section 2.1.6. Requirement 29 say that there should be an explicit primary key. Clients must support views that cannot have a primary key. But if you do, the SQL commandVACUUM can change the key values of your features.

3
  • Are we sure that Requirement 29 mandates a primary key? It's quite unclear to me what being "structured consistently" with an example implies or doesn't imply. (Like, does the PK have to be named id? Are column names required to be in snake_case? Are non-PK attributes required to allow NULL?)
    – das-g
    Mar 14, 2019 at 20:43
  • GeoPackage is bounded to Sqlite and you can't change much on the structure of a existing table in Sqlite. In addition, the structure is designed so that the programmers of the using applications do not have to know the exact structures. Mar 14, 2019 at 21:23
  • Naming ok, but it is due to technical debts in the sqlite actually better to use lower case and then snake case is better readable. Allowing zero for all other columns makes sense when processing steps require empty records. Mar 14, 2019 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.