PATE Anguille – EDA

Context Navigation

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

"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

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

alter table rht.rhtvs2 drop column BV_PATE;
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(105792)) or id_drain='105792';  ---595 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(213048)) or id_drain='213048'; ---1309 lines
update rht.rhtvs2 set BV_PATE='Adour' where id_drain in (select rht.upstream_segments(118671)) or id_drain='118671'; ---3180 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; 

--- Ajout d'une colonne mortalité
alter table rht.pate add column mortalite numeric;

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 lines

-- calcul du nb de barrages cumulé sous R (main_RHT_France line 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');

Comparaison module RHT - Banque Hydro

Les lames d'eau mensuelles (débits produits par unité de surface) sont modélisées par zone hydrographique à l'échelle du territoire français (Sauquet, 2006). Les données de lame d'eau sont des extrapolation spatiales d'observations issues de la Banque Hydro et correspondant à des enregistrements continus réalisés sur une période de 20 ans (1981-2000) et à partir de 965 stations de jaugeage.

Sauquet E., 2006. Mapping mean annual river discharges: geostatistical developments for incorporating river network dependencies. Journal of Hydrology 331, 300-314.

Dans les références données dans le Guide Estimkart, j’ai trouvé : Sauquet E., Krasovskaïa I. & Leblois E., 2000b. Mapping mean monthly runoff pattern using EOF analysis, Hydrology and Earth System Sciences 4, 79-93.

Sauquet E., 2005. Cartographie des écoulements annuels moyens en France, rapport, Cemagref et ministère de l’Ecologie et du Développement Durable, Lyon, 40 p. The French hydrological database HYDRO offers time series of observed and reconstructed natural monthly discharges. To ensure temporal consistency and to provide reliable runoff estimates, a common observation period of continuous records (1981–2000) was used. In France, water year starts the 01-September and ends the 31-August of the following year.

Sauquet et al., 2000a, 2006b Sauquet E., Gottschalk L., Leblois E., 2000a. Mapping average annual runoff: A hierarchical approach applying a stochastic interpolation scheme, Hydrological Sciences Journal, 45(6), 799-816. To ensure temporal consistency and to provide reliable runoff estimates, a common observation period of continuous records (1981–2000) was used. In France, water year starts the 01-September and ends the 31-August of the following year.

Suite au mail de Peggy :

Vérification des projections des ouvrages dans "
update rht.pate set id_drain=201665 where id_roe='ROE11608';

Table "tab complété pour Qe et mort.xls" mail Peggy 1/03/2012 Puis suppression de certains ouvrages où abscence de colonisation par les anguilles d'où le fichier à utiliser : Table "mortalite_11BV_versionFinal2012.03.08"

DROP TABLE IF EXISTS rht.pate_mortalite;
create table rht.pate_mortalite
(
  num double precision,
  bv character varying(80),
  nomcart character varying(80),
  nomtopo character varying(80),
  commnom character varying(80),
  id_roe character varying(80),
  nom character varying(80),
  ouvrage character varying(80),
  dmer character varying(80),
  H character varying(80),
  q_equipement_renseigne character varying(80),
  module character varying(80),
  qe_qm character varying(80),
  selection numeric,
  extrapolation character varying(80),
  q_equipement numeric,
  mortalite50cm numeric,
  mortalite70cm numeric,
  mortalite90cm numeric
);

set client_encoding to 'latin1';
copy rht.pate_mortalite from 'D:/CelineJouanin/PATE/pate_mortalite_final.csv' with csv header delimiter as ';' NULL AS 'NA'; -- lines

DROP TABLE IF EXISTS rht.pate_mortalite;
create table rht.pate_mortalite
(
  num double precision,
  bv character varying(80),
  nomcart character varying(80),
  nomtopo character varying(80),
  commnom character varying(80),
  id_roe character varying(80),
  nom character varying(80),
  ouvrage character varying(80),
  dmer character varying(80),
  H character varying(80),
  q_equipement_renseigne character varying(80),
  module character varying(80),
  qe_qm character varying(80),
  selection numeric,
  extrapolation character varying(80),
  q_equipement numeric,
  mortalite50cm numeric,
  mortalite70cm numeric,
  mortalite90cm numeric
);

set client_encoding to 'latin1';
copy rht.pate_mortalite from 'D:/CelineJouanin/PATE/pate_mortalite.csv' with csv header delimiter as ';' NULL AS 'NA'; -- lines

Table "ATT496756.xls"

DROP TABLE IF EXISTS rht.pate_debit_classe;
some problem to import the table with "copy from", so I have added it in the schema rht via Access -ODBC

alter table "Pate_debit_classe" set schema rht;
alter table rht."Pate_debit_classe" rename to pate_debit_classe;
alter table rht.pate_debit_classe rename column "Numéro" to numero;
alter table rht.pate_debit_classe rename column "XLambertIIetendu" to XLambertIIetendu;
alter table rht.pate_debit_classe rename column "YLambertIIetendu" to YLambertIIetendu;
alter table rht.pate_debit_classe rename column "SurfBV" to SurfBV;
alter table rht.pate_debit_classe rename column "Module" to Module_P;
alter table rht.pate_debit_classe rename column "Q75_n" to Q75_n;
alter table rht.pate_debit_classe rename column "Q90_n" to Q90_n;
alter table rht.pate_debit_classe rename column "Q95_n" to Q95_n;
alter table rht.pate_debit_classe rename column "Q975_n" to Q975_n;
alter table rht.pate_debit_classe rename column "Q99_n" to Q99_n;
alter table rht.pate_debit_classe rename column "annee_min_BH" to annee_min_BH;
alter table rht.pate_debit_classe rename column "annee_max_BH" to annee_max_BH;
alter table rht.pate_debit_classe rename column "Module_BH" to Module_BH;
alter table rht.pate_debit_classe rename column "Q90_BH" to Q90_BH;
alter table rht.pate_debit_classe rename column "Q95_BH" to Q95_BH;
alter table rht.pate_debit_classe rename column "Q98_BH" to Q98_BH;
alter table rht.pate_debit_classe rename column "Q99_BH" to Q99_BH;
alter table rht.pate_debit_classe rename column "Q90_n_BH" to Q90_n_BH;
alter table rht.pate_debit_classe rename column "Q95_n_BH" to Q95_n_BH;
alter table rht.pate_debit_classe rename column "Q98_n_BH" to Q98_n_BH;
alter table rht.pate_debit_classe rename column "Q99_n_BH" to Q99_n_BH;

---Calculating Q (débit classé) with annual module from rht 
alter table rht.pate_debit_classe add column module_rht numeric;
alter table rht.pate_debit_classe add column Q75_rht numeric;
alter table rht.pate_debit_classe add column Q90_rht numeric;
alter table rht.pate_debit_classe add column Q95_rht numeric;
alter table rht.pate_debit_classe add column Q975_rht numeric;
alter table rht.pate_debit_classe add column Q99_rht numeric;

update rht.pate_debit_classe set module_rht=sub.module from (select id_drain, module from rht.attributs_rht_fev_2011_vs2) as sub where pate_debit_classe.id_drain=sub.id_drain;
update rht.pate_debit_classe set Q75_rht=(Q75_n*Module_P)/module_rht;
update rht.pate_debit_classe set Q90_rht=(Q90_n*Module_P)/module_rht;
update rht.pate_debit_classe set Q95_rht=(Q95_n*Module_P)/module_rht;
update rht.pate_debit_classe set Q975_rht=(Q975_n*Module_P)/module_rht;
update rht.pate_debit_classe set Q99_rht=(Q99_n*Module_P)/module_rht;


---Calculating Q (débit classé) with module from rht from october to april
alter table rht.pate_debit_classe add column module_rht_oct_avril numeric;
alter table rht.pate_debit_classe add column Q75_rht_oct_avril numeric;
alter table rht.pate_debit_classe add column Q90_rht_oct_avril numeric;
alter table rht.pate_debit_classe add column Q95_rht_oct_avril numeric;
alter table rht.pate_debit_classe add column Q975_rht_oct_avril numeric;
alter table rht.pate_debit_classe add column Q99_rht_oct_avril numeric;

update rht.pate_debit_classe set module_rht_oct_avril=sub.module_oct_avril from (select id_drain, module_oct_avril from rht.attr_debit) as sub where pate_debit_classe.id_drain=sub.id_drain;
update rht.pate_debit_classe set Q75_rht_oct_avril=(Q75_n*Module_P)/module_rht_oct_avril;
update rht.pate_debit_classe set Q90_rht_oct_avril=(Q90_n*Module_P)/module_rht_oct_avril;
update rht.pate_debit_classe set Q95_rht_oct_avril=(Q95_n*Module_P)/module_rht_oct_avril;
update rht.pate_debit_classe set Q975_rht_oct_avril=(Q975_n*Module_P)/module_rht_oct_avril;
update rht.pate_debit_classe set Q99_rht_oct_avril=(Q99_n*Module_P)/module_rht_oct_avril;

update rht.pate_debit_classe set bv ='Bresle' where numero=1;
update rht.pate_debit_classe set bv ='Vire' where numero=2;
update rht.pate_debit_classe set bv ='Vilaine-Oust' where numero=3;
update rht.pate_debit_classe set bv ='Vilaine-Oust' where numero=4;
update rht.pate_debit_classe set bv ='Blavet' where numero=5;
update rht.pate_debit_classe set bv ='Charente-Boutonne-Tardoire-Bonnieure' where numero=6;
update rht.pate_debit_classe set bv ='Charente-Boutonne-Tardoire-Bonnieure' where numero=7;
update rht.pate_debit_classe set bv ='Dronne' where numero=8;
update rht.pate_debit_classe set bv ='Adour' where numero=9;
update rht.pate_debit_classe set bv ='Herault' where numero=10;
update rht.pate_debit_classe set bv ='Arc-Touloubre' where numero=11;
update rht.pate_debit_classe set bv ='Arc-Touloubre' where numero=12;
update rht.pate_debit_classe set bv ='Adour' where numero=13;
update rht.pate_debit_classe set bv ='Adour' where numero=14;
update rht.pate_debit_classe set bv ='Adour' where numero=15;
update rht.pate_debit_classe set bv ='Adour' where numero=16;
update rht.pate_debit_classe set bv ='Adour' where numero=17;
update rht.pate_debit_classe set bv ='Adour' where numero=18;
update rht.pate_debit_classe set bv ='Mayenne' where numero=19;
update rht.pate_debit_classe set bv ='Mayenne' where numero=20;

alter table rht.pate_debit_classe add column a_conserver boolean default FALSE;
update rht.pate_debit_classe set a_conserver ='true' where code_station='G0402020';
update rht.pate_debit_classe set a_conserver ='true' where code_station='I5221010';
update rht.pate_debit_classe set a_conserver ='true' where code_station='J9300610';
update rht.pate_debit_classe set a_conserver ='true' where code_station='J5712130';
update rht.pate_debit_classe set a_conserver ='true' where code_station='R2240010';
update rht.pate_debit_classe set a_conserver ='true' where code_station='P8462510';
update rht.pate_debit_classe set a_conserver ='true' where code_station='Q3120010';
update rht.pate_debit_classe set a_conserver ='true' where code_station='Y2142010';
update rht.pate_debit_classe set a_conserver ='true' where code_station='Y4214040';
update rht.pate_debit_classe set a_conserver ='true' where code_station='M3630910';

alter table rht.pate_mortalite add column id_drain integer;
update rht.pate_mortalite set id_drain=sub.id_drain from (select id_drain, id_roe from rht.pate) as sub where pate_mortalite.id_roe=sub.id_roe;
update rht.pate_mortalite set id_drain='116909' where id_roe='ROE4907';
update rht.pate_mortalite set id_drain='121003' where id_roe='ROE22958';
insert into rht.pate(id_roe) values ('ROE4907'),('ROE22958');

update rht.pate_mortalite set dmer=sub.dmer/1000 from (select id_drain, dmer from rht.rhtvs2) as sub where pate_mortalite.id_drain=sub.id_drain;
update rht.pate_mortalite set module=sub.module from (select id_drain, module from rht.attributs_rht_fev_2011_vs2) as sub where pate_mortalite.id_drain=sub.id_drain;

alter table rht.pate_mortalite add column mortalite numeric;
alter table rht.pate_mortalite add column a_conserver boolean default TRUE;

Ajout géométrie

SELECT AddGeometryColumn ( 'rht','pate_mortalite','the_geom',3035,'POINT',2);
update rht.pate_mortalite set the_geom=obstacle.the_geom from roe_v3.obstacle
 where obstacle.id_roe=pate_mortalite.id_roe;

Zone non colonisable et ouvrages non hydroélectrique

alter table rht.pate_mortalite add column colonisable boolean default TRUE;
update rht.pate_mortalite set colonisable=pate.colonisable from rht.pate where pate_mortalite.id_roe=pate.id_roe;
update rht.pate_mortalite set colonisable=true where id_roe='ROE29006'


alter table rht.pate_mortalite add column pasusagehydroelectrique boolean default false;
update rht.pate_mortalite set pasusagehydroelectrique='true' where id_roe in ('ROE53383','ROE67681','ROE40720','ROE40770','ROE41094','ROE41266','ROE41396','ROE42085','ROE41308','ROE30483','ROE33992','ROE45655','ROE11436','ROE8994','ROE34342','ROE50550','ROE42219','ROE43146','ROE43235','ROE43248','ROE43275','ROE50540','ROE50562','ROE52953','ROE50596','ROE52181','ROE52185','ROE52186','ROE51911','ROE51845','ROE11245','ROE51405','ROE13462','ROE65180','ROE50424','ROE50398','ROE50403','ROE30840');  --0
alter table rht.pate_france add column pasusagehydroelectrique boolean default false;
update rht.pate_france set pasusagehydroelectrique='true' where id_roe in ('ROE53383','ROE67681','ROE40720','ROE40770','ROE41094','ROE41266','ROE41396','ROE42085','ROE41308','ROE30483','ROE33992','ROE45655','ROE11436','ROE8994','ROE34342','ROE50550','ROE42219','ROE43146','ROE43235','ROE43248','ROE43275','ROE50540','ROE50562','ROE52953','ROE50596','ROE52181','ROE52185','ROE52186','ROE51911','ROE51845','ROE11245','ROE51405','ROE13462','ROE65180','ROE50424','ROE50398','ROE50403','ROE30840'); ---37
alter table rht.pate add column pasusagehydroelectrique boolean default false;
update rht.pate set pasusagehydroelectrique='true' where id_roe in ('ROE53383','ROE67681','ROE40720','ROE40770','ROE41094','ROE41266','ROE41396','ROE42085','ROE41308','ROE30483','ROE33992','ROE45655','ROE11436','ROE8994','ROE34342','ROE50550','ROE42219','ROE43146','ROE43235','ROE43248','ROE43275','ROE50540','ROE50562','ROE52953','ROE50596','ROE52181','ROE52185','ROE52186','ROE51911','ROE51845','ROE11245','ROE51405','ROE13462','ROE65180','ROE50424','ROE50398','ROE50403','ROE30840');  ---38

Export table

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

C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.pate_debit_classe eda2.0_RHT> pate_debit_classe.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.pate_mortalite eda2.0_RHT> pate_mortalite.sql
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
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.rhtvs2 eda2.0_RHT> rhtvs2.sql

Export shape avec exportshape dans baseEdaRHTpate

C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "D:\CelineJouanin\exports_shape\pate_france.shp" -p 5432 -u postgres -P noisette16! -g the_geom -r -k eda2.0_RHT rht.pate_france

C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "D:\CelineJouanin\exports_shape\pate.shp" -p 5432 -u postgres -P noisette16! -g the_geom -r -k eda2.0_RHT rht.pate_mortalite
C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "D:/CelineJouanin/workspace/EDAData/dataEDAccm/shape/Ado" -h localhost -p 5432 -u postgres -g the_geom -r -k eda2.0_RHT "select id_drain,the_geom from rht.rhtvs2 where id_drain in (select rht.upstream_segments(118671)) or id_drain=118671 ;"
C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "D:/CelineJouanin/workspace/EDAData/dataEDAccm/shape/Ado" -h localhost -p 5432 -u postgres -g the_geom -r -k eda2.0_RHT "select id_drain,the_geom from rht.rhtvs2 where id_drain in (select rht.upstream_segments(118671)) or id_drain=118671 ;"
C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "D:/CelineJouanin/workspace/EDAData/dataEDAccm/shape/May" -h localhost -p 5432 -u postgres -g the_geom -r -k eda2.0_RHT "select id_drain,the_geom from rht.rhtvs2 where id_drain in (select rht.upstream_segments(213048)) or id_drain=213048 ;"
C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "D:/CelineJouanin/workspace/EDAData/dataEDAccm/shape/Arc" -h localhost -p 5432 -u postgres -g the_geom -r -k eda2.0_RHT "select id_drain,the_geom from rht.rhtvs2 where id_drain in (select rht.upstream_segments(21942)) or id_drain=21942 ;"
C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "D:/CelineJouanin/workspace/EDAData/dataEDAccm/shape/Vir" -h localhost -p 5432 -u postgres -g the_geom -r -k eda2.0_RHT "select id_drain,the_geom from rht.rhtvs2 where id_drain in (select rht.upstream_segments(303630)) or id_drain=303630 ;"
C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "D:/CelineJouanin/workspace/EDAData/dataEDAccm/shape/Bla" -h localhost -p 5432 -u postgres -g the_geom -r -k eda2.0_RHT "select id_drain,the_geom from rht.rhtvs2 where id_drain in (select rht.upstream_segments(209549)) or id_drain=209549 ;"
C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "D:/CelineJouanin/workspace/EDAData/dataEDAccm/shape/Cha" -h localhost -p 5432 -u postgres -g the_geom -r -k eda2.0_RHT "select id_drain,the_geom from rht.rhtvs2 where id_drain in (select rht.upstream_segments(100517)) or id_drain=100517 ;"
C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "D:/CelineJouanin/workspace/EDAData/dataEDAccm/shape/Vil" -h localhost -p 5432 -u postgres -g the_geom -r -k eda2.0_RHT "select id_drain,the_geom from rht.rhtvs2 where id_drain in (select rht.upstream_segments(212340)) or id_drain=212340 ;"
C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "D:/CelineJouanin/workspace/EDAData/dataEDAccm/shape/Dro" -h localhost -p 5432 -u postgres -g the_geom -r -k eda2.0_RHT "select id_drain,the_geom from rht.rhtvs2 where id_drain in (select rht.upstream_segments(105792)) or id_drain=105792 ;"
C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "D:/CelineJouanin/workspace/EDAData/dataEDAccm/shape/Bre" -h localhost -p 5432 -u postgres -g the_geom -r -k eda2.0_RHT "select id_drain,the_geom from rht.rhtvs2 where id_drain in (select rht.upstream_segments(300002)) or id_drain=300002 ;"
C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "D:/CelineJouanin/workspace/EDAData/dataEDAccm/shape/Her" -h localhost -p 5432 -u postgres -g the_geom -r -k eda2.0_RHT "select id_drain,the_geom from rht.rhtvs2 where id_drain in (select rht.upstream_segments(22611)) or id_drain=22611 ;"

C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "D:/CelineJouanin/workspace/EDAData/dataEDAccm/shape/Gav" -h localhost -p 5432 -u postgres -g the_geom -r -k eda2.0_RHT "select id_drain,the_geom from rht.rhtvs2 where id_drain in (select rht.upstream_segments(118663)) or id_drain=118663 ;"
C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "D:/CelineJouanin/workspace/EDAData/dataEDAccm/shape/Niv" -h localhost -p 5432 -u postgres -g the_geom -r -k eda2.0_RHT "select id_drain,the_geom from rht.rhtvs2 where id_drain in (select rht.upstream_segments(118978)) or id_drain=118978 ;"

Bd_carthage_BV

Last modified 13 years ago Last modified on Oct 3, 2012 4:53:08 PM

Download in other formats:

Plain Text