Context Navigation

back to first page..
back to RHT
Go to Download and load RHT topology

Create new database eda2.0_RHT

right clic, create new database, use template postgis to that the database is a geodatabase
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)
Projection du RHT en ETRS LAEA 1989 (srid 3035) sous ArcGis see Cookbook Change the coordinate system

--Creation du schema rht
CREATE SCHEMA rht;

---Data Integration

d:
cd D:\\RHT_Estimkart\RHTprojection3035
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -c -g the_geom -W LATIN1 -I rht_vs2_Project.shp rht > rht.sql 
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -h localhost -U postgres -p 5432 -f rht.sql 

ALTER TABLE rht SET SCHEMA rht;

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

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

Number of lines into the table attributs: 114600

Version RHT onema (à voir avec Hervé pour récuperer données manquantes) système de coordonnées Lambert II étendu --> 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 for the attribut table

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

--Create an index for 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);

---Integrating a geometry column
SELECT AddGeometryColumn ( 'rht','attributs_rht_fev_2011_vs2','the_geom',3035,'MULTILINESTRING',2);
update rht.attributs_rht_fev_2011_vs2 set the_geom=rht.the_geom from rht.rht
 where rht.id_drain=attributs_rht_fev_2011_vs2.id_drain;

Last modified 7 years ago Last modified on Jun 1, 2018 5:56:06 PM

Download in other formats:

Plain Text