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 riverareakm2,
        sub8.res_value as abondance
          from
        (select id_drain,dmer,dsource,res_value as md, the_geom from rht.rht_topology join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=1) as sub1
join
        (select id_drain, res_value from rht.rht_topology 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.rht_topology 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.rht_topology 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.rht_topology 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.rht_topology 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.rht_topology join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=7) as sub7
        on sub1.id_drain=sub7.id_drain
join 
        (select id_drain, res_value from rht.rht_topology join rht.resultmodel on res_id_drain=id_drain where 
        res_mod_id=8) as sub8
        on sub1.id_drain=sub8.id_drain
);

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