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

[Marcus Blake]

@Richard: thanks for redirecting this question in to the correct people…and apologises for the size of the file…lack of specification on my part to a over enthusiastic team member ;-). I’ll reduce the size and put in a issue report.

I’m in total agreement with you on the tremendous potential of GeoPackage to support the sharing of all types of data (statistical, geospatial, metadata, imagery…). Certainly the statistical community (the UNGGIM), is grappling with the issue of integrating statistical and geospatial data models and I think GeoPackage can provide one important part of the solution.

As well as a useful container for publishing 2016 Census data...One of my aspiration is to use GeoPackage to publish the full data model that the ABS uses to internally store our geographic classification with the aim of providing a point of truth for all users.

But to do both of these things well, views are very valuable and would prevent a lot of data duplication. 

@Even Thanks for the quick reply…and all your important support for GDAL.

One of my team (more knowledgable than I) thought that it was an ID issue and both ESRI and Pitney Bowes seem to use a pseudo PKID to get around this issue (Pitney Bowes uses a specific field name MI_IDX)…so it good to have that confirmed. But it doesn’t look like there is a straight forward solution. Happy help going forward. We have scheduled September for development and testing, but I sure we can find some time before then.

It interesting that, despite the lack of PKID, both the classification and symbolisation for a choropleth maps and the Identify Features Tool work as expected….thoughts?

cheers,

Marcus Blake

——
Geospatial Data Manager
Australian Bureau of Statistics


> On 27 Jun 2016, at 6:10 AM, Even Rouault <even.rouault at spatialys.com <mailto:even.rouault at spatialys.com>> wrote:
> 
> 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 <http://www.spatialys.com/>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20160628/e21b4ca9/attachment-0001.html>