| 66 | | -- insertion données Laura |
| 67 | | create schema belge; |
| 68 | | alter table stations set schema belge; |
| 69 | | alter table mesures_ang set schema belge; |
| 70 | | alter table obstacles set schema belge; |
| 71 | | alter table anguilles set schema belge; |
| 72 | | |
| 73 | | |
| 74 | | set search_path to belge,public,dbeel; |
| 75 | | select * from belge.stations; |
| 76 | | select st_srid(the_geom) from belge.stations;--103300 |
| 77 | | --103300 est le code ESRI mais ce n'est pas un ESPG qui est 31370 |
| 78 | | update belge.stations set the_geom=st_setsrid(the_geom,31370); |
| 79 | | update belge.stations set the_geom=st_transform(the_geom,3035);--2484 |
| 80 | | alter table belge.stations ADD CONSTRAINT station_pkey PRIMARY KEY (st_id ), |
| 81 | | CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), |
| 82 | | CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL), |
| 83 | | CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 27572), |
| 84 | | CONSTRAINT geometry_valid_check CHECK (isvalid(the_geom)); |
| 85 | | |
| 86 | | |
| 87 | | --Meuse |
| 88 | | INSERT INTO dbeel.establishment (et_establishment_name) VALUES ('SPW'); |
| 89 | | INSERT INTO dbeel.data_provider(dp_name, dp_et_id) VALUES ('Laura',7); |
| 90 | | |
| 91 | | alter table stations rename to operations; |
| 92 | | create table stations as select distinct on (site) site, organisme, eau,commune, lambex,lambey, code_precxy, the_geom from operations |
| 93 | | where code_typpec= 'PECHEELECTRIQUE'; |
| 94 | | alter table stations add constraint c_pk_stations PRIMARY KEY (site); |
| 95 | | |
| 96 | | DROP TABLE if exists belge.stationdbeel CASCADE; |
| 97 | | CREATE TABLE belge.stationdbeel ( |
| 98 | | LIKE belge.stations, |
| 99 | | CONSTRAINT pk_so_op_id PRIMARY KEY (op_id), |
| 100 | | CONSTRAINT fk_so_observation_place_type_id FOREIGN KEY (op_no_observationplacetype) |
| 101 | | REFERENCES dbeel_nomenclature.observation_place_type (no_id) |
| 102 | | MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT |
| 103 | | ) INHERITS (dbeel.observation_places); |
| 104 | | |
| 105 | | INSERT INTO belge.stationdbeel |
| 106 | | SELECT uuid_generate_v4() AS op_id, |
| 107 | | 'SPW' AS op_gis_systemname , |
| 108 | | 'STATIONS' AS op_gis_layername, |
| 109 | | st.site AS op_gislocation, |
| 110 | | site AS op_placename, |
| 111 | | 10 AS op_no_observationplacetype, -- Sampling station |
| 112 | | NULL AS op_op_id, |
| 113 | | the_geom, |
| 114 | | site, organisme, eau,commune, lambex,lambey, code_precxy FROM belge.stations st ; -- 563 lines |
| 115 | | |
| 116 | | select * from stationdbeel order by site |
| 117 | | ----- |
| 118 | | -- Pour intégrer les données de pêche il faut détailler le nombre d'anguilles par passage |
| 119 | | -- Intégration dans la table opérations |
| 120 | | |
| 121 | | select numpas from belge.mesures_ang group by numpas -- de 1 à 4 et 12? mail SEP |
| 122 | | select nb_pas from belge.operations group by nb_pas -- de 1 à 4 passages |
| 123 | | select*from belge.mesures_ang order by id -- données en double idem pour anguilles |
| 124 | | select*from belge.operations order by id -- données en double egalement |
| 125 | | select*from belge.stations order by site,organisme,eau -- pas en double |
| 126 | | |
| 127 | | select m.nbrind |
| 128 | | FROM belge.operations op JOIN belge.mesures_ang m ON (m.codsit=op.site AND m.datvis=op.dates) where numpas=1 |
| 129 | | (select m.nbrind |
| 130 | | FROM belge.operations op JOIN belge.mesures_ang m ON (m.codsit=op.site AND m.datvis=op.dates) where numpas=2 |
| 131 | | (select m.nbrind as nbp3 |
| 132 | | FROM belge.operations op JOIN belge.mesures_ang m ON (m.codsit=op.site AND m.datvis=op.dates) where numpas=3 |
| 133 | | (select m.nbrind as nbp4 |
| 134 | | FROM belge.operations op JOIN belge.mesures_ang m ON (m.codsit=op.site AND m.datvis=op.dates) where numpas=4 |
| 135 | | |
| 136 | | -- Rerentrer le tableau mesures_ang ou supprimer les lignes doubles |
| 137 | | -- Créer 4 colonnes dans opérations |
