Creating Virtual layer that is the centroid of another polygon layer

Question

There's a polygon layer building and I want to create a point layer centroid containing centroids of building layer. I have 2 more requests:

• to keep all attributes in the building layer in the newly-created layer.
• to make the newly-created layer auto-updating when I edit the building layer.

So, I created a virtual layer. Here's the query I made:

select b.*, st_centroid(geometry) 
from "building" as b

But, that created a virtual polygon layer instead of a point layer. It seems that because I selected all attributes from the building layer, which also includes the geometry attribute. There are dozens of attributes in the building layer, so typing them all out in the query is time-consuming. Thus, I found a post talking about how to select all but one column using SQL. Then I tried this:

SELECT b.*, st_centroid(geometry) into #TempTable
FROM "building" as b
ALTER TABLE #TempTable
DROP COLUMN geometry
SELECT * FROM #TempTable
DROP TABLE #TempTable

This time it says "syntax error". Could someone help?

Answer 1

It is ok to have multiple geometries in a table, but you would have to tell QGIS which one to use when creating the virtual layer.

Since virtual layers are always recomputed (each time you pan or zoom the map), modifications in the source layer will be shown.

To get the field list, you can export the layer style file, open it with a text editor, and copy-paste the fields name.

Answer 2

You could switch the order in your select statement.

SELECT ST_CENTROID(geometry), b.*
FROM "building" AS b