Changes between Version 4 and Version 5 of CookBook join ROE_CCMv2


Ignore:
Timestamp:
Nov 15, 2010 4:30:10 PM (14 years ago)
Author:
cedric
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • CookBook join ROE_CCMv2

    v4 v5  
    3232Some stations were missing... 
    3333  
    34  * first create two index to speed up queries [[BR]] 
    35 bdcarthage  
     34 * first create two index to speed up queries 
     35{{{ 
     36C:\"Program Files"\PostgreSQL\8.4\bin\psql -d bd_carthage -p 5433 -U postgres -c"CREATE INDEX indexobstacle_referentiel ON obstacle_referentiel USING GIST(ref_position_etrs89 GIST_GEOMETRY_OPS); 
     37}}} 
    3638 
    3739{{{ 
    3840#!sql 
    39 CREATE INDEX indexhylcov_arc ON hylcov_arc 
    40   USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
    41 CREATE INDEX indexStationsp2 ON stationsp2 
    42   USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
    43 }}} 
    44  
    45  
    46 Joining the hylcovarc and stationsp2, the_geom is that of stationsp2, first version at source:trunk/EDAcommun/sql/bd_map_bd_carthage_version1.sql [[BR]] 
    47 Script correspond to trial 3 above is written below 
    48 One big error solved ticket #72 
    49 {{{ 
    50 #!sql 
    51 CREATE INDEX indexhylcov_arc_dist2 ON hylcov_arc_dist2 
    52   USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
     41  -- Here we project dams on the bd_carthage 
     42  -- The result is stored in table roe_bd_carthage  
     43  -- I'm changing ref_position_etrs89 => the_geom 
     44 
    5345   
    54   -- creation de la table bd_map_bd_carthage la jointure entre la bd_carthage et la table des distances par Cédric est déjà faite dans un left join. 
    55   -- Ici on projette les stations de pêche sur la bd_carthage 
    56    
    57 DROP TABLE IF EXISTS bd_map_bd_carthage; 
    58 CREATE TABLE bd_map_bd_carthage as ( 
    59      SELECT distinct on (st_codecsp) st_codecsp, dist_source,dist_sea,strahler,id_trhyd, min(distance) as distance, the_geom FROM ( 
    60                SELECT st_codecsp, n.distance_mer as dist_sea,n.strahler ,d.id_trhyd ,d.dist_source_max as dist_source, CAST(distance(d.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
    61                FROM stationsp2 As s 
    62                INNER JOIN hylcov_arc_dist2 d ON ST_DWithin(d.the_geom, s.the_geom,300) 
     46DROP TABLE IF EXISTS roe_bd_carthage; 
     47CREATE TABLE roe_bd_carthage as ( 
     48     SELECT distinct on (ref_id) ref_id, dist_source,dist_sea,strahler,id_trhyd, min(distance) as distance, the_geom FROM ( 
     49               SELECT ob.ref_id, n.distance_mer as dist_sea,n.strahler ,d.id_trhyd ,d.dist_source_max as dist_source, CAST(distance(d.the_geom, ob.ref_position_etrs89) as  decimal(15,1)) as distance,ob.ref_position_etrs89 as the_geom 
     50               FROM obstacle_referentiel As ob 
     51               INNER JOIN hylcov_arc_dist2 d ON ST_DWithin(d.the_geom, ob.the_geom,300) 
    6352                LEFT JOIN noeud_troncon_final n ON d.id_som_f = n.id_bdcarthage -- pour récupérer les strahler 
    64                WHERE s.the_geom IS NOT NULL 
    65                ORDER BY st_codecsp) as sub 
    66         GROUP BY st_codecsp, distance, dist_source,dist_sea,strahler, id_trhyd, the_geom 
     53               ORDER BY ob.ref_id) as sub 
     54        GROUP BY ref_id, distance, dist_source,dist_sea,strahler, id_trhyd, the_geom 
    6755 ); 
    68 alter table  bd_map_bd_carthage add column id serial; 
     56alter table  roe_bd_carthage add column id serial; 
    6957-- mise à jour de la table geometry_columns 
    7058INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") 
    71 SELECT '', 'public', 'bd_map_bd_carthage', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom) 
    72 FROM bd_map_bd_carthage LIMIT 1; 
     59SELECT '', 'public', 'roe_bd_carthage', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom) 
     60FROM roe_bd_carthage LIMIT 1; 
    7361 
    7462-- creation d'index, clé primaire, et constraintes qui vont bien 
    75 alter table bd_map_bd_carthage add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); 
    76 alter table bd_map_bd_carthage add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL); 
    77 alter table bd_map_bd_carthage add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); 
    78 alter table bd_map_bd_carthage ADD CONSTRAINT pk_id PRIMARY KEY(id); 
    79 CREATE INDEX indexbd_map_bd_carthage ON bd_map_bd_carthage 
     63alter table roe_bd_carthage add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); 
     64alter table roe_bd_carthage add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL); 
     65alter table roe_bd_carthage add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); 
     66alter table roe_bd_carthage ADD CONSTRAINT pk_id_roe_bd_carthage PRIMARY KEY(id); 
     67CREATE INDEX indexroe_bd_carthage ON roe_bd_carthage 
    8068  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
    8169}}} 
    … …  
    8573{{{ 
    8674cd C:\eda\backup 
    87 C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5433 -t bd_map_bd_carthage bd_carthage> bd_map_bd_carthage.sql 
    88 C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -U postgres -c "drop table if exists bd_map.bd_map_bd_carthage" 
    89 C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -U postgres -f bd_map_bd_carthage.sql 
    90 C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -U postgres -c "alter table bd_map_bd_carthage drop CONSTRAINT  pk_id;" 
    91 C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -U postgres -c "alter table bd_map_bd_carthage ADD CONSTRAINT pk_id_bd_map_bd_carthage PRIMARY KEY(id); 
    92 C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -U postgres -c "alter table bd_map_bd_carthage set schema bd_map" 
     75C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5433 -t roe_bd_carthage bd_carthage> roe_bd_carthage.sql 
     76C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -U postgres -c "drop table if exists geobs2010.roe_bd_carthage" 
     77C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -U postgres -f roe_bd_carthage.sql 
     78C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -U postgres -c "alter table roe_bd_carthage set schema geobs2010" 
    9379}}} 
    9480 
    9581== 4. define criteria helping to do the projection == 
    9682 
    97 Below this table does not only select minimum distance but all ccm segments which are within a 300 m distance from the electrofishing station. [[BR]] 
    98   
     83-- the table geobs2010.roe_ccm_300 was created in  ["CookBook join ROE_CCM"] but we need to add some columns 
     84-- launch in eda2.0 
     85{{{ 
     86DROP TABLE IF EXISTS geobs2010.roe_ccm_300_2; 
     87CREATE TABLE geobs2010.roe_ccm_300_2 as ( 
     88               SELECT ref_id, r.cum_len_sea as dist_sea_ccm, r.distance_source as dist_source_ccm, r.strahler as strahler_ccm,gid ,CAST(distance(r.the_geom, s.ref_position_etrs89) as  decimal(15,1)) as distance,s.ref_position_etrs89  
     89               FROM geobs2010.obstacle_referentiel As s 
     90               INNER JOIN  ccm21.riversegments r ON ST_DWithin(r.the_geom, s.ref_position_etrs89,300) 
     91               ORDER BY  ref_id 
     92); 
     93alter table geobs2010.roe_ccm_300_2 add column id serial; 
     94}}} 
     95 
     96 
     97The table from the request joining geobs2010.roe_bd_carthage with the geobs2010.roe_ccm_300 is exported there (use .csv exort to C:\Documents and Settings\cedric\Mes documents\Migrateur\programmes\workspace3.5\EDAdata\dataEDAccm\export_jointure_roe_ccm.csv 
    9998{{{ 
    10099#!sql 
    101 -- launch in eda2.0 
    102 DROP TABLE IF EXISTS bd_map.bdmap_ccm2; 
    103 CREATE TABLE bd_map.bdmap_ccm2 as ( 
    104                SELECT st_codecsp, r.cum_len_sea as dist_sea_ccm, r.distance_source as dist_source_ccm, r.strahler as strahler_ccm,gid ,CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom  
    105                FROM bd_map.stationsp2 As s 
    106                INNER JOIN  ccm21.riversegments r ON ST_DWithin(r.the_geom, s.the_geom,300) 
    107                WHERE s.the_geom IS NOT NULL 
    108                ORDER BY st_codecsp  
    109  
    110 ); 
    111 alter table bd_map.bdmap_ccm2 add column id serial; 
    112 }}} 
    113  
    114 The table from the request joining bd_map.bd_map_bd_carthage with the bd_map.bdmap_ccm2 is exported there (use .csv exort to C:\Documents and Settings\cedric\Mes documents\Migrateur\programmes\workspace3.5\EDAdata\dataEDAccm\export_jointure_bdcarthage_ccm.csv 
    115 {{{ 
    116 #!sql 
    117 select * from bd_map.bd_map_bd_carthage b join bd_map.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp; 
     100select * from geobs2010.roe_bd_carthage b join geobs2010.roe_ccm_300 c on b.st_codecsp=c.st_codecsp; 
    118101}}} 
    119102source:data/dataEDAccm/export_jointure_bdcarthage_ccm.csv [[BR]] 
    … …  
    239222[[Image(source:data/Docs/trac/BDMAP/comp_dist_source_2.jpeg,300px)]]  
    240223[[Image(source:data/Docs/trac/BDMAP/percentage_out1.jpeg,300px)]]  
    241 == 5. query to select the final data from  bd_map.bdmap_ccm2 == 
     224== 5. query to select the final data from  geobs2010.bdmap_ccm2 == 
    242225{{{ 
    243226#!sql 
    244 select count(*) from bd_map.bdmap_ccm2; --13695 this one is with repeated projection on ccm riversegments within 300 m 
    245 select count(*) from bd_map.bdmap_ccm; --8884 this is without selection (first treatment) for the riversegement corresponding to the lowest proj dist 
     227select count(*) from geobs2010.bdmap_ccm2; --13695 this one is with repeated projection on ccm riversegments within 300 m 
     228select count(*) from geobs2010.bdmap_ccm; --8884 this is without selection (first treatment) for the riversegement corresponding to the lowest proj dist 
    246229/* 
    247230REQUEST WITH SELECTION OF CORRECT CRITERIA FOR dist SEA, dist SOURCE, (STRAHLER RANK not taken) 
    … …  
    266249                gid, 
    267250                c.the_geom 
    268                 from bd_map.bd_map_bd_carthage b join bd_map.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp 
     251                from geobs2010.roe_bd_carthage b join geobs2010.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp 
    269252                where b.dist_sea>0) as sub 
    270253WHERE (dist_source_ratio is TRUE OR dist_source_ratio IS NULL) 
    … …  
    275258FINAL REQUEST 
    276259*/ 
    277 drop table if exists bd_map.bdmap_ccm_final; 
    278 create table bd_map.bdmap_ccm_final as (         
     260drop table if exists geobs2010.bdmap_ccm_final; 
     261create table geobs2010.bdmap_ccm_final as (         
    279262select distinct on(st_codecsp) st_codecsp, 
    280263        gid, 
    … …  
    309292                        gid, 
    310293                        c.the_geom 
    311                         from bd_map.bd_map_bd_carthage b join bd_map.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp 
     294                        from geobs2010.roe_bd_carthage b join geobs2010.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp 
    312295                        where b.dist_sea>0 
    313296                ) as sub 
    … …  
    324307* ANALYSIS OF WITHDRAWN NUMBERS 
    325308*/ 
    326 select count(*) from bd_map.bd_map_bd_carthage where dist_source=0 ;-- 0 
    327 select count(*) from bd_map.bd_map_bd_carthage where dist_sea=0; -- 5 
     309select count(*) from geobs2010.roe_bd_carthage where dist_source=0 ;-- 0 
     310select count(*) from geobs2010.roe_bd_carthage where dist_sea=0; -- 5 
    328311-- voir comment on gère ces cas particuliers par la suite.... 
    329312select sum(cast(not(dist_sea_ratio) as integer)) as sum_pb_sea, 
    … …  
    350333                gid, 
    351334                c.the_geom 
    352                 from bd_map.bd_map_bd_carthage b join bd_map.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp 
     335                from geobs2010.roe_bd_carthage b join geobs2010.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp 
    353336                where b.dist_sea>0 
    354337        ) as sub; -- 12 (R=12), 1814-169 NULLS (R=2027 including zero), 457 (R=457) OK 
    … …  
    357340*/ 
    358341 
    359 drop table if exists bd_map.bdmap_ccm_full; 
    360 create table bd_map.bdmap_ccm_full as ( 
     342drop table if exists geobs2010.bdmap_ccm_full; 
     343create table geobs2010.bdmap_ccm_full as ( 
    361344        select b.st_codecsp,  
    362345        b.dist_source as dist_source_bdcar, 
    … …  
    377360        gid, 
    378361        c.the_geom 
    379         from bd_map.bd_map_bd_carthage b join bd_map.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp 
     362        from geobs2010.roe_bd_carthage b join geobs2010.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp 
    380363        where b.dist_sea>0); 
    381364}}}