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'

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

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

BV	id_drain du noeudmer
Bresle	300002
Gave de Pau-Oloron	118671
Vilaine-Oust	212340
Mayenne	214925
AdourSaufPau	118671
Hérault	22611
Charente-Boutonne-Tardoire-Bonnieure	100517
Arc-Touloubre	21942
Blavet	209549
Dronne	105505
Vire	303630