Transferring flows (connections + values) between polygons
You could do a join between the three layers, then aggregate by layerB
. Virtual layers can likely be used. I am not sure if the important data is in layerA
or in the flow
layer.. Here is an (untested) possibility:
SELECT b.id, b.geometry, sum(a.myVar)
FROM layerB b
LEFT JOIN flow f
ON ST_Intersects(ST_EndPoint(f.geometry),b.geometry)
JOIN layerA a
ON ST_Intersects(ST_StartPoint(f.geometry),a.geometry)
GROUP BY b.id
With the Virtual Layers, theoretically, it's possible (with shapefiles, the process will be extra long, but if the layers are in a Spatial Database, I think it is a lot faster).
Here the code :
WITH inter_ab AS (
--create intersection between LayerA and LayerB
SELECT LayerA.id || '_' || LayerB.FLAECHEID AS id,
LayerA.id AS id_a,
ST_AREA(LayerA.geometry) AS area_a,
LayerB.FLAECHEID AS id_b,
ST_INTERSECTION(LayerB.geometry, LayerA.geometry) AS geom
FROM LayerA, LayerB
WHERE ST_INTERSECTION(layerB.geometry, layerA.geometry) IS NOT NULL
),
--calculation of the new flux value
new_flux AS (SELECT t1.id_b AS origine,
t2.id_b AS dest,
SUM(Flows.flux * ST_AREA(t1.geom) / t1.area_a * ST_AREA(t2.geom) / t2.area_a) AS value
FROM inter_ab t1, inter_ab t2, flows
-- no connection between the same feature
WHERE t1.id <> t2.id
-- rule 1
AND t1.id_a <> t2.id_a
-- rule 2
AND t1.id_b <> t2.id_b
-- get flow data
AND flows.origine = t1.id_a
AND flows.dest = t2.id_a
GROUP BY t1.id_b, t2.id_b
)
--create flows between original layerB features
SELECT new_flux.origine,
new_flux.dest,
new_flux.value AS flux,
make_line(ST_CENTROID(t3.geometry), ST_CENTROID(t4.geometry)) AS geom --ST_MakeLine under postGIS
FROM LayerB t3,
LayerB t4,
new_flux
WHERE t3.FLAECHEID = new_flux.origine
AND t4.FLAECHEID = new_flux.dest
The graphical output will look like
The result was tested manually. The difference in "FLUX"
values is neglectable.
The final output will inherit styles from "Flow"
and look like
I recommend to test it with a few data, and if it takes too long for large data sets, execute step by step the queries ("inter_ab"
, "new_flux"
) and save the result and execute the next query.