back to first page..
back to RHT

CREATE TABLE rht.model_mod
(
  mod_id serial NOT NULL,
  mod_name character varying(30) NOT NULL,
  mod_date date,
  mod_description text NOT NULL,
  mod_location character varying(25), -- Geographical area to which the model is applied, should be consistent with europe.wso
  mod_formula text,
  CONSTRAINT model_mod_pkey PRIMARY KEY (mod_id),
  CONSTRAINT model_mod_mod_name_key UNIQUE (mod_name)
);

DROP TABLE IF EXISTS rht.resultmodel;
CREATE TABLE rht.resultmodel
(
  res_id serial NOT NULL,
  res_id_drain integer,
  res_value double precision,
  res_mod_id integer,
  CONSTRAINT resultmodel_pkey PRIMARY KEY (res_id),
  CONSTRAINT c_fk_res_mod_id FOREIGN KEY (res_mod_id)
      REFERENCES rht.model_mod (mod_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

drop table if exists rht.tableresultmodel;
create table rht.tableresultmodel as (
select sub1.*,
        sub2.res_value as mpa,
        sub3.res_value as mdmpa,
        sub4.res_value as largeur,
        sub5.res_value as surface,
        sub6.res_value as ers,
        sub7.res_value as abondance
          from
        (select id_drain,dmer,dsource,res_value as md, the_geom from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=1) as sub1
join
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=2) as sub2
        on sub1.id_drain=sub2.id_drain
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=3) as sub3
        on sub1.id_drain=sub3.id_drain
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=4) as sub4
        on sub1.id_drain=sub4.id_drain
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=5) as sub5
        on sub1.id_drain=sub5.id_drain
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=6) as sub6
        on sub1.id_drain=sub6.id_drain
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=7) as sub7
        on sub1.id_drain=sub7.id_drain  
);

create index indextableresultmod on rht.tableresultmodel using gist(the_geom) ;

drop table if exists rht.tableresultmodelVar;
create table rht.tableresultmodelVar as (
select sub14.*,
        sub9.res_value as p_urban,
        sub10.res_value as p_up_urban,
        sub11.res_value as p_agricultural,
        sub12.res_value as p_up_agricultural,
        sub13.res_value as p_unimpact
        from
        (select id_drain,dmer,dsource,res_value as p_up_no_impact, the_geom from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=14) as sub14
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=9) as sub9
        on sub14.id_drain=sub9.id_drain
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=10) as sub10
        on sub14.id_drain=sub10.id_drain
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=11) as sub11
        on sub14.id_drain=sub11.id_drain
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=12) as sub12
        on sub14.id_drain=sub12.id_drain
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=13) as sub13
        on sub14.id_drain=sub13.id_drain        
);

create index indextableresultmodVar on rht.tableresultmodelVar using gist(the_geom) ;

drop table if exists rht.tableresultmodel2;
create table rht.tableresultmodel2 as (
select sub1.*,
        sub2.res_value as mpa,
        sub3.res_value as mdmpa,
        sub4.res_value as abondance
          from
        (select id_drain,dmer,dsource,res_value as md, the_geom from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=15) as sub1
join
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=16) as sub2
        on sub1.id_drain=sub2.id_drain
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=17) as sub3
        on sub1.id_drain=sub3.id_drain
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=18) as sub4
        on sub1.id_drain=sub4.id_drain
);

drop table if exists rht.tableresultmodel3;
create table rht.tableresultmodel3 as (
select sub1.*,
        sub2.res_value as mpa,
        sub3.res_value as mdmpa,
        sub4.res_value as abondance
          from
        (select id_drain,dmer,dsource,res_value as md, the_geom from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=19) as sub1
join
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=20) as sub2
        on sub1.id_drain=sub2.id_drain
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=21) as sub3
        on sub1.id_drain=sub3.id_drain
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=22) as sub4
        on sub1.id_drain=sub4.id_drain
);

drop table if exists rht.tableresultmodel4;
create table rht.tableresultmodel4 as (
select sub1.*,
        sub2.res_value as mpa,
        sub3.res_value as mdmpa,
        sub4.res_value as abondance
          from
        (select id_drain,dmer,dsource,res_value as md, the_geom from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=23) as sub1
join
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=24) as sub2
        on sub1.id_drain=sub2.id_drain
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=25) as sub3
        on sub1.id_drain=sub3.id_drain
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=26) as sub4
        on sub1.id_drain=sub4.id_drain
); ---76812ms

Cédric : 26/01/2012 Echange des données avec Céline

E:\IAV\eda\rht>psql -U postgres -c "drop table rht.resultmodel" eda2.0_RHT
E:\IAV\eda\rht>psql -U postgres -c "drop table rht.model_mod" eda2.0_RHT
E:\IAV\eda\rht>psql -U postgres -f "model_mod.sql" eda2.0_RHT
E:\IAV\eda\rht>psql -U postgres -f "resultmodel.sql" eda2.0_RHT

Résultats du modèle sans impacts anthropiques

drop table if exists rht.tableresultmodelwitoutImpact;
create table rht.tableresultmodelwitoutImpact as (
select sub1.*,
        sub2.res_value as md,
        sub3.res_value as mdmpa,
        sub4.res_value as abondance
          from
        (select id_drain,dmer,dsource,res_value as mpa, the_geom from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=32) as sub1
join
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=33) as sub2
        on sub1.id_drain=sub2.id_drain
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=34) as sub3
        on sub1.id_drain=sub3.id_drain
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=35) as sub4
        on sub1.id_drain=sub4.id_drain
);