Warn users for non-unique first column (was "Wrong selection if loading a Geopackage View")

[rduivenvoorde]

It seems as a selection in a view of a table in a geopackage as if the id's are interchanged?

In this zip:
cloud3.zip
there is a geopackage with a model output of a smoke cloud of a fire in the North Sea.

There is a grid of all 'cells', a data table, and some views which connect those together.

If I load view3 (which seems to be properly 'registered' in the geopackage, I can even use it with it's temporal properties) all seems fine, until you try to select a some cell's with the normal select tool.

In this screenshot I selected the cells in the upper top of the canvas:

As you see other cells are selected, as if the ID's are interchanged or so?

This is a screencast of the same:

selectingItems.zip

[rduivenvoorde]

Another observation, IF you have a selection as in above picture, if you zoom in, your yellow selected grid cells will change !?

And another observation: using the Information-tool, clicking on the most left cell, you will be presented with exact the right cell: (Cell-ID 19900), so that tool 'selects' the right cells to show? Note you see more because it is a (time based) grid, zo there are several records in the data table which point to that grid cell.

[gioman]

@rduivenvoorde Hi Richard, possibly the same/related to #26804 ?

[rduivenvoorde]

@gioman yes it is I think, and reading:
#26804 (comment)
and
https://gdal.org/drivers/vector/gpkg.html#spatial-views
which tells us:

Starting with GDAL 2.2, in the case of the columns in the SELECT clause of the view acts a integer primary key, then it can be recognized by OGR as the FID column of the view, provided it is renamed as OGC_FID. Selecting a feature id from a source table without renaming will not be sufficient, since due to joins this feature id could appear several times. Thus the user must explicitly acknowledge that the column is really a primary key.

So I added a OGC_FID column to a new view: view4, and the selecting WORKS!!

To test: load view4 from this geopackage: cloud3.zip

Which brings me to the conclusion that this is not really an QGIS issue, but QGIS should maybe more carefull when loading a view from a Geopackage? Just like QGIS is when you load a view from Postgis: you have to tell QGIS which attribute to use as PK?

Current situation is not so nice for the user: you are able to use the select tool but you will have strange output?

[gioman]

Which brings me to the conclusion that this is not really an QGIS issue, but QGIS should maybe more carefull when loading a view from a Geopackage?

@rduivenvoorde I think that this matter should be raised also in the discussion that started today about GPKG on the developers mailing list.

Current situation is not so nice for the user: you are able to use the select tool but you will have strange output?

I agree that is far from good.

[rouault]

The requirement for the OGC_FID column could be relaxed for the upcoming 1.3 version (in public comment phase since a few days) of the GeoPackage standard that has a new requirement, better defining how views should be supported. http://www.geopackage.org/spec/#feature_user_tables :

Requirement 150

A feature table or view SHALL have a column that uniquely identifies the row. For a feature table, the column SHOULD be an integer primary key. If there is no primary key column, the first column SHALL be of type INTEGER and SHALL contain unique values for each row.

So for a GPKG 1.3 database, if a view is registered in gpkg_contents, its first column should act as a primary key column, whatever its name.

I've updated OSGeo/gdal#2479 consequently

[gioman]

Current situation is not so nice for the user: you are able to use the select tool but you will have strange output?

@rduivenvoorde should we change the title of this ticket to something along the lines "warn users if loading a SL/GPKG view with no unique values in the first column"?

[github-actions]

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale".
If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue.
In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue.
If there is no further activity on this issue, it will be closed in a week.

[rduivenvoorde]

@gioman done

[rduivenvoorde]

For those in which it is not clear: having your first (integer) column (a primary key) in view of table named as 'OGC_FID' will make QGIS do a normal selection.