Mercurial > dive4elements > river

annotate flys-backend/doc/schema/oracle-spatial.sql @ 5372:ae3625b89cfd

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Doc.
author Felix Wolfsteller <felix.wolfsteller@intevation.de>
date Fri, 22 Mar 2013 09:11:08 +0100
parents 59f9760e8b7f
children 2da74705c29d
rev   line source
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
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');
94c3eb995bf6 Fixed broken insert statements for axis_kinds.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5158
diff changeset
8 INSERT INTO axis_kinds(id, name) VALUES (1, 'Aktuell');
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,
5205
31fd42400c84 Oracle Schema: add missing constraint and allow 7-digit kms
Tom Gottfried <tom@intevation.de>
parents: 5162
diff changeset
17 kind_id NUMBER(38) REFERENCES axis_kinds(id) NOT NULL DEFAULT 0,
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,
5205
31fd42400c84 Oracle Schema: add missing constraint and allow 7-digit kms
Tom Gottfried <tom@intevation.de>
parents: 5162
diff changeset
37 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
38 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
39 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
40 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
41 );
5358
59f9760e8b7f Switch to 2D for fixpoints and river_axes
Andre Heinecke <aheinecke@intevation.de>
parents: 5354
diff changeset
42 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
43 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
44 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
45 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
46 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
47 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
48 --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
49
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 --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
52 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
53 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
54 name VARCHAR(64)
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
55 );
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
56 INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige');
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 (1, 'Aktuell');
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
58
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
59 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
60 CREATE TABLE cross_section_tracks (
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
61 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
62 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
63 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
5214
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
64 kind_id NUMBER(38) REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0,
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
65 km NUMBER(38,12) NOT NULL,
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
66 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
67 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
68 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
69 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
70 );
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
71 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
72 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
73 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
74 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
75 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
76 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
77 --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
78
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 -- Geodaesie/Bauwerke/Wehre.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
81 CREATE SEQUENCE BUILDINGS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
82 CREATE TABLE buildings(
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
83 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
84 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
85 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
86 name VARCHAR2(255),
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
87 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
88 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
89 );
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
90 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
91 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
92 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
93 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
94 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
95 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
96 --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
97
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
98
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
99 -- Geodaesie/Festpunkte/Festpunkte.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
100 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
101 CREATE TABLE fixpoints (
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
102 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
103 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
104 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
105 x NUMBER(38,11),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
106 y NUMBER(38,11),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
107 km NUMBER(38,11) NOT NULL,
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
108 HPGP VARCHAR2(255),
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
109 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
110 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
111 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
112 );
5358
59f9760e8b7f Switch to 2D for fixpoints and river_axes
Andre Heinecke <aheinecke@intevation.de>
parents: 5354
diff changeset
113 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
114 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
115 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
116 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
117 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
118 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
119 --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
120
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
121
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
122 -- 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
123 CREATE TABLE floodplain_kinds(
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
124 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
125 name VARCHAR(64)
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
126 );
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
127 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige');
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
128 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'Aktuell');
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
129
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
130 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
131 CREATE TABLE floodplain(
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
132 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
133 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
134 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
5214
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
135 kind_id NUMBER(38) REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0,
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
136 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
137 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
138 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
139 );
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
140 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
141 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
142 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
143 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
144 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
145 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
146 --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
147
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
148
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
149 -- Geodaesie/Hoehenmodelle/*
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
150 CREATE SEQUENCE DEM_ID_SEQ;
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
151 CREATE TABLE dem (
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
152 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
153 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
154 name VARCHAR(64),
5260
8f71fe38977c fixed DEM-importer to work with Oracle (made by A. Heinecke)
Tom Gottfried <tom@intevation.de>
parents: 5214
diff changeset
155 range_id NUMBER(38) REFERENCES ranges(id),
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
156 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
157 projection VARCHAR(32),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
158 elevation_state VARCHAR(32),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
159 srid NUMBER NOT NULL,
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
160 format VARCHAR(32),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
161 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
162 resolution VARCHAR(16),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
163 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
164 path VARCHAR(256) NOT NULL
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
165 );
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
166 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
167 BEGIN
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
168 SELECT DEM_ID_SEQ.nextval INTO :new.id FROM dual;
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
169 END;
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
170 /
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
171
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
172 --Static lookup tables for Hochwasserschutzanlagen
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
173 CREATE TABLE hws_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
174 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
175 kind VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
176 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
177 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
178 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
179 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
180
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
181 CREATE TABLE fed_states (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
182 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
183 name VARCHAR(23) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
184 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
185 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
186 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
187 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
188 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
189 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
190 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
191 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
192 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
193 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
194 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
195 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
196 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
197 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
198 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
199 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
200 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
201
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
202 --Hydrologie/HW-Schutzanlagen/hws.shp
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
203 -- HWS-Lines
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
204 CREATE SEQUENCE HWS_LINES_ID_SEQ;
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
205 CREATE TABLE hws_lines (
5122
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
206 OGR_FID NUMBER(38),
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
207 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
208 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
209 fed_state_id NUMBER(2) REFERENCES fed_states(id),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
210 river_id NUMBER(38) REFERENCES rivers(id),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
211 name VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
212 path VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
213 official NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
214 agency VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
215 range VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
216 shore_side NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
217 source VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
218 status_date TIMESTAMP,
5122
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
219 description VARCHAR(256),
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
220 id NUMBER PRIMARY KEY NOT NULL
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
221 );
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
222 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
223 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
224 BEGIN
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
225 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
226 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
227 /
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
228 -- HWS Points lookup tables
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
229 CREATE TABLE sectie_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
230 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
231 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
232 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
233 INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
234 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
235 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
236 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
237
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
238 CREATE TABLE sobek_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
239 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
240 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
241 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
242 INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
243 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
244 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
245
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
246 CREATE TABLE boundary_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
247 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
248 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
249 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
250 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
251 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
252 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
253 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
254
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
255 -- HWS Points
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
256 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
257 CREATE TABLE hws_points (
5122
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
258 OGR_FID NUMBER(38),
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
259 GEOM MDSYS.SDO_GEOMETRY,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
260 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
261 fed_state_id NUMBER REFERENCES fed_states(id),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
262 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
263 name VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
264 path VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
265 official NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
266 agency VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
267 range VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
268 shore_side NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
269 source VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
270 status_date VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
271 description VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
272 freeboard NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
273 dike_km NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
274 z NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
275 z_target NUMBER(19,5),
5122
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
276 rated_level NUMBER(19,5),
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
277 id NUMBER PRIMARY KEY NOT NULL
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
278 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
279
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
280 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
281
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
282 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
283 BEGIN
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
284 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
285 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
286 /
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
287
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
288 --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
289 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
290 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
291 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
292 );
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
293 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
294 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
295 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
296 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
297 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
298
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
299 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
300 CREATE TABLE floodmaps (
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
301 OGR_FID NUMBER(38),
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
302 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
303 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
304 name VARCHAR(255),
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
305 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
306 diff NUMBER(19,5),
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
307 count NUMBER(38),
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
308 area NUMBER(19,5),
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
309 perimeter NUMBER(19,5),
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
310 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
311 source varchar(64),
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
312 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
313 );
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
314 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
315 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
316 BEGIN
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
317 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
318 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
319 /
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
320
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
321 --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
322 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
323 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
324 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
325 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
326 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
327 name VARCHAR(255),
5078
0fd7720e5c7f Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents: 5038
diff changeset
328 kind NUMBER(38) REFERENCES boundary_kinds(id),
0fd7720e5c7f Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents: 5038
diff changeset
329 sectie NUMBER(38) REFERENCES sectie_kinds(id),
0fd7720e5c7f Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents: 5038
diff changeset
330 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
331 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
332 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
333 );
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 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
335 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
336 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
337 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
338 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
339 /
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
340
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 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
342 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
343 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
344 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
345 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
346 name VARCHAR(255),
5078
0fd7720e5c7f Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents: 5038
diff changeset
347 kind NUMBER(38) REFERENCES boundary_kinds(id),
0fd7720e5c7f Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents: 5038
diff changeset
348 sectie NUMBER(38) REFERENCES sectie_kinds(id),
0fd7720e5c7f Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents: 5038
diff changeset
349 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
350 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
351 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
352 );
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
353 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
354 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
355 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
356 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
357 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
358 /
2862
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
359
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
360 -- Hydrologie/Streckendaten/
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
361 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
362 CREATE TABLE gauge_location (
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
363 OGR_FID NUMBER(38),
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
364 GEOM MDSYS.SDO_GEOMETRY,
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
365 river_id NUMBER(38),
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
366 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
367 path VARCHAR(256),
2862
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
368 id NUMBER PRIMARY KEY NOT NULL
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
369 );
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
370 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('gauge_location', '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);
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
371 CREATE OR REPLACE TRIGGER gauge_location_trigger BEFORE INSERT ON gauge_location FOR EACH ROW
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
372 BEGIN
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
373 SELECT GAUGE_LOCATION_ID_SEQ.nextval INTO :new.id FROM dual;
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
374 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
375 /
5291
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
376
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
377
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
378 CREATE TABLE jetty_kinds(
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
379 id NUMBER PRIMARY KEY NOT NULL,
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
380 name VARCHAR(64)
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
381 );
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
382 INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf');
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
383 INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß');
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
384 INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel');
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
385
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
386 CREATE SEQUENCE JETTIES_ID_SEQ;
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
387 CREATE TABLE jetties (
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
388 OGR_FID NUMBER(38),
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
389 GEOM MDSYS.SDO_GEOMETRY,
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
390 id NUMBER PRIMARY KEY NOT NULL,
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
391 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
392 path VARCHAR(256),
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
393 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
394 km NUMBER(7,3),
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
395 z NUMBER(38,12)
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
396 );
5354
555a99dff849 Jetties are two dimensional
Andre Heinecke <aheinecke@intevation.de>
parents: 5315
diff changeset
397 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
398 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
399 BEGIN
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
400 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
401 END;
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
402 /
http://dive4elements.wald.intevation.org