back to first page ..

Adding transborder river dams

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

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';



--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)
--gid 293773
delete from geobs2010.roe_ccm_500_final_gid where ref_id in (

#TO DO
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

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é.
#6646 à garder 6647 à supprimer
#21610 à garder 21611 à supprimer
#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

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).