back to first page..
back to RHT

Version 2 rhtvs2

d:
cd d:\CelineJouanin\export_table\version2
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.attr_debit eda2.0_RHT> rht_attr_debit.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.rhtvs2_hydrographie_surfacique eda2.0_RHT> rhtvs2_hydrographie_surfacique.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.temp_ltree_distance eda2.0_RHT> temp_ltree_distance.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t bdmap2009.bdmap_rhtvs2 eda2.0_RHT> bdmap_rhtvs2.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t bdmap2009.bdmap_rhtvs2 -t rht.rhtvs2 -t rht.rhtvs2_roev2_nbdams -t rht.rhtvs2_roev2 -t rht.noeudmervs2 eda2.0_RHT> rhtvs2_4tables.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.rhtvs2 eda2.0_RHT> rhtvs2.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.noeudmervs2 eda2.0_RHT> noeudmervs2.sql

C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.model_mod eda2.0_RHT> model_mod.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.resultmodel eda2.0_RHT> resultmodel.sql

C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.attributs_rht_fev_2011_vs2 eda2.0_RHT> attributs_rht_fev_2011_vs2.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t bdmap2009.ers_full_france eda2.0_RHT> ers_full_france.sql

/* 
SCRIPT DE CREATION D'UN TABLEAU UNIQUE DES RESULTATS 
6 avril 2012 Cédric Briand Céline Jouanin
*/

-- pour voir les données du modèle, ci dessous seules certaines colonnes de résultats sont retenues

select * from rht.model_mod

-- le script utilise la fonction crosstab (text,text) pour passer d'un format 'long' a un format 'court' (données en colonnes)
-- comme crosstab est une fonction renvoyant un setof record, il faut nommer et typer les colonnes de sortie as....

DROP TABLE IF EXISTS rht.crosstab_rhtvs2;
CREATE TABLE rht.crosstab_rhtvs2 as (
	SELECT 
  rhtvs2.id_drain,
  crosstab.largeur, 
  crosstab.surface, 
  crosstab.ers_id_drain, 
  crosstab.exutoire, 
  crosstab.gamma, 
  crosstab.delta, 
  crosstab.densite, 
  crosstab.abondance,
  crosstab.nb_ar_mort_turb,
  crosstab.tx_mort_turb,
  rhtvs2.the_geom,
  rhtvs2.dmer,
  rhtvs2.dsource,
  rhtvs2.cumnbbar from rht.rhtvs2 left join 
	(SELECT * FROM crosstab('select res_id_drain, res_mod_id,res_value from rht.resultmodel
					where res_mod_id in (24,23,25,26,4,5,6,27,28,29)
					order by 1,2',
					'select mod_id from rht.model_mod where mod_id in (24,23,25,26,4,5,6,27,28,29) order by mod_id')
			AS ct(id_drain integer, 
			largeur numeric,
			surface numeric,
			ers_id_drain numeric,
			gamma numeric, 
			delta numeric,
			densite numeric,
			abondance numeric,
			exutoire numeric,
			nb_ar_mort_turb numeric,
			tx_mort_turb numeric) 
	)as crosstab
on rhtvs2.id_drain=crosstab.id_drain);

comment on table rht.crosstab_rhtvs2 is 'table crée le 6 avril 2012 a partir des résultats définitifs d''EDA, modifiée le 11/04/2012 par cedric pour intégrer les premiers résultats de turbine';
alter table rht.crosstab_rhtvs2 rename tx_mort_turb to tx_survie_turb
alter table rht.crosstab_rhtvs2 add column tx_mort_turb numeric;
update rht.crosstab_rhtvs2 set tx_mort_turb=1-tx_survie_turb;
-- creation des clés et des index qui vont bien
alter table rht.crosstab_rhtvs2 add constraint c_pk_crosstab_rhtvs2 PRIMARY KEY (id_drain);	
CREATE INDEX crosstab_rhtvs2_index
  ON rht.crosstab_rhtvs2
  USING btree
  (id_drain);
CREATE INDEX crosstab_rhtvs2_gistindex
  ON rht.rhtvs2
  USING gist
  (the_geom);
-- creation des clés et des index qui vont bien
alter table rht.crosstab_rhtvs2 add constraint c_pk_crosstab_rhtvs2 PRIMARY KEY (id_drain);	
CREATE INDEX crosstab_rhtvs2_index
  ON rht.crosstab_rhtvs2
  USING btree
  (id_drain);
CREATE INDEX crosstab_rhtvs2_gistindex
  ON rht.rhtvs2
  USING gist
  (the_geom);
  
-- code dump
-- pg_dump -U postgres -f 'v_rht_model_mod.sql' --table rht.crosstab.rhtvs2 eda2.0_RHT

comment on column rht.crosstab_rhtvs2.surface is 'surface en km²';
comment on column rht.crosstab_rhtvs2.largeur is 'largeur en m';

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

Version 1 rht

d:
cd d:\CelineJouanin\export_table\version1
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.rht_bdcarthage_roev2_nbdams eda2.0_RHT> rht_bdcarthage_roev2_nbdams.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.rht eda2.0_RHT> rht.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.noeudmer eda2.0_RHT> noeudmer.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.rht_topology eda2.0_RHT> rht_topology.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t uga2010.id_drain_uga eda2.0_RHT> uga2010_id_drain_uga.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t uga2010.id_bdcarthage eda2.0_RHT> uga2010_id_bdcarthage.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.noeudmer_polygon eda2.0_RHT> noeudmer_polygon.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.rht_150 eda2.0_RHT> rht_150.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.rht_150b eda2.0_RHT> rht_150b.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.rht_bdcarthage eda2.0_RHT> rht_bdcarthage.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.rht_roev2 eda2.0_RHT> rht_roev2.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.rht_bdcarthage_roev2 eda2.0_RHT> rht_bdcarthage_roev2.sql