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;
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
select 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') from wrbd.operation_op where extract('year' from op_date) !=op_year;
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;

-- 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);