Wrong identification of feature in a View

[qgib]

Author Name: matteo ghetta (@ghtmtt)
Original Redmine Issue: 18973
Affected QGIS version: 3.7(master)
Redmine category:data_provider

---

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 (matteo ghetta)
• geo.gpkg (matteo ghetta)

---

Related issue(s): #23523 (relates)
Redmine related issue(s): 15600

---

[qgib]

Author Name: Hugo Mercier (@mhugo)

---

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

---

• status_id was changed from Open to Feedback

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

Sounds something similar to #27753 (Fixed by #8052) but that one was spatialite

[qgib]

Author Name: matteo ghetta (@ghtmtt)

---

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

---

• 13578 was configured as geo.gpkg

---

• geo.gpkg (matteo ghetta)

[qgib]

Author Name: Hugo Mercier (@mhugo)

---

• relates was configured as Selecting features feature in spatial views and virtual layers leads to wrong results #23523

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

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

[qgib]

Author Name: Harrissou Santanna (@DelazJ)

---

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.

[qgib]

Author Name: matteo ghetta (@ghtmtt)

---

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

[qgib]

Author Name: Hugo Mercier (@mhugo)

---

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 (#8323) and spatialite layers (#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.

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

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.

---

• status_id was changed from Feedback to Closed
• resolution was changed from to no timely feedback

[qgib]

Author Name: matteo ghetta (@ghtmtt)

---

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.

---

• status_id was changed from Closed to Reopened

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

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?

---

• resolution was changed from no timely feedback to
• status_id was changed from Reopened to Feedback
• operating_system was changed from any to

[qgib]

Author Name: matteo ghetta (@ghtmtt)

---

last master 3.7

---

• version was changed from 3.1(master) to 3.7(master)

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

• status_id was changed from Feedback to Open

[ghtmtt]

guys, are there any WIP for making Views use more comfortable into a gpkg? I really really think that using a real database with such a complexity to create views is not user friendly.

[gioman]

guys, are there any WIP for making Views use more comfortable into a gpkg? I really really think that using a real database with such a complexity to create views is not user-friendly.

@ghtmtt I can't agree more... In DB manager we can run an arbitrary query on a Spatialite datasource and then with the "create view" button easily store it. With GPKGs that button is not there. Moreover, GPKGs views do NOT show in DB manager***, not even after the steps you describe in #26804 (comment) (with or without the tip from Hugo here #26804 (comment)).

I am very interested in understanding how much work would be needed to make GPKGs fully supported in DB Manager, and eventually do my share to support such effort.

*** Also GPKG rasters do not show in DB Manager see: #30932

[ghtmtt]

thanks @gioman for the feedback. IMHO having a real db as default (or at least suggested) file format like gpkg without having a nice method to add views is not use friendly.

Moreover: I just checked after you mentioned that also with spatialite there is the Create View button. It works but not for spatial layers: after creating a subset with a simple select, creating a view, the view is in the DB (also in DB Manager) but when adding the view to QGIS it is read a not spatial table. Don't know if this is the normal behavior with views. If not I can open a ticket, but just wanted to have a feedback before.

[gioman]

Moreover: I just checked after you mentioned that also with spatialite there is the Create View button. It works but not for spatial layers: after creating a subset with a simple select, creating a view, the view is in the DB (also in DB Manager) but when adding the view to QGIS it is read a not spatial table. Don't know if this is the normal behavior with views. If not I can open a ticket, but just wanted to have a feedback before.

@ghtmtt this is my observation:

• add a layer (i.e. shapefile) to QGIS
• save it as SL (call it "test")
• create a connection to this new SL dataset
• in DB Manager query "test":

select * from test

• is possible to add the result as "query layer" in QGIS
• if the "create view" is pressed a message error is returned (after choosing the view name)

insert on geometry_columns violates constraint: f_geometry_column value must be lower case

this happens because when saving the shape to SL QGIS calls the geom column "GEOMETRY" and not "geometry". The view is created anyway, but is not spatial.

• if the user changes the query to

SELECT ogc_fid,GEOMETRY AS geometry FROM test

then when clicking on "create view" there are no errors a the spatial view is correctly listed in DB Manager/Browser and added to the project.

[ffierm]

In my opinion, when we use the DBManager to create VIEWS or TABLES into a GPKG, a "system help" (more friendly) should registering "on the fly" the new objects...

[gioman]

a "system help" (more friendly)

sure, the whole discussion above is about suggesting exactly it.

[gioman]

@ghtmtt the original issue seems fixed on 3.24.0

having to do those manual steps to register a GPKG view is an horrible thing for final users, but I guess is a separate issue.

Author Name: matteo ghetta (@ghtmtt)

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

* 13578  was configured    as geo.gpkg


* [geo.gpkg](https://issues.qgis.org/attachments/download/13578/geo.gpkg) (matteo ghetta)

[ghtmtt]

@gioman issue solved, confirmed!

having to do those manual steps to register a GPKG view is an horrible thing for final users, but I guess is a separate issue.

definitely! IMHO having the nice button as we have for SpatiaLite would be a great improvement for Geopackage.

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

[ghtmtt]

@gioman should this issue converted into a feature request?

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

[ghtmtt]

ping @gioman just to know what to effectively do with this issue. Thanks!

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

[github-actions]

While we hate to see this happen, this issue has been automatically closed because it has not had any activity in the last 42 days despite being marked as feedback. If this issue should be reconsidered, please follow the guidelines in the previous comment and reopen this issue.
Or, if you have any further questions, there are also further support channels that can help you.