back to first page[..][[BR]] back to ["RHT"][[BR]] == 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 }}}