Creating new layer through DB Manager in QGIS 3

Question

When I load a polygon .shp-file into QGIS 3.6 and run a query finding the centroids of the polygons, I cannot load it back to the layers panel. The same operation works fine in QGIS 2.18.2.
The query looks like this:
select id, st_centroid(geometry) as geom
from polygon_table

My main intention is to do the same operation in PyQGIS 3, and automate this process by allowing the user to choose the shp file from a GUI and load both the polygons as well as their centroids simultaneously.

geozelot · Accepted Answer

There are several issues filed on the QGIS Issue Tracking list concerning this problem (this one being the one referencing the possible fix).

Until the patch has been released through the official software update repositories you seem to be able to solve the problem manually (according to the proposed fix):

locate file dlg_sql_window.py (in <QGIS>/python/plugins/db_manager/)
locate expression query = self._getSqlExecutableQuery() (line 396)
change to query = self._getExecutableSqlQuery()
save and restart QGIS

pdavis · Answer

I assume you're talking about creating a Virtual Layer on this previously loaded shapefile, using DB Manager. If this is the case, changing your query to use SpatiaLite syntax by changing 'geom' to 'geometry' should fix your problem.

Answered by pdavis on November 30, 2020

Creating new layer through DB Manager in QGIS 3

2 Answers

Add your own answers!

Ask a Question