QGIS – How to Use ST_Union for Polygon and Table Operations

qgissql

In QGIS I have:

  1. A polygon table (A) with farmed area. It has an UUID field.
  2. A no geometry table (B) with farmers data. It has a UUID field.
  3. A no geometry pivot table (C) that relates them both N-M, so 1 farm can have several farmers and 1 farmer can work on several farms. It has the 2 UUID fields of the other tables and a farming intensity field (with values from 0 to 90).

In green farm Nº1 with intensity 90 and only 1 farmer, in orange farm Nº2 with intensity 20 and only 1 farmer and in light brown farm Nº3 with intensity 40 and 2 farmers (the previous ones combined)

I want a virtual layer with 2 fields both taken from table C:

  1. Farming intensity (that is previously taken from A via the relationships)
  2. Farmer UUID (that is previously taken from B via the relationships)
    Also, this virtual layer must have the geometry from A duplicating it where there are 2 farmers working the same farm (this duplicated farm will have the same farm intensity but different farmers).

I usually do this manually with dissolve and manually duplicating the geometries in which there are several farmers, but this is too much time consuming.
I looked for a ST_Dissolve function that may somehow work with the related table C, but is not implemented.
With ST_union, the most I achieve is to have the correct fields and rows but them all with the whole geometry. In the image is shown the attribute table of the virtual layer with the farmer UUID and farming intensity assigned to the farmer, but the geometry is made of 4 features all of them the same extent:

enter image description here

This is the code I used for the virtual layer:

select NM_Relationship_Ganaderos_Certificacion.fid, NM_Relationship_Ganaderos_Certificacion.Ganadero_UUID, NM_Relationship_Ganaderos_Certificacion.Valor_Certificacion, st_union(Certificacion_Campo_Plantilla_v3.geometry) as geometry
from NM_Relationship_Ganaderos_Certificacion, Certificacion_Campo_Plantilla_v3
group by NM_Relationship_Ganaderos_Certificacion.Ganadero_UUID, NM_Relationship_Ganaderos_Certificacion.Valor_Certificacion, Certificacion_Campo_Plantilla_v3.geometry

Best Answer

If I understand correctly, you don't need to aggregate the geometries.

As it is, you are doing a cross join between the two tables, then you are trying to aggregate/separate the records using some group by. Instead, try using a join with some conditions

SELECT a.*,b.*
FROM myPivotTable c
 JOIN myLayer a ON a.uuid = c.uuid
 JOIN myTable b ON b.uuid = c.uuid
Related Question