-- ddh=deltadamheight
--dnch = deltanaturalcatchmentheight
-- calcul pour wso_id=3
--te taux d'étagement
-- dnh deltanodeheight

select max(cs_height)-min(cs_height) as ddh * from ccm21.catchments c
join ccm21.riversegments r on r.wso1_id=c.wso1_id
where wso3_id=294040;

select  max(elev_mean)-min(elev_mean) as dnch from ccm21.catchments c
join ccm21.riversegments r on r.wso1_id=c.wso1_id
where wso3_id=294040;


drop table if exists ccm21.bretagne_test;
create table ccm21.bretagne_test as (
        select te.*,c0.the_geom from ccm21.catchments_bretagne c0 join (
                select wso3_id,max(cs_height)-min(cs_height) as ddh,
                 max(elev_mean)-min(elev_mean) as dnch,
                 CASE WHEN (max(elev_mean)-min(elev_mean))=0 then NULL
                 ELSE  (max(cs_height)-min(cs_height)) / (max(elev_mean)-min(elev_mean)) 
                 END as te 
                 from ccm21.catchments_bretagne c
                join ccm21.riversegments_bretagne r on r.wso1_id=c.wso1_id
                group by wso3_id) as te
        on te.wso3_id=c0.wso3_id);
alter table ccm21.bretagne_test add column id serial;
 alter table ccm21.bretagne_test add CONSTRAINT bretagne_test_pkey PRIMARY KEY (id);
 alter table ccm21.bretagne_test add CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2);
 alter table ccm21.bretagne_test add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
 alter table ccm21.bretagne_test add CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);



-- essai en prenant les altitudes des rivernodes (c'est quand même mieux)

 drop table if exists ccm21.bretagne_test;
create table ccm21.bretagne_test as (
        select te.*,c0.the_geom from ccm21.catchments_bretagne c0 join (
                select wso3_id,max(cs_height)-min(cs_height) as ddh,
                 max(elev_mean)-min(elev_mean) as dnch,
                 max(fromnode.elev) -min(tonode.elev) as dnh,
                 CASE WHEN (max(fromnode.elev) -min(tonode.elev))=0 then NULL
                 ELSE  (max(cs_height)-min(cs_height)) /(max(fromnode.elev) -min(tonode.elev))
                 END as te
                 from ccm21.catchments_bretagne c
                join ccm21.riversegments_bretagne r on r.wso1_id=c.wso1_id
                join ccm21.rivernodes fromnode on r.fromnode=fromnode.id
                join ccm21.rivernodes tonode on r.tonode=tonode.id
                group by wso3_id) as te
        on te.wso3_id=c0.wso3_id);
alter table ccm21.bretagne_test add column id serial;
 alter table ccm21.bretagne_test add CONSTRAINT bretagne_test_pkey PRIMARY KEY (id);
 alter table ccm21.bretagne_test add CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2);
 alter table ccm21.bretagne_test add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
 alter table ccm21.bretagne_test add CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);

 
drop table if exists ccm21.taux_etagement;
create table ccm21.taux_etagement as (
        select te.*,c0.the_geom from ccm21.catchments c0 join (
              select wso3_id,max(cs_height)-min(cs_height) as ddh,
                 max(elev_mean)-min(elev_mean) as dnch,
                 max(fromnode.elev) -min(tonode.elev) as dnh,
                 CASE WHEN (max(fromnode.elev) -min(tonode.elev))=0 then NULL
                 ELSE  (max(cs_height)-min(cs_height)) /(max(fromnode.elev) -min(tonode.elev))
                 END as te
                 from ccm21.catchments c
                join (select * from ccm21.riversegments  WHERE wso_id IN (SELECT wso_id FROM europe.wso WHERE area='France' OR area='Spain_Atl')) r
                on r.wso1_id=c.wso1_id
                join (select id,elev from ccm21.rivernodes  WHERE wso_id IN (SELECT wso_id FROM europe.wso WHERE area='France' OR area='Spain_Atl'))
                fromnode on r.fromnode=fromnode.id
                join (select id,elev from ccm21.rivernodes  WHERE wso_id IN (SELECT wso_id FROM europe.wso WHERE area='France' OR area='Spain_Atl'))
                tonode on r.tonode=tonode.id
                group by wso3_id) as te
        on te.wso3_id=c0.wso3_id);
alter table ccm21.taux_etagement add column id serial;
 alter table ccm21.taux_etagement add CONSTRAINT taux_etagement_pkey PRIMARY KEY (id);
 alter table ccm21.taux_etagement add CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2);
 alter table ccm21.taux_etagement add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
 alter table ccm21.taux_etagement add CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);

-- par bv
 drop table if exists ccm21.bretagne_test;
create table ccm21.bretagne_test as (
        select te.*,c0.the_geom from ccm21.catchments_bretagne c0 join (
                select c.wso1_id,
                cs_height as ddh,
                 fromnode.elev -tonode.elev as dnh,
                 CASE WHEN (fromnode.elev -tonode.elev)=0 then NULL
                 ELSE  cs_height /(fromnode.elev -tonode.elev)
                 END as te
                 from ccm21.catchments_bretagne c
                join ccm21.riversegments_bretagne r on r.wso1_id=c.wso1_id
                join ccm21.rivernodes fromnode on r.fromnode=fromnode.id
                join ccm21.rivernodes tonode on r.tonode=tonode.id
                ) as te
        on te.wso1_id=c0.wso1_id);
alter table ccm21.bretagne_test add column id serial;
 alter table ccm21.bretagne_test add CONSTRAINT bretagne_test_pkey PRIMARY KEY (id);
 alter table ccm21.bretagne_test add CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2);