Changes between Version 2 and Version 3 of europe.wso1


Ignore:
Timestamp:
Jun 10, 2011 4:05:42 PM (14 years ago)
Author:
celine
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • europe.wso1

    v2 v3  
    6363    ON ST_Intersects(sub.the_geom,r.the_geom); 
    6464    UPDATE europe.wso1 set area='Elbe' where area IS NULL; --18 lines 
     65}}} 
    6566 
     67{{{ 
     68---- Cas Espagne à voir !!! beaucoup trop de segments retenus dans Spain_Atl 
    6669---Spain-Atl 
    6770insert into europe.wso1(wso_id, wso1_id)  
    … …  
    7073intersect (select wso_id from ccm21.seaoutlets where area_cd='A1');  
    7174UPDATE europe.wso1 set area='Spain_Atl' where area IS NULL;  --348 lines 
    72  
    7375}}} 
    7476 
    7577{{{ 
    76 ---Sauvegarde des données 
    77 D: 
    78 cd D:\CelineJouanin\backup 
    79 C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5433 -t geobs2010.obstacle_referenciel_projccm eda2.0> geobs2010.obstacle_referenciel_projccm.sql  
     78insert into europe.wso1(wso_id, wso1_id)  
     79    select distinct on (wso_id, wso1_id)  wso_id, wso1_id from ccm21.riversegments r where wso_id='342310' 
     80        UPDATE europe.wso1 set area='SeineNormandie' where area IS NULL; 
     81insert into europe.wso1(wso_id, wso1_id)  
     82    select distinct on (wso_id, wso1_id)  wso_id, wso1_id from ccm21.riversegments r where wso_id='353376' 
     83        UPDATE europe.wso1 set area='Bretagne' where area IS NULL; 
     84delete from europe.wso where wso_id='442529' 
     85insert into europe.wso1(wso_id, wso1_id)  
     86    select distinct on (wso_id, wso1_id)  wso_id, wso1_id from ccm21.riversegments r where wso_id in ('383','2005','8639','38619'); 
     87     UPDATE europe.wso1 set area='Elbe' where area IS NULL; 
     88delete from europe.wso1 where wso_id='130439' 
     89delete from europe.wso where wso_id='129487' and area='France' 
     90delete from europe.wso where wso_id='442353' and area='France' 
     91 
     92---uga2010 
     93insert into uga2010.wso1(wso_id, wso1_id)  
     94    select distinct on (wso_id, wso1_id)  wso_id, wso1_id from ccm21.riversegments r where wso_id='353376' 
     95        UPDATE uga2010.wso1 set uga='Bretagne' where uga IS NULL; 
     96insert into uga2010.wso1(wso_id, wso1_id)  
     97    select distinct on (wso_id, wso1_id)  wso_id, wso1_id from ccm21.riversegments r where wso_id='342310' 
     98        UPDATE uga2010.wso1 set uga='SeineNormandie' where uga IS NULL; 
     99delete from uga2010.wso where wso_id='442529' 
     100delete from uga2010.wso1 where wso_id='130439' 
    80101}}} 
    81  
    82 Comparaison europe.wso et rbd_f1v3.bassin_pose 
    83 {{{ 
    84 select cum_len_sea, up_area, shree, scheid, distance_relative, c_area, distance_source, catchment_area from ccm21.riversegments r inner join europe.wso e on e.wso_id=r.wso_id where e.area='Sardinia' 
    85 select cum_len_sea, up_area, shree, scheid, distance_relative, c_area, distance_source, catchment_area from ccm21.riversegments r inner join rbd_f1v3.bassin_pose e on e.wso_id=r.wso_id where e.emu='Sardinia' 
    86 select * from rbd_f1v3.bassin_pose e where e.emu='Anglian' 
    87 select * from europe.wso w where w.area='Sardinia' 
    88  
    89 select * from europe.wso w left join rbd_f1v3.bassin_pose b on b.wso_id=w.wso_id where w.area='Elbe' 
    90 select * from rbd_f1v3.bassin_pose b right join europe.wso w on b.wso_id=w.wso_id where emu='Sardinia' 
    91 }}} 
    92  
    93 ---63 wso_id qui notés Anglian et UK dans europe.wso --> OK 
    94 {{{ 
    95 select * from rbd_f1v3.bassin_pose b right join europe.wso w on b.wso_id=w.wso_id where emu='Anglian' and area='UK' 
    96 }}} 
    97  
    98 ---6 wso_id qui doivent être rajouté dans europe.wso pour l'Elbe 
    99 {{{ 
    100 select * from rbd_f1v3.bassin_pose b right join europe.wso w on b.wso_id=w.wso_id where emu='Elbe' 
    101 }}} 
    102 {{{ 
    103 insert into europe.wso(wso_id) values ('383'),('2005'),('8639'),('38619'),('38481'),('2020'); 
    104 UPDATE europe.wso set area='Elbe' where area IS NULL; 
    105 }}} 
    106  
    107 Problème : 
    108 ||Nb lignes dans||riversegments_uga avec europe.wso||CCM sous R uga2010.wso|| 
    109 ||Bretagne||2115||2035|| 
    110 ||Corse||4839||4836|| 
    111 ||Garonne||13219||13223|| 
    112 ||RMC||91635||91548|| 
    113 ||!SeineNormandie||7415||7413|| 
    114 {{{ 
    115 select * from uga2010.wso b left join europe.wso w on b.wso_id=w.wso_id where uga='SeineNormandie' 
    116 select count(b.wso_id), b.wso_id from uga2010.wso b left join europe.wso w on b.wso_id=w.wso_id where uga='SeineNormandie' group by b.wso_id order by count(b.wso_id) 
    117 }}} 
    118 ||!SeineNormandie||332670, 337665||A mettre dans area France|| 
    119  
    120 {{{ 
    121 ---SeineNormandie 
    122 insert into europe.wso(wso_id) values ('332670'),('337665'),('342310'); 
    123 UPDATE europe.wso set area='France' where area IS NULL; 
    124 ---Corse 
    125 insert into europe.wso(wso_id) values ('263289'),('261207'),('261251'); 
    126 UPDATE europe.wso set area='France' where area IS NULL; 
    127 ---RMC 
    128 insert into europe.wso(wso_id) values ('238201'),('434444'),('434159'),('433693'),('432880'),('432326'),('433584'),('434407'),('440607'),('432933'); 
    129 UPDATE europe.wso set area='France' where area IS NULL; 
    130 ---Garonne 
    131 insert into europe.wso(wso_id) values ('394204'); 
    132 UPDATE europe.wso set area='France' where area IS NULL; 
    133 ---Bretagne 
    134 insert into europe.wso(wso_id) values ('353376'),('352555'); 
    135 UPDATE europe.wso set area='France' where area IS NULL; 
    136 }}} 
    137  
    138 {{{ 
    139 delete from europe.wso where wso_id='291126' and area='Spain'; 
    140 delete from europe.wso where wso_id='442494' and area='Spain'; 
    141 delete from europe.wso where wso_id='445532' and area='Spain'; 
    142 delete from europe.wso where wso_id='510766' and area='Spain'; 
    143 delete from europe.wso where wso_id='442508' and area='Spain'; 
    144 delete from europe.wso where wso_id='129681' and area='Italy'; 
    145 delete from europe.wso where wso_id='129487' and area='RhoneMediterranee'; 
    146 delete from europe.wso where wso_id='442353' and area='RhoneMediterranee'; 
    147 insert into europe.wso(wso_id) values ('442529'); 
    148 UPDATE europe.wso set area='RhoneMediterranee' where area IS NULL; 
    149 insert into uga2010.wso(wso_id) values ('442529'); 
    150 UPDATE uga2010.wso set uga='RhoneMediterranee' where uga IS NULL; 
    151 delete from uga2010.wso where wso_id='129487' and uga='RhoneMediterranee'; 
    152 delete from europe.wso where wso_id='291112' and area='Italy'; 
    153 delete from europe.wso where wso_id='442529' and area='Spain'; 
    154 delete from uga2010.wso where wso_id='130439' and uga='RhoneMediterranee'; 
    155 delete from europe.wso where wso_id='130439' and area='France'; 
    156 delete from europe.wso where wso_id='130439' and area='RhoneMediterranee'; 
    157 insert into europe.wso(wso_id) values ('130439'); 
    158 UPDATE europe.wso set area='Italy' where area IS NULL; 
    159 delete from uga2010.wso where wso_id='442353' and uga='RhoneMediterranee'; 
    160 }}}