Changes between Version 27 and Version 28 of INTEGRATING fish

Timestamp:

Dec 6, 2010 10:05:47 PM (14 years ago)

Author:

cedric

Comment:

--

INTEGRATING fish

@@ -209 +209 @@
 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 ';';
+-- 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
+}}}