Changes between Version 71 and Version 72 of CookBook join BDMAP_CCM v2

Timestamp:

Nov 11, 2010 8:36:42 AM (15 years ago)

Author:

cedric

Comment:

--

CookBook join BDMAP_CCM v2

@@ -257 +257 @@
 REQUEST WITH SELECTION OF CORRECT CRITERIA FOR DISTANCE SEA, DISTANCE SOURCE, STRAHLER RANK
 */
-drop table if exists bd_map.bdmap_ccm_full;
-create table bd_map.bdmap_ccm_full as ( 
+
 select * from (
         select b.st_codecsp, 
@@ -280 +279 @@
 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
+AND strahler_diff is TRUE; --10783 lines this is with repeated stations but selection has removed ~ 3000 lines
 /*
 FINAL REQUEST
@@ -357 +356 @@
                 and b.distance_mer>0
         ) as sub; -- 15 (R=15), 2234 (R=2279 including zero), 490 (R=517) OK
-
-
-}}}
-
+/*
+* TABLE TO SHOW THE RESULTS IN A MAP FOR THOSE THAT WERE NOT SELECTED TOO
+*/
+
+drop table if exists bd_map.bdmap_ccm_full;
+create table bd_map.bdmap_ccm_full as (
+                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 
+                and b.distance_mer>0
+);
+}}}
+