back to first page ..
- Source des données de pêche
- stations
- operations-lots-lots par passage-poissons
- Projection
- Intégration des données de la dernière version de la BD_MAP dan dbeel
- Meuse par EMU et par pays jointure avec CCM
- Recherche des wso1_id où il y a eu des déversements
- Création d'une table pour avoir les prédictions par sous-bassins
Source des données de pêche
Les données en vert sont dans la table stations, système de projection 31370, en marron les données de la bdmap déjà dans la dbeel mais qu'il va falloir mettre à jour avec des données plus récentes.
stations
-- insertion données Laura create schema belge; alter table stations set schema belge; alter table mesures_ang set schema belge; alter table obstacles set schema belge; alter table anguilles set schema belge; set search_path to belge,public,dbeel; select * from belge.stations; select st_srid(the_geom) from belge.stations;--103300 --103300 est le code ESRI mais ce n'est pas un ESPG qui est 31370 update belge.stations set the_geom=st_setsrid(the_geom,31370); update belge.stations set the_geom=st_transform(the_geom,3035);--2484 alter table belge.stations ADD CONSTRAINT station_pkey PRIMARY KEY (st_id ), CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL), CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 27572), CONSTRAINT geometry_valid_check CHECK (isvalid(the_geom)); --Meuse INSERT INTO dbeel.establishment (et_establishment_name) VALUES ('SPW'); INSERT INTO dbeel.data_provider(dp_name, dp_et_id) VALUES ('Laura',7); alter table stations rename to operations; create table stations as select distinct on (site) site, organisme, eau,commune, lambex,lambey, code_precxy, the_geom from operations where code_typpec= 'PECHEELECTRIQUE'; alter table stations add constraint c_pk_stations PRIMARY KEY (site); DROP TABLE if exists belge.stationdbeel CASCADE; CREATE TABLE belge.stationdbeel ( LIKE belge.stations, CONSTRAINT pk_so_op_id PRIMARY KEY (op_id), CONSTRAINT fk_so_observation_place_type_id FOREIGN KEY (op_no_observationplacetype) REFERENCES dbeel_nomenclature.observation_place_type (no_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT ) INHERITS (dbeel.observation_places); INSERT INTO belge.stationdbeel SELECT uuid_generate_v4() AS op_id, 'SPW' AS op_gis_systemname , 'STATIONS' AS op_gis_layername, st.site AS op_gislocation, site AS op_placename, 10 AS op_no_observationplacetype, -- Sampling station NULL AS op_op_id, the_geom, site, organisme, eau,commune, lambex,lambey, code_precxy FROM belge.stations st ; -- 563 lines
==Intégration des stations dans stationdbeel== On vire les passes à poissons.
operations-lots-lots par passage-poissons
---------------------------------------------------- -- belge.electrofishing --------------------------------------------------- alter table operations add constraint c_pk_id PRIMARY KEY (ope_id); drop table if exists operationspeche; create table operationspeche as select ope_id, organisme, code_typpec, eau, site, dates, nb_pas, statut_validation, resultatsfish, code_repres_spec, surface_peche from operations where code_typpec = 'PECHEELECTRIQUE';--874 alter table operationspeche add constraint c_pk_id_operationspeche PRIMARY KEY (ope_id); -- creation de la cle etrangere vers station alter table operationspeche add constraint c_fk_site FOREIGN KEY (site) REFERENCES stations(site) ; select count(*) from operationspeche; --874 DROP TABLE if exists belge.electrofishing CASCADE; CREATE TABLE belge.electrofishing ( LIKE belge.operationspeche INCLUDING DEFAULTS INCLUDING CONSTRAINTS,-- seulement pour les contraintes check CONSTRAINT pk_oo_ob_id PRIMARY KEY (ob_id), CONSTRAINT fk_oo_dp_id FOREIGN KEY (ob_dp_id) REFERENCES dbeel.data_provider (dp_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_oo_electrofishing_mean FOREIGN KEY (ef_no_electrofishing_mean) REFERENCES dbeel_nomenclature.electrofishing_mean (no_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_oo_electrofishing_method FOREIGN KEY (ef_no_fishingmethod) REFERENCES dbeel_nomenclature.scientific_observation_method (no_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_oo_ob_no_origin FOREIGN KEY (ob_no_origin) REFERENCES dbeel_nomenclature.observation_origin (no_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_oo_ob_no_period FOREIGN KEY (ob_no_period) REFERENCES dbeel_nomenclature.period_type (no_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_oo_ob_no_type FOREIGN KEY (ob_no_type) REFERENCES dbeel_nomenclature.observation_type (no_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT ) INHERITS (dbeel.electrofishing); INSERT INTO belge.electrofishing (ob_id,ob_no_origin,ob_no_type,ob_no_period,ob_starting_date,ob_ending_date,ob_dp_id, ef_no_fishingmethod,ef_no_electrofishing_mean,ef_wetted_area,ef_fished_length,ef_fished_width,ef_duration,ef_nbpas,ob_op_id, ope_id, organisme, code_typpec, eau, site, dates, nb_pas, statut_validation, resultatsfish, code_repres_spec, surface_peche ) SELECT uuid_generate_v4() AS ob_id , observation_origin.no_id AS ob_no_origin, observation_type.no_id AS ob_no_type, period_type.no_id AS ob_no_period, dates AS ob_starting_date, NULL AS ob_ending_date, data_provider.dp_id AS ob_dp_id, -- belge case when nb_pas is null then 61 -- Unknown when nb_pas>=2 then 62 --whole (peche complete) when nb_pas=1 then 61 else 61 end AS ef_no_fishingmethod, --scientific_observation_method.no_id, electrofishing_mean.no_id AS ef_no_electrofishing_mean, surface_peche AS ef_wetted_area, NULL AS ef_fished_length, NULL AS ef_fished_width, NULL AS ef_duration, nb_pas AS ef_nbpas, op.* FROM dbeel_nomenclature.observation_origin, dbeel_nomenclature.scientific_observation_method, dbeel_nomenclature.observation_type, dbeel_nomenclature.period_type, dbeel.data_provider, dbeel_nomenclature.electrofishing_mean, (select st.op_id as op_ob_id, op.* from belge.operationspeche op join belge.stationdbeel st on op.site=st.site order by site ) as op WHERE observation_origin.no_name='Raw data' AND scientific_observation_method.sc_observation_category='Unknown' --AND scientific_observation_method.sc_observation_category='Electro-fishing' (je le met pas plusieurs cas) AND observation_type.no_name='Electro-fishing' AND electrofishing_mean.no_name='Unknown' -- a preciser plus tard AND period_type.no_name='Daily' AND data_provider.dp_name='Laura'; --874 lignes select * from belge.electrofishing ------------------------------- -- Batch intégration ------------------------------- -- select count (*) from mesures_ang;--1501 select * from mesures_ang; /* alter table mesures_ang add constraint c_pk_mea_id PRIMARY KEY (mea_id) ; alter table mesures_ang add column mea_ope_id integer; alter table mesures_ang add constraint c_fk_ope_id FOREIGN KEY (mea_ope_id) references operationspeche (ope_id); */ update mesures_ang set mea_ope_id=sub.ope_id from (select ope_id, mea_id from mesures_ang mea join operationspeche o on (mea.codsit,mea.datvis)=(o.site,o.dates)) sub where sub.mea_id=mesures_ang.mea_id;--1285 --select * from mesures_ang where mea_ope_id is not null; update mesures_ang set numpas=1 where mea_ope_id=263;--11 select * from belge.electrofishing drop view joineel; create or replace view joineel as select ob_op_id,ob_id,mea_id,mea_ope_id,site as ope_st_id,numpas,lonmin,lonmax,nbrind from belge.electrofishing join mesures_ang on mea_ope_id=ope_id; select * from joineel; -- La première table fait référence aux opérations de pêches (electrofishing) DROP TABLE if exists belge.batch_ope CASCADE; CREATE TABLE belge.batch_ope ( ope_id integer, ope_st_id character varying(25), pa_numero integer, nb integer, CONSTRAINT pk_batch_ope_id PRIMARY KEY (ba_id), CONSTRAINT fk_batch_ope_ba_no_species_id FOREIGN KEY (ba_no_species) REFERENCES dbeel_nomenclature.species (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_batch_ope_ba_no_stage_id FOREIGN KEY (ba_no_stage) REFERENCES dbeel_nomenclature.stage (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_batch_ope_ba_value_type FOREIGN KEY (ba_no_value_type) REFERENCES dbeel_nomenclature.value_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_batch_ope_ba_no_biological_characteristic_type FOREIGN KEY (ba_no_biological_characteristic_type) REFERENCES dbeel_nomenclature.biological_characteristic_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_batch_ope_ba_no_individual_status FOREIGN KEY (ba_no_individual_status) REFERENCES dbeel_nomenclature.individual_status(no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT c_fk_batch_ope_ope_st_id FOREIGN KEY (ope_st_id) REFERENCES belge.stations (site) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) INHERITS (dbeel.batch); --select * from belge.batch_ope; -- on peut mettre les ob_op_id dans le group by car structurellement ça rajoutera pas de lignes /* select sum(nbrind),ob_op_id,ob_id from joineel group by ob_op_id,ob_id union select 0 as sum, ob_op_id,ob_id from belge.electrofishing where ope_id not in ( select mea_ope_id from joineel);--874 */ -- nb_total INSERT INTO belge.batch_ope SELECT uuid_generate_v4() AS ba_id, species.no_id AS ba_no_species, stage.no_id AS ba_no_stage, value_type.no_id AS ba_no_value_type, biological_characteristic_type.no_id AS ba_no_biological_characteristic_type, joineel2.nb AS ba_quantity, individual_status.no_id AS ba_no_individual_status, 1 AS ba_batch_level, joineel2.* --contains ba_ob_id and ba_ba_id FROM dbeel_nomenclature.species, dbeel_nomenclature.stage, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type, dbeel_nomenclature.individual_status, (select ob_id,cast(NULL as uuid) as ba_ba_id,mea_ope_id as ope_id,ope_st_id,0 as pa_numero,sum(nbrind) as nb from joineel group by ob_op_id,ob_id,mea_ope_id,ope_st_id union select ob_id,cast(NULL as uuid) as ba_ba_id,ope_id,site as ope_st_id, 0 as pa_numero, 0 as nb from belge.electrofishing where ope_id not in ( select mea_ope_id from joineel) )joineel2 WHERE species.no_name='Anguilla anguilla' AND stage.no_name='Yellow eel' AND biological_characteristic_type.no_name='Number' AND individual_status.no_name='Alive' AND value_type.no_name='Raw data or Individual data'; --874 lines /* select * from batch_ope select * from joineel where numpas=12; select * from joineel where mea_ope_id=263 select * from joineel; select ob_op_id,ob_id,cast(NULL as uuid) as ba_ba_id,mea_ope_id as ope_id,1 as pa_numero,sum(nbrind) as nb from joineel where numpas=1 group by ob_op_id,ob_id,mea_ope_id */ -- op_nbp1 INSERT INTO belge.batch_ope SELECT uuid_generate_v4() AS ba_id, species.no_id AS ba_no_species, stage.no_id AS ba_no_stage, value_type.no_id AS ba_no_value_type, biological_characteristic_type.no_id AS ba_no_biological_characteristic_type, joineel2.nb AS ba_quantity, individual_status.no_id AS ba_no_individual_status, 2 AS ba_batch_level, joineel2.* --contains ba_ob_id and ba_ba_id FROM dbeel_nomenclature.species, dbeel_nomenclature.stage, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type, dbeel_nomenclature.individual_status, (select ob_id,cast(NULL as uuid) as ba_ba_id,mea_ope_id as ope_id,ope_st_id,1 as pa_numero,sum(nbrind) as nb from joineel where numpas=1 group by ob_op_id,ob_id,mea_ope_id,ope_st_id) as joineel2 WHERE species.no_name='Anguilla anguilla' AND stage.no_name='Yellow eel' AND biological_characteristic_type.no_name='Number p1' AND individual_status.no_name='Alive' AND value_type.no_name='Raw data or Individual data';--200 lines -- op_nbp2 INSERT INTO belge.batch_ope SELECT uuid_generate_v4() AS ba_id, species.no_id AS ba_no_species, stage.no_id AS ba_no_stage, value_type.no_id AS ba_no_value_type, biological_characteristic_type.no_id AS ba_no_biological_characteristic_type, joineel2.nb AS ba_quantity, individual_status.no_id AS ba_no_individual_status, 2 AS ba_batch_level, joineel2.* --contains ba_ob_id and ba_ba_id FROM dbeel_nomenclature.species, dbeel_nomenclature.stage, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type, dbeel_nomenclature.individual_status, (select ob_id,cast(NULL as uuid) as ba_ba_id,mea_ope_id as ope_id,ope_st_id,2 as pa_numero,sum(nbrind) as nb from joineel where numpas=2 group by ob_op_id,ob_id,mea_ope_id,ope_st_id) as joineel2 WHERE species.no_name='Anguilla anguilla' AND stage.no_name='Yellow eel' AND biological_characteristic_type.no_name='Number p2' AND individual_status.no_name='Alive' AND value_type.no_name='Raw data or Individual data';--96 lines -- op_nbp3 INSERT INTO belge.batch_ope SELECT uuid_generate_v4() AS ba_id, species.no_id AS ba_no_species, stage.no_id AS ba_no_stage, value_type.no_id AS ba_no_value_type, biological_characteristic_type.no_id AS ba_no_biological_characteristic_type, joineel2.nb AS ba_quantity, individual_status.no_id AS ba_no_individual_status, 2 AS ba_batch_level, joineel2.* --contains ba_ob_id and ba_ba_id FROM dbeel_nomenclature.species, dbeel_nomenclature.stage, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type, dbeel_nomenclature.individual_status, (select ob_id,cast(NULL as uuid) as ba_ba_id,mea_ope_id as ope_id,ope_st_id,3 as pa_numero,sum(nbrind) as nb from joineel where numpas=3 group by ob_op_id,ob_id,mea_ope_id,ope_st_id) as joineel2 WHERE species.no_name='Anguilla anguilla' AND stage.no_name='Yellow eel' AND biological_characteristic_type.no_name='Number p3' AND individual_status.no_name='Alive' AND value_type.no_name='Raw data or Individual data';--2 lines -- op_nbp4 INSERT INTO belge.batch_ope SELECT uuid_generate_v4() AS ba_id, species.no_id AS ba_no_species, stage.no_id AS ba_no_stage, value_type.no_id AS ba_no_value_type, biological_characteristic_type.no_id AS ba_no_biological_characteristic_type, joineel2.nb AS ba_quantity, individual_status.no_id AS ba_no_individual_status, 2 AS ba_batch_level, joineel2.* --contains ba_ob_id and ba_ba_id FROM dbeel_nomenclature.species, dbeel_nomenclature.stage, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type, dbeel_nomenclature.individual_status, (select ob_id,cast(NULL as uuid) as ba_ba_id,mea_ope_id as ope_id,ope_st_id,4 as pa_numero,sum(nbrind) as nb from joineel where numpas=4 group by ob_op_id,ob_id,mea_ope_id,ope_st_id) as joineel2 WHERE species.no_name='Anguilla anguilla' AND stage.no_name='Yellow eel' AND biological_characteristic_type.no_name='Number p4' AND individual_status.no_name='Alive' AND value_type.no_name='Raw data or Individual data';--1 line -- pas de densités /* INSERT INTO belge.batch_ope SELECT uuid_generate_v4() AS ba_id, species.no_id AS ba_no_species, stage.no_id AS ba_no_stage, value_type.no_id AS ba_no_value_type, biological_characteristic_type.no_id AS ba_no_biological_characteristic_type, joineel.op_nb AS ba_quantity, individual_status.no_id AS ba_no_individual_status, 1 AS ba_batch_level, joineel.* --contains ba_ob_id and ba_ba_id FROM dbeel_nomenclature.species, dbeel_nomenclature.stage, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type, dbeel_nomenclature.individual_status, (select ob_id as ba_ob_id, cast(NULL as uuid) as ba_ba_id,op_st_id,op_id,op_density as op_nb, cast(Null as integer) as pa_numero from belge.electrofishing) as joineel WHERE species.no_name='Anguilla anguilla' AND stage.no_name='Yellow eel' AND biological_characteristic_type.no_name='Density' AND individual_status.no_name='Alive' AND value_type.no_name='Raw data or Individual data'; */ -- Batch integration, the second level will reference the fish table select * from joineel select * from joineel join operationspeche on mea_ope_id=ope_id order by lonmax -- les anguilles de belgique sont grosses mais pas plus que ça.... -- A 100 km de la mer... -- Mais il y a eu des transports... update mesures_ang set lonmax=NULL where lonmax=9999;-- prétentieux -- les petites anguilles correspondent elles à des opérations ou les anguilles sont nombreuses (sites de déversements ?) select * from mesures_ang where mea_ope_id in (select ope_id from batch_ope where ba_batch_level=1 and ba_quantity>10) order by codsit,lonmax --1275 1276 1277=> civelles a virer --100BUR --1288 1290 => civelles --BERW_40222 lâché de civelles (correspondant à 1288 1290) --BERW_03260 189 182 --1338 site=BERW_04130 --18 select * from mesures_ang where codsit='100BUR' select * from mesures_ang where mea_ope_id not in (1275,1276,1277,1288,1290,1338,189,182) order by lonmax; select * from mesures_ang where lonmax<50 and datvis<'1998-01-01' update mesures_ang set lonmax=10*lonmax where lonmax<50 and datvis<'1998-01-01';--3 -- données de taille DROP TABLE if exists belge.batch_fish CASCADE; CREATE TABLE belge.batch_fish ( ba_ope_id integer, mea_id integer, --LIKE belge.fish_fi INCLUDING DEFAULTS INCLUDING CONSTRAINTS, CONSTRAINT pk_fish_fi_id PRIMARY KEY (ba_id), CONSTRAINT fk_fish_fi_ba_no_species_id FOREIGN KEY (ba_no_species) REFERENCES dbeel_nomenclature.species (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_fish_fi_ba_no_stage_id FOREIGN KEY (ba_no_stage) REFERENCES dbeel_nomenclature.stage (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_fish_fi_ba_value_type FOREIGN KEY (ba_no_value_type) REFERENCES dbeel_nomenclature.value_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_fish_fi_ba_no_biological_characteristic_type FOREIGN KEY (ba_no_biological_characteristic_type) REFERENCES dbeel_nomenclature.biological_characteristic_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_fish_fi_ba_no_individual_status FOREIGN KEY (ba_no_individual_status) REFERENCES dbeel_nomenclature.individual_status(no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT c_fk_fi_op_id FOREIGN KEY (ba_ope_id) REFERENCES belge.operationspeche (ope_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) INHERITS (dbeel.batch); select * from belge.batch_fish -- creation des lots avec 1 ind; INSERT INTO belge.batch_fish SELECT uuid_generate_v4() AS ba_id, species.no_id AS ba_no_species, stage.no_id AS ba_no_stage, value_type.no_id AS ba_no_value_type, biological_characteristic_type.no_id AS ba_no_biological_characteristic_type, 1 AS ba_quantity, individual_status.no_id AS ba_no_individual_status, 3 AS ba_batch_level, joineel.* --contains ba_ob_id and ba_ba_id FROM dbeel_nomenclature.species, dbeel_nomenclature.stage, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type, dbeel_nomenclature.individual_status, (select ob_id as ba_ob_id, cast(NULL as uuid) as ba_ba_id, mea_ope_id as ba_ope_id, mea_id from joineel where lonmax is not null and nbrind=1 ) as joineel WHERE species.no_name='Anguilla anguilla' AND stage.no_name='Yellow eel' AND biological_characteristic_type.no_name='Number' AND individual_status.no_name='Alive' AND value_type.no_name='Raw data or Individual data';--1211 DROP TABLE if exists belge.mensurationindiv_biol_charac CASCADE; CREATE TABLE belge.mensurationindiv_biol_charac ( CONSTRAINT pk_mensindivbiocho_id PRIMARY KEY (bc_id), CONSTRAINT fk_mensindivbiocho_bc_characteristic_type FOREIGN KEY (bc_no_characteristic_type) REFERENCES dbeel_nomenclature.biological_characteristic_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_mensindivbiocho_bc_value_type FOREIGN KEY (bc_no_value_type) REFERENCES dbeel_nomenclature.value_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_mensindivbiocho_bc_ba_id FOREIGN KEY (bc_ba_id) REFERENCES belge.batch_fish (ba_id) ON DELETE RESTRICT ON UPDATE CASCADE )INHERITS (dbeel.biological_characteristic); -- select * from belge.mensurationindiv_biol_charac -- tailles INSERT INTO belge.mensurationindiv_biol_charac SELECT uuid_generate_v4() AS bc_id, ba_id AS bc_ba_id, biological_characteristic_type.no_id AS bc_no_characteristic_type, value_type.no_id AS bc_no_value_type, lonmax AS bc_numvalue FROM dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type, belge.batch_fish, mesures_ang WHERE mesures_ang.mea_id=batch_fish.mea_id AND biological_characteristic_type.no_name = 'Length' AND value_type.no_name = 'Raw data or Individual data' ; --1211 lines -- select * from mesures_ang
Projection
Nous choissions de projeter ensemble les données d'ouvrage et les données de stations de pêche qui sont dans la table observation places.
----------------------------------------------------------- -- Jointure géographique entre les observations et la CCM ----------------------------------------------------------- ------ -- selection des opérations de belgiques: ------ DROP TABLE IF EXISTS belge.observation_places; create table belge.observation_places as( select * from dbeel.observation_places where op_gis_systemname='SPW' or op_gis_layername='OBSTACLES'); DROP TABLE IF EXISTS belge.meuse_operations_ccm_500; CREATE TABLE belge.meuse_operations_ccm_500 as ( SELECT distinct on (op_id) op_id, gid, wso1_id, min(distance) as distance, the_geom FROM ( SELECT op_id, gid , wso1_id, CAST(distance(r.the_geom, s.the_geom) as decimal(15,1)) as distance,s.the_geom FROM belge.observation_places As s INNER JOIN ccm21.riversegments r ON ST_DWithin(r.the_geom, s.the_geom,500) (beaucoup d'exclus sont des obstacles en dehors du CCM) WHERE s.the_geom IS NOT NULL ) AS sub GROUP BY op_id,distance, gid, wso1_id, the_geom ); alter table belge.meuse_operations_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 '', 'belge', 'meuse_operations_ccm_500', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom) FROM belge.meuse_operations_ccm_500 LIMIT 1; -- creation d'index, clé primaire, et constraintes qui vont bien alter table belge.meuse_operations_ccm_500 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); alter table belge.meuse_operations_ccm_500 add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL); alter table belge.meuse_operations_ccm_500 add CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); alter table belge.meuse_operations_ccm_500 ADD CONSTRAINT pk_id PRIMARY KEY(id); CREATE INDEX indexstation_ccm_500 ON belge.meuse_operations_ccm_500 USING GIST ( the_geom GIST_GEOMETRY_OPS ); --select*from belge.meuse_operations_ccm_500 ------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------ -- selection des opérations de l'ensemble des données de dbeel: DROP TABLE IF EXISTS dbeel.observation_places_ccm_500; CREATE TABLE dbeel.observation_places_ccm_500 as ( SELECT distinct on (op_id) op_id, gid, wso1_id, min(distance) as distance,op_gis_layername, the_geom FROM ( SELECT op_id, gid , wso1_id,op_gis_layername, CAST(distance(r.the_geom, s.the_geom) as decimal(15,1)) as distance,s.the_geom FROM dbeel.observation_places As s INNER JOIN ccm21.riversegments r ON ST_DWithin(r.the_geom, s.the_geom,500) --69931 observations sur 87204 lignes WHERE s.the_geom IS NOT NULL ) AS sub GROUP BY op_id,distance, gid, wso1_id, the_geom,op_gis_layername ); alter table dbeel.observation_places_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 '', 'dbeel', 'observation_places_ccm_500', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom) FROM dbeel.observation_places_ccm_500 LIMIT 1; -- creation d'index, clé primaire, et constraintes qui vont bien alter table dbeel.observation_places_ccm_500 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); alter table dbeel.observation_places_ccm_500 add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL); alter table dbeel.observation_places_ccm_500 add CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); alter table dbeel.observation_places_ccm_500 ADD CONSTRAINT pk_id PRIMARY KEY(id); CREATE INDEX indexstation_ccm_500 ON dbeel.observation_places_ccm_500 USING GIST ( the_geom GIST_GEOMETRY_OPS ); ---------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------
----------------------------------------------------------------------------- -- Creation de la table stations_ccm_500 avec l'id station et l'id de la CCM ----------------------------------------------------------------------------- -- Table pour les stations -- Juste la Meuse belge car on doit attendre les données de pêches pour la hollande et la France DROP TABLE IF EXISTS dbeel.stations_ccm_500; CREATE TABLE belge.stations_ccm_500 as ( SELECT distinct (site) site, gid, wso1_id, min(distance) as distance, the_geom FROM ( SELECT site, gid, wso1_id, distance, s.the_geom FROM dbeel.observation_places_CCM_500 s join belge.stationdbeel o ON o.op_id=s.op_id WHERE s.the_geom IS NOT NULL ORDER BY site) AS sub GROUP BY site, gid, gid, wso1_id, distance,the_geom -- 516 lignes pour Meuse ); alter table belge.stations_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 '', 'belge', 'stations_ccm_500', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom) FROM oria.oria_ccm_500 LIMIT 1; -- creation d'index, clé primaire, et constraintes qui vont bien alter table belge.stations_ccm_500 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); alter table belge.stations_ccm_500 add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL); alter table belge.stations_ccm_500 add CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); alter table belge.stations_ccm_500 ADD CONSTRAINT pk_id PRIMARY KEY(id); CREATE INDEX indexbelge_ccm_500 ON belge.stations_ccm_500 USING GIST ( the_geom GIST_GEOMETRY_OPS ); -- select * from belge.stations_ccm_500
Intégration des données de la dernière version de la BD_MAP dan dbeel
-- dump de bd_map sch public vers EDA2 public: pg_dump -h 1.100.1.6 -U postgres bd_map| psql -h 1.100.1.6 -U postgres eda2 -- transfers de public à ONEMA. Les tables existent déjà. ALTER TABLE zonepeche SET SCHEMA onema; ALTER TABLE version SET SCHEMA onema; ALTER TABLE taxon SET SCHEMA onema; ALTER TABLE tableauclassetaille SET SCHEMA onema; ALTER TABLE station SET SCHEMA onema; ALTER TABLE patho SET SCHEMA onema; ALTER TABLE passtaxon SET SCHEMA onema; ALTER TABLE optaxon SET SCHEMA onema; ALTER TABLE operation SET SCHEMA onema; ALTER TABLE mensurationindiv SET SCHEMA onema; ALTER TABLE codier SET SCHEMA onema; ALTER TABLE classe_taille_taxon SET SCHEMA onema; ALTER TABLE lotpeche SET SCHEMA onema; INSERT INTO dbeel.establishment (et_establishment_name) VALUES ('ONEMA'); INSERT INTO dbeel.data_provider(dp_name, dp_et_id) VALUES ('Laurent Beaulaton',1); -- Bon il est pas content ca il n y a pas de colonnes the_geom select st_abcisse, st_ordonnee from onema.station; ALTER TABLE onema.station ADD COLUMN the_geom geometry; UPDATE onema.station SET the_geom = ST_GeometryFromText ('POINT(' || st_abcisse|| ' ' || st_ordonnee || ')', 27572) select * from onema.station -- il faut trouver quelle colonne il prend pour the_geom DROP TABLE if exists onema.station_onema CASCADE; CREATE TABLE onema.station_onema ( LIKE onema.station, CONSTRAINT pk_so_op_id PRIMARY KEY (op_id), CONSTRAINT fk_so_observation_place_type_id FOREIGN KEY (op_no_observationplacetype) REFERENCES dbeel_nomenclature.observation_place_type (no_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT ) INHERITS (dbeel.observation_places); -- select * from onema.station_onema, the_geom c'ets la 8ième colonne -- select * from onema.station --> il faut placer the_geom au même niveau INSERT INTO onema.station_onema SELECT uuid_generate_v4() AS op_id, 'onema' AS op_gis_systemname , 'BDMAP' AS op_gis_layername, s.st_id AS op_gislocation, s.st_localisation AS op_placename, 10 AS op_no_observationplacetype, -- Sampling station NULL AS op_op_id, s.the_geom AS the_geom, s.st_id, s.st_altitude, s.st_abcisse, s.st_codecsp, s.st_codesie, s.st_datearret, s.st_datecreation, s.st_distancesource, s.st_distancemer, s.st_finalite, s.st_imageign, s.st_imagedept, s.st_lieudit, s.st_limites, s.st_localisation, s.st_longueur, s.st_moduleia, s.st_cd_naturecourseau, s.st_ordonnee, s.st_penteign, s.st_pkaval, s.st_raisremp, s.st_sbv, s.st_t_janvier, s.st_t_juillet, s.st_cd_typecourseau, s.st_cd_tet, s.st_st_id, s.st_cm_id, s.st_cx_id, s.st_th_id, s.st_eh_id, s.st_uh_id, s.st_dt_cre, s.st_dt_maj, s.st_qi_maj, s.st_masseeau, s.st_abcisse_l93, s.st_ordonnee_l93 FROM onema.station s ; DROP TABLE if exists onema.operation_onema CASCADE; CREATE TABLE onema.operation_onema ( LIKE onema.operation, CONSTRAINT pk_oo_ob_id PRIMARY KEY (ob_id), CONSTRAINT fk_oo_ob_op_id FOREIGN KEY (ob_op_id) REFERENCES onema.station_onema (op_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_oo_dp_id FOREIGN KEY (ob_dp_id) REFERENCES dbeel.data_provider (dp_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_oo_electrofishing_mean FOREIGN KEY (ef_no_electrofishing_mean) REFERENCES dbeel_nomenclature.electrofishing_mean (no_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_oo_electrofishing_method FOREIGN KEY (ef_no_fishingmethod) REFERENCES dbeel_nomenclature.scientific_observation_method (no_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_oo_ob_no_origin FOREIGN KEY (ob_no_origin) REFERENCES dbeel_nomenclature.observation_origin (no_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_oo_ob_no_period FOREIGN KEY (ob_no_period) REFERENCES dbeel_nomenclature.period_type (no_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_oo_ob_no_type FOREIGN KEY (ob_no_type) REFERENCES dbeel_nomenclature.observation_type (no_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT ) INHERITS (dbeel.electrofishing); -- select * from onema.station INSERT INTO onema.operation_onema SELECT uuid_generate_v4() AS ob_id , observation_origin.no_id AS ob_no_origin, observation_type.no_id AS ob_no_type, period_type.no_id AS ob_no_period, op_datedebut AS ob_starting_date, op_datedebut AS ob_ending_date, station.op_id AS ob_op_id, data_provider.dp_id AS ob_dp_id, -- ONEMA 61 AS ef_no_fishingmethod, -- TODO update this correctly here it is electrofishing unknown electrofishing_mean.no_id AS ef_no_electrofishing_mean, case when operation.op_surfaceechantillon >0 then operation.op_surfaceechantillon else NULL end AS ef_wetted_area, case when operation.op_longueur>0 then operation.op_longueur else NULL end AS ef_fished_length, case when operation.op_cs_largeurlameeau>0 then operation.op_cs_largeurlameeau else null end AS ef_fished_width, operation.op_tempspeche AS ef_duration, operation.op_nbrpassage AS ef_nbpas, operation.* FROM dbeel_nomenclature.observation_origin, dbeel_nomenclature.scientific_observation_method, dbeel_nomenclature.observation_type, dbeel_nomenclature.period_type, dbeel.data_provider, onema.station_onema AS station join onema.operation AS operation on station.st_id = operation.op_st_id, dbeel_nomenclature.electrofishing_mean WHERE observation_origin.no_name='Raw data' AND scientific_observation_method.no_id=61 AND observation_type.no_name='Electro-fishing' AND electrofishing_mean.no_name='Unknown' AND period_type.no_name='Daily' AND data_provider.dp_name='Laurent Beaulaton'; --------------------------------------------------------------------------------------- -- optaxon DROP TABLE if exists onema.optaxon_onema CASCADE; CREATE TABLE onema.optaxon_onema ( LIKE onema.optaxon, CONSTRAINT pk_opo_id PRIMARY KEY (ba_id), CONSTRAINT fk_opo_ba_no_species_id FOREIGN KEY (ba_no_species) REFERENCES dbeel_nomenclature.species (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_opo_ba_no_stage_id FOREIGN KEY (ba_no_stage) REFERENCES dbeel_nomenclature.stage (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_opo_ba_value_type FOREIGN KEY (ba_no_value_type) REFERENCES dbeel_nomenclature.value_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_opo_ba_no_biological_characteristic_type FOREIGN KEY (ba_no_biological_characteristic_type) REFERENCES dbeel_nomenclature.biological_characteristic_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_opo_ba_no_individual_status FOREIGN KEY (ba_no_individual_status) REFERENCES dbeel_nomenclature.individual_status(no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_opo_ba_ob_id FOREIGN KEY (ba_ob_id) REFERENCES onema.operation_onema(ob_id) --setting the constraint to the uppermost level dbeel.observations(ob_id) does not work ! ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_opo_ba_ba_id FOREIGN KEY (ba_ba_id) REFERENCES dbeel.batch(ba_id) -- here I'm setting the constraint to the uppermost level ON DELETE RESTRICT ON UPDATE CASCADE ) INHERITS (dbeel.batch); -- TODO: insert trigger INSERT INTO onema.optaxon_onema SELECT uuid_generate_v4() AS ba_id, species.no_id AS ba_no_species, stage.no_id AS ba_no_stage, value_type.no_id AS ba_no_value_type, biological_characteristic_type.no_id AS ba_no_biological_characteristic_type, optaxon.ot_effectif AS ba_quantity, individual_status.no_id AS ba_no_individual_status, 1 AS ba_batch_level, operation_onema.ob_id AS ba_ob_id, NULL AS ba_ba_id, optaxon.* FROM dbeel_nomenclature.species, dbeel_nomenclature.stage, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type, dbeel_nomenclature.individual_status, onema.operation_onema AS operation_onema, onema.optaxon WHERE species.no_name='Anguilla anguilla' AND stage.no_name='Yellow & silver eel mixed' AND biological_characteristic_type.no_name='Number' AND value_type.no_name='Raw data or Individual data' AND individual_status.no_name='Alive' AND operation_onema.op_id = optaxon.ot_op_id AND optaxon.ot_ta_id=2; --9781 lines UPDATE onema.optaxon_onema SET ba_quantity = 0 WHERE ba_quantity IS NULL; --0 lines --------------------------------------------------------------------------------------- DROP TABLE if exists onema.passtaxon_onema CASCADE; CREATE TABLE onema.passtaxon_onema ( LIKE onema.passtaxon, CONSTRAINT pk_pao_id PRIMARY KEY (ba_id), CONSTRAINT fk_pao_ba_no_species_id FOREIGN KEY (ba_no_species) REFERENCES dbeel_nomenclature.species (no_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_pao_ba_no_stage_id FOREIGN KEY (ba_no_stage) REFERENCES dbeel_nomenclature.stage (no_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_pao_ba_value_type FOREIGN KEY (ba_no_value_type) REFERENCES dbeel_nomenclature.value_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_pao_ba_no_biological_characteristic_type FOREIGN KEY (ba_no_biological_characteristic_type) REFERENCES dbeel_nomenclature.biological_characteristic_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_pao_ba_no_individual_status FOREIGN KEY (ba_no_individual_status) REFERENCES dbeel_nomenclature.individual_status(no_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_pao_ba_ob_id FOREIGN KEY (ba_ob_id) REFERENCES onema.operation_onema(ob_id) --setting the constraint to the uppermost level dbeel.observations(ob_id) does not work ! ON DELETE RESTRICT ON UPDATE CASCADE -- CONSTRAINT fk_pao_ba_ba_id FOREIGN KEY (ba_ba_id) -- REFERENCES dbeel.batch(ba_id) -- here I'm setting the constraint to the uppermost level -- ON DELETE RESTRICT ON UPDATE CASCADE ) INHERITS (dbeel.batch); -- TODO: insert trigger INSERT INTO onema.passtaxon_onema SELECT uuid_generate_v4() AS ba_id, species.no_id AS ba_no_species, stage.no_id AS ba_no_stage, value_type.no_id AS ba_no_value_type, biological_characteristic_type.no_id AS ba_no_biological_characteristic_type, passtaxon.pa_effectif AS ba_quantity, individual_status.no_id AS ba_no_individual_status, 2 AS ba_batch_level, operation_onema.ob_id AS ba_ob_id, NULL AS ba_ba_id, passtaxon.* FROM dbeel_nomenclature.species, dbeel_nomenclature.stage, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type, dbeel_nomenclature.individual_status, onema.operation_onema AS operation_onema, onema.passtaxon WHERE species.no_name='Anguilla anguilla' AND stage.no_name='Yellow & silver eel mixed' AND biological_characteristic_type.no_name='Number' AND value_type.no_name='Raw data or Individual data' AND individual_status.no_name='Alive' AND operation_onema.op_id = passtaxon.pa_op_id; -- select * from onema.optaxon_onema -- UPDATE onema.passtaxon_onema SET quantity = 0 WHERE quantity ISNULL; UPDATE onema.passtaxon_onema pao SET ba_ba_id=opo.ba_id FROM onema.optaxon_onema opo WHERE opo.ba_ob_id = pao.ba_ob_id; -- select * from onema.passtaxon_onema limit 100; DELETE FROM onema.passtaxon_onema where pa_ta_id!=2; -- select * from onema.passtaxon_onema order by pa_dt_maj --15396 rows, il y a 2011, 2012, 2013 /*-------------------------------------------------------------------------------------- lotpeche --------------------------------------------------------------------------------------*/ DROP TABLE if exists onema.lotpeche_onema CASCADE; CREATE TABLE onema.lotpeche_onema ( LIKE onema.lotpeche, CONSTRAINT pk_lopeo_id PRIMARY KEY (ba_id), CONSTRAINT fk_lopeo_ba_no_species_id FOREIGN KEY (ba_no_species) REFERENCES dbeel_nomenclature.species (no_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_lopeo_ba_no_stage_id FOREIGN KEY (ba_no_stage) REFERENCES dbeel_nomenclature.stage (no_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_lopeo_ba_value_type FOREIGN KEY (ba_no_value_type) REFERENCES dbeel_nomenclature.value_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_lopeo_ba_no_biological_characteristic_type FOREIGN KEY (ba_no_biological_characteristic_type) REFERENCES dbeel_nomenclature.biological_characteristic_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_lopeo_ba_no_individual_status FOREIGN KEY (ba_no_individual_status) REFERENCES dbeel_nomenclature.individual_status(no_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_lopeo_ba_ob_id FOREIGN KEY (ba_ob_id) REFERENCES onema.operation_onema(ob_id) --setting the constraint to the uppermost level dbeel.observations(ob_id) does not work ! ON DELETE RESTRICT ON UPDATE CASCADE -- CONSTRAINT fk_lopeo_ba_ba_id FOREIGN KEY (ba_ba_id) -- REFERENCES dbeel.batch(ba_id) -- Il ne peut pas y avoir de contraintes de clé étrangères dans la table -- ON DELETE RESTRICT ON UPDATE CASCADE ) INHERITS (dbeel.batch); -- TODO: insert trigger -- this is way too long, I'm only inserting the eels SELECT * FROM onema.operation_onema join onema.lotpeche on operation_onema.op_id = lotpeche.lo_op_id limit 10; INSERT INTO onema.lotpeche_onema SELECT uuid_generate_v4() AS ba_id, species.no_id AS ba_no_species, stage.no_id AS ba_no_stage, value_type.no_id AS ba_no_value_type, biological_characteristic_type.no_id AS ba_no_biological_characteristic_type, lotpeche.lo_effectif AS ba_quantity, individual_status.no_id AS ba_no_individual_status, 3 AS ba_batch_level, operation_onema.ob_id AS ba_ob_id, NULL AS ba_ba_id, lotpeche.* FROM dbeel_nomenclature.species, dbeel_nomenclature.stage, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type, dbeel_nomenclature.individual_status, onema.operation_onema join onema.lotpeche on operation_onema.op_id = lotpeche.lo_op_id WHERE species.no_name='Anguilla anguilla' AND stage.no_name='Yellow & silver eel mixed' AND biological_characteristic_type.no_name='Number' AND value_type.no_name='Raw data or Individual data' AND individual_status.no_name='Alive' AND lo_ta_id=2; UPDATE onema.lotpeche_onema lopeo SET ba_ba_id = pao.ba_id FROM onema.passtaxon_onema pao WHERE lopeo.ba_ob_id = pao.ba_ob_id AND lopeo.lo_numeropassage = pao.pa_numero; -- select * from onema.lotpeche_onema limit 100; --------------------------------------------------------------------------------------- -- Biological characteristic for lotpeche --------------------------------------------------------------------------------------- -- Ici les résultats biologiques sont par lot du coup pour chacun d'eux on a Lt min, taille minimum, Lt max, le poid du lot, poid moyen individuel DROP TABLE if exists onema.lotpeche_biol_charac_onema CASCADE; CREATE TABLE onema.lotpeche_biol_charac_onema ( CONSTRAINT lotpeche_biol_charac_onema_id PRIMARY KEY (bc_id), CONSTRAINT fk_lopebiocho_bc_characteristic_type FOREIGN KEY (bc_no_characteristic_type) REFERENCES dbeel_nomenclature.biological_characteristic_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_lopebiocho_bc_value_type FOREIGN KEY (bc_no_value_type) REFERENCES dbeel_nomenclature.value_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_lopebiocho_bc_ba_id FOREIGN KEY (bc_ba_id) REFERENCES onema.lotpeche_onema (ba_id) ON DELETE RESTRICT ON UPDATE CASCADE ) INHERITS (dbeel.biological_characteristic); -- TODO: insert trigger -- Lt min --> lot G et lot L INSERT INTO onema.lotpeche_biol_charac_onema SELECT uuid_generate_v4() AS bc_id, ba_id AS bc_ba_id, biological_characteristic_type.no_id AS bc_no_characteristic_type, value_type.no_id AS bc_no_value_type, lo_taillemin/10 AS bc_numvalue FROM onema.lotpeche_onema, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type WHERE (lo_cd_type = 1059 OR lo_cd_type =1061) AND lo_taillemin>0 AND biological_characteristic_type.no_name = 'Lower length' AND value_type.no_name = 'Class value'; -- Lt max --> lot G et lot L INSERT INTO onema.lotpeche_biol_charac_onema SELECT uuid_generate_v4() AS bc_id, ba_id AS bc_ba_id, biological_characteristic_type.no_id AS bc_no_characteristic_type, value_type.no_id AS bc_no_value_type, lo_taillemax/10 AS bc_numvalue FROM onema.lotpeche_onema, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type WHERE (lo_cd_type = 1059 OR lo_cd_type =1061) AND lo_taillemin>0 AND biological_characteristic_type.no_name = 'Upper length' AND value_type.no_name = 'Class value'; -- P total --> lot G, lot I, lot L INSERT INTO onema.lotpeche_biol_charac_onema SELECT uuid_generate_v4() AS bc_id, ba_id AS bc_ba_id, biological_characteristic_type.no_id AS bc_no_characteristic_type, value_type.no_id AS bc_no_value_type, lo_poids AS biological_characteristic_numvalue FROM onema.lotpeche_onema, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type WHERE (lo_cd_type = 1059 OR lo_cd_type =1060 OR lo_cd_type =1061) AND lo_poids>0 AND lo_cd_codeestimpoids=752 -- only real weight AND biological_characteristic_type.no_name = 'Weight' AND value_type.no_name = 'Cumulated data' ;-- 3527 -- L individual --> lot N INSERT INTO onema.lotpeche_biol_charac_onema SELECT uuid_generate_v4() AS bc_id, ba_id AS bc_ba_id, biological_characteristic_type.no_id AS bc_no_characteristic_type, value_type.no_id AS bc_no_value_type, lo_taille/10 AS biological_characteristic_numvalue FROM onema.lotpeche_onema, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type WHERE lo_cd_type = 1062 AND lo_taille>0 AND biological_characteristic_type.no_name = 'Length' AND value_type.no_name = 'Raw data or Individual data' ; --161428 -- P individual --> lot N INSERT INTO onema.lotpeche_biol_charac_onema SELECT uuid_generate_v4() AS bc_id, ba_id AS bc_ba_id, biological_characteristic_type.no_id AS bc_no_characteristic_type, value_type.no_id AS bc_no_value_type, lo_poids AS biological_characteristic_numvalue FROM onema.lotpeche_onema, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type WHERE lo_cd_type = 1062 AND lo_poids>0 AND lo_cd_codeestimpoids=752 -- only real weight AND biological_characteristic_type.no_name = 'Weight' AND value_type.no_name = 'Raw data or Individual data' -- 77189 ; -- select * from onema.lotpeche_biol_charac_onema --280018 rows --------------------------------------------------------------------------------------- -- mensurationindiv ------------------------------------------------------------------------------------ DROP TABLE if exists onema.mensurationindiv_onema CASCADE; CREATE TABLE onema.mensurationindiv_onema ( LIKE onema.mensurationindiv, CONSTRAINT pk_mensindivonema_id PRIMARY KEY (ba_id), CONSTRAINT fk_mensindivonema_ba_no_species_id FOREIGN KEY (ba_no_species) REFERENCES dbeel_nomenclature.species (no_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_mensindivonema_ba_no_stage_id FOREIGN KEY (ba_no_stage) REFERENCES dbeel_nomenclature.stage (no_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_mensindivonema_ba_value_type FOREIGN KEY (ba_no_value_type) REFERENCES dbeel_nomenclature.value_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_mensindivonema_ba_no_biological_characteristic_type FOREIGN KEY (ba_no_biological_characteristic_type) REFERENCES dbeel_nomenclature.biological_characteristic_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_mensindivonema_ba_no_individual_status FOREIGN KEY (ba_no_individual_status) REFERENCES dbeel_nomenclature.individual_status(no_id) MATCH SIMPLE ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_mensindivonema_ba_ob_id FOREIGN KEY (ba_ob_id) REFERENCES onema.operation_onema(ob_id) --setting the constraint to the uppermost level dbeel.observations(ob_id) does not work ! ON DELETE RESTRICT ON UPDATE CASCADE -- CONSTRAINT fk_lopeo_ba_ba_id FOREIGN KEY (ba_ba_id) -- REFERENCES dbeel.batch(ba_id) -- Il ne peut pas y avoir de contraintes de clé étrangères dans la table -- ON DELETE RESTRICT ON UPDATE CASCADE ) INHERITS (dbeel.batch); (select * from onema.operation_onema join onema.lotpeche_onema on ob_id=ba_ob_id join onema.mensurationindiv on mi_lo_id=lotpeche_onema.lo_id limit 10) as sub INSERT INTO onema.mensurationindiv_onema SELECT uuid_generate_v4() AS ba_id, species.no_id AS ba_no_species, stage.no_id AS ba_no_stage, value_type.no_id AS ba_no_value_type, biological_characteristic_type.no_id AS ba_no_biological_characteristic_type, 1 AS ba_quantity, individual_status.no_id AS ba_no_individual_status, 4 AS ba_batch_level, joineel. * --contains ba_ob_id and ba_ba_id FROM dbeel_nomenclature.species, dbeel_nomenclature.stage, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type, dbeel_nomenclature.individual_status, (select ob_id as ba_ob_id,ba_id AS ba_ba_id, mensurationindiv.* from onema.operation_onema join onema.lotpeche_onema on ob_id=ba_ob_id join onema.mensurationindiv on mi_lo_id=lotpeche_onema.lo_id where lo_ta_id=2) as joineel WHERE species.no_name='Anguilla anguilla' AND stage.no_name='Yellow & silver eel mixed' AND biological_characteristic_type.no_name='Number' AND individual_status.no_name='Alive' AND value_type.no_name='Raw data or Individual data';--42253 lines -- select * from onema.mensurationindiv_onema --------------------------------------------------------------------------------------- -- Biological characteristic for mensurationindiv DROP TABLE if exists onema.mensurationindiv_biol_charac_onema CASCADE; CREATE TABLE onema.mensurationindiv_biol_charac_onema ( CONSTRAINT pk_mensindivbiocho_id PRIMARY KEY (bc_id), CONSTRAINT fk_mensindivbiocho_bc_characteristic_type FOREIGN KEY (bc_no_characteristic_type) REFERENCES dbeel_nomenclature.biological_characteristic_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_mensindivbiocho_bc_value_type FOREIGN KEY (bc_no_value_type) REFERENCES dbeel_nomenclature.value_type (no_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_mensindivbiocho_bc_ba_id FOREIGN KEY (bc_ba_id) REFERENCES onema.mensurationindiv_onema (ba_id) ON DELETE RESTRICT ON UPDATE CASCADE )INHERITS (dbeel.biological_characteristic); -- TODO: insert trigger INSERT INTO onema.mensurationindiv_biol_charac_onema SELECT uuid_generate_v4() AS bc_id, ba_id AS bc_ba_id, biological_characteristic_type.no_id AS bc_no_characteristic_type, value_type.no_id AS bc_no_value_type, mi_poids AS bc_numvalue FROM onema.mensurationindiv_onema, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type WHERE mi_cd_codeestimationpoids = 752 AND mi_poids>0 AND biological_characteristic_type.no_name = 'Weight' AND value_type.no_name = 'Raw data or Individual data' ; --146 lines INSERT INTO onema.mensurationindiv_biol_charac_onema SELECT uuid_generate_v4() AS bc_id, ba_id AS bc_ba_id, biological_characteristic_type.no_id AS bc_no_characteristic_type, value_type.no_id AS bc_no_value_type, mi_taille AS bc_numvalue FROM onema.mensurationindiv_onema, dbeel_nomenclature.biological_characteristic_type, dbeel_nomenclature.value_type WHERE mi_taille>0 AND biological_characteristic_type.no_name = 'Length' AND value_type.no_name = 'Raw data or Individual data' ; --42252 (contre 40721 avant) -- select * from onema.operation_onema order by ob_starting_date -- select * from dbeel.electrofishing where extract(year from ob_starting_date) >2010 order by ob_starting_date -- c'est ok elles sont dans la dbeel
-- On refais la table observation_place_500 du coup avec la nouvelle version BD_MAP ( et IDEM pour la table station_500 avec le même script) --select * from dbeel.observation_places where op_gis_layername='BDMAP' -- 13059 --select * from dbeel.observation_places where op_gis_layername='BDMAP' and the_geom is NULL -- 113 (c'est toujours les mêmes répétées par ans. select st_srid(the_geom) from dbeel.observation_places group by st_srid(the_geom) -- y a 3035 et lambert 93 update dbeel.observation_places set the_geom=st_transform(the_geom,3035) DROP TABLE IF EXISTS dbeel.observation_places_ccm_500; CREATE TABLE dbeel.observation_places_ccm_500 as ( SELECT distinct on (op_id) op_id, gid, wso1_id, min(distance) as distance,op_gis_layername, the_geom FROM ( SELECT op_id, gid , wso1_id,op_gis_layername, CAST(distance(r.the_geom, s.the_geom) as decimal(15,1)) as distance,s.the_geom FROM dbeel.observation_places As s INNER JOIN ccm21.riversegments r ON ST_DWithin(r.the_geom, s.the_geom,500) --69931 observations sur 87204 lignes WHERE s.the_geom IS NOT NULL ) AS sub GROUP BY op_id,distance, gid, wso1_id, the_geom,op_gis_layername ); alter table dbeel.observation_places_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 '', 'dbeel', 'observation_places_ccm_500', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom) -- FROM dbeel.observation_places_ccm_500 LIMIT 1; -- creation d'index, clé primaire, et constraintes qui vont bien alter table dbeel.observation_places_ccm_500 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); alter table dbeel.observation_places_ccm_500 add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL); alter table dbeel.observation_places_ccm_500 add CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); alter table dbeel.observation_places_ccm_500 ADD CONSTRAINT pk_id PRIMARY KEY(id); CREATE INDEX indexstation_ccm_500 ON dbeel.observation_places_ccm_500 USING GIST ( the_geom GIST_GEOMETRY_OPS );
--------------------------------------------------------------------------------------------------------------- ---------------- Table station_CCM_500 france_Belge_Meuse---------------- --------------------------------------------------------------------------------------------------------------- ALTER TABLE onema.station_onema RENAME COLUMN st_id TO site; ALTER TABLE belge.stationdbeel ALTER COLUMN site TYPE bigint; ALTER TABLE onema.station_onema ALTER COLUMN site TYPE character varying; DROP TABLE IF EXISTS dbeel.meuse_stations_ccm_500; CREATE TABLE dbeel.meuse_stations_ccm_500 as ( SELECT n.site, n.gid, n.wso1_id, min(n.distance) as distance, n.the_geom FROM ( SELECT site, gid, wso1_id, distance, s.the_geom FROM dbeel.observation_places_CCM_500 s join belge.stationdbeel o ON o.op_id=s.op_id --516 lignes union ALL SELECT p.site, gid, m.wso1_id, m.distance, p.the_geom FROM ( select o.gid, o.wso1_id, o.op_id, o.distance from dbeel.observation_places_CCM_500 o JOIN europe.wso1 e ON o.wso1_id=e.wso1_id where area='Meuse') m join onema.station_onema p ON p.op_id=m.op_id ) n --901 lignes WHERE n.the_geom IS NOT NULL GROUP BY n.site, n.gid, n.wso1_id, distance, n.the_geom -- 901 order by n.wso1_id, n.site ); select * from dbeel.meuse_stations_ccm_500 alter table dbeel.meuse_stations_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 '', 'dbeel', 'meuse_stations_ccm_500', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom) FROM dbeel.meuse_stations_ccm_500 LIMIT 1; -- creation d'index, clé primaire, et constraintes qui vont bien alter table dbeel.meuse_stations_ccm_500 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); alter table dbeel.meuse_stations_ccm_500 add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL); alter table dbeel.meuse_stations_ccm_500 add CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); alter table dbeel.meuse_stations_ccm_500 ADD CONSTRAINT pk_id PRIMARY KEY(id); CREATE INDEX indexmeuse_FB_ccm_500 ON dbeel.meuse_stations_ccm_500 USING GIST ( the_geom GIST_GEOMETRY_OPS );
Meuse par EMU et par pays jointure avec CCM
DROP TABLE IF EXISTS belge.join_emu_wso; create table belge.join_emu_wso as select wso1_id,sub2.gid,emu_name_short,emu_coun_abrev,emu_cty_id from ( select * from ccm21.riversegments where wso1_id in (select wso1_id from europe.wso where area='Meuse') )sub join (select * from uga2010.t_emuagreg_ema where emu_coun_abrev='NL' or emu_coun_abrev='FR' or emu_coun_abrev='BE') sub2 on st_intersects(geom,the_geom) where wso_id in (select wso_id from europe.wso where area='Meuse');
Recherche des wso1_id où il y a eu des déversements
select b.wso1_id, a.ob_op_id from belge.electrofishing a JOIN dbeel.observation_places_ccm_500 b ON b.op_id=a.ob_op_id where a.ope_id in (1275,1276,1277,1288,1290,1338) -- wso1_id: 328521,328497,328314
Création d'une table pour avoir les prédictions par sous-bassins
-- Table des sous-bassins dans postgresql pour la Belgique DROP TABLE if exists belge.sous_bassin; CREATE TABLE belge.sous_bassin as select wso4_id, wso1_id from ccm21.catchments where wso4_id in (select c.wso4_id from ccm21.catchments c join europe.wso e on e.wso_id=c.wso_id where e.area='Meuse' group by wso4_id); -- 32 wso4 - 123283 rows ALTER TABLE belge.sous_bassin ADD COLUMN ss_bassin character varying (50); UPDATE belge.sous_bassin SET ss_bassin = 'Amblève' where wso4_id = '291590'; UPDATE belge.sous_bassin SET ss_bassin = 'Sambre' where wso4_id = '291589'; UPDATE belge.sous_bassin SET ss_bassin = 'Vesdre' where wso4_id = '291579'; UPDATE belge.sous_bassin SET ss_bassin = 'Semois-Cher' where wso4_id in ('291647','291701','291702','291676','291705'); UPDATE belge.sous_bassin SET ss_bassin = 'Lesse' where wso4_id in ('291605','291618','291613'); UPDATE belge.sous_bassin SET ss_bassin = 'Meuse amont - Oise' where wso4_id in ('291620','291604','291586'); UPDATE belge.sous_bassin SET ss_bassin = 'Ourthe' where wso4_id in ('291588','291578'); UPDATE belge.sous_bassin SET ss_bassin = 'Meuse avale' where wso4_id in ('291577','291586'); select * from belge.sous_bassin select ss_bassin from belge.sous_bassin group by 1 UPDATE belge.sous_bassin SET ss_bassin = 'Semois-Chiers' where ss_bassin = 'Semois-Cher'; UPDATE belge.sous_bassin SET ss_bassin = 'Partie Flandre' where wso4_id in ('291551','291527','0'); UPDATE belge.sous_bassin SET ss_bassin = 'Tete de bassin' where wso4_id = '291823'; UPDATE belge.sous_bassin SET ss_bassin = 'Meuse moyenne française' where wso4_id = '291682'; UPDATE belge.sous_bassin SET ss_bassin = 'Le Chiers' where wso4_id in ('291703','291705'); UPDATE belge.sous_bassin SET ss_bassin = 'Meuse avale française' where wso4_id = '291651'; -- en tout 159 rows
Last modified 7 years ago
Last modified on Jun 1, 2018 6:40:18 PM
