Distance source

back to first page ..

alter table ccm21.riversegments add column distance_source numeric;

The distance source is calculated by the following request : The distance sea corresponds to the distance of the downstream node of each riversegment The distance source for a riversegments A is the maximum of the distance + length of the upstream riversegment Note the upstream catchment function does not return the catchment upstream... so we have to use the upstream_segment function

In the ccm we find the cum_length which is the cumulated length of upstream riversegments but not the distance to the farthest source

select round(max(cum_len_sea+shape_leng) -(select cum_len_sea from ccm21.riversegments where gid =234706)) 
as dist_source from ccm21.riversegments where gid in (select ccm21.upstream_segments(234706));

The calculation in implemented as method in the BaseEdaCCMRiversegments.r class and called in main_CCM_France

Problem :
The algorithm stopped at i = 160830 (170828 lines)
"erreur dans if(!is.na(gid)&mtch[i]){ valeur manquante là où TRUE/FALSE est requis"

select cum_len_sea from ccm21.riversegments where gid =381001 --> 794987
select ccm21.upstream_segments(381001) --> ne renvoie aucune ligne (correspond to a segment in the Basque country in Spain)
select round(max(cum_len_sea+shape_leng) -(select cum_len_sea from ccm21.riversegments where gid =381001))as dist_source

from ccm21.riversegments where gid in (select ccm21.upstream_segments(381001)); --> renvoie une ligne avec une cellule vide

gid=381001 appartient à zonegeo="France"
J'ai lancé la suite :
distance_source(ccm,silent=TRUE,from=160831)
-->idem

Avec cette requête :--> le gid n'est pas dans la liste

select distinct on (rs.gid) rs.gid  from ccm21.riversegments rs 
inner JOIN ccm21.riversegments rup on rup.tonode=rs.fromnode 
where rs.wso_id in (select wso_id from france.wso where area='France')
order by gid;

Pour un segment amont, la distance à la source est la longueur du segment.

update ccm21.riversegments set distance_source=shape_leng where gid in(
select distinct on (rs.gid) rs.gid from ccm21.riversegments rs
where rs.wso_id in (select wso_id from france.wso where area='France')
except (        
select distinct on (rs.gid) rs.gid  from ccm21.riversegments rs
        inner join ccm21.riversegments rup on rup.tonode=rs.fromnode
        where rs.wso_id in (select wso_id from france.wso where area='France'))
        order by gid)