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?