3

There is a polygon layer 'Union23' that was created with the "Union" tool.

Polygon_Layer

Layer's attribute table includes the following values.

AT_values

My effort is simply to aggregate those polygons by "FLAECHEID" field with the sum and count of a field "In_Value1" via a Virtual Layer. So I am using the expression:

SELECT
    *,
    SUM(In_Value1),
    COUNT(In_Value1)
FROM
    Union23
GROUP BY
    "FLAECHEID"

However, I am getting really weird output and I do not understand why I am losing a part of geometry on the way? Any suggestions?

VL_Output

References:

Taras
  • 32,823
  • 4
  • 66
  • 137

1 Answers1

4

When using a mix of an aggregate function and a regular row, SQLite will return a random row for the not-aggregated field (see doc, point 3). To overcome this, don't use * but properly aggregated fields, including on the geometry

SELECT ST_UNION(geometry), SUM(In_Value1), COUNT(In_Value1)
FROM Union23
GROUP BY "FLAECHEID"
JGH
  • 41,794
  • 3
  • 43
  • 89
  • So to say, If I want to maintain all of my fields I will need to perform it manually instead of star-symbol *, am I right? – Taras Mar 12 '19 at 12:03
  • Yes. with min/max etc, unless they all have the same value and a random row is OK – JGH Mar 12 '19 at 12:05
  • You could also do a join on Flaecheid between the spatial layer and the newly computed stat table - that would not contain any geometry - – JGH Mar 12 '19 at 12:14
  • yeap, I think this will be the best option via Join. thank you – Taras Mar 12 '19 at 12:18
  • 2
    I often find that unexpected results can be traced to bad geometry, particularly with shapefiles. Consider validating both the input and output layer's geometry with Check Geometry. You can also Repair. Then – Stu Smith Mar 13 '19 at 01:59
  • @JGH, why not extending your answer ? I do not want to put a new answer ... it is meaningless – Taras Feb 18 '22 at 12:56
  • @Taras because your edit had nothing to do with the question. Saying/showing that your particular geometry fails to union perfectly is not related to the fact that you need to put an aggregate on the geometry field. Feel free to add this information as a comment or as an edit to your question (but not as en edit to the answer) – JGH Feb 18 '22 at 13:04
  • idk, it is a bit strange for me. I just wanted to show that your query works well, and give the desired output, that is all ... – Taras Feb 18 '22 at 13:15