Changes between Version 83 and Version 84 of CCM2 download and load

Timestamp:

Mar 3, 2014 5:30:05 PM (11 years ago)

Author:

cedric

Comment:

--

CCM2 download and load

@@ -631 +631 @@
 COMMENT ON FUNCTION ccm21.mycatchment2(numeric) IS 'Uses the wso1_id from ccm21.riversegments, checks strahler, and select the catchment accordingly, then returns all primary catchments from this larger catchment';
 }}}
+
+{{{#!sql
+-- Function: ccm21.upstream_segments2(numeric)
+
+-- DROP FUNCTION ccm21.upstream_segments2(numeric);
+
+CREATE OR REPLACE FUNCTION ccm21.upstream_segments2(wso1_id_ numeric)
+  RETURNS SETOF integer AS
+$BODY$
+        DECLARE
+        wso1_id_riversegments wso1_id%ROWTYPE;
+        rec_pfafstette_length RECORD;
+        rec_riversegments ccm21.riversegments%ROWTYPE;
+        -- pfafstette_chain_length int;
+        pfafstette_max_chain_length int;
+        pfafstette_value int8;
+        BEGIN
+        -- filling a new table with the results from a catchment
+        
+        DROP TABLE IF EXISTS ccm21.upstream_riversegments;
+        CREATE TABLE ccm21.upstream_riversegments AS SELECT * FROM ccm21.riversegments
+        where wso1_id in (SELECT  ccm21.mycatchment2(wso1_id_));
+        ALTER TABLE ccm21.upstream_riversegments ADD CONSTRAINT pk_upstream_riversegments PRIMARY KEY (wso1_id);
+
+        -- pfafstette chain length
+        --select into pfafstette_chain_length character_length(CAST(round(pfafstette) AS TEXT) 
+        --      FROM ccm21.upstream_riversegments WHERE wso1_id=wso1_id_;
+        -- RAISE NOTICE 'pfafstette chain length is %', pfafstette_chain_length;
+        -- pfafstette max chain length in the selected  basin 
+        select into pfafstette_max_chain_length max(character_length(CAST(round(pfafstette) AS TEXT)))
+                FROM ccm21.upstream_riversegments; 
+        -- pfafstette value
+        -- the chain is lengthened to the pfafstette max chain length
+        Update ccm21.upstream_riversegments set pfafstette = 
+        floor(pfafstette)*power(10,(pfafstette_max_chain_length-character_length(CAST(floor(pfafstette) AS TEXT) )));  
+        select into pfafstette_value CAST(round(pfafstette) AS int8) FROM ccm21.upstream_riversegments where wso1_id=wso1_id_;
+        RAISE NOTICE 'pfafstette  is %', pfafstette_value;
+
+        DELETE FROM ccm21.upstream_riversegments WHERE pfafstette < pfafstette_value; 
+        -- extracting the riversegments corresponding to the basin
+       
+        for wso1_id_riversegments in select wso1_id from ccm21.upstream_riversegments order by wso1_id loop
+                wso1_id_riversegments.wso1_id=CAST(wso1_id_riversegments.wso1_id AS int8);
+                return next wso1_id_riversegments.wso1_id;
+                end loop;
+                return; 
+        END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE
+  COST 100
+  ROWS 1000;
+ALTER FUNCTION ccm21.upstream_segments2(numeric)
+  OWNER TO postgres;
+COMMENT ON FUNCTION ccm21.upstream_segments2(numeric) IS 'This function uses ccm21.mycatchment2 to get all the segments from a catchment and searches all segments with a pfafstetter higher than the segment in the bassin. The pfafstette are trucated to the level of the segment used as input in the function';
+}}}