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

Keller Stefan stefan.keller at hsr.ch
Thu Mar 21 17:08:48 EDT 2019


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.

:Stefan

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

Jeff,

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

Even 

> 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