Changes between Version 31 and Version 32 of CCM

Timestamp:

Apr 2, 2010 9:21:55 PM (15 years ago)

Author:

cedric

Comment:

--

CCM

@@ -110 +110 @@
 
 [[Image(source:data/Docs/trac/Longueur des tronçons CCM par bassin.jpeg,450px)]]
-== How to explore the ccm database ==
 
-which segments are upstream from the current segment ?
-
-{{{
--- Vilaine
---- creating a second index to accelerate the requests
-CREATE INDEX indexriversegments_wso_id
-ON ccm21.riversegments
-  USING btree
-  (wso_id);
-
-select * from ccm21.riversegments where wso_id=291146;
-CREATE INDEX indexcatchments_wso_id
-ON ccm21.catchments
-  USING btree
-  (wso_id);
-select * from ccm21.catchments where wso_id=291146 and wso3_id=291828 ;
--- extraction du chiffre immediatement à gauche du segment selectionné
-select * from ccm21.riversegments where wso_id=291146 order by cum_len desc;
-select round(pfafstette) from ccm21.riversegments where wso_id=291146 order by cum_len desc;
-select CAST(round(pfafstette) AS TEXT) from ccm21.riversegments where wso_id=291146 order by cum_len desc;
-select substring(CAST(round(pfafstette) AS TEXT),2,2) from ccm21.riversegments where wso_id=291146 order by cum_len desc;
-
-}}}
-
-
-{{{
-Create an index on all wso1_id
-CREATE INDEX indexriversegments_wso1_id
-ON ccm21.riversegments
-  USING btree
-  (wso1_id);
-}}}
-
-{{{
-Create an index on all wso1..._id
-CREATE INDEX indexcatchments_wso1_id
-ON ccm21.catchments
-  USING btree
-  (wso1_id);
-}}}
-
-{{{
--- segment mer vilaine
-select * from ccm21.riversegments where gid=234706;
--- le rang de strahler est 6
--- recupération de l'identifiant du bv correspondant
-select wso6_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=234706;
--- 291146
-select wso1_id from ccm21.catchments c  where wso6_id = 291146;
--- le m^ en plus compliqué
-select wso1_id from ccm21.catchments c  where wso6_id = 
-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=234706);
--- liste de wso1_id
--- extraction des segments contenus dans ces bassins
-select * from ccm21.riversegments where wso1_id in 
-(select wso1_id from ccm21.catchments c  where wso6_id = 291146) order by cum_len desc;
--- le m^ en plus compliqué
-select * from ccm21.riversegments where wso1_id 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=234706)
-) ;
--- avec un cas adapté à chaque rang de strahler (marche pas)
-
-select CASE     WHEN r.strahler=5 THEN (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=234706)) 
-                WHEN r.strahler=6 THEN (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=234706))
-                ELSE 0
-        END AS wso1_id
-        FROM ccm21.riversegments r
-        join ccm21.catchments c on r.wso1_id=c.wso1_id 
-        where r.gid=234706;             
-        
--- marche pas 
-
-
-
--- longueur de la cle pfstette
-select character_length(CAST(round(pfafstette) AS TEXT)) from ccm21.riversegments where gid=234706; --1
--- selection de tous les noeuds de la Vilaine en amont du noeud choisi
-select gid from ccm21.riversegments
-        where wso_id=291146 
-        and     CAST(substring(CAST(round(pfafstette) AS TEXT),
-                                1,
-                                1 -- voir ci dessus pour le calcul
-                                ) as numeric    )       
-        
-        >1              ;
--- le même en plus compliqué
-select gid from ccm21.riversegments
-        where wso_id=291146 
-        and     CAST(substring(CAST(round(pfafstette) AS TEXT),
-                                1,
-                                1 -- voir ci dessus pour le calcul
-                                ) as numeric    )       
-        
-        > (select pfafstette FROM ccm21.riversegments where gid=234706);
-
--- le même en plus compliqué
-select gid from ccm21.riversegments
-        where wso_id=291146 
-        and     CAST(substring(CAST(round(pfafstette) AS TEXT),
-                                1,
-                                (select character_length(CAST(round(pfafstette) AS TEXT)) from ccm21.riversegments where gid=234706)
-                                ) as numeric    )       
-        
-        > (select pfafstette FROM ccm21.riversegments where gid=234706);
-select max (strahler) from ccm21.riversegments -- 10
--- le même en plus compliqué
-select gid from ccm21.riversegments
-        where wso1_id 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=234706)
-        ) 
-        and     CAST(substring(CAST(round(pfafstette) AS TEXT),
-                                1,
-                                (select character_length(CAST(round(pfafstette) AS TEXT)) from ccm21.riversegments where gid=234706)
-                                ) as numeric    )       
-        
-        > (select pfafstette FROM ccm21.riversegments where gid=234706);
-
--- le même en plus compliqué
-}}}