Changes between Version 2 and Version 3 of CookBook taux_etagement

Timestamp:

Nov 26, 2010 10:32:58 PM (14 years ago)

Author:

cedric

Comment:

--

CookBook taux_etagement

@@ -50 +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);
  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);
+ 
 }}}