Changes between Version 42 and Version 43 of CCM2 download and load

Timestamp:

Apr 3, 2010 3:39:30 PM (15 years ago)

Author:

cedric

Comment:

--

CCM2 download and load

@@ -201 +201 @@
 
 === Plsql function which returns the id of the primary catchments within the catchment corresponding to the strahler order ===
+{{{
+-- it is necessary to create a type to hold the output type
+DROP TYPE IF EXISTS wso1_id;
+CREATE TYPE wso1_id as (wso1_id int);
+-- In the function I tried to create a wso type to simplify the text. Pb you need to specify 
+-- a tablename.columname%TYPE which will have to change along with the column, hence the big ugly script below...
+DROP  FUNCTION IF EXISTS ccm21.pricatch_from_catch(gid_ numeric);
+CREATE OR REPLACE FUNCTION ccm21.pricatch_from_catch(gid_ numeric) RETURNS setof int AS $$
+        DECLARE
+        result RECORD;
+        pri wso1_id%rowtype;
+        resultcount RECORD;
+        BEGIN
+            SELECT INTO result strahler FROM ccm21.riversegments where gid=gid_;
+            RAISE NOTICE 'result is %' ,result;
+            IF result.strahler=2 THEN   
+                RAISE NOTICE 'WSO_2 used';
+                SELECT INTO resultcount count(*) from  ccm21.catchments c  where wso2_id =
+                        (select wso2_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_);
+                RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; 
+                for pri in select wso1_id from ccm21.catchments c  where wso2_id =
+                        (select wso2_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_) loop
+                pri.wso1_id=CAST(pri.wso1_id AS int8);
+                return next pri.wso1_id;
+                end loop;
+                return; 
+            ELSIF result.strahler=3 THEN 
+                RAISE NOTICE 'WSO_3 used';
+                SELECT INTO resultcount count(*) from  ccm21.catchments c  where wso3_id =
+                        (select wso3_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_);
+                RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; 
+                for pri in select wso1_id from ccm21.catchments c  where wso3_id =
+                        (select wso3_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_) loop
+                pri.wso1_id=CAST(pri.wso1_id AS int8);
+                return next pri.wso1_id;
+                end loop;
+                return; 
+            ELSIF result.strahler=4 THEN
+                RAISE NOTICE 'WSO_4 used';
+                SELECT INTO resultcount count(*) from  ccm21.catchments c  where wso4_id =
+                        (select wso4_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_);
+                RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; 
+                for pri in select wso1_id from ccm21.catchments c  where wso4_id =
+                        (select wso4_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_) loop
+                pri.wso1_id=CAST(pri.wso1_id AS int8);
+                return next pri.wso1_id;
+                end loop;
+                return;                 
+            ELSIF result.strahler=5 THEN
+                RAISE NOTICE 'WSO_5 used';
+                SELECT INTO resultcount count(*) from  ccm21.catchments c  where wso5_id =
+                        (select wso5_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_);
+                RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; 
+                for pri in select wso1_id from ccm21.catchments c  where wso5_id =
+                        (select wso5_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_) loop
+                pri.wso1_id=CAST(pri.wso1_id AS int8);
+                return next pri.wso1_id;
+                end loop;
+                return; 
+            ELSIF result.strahler=6 THEN
+                RAISE NOTICE 'WSO_6 used';
+                SELECT INTO resultcount count(*) from  ccm21.catchments c  where wso6_id =
+                        (select wso6_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_);
+                RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; 
+                for pri in select wso1_id from ccm21.catchments c  where wso6_id =
+                        (select wso6_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_) loop
+                pri.wso1_id=CAST(pri.wso1_id AS int8);
+                return next pri.wso1_id;
+                end loop;
+                return;         
+            ELSIF result.strahler=7 THEN
+                RAISE NOTICE 'WSO_7 used';
+                SELECT INTO resultcount count(*) from  ccm21.catchments c  where wso7_id =
+                        (select wso7_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_);
+                RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; 
+                for pri in select wso1_id from ccm21.catchments c  where wso7_id =
+                        (select wso7_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_) loop
+                pri.wso1_id=CAST(pri.wso1_id AS int8);
+                return next pri.wso1_id;
+                end loop;
+                return; 
+            ELSIF result.strahler=8 THEN
+                RAISE NOTICE 'WSO_8 used';
+                SELECT INTO resultcount count(*) from  ccm21.catchments c  where wso8_id =
+                        (select wso8_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_);
+                RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; 
+                for pri in select wso1_id from ccm21.catchments c  where wso8_id =
+                        (select wso8_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_) loop
+                pri.wso1_id=CAST(pri.wso1_id AS int8);
+                return next pri.wso1_id;
+                end loop;
+                return;                 
+            ELSIF result.strahler=9 THEN
+                RAISE NOTICE 'WSO_9 used';
+                SELECT INTO resultcount count(*) from  ccm21.catchments c  where wso9_id =
+                        (select wso9_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_);
+                RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; 
+                for pri in select wso1_id from ccm21.catchments c  where wso9_id =
+                        (select wso9_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_) loop
+                pri.wso1_id=CAST(pri.wso1_id AS int8);
+                return next pri.wso1_id;
+                end loop;
+                return;                 
+            ELSIF result.strahler=10 THEN
+                RAISE NOTICE 'WSO_10 used';
+                SELECT INTO resultcount count(*) from  ccm21.catchments c  where wso10_id =
+                        (select wso10_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_);
+                RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; 
+                for pri in select wso1_id from ccm21.catchments c  where wso10_id =
+                        (select wso10_id from ccm21.riversegments r 
+                        join ccm21.catchments c on r.wso1_id=c.wso1_id 
+                        where r.gid=gid_) loop
+                pri.wso1_id=CAST(pri.wso1_id AS int8);
+                return next pri.wso1_id;
+                end loop;
+                return;                                                                 
+            ELSE RAISE NOTICE 'Strahler is larger than this function can handle !';
+            END IF;
+            RETURN;
+        END;
+        $$
+LANGUAGE 'plpgsql' ;
+COMMENT ON FUNCTION ccm21.pricatch_from_catch (gid_ numeric) IS 'Uses the gid from ccm21.riversegments, checks strahler, and select the catchment accordingly, then returns all primary catchments from this larger catchment';
+}}}
+{{{
+--examples for use
+
+SELECT  ccm21.pricatch_from_catch(234706); --la vilaine dans son ensemble
+-- NOTICE:  result is (6)
+-- NOTICE:  WSO_6 used
+-- NOTICE:  number of primary catchments in the catchment =(759)
+-- durée=2s
+--
+SELECT * from ccm21.catchments where wso1_id in (SELECT  ccm21.pricatch_from_catch(234706));
+}}}
+