Opened 14 years ago

Closed 7 years ago

#74 closed task (fixed)

calcul taux d'étagement

Reported by: cedric Owned by: cedric
Priority: trivial Milestone:
Component: SIG-data Version: EDA2.0
Keywords: Cc:

Description 

-- 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 c.wso1_id,
                cs_height,
                sum(g.height) as sumheight,
                sum(g.nbdams) as sumnbdams,
                 fromnode.elev -tonode.elev as slope,
                 CASE WHEN (fromnode.elev -tonode.elev)=0 then NULL
                 ELSE  cs_height /(fromnode.elev -tonode.elev)
                 END as te,
                CASE WHEN fromnode.elev=0 then NULL
                ELSE cs_height/fromnode.elev END as te_mer,
                c.the_geom
                 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
                left join geobs2010.roe_ccm_500_final g on r.gid=g.gid
                group by c.wso1_id,cs_height, slope, te_mer,c.the_geom);
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);
 

-- France

 drop table if exists ccm21.taux_etagement;
create table ccm21.taux_etagement as (
                select c.wso1_id,
                cs_height,
                sum(g.height) as sumheight,
                sum(g.nbdams) as sumnbdams,
                 fromnode.elev -tonode.elev as slope,
                 CASE WHEN (fromnode.elev -tonode.elev)=0 then NULL
                 ELSE  cs_height /(fromnode.elev -tonode.elev)
                 END as te,
                CASE WHEN fromnode.elev=0 then NULL
                ELSE cs_height/fromnode.elev END as te_mer,
                c.the_geom
                 from ccm21.catchments c
                join ccm21.riversegments 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
                left join geobs2010.roe_ccm_500_final g on r.gid=g.gid
                group by c.wso1_id,cs_height, slope, te_mer,c.the_geom);
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);

Change History (6)

comment:1 Changed 14 years ago by cedric

  • Status changed from new to accepted

comment:2 Changed 14 years ago by cedric 

drop table if exists ccm21.taux_etagement;
create table ccm21.taux_etagement as (
                select c.wso1_id,
                cs_height,
                sum(g.height) as sumheight,
                sum(g.nbdams) as sumnbdams,
                 fromnode.elev -tonode.elev as slope,
                 CASE WHEN (fromnode.elev -tonode.elev)=0 then NULL
                 ELSE  cs_height /(fromnode.elev -tonode.elev)
                 END as te,
                CASE WHEN fromnode.elev=0 then NULL
                ELSE cs_height/fromnode.elev END as te_mer,
                c.the_geom
                 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  
                left join geobs2010.roe_ccm_500_final g on r.gid=g.gid
                group by c.wso1_id,cs_height, slope, te_mer,c.the_geom);
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);

comment:3 Changed 14 years ago by cedric 

drop table if exists ccm21.bretagne_test;
create table ccm21.bretagne_test as (
                select c.wso1_id,
                cs_height,
                sum(g.height) as sumheight,
                sum(g.nbdams) as sumnbdams,
                 fromnode.elev -tonode.elev as slope,
                 CASE WHEN (fromnode.elev -tonode.elev)=0 then NULL
                 ELSE  sum(g.height)/(fromnode.elev -tonode.elev)
                 END as te,
                CASE WHEN fromnode.elev=0 then NULL
                ELSE cs_height/fromnode.elev END as te_mer,
                c.the_geom
                 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
                left join geobs2010.roe_ccm_500_final g on r.gid=g.gid
                group by c.wso1_id,cs_height, slope, te_mer,c.the_geom);
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 c.wso1_id,
                cs_height,
                sum(g.height) as sumheight,
                sum(g.nbdams) as sumnbdams,
                 fromnode.elev -tonode.elev as slope,
                 CASE WHEN (fromnode.elev -tonode.elev)=0 then NULL
                 ELSE  sum(g.height)/(fromnode.elev -tonode.elev)
                 END as te,
                CASE WHEN fromnode.elev=0 then NULL
                ELSE cs_height/fromnode.elev END as te_mer,
                c.the_geom
                 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  
                left join geobs2010.roe_ccm_500_final g on r.gid=g.gid
                group by c.wso1_id,cs_height, slope, te_mer,c.the_geom);
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);

comment:5 Changed 14 years ago by cedric

drop table if exists ccm21.taux_etagement;

create table ccm21.taux_etagement as (

select c.wso1_id,
cs_height,
c_height,
r.nbdams,

fromnode.elev -tonode.elev as slope,
CASE WHEN (fromnode.elev -tonode.elev)=0 then -9999
ELSE c_height/(fromnode.elev -tonode.elev)
END as te,

CASE WHEN fromnode.elev=0 then NULL
ELSE cs_height/fromnode.elev END as te_mer,
c.the_geom

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

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

comment:6 Changed 7 years ago by cedric

  • Milestone POSE => Writing a description of EDA 2.0 deleted

Milestone POSE => Writing a description of EDA 2.0 deleted

comment:7 Changed 7 years ago by cedric

  • Resolution set to fixed
  • Status changed from accepted to closed
Note: See TracTickets for help on using tickets.