{{{
#!sql
-- ddh=deltadamheight
--dnh = deltanaturalheight
-- calcul pour wso_id=3
--te taux d'étagement

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 dnh 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 dnh,
		 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);

 drop table if exists ccm21.bretagne_test_wso_2;
create table ccm21.bretagne_test_wso_2 as (
	select te.*,c0.the_geom from ccm21.catchments_bretagne c0 join (
		select wso2_id,max(cs_height)-min(cs_height) as ddh,
		 max(elev_mean)-min(elev_mean) as dnh,
		 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 wso2_id) as te
	on te.wso2_id=c0.wso2_id);
alter table ccm21.bretagne_test_wso_2 add column id serial;
 alter table ccm21.bretagne_test_wso_2 add CONSTRAINT bretagne_testwso2_pkey PRIMARY KEY (id);
 alter table ccm21.bretagne_test_wso_2 add CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2);
 alter table ccm21.bretagne_test_wso_2 add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
 alter table ccm21.bretagne_test_wso_2 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 dnh,
		 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 c
		join (select * from ccm21.riversegments  WHERE wso_id IN (SELECT wso_id FROM europe.wso WHERE area='France'))r
  on r.wso1_id=c.wso1_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);
 

-- 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);

 
}}}