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

Timestamp:

Apr 3, 2010 10:04:02 PM (15 years ago)

Author:

cedric

Comment:

--

CCM2 download and load

@@ -200 +200 @@
 http://www.postgresql.org/docs/8.4/static/plpgsql.html
 
-=== Plsql function which returns the id of the primary catchments within the catchment corresponding to the strahler order ===
+=== plpgsql 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
@@ -360 +360 @@
 }}}
 {{{
---examples for use
+--examples of use
 
 SELECT  ccm21.pricatch_from_catch(234706); --la vilaine dans son ensemble
@@ -370 +370 @@
 SELECT * from ccm21.catchments where wso1_id in (SELECT  ccm21.pricatch_from_catch(234706));
 }}}
-
+=== Plgsql function which returns the riversegments upstream from the riversegment selected ===
+{{{
+DROP TYPE IF EXISTS gid;
+CREATE TYPE gid as (gid int);
+DROP  FUNCTION IF EXISTS ccm21.upstream_segments(gid_ numeric);
+CREATE OR REPLACE FUNCTION ccm21.upstream_segments(gid_ numeric) RETURNS setof int AS $$
+        DECLARE
+        gid_riversegments gid%rowtype;
+        rec_pfafstette_length RECORD;
+        pfafstette_chain_length int;
+        pfafstette_value int8;
+        BEGIN
+        -- Valeur de la chaine pfafstette
+        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.pricatch_from_catch(gid_))
+                and CAST(
+                        substring(CAST(round(pfafstette) AS TEXT),1,pfafstette_chain_length)
+                as numeric)> pfafstette_value loop
+                gid_riversegments.gid=CAST(gid_riversegments.gid AS int8);
+                return next gid_riversegments.gid;
+                end loop;
+                return; 
+        END;
+$$
+LANGUAGE 'plpgsql' ;
+COMMENT ON FUNCTION ccm21.upstream_segments(gid_ numeric) IS 'This function uses ccm21.pricatch_from_catch 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';
+}}}
+Usage example
+{{{
+select ccm21.upstream_segments(234706);
+}}}