Changes between Version 2 and Version 3 of CookBook join Corine Land Cover-RHT


Ignore:
Timestamp:
Sep 23, 2011 5:28:38 PM (14 years ago)
Author:
celine
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • CookBook join Corine Land Cover-RHT

    v2 v3  
    1313 
    1414{{{ 
    15 create schema clc2000; 
    16 alter table ag_clc set schema clc2000; 
    17  
    18  
    19 create schema clc2000; 
    20 alter table ag_clc set schema clc2000; 
    21 ALTER TABLE clc.ag_clc RENAME COLUMN "Reach Number" TO  
    22 ALTER TABLE clc.ag_clc RENAME COLUMN "Catchment_area(m2)" TO  
    23 ALTER TABLE clc.ag_clc RENAME COLUMN "111" TO art_111 
    24 ALTER TABLE clc.ag_clc RENAME COLUMN "112" TO art_112 
    25 ALTER TABLE clc.ag_clc RENAME COLUMN "121" TO art_121 
    26 ALTER TABLE clc.ag_clc RENAME COLUMN "122" TO art_122 
    27 ALTER TABLE clc.ag_clc RENAME COLUMN "123" TO art_123 
    28 ALTER TABLE clc.ag_clc RENAME COLUMN "131" TO art_131 
    29 ALTER TABLE clc.ag_clc RENAME COLUMN "132" TO art_132 
    30 ALTER TABLE clc.ag_clc RENAME COLUMN "133" TO art_133 
    31 ALTER TABLE clc.ag_clc RENAME COLUMN "141" TO art_141 
    32 ALTER TABLE clc.ag_clc RENAME COLUMN "142" TO art_142 
    33 ALTER TABLE clc.ag_clc RENAME COLUMN "211" TO arable_211 
    34 ALTER TABLE clc.ag_clc RENAME COLUMN "212" TO arable_212 
    35 ALTER TABLE clc.ag_clc RENAME COLUMN "213" TO arable_213 
    36 ALTER TABLE clc.ag_clc RENAME COLUMN "221" TO permcrop_221 
    37 ALTER TABLE clc.ag_clc RENAME COLUMN "222" TO permcrop_222 
    38 ALTER TABLE clc.ag_clc RENAME COLUMN "223" TO permcrop_223 
    39 ALTER TABLE clc.ag_clc RENAME COLUMN "231" TO pasture_231 
    40 ALTER TABLE clc.ag_clc RENAME COLUMN "241" TO hetagr_242 
    41 ALTER TABLE clc.ag_clc RENAME COLUMN "242" TO hetagr_243 
    42 ALTER TABLE clc.ag_clc RENAME COLUMN "244" TO hetagr_244 
    43 ALTER TABLE clc.ag_clc RENAME COLUMN "311" TO forest_311 
    44 ALTER TABLE clc.ag_clc RENAME COLUMN "312" TO forest_312 
    45 ALTER TABLE clc.ag_clc RENAME COLUMN "313" TO forest_313 
    46 ALTER TABLE clc.ag_clc RENAME COLUMN "321" TO natural_321 
    47 ALTER TABLE clc.ag_clc RENAME COLUMN "322" TO natural_322 
    48 ALTER TABLE clc.ag_clc RENAME COLUMN "323" TO natural_323 
    49 ALTER TABLE clc.ag_clc RENAME COLUMN "324" TO natural_324 
    50 ALTER TABLE clc.ag_clc RENAME COLUMN "331" TO natural_331 
    51 ALTER TABLE clc.ag_clc RENAME COLUMN "332" TO natural_332 
    52 ALTER TABLE clc.ag_clc RENAME COLUMN "333" TO natural_333 
    53 ALTER TABLE clc.ag_clc RENAME COLUMN "334" TO natural_334 
    54 ALTER TABLE clc.ag_clc RENAME COLUMN "335" TO natural_335 
    55 ALTER TABLE clc.ag_clc RENAME COLUMN "411" TO wetlands_411 
    56 ALTER TABLE clc.ag_clc RENAME COLUMN "412" TO wetlands_412 
    57 ALTER TABLE clc.ag_clc RENAME COLUMN "421" TO wetlands_421 
    58 ALTER TABLE clc.ag_clc RENAME COLUMN "422" TO wetlands_422 
    59 ALTER TABLE clc.ag_clc RENAME COLUMN "423" TO wetlands_423 
    60 ALTER TABLE clc.ag_clc RENAME COLUMN "511" TO inwat_511 
    61 ALTER TABLE clc.ag_clc RENAME COLUMN "512" TO inwat_512 
    62 ALTER TABLE clc.ag_clc RENAME COLUMN "521" TO marwat_521 
    63 ALTER TABLE clc.ag_clc RENAME COLUMN "522" TO marwat_522 
    64 ALTER TABLE clc.ag_clc RENAME COLUMN "523" TO marwat_523 
    65  
    66 --- le copy ne fonctionne pas, problème dans le fichier 
    67  
    6815create schema clc2000; 
    6916drop table if exists clc2000.area_clc; 
    … …  
    11764  marwat_523 double precision 
    11865); 
     66alter table clc2000.area_clc ADD CONSTRAINT pk_idrnum PRIMARY KEY(Reach_Number); 
    11967copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/ag_clc.csv' with csv delimiter as ';' header;  
     68copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/ap_clc.csv' with csv delimiter as ';' header;  
     69copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/bret_clc.csv' with csv delimiter as ';' header;  
     70copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/cors_clc.csv' with csv delimiter as ';' header;  
     71copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/loir_clc.csv' with csv delimiter as ';' header;  
     72copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/rhon_clc.csv' with csv delimiter as ';' header;  
     73copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/rhow_clc.csv' with csv delimiter as ';' header;  
     74copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/rm_clc.csv' with csv delimiter as ';' header;  
     75copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/sn_clc.csv' with csv delimiter as ';' header;  
     76 
     77select count(*) from clc2000.area_clc ---114728 
     78select count(*) from rht.rht  ---114601 
     79 
     80drop table if exists clc2000.uparea_clc; 
     81CREATE TABLE clc2000.uparea_clc 
     82( 
     83  Reach_Number integer, 
     84  up_area double precision, 
     85  art_111 double precision, 
     86  art_112 double precision, 
     87  art_121 double precision, 
     88  art_122 double precision, 
     89  art_123 double precision, 
     90  art_124 double precision, 
     91  art_131 double precision, 
     92  art_132 double precision, 
     93  art_133 double precision, 
     94  art_141 double precision, 
     95  art_142 double precision, 
     96  arable_211 double precision, 
     97  arable_212 double precision, 
     98  arable_213 double precision, 
     99  permcrop_221 double precision, 
     100  permcrop_222 double precision, 
     101  permcrop_223 double precision, 
     102  pasture_231 double precision, 
     103  hetagr_241 double precision, 
     104  hetagr_242 double precision, 
     105  hetagr_243 double precision, 
     106  hetagr_244 double precision, 
     107  forest_311 double precision, 
     108  forest_312 double precision, 
     109  forest_313 double precision, 
     110  natural_321 double precision, 
     111  natural_322 double precision, 
     112  natural_323 double precision, 
     113  natural_324 double precision, 
     114  natural_331 double precision, 
     115  natural_332 double precision, 
     116  natural_333 double precision, 
     117  natural_334 double precision, 
     118  natural_335 double precision, 
     119  wetlands_411 double precision, 
     120  wetlands_412 double precision, 
     121  wetlands_421 double precision, 
     122  wetlands_422 double precision, 
     123  wetlands_423 double precision, 
     124  inwat_511 double precision, 
     125  inwat_512 double precision, 
     126  marwat_521 double precision, 
     127  marwat_522 double precision, 
     128  marwat_523 double precision 
     129); 
     130alter table clc2000.uparea_clc ADD CONSTRAINT pk_idnum PRIMARY KEY(Reach_Number); 
     131copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/ag_clcacc.csv' with csv delimiter as ';' header;  
     132copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/ap_clcacc.csv' with csv delimiter as ';' header;  
     133copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/bret_clcacc.csv' with csv delimiter as ';' header;  
     134copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/cors_clcacc.csv' with csv delimiter as ';' header;  
     135copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/loir_clcacc.csv' with csv delimiter as ';' header;  
     136copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/rhon_clcacc.csv' with csv delimiter as ';' header;  
     137copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/rhow_clcacc.csv' with csv delimiter as ';' header;  
     138copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/rm_clcacc.csv' with csv delimiter as ';' header;  
     139copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/sn_clcacc.csv' with csv delimiter as ';' header;  
     140 
     141select count(*) from clc2000.uparea_clc  ---86992 (sans loire) 
     142select count(*) from clc2000.uparea_clc  ---114599 (avec loire) manque id_drain=223988 
     143 
     144---pb avec loir_clcacc 
     145---ERREUR:  la valeur d'une clé dupliquée rompt la contrainte unique « pk_idnum » 
     146---CONTEXT:  COPY uparea_clc, ligne 27607 : « 223988;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0 » 
     147 
     148select * from clc2000.uparea_clc where Reach_Number='223988'; 
     149select * from clc2000.uparea_clc where up_area is null; 
     150 
     151select * from rht.attributs_rht_fev_2011_vs2 where id_drain='223988'   --- sfbvu 14.3 surf_bv 14.3 
     152select * from rht.attributs_rht_fev_2011_vs2 where id_drain='223887' 
    120153}}}