Bug report #18974

No geometry operation on geopackages layers

Added by matteo ghetta about 2 years ago. Updated over 1 year ago.

Status:Open
Priority:Normal
Assignee:-
Category:DB Manager
Affected QGIS version:3.6.0 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:26805

Description

In DB manager it seems that it is not possible to make any complex or even simple geometric or spatial operation with layers stored in geopackage. In attach a very simple geopackage with a single layer.

Trying to run the query:

SELECT ST_AREA(geom) AS area, geom FROM province

the area column in output contains only NULL values, while the geom column, even if it is possible to load the layer as a new layer, contains values like: `b'GP\x00\x03.........`.

In the layer table the geom column is correctly read with, e.g. MULTIPOLYGON... type

The exact same query works on the same layer loaded in QGIS but using Virtual Layers. Output area column is correctly calculated and geometry is correctly displayed as MULTIPOLIGON type.

In attach the used geopackage with the layer

bug.gpkg (1.03 MB) matteo ghetta, 2018-05-17 04:46 PM

Selection_007.png (102 KB) matteo ghetta, 2018-09-24 08:28 PM


Related issues

Related to QGIS Application - Bug report #20090: GeoPackage SQL issues in DB Manager Closed 2018-10-12
Related to QGIS Application - Bug report #21028: DB manager: SQLite objects created in a thread can only b... Closed 2019-01-18

History

#1 Updated by Dominique Lyszczarz almost 2 years ago

Confirmed on 3.3

#2 Updated by Giovanni Manghi almost 2 years ago

  • Status changed from Open to Feedback
  • Affected QGIS version changed from 3.1(master) to 3.3(master)

Does it works on 2.18?

This type of operation also works if the dataset is Spatialite.

#3 Updated by matteo ghetta almost 2 years ago

yes and no :) the query still gives NULL values in the geom column, but the layer can be correctly loaded in QGIS (see attached screenshot).

Moreover, same gpkg and same layer, with QGIS 3.3 I'm having a python stacktrace:

RecursionError
maximum recursion depth exceeded while calling a Python object

#4 Updated by Giovanni Manghi over 1 year ago

#5 Updated by matteo ghetta over 1 year ago

  • Affected QGIS version changed from 3.3(master) to 3.5(master)

#6 Updated by Giovanni Manghi over 1 year ago

  • Status changed from Feedback to Open
  • Affected QGIS version changed from 3.5(master) to 3.6.0
  • Operating System deleted (any)

Now when trying to run query against a GPKG in DB Manager the results do not even show, instead a very reassuring

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

is shown. Not good for what should be a first class citizen in QGIS.

#7 Updated by Giovanni Manghi over 1 year ago

  • Related to Bug report #21028: DB manager: SQLite objects created in a thread can only be used in that same thread added

Also available in: Atom PDF