Changes between Version 31 and Version 32 of CCM2 download and load

Timestamp:

Apr 2, 2010 9:22:36 PM (15 years ago)

Author:

cedric

Comment:

--

CCM2 download and load

@@ -131 +131 @@
 ALTER TABLE lakes SET SCHEMA ccm21;
 }}}
+== 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é
+}}}