back to first page..
back to WRBD

-- operations
-- changing format of column i
drop table if exists wrbd.operation_op;

create table wrbd.operation_op (
op_folio_no character varying(50),
op_st_id character varying(10),
ope_date date,
ope_year integer,
op_area numeric,
op_glass numeric, 
op_elver numeric,
op_yellow numeric,
op_silver numeric,
op_not_recorded numeric,
op_eels_anaesthetized text, 
op_num_fishing integer,
op_p1 integer,
op_p2 integer,
op_p3 integer,
op_p4 integer,
op_p5 integer,
op_total integer,
op_abundance_rating integer,
op_distribution character varying(15),
op_main_survey_target character varying(20),
opedisthwm numeric,
constraint c_fk_op_code  FOREIGN KEY  (op_st_id) REFERENCES wrbd.station_st(st_id)
);



copy wrbd.operation_op from 'C:/base/Operation_14_Eel_Db_MI_Rivers.csv' with csv delimiter as ';'  ; 
copy wrbd.operation_op from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/Operation_14_Eel_Db_MI_Rivers.csv' with csv delimiter as ';'  ; 
-- 966 lines

alter table wrbd.operation_op add column op_nbsets numeric;
update wrbd.operation_op set op_nbsets=1;
alter table wrbd.operation_op rename column op_num_fishing  to op_nb_pass;
alter table wrbd.operation_op add column op_density numeric;
alter table wrbd.operation_op add column op_equipment character varying(40);
update wrbd.operation_op set op_equipment='Handset';
alter table  wrbd.operation_op add column op_nbfem integer;
alter table  wrbd.operation_op add column op_nbmal integer;
alter table  wrbd.operation_op add column op_nbimmat integer;
alter table  wrbd.operation_op add column op_nbind integer;
alter table  wrbd.operation_op add column op_totalestim numeric;
alter table  wrbd.operation_op add column op_totalweight numeric;
alter table  wrbd.operation_op rename column ope_year to op_year;
alter table  wrbd.operation_op rename column ope_date to op_date;
alter table wrbd.operation_op rename colum opedisthwm to op_disthwm;
copy wrbd.operation_op(op_st_id,op_year,op_equipment,op_area,op_nbsets,op_nb_pass,op_density,op_totalestim,op_totalweight,op_total,op_nbfem,op_nbmal,op_nbimmat) from 'C:/base/8WFD_river_survey_operations.csv' with csv delimiter as ';' header ; 
copy wrbd.operation_op from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/8WFD_river_survey_operations.csv' with csv delimiter as ';'  ; 
--984 lines

alter table wrbd.operation_op add column op_id serial;
alter table wrbd.operation_op add constraint pk_wrbd_operation_op PRIMARY KEY (op_id);
alter table wrbd.operation_op add column op_nbnights integer;
alter table wrbd.operation_op add column op_nbnets integer;
alter table wrbd.operation_op add column op_cpue numeric;
alter table wrbd.operation_op rename column op_total  to op_nbtotal;

copy wrbd.operation_op(op_st_id,op_year,op_equipment,op_nbnights,op_nbnets,op_nbtotal,op_cpue,op_totalweight,op_nbfem,op_nbmal,op_nbimmat) from 'C:/base/8.lakes_operation.csv' with csv delimiter as ';' header ; 
copy wrbd.operation_op(op_st_id,op_year,op_equipment,op_nbnights,op_nbnets,op_nbtotal,op_cpue,op_totalweight,op_nbfem,op_nbmal,op_nbimmat) from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/8.lakes_operation.csv' with csv delimiter as ';' header ; 

-- problème de dates dans operation
select * from wrbd.operation_op where extract('year' from op_date) !=op_year;
-- modification des dates concernées
update wrbd.operation_op set op_date=to_date(
                cast(op_year AS text)||' '||
                cast(extract('month' from op_date) AS text)||' '||
                cast(extract('day' from op_date) AS text),'YYYY MM DD'
        ) where extract('year' from op_date) !=op_year;

copy wrbd.operation_op(op_st_id,op_date,op_totalweight,op_nbind,op_nbnights,op_cpue)from 'c:/eda/ireland/POSE_Data_for_Modellers/lake_operations_5.csv' with csv header delimiter as ';';
copy wrbd.operation_op(op_st_id,op_date,op_totalweight,op_nbind,op_nbnights,op_cpue)from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/lake_operations_5.csv' with csv header delimiter as ';';
-- updates on the excel file from Elvira
copy wrbd.operation_op(op_st_id,op_date,op_totalweight,op_nbind,op_nbnights,op_cpue)from 'c:/eda/ireland/POSE_Data_for_Modellers/lake_operations_5_bis.csv' with csv delimiter as ';';
update wrbd.operation_op set op_equipment= 'fyke net' where op_id>1016 and op_id<1335; 

-- joining operations and stations
drop table if exists wrbd.operation_station;
create table wrbd.operation_station as (
        select * from wrbd.station_st join wrbd.operation_op on op_st_id=st_id
        where st_id in (select st_id from wrbd.wrbd_station_ccm_500));
alter table wrbd.operation_station add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table wrbd.operation_station add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
alter table wrbd.operation_station add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);

• Integrating operations from 4.WRBD_NDP_dbase Historical Lakes and all burr silvers

  alter table wrbd.operation_op add column op_datasource character(30);
  alter table wrbd.operation_op add column op_nbdaysfishing numeric;
  alter table wrbd.operation_op add column op_nbnightsbeforelifting numeric;
  alter table wrbd.operation_op add column op_silvereelcatchwt numeric;
  
  copy wrbd.operation_op(op_folio_no,op_st_id,op_date,op_year,op_datasource,op_equipment,op_nbnets,op_nbdaysfishing,
  op_nbnightsbeforelifting,op_glass,op_elver,op_yellow,op_silver,op_not_recorded,op_nb_pass,op_p1,op_p2,
  op_p3,op_p4,op_p5,op_distribution,op_main_survey_target,op_silvereelcatchwt) 
  from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/Operation_4.WRBD_NDP_dbase_Historical_Lakes_and_all_burr_silvers.csv' with csv header delimiter as ';' header;
  
  copy wrbd.operation_op(op_folio_no,op_st_id,op_date,op_year,op_datasource,op_equipment,op_nbnets,op_nbdaysfishing,
  op_nbnightsbeforelifting,op_glass,op_elver,op_yellow,op_silver,op_not_recorded,op_nb_pass,op_p1,op_p2,
  op_p3,op_p4,op_p5,op_distribution,op_main_survey_target,op_silvereelcatchwt) 
  from 'C:/eda/ireland/POSE_Data_for_Modellers/Operation_4.WRBD_NDP_dbase_Historical_Lakes_and_all_burr_silvers.csv' with csv header delimiter as ';' ;
  
  -- remplacement des -99 par rien
  -- ajout des op_di (je ne prends pas la valeur du serial)
  delete from wrbd.operation_op where op_id>=1961;
  --Cédric
  copy wrbd.operation_op(op_id,op_folio_no,op_st_id,op_date,op_year,op_datasource,op_equipment,op_nbnets,op_nbdaysfishing,
  op_nbnightsbeforelifting,op_glass,op_elver,op_yellow,op_silver,op_not_recorded,op_nb_pass,op_p1,op_p2,
  op_p3,op_p4,op_p5,op_distribution,op_main_survey_target,op_silvereelcatchwt) 
  from 'C:/eda/ireland/POSE_Data_for_Modellers/Operation_4.WRBD_NDP_dbase_Historical_Lakes_and_all_burr_silvers.csv' with csv header delimiter as ';'
  --céline
  copy wrbd.operation_op(op_id,op_folio_no,op_st_id,op_date,op_year,op_datasource,op_equipment,op_nbnets,
  op_nbnightsbeforelifting,op_glass,op_elver,op_yellow,op_silver,op_not_recorded,op_nb_pass,op_p1,op_p2,
  op_p3,op_p4,op_p5,op_distribution,op_main_survey_target,op_silvereelcatchwt) 
  from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/Operation_4.WRBD_NDP_dbase_Historical_Lakes_and_all_burr_silvers.csv' with csv header delimiter as ';'
  
  delete from wrbd.operation_op where op_id>=1335;-- nouvelle numerotation dans le fichier
  alter table wrbd.operation_op drop column op_nbnightsbeforelifting ; --c'est la colonne op_nbnights
  alter table wrbd.operation_op drop column op_nbdaysfishing; -- contains the values of nbnights
  --Cédric
  copy wrbd.operation_op(op_id,op_folio_no,op_st_id,op_date,op_year,op_datasource,op_equipment,op_nbnets,op_nbnights,
  op_glass,op_elver,op_yellow,op_silver,op_not_recorded,op_nb_pass,op_p1,op_p2,
  op_p3,op_p4,op_p5,op_distribution,op_main_survey_target,op_silvereelcatchwt) 
  from 'C:/eda/ireland/POSE_Data_for_Modellers/Operation_4.WRBD_NDP_dbase_Historical_Lakes_and_all_burr_silvers.csv' with csv header delimiter as ';'
  --Céline
  copy wrbd.operation_op(op_id,op_folio_no,op_st_id,op_date,op_year,op_datasource,op_equipment,op_nbnets,op_nbnights,
  op_glass,op_elver,op_yellow,op_silver,op_not_recorded,op_nb_pass,op_p1,op_p2,
  op_p3,op_p4,op_p5,op_distribution,op_main_survey_target,op_silvereelcatchwt) 
  from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/Operation_4.WRBD_NDP_dbase_Historical_Lakes_and_all_burr_silvers.csv' with csv header delimiter as ';'
  
  
  update wrbd.operation_op set op_equipment='fyke net' where op_equipment='fyke net standard'