CookBook join Corine Land Cover-RHT – EDA

wiki:CookBook join Corine Land Cover-RHT

Context Navigation

back to first page..
back to RHT

Integrate Corine Land Cover into RHT

A layer is available from Hervé #81

Version 2000.

pour chaque identifiant RHT (id_drain), la surface unitaire (catchment_area en m²) et la surface pour chacune des 44 classes de Corine land Cover. à voir fichiers _clc
pour chaque identifiant RHT (id_drain), la surface amont (m²) et la surface pour chacune des 44 classes de Corine Land Cover à voir fichiers _clcacc (attention dans ces fichiers il manque les deux premiers noms de colonnes).

Import data ag_clc.txt from access into Postgre.

create schema clc2000;
drop table if exists clc2000.area_clc;
CREATE TABLE clc2000.area_clc
(
  Reach_Number integer,
  Catchment_area double precision,
  art_111 double precision,
  art_112 double precision,
  art_121 double precision,
  art_122 double precision,
  art_123 double precision,
  art_124 double precision,
  art_131 double precision,
  art_132 double precision,
  art_133 double precision,
  art_141 double precision,
  art_142 double precision,
  arable_211 double precision,
  arable_212 double precision,
  arable_213 double precision,
  permcrop_221 double precision,
  permcrop_222 double precision,
  permcrop_223 double precision,
  pasture_231 double precision,
  hetagr_241 double precision,
  hetagr_242 double precision,
  hetagr_243 double precision,
  hetagr_244 double precision,
  forest_311 double precision,
  forest_312 double precision,
  forest_313 double precision,
  natural_321 double precision,
  natural_322 double precision,
  natural_323 double precision,
  natural_324 double precision,
  natural_331 double precision,
  natural_332 double precision,
  natural_333 double precision,
  natural_334 double precision,
  natural_335 double precision,
  wetlands_411 double precision,
  wetlands_412 double precision,
  wetlands_421 double precision,
  wetlands_422 double precision,
  wetlands_423 double precision,
  inwat_511 double precision,
  inwat_512 double precision,
  marwat_521 double precision,
  marwat_522 double precision,
  marwat_523 double precision
);
alter table clc2000.area_clc ADD CONSTRAINT pk_idrnum PRIMARY KEY(Reach_Number);
copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/ag_clc.csv' with csv delimiter as ';' header; 
copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/ap_clc.csv' with csv delimiter as ';' header; 
copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/bret_clc.csv' with csv delimiter as ';' header; 
copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/cors_clc.csv' with csv delimiter as ';' header; 
copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/loir_clc.csv' with csv delimiter as ';' header; 
copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/rhon_clc.csv' with csv delimiter as ';' header; 
copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/rhow_clc.csv' with csv delimiter as ';' header; 
copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/rm_clc.csv' with csv delimiter as ';' header; 
copy clc2000.area_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/sn_clc.csv' with csv delimiter as ';' header; 

select count(*) from clc2000.area_clc ---114728
select count(*) from rht.rht  ---114601

drop table if exists clc2000.uparea_clc;
CREATE TABLE clc2000.uparea_clc
(
  Reach_Number integer,
  up_area double precision,
  art_111 double precision,
  art_112 double precision,
  art_121 double precision,
  art_122 double precision,
  art_123 double precision,
  art_124 double precision,
  art_131 double precision,
  art_132 double precision,
  art_133 double precision,
  art_141 double precision,
  art_142 double precision,
  arable_211 double precision,
  arable_212 double precision,
  arable_213 double precision,
  permcrop_221 double precision,
  permcrop_222 double precision,
  permcrop_223 double precision,
  pasture_231 double precision,
  hetagr_241 double precision,
  hetagr_242 double precision,
  hetagr_243 double precision,
  hetagr_244 double precision,
  forest_311 double precision,
  forest_312 double precision,
  forest_313 double precision,
  natural_321 double precision,
  natural_322 double precision,
  natural_323 double precision,
  natural_324 double precision,
  natural_331 double precision,
  natural_332 double precision,
  natural_333 double precision,
  natural_334 double precision,
  natural_335 double precision,
  wetlands_411 double precision,
  wetlands_412 double precision,
  wetlands_421 double precision,
  wetlands_422 double precision,
  wetlands_423 double precision,
  inwat_511 double precision,
  inwat_512 double precision,
  marwat_521 double precision,
  marwat_522 double precision,
  marwat_523 double precision
);
alter table clc2000.uparea_clc ADD CONSTRAINT pk_idnum PRIMARY KEY(Reach_Number);
copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/ag_clcacc.csv' with csv delimiter as ';' header; 
copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/ap_clcacc.csv' with csv delimiter as ';' header; 
copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/bret_clcacc.csv' with csv delimiter as ';' header; 
copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/cors_clcacc.csv' with csv delimiter as ';' header; 
copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/loir_clcacc.csv' with csv delimiter as ';' header; 
copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/rhon_clcacc.csv' with csv delimiter as ';' header; 
copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/rhow_clcacc.csv' with csv delimiter as ';' header; 
copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/rm_clcacc.csv' with csv delimiter as ';' header; 
copy clc2000.uparea_clc from 'D:/CelineJouanin/RHT_Estimkart/clc/sn_clcacc.csv' with csv delimiter as ';' header; 

select count(*) from clc2000.uparea_clc  ---86992 (sans loire)
select count(*) from clc2000.uparea_clc  ---114599 (avec loire) manque id_drain=223988

---pb avec loir_clcacc
---ERREUR:  la valeur d'une clé dupliquée rompt la contrainte unique « pk_idnum »
---CONTEXT:  COPY uparea_clc, line 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 »

select * from clc2000.uparea_clc where Reach_Number='223988';
select * from clc2000.uparea_clc where up_area is null;

select * from rht.attributs_rht_fev_2011_vs2 where id_drain='223988'   --- sfbvu 14.3 surf_bv 14.3
select * from rht.attributs_rht_fev_2011_vs2 where id_drain='223887'

CREATE INDEX indexareaclc
ON clc2000.area_clc
USING btree (reach_number);

CREATE INDEX indexupareaclc
ON clc2000.uparea_clc
USING btree (reach_number);

SELECT AddGeometryColumn ( 'clc2000','area_clc','the_geom',3035,'MULTILINESTRING',2);
update clc2000.area_clc set the_geom=rhtvs2.the_geom from rht.rhtvs2
 where rhtvs2.id_drain=area_clc.reach_number; 

SELECT AddGeometryColumn ( 'clc2000','uparea_clc','the_geom',3035,'MULTILINESTRING',2);
update clc2000.uparea_clc set the_geom=rhtvs2.the_geom from rht.rhtvs2
 where rhtvs2.id_drain=uparea_clc.reach_number;

Last modified 13 years ago Last modified on Oct 3, 2012 4:50:49 PM

Download in other formats:

Plain Text