Changes between Initial Version and Version 1 of INTEGRATING stations

Timestamp:

Dec 3, 2010 4:57:29 PM (14 years ago)

Author:

celine

Comment:

--

INTEGRATING stations

@@ +1 @@
+back to first page[..] [[BR]]
+back to ["WRBD"][[BR]]
+
+Station WRBD (Western River Basin District)
+{{{
+#!sql
+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';
+
+}}}
+
+{{{
+#!sql
+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 );
+}}}
+
+{{{
+#!sql
+-- 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)
+);
+
+alter table wrbd.operation_op add column op_nb_sets numeric;
+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 rename column op_num_fishing  to op_nb_pass;
+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_nbind integer;
+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 ';'  ; 
+
+}}}
+{{{
+#!sql
+-- 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);
+
+}}}
+{{{
+#!sql
+drop table if exists wrbd.operation_fish_fi;
+
+create table wrbd.operation_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.operation_fish_fi from 'C:/base/Operation_fish_14_Eel_Db_MI_Rivers.csv' with csv delimiter as ';'  ; 
+copy wrbd.operation_fish_fi from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/Operation_fish_14_Eel_Db_MI_Rivers.csv' with csv delimiter as ';'  header; 
+
+}}}