Bug report #18973

Wrong identification of feature in a View

Added by matteo ghetta over 6 years ago. Updated over 5 years ago.

Status:Open
Priority:High
Assignee:-
Category:Data Provider
Affected QGIS version:3.7(master) Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:26804

Description

I have created a very simple view in a geopackage layer. Trying to use the selection tool AND the identify tool results in a wrong feature identification.

With the selection tool wrong features are selected while with the identify tool the form of the same feature is opened again and again.

A small video in attach to better see the error.

BTW: the original layer (from where the view comes from) works perfectly, so it really seems a view related problem

bug.mp4 (425 KB) matteo ghetta, 2018-05-17 03:36 PM

geo.gpkg (96 KB) matteo ghetta, 2018-10-24 09:14 AM

Related issues

Related to QGIS Application - Bug report #15600: Selecting features feature in spatial views and virtual l... Open 2016-09-21

History

#1 Updated by Hugo Mercier about 6 years ago

  • Status changed from Open to Feedback

Can you provide your test data / project ?
Does the field that serves as unique identifier in the view is correct ?

#2 Updated by Alessandro Pasotti about 6 years ago

Sounds something similar to #19930 (Fixed by https://github.com/qgis/QGIS/pull/8052) but that one was spatialite

#3 Updated by matteo ghetta about 6 years ago

hi guys, of course! In attach a geopackage with a single layer.

I created the view within the DB manager (a view that actually is just an exact copy of the source layer), as:

CREATE VIEW vista AS
SELECT * FROM cities

then registered the view in the gpkg_content table:

INSERT INTO gpkg_contents (table_name, identifier, data_type, srs_id)
VALUES ( 'vista', 'vista', 'features', 3003)

Finally registered also the geometric information in the gpkg_geometry_columns table:

INSERT INTO gpkg_geometry_columns (table_name, column_name, geometry_type_name, srs_id, z, m) 
VALUE ('vista', 'geom', 'GEOMETRY', 3003, 0, 0)

Am I doing something wrong with the view creation?

Thanks guys

#4 Updated by Hugo Mercier about 6 years ago

  • Related to Bug report #15600: Selecting features feature in spatial views and virtual layers leads to wrong results added

#5 Updated by Giovanni Manghi about 6 years ago

then registered the view in the gpkg_content table:

[...]

Finally registered also the geometric information in the gpkg_geometry_columns table:

[...]

Side comment: this is really what a user must do to see a (spatial) view show up in its GPKG datasource in QGIS?!
Can't really we do this automatically?

By the way, I just tried to register the view/spatial information as described by Matteo and was greeted with this (not really clear) message:

SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140167101355776 and this is thread id 140167109748480.

#6 Updated by Harrissou Santanna about 6 years ago

Side comment: this is really what a user must do to see a (spatial) view show up in its GPKG datasource in QGIS?!
Can't really we do this automatically?

Agreed! If these steps are required then it would make gpkg use only for an elite and hard to replace shp.

#7 Updated by matteo ghetta about 6 years ago

yes, fully agreed. This improvement could be another step towards the using geopackages instead of shapefile

#8 Updated by Hugo Mercier about 6 years ago

Thanks. It is the same problem as with virtual layers and spatialite layers. When no primary key is defined, an id is returned that is not the same when querying the whole layer or when querying only a subset (with a rectangle or by id).

According to OGR documentation, you can manually specify which column will serve as a primary key for your view by naming it "OGC_FID".

https://www.gdal.org/drv_geopackage.html

So for instance:

CREATE VIEW vista AS
SELECT fid AS OGC_FID, * FROM cities

To fix this in the general case, we should apply a fix similar to the ones for virtual layers (https://github.com/qgis/QGIS/pull/8323) and spatialite layers (https://github.com/qgis/QGIS/pull/8327) : do not use SQL filters if we do not have a primary key defined. I'll see what is possible with the OGR provider.

#9 Updated by Jürgen Fischer over 5 years ago

  • Status changed from Feedback to Closed
  • Resolution set to no timely feedback

Bulk closing 82 tickets in feedback state for more than 90 days affecting an old version. Feel free to reopen if it still applies to a current version and you have more information that clarify the issue.

#10 Updated by matteo ghetta over 5 years ago

  • Status changed from Closed to Reopened

issue is still valid. I don't know what could be the easiest fix, but maybe having the `save as view` button in DB manager also for geopackage could speed up a lot this problem.

#11 Updated by Giovanni Manghi over 5 years ago

  • Resolution deleted (no timely feedback)
  • Status changed from Reopened to Feedback
  • Operating System deleted (any)

matteo ghetta wrote:

issue is still valid. I don't know what could be the easiest fix, but maybe having the `save as view` button in DB manager also for geopackage could speed up a lot this problem.

on what version?

#12 Updated by matteo ghetta over 5 years ago

  • Affected QGIS version changed from 3.1(master) to 3.7(master)

last master 3.7

#13 Updated by Giovanni Manghi over 5 years ago

  • Status changed from Feedback to Open

Also available in: Atom PDF