wiki:PATE Anguille

Version 54 (modified by celine, 13 years ago) (diff)

--

back to first page..
back to RHT
Go to RHT PATE

"id_roe" in ('ROE50534','ROE65180','ROE66096','ROE47382','ROE47384','ROE64290','ROE47361','ROE47363','ROE47352','ROE46580','ROE47341','ROE47362','ROE49696','ROE47348','ROE47353','ROE47342','ROE47350','ROE47358','ROE47359','ROE47373','ROE47374','ROE47409','ROE47412','ROE64330','ROE47356','ROE47357','ROE47360','ROE46281','ROE52263','ROE67681')

Integration des données brutes

Access puis : 

alter table "ROE-BV" set schema rht
alter table rht."ROE-BV" rename to roe_pate_bv11axes
alter table rht.roe_pate_bv11axes rename column "Id_ROE" to Id_ROE

select count(*) from rht.roe_pate_bv11axes limit 7

Jointure et Ajout des variables du RHT

---Ajout d'une colonne a_conserver pour ne conserver que les obstacles non aqueduc et bien projetés
alter table rht.roe_pate_bv11axes add column a_conserver boolean default TRUE;
update rht.roe_pate_bv11axes set a_conserver=FALSE where id_roe in ('ROE46281','ROE46580','ROE47341','ROE47342',
'ROE47348','ROE47350','ROE47352','ROE47353','ROE47356','ROE47357','ROE47358','ROE47359','ROE47360','ROE47361','ROE47362',
'ROE47363','ROE47374','ROE47384','ROE47409','ROE49696','ROE52263','ROE50534');

--Ajout d'une colonne id_drain
alter table rht.roe_pate_bv11axes add column id_drain integer;
update rht.roe_pate_bv11axes set id_drain=sub.id_drain from 
(select id_drain, id_roe from rht.rhtvs2_roev2) as sub
where sub.id_roe=roe_pate_bv11axes.id_roe;

---Intégration des barrages non disponibles dans la version publique d'avril 2011 - voir mail Laurent
update rht.roe_pate_bv11axes set id_drain='122703' where id_roe='ROE64290';
update rht.roe_pate_bv11axes set id_drain='121476' where id_roe='ROE64330';
update rht.roe_pate_bv11axes set id_drain='100382' where id_roe='ROE65180';
update rht.roe_pate_bv11axes set id_drain='104526' where id_roe='ROE66096';
update rht.roe_pate_bv11axes set id_drain='20379' where id_roe='ROE67681';
---Projection des barrages sur le bon id_drain
update rht.roe_pate_bv11axes set id_drain='122946' where id_roe='ROE47373';
update rht.roe_pate_bv11axes set id_drain='123005' where id_roe='ROE47412';
update rht.roe_pate_bv11axes set id_drain='122667' where id_roe='ROE47382';


drop table if exists rht.rhtvs2_roe_pate_bv11axes;
create table rht.rhtvs2_roe_pate_bv11axes as (
select  b.*, t.dmer, module, module_icinf, module_icsup, minqmx, minqmxicinf, minqmxicsup, cumnbbar, etatnom from rht.roe_pate_bv11axes b     
        left join roe_v2.pre_ice_v2 r on r.id_roe=b.id_roe
        left join rht.rhtvs2 t on t.id_drain=b.id_drain
        left join rht.attributs_rht_fev_2011_vs2 a on b.id_drain=a.id_drain);

select count(*) from rht.rhtvs2_roe_pate_bv11axes ---398
select count(*) from rht.rhtvs2_roe_pate_bv11axes where a_conserver='TRUE' ---376

drop table if exists rht.pate;
alter table rht.rhtvs2_roe_pate_bv11axes rename to rht.pate;
COMMENT ON TABLE rht.pate IS 'Table pate comes from roe_pate_bv11axes.xls'

---Intégration d'une colonne où le cours d'eau est considéré comme non colonisable par l'anguille (voir mail Philippe)
alter table rht.pate add column colonisable boolean default TRUE;
update rht.pate set colonisable=FALSE where id_roe in ('ROE29006','ROE29605','ROE45473','ROE45622','ROE62311','ROE29358','ROE44880',
'ROE45645','ROE29734','ROE4050','ROE4044','ROE4041','ROE45382','ROE45355','ROE45623','ROE45378','ROE47387','ROE46238','ROE46243',
'ROE45353','ROE45363','ROE45364','ROE47382','ROE41308','ROE42196','ROE42378','ROE42399','ROE42408','ROE41113','ROE41045','ROE26787',
'ROE26790','ROE42025','ROE33655','ROE46232','ROE46570','ROE49698','ROE32917','ROE32920','ROE32925','ROE32929','ROE46289','ROE46249',
'ROE46250','ROE46256','ROE47370','ROE47369','ROE46567','ROE33633','ROE46202','ROE46224','ROE62066','ROE46306','ROE47371','ROE47436',
'ROE47449','ROE33702','ROE47476','ROE44024','ROE47461','ROE33689','ROE62068','ROE47365','ROE47372','ROE47378','ROE47339','ROE29769',
'ROE47338','ROE47465','ROE47391','ROE47455','ROE29751','ROE47398','ROE46576','ROE64290','ROE46573','ROE47472','ROE46581','ROE47459',
'ROE46305','ROE47373','ROE33647','ROE47458','ROE47396','ROE46220','ROE46236','ROE46234','ROE47475','ROE47332','ROE47412','ROE44029',
'ROE44035','ROE44041');  ---93 lines

Changement des noms 

alter table rht.pate RENAME COLUMN "Numéro" TO Numero;
alter table rht.pate RENAME COLUMN "BV" TO BV;
alter table rht.pate RENAME COLUMN "Q équipement" TO Q_equipement;
alter table rht.pate RENAME COLUMN "XL93" TO XL93;
alter table rht.pate RENAME COLUMN "YL93" TO YL93;
alter table rht.pate RENAME COLUMN "Nom" TO Nom;
alter table rht.pate RENAME COLUMN "typeNom" TO typeNom;
alter table rht.pate RENAME COLUMN "stypeNom" TO stypeNom;
alter table rht.pate RENAME COLUMN "staNom" TO staNom;
alter table rht.pate RENAME COLUMN "dateModif" TO dateModif;
alter table rht.pate RENAME COLUMN "nomCart" TO nomCart;
alter table rht.pate RENAME COLUMN "nomTopo" TO nomTopo;
alter table rht.pate RENAME COLUMN "commNom" TO commNom;
alter table rht.pate RENAME COLUMN "deptNom" TO deptNom;
alter table rht.pate RENAME COLUMN "CHCart" TO CHCart;
alter table rht.pate RENAME COLUMN "note_fr_An" TO note_fr_An

Problèmes d'intégration de la table via (par access ok): 

DROP TABLE IF EXISTS rht.roe_pate_bv11axes;
CREATE TABLE rht.roe_pate_bv11axes
(
  BV character varying(255),
  Numero double precision,
  Id_ROE character varying(255),
  Q_equipement character varying(255),
  XL93 character varying(255),
  YL93 character varying(255),
  Nom character varying(255),
  typeNom character varying(255),
  stypeNom character varying(255),
  staNom character varying(255),
  dateModif character varying(255),
  source character varying(255),
  nomCart character varying(255),
  nomTopo character varying(255),
  commNom character varying(255),
  deptNom character varying(255),
  bassin character varying(255),
  CHCart character varying(255),
  hauteur_ch character varying(255),
  usage1 character varying(255),
  usage2 character varying(255),
  usage3 character varying(255),
  usage4 character varying(255),
  fpi1 character varying(255),
  fpi2 character varying(255),
  fpi3 character varying(255),
  fpi4 character varying(255),
  fpi5 character varying(255),
  note_fr_An character varying(255),
  ouvrage_gr character varying(255),
  ouv_lies character varying(255)
);

---Celine
copy rht.roe_pate_bv11axes from 'D:/CelineJouanin/EDA20RHT/ROE_PATE_BV11axes.csv' with csv header delimiter as ';' NULL AS 'NA'; -- lines
set client_encoding to 'UTF8';

Coordonnées absentes car les obstacles ne sont pas dans la version publique d'avril 2011 --> récupérer les coordonnées dans la table roe_v2.obstacle

id_roe id_drain
ROE64290 pas dans la version ROE publique d'avril 2011 122703
ROE64330 pas dans la version ROE publique d'avril 2011 121476
ROE65180 pas dans la version ROE publique d'avril 2011 100382
ROE66096 pas dans la version ROE publique d'avril 2011 104526
ROE67681 pas dans la version ROE publique d'avril 2011 20379

#A FINIR !! Un barrage ajouté aux id_drain mais cumnbbar non modifié
id_drain in ('122703','122946','123005','122667')

Un barrage ajouté aux id_drain mais cumnbbar augmenté de 1 pour tous les segments amont
id_drain in ('121476','100382','104526','20379')

Noeudmer pour chaque BV

BVid_drain du noeudmerid_drain à considérer
Bresle300002300002
Vilaine-Oust212340212340
Mayenne214925213048
Hérault2261122611
Charente-Boutonne-Tardoire-Bonnieure100517100517
Arc-Touloubre2194221942
Blavet209549209549
Dronne105505105792 ou 106306
Vire303630303630
AdourSaufPau118671118514
Gave de Pau-Oloron118671118612 
alter table rht.rhtvs2 add column BV_PATE character varying(25);
update rht.rhtvs2 set BV_PATE='Dronne' where id_drain in (select rht.upstream_segments(105505)) or id_drain='105505';  ---16328 lines
update rht.rhtvs2 set BV_PATE='Blavet' where id_drain in (select rht.upstream_segments(209549)) or id_drain='209549'; ---573 lines
update rht.rhtvs2 set BV_PATE='Bresle' where id_drain in (select rht.upstream_segments(300002)) or id_drain='300002'; ---163 lines
update rht.rhtvs2 set BV_PATE='Vire' where id_drain in (select rht.upstream_segments(303630)) or id_drain='303630'; ---415 lines
update rht.rhtvs2 set BV_PATE='ArcTouloubre' where id_drain in (select rht.upstream_segments(21942)) or id_drain='21942'; ---333 lines
update rht.rhtvs2 set BV_PATE='CBTB' where id_drain in (select rht.upstream_segments(100517)) or id_drain='100517'; ---2006 lines
update rht.rhtvs2 set BV_PATE='Herault' where id_drain in (select rht.upstream_segments(22611)) or id_drain='22611'; ---603 lines
update rht.rhtvs2 set BV_PATE='VilaineOust' where id_drain in (select rht.upstream_segments(212340)) or id_drain='212340'; ---2360 lines
update rht.rhtvs2 set BV_PATE='Mayenne' where id_drain in (select rht.upstream_segments(214925)) or id_drain='214925'; ---25683 lines
update rht.rhtvs2 set BV_PATE='GavedePauOloron' where id_drain in (select rht.upstream_segments(118671)) or id_drain='118671'; --- lines

drop table if exists rht.pate_Bresle;
create table rht.pate_Bresle as (
select * from rht.rhtvs2 where id_drain in (select rht.upstream_segments(300002)) or id_drain='300002');
CREATE INDEX indexBresle
ON rht.pate_Bresle
USING btree (id_drain);

drop table if exists rht.pate_Mayenne;
create table rht.pate_Mayenne as (
select * from rht.rhtvs2 where id_drain in (select rht.upstream_segments(213048)) or id_drain='213048');
CREATE INDEX indexMayenne
ON rht.pate_Mayenne
USING btree (id_drain);

drop table if exists rht.pate_VilaineOust;
create table rht.pate_VilaineOust as (
select * from rht.rhtvs2 where id_drain in (select rht.upstream_segments(212340)) or id_drain='212340');
CREATE INDEX indexVilaineOust
ON rht.pate_VilaineOust
USING btree (id_drain);


drop table if exists rht.pate_Herault;
create table rht.pate_Herault as (
select * from rht.rhtvs2 where id_drain in (select rht.upstream_segments(22611)) or id_drain='22611');
CREATE INDEX indexHerault
ON rht.pate_Herault
USING btree (id_drain);

drop table if exists rht.pate_CBTB;
create table rht.pate_CBTB as (
select * from rht.rhtvs2 where id_drain in (select rht.upstream_segments(100517)) or id_drain='100517');
CREATE INDEX indexCBTB
ON rht.pate_CBTB
USING btree (id_drain);

drop table if exists rht.pate_ArcTouloubre;
create table rht.pate_ArcTouloubre as (
select * from rht.rhtvs2 where id_drain in (select rht.upstream_segments(21942)) or id_drain='21942');
CREATE INDEX indexArcTouloubre
ON rht.pate_ArcTouloubre
USING btree (id_drain);

drop table if exists rht.pate_Blavet;
create table rht.pate_Blavet as (
select * from rht.rhtvs2 where id_drain in (select rht.upstream_segments(209549)) or id_drain='209549');
CREATE INDEX indexBlavet
ON rht.pate_Blavet
USING btree (id_drain);

drop table if exists rht.pate_Dronne;
create table rht.pate_Dronne as (
select * from rht.rhtvs2 where id_drain in (select rht.upstream_segments(105792)) or id_drain='105792');
CREATE INDEX indexDronne
ON rht.pate_Dronne
USING btree (id_drain);

drop table if exists rht.pate_Vire;
create table rht.pate_Vire as (
select * from rht.rhtvs2 where id_drain in (select rht.upstream_segments(303630)) or id_drain='303630');
CREATE INDEX indexVire
ON rht.pate_Vire
USING btree (id_drain);

drop table if exists rht.pate_GavedePauOloron;
create table rht.pate_GavedePauOloron as (
select * from rht.rhtvs2 where id_drain in (select rht.upstream_segments(118612)) or id_drain='118612');
CREATE INDEX indexGavedePauOloron
ON rht.pate_GavedePauOloron
USING btree (id_drain);

drop table if exists rht.pate_AdourSaufPau;
create table rht.pate_AdourSaufPau as (
select * from rht.rhtvs2 where id_drain in (select rht.upstream_segments(118514)) or id_drain='118514');
CREATE INDEX indexAdourSaufPau
ON rht.pate_AdourSaufPau
USING btree (id_drain);

ROE_v3.obstacle

Integration du ROE version 13/02/2012 

d:
cd D:\CelineJouanin\ROE2012
C:\"Program Files"\PostgreSQL\8.4\bin\psql  -U postgres --dbname "eda2.0_RHT" -f "roe.sql"

create schema roe_v3;
COMMENT ON SCHEMA roe_v3 IS 'Table obstacle transmise par Laurent du 13 février 2012';

alter table obstacle set schema roe_v3
COMMENT ON TABLE roe_v3.obstacle IS 'Table transmise par Laurent du 13 février 2012';

UPDATE roe_v3.obstacle SET the_geom = ST_transform(the_geom, 3035); 
ALTER TABLE roe_v3.obstacle ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);

alter table roe_v3.obstacle RENAME COLUMN "Id_ROE" TO Id_ROE;
alter table roe_v3.obstacle RENAME COLUMN  "Nom" TO Nom;
alter table roe_v3.obstacle RENAME COLUMN "typeNom" TO typeNom;
alter table roe_v3.obstacle RENAME COLUMN "stypeNom" TO stypeNom;
alter table roe_v3.obstacle RENAME COLUMN "staNom" TO staNom;
alter table roe_v3.obstacle RENAME COLUMN "etatNom" TO etatNom;
alter table roe_v3.obstacle RENAME COLUMN "IdTrCart" TO IdTrCart;
alter table roe_v3.obstacle RENAME COLUMN "nomCart" TO nomCart;
alter table roe_v3.obstacle RENAME COLUMN "CHCart" TO CHCart;
alter table roe_v3.obstacle RENAME COLUMN "CGCart" TO CGCart;
alter table roe_v3.obstacle RENAME COLUMN "ZHCart" TO ZHCart;
alter table roe_v3.obstacle RENAME COLUMN "nomTopo" TO nomTopo;
alter table roe_v3.obstacle RENAME COLUMN "commNom" TO commNom;
alter table roe_v3.obstacle RENAME COLUMN "deptCd" TO deptCd;

PATE

Nouvelle version table PATE Copie de ROE_PATE_dmerCorrigé_20120213_LB 

---Integration via Access puis
alter table "ROE_PATE" set schema rht;
alter table rht.pate rename to pate1;
alter table rht."ROE_PATE" rename to pate;

---Changement des noms
alter table rht.pate RENAME COLUMN "Num" TO Num;
alter table rht.pate RENAME COLUMN "BV" TO BV;
alter table rht.pate RENAME COLUMN "nomCart" TO nomCart;
alter table rht.pate RENAME COLUMN "nomTopo" TO nomTopo;
alter table rht.pate RENAME COLUMN "commNom" TO commNom;
alter table rht.pate RENAME COLUMN "Nom" TO Nom;
alter table rht.pate RENAME COLUMN "dmer (km)" TO dmer
alter table rht.pate RENAME COLUMN "Q réservé (m3/s)" TO Q_reserve
alter table rht.pate RENAME COLUMN "Q équipement(m3/s)" TO Q_equipement
alter table rht.pate RENAME COLUMN "Surf_BV (km2)" TO Surf_BV
alter table rht.pate RENAME COLUMN "Numéro" TO Numero;
alter table rht.pate RENAME COLUMN "XL93" TO XL93;
alter table rht.pate RENAME COLUMN "YL93" TO YL93;
alter table rht.pate RENAME COLUMN "typeNom" TO typeNom;
alter table rht.pate RENAME COLUMN "stypeNom" TO stypeNom;
alter table rht.pate RENAME COLUMN "staNom" TO staNom;
alter table rht.pate RENAME COLUMN "deptNom" TO deptNom;
alter table rht.pate RENAME COLUMN "CHCart" TO CHCart;
alter table rht.pate RENAME COLUMN "note_fr_An" TO note_fr_An

COMMENT ON COLUMN rht.pate.Q_reserve IS 'm3/s';
COMMENT ON COLUMN rht.pate.Q_equipement IS 'm3/s';
COMMENT ON COLUMN rht.pate.Surf_BV IS 'km²';

COMMENT ON COLUMN rht.pate.dmer IS 'en km, dmer provient des calculs sur le RHT';
COMMENT ON COLUMN rht.pate.module IS 'm3/s, provient du RHT';
COMMENT ON COLUMN rht.pate.module_icsup IS 'm3/s, provient du RHT';
COMMENT ON COLUMN rht.pate.module_icinf IS 'm3/s, provient du RHT';
COMMENT ON COLUMN rht.pate.minqmx IS 'm3/s, provient du RHT';
COMMENT ON COLUMN rht.pate.minqmxicinf IS 'm3/s, provient du RHT';
COMMENT ON COLUMN rht.pate.minqmxicsup IS 'm3/s, provient du RHT';

---Ajout d'une colonne a_conserver pour ne conserver que les obstacles non aqueduc et bien projetés
alter table rht.roe_pate_bv11axes add column a_conserver boolean default TRUE;
update rht.roe_pate_bv11axes set a_conserver=FALSE where id_roe in ('ROE46281','ROE46580','ROE47341','ROE47342',
'ROE47348','ROE47350','ROE47352','ROE47353','ROE47356','ROE47357','ROE47358','ROE47359','ROE47360','ROE47361','ROE47362',
'ROE47363','ROE47374','ROE47384','ROE47409','ROE49696','ROE52263','ROE50534');

---Intégration d'une colonne où le cours d'eau est considéré comme non colonisable par l'anguille (voir mail Philippe - en rouge)
alter table rht.pate add column colonisable boolean default TRUE;
update rht.pate set colonisable=FALSE where id_roe in ('ROE29006','ROE29605','ROE45473','ROE45622','ROE62311','ROE29358','ROE44880',
'ROE45645','ROE29734','ROE4050','ROE4044','ROE4041','ROE45382','ROE45355','ROE45623','ROE45378','ROE47387','ROE46238','ROE46243',
'ROE45353','ROE45363','ROE45364','ROE47382','ROE41308','ROE42196','ROE42378','ROE42399','ROE42408','ROE41113','ROE41045','ROE26787',
'ROE26790','ROE42025','ROE33655','ROE46232','ROE46570','ROE49698','ROE32917','ROE32920','ROE32925','ROE32929','ROE46289','ROE46249',
'ROE46250','ROE46256','ROE47370','ROE47369','ROE46567','ROE33633','ROE46202','ROE46224','ROE62066','ROE46306','ROE47371','ROE47436',
'ROE47449','ROE33702','ROE47476','ROE44024','ROE47461','ROE33689','ROE62068','ROE47365','ROE47372','ROE47378','ROE47339','ROE29769',
'ROE47338','ROE47465','ROE47391','ROE47455','ROE29751','ROE47398','ROE46576','ROE64290','ROE46573','ROE47472','ROE46581','ROE47459',
'ROE46305','ROE47373','ROE33647','ROE47458','ROE47396','ROE46220','ROE46236','ROE46234','ROE47475','ROE47332','ROE47412','ROE44029',
'ROE44035','ROE44041');  ---93 lines

update rht.pate set colonisable=TRUE where id_roe='ROE42025';
update rht.pate set colonisable=FALSE where id_roe in ('ROE46242','ROE47366','ROE47367','ROE46582','ROE47463','ROE47403','ROE47462','ROE47452','ROE47460');


---Intégration des barrages non disponibles dans la version publique d'avril 2011 - voir mail Laurent
Les obstacles ont bien été intégrés avec id_drain correspondant
select id_roe, id_drain from rht.pate where id_roe in ('ROE64290','ROE64330','ROE65180','ROE66096','ROE67681','ROE47373','ROE47412','ROE47382');


---Mise à jour de la distance mer
update rht.pate set dmer=sub.dmer from 
(select id_drain, dmer from rht.rhtvs2) as sub
where sub.id_drain=pate.id_drain and pate.dmer is null;

---Ajout de la surface du BV du RHT (pout comparaison avec la "surf_bv")
alter table rht.pate add column surf_bv_rht numeric;
update rht.pate set surf_bv_rht=sub.surf_bv from 
(select id_drain, surf_bv from rht.attributs_rht_fev_2011_vs2) as sub
where sub.id_drain=pate.id_drain;

---Intégration de la géométrie (à partir de the_geom de la table obstacle)
SELECT AddGeometryColumn ( 'rht','pate','the_geom',3035,'POINT',2);
update rht.pate set the_geom=obstacle.the_geom from roe_v3.obstacle
 where obstacle.id_roe=pate.id_roe; 

--- Ajout de l'identifiant bdcarthage (à partir de la table roe_v3.obstacle)
alter table rht.pate add column idtrcart integer;
update rht.pate set idtrcart=obstacle.idtrcart from roe_v3.obstacle where obstacle.id_roe=pate.id_roe;

Projection id_roe sur RHT à vérifier

id_roe in ('ROE46202','ROE42025','ROE45125','ROE6785','ROE20586','ROE43996')
select surf_bv, surf_bv_rht, id_drain, surf_bv-surf_bv_rht as diff from rht.pate where surf_bv is not null order by diff

A modifier dans rht.rht_roev2 --> calcul cumnbbar à revoir 

id_roe in ('ROE42025','ROE20586','ROE46202','ROE45125','ROE6785','ROE43996')
update rht.rhtvs2_roev2 set id_drain=103936 where id_roe='ROE42025';
update rht.rhtvs2_roev2 set id_drain=207306 where id_roe='ROE20586';
update rht.rhtvs2_roev2 set id_drain=122291 where id_roe='ROE46202';
update rht.rhtvs2_roev2 set id_drain=120931 where id_roe='ROE45125';
update rht.rhtvs2_roev2 set id_drain=121592 where id_roe='ROE6785';
update rht.rhtvs2_roev2 set id_drain=121530 where id_roe='ROE43996';
update rht.rhtvs2_roev2 set id_drain=20602 where id_roe='ROE42134';
update rht.rhtvs2_roev2 set id_drain=306907 where id_roe='ROE7104';
update rht.rhtvs2_roev2 set id_drain=201665 where id_roe='ROE11608';

update rht.rhtvs2_roev2 set nbdams=1;
---Clause group by par id_drain
drop table if exists rht.rhtvs2_roev2_nbdams;
create table rht.rhtvs2_roev2_nbdams as
select id_drain, count(nbdams) as nbdams from rht.rhtvs2_roev2 as r group by id_drain;
alter table rht.rhtvs2_roev2_nbdams add column height integer;
alter table rht.rhtvs2_roev2_nbdams add column score integer;
update rht.rhtvs2_roev2_nbdams set height=1;
update rht.rhtvs2_roev2_nbdams set score=1;

alter table rht.rhtvs2 rename column cumnbbar to cumnbbar1;  ---pour vérifier que je me trompe pas
alter table rht.rhtvs2 add column cumnbbar integer;
-- mise à jour des données dans rhtvs2
update rht.rhtvs2 set (dmer, cumnbbar)=(sub.dmer, sub.cumnbbar)
from (select id_drain,dmer,cumnbbar from rht.noeudmervs2 where dmer is not null and noeudmer) sub
where sub.id_drain=rhtvs2.id_drain;  ---100 lignes

-- calcul du nb de barrages cumulé sous R (main_RHT_France ligne 68) et réimport 
drop table if exists rht.cumnbbarvs2;
create table rht.cumnbbarvs2(
        id_drain integer primary key,
        cumnbbar integer);
---Cédric
copy rht.cumnbbarvs2 from 'E:/workspace/EDAdata/dataEDArht/cumnbbar.csv' with csv header delimiter as ';' NULL AS 'NA';--114600
---Céline
copy rht.cumnbbarvs2 from 'D:/CelineJouanin/workspace/EDAData/dataEDArht/cumnbbar.csv' with csv header delimiter as ';' NULL AS 'NA'; ---114564

-- mise à jour des cumuls barrage  
update rht.rhtvs2 t set cumnbbar=sub.cumnbbar from (
select id_drain, cumnbbar from rht.cumnbbarvs2) as sub
where t.id_drain=sub.id_drain

A modifier dans rht.pate --> redonner le bon module 

id_roe in ('ROE46202','ROE45125','ROE6785','ROE43996')
update rht.pate set id_drain=122291 where id_roe='ROE46202';
update rht.pate set id_drain=120931 where id_roe='ROE45125';
update rht.pate set id_drain=121592 where id_roe='ROE6785';
update rht.pate set id_drain=121530 where id_roe='ROE43996';
update rht.pate set id_drain=20602 where id_roe='ROE42134';
update rht.pate set id_drain=306907 where id_roe='ROE7104';
update rht.pate set id_drain=201665 where id_roe='ROE11608';
---Mise à jour de la distance mer
 update rht.pate set dmer=sub.dmer from 
(select id_drain, dmer from rht.rhtvs2) as sub
where sub.id_drain=pate.id_drain and id_roe in ('ROE46202','ROE45125','ROE6785','ROE43996','ROE42134','ROE7104','ROE11608');
---Mise à jour de la surface du BV du RHT (pout comparaison avec la "surf_bv")
update rht.pate set surf_bv_rht=sub.surf_bv from 
(select id_drain, surf_bv from rht.attributs_rht_fev_2011_vs2) as sub
where sub.id_drain=pate.id_drain and id_roe in ('ROE46202','ROE45125','ROE6785','ROE43996','ROE42134','ROE7104','ROE11608');
---Mise à jour du module
update rht.pate set module=sub.module from 
(select id_drain, module from rht.attributs_rht_fev_2011_vs2) as sub
where sub.id_drain=pate.id_drain and id_roe in ('ROE46202','ROE45125','ROE6785','ROE43996','ROE42134','ROE7104','ROE11608');
---Mise à jour du minqmx
update rht.pate set minqmx=sub.minqmx from 
(select id_drain, minqmx from rht.attributs_rht_fev_2011_vs2) as sub
where sub.id_drain=pate.id_drain and id_roe in ('ROE46202','ROE45125','ROE6785','ROE43996','ROE42134','ROE7104','ROE11608');

Export table 

d:
cd d:\CelineJouanin\export_table\version2
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.pate eda2.0_RHT> pate.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.cumnbbarvs2 eda2.0_RHT> cumnbbarvs2.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.rhtvs2_roev2_nbdams eda2.0_RHT> rhtvs2_roev2_nbdams.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.rhtvs2_roev2 eda2.0_RHT> rhtvs2_roev2.sql