wiki:Download and load RHT

Context Navigation

Version 32 (modified by celine, 14 years ago) (diff)
--

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 data into postgre (rht.shp and Attributs_RHT_fev_2011.txt)

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

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

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

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;   

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.module IS 'Module (m^3/s)';

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

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

Download in other formats:

Plain Text