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

/********************************************
*  INTEGRATING WRBD stations
*********************************************/
ALTER TABLE wrbd.station_st add column st_riverbasin_district character(10);
ALTER TABLE wrbd.station_st add column st_GPS_description character(30);

--Même station dans la table Station_14_Eel_Db_MI_Rivers.csv
--A conserver les stations de cette table et ne pas copier les stations MI0121 à MI0138

copy wrbd.station_st(st_foliono,st_id,st_riverbasin_district,st_district,st_catchment,st_river,st_sitetributary,st_eastings,st_northing,st_x,st_y,st_location,st_gps_description) from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/Station_4.WRBD_NDP_dbase_Historical_Lakes_and_all_burr_silvers.csv' with csv delimiter as ';' header ; 
--  lines

5. WRBD Lakes Fyke Net Survey 2009.xls

alter table wrbd.station_st add column st_lake text;
alter table wrbd.station_st add column st_lake_code text;
-- integrating stations
copy wrbd.station_st(st_id,st_lake, st_location, st_eastings,st_northing,st_x,st_y,st_source)from 'c:/base/lake_stations_5.csv' with csv header delimiter as ';';
-- some other stations
copy wrbd.station_st(st_id,st_lake, st_location, st_eastings,st_northing,st_x,st_y,st_source)from 'c:/base/lake_stations_5_bis.csv' with csv delimiter as ';';

UPDATE wrbd.station_st   SET the_geom=ST_Transform(PointFromText('POINT(' || st_eastings || ' ' || st_northing || ')',29901),3035) where the_geom is null;

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

delete from wrbd.station_st where st_id='st3'

Number of station in Celine database (problem in ERS)

select * from dbeel.view_electrofishing where ob_dp_name='Russell Poole & Elvira de Eito'  ---984 lines --2240
select * from wrbd.station_st ---576 lines
select * from wrbd.wrbd_station_ccm_500  ---333 lines