[Qgis-developer] Fwd: [Qgis-user] QGIS problem with GeoPackage database views

[Even Rouault]

Le dimanche 26 juin 2016 21:50:38, Richard Duivenvoorde a écrit :
> On 26-06-16 21:22, Even Rouault wrote:
> > The issue is that the OGR GeoPackage driver cannot derive an integer
> > primary key from the view, and affects 0 as the fid for all features. In
> > the case of the views of this particular dataset, the OBJECTID field
> > could be used as the FID (given some logic to be implemented in the
> > driver to analyze the structure of the view, the underlying tables and
> > the unicity constraints on the join fields)
> 
> Ok... so, is this a driver challenge?

Eh, I love driver challenges :-)

> 
> Or is it more an explicit problem, because the view is created without
> explicit primary key?
> 
> Because, instead of putting 'some logic' into the driver trying to guess
> a primary key, we can maybe create some kind of PK-constraint?
> 
> Like: a view (just like an Oracle table?) should have some kind of
> primary key defined?

I'm not aware of a way of doing it :

Here's a failed attempt :
sqlite> create unique index foo on sa1_b01_map_v ( OBJECTID );
Error: views may not be indexed

I'd be curious if other databases have a way of doing it, but my feeling is 
that I don't think it is possible for normal dynamic views. (Unless you are 
using a materialized view as in Postgres for example)


> If not: ask for it, so it is defined into the view
> definition (uh... not sure IF that is possible at all in a view...).
> Or QGIS asks for it: showing the attributes, and letting the user choose
> the id('s) that the user (thinks) are unique and should be used as
> primary key...

That would be a possibility, although that would still require an enhancement 
in the OGR side to accept the name of the PKID as an option.

Anyway as a fallback in the absence of a PKID a sequential numbering would 
probably be desirable (with the caveat that it wouldn't be stable when 
attribute or spatial filters are applied, which could cause some problems )

Even

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