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

bradh at frogmouth.net bradh at frogmouth.net
Fri Mar 22 19:12:26 EDT 2019

There is nothing in geopackage that constrains views to be read only, and attribute tables are specifically called out as updatable.

I think that is a problem - not because its not potentially useful, but because IMHO the complexity and interoperability problems are too much for the mass-market "Just Works" we should be aiming for.

-----Original Message-----
From: Geopackage <geopackage-bounces at lists.opengeospatial.org> On Behalf Of Keller Stefan via Geopackage
Sent: Friday, 22 March 2019 8:09 AM
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 _______________________________________________
You may visit our Privacy Policy at http://www.opengeospatial.org/privacy.
Geopackage mailing list
Geopackage at lists.opengeospatial.org
You may also unsubscribe or manage your OGC public list subscriptions at  https://lists.opengeospatial.org/mailman/listinfo/geopackage 

More information about the Geopackage mailing list