back to first page..
back to WRBD

drop table if exists wrbd.fish_fi;

create table wrbd.fish_fi (
fi_folio_no character varying(50),
fi_st_id character varying(10),
fi_fish_id character varying(10),
fi_date date,
fi_year integer,
fi_individual_life character varying(10),
fi_retained character varying(10),
fi_length numeric
);


copy wrbd.fish_fi from 'C:/base/Operation_fish_14_Eel_Db_MI_Rivers.csv' with csv delimiter as ';'  ; 
copy wrbd.fish_fi from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/Operation_fish_14_Eel_Db_MI_Rivers.csv' with csv delimiter as ';'  header; 
--2208 lines

alter table wrbd.fish_fi add column fi_id serial PRIMARY KEY;
alter table wrbd.fish_fi rename column fi_fish_id to fi_fishid;

• pour aller rajouter les op_id dans la table poissons ticket #75

  alter table wrbd.fish_fi add column fi_op_id integer;
  --sous la console cmd:
  D :
  cd D:\CelineJouanin\POSEProject\Ireland\DataIreland
  C:\"Program Files"\PostgreSQL\8.4\bin\psql  -U postgres --dbname "eda2.0" -f "update_fish.sql"
  

4/01/2011 THIS IS FALSE ticket #75

cd C:\eda\ireland\POSE_Data_for_Modellers
C:\"Program Files"\PostgreSQL\8.4\bin\psql  -U postgres --dbname "eda2.0" -f "update_fish_corrected.sql"

• Integration the data from 4.WRBD_NDP_ddbase Historical lakes and all Burr silvers

experimental fishing data from Russell

ALTER TABLE wrbd.fish_fi ADD COLUMN fi_op_nbdays integer;
ALTER TABLE wrbd.fish_fi ADD COLUMN fi_weight numeric;
ALTER TABLE wrbd.fish_fi ADD COLUMN fi_eye_diameter numeric;
ALTER TABLE wrbd.fish_fi ADD COLUMN fi_black_spots_on_lateral_line character(10);
ALTER TABLE wrbd.fish_fi ADD COLUMN fi_colour character(10);
ALTER TABLE wrbd.fish_fi ADD COLUMN fi_metallic_colour character(10);
ALTER TABLE wrbd.fish_fi ADD COLUMN fi_pectoral_fin_length numeric;
ALTER TABLE wrbd.fish_fi ADD COLUMN fi_measured_live_or_frozen character(10);
ALTER TABLE wrbd.fish_fi ADD COLUMN fi_sexe character(20);
ALTER TABLE wrbd.fish_fi ADD COLUMN fi_age character(20);
ALTER TABLE wrbd.fish_fi ADD COLUMN fi_pigment_stage character(20);
ALTER TABLE wrbd.fish_fi ADD COLUMN fi_anguillicola character(20);
ALTER TABLE wrbd.fish_fi ADD COLUMN fi_anguillicola_nos numeric;




copy wrbd.fish_fi(fi_folio_no,fi_st_id,fi_fishid,fi_date,fi_year,fi_individual_life,fi_retained,fi_length,fi_weight,fi_eye_diameter,fi_black_spots_on_lateral_line,fi_colour,fi_metallic_colour,fi_pectoral_fin_length,fi_measured_live_or_frozen,fi_sexe,fi_age,fi_pigment_stage,fi_anguillicola,fi_anguillicola_nos,fi_op_nbdays) from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/Fish_4.WRBD_NDP_dbase_ Historical_Lakes_and_all_burr_silvers.csv' with csv delimiter as ';' header ; 
-- 25669 lines
COPY wrbd.fish_fi(fi_folio_no,fi_st_id,fi_fishid,fi_date,fi_year,fi_individual_life,fi_retained,fi_length,fi_weight,fi_eye_diameter,fi_black_spots_on_lateral_line,fi_colour,fi_metallic_colour,fi_pectoral_fin_length,fi_measured_live_or_frozen,fi_sexe,fi_age,fi_pigment_stage,fi_anguillicola,fi_anguillicola_nos,fi_op_nbdays) 
from 'C:/base/4fish.csv' with csv delimiter as ';' header ; 

specimen_riv_08_09

-- il faut aller récupérer les op_id et st_id je le fais sur des tables temporaires
CREATE TABLE wrbd.specimen_riv_08_09 (
location text,
easting integer,
northing integer,
lat numeric,
long numeric,
db_code character varying(10),
id integer,
fish_id integer,
date date,
year integer,
individual_life_silver_stage text,
retained text,
length_cm numeric,
estimated_length_cm numeric,
weight_g numeric);

copy wrbd.specimen_riv_08_09 from 'C:/base/specimen_riv_08_09.csv' with CSV header delimiter as ';';
copy wrbd.specimen_riv_08_09 from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/specimen_riv_08_09.csv' with CSV header delimiter as ';';

select count (*) from wrbd.specimen_riv_08_09; --162
select count(*)  from (
select * from  wrbd.specimen_riv_08_09 left join wrbd.station_st on (easting,northing)=(st_eastings,st_northing)
)as tititata; --162 OK

drop table if exists wrbd.specimen_riv_08_09_1;
create table wrbd.specimen_riv_08_09_1 as(
select ss.st_id as fi_st_id, sr.* from  wrbd.specimen_riv_08_09 sr left join wrbd.station_st ss on (easting,northing)=(st_eastings,st_northing)
);
drop table wrbd.specimen_riv_08_09;
-- le seul pivot possible est sur l'année
select * from wrbd.specimen_riv_08_09_1 join wrbd.operation_op on (op_st_id,op_year)=(fi_st_id,year) ;-- 162
create table wrbd.specimen_riv_08_09_2 as(
        select o.op_id as fi_ope_id, sr.* from wrbd.specimen_riv_08_09_1 sr join wrbd.operation_op o on (op_st_id,op_year)=(fi_st_id,year)
);
drop table wrbd.specimen_riv_08_09_1;
--wrbd.specimen_riv_08_09_2 now contains operation and stations

insert into wrbd.fish_fi(
fi_st_id,
fi_op_id,
fi_fishid,
fi_date,
fi_year,
fi_individual_life,
fi_retained,
fi_length,
fi_weight)
  select 
fi_st_id, 
fi_ope_id,
fish_id,
date,
year,
individual_life_silver_stage,
retained,
case when length_cm is null then estimated_length_cm
else length_cm end as length,
weight_g from wrbd.specimen_riv_08_09_2;
drop table wrbd.specimen_riv_08_09_2;

specimen_lakes_08_09

alter table wrbd.fish_fi rename column fi_sexe to fi_sex;
alter table wrbd.fish_fi rename column fi_individual_life to fi_lifestage;
alter table wrbd.fish_fi add column fi_anguillicolaweight numeric;
create table wrbd.specimen_lakes_08_09(
lake text,
easting integer,
northing integer,
lat numeric,
long numeric,
lake_name text,
date_picked_up date,
year integer,
net_no integer,
net_type text,
no_net integer,
length_cm numeric,
weight_g numeric,
lifestage text,
retained text,
sex text,
anguillicola_count integer,
weight_of_anguillicola_infection_g numeric);

copy wrbd.specimen_lakes_08_09 from 'C:/base/specimen_lakes_08_09.csv' with CSV header delimiter as ';';
copy wrbd.specimen_lakes_08_09 from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/specimen_lakes_08_09.csv' with CSV header delimiter as ';';


select count (*) from wrbd.specimen_lakes_08_09; --219
select count(*)  from (
select * from  wrbd.specimen_lakes_08_09 left join wrbd.station_st on (easting,northing)=(st_eastings,st_northing)
)as tititata; --219 OK

drop table if exists wrbd.specimen_lakes_08_09_1;
create table wrbd.specimen_lakes_08_09_1 as(
select ss.st_id as fi_st_id, sr.* from  wrbd.specimen_lakes_08_09 sr left join wrbd.station_st ss on (easting,northing)=(st_eastings,st_northing)
);
drop table wrbd.specimen_lakes_08_09;
-- le seul pivot possible est sur l'année
select * from wrbd.specimen_lakes_08_09_1 join wrbd.operation_op on (op_st_id,op_year)=(fi_st_id,year) ;-- 219
create table wrbd.specimen_lakes_08_09_2 as(
        select o.op_id as fi_ope_id, sr.* from wrbd.specimen_lakes_08_09_1 sr join wrbd.operation_op o on (op_st_id,op_year)=(fi_st_id,year)
);
drop table wrbd.specimen_lakes_08_09_1;
--wrbd.specimen_lakes_08_09_2 now contains operation and stations

insert into wrbd.fish_fi(
fi_st_id,
fi_op_id,
fi_date,
fi_year,
fi_lifestage,
fi_retained,
fi_length,
fi_weight,
fi_sex,
fi_anguillicola_nos,
fi_anguillicolaweight
)
  select 
fi_st_id, 
fi_ope_id,
date_picked_up,
year,
lifestage,
retained,
length_cm,
weight_g,
sex,
anguillicola_count,
weight_of_anguillicola_infection_g
from wrbd.specimen_lakes_08_09_2;

drop table wrbd.specimen_lakes_08_09_2;

select count(*) from wrbd.fish_fi; --26050

specimen_tw_08_09

drop table if exists wrbd.specimen_tw_08_09;
create table wrbd.specimen_tw_08_09(
estuary_name text,
date date,
easting integer,
northing integer,
lat numeric,
long numeric,
net_no text,
net_individual_no text,
net_type text,
length_cm numeric,
lifestage text,
sex text,
anguillicola_count integer,
weight_of_anguillicola_infection_g numeric,
otoliths_taken text,
otolith_box_no text,
stomach_contents text,
comment text);

copy wrbd.specimen_tw_08_09 from 'C:/base/specimen_tw_08_09.csv' with CSV header delimiter as ';';
copy wrbd.specimen_tw_08_09 from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/specimen_tw_08_09.csv' with CSV header delimiter as ';';

-- a long and painfull process to verify that the names match, the cooridinates of the stations were sometimes different ex: Moy, I had only 39 station 
select count (*) from wrbd.specimen_tw_08_09; --98
select * from  wrbd.specimen_tw_08_09 left join wrbd.station_st on (estuary_name)=(st_location); -- OK everything is filled in 98

drop table if exists wrbd.specimen_tw_08_09_1;
create table wrbd.specimen_tw_08_09_1 as(
select ss.st_id as fi_st_id,extract(year from date) as year, sr.* from  wrbd.specimen_tw_08_09 sr left join wrbd.station_st ss on (estuary_name)=(st_location)
);
drop table wrbd.specimen_tw_08_09;
-- le seul pivot possible est sur l'année
select * from wrbd.specimen_tw_08_09_1 join wrbd.operation_op on (op_st_id,op_year)=(fi_st_id,year) ;-- 98
create table wrbd.specimen_tw_08_09_2 as(
        select o.op_id as fi_ope_id, sr.* from wrbd.specimen_tw_08_09_1 sr join wrbd.operation_op o on (op_st_id,op_year)=(fi_st_id,year)
);
drop table wrbd.specimen_tw_08_09_1;
--wrbd.specimen_tw_08_09_2 now contains operation and stations

insert into wrbd.fish_fi(
fi_st_id,
fi_op_id,
fi_date,
fi_year,
fi_lifestage,
fi_retained,
fi_length,
fi_sex,
fi_anguillicola_nos,
fi_anguillicolaweight
)
  select 
fi_st_id, 
fi_ope_id,
date,
year,
lifestage,
case when otoliths_taken is not null then 'yes'
else 'no' end as retained,
length_cm,
sex,
anguillicola_count,
weight_of_anguillicola_infection_g
from wrbd.specimen_tw_08_09_2;

drop table wrbd.specimen_tw_08_09_2;

select count(*) from wrbd.fish_fi; --26148
--pb with céline, the names do not match
select * from wrbd.fish_fi where fi_id>=26051;
-- saving 3 from cédric
delete from wrbd.fish_fi where fi_id>=26051;
copy wrbd.fish_fi from 'C:/base/specimen_tw_08_09_3.csv' with CSV header delimiter as ';';
copy wrbd.fish_fi from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/specimen_tw_08_09_3.csv' with CSV header delimiter as ';';

wfd river depletion

some additional information to be added by update (the operations were already there

/* pour mémoire
drop table if exists wrbd.wfd_operations;
create table wrbd.wfd_operations (date date,year integer,station character varying(10),
length numeric,width numeric,area numeric,p1 integer,p2 integer,p3 integer,total integer,totalest numeric);

copy wrbd.wfd_operations from 'C:/base/wfd_operations.csv' with CSV header delimiter as ';';
copy wrbd.wfd_operations from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/wfd_operations.csv' with CSV header delimiter as ';';
-- le seul pivot possible est sur l'année
select * from wrbd.wfd_operations join wrbd.operation_op on (op_st_id,op_year)=(station,year) ;
create table wrbd.wfd_operations_1 as(
        select o.op_id as ope_id, sr.* from wrbd.wfd_operations sr join wrbd.operation_op o on (op_st_id,op_year)=(station,year)
);
drop table wrbd.wfd_operations;
drop table wrbd.wfd_operations_1;
*/
-- export excel
--wrbd.wfd_operations_1 now contains operation
-- a la main sous excel
update wrbd.operation_op set (op_p1,op_p2,op_p3,op_nbtotal,op_density,op_totalestim) =(13,8,7,28,0.072,34) where op_id=8;
update wrbd.operation_op set (op_p1,op_p2,op_p3,op_nbtotal,op_density,op_totalestim) =(1,0,0,1,0.006,2) where op_id=18;
update wrbd.operation_op set (op_p1,op_p2,op_p3,op_nbtotal,op_density,op_totalestim) =(1,0,0,1,0.004,2) where op_id=13;
update wrbd.operation_op set (op_p1,op_p2,op_p3,op_nbtotal,op_density,op_totalestim) =(1,1,1,3,0.004,3) where op_id=16;
update wrbd.operation_op set (op_p1,op_p2,op_p3,op_nbtotal,op_density,op_totalestim) =(9,25,17,51,0.068,43) where op_id=12;
update wrbd.operation_op set (op_p1,op_p2,op_p3,op_nbtotal,op_density,op_totalestim) =(12,7,9,28,0.014,31) where op_id=15;
update wrbd.operation_op set (op_p1,op_p2,op_p3,op_nbtotal,op_density,op_totalestim) =(9,2,2,13,0.003,20) where op_id=14;
update wrbd.operation_op set (op_p1,op_p2,op_p3,op_nbtotal,op_density,op_totalestim) =(0,2,0,2,0.007,2) where op_id=2;
update wrbd.operation_op set (op_p1,op_p2,op_p3,op
_nbtotal,op_density,op_totalestim) =(17,15,3,35,0.097,49) where op_id=5;

Adding a constraint

alter table wrbd.fish_fi add  CONSTRAINT c_fk_fi_op_id FOREIGN KEY (fi_op_id)
      REFERENCES wrbd.operation_op (op_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION