4

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?

2
  • In typing this question you already invested more time you would have if you just typed all the columns by hand ;-). I also often fall into this "automation trap". See xkcd.com/1319 .
    – til_b
    Sep 21, 2020 at 7:43
  • Your second SQL block is not simply a query but does create temp tables. Depending on the specifics of how QGIS executes the code for the virtual statement this may work or not work.
    – til_b
    Sep 21, 2020 at 7:46

2 Answers 2

5

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.

enter image description here

6
  • One more problem. When not using "autodetect" to generate the geometry, point layer generated is invisible. I tried using different CRS, but that doesn't seem to make a difference. Sep 12, 2020 at 11:28
  • it should be the same as the source CRS
    – JGH
    Sep 12, 2020 at 11:29
  • Yes, I set both of them to WGS 84, but the point layer is invisible. Sep 12, 2020 at 11:31
  • :-/ does it work if you do just select b.id, st_centroid(b.geometry) as geometry with auto-detect? Could it be a multi-point instead?
    – JGH
    Sep 12, 2020 at 11:34
  • It works with "autodetect". Sep 12, 2020 at 11:36
3

You could switch the order in your select statement.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.