[Geopackage] GeoPackages: Are PK's really mandatory?

Keller Stefan stefan.keller at hsr.ch
Fri Mar 22 08:58:53 EDT 2019

I'd like to add some thoughts.

I'm very much in favor that GeoPackage supports Views.

I expect (and suggest if needed) that there's a possibility of a GeoPackage reader, to find out from the tables catalog, which is the geometry, which is the PK (if any) and if the table is editable or not. 

The only defining property of a view currently in SQLite (and also PostgreSQL) is its select query. This is an implementation decision we can't change within the GeoPackage spec. (see P.S. below). 

We could eventually allow GeoPackage writers to add the metadata (PRAGMA?) to views. Being an feature class which is "editable" or not is a common property of feature classes including tables).


P.S. Just a word about internas of a database: The question from a database implementation point of view is, whether uniqueness and not-nullability can be deduced from the query and the base tables of a view. Sometimes it can, but SQLite and PostgreSQL obviously don't try and don't expose that automatically (though they will sometimes use it in planning!).

-----Original Message-----
From: Keller Stefan 
Sent: Thursday, March 21, 2019 10:09 PM
To: 'Even Rouault' <even.rouault at spatialys.com>; geopackage at lists.opengeospatial.org; Jeff Yutzler <jeffy at imagemattersllc.com>
Subject: RE: [Geopackage] GeoPackages: Are PK's really mandatory?

Hi Jeff and Even

Can you help me understanding for what use cases or mandatory reasons PKs or constraints are required?
Even Postgres does not support constraints nor indexes on views (by referring to the fact that the underlying tables all do of course).
Views are read only, so editing doesn't seem to be a use case.


-----Original Message-----
From: Even Rouault [mailto:even.rouault at spatialys.com]
Sent: Thursday, March 21, 2019 9:05 PM
To: geopackage at lists.opengeospatial.org; Jeff Yutzler <jeffy at imagemattersllc.com>
Cc: Keller Stefan <stefan.keller at hsr.ch>
Subject: Re: [Geopackage] GeoPackages: Are PK's really mandatory?


For real SQLite *tables*, I believe we should still require PK, or at least a UNIQUE and NOT NULL constraints.
For the RTree spatial index extension to operate correctly, this is needed at least.

(Stefan: The empty column name of the SE thread is an abuse of SQLite capabalities. It is certainly a bad practice...)


> Stefan,
> Primary keys are not strictly required on features tables. Part of the 
> reason for this is that we want to support views (see footnote K17
> <http://www.geopackage.org/spec/#K17>) and a) SQLite can't do primary 
> keys on views and b) it is difficult if not impossible for a 
> conformance test to handle the distinction properly. We are in the 
> process of clarifying how views are supposed to work 
> <https://github.com/opengeospatial/geopackage/issues/446> and I will 
> be sure to make a blog post when we do.
> Regards,
> Jeff
> On Thu, Mar 21, 2019 at 2:35 PM Keller Stefan <stefan.keller at hsr.ch> wrote:
> > Hi Jeff
> > 
> > 
> > 
> > I just stumbled over this issue:
> > 
> > 
> > https://gis.stackexchange.com/questions/315547/is-a-pk-required-for-
> > geopac
> > kage-vector-layers
> > 
> > 
> > 
> > 1. Is a Primary Key really a MUST requirement in GeoPackages?
> > 
> > 2. If yes, is this really actually a tech dept from SQLite?
> > 
> > 3. Creating a PK field with null length as reported in the issue 
> > above looks like a smell to me.
> > 
> > 
> > 
> > My impression is, that there are three actions to be considered:
> > 
> > a. Clarification of the GeoPackage spec.
> > 
> > b. Considering not to require mandatory PKs in the spec. (and not 
> > trying to do “SQlite magic” under the hood).
> > 
> > c. (Outside scope of spec. Some enhancement in QGIS how it handles 
> > intersections/overlays).
> > 
> > 
> > 
> > Yours, Stefan

Spatialys - Geospatial professional services http://www.spatialys.com

More information about the Geopackage mailing list