1

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.

5
  • 1
    Hi and welcome! Could you please give some more information for example about what tools you have been using? Also what do you mean by "copy the value of the last section"?
    – RafDouglas
    Aug 31 at 17:57
  • 1
    Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community
    Aug 31 at 18:04
  • Hi ! I have a layer of points locating pollutant spills in the vicinity of the river network (cut into segments) and i need to map for each year between 2025-2030. I'm writing a postgis script to map year by year the pollution on the river network. Currently I proceeded to the join by proximity, of the dates (contained in the layer of point). Then create a field/year that I filled with the value 1 for pollution source segment for the year in question and value 0 if no join or out of date. Now I try to add the values (0 or 1) from upstream to downstream
    – SCH kiman
    Aug 31 at 18:21
  • it is the exact opposit of this : [link]imagizer.imageshack.com/a/img923/5183/tuucwm.jpg
    – SCH kiman
    Aug 31 at 18:23
  • 1
    Please Edit the question in response to requests for clarification. It's not fair to those who would help to need to scan comments for details.
    – Vince
    Aug 31 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.