Changes between Version 66 and Version 67 of CookBook join BDMAP_CCM v2

Timestamp:

Nov 10, 2010 10:21:14 PM (14 years ago)

Author:

cedric

Comment:

--

CookBook join BDMAP_CCM v2

@@ -250 +250 @@
 {{{
 #!sql
+select count(*) from bd_map.bdmap_ccm2; --13695 this one is with repeated projection on ccm riversegments within 300 m
+select count(*) from bd_map.bdmap_ccm; --8884 this is without selection (first treatment) for the riversegement corresponding to the lowest proj distance
+/*
+REQUEST WITH SELECTION OF CORRECT CRITERIA FOR DISTANCE SEA, DISTANCE SOURCE, STRAHLER RANK
+*/
+select * from (
         select b.st_codecsp, 
         b.distance_source as dist_source_bdcar,
@@ -267 +273 @@
         from bd_map.bd_map_bd_carthage b join bd_map.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp
         where b.distance_source >0 -- to avoid division by zero
-        and b.distance_mer>0) as sub;
+        and b.distance_mer>0) as sub
+WHERE dist_source_ratio is TRUE
+AND dist_sea_ratio is TRUE
+AND strahler_diff is TRUE --10783 lines this is with repeated stations but selection has removed ~ 3000 lines
 /*
-* ANALYSE DES EFFECTIFS ECARTES
+FINAL REQUEST
+*/
+select distinct on(st_codecsp) st_codecsp,
+        gid,
+        id_trhyd,
+        dist_source_bdcar,
+        dist_source_ccm,
+        dist_sea_bdcar,
+        dist_sea_ccm,
+        strahler_bdcar,
+        strahler_ccm,
+        distproj_bdcar,
+        min(distproj_ccm) as distproj_ccm,
+        the_geom
+        from( select *  
+                from (
+                        select b.st_codecsp, 
+                        b.distance_source as dist_source_bdcar,
+                        c.distance_source/1000 as dist_source_ccm,
+                        (c.distance_source/(1000*b.distance_source))<=2 as dist_source_ratio,
+                        b.distance_mer as dist_sea_bdcar,
+                        c.cum_len_sea/1000 as dist_sea_ccm,
+                        NOT((c.cum_len_sea/(1000*b.distance_mer))>2 and c.cum_len_sea>100000) as dist_sea_ratio,
+                        b.strahler as strahler_bdcar,
+                        c.strahlerccm as strahler_ccm,
+                        (c.strahlerccm-b.strahler)<=1 as strahler_diff,
+                        b.distance as distproj_bdcar,
+                        c.distance as distproj_ccm,
+                        id_trhyd,
+                        gid,
+                        c.the_geom
+                        from bd_map.bd_map_bd_carthage b join bd_map.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp
+                        where b.distance_source >0 -- to avoid division by zero
+                        and b.distance_mer>0
+                ) as sub
+                WHERE dist_source_ratio is TRUE
+                AND dist_sea_ratio is TRUE
+                AND strahler_diff is TRUE
+        ) as sub1 --10783 lines
+        group by st_codecsp, gid,id_trhyd,dist_source_bdcar,dist_source_ccm,dist_sea_bdcar,dist_sea_ccm,strahler_bdcar,strahler_ccm,distproj_bdcar,     distproj_ccm,
+        the_geom
+        order by st_codecsp; --7036 8884-7036=1848 stations removed
+
+/*
+* ANALYSIS OF WITHDRAWN NUMBERS
 */
 select count(*) from bd_map.bd_map_bd_carthage where distance_source=0 ;-- 42
 select count(*) from bd_map.bd_map_bd_carthage where distance_mer=0; -- 5
+-- voir comment on gère ces cas particuliers par la suite....
 select sum(cast(not(dist_sea_ratio) as integer)) as sum_pb_sea,
         sum(cast(not(dist_source_ratio) as integer)) as sum_pb_source,
@@ -296 +350 @@
                 and b.distance_mer>0
         ) as sub; -- 15 (R=15), 2234 (R=2279 including zero), 490 (R=517) OK
--- voir comment on gère ces cas particuliers par la suite....
-}}}
-
+
+
+}}}
+