I'm looking to map the streams in which certain pollutants are found. These readings are points, and we assume that the water downstream of each point is contaminated. The mapping is not binary but additive.
I have so far realized a spatial join according to the proximity of the points and the hydrographic sections. Create a field by year of study and assigned the value 1 if the hydrography join = point match and 0 otherwise. I try to add the values in order to pass the values from upstream to downstream if possible under postgis (the method must be automated)
For the moment this what i have : pk (primary key) ; id_bdcarth (id of hydrological networks section) ; turbi_19 (value for origine of the pollution) ; id_nd_ini(id for the section's startpoint); id_nd_fini (same but for endpoint)
CREATE TABLE turbi.flowAdd_v1 AS WITH RECURSIVE additionflow (pk,turbi_19,id_bdcarth,id_nd_ini,id_nd_fin) AS( SELECT tu.pk, 0,--tu.turbi_19, tu.id_bdcarth, tu.id_nd_ini, tu.id_nd_fin from turbi."input" tu LEFT JOIN turbi."input" t2 ON tu.id_nd_ini = t2.id_nd_fin --WHERE t2.id_nd_fin IS NULL --valeur probablement a inverser id id_nd_ini ou alors is not null --WHERE t2.id_nd_fin IS NULL --WHERE t2.turbi_19 is not null UNION SELECT tu.pk, tu.turbi_19, tu.id_bdcarth, tu.id_nd_ini, tu.id_nd_fin FROM additionflow JOIN turbi."input" tu ON additionflow.id_nd_fin =tu.id_nd_ini ) SELECT pk, id_bdcarth, flow FROM (SELECT pk, id_bdcarth, SUM(turbi_19) flow FROM additionflow GROUP BY id_bdcarth,pk) f GROUP BY f.pk,f.id_bdcarth,flow ORDER BY flow ; select * from turbi.flowAdd_v1
But it is not working , i got just 0 or 1 and not 0,1,2,3,... as i expect.