Changes between Version 5 and Version 6 of Meuse dbeel

Timestamp:

May 26, 2014 4:45:18 PM (11 years ago)

Author:

cedric

Comment:

--

Meuse dbeel

@@ -6 +6 @@
 mettre à jour avec des données plus récentes.
  [[Image(source:data/Docs/trac/Meuse/pechefrancebelgique.jpg)]] [[BR]]
+{{{#!sql
 
+-- 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.