2

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

2 Answers 2

2

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
0

I tried what JGH suggested without success (I couldn't make it work, but that could be I didn't wrote the code correctly). Finally I found a solution, was just adding a WHERE clause to the code:

select NM_Pivot_Ganadero_Superficies.fid, NM_Pivot_Ganadero_Superficies.UUID_Ganaderos_NM, NM_Pivot_Ganadero_Superficies.VF_Valor_Certificacion, st_union(General_PASCONIF.geometry) as geometry
from NM_Pivot_Ganadero_Superficies, General_PASCONIF
where General_PASCONIF.UUID_General is NM_Pivot_Ganadero_Superficies.UUID_General_NM
group by NM_Pivot_Ganadero_Superficies.UUID_Ganaderos_NM, NM_Pivot_Ganadero_Superficies.VF_Valor_Certificacion, General_PASCONIF.geometry

In the way, I changed the name of the layers, but essentially it's the same.

Your Answer

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

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