Changes between Version 57 and Version 58 of CCM2 download and load

Timestamp:

Apr 5, 2010 6:27:14 PM (15 years ago)

Author:

cedric

Comment:

--

CCM2 download and load

@@ -371 +371 @@
 }}}
 === Plpgsql function which returns the riversegments upstream from the riversegment selected ===
+Note : It is necessary to create a table as the pfafstetter along an axis can have a shorter length upstream than the length selected
+The maximum length for a pfafstetter is 14 in the ccm. Transforming the database to store a column number with 14 number would increase the size of the database.
+It is also not possible to create a variable in PlSql storing more than one line. For these two reasons, a table upstream_segments is created and is filled with 
 {{{
 DROP TYPE IF EXISTS gid;
@@ -377 +380 @@
 CREATE OR REPLACE FUNCTION ccm21.upstream_segments(gid_ numeric) RETURNS setof int AS $$
         DECLARE
-        gid_riversegments gid%rowtype;
+        gid_riversegments gid%ROWTYPE;
         rec_pfafstette_length RECORD;
-        pfafstette_chain_length int;
+        rec_riversegments ccm21.riversegments%ROWTYPE;
+        -- pfafstette_chain_length int;
+        pfafstette_max_chain_length int;
         pfafstette_value int8;
         BEGIN
-        -- Valeur de la chaine pfafstette
+        -- 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.mycatchment(gid_));
+        ALTER TABLE ccm21.upstream_riversegments ADD CONSTRAINT pk_upstream_riversegments PRIMARY KEY (gid);
+
+        -- pfafstette chain length
+        --select into pfafstette_chain_length character_length(CAST(round(pfafstette) AS TEXT) 
+        --      FROM ccm21.upstream_riversegments WHERE gid=gid_;
+        -- 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.riversegments where gid=gid_;
         RAISE NOTICE 'pfafstette  is %', pfafstette_value;
-        -- Longueur de la chaine pfafstette
-        select into rec_pfafstette_length character_length(CAST(round(pfafstette) AS TEXT)) AS length 
-                FROM ccm21.riversegments WHERE gid=gid_;
-        pfafstette_chain_length=rec_pfafstette_length.length;   
-        RAISE NOTICE 'pfafstette chain length is %', pfafstette_chain_length;
-        -- loop
-        for gid_riversegments in select gid from ccm21.riversegments
-                where wso1_id in (SELECT  ccm21.mycatchment(gid_))
-                and CAST(
-                        substring(CAST(round(pfafstette) AS TEXT),1,pfafstette_chain_length)
-                as numeric)>= pfafstette_value loop
+
+        DELETE FROM ccm21.upstream_riversegments WHERE pfafstette < pfafstette_value; 
+        -- extracting the riversegments corresponding to the basin
+       
+        for gid_riversegments in select gid from ccm21.upstream_riversegments order by gid loop
                 gid_riversegments.gid=CAST(gid_riversegments.gid AS int8);
                 return next gid_riversegments.gid;