4

I have two polygon layers that partially overlap: buidings and plots. Each layer has 126 features.

I want to create a Virtual Layer that is simply an addition of the two layers, thus it should contain 254 features (all features from buildings + all features from plots).

This is what I tried:

select b.geometry, p.geometry as geom
from building as b, plot as p

select st_union (b.geometry, p.geometry) as geom
from building as b, plot as p

I also tried replacing st_union by combine. However, every time I get 15'876 features, thus 126 * 126 instead of 126 + 126 features.

How should the query be to get a simple addition of the features of both layers?

Taras
  • 32,823
  • 4
  • 66
  • 137
Babel
  • 71,072
  • 14
  • 78
  • 208

1 Answers1

6

You should union the 2 selects like:

SELECT b.geometry
FROM building b
UNION
SELECT p.geometry
FROM plot p;
eurojam
  • 10,762
  • 1
  • 13
  • 27
  • 2
    Keep in mind that if more fields should be selected, pay attention that dimension of each selection have to be equal, otherwise it may lead to this error: Query preparation error on PRAGMA table_info(_tview): SELECTs to the left and right of UNION do not have the same number of result columns – Taras Sep 01 '21 at 11:35
  • @Taras thanks for this clarification. So I guess converting geometries using centroid (to point), buffer (to polygon) or boundary/buffer-boundary (to line) will solve the problem, right? – Babel Sep 01 '21 at 13:49
  • It depends what you are trying to do. My message was more about the dimension of your selection i.e. attributes you are trying to select (geometry is "one of them") – Taras Sep 02 '21 at 06:38