By das-g


2019-03-14 17:10:29 8 Comments

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 comments

@user30184 2019-03-15 07:16:16

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.

@das-g 2019-03-15 10:16:54

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 2019-03-15 10:17:09

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?

@user30184 2019-03-15 14:49:21

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.

@das-g 2019-03-15 15:28:45

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.

@Stefan 2019-03-21 21:32:07

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.

@user30184 2019-03-21 21:48:32

But technically it is impossible to have a SQLite table without primary key. Either it has the internal ROWID which may have an alias name (as defined in the GeoPackage specification), or some other PK field that is given by the user if table is created without rowids. QGIS obviously was not prepared to case when user gives an empty string as the name of the PK field as you and I did in our tests. I agree that QGIS should try to prevent users from doing stupid things. I think that "without rowid" tables do not fit well for GeoPackage and typical GIS usage but there may be some exceptions.

@das-g 2019-05-18 12:28:41

I don't think that's what QGIS tried to do in my tests. On the contrary, it tried to use a column with non-unique entries as the PK column, which in one case resulted in rows being silently discarded and in the other resulted in the operation failing due to violating the UNIQUE aspect of the PK constraint.

@Andreas W. Bartels 2019-03-14 20:21:17

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.

@das-g 2019-03-14 20:43:58

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?)

@Andreas W. Bartels 2019-03-14 21:23:51

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.

@Andreas W. Bartels 2019-03-14 21:24: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.

Related Questions

Sponsored Content

1 Answered Questions

1 Answered Questions

1 Answered Questions

2 Answered Questions

[SOLVED] Saving duplicate geometry layers into a geopackage?

  • 2018-06-01 19:39:40
  • Ruslan
  • 380 View
  • 0 Score
  • 2 Answer
  • Tags:   qgis geopackage

1 Answered Questions

[SOLVED] Negative values in area calculation as Geopackage

1 Answered Questions

[SOLVED] Add layer to existing geopackage not working in QGIS?

1 Answered Questions

[SOLVED] Writing GeoPackage with FME - no geometry

  • 2016-05-30 05:03:26
  • Topi
  • 380 View
  • 1 Score
  • 1 Answer
  • Tags:   fme geopackage

1 Answered Questions

[SOLVED] Why might a newly created vector layer not appear on canvas?

Sponsored Content