back to first page..
back to WRBD

Station WRBD (Western River Basin District)

CREATE SCHEMA wrbd;

drop table if exists wrbd.station_st;
create table wrbd.station_st ( 
        st_foliono character varying(50),
        st_id character varying(10),
        st_district character varying(30),
        st_catchment character varying(15),
        st_river character varying(40),
        st_sitetributary character(5),
        st_eastings numeric,
        st_northing numeric,
        st_location text,
        st_x numeric,
        st_y numeric
        );
alter table wrbd.station_st add constraint  pk_st_id primary key (st_id);
set client_encoding to 'latin1';
copy wrbd.station_st  from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/Station_14_Eel_Db_MI_Rivers.csv' with csv delimiter as ';' header; 
-- projection sur le SRID 3035
SELECT AddGeometryColumn('wrbd', 'station_st','the_geom', 3035,'POINT',2); 
UPDATE wrbd.station_st   SET the_geom=ST_Transform(PointFromText('POINT(' || st_eastings || ' ' || st_northing || ')',29901),3035);
ALTER TABLE wrbd.station_st SET WITH OIDS;

--srid =29903 or srid=29901, 29900

CREATE INDEX indexstation_st ON wrbd.station_st
  USING GIST ( the_geom GIST_GEOMETRY_OPS );

/********************************************
*  INTEGRATING WFD stations
*********************************************/
 alter table wrbd.station_st add column st_source character(25);
 update  wrbd.station_st set st_source='MI electrofishing survey';
 alter table wrbd.station_st add column st_waterbody character(25);
 update  wrbd.station_st set st_waterbody='river';
-- COPYING survey stations
copy wrbd.station_st (st_id,st_waterbody,st_catchment,st_location, st_eastings,st_northing,st_x,st_y)  from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/8.WFD_Rivers_Survey_Stations.csv' with csv delimiter as ';' header; 
copy wrbd.station_st (st_id,st_waterbody,st_catchment,st_location, st_eastings,st_northing,st_x,st_y) from 'C:/base/8.WFD_Rivers_Survey_Stations.csv' with csv delimiter as ';' header; 
-- copying transitional water stations
 copy wrbd.station_st (st_id,st_waterbody,st_catchment,st_location, st_eastings,st_northing,st_x,st_y)  from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/8.Lakes_TWaters_Surveys_Stations.csv' with csv delimiter as ';' header; 
copy wrbd.station_st (st_id,st_waterbody,st_catchment,st_location, st_eastings,st_northing,st_x,st_y) from 'C:/base/8.Lakes_TWaters_Surveys_Stations.csv' with csv delimiter as ';' header; 
-- I forgot to include st_source to differentiate
  update  wrbd.station_st set st_source='WFD_Rivers_Surveys' where st_waterbody='River';
  update  wrbd.station_st set st_source='WFD_Lake_Surveys' where st_waterbody='Lake';
  update  wrbd.station_st set st_source='WFD_Lake_Surveys' where st_waterbody='Non-Freshwater';
  update  wrbd.station_st set st_waterbody='river' where st_waterbody='River';
  UPDATE wrbd.station_st SET the_geom=ST_Transform(PointFromText('POINT(' || st_eastings || ' ' || st_northing || ')',29903),3035) where wrbd.station_st.st_source='WFD_Rivers_Surveys';
   UPDATE wrbd.station_st SET the_geom=ST_Transform(PointFromText('POINT(' || st_eastings || ' ' || st_northing || ')',29903),3035) where wrbd.station_st.st_source='WFD_Lake_Surveys';

-- below this is a qgis oproblem
   alter table wrbd.station_st add column st_source_code integer;
   update  wrbd.station_st set st_source_code=1 where st_source='MI electrofishing survey';
   update  wrbd.station_st set st_source_code=2 where st_source='WFD_Rivers_Surveys';
   update  wrbd.station_st set st_source_code=3 where st_source='WFD_Lake_Surveys';  

DROP TABLE IF EXISTS wrbd.wrbd_station_ccm_500;
CREATE TABLE wrbd.wrbd_station_ccm_500 as (
        SELECT distinct on (st_id ) st_id , gid, wso1_id, min(distance) as distance, the_geom FROM (
               SELECT st_id, gid , wso1_id, CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
               FROM wrbd.station_st As s
               INNER JOIN  ccm21.riversegments r ON ST_DWithin(r.the_geom, s.the_geom,500)
               WHERE s.the_geom IS NOT NULL
               ORDER BY st_id) AS sub 
        GROUP BY st_id, gid, wso1_id,  distance,the_geom
);
alter table wrbd.wrbd_station_ccm_500 add column id serial;
-- mise à jour de la table geometry_columns
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'wrbd', 'wrbd_station_ccm_500', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM wrbd.wrbd_station_ccm_500 LIMIT 1;
-- creation d'index, clé primaire, et constraintes qui vont bien
alter table wrbd.wrbd_station_ccm_500 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table wrbd.wrbd_station_ccm_500 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
alter table wrbd.wrbd_station_ccm_500 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
alter table wrbd.wrbd_station_ccm_500 ADD CONSTRAINT pk_id PRIMARY KEY(id);
CREATE INDEX indexstation_ccm_500 ON wrbd.wrbd_station_ccm_500
  USING GIST ( the_geom GIST_GEOMETRY_OPS );

-- 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 ; 
--1016 lines

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

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; 
copy wrbd.fish_fi(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 ;