back to first page[..][[BR]]
back to ["RHT"][[BR]]
Go to ["Download and load RHT topology"][[BR]]

= Create new database eda2.0_RHT =
right clic, create new database, use template postgis to that the database is a geodatabase [[BR]]
["Integrate schema into eda2.0_RHT"]

= Integrating RHT into postgre (rht.shp) =
{{{
NTF_France_II_degrees / NTF Lambert zone II (srid 27572) --> ETRS1989-LAEA (srid 3035)

--Celine
d:
cd D:\CelineJouanin\RHT_Estimkart
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 27572 -c -g the_geom -W LATIN1 -I rht_vs2.shp rht > rht.sql 
(I can't convert this version into utf8 with notepad)
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -h localhost -U postgres -p 5432 -f rht.sql 

--Creation du schema rht
CREATE SCHEMA rht;
ALTER TABLE rht SET SCHEMA rht;

--Changement du type de projection :   
ALTER TABLE rht.rht DROP CONSTRAINT enforce_srid_the_geom;
UPDATE rht.rht SET the_geom = ST_transform(the_geom, 3035); 
ALTER TABLE rht.rht ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);

ALTER TABLE rht.rht DROP CONSTRAINT rht_pkey
ALTER TABLE rht.rht ADD CONSTRAINT  pk_id_drain PRIMARY KEY (id_drain);

-- Create an index on rht and attributs tables
CREATE INDEX indexrht ON rht.rht
  USING GIST ( the_geom GIST_GEOMETRY_OPS );

DROP INDEX IF EXISTS rht.indexrht2;
CREATE INDEX indexrht2
ON rht.rht
USING btree (id_drain);
}}}


== Attributs RHT Integrating (Attributs_RHT_fev_2011.txt) ==
See in ["CookBook export access"] to integrate the database into postgre from ACCESS (it's usefull when you got lots of variables in the table)
This allows us to create the table quickly.
{{{
#!sql
drop table  if exists rht.attributs_rht_fev_2011;
CREATE TABLE rht.attributs_rht_fev_2011
(
  ID_DRAIN integer,
  TJAN_MOY double precision,
  TJUIL_MOY double precision,
  NONCALC integer,
  REGION_CSP character varying(255),
  D_SOURCE character varying(255),
  ALTITUDE character varying(255),
  SFBVU double precision,
  LONG_ integer,
  STRAHLER integer,
  SURF_BVICinf character varying(255),
  SURF_BV double precision,
  SURF_BVICsup character varying(255),
  PENTEICinf character varying(255),
  PENTE character varying(255),
  PENTEICsup character varying(255),
  MINQMXICinf character varying(255),
  MINQMX double precision,
  MINQMXICsup character varying(255),
  MODULE_ICinf character varying(255),
  MODULE_ double precision,
  MODULE_ICsup character varying(255),
  LICinf character varying(255),
  L character varying(255),
  LICsup character varying(255),
  HICinf character varying(255),
  H character varying(255),
  HICsup character varying(255),
  PESP_ABL character varying(255),
  PESP_ANG character varying(255),
  PESP_BAF character varying(255),
  PESP_BAM character varying(255),
  PESP_BLN character varying(255),
  PESP_BOU character varying(255),
  PESP_BRB character varying(255),
  PESP_BRO character varying(255),
  PESP_CAS character varying(255),
  PESP_CCO character varying(255),
  PESP_CHA character varying(255),
  PESP_CHE character varying(255),
  PESP_EPI character varying(255),
  PESP_EPT character varying(255),
  PESP_GAR character varying(255),
  PESP_GOU character varying(255),
  PESP_GRE character varying(255),
  PESP_HOT character varying(255),
  PESP_LOF character varying(255),
  PESP_LOT character varying(255),
  PESP_LPP character varying(255),
  PESP_OBR character varying(255),
  PESP_PCH character varying(255),
  PESP_PER character varying(255),
  PESP_PES character varying(255),
  PESP_ROT character varying(255),
  PESP_SAN character varying(255),
  PESP_SAT character varying(255),
  PESP_SPI character varying(255),
  PESP_TAN character varying(255),
  PESP_TOX character varying(255),
  PESP_TRF character varying(255),
  PESP_VAI character varying(255),
  PESP_VAN character varying(255)
);

copy rht.attributs_rht_fev_2011  from 'D:/CelineJouanin/RHT_Estimkart/Attributs_RHT_fev_2011_vs2.csv' with csv delimiter as ';' header; 
--Plus de problème de doublons
--UPDATE rht.attributs_rht_fev_2011 SET id_drain=240000 where id_drain=223988 and surf_bv=14.3;  ---probleme de doublons)
alter table rht.attributs_rht_fev_2011 add constraint  pk_id_drainatt primary key (id_drain);
set client_encoding to 'latin1';

COMMENT ON COLUMN rht.attributs_rht_fev_2011.tjan_moy IS 'Température minimale moyenne de air en janvier(°C)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011.tjuil_moy IS 'Température maximale moyenne de air en juillet(°C)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011.surf_bv IS 'Surperficie du bassin versant (km²)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011.surf_bvicinf IS 'Limite inférieure IC de Surperficie du bassin versant (km²)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011.surf_bvicsup IS 'Limite supérieure IC de Surperficie du bassin versant (km²)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011.pente IS 'Pente moyenne du bassin versant (m/km)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011.penteicinf IS 'Limite inférieure IC de la Pente moyenne du bassin versant (m/km)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011.penteicsup IS 'Limite supérieure IC de la Pente moyenne du bassin versant (m/km)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011.altitude IS 'Altitude moyenne du bassin versant (m)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011.module_ IS 'Module (m^3/s)';
}}}

Nombre de lignes dans la table attributs : 114600


Version RHT onema (à voir avec Hervé pour récuperer données manquantes) système de coordonnées Lambert II étandu --> ETRS LAEA (srid 3035)
{{{
d:
cd D:\CelineJouanin\RHT_Estimkart
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -c -g the_geom -W LATIN1 -I rht_simulationResultats_Proj.shp rht_simulation > rht_simulation.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -h localhost -U postgres -p 5432 -f rht_simulation.sql 

alter table rht_simulation set schema rht
}}}

New version attribut
{{{
#!sql
drop table if exists rht.attributs_rht_fev_2011;
alter table "Attributs_RHT_fev_2011_vs2" set schema rht;
ALTER TABLE rht."Attributs_RHT_fev_2011_vs2" RENAME TO attributs_rht_fev_2011_vs2;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "ID_DRAIN" TO ID_DRAIN;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "TJAN_MOY" TO TJAN_MOY;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "TJUIL_MOY" TO TJUIL_MOY;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "NONCALC" TO NONCALC;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "REGION_CSP" TO REGION_CSP;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "D_SOURCE" TO D_SOURCE;  
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "ALTITUDE" TO ALTITUDE;  
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "SFBVU" TO SFBVU;  
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "long" TO LONG_;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "STRAHLER" TO STRAHLER;  
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "SURF_BVICinf" TO SURF_BVICinf;  
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "SURF_BV" TO SURF_BV;  
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "SURF_BVICsup" TO SURF_BVICsup;  
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PENTEICinf" TO PENTEICinf;      
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PENTE" TO PENTE;      
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PENTEICsup" TO PENTEICsup;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "MINQMXICinf" TO MINQMXICinf;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "MINQMX" TO MINQMX;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "MINQMXICsup" TO MINQMXICsup;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "MODULE_ICinf" TO MODULE_ICinf;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "MODULE_" TO MODULE;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "MODULE_ICsup" TO MODULE_ICsup;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "LICinf" TO LICinf;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "L" TO L;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "LICsup" TO LICsup;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "HICinf" TO HICinf;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "H" TO H;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "HICsup" TO HICsup;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_ABL" TO PESP_ABL;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_ANG" TO PESP_ANG;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_BAF" TO PESP_BAF;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_BAM" TO PESP_BAM;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_BLN" TO PESP_BLN;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_BOU" TO PESP_BOU;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_BRB" TO PESP_BRB;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_BRO" TO PESP_BRO;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_CAS" TO PESP_CAS;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_CCO" TO PESP_CCO;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_CHA" TO PESP_CHA;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_CHE" TO PESP_CHE;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_EPI" TO PESP_EPI;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_EPT" TO PESP_EPT;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_GAR" TO PESP_GAR;
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_GOU" TO PESP_GOU;  
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_GRE" TO PESP_GRE;   
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_HOT" TO PESP_HOT;   
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_LOF" TO PESP_LOF;   
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_LOT" TO PESP_LOT;   
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_LPP" TO PESP_LPP;   
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_OBR" TO PESP_OBR;   
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_PCH" TO PESP_PCH;   
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_PER" TO PESP_PER;   
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_PES" TO PESP_PES;   
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_ROT" TO PESP_ROT;   
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_SAN" TO PESP_SAN;   
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_SAT" TO PESP_SAT;   
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_SPI" TO PESP_SPI;   
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_TAN" TO PESP_TAN;   
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_TOX" TO PESP_TOX;   
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_TRF" TO PESP_TRF;   
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_VAI" TO PESP_VAI;   
ALTER TABLE rht.Attributs_RHT_fev_2011_vs2 RENAME COLUMN "PESP_VAN" TO PESP_VAN;   

alter table rht.attributs_rht_fev_2011_vs2 rename column long_ to length;

COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.tjan_moy IS 'Température minimale moyenne de air en janvier(°C)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.tjuil_moy IS 'Température maximale moyenne de air en juillet(°C)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.surf_bv IS 'Surperficie du bassin versant (km²)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.surf_bvicinf IS 'Limite inférieure IC de Surperficie du bassin versant (km²)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.surf_bvicsup IS 'Limite supérieure IC de Surperficie du bassin versant (km²)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.pente IS 'Pente moyenne du bassin versant (m/km)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.penteicinf IS 'Limite inférieure IC de la Pente moyenne du bassin versant (m/km)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.penteicsup IS 'Limite supérieure IC de la Pente moyenne du bassin versant (m/km)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.altitude IS 'Altitude moyenne du bassin versant (m)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.id_drain IS 'Identifiant de chaque arc';		
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.region_csp IS 'Grand bassin d appartenance (8BV d après Oberdorff et al. 2001';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.d_source IS 'Distance à la source (km)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.sfbvu IS 'Surface bassin versant unitaire (km²)';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.strahler IS 'Ordre de l arc';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.minqmxicinf IS 'Limite inférieure IC du minimum des débits mensuels m3/s';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.minqmx IS 'Minimum des débits mensuels	m3/s';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.minqmxicsup IS	'Limite supérieur IC du minimum des débits mensuels	m3/s';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.module_icinf IS 'Limite inférieur IC du débit moyen	(m3) /s';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.module IS 'Débit moyen (module)	(m3) /s';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.module_icsup IS 'Limite supérieure IC du débit moyen	(m3)/s';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.licinf IS 'Limite inférieure IC de la largeur mouillée';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.l IS 'Largeur mouillée moyenne du tronçon au débit instantané Q';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.licsup IS 'Limite supérieure IC de la largeur mouillée';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.hicinf IS 'Limite inférieure IC de hauteur d eau';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.h IS 'Hauteur d eau moyenne du tronçon au débit instantané Q';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.hicsup IS 'Limite supérieure IC de hauteur d eau';
COMMENT ON COLUMN rht.attributs_rht_fev_2011_vs2.pesp_ABL IS 'Peuplement en place';
}}}

{{{
#!sql
--Index table attributs_rht_fev_2011_vs2
DROP INDEX IF EXISTS rht.indexattribut;
CREATE INDEX indexattribut
ON rht.attributs_rht_fev_2011_vs2
USING btree (id_drain);
}}}