QGIS 3 - Creating a new layer through DB Manager

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.

Answer 1

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

Answer 2

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.