Changes between Version 3 and Version 4 of Meuse_dams

Timestamp:

Jun 2, 2014 3:53:41 PM (11 years ago)

Author:

cedric

Comment:

--

Meuse_dams

@@ -9 +9 @@
 #!sql 
 
-
+-- Insertion des ouvrages de la Meuse Belge dans la dbeel
 
 set search_path to belge,public;
@@ -117 +117 @@
 }}}
 
-
+{{{ 
+#!sql 
+
+--Insertion des ouvrages français (ROE) dans la dbeel
+-- select * from obstacles
+
+set search_path to belge,onema,public,dbeel;
+select * from roe limit 10;
+alter table roe set schema onema;
+
+-- il faut d'abord rentrer un lieu
+create table onema.roecourt as (select "Id_ROE",
+        "roe_Nom",
+        "bassin_hydrographique",
+        "bassin_administratif",
+        "deptCd",
+        "deptNom",
+        "commCd",
+        "commNom",
+        "ObjFusion_id",
+        "IdTopo",
+        "nomTopo",
+        "IdTrCart",
+        "nomCart",
+        "etatNom",
+        "roe_typeCd",
+        "roe_typeNom",
+        "roe_stypeCd",
+        "roe_stypeNom",
+        "roe_stypEmCd1",
+        "roe_stypEmNom1",
+        "roe_stypEmCd2",
+        "roe_stypEmNom2",
+        "roe_stypEmCd3",
+        "roe_stypEmNom3",
+        "roe_fnt1_code",
+        "roe_fnt1_nom",
+        "roe_fnt2_code",
+        "roe_fnt2_nom",
+        "roe_fnt3_code",
+        "roe_fnt3_nom",
+        "roe_staCd",
+        "roe_staNom",
+        "roe_etatCd",
+        "roe_dateModif",
+        "roe_source",
+        "pre_ice_hauteur_terrain",
+        "pre_ice_hauteur_chute",
+        "pre_ice_nom_2",
+        "pre_ice_date_creation_ouvrage",
+        "pre_ice_usage1",
+        "pre_ice_usage2",
+        "pre_ice_usage3",
+        "pre_ice_usage4",
+        "pre_ice_fpi1",
+        "pre_ice_fpi2",
+        "pre_ice_fpi3",
+        "pre_ice_fpi4",
+        "pre_ice_fpi5",
+        "pre_ice_note_fr_Anguille",
+        "roe_commentaire_validation",
+        "roe_commentaire_suppression"
+        FROM onema.roe)
+
+
+DROP TABLE if exists onema.roedbeel CASCADE;
+CREATE TABLE onema.roedbeel (
+        "Id_ROE" character varying,
+        CONSTRAINT pk_obs_op_id PRIMARY KEY (op_id),
+        CONSTRAINT c_uk_id_roe UNIQUE ("Id_ROE"),
+        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 belge.ouvragedbeel;
+
+INSERT INTO onema.roedbeel
+        SELECT  uuid_generate_v4() AS op_id,
+        '3035' AS op_gis_systemname ,
+        'roe' AS op_gis_layername, 
+        "Id_ROE" AS op_gislocation,
+        "roe_Nom" AS op_placename,
+        11 AS op_no_observationplacetype, -- Obstacle location
+        NULL AS  op_op_id,
+        geom as the_geom,
+        "Id_ROE" -- on a rajouté qu'une seule colonne, seulement l'identifiant de l'ouvrage....
+         FROM onema.roe 
+         where "roe_staCd"=1 ; -- 80262 lines
+
+--select * from onema.roedbeel
 
   
-
-
+DROP TABLE if exists onema.physical_obstruction CASCADE;
+CREATE TABLE onema.physical_obstruction(
+  LIKE onema.roecourt,
+  CONSTRAINT physical_obstruction_id PRIMARY KEY (ob_id),
+  CONSTRAINT fk_dp FOREIGN KEY (ob_dp_id) REFERENCES dbeel.data_provider (dp_id),
+  CONSTRAINT fk_ob_origin FOREIGN KEY (ob_no_origin)REFERENCES dbeel_nomenclature.observation_origin (no_id) ,
+  CONSTRAINT fk_ob_period FOREIGN KEY (ob_no_period) REFERENCES dbeel_nomenclature.period_type (no_id) ,
+  CONSTRAINT fk_ob_type FOREIGN KEY (ob_no_type) REFERENCES dbeel_nomenclature.observation_type (no_id) ,
+  CONSTRAINT fk_po_obstruction_passability FOREIGN KEY (po_no_obstruction_passability)REFERENCES dbeel_nomenclature.obstruction_impact (no_id) 
+) INHERITS (dbeel.physical_obstruction);
+
+-- select * from belge.physical_obstruction
+--select "pre_ice_note_fr_Anguille",count(*) from onema.roe group by "pre_ice_note_fr_Anguille"
+INSERT INTO onema.physical_obstruction 
+  SELECT 
+  uuid_generate_v4() as ob_id,
+  11 AS ob_no_origin, -- raw data
+  16 AS ob_no_type, -- obstruction
+  74 AS ob_no_period, -- Unknown
+  NULL AS ob_starting_date,
+  NULL AS ob_ending_date,
+  d.op_id as ob_op_id,
+  7 AS ob_dp_id,
+  219 as ot_no_obstruction_type,
+  1 as ot_obstruction_number,
+  NULL AS ot_no_mortality_type,
+  NULL AS ot_no_mortality,
+  NULL as po_no_obstruction_passability,
+  case when  (pre_ice_hauteur_chute=-999 and pre_ice_hauteur_terrain != -999) then pre_ice_hauteur_terrain
+  when pre_ice_hauteur_chute=-999 then NULL
+  else pre_ice_hauteur_chute end as po_obstruction_height,
+  NULL AS po_turbine_number,
+         d."Id_ROE",
+        "roe_Nom",
+        "bassin_hydrographique",
+        "bassin_administratif",
+        "deptCd",
+        "deptNom",
+        "commCd",
+        "commNom",
+        "ObjFusion_id",
+        "IdTopo",
+        "nomTopo",
+        "IdTrCart",
+        "nomCart",
+        "etatNom",
+        "roe_typeCd",
+        "roe_typeNom",
+        "roe_stypeCd",
+        "roe_stypeNom",
+        "roe_stypEmCd1",
+        "roe_stypEmNom1",
+        "roe_stypEmCd2",
+        "roe_stypEmNom2",
+        "roe_stypEmCd3",
+        "roe_stypEmNom3",
+        "roe_fnt1_code",
+        "roe_fnt1_nom",
+        "roe_fnt2_code",
+        "roe_fnt2_nom",
+        "roe_fnt3_code",
+        "roe_fnt3_nom",
+        "roe_staCd",
+        "roe_staNom",
+        "roe_etatCd",
+        "roe_dateModif",
+        "roe_source",
+        "pre_ice_hauteur_terrain",
+        "pre_ice_hauteur_chute",
+        "pre_ice_nom_2",
+        "pre_ice_date_creation_ouvrage",
+        "pre_ice_usage1",
+        "pre_ice_usage2",
+        "pre_ice_usage3",
+        "pre_ice_usage4",
+        "pre_ice_fpi1",
+        "pre_ice_fpi2",
+        "pre_ice_fpi3",
+        "pre_ice_fpi4",
+        "pre_ice_fpi5",
+        "pre_ice_note_fr_Anguille",
+        "roe_commentaire_validation",
+        "roe_commentaire_suppression"
+  FROM                  
+  onema.roecourt r JOIN  onema.roedbeel d ON r."Id_ROE"=d."Id_ROE"
+  where "roe_staCd"=1;--73332
+
+}}}
+
+  
+
+