back to first page [..][[BR]]
back to ["Obstacle pressure"][[BR]]
back to ["CookBook join ROE_CCM"][[BR]]

["Adding transborder river dams"]

Some dams must be reproject and the gid (or wso1_id) must be changed:[[BR]]

{{{
DROP table if exists geobs2010.roe_ccm_500_final_gid;
CREATE TABLE geobs2010.roe_ccm_500_final_gid as (
select distinct on (ref_id) * from geobs2010.roe_ccm_500_final
);
update geobs2010.roe_ccm_500_final_gid SET gid='266956' where ref_id='6186';
update geobs2010.roe_ccm_500_final_gid SET gid='266956' where ref_id='6187';
update geobs2010.roe_ccm_500_final_gid SET gid='266956' where ref_id='6188';
update geobs2010.roe_ccm_500_final_gid SET gid='243685' where ref_id='7372';
update geobs2010.roe_ccm_500_final_gid SET gid='243685' where ref_id='7371';
update geobs2010.roe_ccm_500_final_gid SET gid='244061' where ref_id='7918';
update geobs2010.roe_ccm_500_final_gid SET gid='235907' where ref_id='7908';
update geobs2010.roe_ccm_500_final_gid SET gid='282306' where ref_id='15460';
update geobs2010.roe_ccm_500_final_gid SET gid='234501' where ref_id='27704';
update geobs2010.roe_ccm_500_final_gid SET gid='298703' where ref_id='47867';
update geobs2010.roe_ccm_500_final_gid SET gid='294805' where ref_id='10362';
update geobs2010.roe_ccm_500_final_gid SET gid='291613' where ref_id='12546';
update geobs2010.roe_ccm_500_final_gid SET gid='309071' where ref_id='26132';
update geobs2010.roe_ccm_500_final_gid SET gid='324115' where ref_id='20881';
update geobs2010.roe_ccm_500_final_gid SET gid='322893' where ref_id='19021';
}}}

#TO DO[[BR]]
Barrages mal projetés (à entrer dans la table roe_ccm_500_final_gid :
Trois barrages projetés sur gid=265936 or doivent être projetés sur 266956 (wso1_id=379706).--> nb barrages à modifier et cs_nbdams à modifier pour segments à l'amont.

||ref_id||gid ds roe_ccm_500_final||A projeter sur gid ||
||6186||265936||266956||
||6187||265936||266956||
||6188||265936||266956||
||7372||233625||243685||
||7371||233625||243685||
||7918||242614||244061||
||7908||233411||235907||

#Requête pour vérifier si présente des barrages mal projetés
select * from geobs2010.roe_ccm_500_final where distproj_bdcar-distproj_ccm>200



#Problème de doublons
{{{
select gid, id_trhyd, dist_source_bdcar, dist_sea_bdcar, height, distproj_bdcar, nbdams, distproj_ccm, count(dist_source_bdcar) as Doublons from geobs2010.roe_ccm_500_final group by dist_source_bdcar, gid, id_trhyd, dist_sea_bdcar, height, distproj_bdcar, nbdams, distproj_ccm order by doublons

select gid, id_trhyd, dist_source_bdcar, dist_sea_bdcar, height, distproj_bdcar, nbdams, distproj_ccm, count(distproj_ccm) as Doublons from geobs2010.roe_ccm_500_final group by dist_source_bdcar, gid, id_trhyd, dist_sea_bdcar, height, distproj_bdcar, nbdams, distproj_ccm order by doublons
}}}

A vérifier : gid=298703, 293773, 266956, 272629  (pas de statut la plupart du temps)
{{{
select * from geobs2010.roe_ccm_500_final where gid='298703'
select o.ref_id_national, r.*, o.ref_sta_id from geobs2010.roe_ccm_500_final r inner join geobs2010.obstacle_referentiel o on o.ref_id=r.ref_id where gid='298703'
}}}

In geobs2010.roe_ccm_500_final (see http://www.moulinsdefrance.org/ffam/DescriptifROE.pdf page 14)
||Statut||sta_id||Nb barrages (nb lignes)||
||En projet -mis hors service||0||21||
||En construction -mis hors service||1||35||
||Existant -mis en service/mis hors service||2||23081||
||Obsolète - mis hors service||3||2913||
||sans statut||||112136||

[[BR]]
||Etat||Nb barrages (nb lignes)||
||0 - Validé||35006||
||1 - Confirmé||256||
||2 - Non validé||2004||

In "DescriptifROE": "Seuls les ouvrages validés ont été intégrés au référentiel des obstacles à l'écoulement" (page15)

#A vérifier
"wso1_id"='341459' OR "wso1_id"='351706' OR "wso1_id"='341485'


#Problèmes de projections
{{{
select * from geobs2010.roe_ccm_500_final where distproj_bdcar-distproj_ccm>200
}}}

#Ref_id avec les mêmes hauteurs
{{{
select * from 
 (select count(*) as num, gid as gid1,distproj_ccm as distproj_ccm1,dist_sea_ccm as dist_sea_ccm1, max(height) as height1 from geobs2010.roe_ccm_500_final 
	where height is not null group by gid,distproj_ccm,dist_sea_ccm, height) as f1
join geobs2010.roe_ccm_500_final f2 on (f1.gid1,f1.distproj_ccm1,f1.dist_sea_ccm1)=(f2.gid,f2.distproj_ccm,f2.dist_sea_ccm) 
 where num>1 
order by num, distproj_ccm, gid   --26 lignes
}}}

On conserve les obstacles où il y a le plus de données disponibles et avec le ref_id le plus élevé.[[BR]]
#A voir 27676, 27677, 27678  27676 à conserver ? Peut-être est-ce deux obstacles différents (pas le même nom) mais ont des localisation identiques [[BR]]
{{{

--Supression des doublons
--gid 298703
delete from geobs2010.roe_ccm_500_final_gid where ref_id in (select r.ref_id from geobs2010.roe_ccm_500_final r inner join geobs2010.obstacle_referentiel o on o.ref_id=r.ref_id where gid='298703' and ref_sta_id is null)

delete from geobs2010.roe_ccm_500_final_gid where ref_id in (50516,6186,6647,21611,22199,24332,24425,27676,27677,28517,48653,5560,5561,24211)  ---14 lignes

delete from geobs2010.roe_ccm_500_final_gid where ref_id in (19490,46319,44656,28268,1351,41222,18132,45254,32113,50953,27194,27205,46223,16814,	2148,37320,3299,44105,43933,44660,31523,11516,23932,7925,18277,35738,26295,16968,36528,41456,35815,27980,26988,21748,17325,34879,25841,51625,46010,37184,43448,32398,30628,44841,27309,27328,10369,10371,10372) --- 49 lignes

delete from geobs2010.roe_ccm_500_final_gid where ref_id in (7626,14304,40231,27466,36825,46539,40169,39608,40184,25754,312213,47109,37138,4414,24538,46938,38159,24291,
28183,7326,11077,10648,25010,24323,46929,41628,25940,17967,11795,51218,24295,11721,5307,33793,26832,13537,46936,6181,30536,41009,22193,28903,4318,46930,46915,46919,8502,15448,46939,46946,46931,49979,46932,4716,46927,8500,6579,17766,23955,41011,41012,20282)  ---61 lignes
}}}
--> 37059 lignes après suppression des doublons (avant :37266 lignes).

Barrages à supprimer, qui ne doivent pas être projetés sur la CCM car pas sur le bon cours d'eau correspondant.
{{{
delete from geobs2010.roe_ccm_500_final_gid where ref_id in (10395)
}}}


#Après avoir refait tourner la CCM pas le calcul pour ROE
J'ai des cs_nbdams et nbdams =NA
{{{
select * from geobs2010.roe_ccm_500_final where gid in (69494,71197,248812,277251,304534,312705,316586)
}}}
Ces gid (wso_id correspondants : 125196 125112 401675 342882 350998 387315 347851) ne sont pas dans france.wso [[BR]]
Or c'est france.wso qui a été utilisé pour crée la table riversegments_france --> A modifier et/ou à utiliser europe.wso dorénavant.[[BR]]
{{{
insert into france.wso(wso_id) values ('125196'),('125112'),('401675'),('342882'),('350998'),('387315'),('347851');
UPDATE france.wso set area='France' where area IS NULL;
}}}


Vérification de certain barrages sélectionnés pour savoir s'ils ont été projetés sur le bon cours d'eau.[[BR]]
Le seuil de distance est à modifier (10-30-50m plus ?)[[BR]]
Tous les ref_id avec une distance <=15 ont été vérifiés[[BR]]
avec une distance_sea_ccm <30
{{{
SELECT s.ref_id, s.gid ,s.dist_sea_ccm, s.id_trhyd, CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance, s.the_geom 
FROM geobs2010.roe_ccm_500_final_gid as s 
inner join ccm21.rivernodes_france r on ST_DWithin(r.the_geom, s.the_geom,50) order by s.dist_sea_ccm, distance

SELECT s.ref_id, s.gid ,s.dist_sea_ccm, s.id_trhyd, CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance, s.the_geom 
FROM geobs2010.roe_ccm_500_final_gid as s inner join ccm21.rivernodes_france r on ST_DWithin(r.the_geom, s.the_geom,50) 
where CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1))>15 order by s.dist_sea_ccm, distance;

SELECT s.ref_id, s.gid, s.id_trhyd, s.dist_sea_ccm, CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance, s.the_geom 
               FROM geobs2010.roe_ccm_500_final_gid as s inner join ccm21.rivernodes_france r on ST_DWithin(r.the_geom, s.the_geom,50) where CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1))>15 and dist_source_ccm>200 order by s.dist_sea_ccm, distance, dist_source_ccm;
}}}
Changements effectués :
{{{
update geobs2010.roe_ccm_500_final_gid SET gid='229065' where ref_id='41580';
update geobs2010.roe_ccm_500_final_gid SET gid='324318' where ref_id='36390';
update geobs2010.roe_ccm_500_final_gid SET gid='319424' where ref_id='6067';
update geobs2010.roe_ccm_500_final_gid SET gid='322781' where ref_id='40060';
update geobs2010.roe_ccm_500_final_gid SET gid='316241' where ref_id='47207';
update geobs2010.roe_ccm_500_final_gid SET gid='227173' where ref_id='37731';
update geobs2010.roe_ccm_500_final_gid SET gid='282106' where ref_id='32230';
update geobs2010.roe_ccm_500_final_gid SET gid='272700' where ref_id='24054';
update geobs2010.roe_ccm_500_final_gid SET gid='295827' where ref_id='49391';
update geobs2010.roe_ccm_500_final_gid SET gid='305966' where ref_id='47198';
update geobs2010.roe_ccm_500_final_gid SET gid='271431' where ref_id='2515';
update geobs2010.roe_ccm_500_final_gid SET gid='322535' where ref_id='20123';
update geobs2010.roe_ccm_500_final_gid SET gid='312429' where ref_id='18311';
update geobs2010.roe_ccm_500_final_gid SET gid='296866' where ref_id='35950';
update geobs2010.roe_ccm_500_final_gid SET gid='233598' where ref_id='15939';
update geobs2010.roe_ccm_500_final_gid SET gid='262912' where ref_id='34914';
update geobs2010.roe_ccm_500_final_gid SET gid='313840' where ref_id='32255';
update geobs2010.roe_ccm_500_final_gid SET gid='309259' where ref_id='16258';
update geobs2010.roe_ccm_500_final_gid SET gid='273463' where ref_id='50848';
update geobs2010.roe_ccm_500_final_gid SET gid='300460' where ref_id='44999';
update geobs2010.roe_ccm_500_final_gid SET gid='183827' where ref_id='43231';
update geobs2010.roe_ccm_500_final_gid SET gid='328053' where ref_id='49884';
update geobs2010.roe_ccm_500_final_gid SET gid='236848' where ref_id='43508';
update geobs2010.roe_ccm_500_final_gid SET gid='244317' where ref_id='26996';
update geobs2010.roe_ccm_500_final_gid SET gid='318345' where ref_id='37675';
update geobs2010.roe_ccm_500_final_gid SET gid='148749' where ref_id='43181';
update geobs2010.roe_ccm_500_final_gid SET gid='296196' where ref_id='24006';
update geobs2010.roe_ccm_500_final_gid SET gid='318698' where ref_id='5074';
update geobs2010.roe_ccm_500_final_gid SET gid='245805' where ref_id='10089';
update geobs2010.roe_ccm_500_final_gid SET gid='220980' where ref_id='31429';
update geobs2010.roe_ccm_500_final_gid SET gid='270625' where ref_id='31817';
update geobs2010.roe_ccm_500_final_gid SET gid='295776' where ref_id='38184';
update geobs2010.roe_ccm_500_final_gid SET gid='225709' where ref_id='8386';
update geobs2010.roe_ccm_500_final_gid SET gid='296830' where ref_id='14783';
update geobs2010.roe_ccm_500_final_gid SET gid='284555' where ref_id='6327';
update geobs2010.roe_ccm_500_final_gid SET gid='289420' where ref_id='24740';
update geobs2010.roe_ccm_500_final_gid SET gid='308062' where ref_id='35779';
update geobs2010.roe_ccm_500_final_gid SET gid='279821' where ref_id='18728';
update geobs2010.roe_ccm_500_final_gid SET gid='245328' where ref_id='33987';
update geobs2010.roe_ccm_500_final_gid SET gid='331175' where ref_id='39397';
update geobs2010.roe_ccm_500_final_gid SET gid='224744' where ref_id='25220';
update geobs2010.roe_ccm_500_final_gid SET gid='301578' where ref_id='11859';
update geobs2010.roe_ccm_500_final_gid SET gid='181075' where ref_id='5221';
update geobs2010.roe_ccm_500_final_gid SET gid='194355' where ref_id='52921';
update geobs2010.roe_ccm_500_final_gid SET gid='103034' where ref_id='52911';
update geobs2010.roe_ccm_500_final_gid SET gid='198245' where ref_id='51136';
update geobs2010.roe_ccm_500_final_gid SET gid='207345' where ref_id='51152';
update geobs2010.roe_ccm_500_final_gid SET gid='322878' where ref_id='46488';
update geobs2010.roe_ccm_500_final_gid SET gid='301479' where ref_id='52440';
update geobs2010.roe_ccm_500_final_gid SET gid='296773' where ref_id='12205';
update geobs2010.roe_ccm_500_final_gid SET gid='290135' where ref_id='13518';

update geobs2010.roe_ccm_500_final_gid SET gid='' where ref_id='';
}}}

Pour savoir quels sont les barrages (ref_id) qui ont été vérifiés, ajout de la colonne verificationccm
{{{
alter table geobs2010.roe_ccm_500_final_gid add column verificationccm boolean;
update geobs2010.roe_ccm_500_final_gid SET verificationccm='t' where ref_id in (6187,6188,7372,7371,7918,7908,15460,27704,47867,10362,12546,26132,20881,19021);

update geobs2010.roe_ccm_500_final_gid SET verificationccm='t' where ref_id in (SELECT s.ref_id 
FROM geobs2010.roe_ccm_500_final_gid as s inner join ccm21.rivernodes_france r on ST_DWithin(r.the_geom, s.the_geom,50) 
where CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1))<15.1);

update geobs2010.roe_ccm_500_final_gid SET verificationccm='t' where ref_id in (SELECT s.ref_id 
FROM geobs2010.roe_ccm_500_final_gid as s inner join ccm21.rivernodes_france r on ST_DWithin(r.the_geom, s.the_geom,50) 
where dist_sea_ccm<30 and CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1))>15);

update geobs2010.roe_ccm_500_final_gid SET verificationccm='t' where ref_id in (12205,13518);
}}}


Nombre de barrages projetés suivant le ref_eta_id
||ref_eta_id||Nb dam||
||0||34895 (94.1%)||
||1||255 (0.69%)||
||2||1908 (5.1%)||