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 sub8.*,
        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,
        sub14.res_value as p_up_no_impact
          from
        (select id_drain,dmer,dsource,res_value as exutoire, the_geom from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=8) as sub8
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 sub8.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 sub8.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 sub8.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 sub8.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 sub8.id_drain=sub13.id_drain        
join 
        (select id_drain, res_value from rht.rhtvs2 join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=14) as sub14
        on sub8.id_drain=sub14.id_drain     
);

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