Mercurial > dive4elements > river

annotate backend/doc/schema/oracle-spatial.sql @ 9415:9744ce3c3853

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Rework of fixanalysis computation and dWt and WQ facets. Got rid of strange remapping and bitshifting code by explicitely saving the column information and using it in the facets. The facets also put the valid station range into their xml-metadata
author gernotbelger
date Thu, 16 Aug 2018 16:27:53 +0200
parents cc53aae06303
children 6824c447f5b0
rev   line source
6632
97145ec4e33c Schema changes: fixed typos, syntax errors and missing or obsolete statements in Oracle schema
Tom Gottfried <tom@intevation.de>
parents: 5886
diff changeset
1 --WHENEVER SQLERROR EXIT;
5146
d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents: 5127
diff changeset
2
d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents: 5127
diff changeset
3 CREATE TABLE axis_kinds(
d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents: 5127
diff changeset
4 id NUMBER PRIMARY KEY NOT NULL,
d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents: 5127
diff changeset
5 name VARCHAR(64)
d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents: 5127
diff changeset
6 );
5162
94c3eb995bf6 Fixed broken insert statements for axis_kinds.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5158
diff changeset
7 INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt');
5495
1488227b97b7 Minor scheme change. Update kind names to their UI representation.
Andre Heinecke <aheinecke@intevation.de>
parents: 5409
diff changeset
8 INSERT INTO axis_kinds(id, name) VALUES (1, 'aktuelle Achse');
5162
94c3eb995bf6 Fixed broken insert statements for axis_kinds.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5158
diff changeset
9 INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige');
5146
d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents: 5127
diff changeset
10
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
11 -- Geodaesie/Flussachse+km/achse
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
12 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
13 CREATE TABLE river_axes(
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
14 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
15 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
16 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
6632
97145ec4e33c Schema changes: fixed typos, syntax errors and missing or obsolete statements in Oracle schema
Tom Gottfried <tom@intevation.de>
parents: 5886
diff changeset
17 kind_id NUMBER(38) DEFAULT 0 NOT NULL REFERENCES axis_kinds(id),
2861
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
18 name VARCHAR(64),
3677
c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2862
diff changeset
19 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
20 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
21 );
2370
7d378970d764 doc/schema/oracle-spatial.sql: Adjust extent of germany to EPSG:31467 (GK3) coordinates.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2369
diff changeset
22 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
23 CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
24 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
25 SELECT RIVER_AXES_ID_SEQ.nextval INTO :new.id FROM dual;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
26 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
27 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
28 --CREATE INDEX river_axes_spatial_idx ON river_axes(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
29
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
30
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
31 -- Geodaesie/Flussachse+km/km.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
32 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
33 CREATE TABLE river_axes_km(
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
34 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
35 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
36 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5748
diff changeset
37 km NUMBER(7,3) NOT NULL,
5886
6a63456b99d9 Schema change, importer, i18n: additional field for federal state stations
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5854
diff changeset
38 fedstate_km NUMBER(7,3),
2861
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
39 name VARCHAR(64),
3677
c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2862
diff changeset
40 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
41 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
42 );
5358
59f9760e8b7f Switch to 2D for fixpoints and river_axes
Andre Heinecke <aheinecke@intevation.de>
parents: 5354
diff changeset
43 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes_km', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001)), 31467);
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
44 CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
45 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
46 SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
47 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
48 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
49 --CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point');
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
50
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
51
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
52 --Geodaesie/Querprofile/QP-Spuren/qps.shp
5214
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
53 CREATE TABLE cross_section_track_kinds(
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
54 id NUMBER PRIMARY KEY NOT NULL,
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
55 name VARCHAR(64)
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
56 );
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
57 INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige');
5495
1488227b97b7 Minor scheme change. Update kind names to their UI representation.
Andre Heinecke <aheinecke@intevation.de>
parents: 5409
diff changeset
58 INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'aktuelle Querprofilspuren');
5214
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
59
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
60 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
61 CREATE TABLE cross_section_tracks (
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
62 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
63 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
64 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
6632
97145ec4e33c Schema changes: fixed typos, syntax errors and missing or obsolete statements in Oracle schema
Tom Gottfried <tom@intevation.de>
parents: 5886
diff changeset
65 kind_id NUMBER(38) DEFAULT 0 NOT NULL REFERENCES cross_section_track_kinds(id),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
66 km NUMBER(38,12) NOT NULL,
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
67 z NUMBER(38,12) DEFAULT 0 NOT NULL,
2861
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
68 name VARCHAR(64),
3677
c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2862
diff changeset
69 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
70 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
71 );
2370
7d378970d764 doc/schema/oracle-spatial.sql: Adjust extent of germany to EPSG:31467 (GK3) coordinates.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2369
diff changeset
72 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('cross_section_tracks', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
73 CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
74 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
75 SELECT CROSS_SECTION_TRACKS_ID_SEQ.nextval INTO :new.id FROM dual;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
76 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
77 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
78 --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
79
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
80
5385
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5358
diff changeset
81 CREATE TABLE building_kinds(
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5358
diff changeset
82 id NUMBER PRIMARY KEY NOT NULL,
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5358
diff changeset
83 name VARCHAR(64)
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5358
diff changeset
84 );
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5358
diff changeset
85 INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige');
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5358
diff changeset
86 INSERT INTO building_kinds(id, name) VALUES (1, 'Brücken');
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5358
diff changeset
87 INSERT INTO building_kinds(id, name) VALUES (2, 'Wehre');
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5358
diff changeset
88 INSERT INTO building_kinds(id, name) VALUES (3, 'Pegel');
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5358
diff changeset
89
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5358
diff changeset
90 -- Geodaesie/Bauwerke
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
91 CREATE SEQUENCE BUILDINGS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
92 CREATE TABLE buildings(
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
93 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
94 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
95 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
5408
91f5908c472a Add building_kinds to drop script and fix syntax of buildings table
Andre Heinecke <aheinecke@intevation.de>
parents: 5391
diff changeset
96 kind_id NUMBER(38) DEFAULT 0 NOT NULL REFERENCES building_kinds(id),
91f5908c472a Add building_kinds to drop script and fix syntax of buildings table
Andre Heinecke <aheinecke@intevation.de>
parents: 5391
diff changeset
97 km NUMBER(38,11),
5385
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5358
diff changeset
98 name VARCHAR2(255), -- The layername
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5358
diff changeset
99 description VARCHAR(256), -- Name taken from attributes
3677
c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2862
diff changeset
100 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
101 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
102 );
2370
7d378970d764 doc/schema/oracle-spatial.sql: Adjust extent of germany to EPSG:31467 (GK3) coordinates.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2369
diff changeset
103 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('buildings', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
104 CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
105 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
106 SELECT BUILDINGS_ID_SEQ.nextval INTO :new.id FROM dual;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
107 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
108 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
109 --CREATE INDEX buildings_spatial_idx ON buildings(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
110
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
111
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
112 -- Geodaesie/Festpunkte/Festpunkte.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
113 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
114 CREATE TABLE fixpoints (
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
115 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
116 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
117 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
118 x NUMBER(38,11),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
119 y NUMBER(38,11),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
120 km NUMBER(38,11) NOT NULL,
5623
0aaed46a88be Schema change: Allow longer strings in fixpoints.hpgp
Tom Gottfried <tom@intevation.de>
parents: 5577
diff changeset
121 HPGP VARCHAR(64),
2861
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
122 name VARCHAR(64),
3677
c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2862
diff changeset
123 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
124 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
125 );
5358
59f9760e8b7f Switch to 2D for fixpoints and river_axes
Andre Heinecke <aheinecke@intevation.de>
parents: 5354
diff changeset
126 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('fixpoints', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001)), 31467);
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
127 CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
128 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
129 SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
130 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
131 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
132 --CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
133
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
134
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
135 -- Hydrologie/Hydr. Grenzen/talaue.shp
5214
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
136 CREATE TABLE floodplain_kinds(
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
137 id NUMBER PRIMARY KEY NOT NULL,
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
138 name VARCHAR(64)
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
139 );
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
140 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige');
5495
1488227b97b7 Minor scheme change. Update kind names to their UI representation.
Andre Heinecke <aheinecke@intevation.de>
parents: 5409
diff changeset
141 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'aktuelle Talaue');
5214
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
142
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
143 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
144 CREATE TABLE floodplain(
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
145 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
146 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
147 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
6632
97145ec4e33c Schema changes: fixed typos, syntax errors and missing or obsolete statements in Oracle schema
Tom Gottfried <tom@intevation.de>
parents: 5886
diff changeset
148 kind_id NUMBER(38) DEFAULT 0 NOT NULL REFERENCES floodplain_kinds(id),
2861
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
149 name VARCHAR(64),
3677
c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2862
diff changeset
150 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
151 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
152 );
2370
7d378970d764 doc/schema/oracle-spatial.sql: Adjust extent of germany to EPSG:31467 (GK3) coordinates.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2369
diff changeset
153 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodplain', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
154 CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
155 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
156 SELECT FLOODPLAIN_ID_SEQ.nextval INTO :new.id FROM dual;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
157 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
158 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
159 --CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON');
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
160
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
161
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
162 -- Geodaesie/Hoehenmodelle/*
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
163 CREATE SEQUENCE DEM_ID_SEQ;
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
164 CREATE TABLE dem (
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
165 ID NUMBER PRIMARY KEY NOT NULL,
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
166 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
167 name VARCHAR(64),
7001
dcc7a6c8b92c Minor schema change: allow to delete ranges with associated DEMs.
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6632
diff changeset
168 range_id NUMBER(38) REFERENCES ranges(id) ON DELETE CASCADE,
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
169 time_interval_id NUMBER(38) REFERENCES time_intervals(id),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
170 projection VARCHAR(32),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
171 elevation_state VARCHAR(32),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
172 srid NUMBER NOT NULL,
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
173 format VARCHAR(32),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
174 border_break NUMBER(1) DEFAULT 0 NOT NULL,
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
175 resolution VARCHAR(16),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
176 description VARCHAR(256),
5315
aa407dfb9949 removed unique constraint to allow one dem to be used by several rivers
Tom Gottfried <tom@intevation.de>
parents: 5291
diff changeset
177 path VARCHAR(256) NOT NULL
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
178 );
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
179 CREATE OR REPLACE TRIGGER dem_trigger BEFORE INSERT ON dem FOR each ROW
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
180 BEGIN
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
181 SELECT DEM_ID_SEQ.nextval INTO :new.id FROM dual;
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
182 END;
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
183 /
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
184
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
185 --Static lookup tables for Hochwasserschutzanlagen
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
186 CREATE TABLE hws_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
187 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
188 kind VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
189 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
190 INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
191 INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
192 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
193
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
194 CREATE TABLE fed_states (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
195 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
196 name VARCHAR(23) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
197 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
198 INSERT INTO fed_states (id, name) VALUES (1, 'Bayern');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
199 INSERT INTO fed_states (id, name) VALUES (2, 'Hessen');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
200 INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
201 INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
202 INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
203 INSERT INTO fed_states (id, name) VALUES (6, 'Saarland');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
204 INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
205 INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
206 INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
207 INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
208 INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
209 INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
210 INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
211 INSERT INTO fed_states (id, name) VALUES (14, 'Berlin');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
212 INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
213 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
214
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
215 --Hydrologie/HW-Schutzanlagen/hws.shp
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
216 -- HWS-Lines
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
217 CREATE SEQUENCE HWS_LINES_ID_SEQ;
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
218 CREATE TABLE hws_lines (
5122
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
219 OGR_FID NUMBER(38),
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
220 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
221 kind_id NUMBER(2) DEFAULT 2 REFERENCES hws_kinds(id),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
222 fed_state_id NUMBER(2) REFERENCES fed_states(id),
5577
5f91881124ba added missing ON DELETE CASCADE to Oracle schema
Tom Gottfried <tom@intevation.de>
parents: 5535
diff changeset
223 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
224 name VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
225 path VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
226 official NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
227 agency VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
228 range VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
229 shore_side NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
230 source VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
231 status_date TIMESTAMP,
5122
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
232 description VARCHAR(256),
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
233 id NUMBER PRIMARY KEY NOT NULL
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
234 );
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
235 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws_lines', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
236 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
237 BEGIN
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
238 SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual;
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
239 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
240 /
5748
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
241
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
242 CREATE TABLE sectie_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
243 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
244 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
245 );
5748
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
246 INSERT INTO sectie_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
247 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Hauptgerinne');
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
248 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbereich');
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
249 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Vorland');
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
250
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
251 CREATE TABLE sobek_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
252 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
253 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
254 );
5748
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
255 INSERT INTO sobek_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
256 INSERT INTO sobek_kinds (id, name) VALUES (1, 'durchströmt');
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
257 INSERT INTO sobek_kinds (id, name) VALUES (2, 'nicht durchströmt');
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
258
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
259 CREATE TABLE boundary_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
260 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
261 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
262 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
263 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
264 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
265 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
266 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
267
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
268 -- HWS Points
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
269 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
270 CREATE TABLE hws_points (
5122
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
271 OGR_FID NUMBER(38),
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
272 GEOM MDSYS.SDO_GEOMETRY,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
273 kind_id NUMBER DEFAULT 2 REFERENCES hws_kinds(id),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
274 fed_state_id NUMBER REFERENCES fed_states(id),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
275 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
276 name VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
277 path VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
278 official NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
279 agency VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
280 range VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
281 shore_side NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
282 source VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
283 status_date VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
284 description VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
285 freeboard NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
286 dike_km NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
287 z NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
288 z_target NUMBER(19,5),
5122
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
289 rated_level NUMBER(19,5),
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
290 id NUMBER PRIMARY KEY NOT NULL
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
291 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
292
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
293 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws_points', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
294
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
295 CREATE OR REPLACE TRIGGER hws_points_trigger BEFORE INSERT ON hws_points FOR each ROW
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
296 BEGIN
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
297 SELECT HWS_POINTS_ID_SEQ.nextval INTO :new.id FROM dual;
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
298 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
299 /
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
300
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
301 --Hydrologie/UeSG
5276
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5260
diff changeset
302 CREATE TABLE floodmap_kinds (
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5260
diff changeset
303 id NUMBER PRIMARY KEY NOT NULL,
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5260
diff changeset
304 name varchar(64) NOT NULL
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5260
diff changeset
305 );
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5260
diff changeset
306 INSERT INTO floodmap_kinds VALUES (200, 'Messung');
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5260
diff changeset
307 INSERT INTO floodmap_kinds VALUES (111, 'Berechnung-Aktuell-BfG');
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5260
diff changeset
308 INSERT INTO floodmap_kinds VALUES (112, 'Berechnung-Aktuell-Bundesländer');
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5260
diff changeset
309 INSERT INTO floodmap_kinds VALUES (121, 'Berechnung-Potenziell-BfG');
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5260
diff changeset
310 INSERT INTO floodmap_kinds VALUES (122, 'Berechnung-Potenziell-Bundesländer');
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5260
diff changeset
311
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
312 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
313 CREATE TABLE floodmaps (
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
314 OGR_FID NUMBER(38),
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
315 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
316 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5748
diff changeset
317 name VARCHAR(255) NOT NULL,
5276
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5260
diff changeset
318 kind NUMBER NOT NULL REFERENCES floodmap_kinds(id),
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
319 diff NUMBER(19,5),
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
320 count NUMBER(38),
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
321 area NUMBER(19,5),
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
322 perimeter NUMBER(19,5),
5854
249b8766a178 Schema change: new column 'waterbody' for floodmaps and importer
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5838
diff changeset
323 waterbody VARCHAR(64),
3677
c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2862
diff changeset
324 path VARCHAR(256),
5276
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5260
diff changeset
325 source varchar(64),
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
326 id NUMBER PRIMARY KEY NOT NULL
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
327 );
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
328 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodmaps', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
329 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
330 BEGIN
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
331 SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual;
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
332 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
333 /
2861
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
334
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
335 --Hydrologie/Hydr.Grenzen/Linien
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
336 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
337 CREATE TABLE hydr_boundaries (
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
338 OGR_FID NUMBER(38),
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
339 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
340 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
2861
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
341 name VARCHAR(255),
5535
16afd4613f9d Fix syntax errors in oracle scheme
Andre Heinecke <aheinecke@intevation.de>
parents: 5514
diff changeset
342 kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id),
5748
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
343 sectie NUMBER(38) REFERENCES sectie_kinds(id),
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
344 sobek NUMBER(38) REFERENCES sobek_kinds(id),
3677
c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2862
diff changeset
345 path VARCHAR(256),
2861
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
346 id NUMBER PRIMARY KEY NOT NULL
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
347 );
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
348 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hydr_boundaries', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
349 CREATE OR REPLACE TRIGGER hydr_boundaries_trigger BEFORE INSERT ON hydr_boundaries FOR each ROW
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
350 BEGIN
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
351 SELECT HYDR_BOUNDARIES_ID_SEQ.nextval INTO :new.id FROM dual;
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
352 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
353 /
2861
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
354
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
355 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
356 CREATE TABLE hydr_boundaries_poly (
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
357 OGR_FID NUMBER(38),
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
358 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
359 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
2861
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
360 name VARCHAR(255),
5535
16afd4613f9d Fix syntax errors in oracle scheme
Andre Heinecke <aheinecke@intevation.de>
parents: 5514
diff changeset
361 kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id),
5748
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
362 sectie NUMBER(38) REFERENCES sectie_kinds(id),
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
363 sobek NUMBER(38) REFERENCES sobek_kinds(id),
3677
c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2862
diff changeset
364 path VARCHAR(256),
2861
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
365 id NUMBER PRIMARY KEY NOT NULL
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
366 );
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
367 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hydr_boundaries_poly', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
368 CREATE OR REPLACE TRIGGER hydr_boundaries_poly_trigger BEFORE INSERT ON hydr_boundaries_poly FOR each ROW
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
369 BEGIN
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
370 SELECT HYDR_BOUNDARIES_POLY_ID_SEQ.nextval INTO :new.id FROM dual;
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2852
diff changeset
371 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
372 /
2862
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
373
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
374 -- Hydrologie/Streckendaten/
5291
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
375 CREATE TABLE jetty_kinds(
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
376 id NUMBER PRIMARY KEY NOT NULL,
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
377 name VARCHAR(64)
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
378 );
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
379 INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf');
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
380 INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß');
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
381 INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel');
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
382
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
383 CREATE SEQUENCE JETTIES_ID_SEQ;
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
384 CREATE TABLE jetties (
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
385 OGR_FID NUMBER(38),
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
386 GEOM MDSYS.SDO_GEOMETRY,
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
387 id NUMBER PRIMARY KEY NOT NULL,
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
388 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
389 path VARCHAR(256),
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
390 kind_id NUMBER(38) REFERENCES jetty_kinds(id),
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
391 km NUMBER(7,3),
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
392 z NUMBER(38,12)
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
393 );
5354
555a99dff849 Jetties are two dimensional
Andre Heinecke <aheinecke@intevation.de>
parents: 5315
diff changeset
394 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('jetties', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001)), 31467);
5291
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
395 CREATE OR REPLACE TRIGGER jetties_trigger BEFORE INSERT ON jetties FOR EACH ROW
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
396 BEGIN
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
397 SELECT JETTIES_ID_SEQ.nextval INTO :new.id FROM dual;
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
398 END;
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
399 /
5409
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
400
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
401 CREATE SEQUENCE FLOOD_MARKS_ID_SEQ;
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
402 CREATE TABLE flood_marks (
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
403 OGR_FID NUMBER(38),
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
404 GEOM MDSYS.SDO_GEOMETRY,
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
405 id NUMBER PRIMARY KEY NOT NULL,
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
406 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
407 path VARCHAR(256),
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
408 km NUMBER(7,3),
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
409 z NUMBER(38,12),
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
410 location VARCHAR(64),
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
411 year NUMBER(38,0)
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
412 );
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
413 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('flood_marks', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001)), 31467);
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
414 CREATE OR REPLACE TRIGGER flood_marks_trigger BEFORE INSERT ON flood_marks FOR EACH ROW
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
415 BEGIN
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
416 SELECT FLOOD_MARKS_ID_SEQ.nextval INTO :new.id FROM dual;
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
417 END;
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
418 /
http://dive4elements.wald.intevation.org