Changes between Version 13 and Version 14 of CookBook join ROE_CCMv2

Timestamp:

Nov 16, 2010 5:52:55 PM (14 years ago)

Author:

cedric

Comment:

--

CookBook join ROE_CCMv2

@@ -182 +182 @@
 {{{
 #!sql
-select count(*) from geobs2010.bdmap_ccm2; --13695 this one is with repeated projection on ccm riversegments within 300 m
-select count(*) from geobs2010.bdmap_ccm; --8884 this is without selection (first treatment) for the riversegement corresponding to the lowest proj dist
-/*
-REQUEST WITH SELECTION OF CORRECT CRITERIA FOR dist SEA, dist SOURCE, (STRAHLER RANK not taken)
-*/
-select * from (
-                select b.st_codecsp, 
-                b.dist_source as dist_source_bdcar,
-                c.dist_source_ccm/1000 as dist_source_ccm,
-                (((c.dist_source_ccm/(1000*b.dist_source))<1.3) AND
-                (c.dist_source_ccm/(1000*b.dist_source))>0.7) OR
-                ((c.dist_source_ccm/1000-b.dist_source)> -20 AND
-                (c.dist_source_ccm/1000-b.dist_source)<20) AS dist_source_ratio,
-                b.dist_sea as dist_sea_bdcar,
-                c.dist_sea_ccm/1000 as dist_sea_ccm,
-                NOT((c.dist_sea_ccm/(1000*b.dist_sea))>2 and c.dist_sea_ccm>100000) as dist_sea_ratio,
-                b.strahler as strahler_bdcar,
-                c.strahler_ccm as strahler_ccm,
-                (c.strahler_ccm-b.strahler)<=1 as strahler_diff,
-                b.distance as distproj_bdcar,
-                c.distance as distproj_ccm,
-                id_trhyd,
-                gid,
-                c.the_geom
-                from geobs2010.roe_bd_carthage b join geobs2010.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp
-                where b.dist_sea>0) as sub
-WHERE (dist_source_ratio is TRUE OR dist_source_ratio IS NULL)
-AND dist_sea_ratio is TRUE
---AND strahler_diff is TRUE; 
---9837 lines this is with repeated stations but selection has removed ~ 3000 lines
-/*
-FINAL REQUEST
-*/
-drop table if exists geobs2010.bdmap_ccm_final;
-create table geobs2010.bdmap_ccm_final as (        
-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.dist_source as dist_source_bdcar,
-                        c.dist_source_ccm/1000 as dist_source_ccm,
-                        (((c.dist_source_ccm/(1000*b.dist_source))<1.3) AND
-                        (c.dist_source_ccm/(1000*b.dist_source))>0.7) OR
-                        ((c.dist_source_ccm/1000-b.dist_source)> -20 AND
-                        (c.dist_source_ccm/1000-b.dist_source)<20) AS dist_source_ratio,
-                        b.dist_sea as dist_sea_bdcar,
-                        c.dist_sea_ccm/1000 as dist_sea_ccm,
-                        NOT((c.dist_sea_ccm/(1000*b.dist_sea))>2 and c.dist_sea_ccm>100000) as dist_sea_ratio,
-                        b.strahler as strahler_bdcar,
-                        c.strahler_ccm as strahler_ccm,
-                        (c.strahler_ccm-b.strahler)<=1 as strahler_diff,
-                        b.distance as distproj_bdcar,
-                        c.distance as distproj_ccm,
-                        id_trhyd,
-                        gid,
-                        c.the_geom
-                        from geobs2010.roe_bd_carthage b join geobs2010.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp
-                        where b.dist_sea>0
-                ) as sub
-                WHERE (dist_source_ratio is TRUE or dist_source_ratio is NULL)
-                AND dist_sea_ratio is TRUE
-                --AND strahler_diff is TRUE
-        ) as sub1 
-        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 
-) ;
 
-/*
-* ANALYSIS OF WITHDRAWN NUMBERS
-*/
-select count(*) from geobs2010.roe_bd_carthage where dist_source=0 ;-- 0
-select count(*) from geobs2010.roe_bd_carthage where dist_sea=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,
-        sum(cast((dist_source_ratio is NULL) as integer)) as sum_pb_source_NULL,
-        sum(cast(not(strahler_diff) as integer)) as sum_pb_strahler
-        from (
-                select b.st_codecsp, 
-                b.dist_source as dist_source_bdcar,
-                c.dist_source_ccm/1000 as dist_source_ccm,
-                (((c.dist_source_ccm/(1000*b.dist_source))<1.3) AND
-                (c.dist_source_ccm/(1000*b.dist_source))>0.7) OR
-                ((c.dist_source_ccm/1000-b.dist_source)> -20 AND
-                (c.dist_source_ccm/1000-b.dist_source)<20) AS dist_source_ratio,
-                b.dist_sea as dist_sea_bdcar,
-                c.dist_sea_ccm/1000 as dist_sea_ccm,
-                NOT((c.dist_sea_ccm/(1000*b.dist_sea))>2 and c.dist_sea_ccm>100000) as dist_sea_ratio,
-                b.strahler as strahler_bdcar,
-                c.strahler_ccm as strahler_ccm,
-                (c.strahler_ccm-b.strahler)<=1 as strahler_diff,
-                b.distance as distproj_bdcar,
-                c.distance as distproj_ccm,
-                id_trhyd,
-                gid,
-                c.the_geom
-                from geobs2010.roe_bd_carthage b join geobs2010.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp
-                where b.dist_sea>0
-        ) as sub; -- 12 (R=12), 1814-169 NULLS (R=2027 including zero), 457 (R=457) OK
-/*
-* TABLE TO SHOW THE RESULTS IN A MAP FOR THOSE THAT WERE NOT SELECTED TOO
-*/
+}}}
 
-drop table if exists geobs2010.bdmap_ccm_full;
-create table geobs2010.bdmap_ccm_full as (
-        select b.st_codecsp, 
-        b.dist_source as dist_source_bdcar,
-        c.dist_source_ccm/1000 as dist_source_ccm,
-        (((c.dist_source_ccm/(1000*b.dist_source))<1.3) AND
-        (c.dist_source_ccm/(1000*b.dist_source))>0.7) OR
-        ((c.dist_source_ccm/1000-b.dist_source)> -20 AND
-        (c.dist_source_ccm/1000-b.dist_source)<20) AS dist_source_ratio,
-        b.dist_sea as dist_sea_bdcar,
-        c.dist_sea_ccm/1000 as dist_sea_ccm,
-        NOT((c.dist_sea_ccm/(1000*b.dist_sea))>2 and c.dist_sea_ccm>100000) as dist_sea_ratio,
-        b.strahler as strahler_bdcar,
-        c.strahler_ccm as strahler_ccm,
-        (c.strahler_ccm-b.strahler)<=1 as strahler_diff,
-        b.distance as distproj_bdcar,
-        c.distance as distproj_ccm,
-        id_trhyd,
-        gid,
-        c.the_geom
-        from geobs2010.roe_bd_carthage b join geobs2010.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp
-        where b.dist_sea>0);
-}}}
-v0.1 There is a problem of distance source too short for bd_carthage this trouble was already seen on the first figure on this page [[BR]]
- [[Image(source:data/Docs/trac/BDMAP/stations_selection_1.jpg,700px)]] [[BR]]
-Reference for the qgis project for Cédric ''' c/eda/bdmpa/projection_bdmap.qgs '''
-En normandie, pas mal de segments manquants.. Pourquoi ? Le ratio distance source est null, l'autoriser
-Problèmes de calculs bd_carthage sur les rivières en réseau...
-
-10896 station sp2=> 8884 projetées à 300m => 6896 après projection final
-Après examen détaillé il s'agit plus de calculs de distances sources que de réels problèmes de projection.
-A vérifier sur les pb de rang de strahler....