Changes between Version 4 and Version 5 of CookBook taux_etagement


Ignore:
Timestamp:
Nov 27, 2010 9:40:19 PM (14 years ago)
Author:
cedric
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • CookBook taux_etagement

    v4 v5  
    22#!sql 
    33-- ddh=deltadamheight 
    4 --dnh = deltanaturalheight 
     4--dnch = deltanaturalcatchmentheight 
    55-- calcul pour wso_id=3 
    66--te taux d'étagement 
     7-- dnh deltanodeheight 
    78 
    89select max(cs_height)-min(cs_height) as ddh * from ccm21.catchments c 
    … …  
    1011where wso3_id=294040; 
    1112 
    12 select  max(elev_mean)-min(elev_mean) as dnh from ccm21.catchments c 
     13select  max(elev_mean)-min(elev_mean) as dnch from ccm21.catchments c 
    1314join ccm21.riversegments r on r.wso1_id=c.wso1_id 
    1415where wso3_id=294040; 
    … …  
    1718drop table if exists ccm21.bretagne_test; 
    1819create table ccm21.bretagne_test as ( 
    19         select te.*,c0.the_geom from ccm21.catchments_bretagne c0 join ( 
    20                 select wso3_id,max(cs_height)-min(cs_height) as ddh, 
    21                  max(elev_mean)-min(elev_mean) as dnh, 
    22                 CASE WHEN (max(elev_mean)-min(elev_mean))=0 then NULL 
    23                 ELSE  (max(cs_height)-min(cs_height)) / (max(elev_mean)-min(elev_mean))  
    24                 END as te  
    25                 from ccm21.catchments_bretagne c 
    26                 join ccm21.riversegments_bretagne r on r.wso1_id=c.wso1_id 
    27                 group by wso3_id) as te 
    28         on te.wso3_id=c0.wso3_id); 
     20        select te.*,c0.the_geom from ccm21.catchments_bretagne c0 join ( 
     21                select wso3_id,max(cs_height)-min(cs_height) as ddh, 
     22                 max(elev_mean)-min(elev_mean) as dnch, 
     23                CASE WHEN (max(elev_mean)-min(elev_mean))=0 then NULL 
     24                ELSE  (max(cs_height)-min(cs_height)) / (max(elev_mean)-min(elev_mean))  
     25                END as te  
     26                from ccm21.catchments_bretagne c 
     27                join ccm21.riversegments_bretagne r on r.wso1_id=c.wso1_id 
     28                group by wso3_id) as te 
     29        on te.wso3_id=c0.wso3_id); 
    2930alter table ccm21.bretagne_test add column id serial; 
    3031 alter table ccm21.bretagne_test add CONSTRAINT bretagne_test_pkey PRIMARY KEY (id); 
    … …  
    3334 alter table ccm21.bretagne_test add CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035); 
    3435 
    35  drop table if exists ccm21.bretagne_test_wso_2; 
    36 create table ccm21.bretagne_test_wso_2 as ( 
    37         select te.*,c0.the_geom from ccm21.catchments_bretagne c0 join ( 
    38                 select wso2_id,max(cs_height)-min(cs_height) as ddh, 
    39                  max(elev_mean)-min(elev_mean) as dnh, 
    40                  CASE WHEN (max(elev_mean)-min(elev_mean))=0 then NULL 
    41                  ELSE  (max(cs_height)-min(cs_height)) / (max(elev_mean)-min(elev_mean))  
    42                  END as te  
    43                  from ccm21.catchments_bretagne c 
    44                 join ccm21.riversegments_bretagne r on r.wso1_id=c.wso1_id 
    45                 group by wso2_id) as te 
    46         on te.wso2_id=c0.wso2_id); 
    47 alter table ccm21.bretagne_test_wso_2 add column id serial; 
    48  alter table ccm21.bretagne_test_wso_2 add CONSTRAINT bretagne_testwso2_pkey PRIMARY KEY (id); 
    49  alter table ccm21.bretagne_test_wso_2 add CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2); 
    50  alter table ccm21.bretagne_test_wso_2 add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL); 
    51  alter table ccm21.bretagne_test_wso_2 add CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035); 
    5236 
    53 drop table if exists ccm21.taux_etagement; 
    54 create table ccm21.taux_etagement as ( 
    55         select te.*,c0.the_geom from ccm21.catchments c0 join ( 
    56                 select wso3_id,max(cs_height)-min(cs_height) as ddh, 
    57                  max(elev_mean)-min(elev_mean) as dnh, 
    58                  CASE WHEN (max(elev_mean)-min(elev_mean))=0 then NULL 
    59                  ELSE  (max(cs_height)-min(cs_height)) / (max(elev_mean)-min(elev_mean))  
    60                  END as te  
    61                  from ccm21.catchments c 
    62                 join (select * from ccm21.riversegments  WHERE wso_id IN (SELECT wso_id FROM europe.wso WHERE area='France'))r 
    63   on r.wso1_id=c.wso1_id 
    64                 group by wso3_id) as te 
    65         on te.wso3_id=c0.wso3_id); 
    66 alter table ccm21.taux_etagement add column id serial; 
    67  alter table ccm21.taux_etagement add CONSTRAINT taux_etagement_pkey PRIMARY KEY (id); 
    68  alter table ccm21.taux_etagement add CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2); 
    69  alter table ccm21.taux_etagement add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL); 
    70  alter table ccm21.taux_etagement add CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035); 
    71   
    7237 
    7338-- essai en prenant les altitudes des rivernodes (c'est quand même mieux) 
    … …  
    9560 
    9661  
     62drop table if exists ccm21.taux_etagement; 
     63create table ccm21.taux_etagement as ( 
     64        select te.*,c0.the_geom from ccm21.catchments c0 join ( 
     65              select wso3_id,max(cs_height)-min(cs_height) as ddh, 
     66                 max(elev_mean)-min(elev_mean) as dnch, 
     67                 max(fromnode.elev) -min(tonode.elev) as dnh, 
     68                 CASE WHEN (max(fromnode.elev) -min(tonode.elev))=0 then NULL 
     69                 ELSE  (max(cs_height)-min(cs_height)) /(max(fromnode.elev) -min(tonode.elev)) 
     70                 END as te 
     71                 from ccm21.catchments c 
     72                join (select * from ccm21.riversegments  WHERE wso_id IN (SELECT wso_id FROM europe.wso WHERE area='France' OR area='Spain_Atl')) r 
     73                on r.wso1_id=c.wso1_id 
     74                join (select id,elev from ccm21.rivernodes  WHERE wso_id IN (SELECT wso_id FROM europe.wso WHERE area='France' OR area='Spain_Atl')) 
     75                fromnode on r.fromnode=fromnode.id 
     76                join (select id,elev from ccm21.rivernodes  WHERE wso_id IN (SELECT wso_id FROM europe.wso WHERE area='France' OR area='Spain_Atl')) 
     77                tonode on r.tonode=tonode.id 
     78                group by wso3_id) as te 
     79        on te.wso3_id=c0.wso3_id); 
     80alter table ccm21.taux_etagement add column id serial; 
     81 alter table ccm21.taux_etagement add CONSTRAINT taux_etagement_pkey PRIMARY KEY (id); 
     82 alter table ccm21.taux_etagement add CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2); 
     83 alter table ccm21.taux_etagement add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL); 
     84 alter table ccm21.taux_etagement add CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035); 
     85  
    9786}}}