back to first page[..] [[BR]]
back to ["WRBD"][[BR]]

{{{
#!sql
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
}}}

{{{
#!sql
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"
}}}


 * Integration the data from 4.WRBD_NDP_ddbase Historical lakes and all Burr silvers
experimental fishing data from Russell 
{{{
#!sql
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/4.WRBD_NDP_dbase_Historical_Lakes_and_all_burr_silvers.csv' with csv delimiter as ';' header ; 
}}}


 = specimen_riv_08_09 =
{{{
#!sql
-- 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 ';';
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
}}}