Mercurial > dive4elements > river

annotate flys-backend/doc/schema/oracle-spatial.sql @ 5430:ba489a16f4d8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Datacage Function Resolver: Lift from static to object space.
author Sascha L. Teichmann <teichmann@intevation.de>
date Tue, 26 Mar 2013 16:16:12 +0100
parents 8b29a94ebdd8
children 1488227b97b7
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
5385
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5358
diff changeset
80 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
81 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
82 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
83 );
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5358
diff changeset
84 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
85 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
86 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
87 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
88
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5358
diff changeset
89 -- Geodaesie/Bauwerke
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
90 CREATE SEQUENCE BUILDINGS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
91 CREATE TABLE buildings(
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
92 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
93 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
94 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
95 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
96 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
97 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
98 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
99 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
100 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
101 );
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
102 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
103 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
104 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
105 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
106 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
107 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
108 --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
109
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 -- Geodaesie/Festpunkte/Festpunkte.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
112 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
113 CREATE TABLE fixpoints (
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
114 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
115 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
116 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
117 x NUMBER(38,11),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
118 y NUMBER(38,11),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
119 km NUMBER(38,11) NOT NULL,
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
120 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
121 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
122 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
123 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
124 );
5358
59f9760e8b7f Switch to 2D for fixpoints and river_axes
Andre Heinecke <aheinecke@intevation.de>
parents: 5354
diff changeset
125 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
126 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
127 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
128 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
129 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
130 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
131 --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
132
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 -- 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
135 CREATE TABLE floodplain_kinds(
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
136 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
137 name VARCHAR(64)
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
138 );
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
139 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
140 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
141
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
142 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
143 CREATE TABLE floodplain(
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
144 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
145 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
146 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
147 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
148 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
149 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
150 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
151 );
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
152 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
153 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
154 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
155 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
156 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
157 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
158 --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
159
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 -- Geodaesie/Hoehenmodelle/*
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
162 CREATE SEQUENCE DEM_ID_SEQ;
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
163 CREATE TABLE dem (
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
164 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
165 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
166 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
167 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
168 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
169 projection VARCHAR(32),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
170 elevation_state VARCHAR(32),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
171 srid NUMBER NOT NULL,
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
172 format VARCHAR(32),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
173 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
174 resolution VARCHAR(16),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5205
diff changeset
175 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
176 path VARCHAR(256) NOT NULL
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
177 );
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
178 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
179 BEGIN
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
180 SELECT DEM_ID_SEQ.nextval INTO :new.id FROM dual;
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
181 END;
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
182 /
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
183
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
184 --Static lookup tables for Hochwasserschutzanlagen
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
185 CREATE TABLE hws_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
186 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
187 kind VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
188 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
189 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
190 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
191 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
192
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
193 CREATE TABLE fed_states (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
194 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
195 name VARCHAR(23) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
196 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
197 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
198 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
199 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
200 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
201 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
202 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
203 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
204 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
205 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
206 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
207 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
208 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
209 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
210 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
211 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
212 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
213
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
214 --Hydrologie/HW-Schutzanlagen/hws.shp
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
215 -- HWS-Lines
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
216 CREATE SEQUENCE HWS_LINES_ID_SEQ;
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
217 CREATE TABLE hws_lines (
5122
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
218 OGR_FID NUMBER(38),
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
219 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
220 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
221 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
222 river_id NUMBER(38) REFERENCES rivers(id),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
223 name VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
224 path VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
225 official NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
226 agency VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
227 range VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
228 shore_side NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
229 source VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
230 status_date TIMESTAMP,
5122
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
231 description VARCHAR(256),
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
232 id NUMBER PRIMARY KEY NOT NULL
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
233 );
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
234 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
235 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
236 BEGIN
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
237 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
238 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
239 /
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
240 -- HWS Points lookup tables
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
241 CREATE TABLE sectie_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
242 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
243 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
244 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
245 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
246 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
247 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
248 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
249
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
250 CREATE TABLE sobek_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
251 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
252 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
253 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
254 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
255 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
256 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
257
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
258 CREATE TABLE boundary_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
259 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
260 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
261 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
262 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
263 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
264 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
265 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
266
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
267 -- HWS Points
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
268 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
269 CREATE TABLE hws_points (
5122
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
270 OGR_FID NUMBER(38),
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
271 GEOM MDSYS.SDO_GEOMETRY,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
272 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
273 fed_state_id NUMBER REFERENCES fed_states(id),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
274 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
275 name VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
276 path VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
277 official NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
278 agency VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
279 range VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
280 shore_side NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
281 source VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
282 status_date VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
283 description VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
284 freeboard NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
285 dike_km NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
286 z NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
287 z_target NUMBER(19,5),
5122
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
288 rated_level NUMBER(19,5),
5ba502e78e05 Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
289 id NUMBER PRIMARY KEY NOT NULL
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
290 );
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 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
293
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
294 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
295 BEGIN
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
296 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
297 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
298 /
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
299
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
300 --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
301 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
302 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
303 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
304 );
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 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
306 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
307 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
308 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
309 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
310
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
311 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
312 CREATE TABLE floodmaps (
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
313 OGR_FID NUMBER(38),
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
314 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
315 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
316 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
317 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
318 diff NUMBER(19,5),
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
319 count NUMBER(38),
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
320 area NUMBER(19,5),
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
321 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
322 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
323 source varchar(64),
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
324 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
325 );
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
326 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
327 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
328 BEGIN
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
329 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
330 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
331 /
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
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 --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
334 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
335 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
336 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
337 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
338 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
339 name VARCHAR(255),
5078
0fd7720e5c7f Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents: 5038
diff changeset
340 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
341 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
342 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
343 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
344 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
345 );
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 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
347 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
348 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
349 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
350 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
351 /
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
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 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
354 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
355 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
356 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
357 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
358 name VARCHAR(255),
5078
0fd7720e5c7f Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents: 5038
diff changeset
359 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
360 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
361 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
362 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
363 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
364 );
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 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
366 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
367 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
368 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
369 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
370 /
2862
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
371
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
372 -- Hydrologie/Streckendaten/
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
373 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
374 CREATE TABLE gauge_location (
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
375 OGR_FID NUMBER(38),
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
376 GEOM MDSYS.SDO_GEOMETRY,
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
377 river_id NUMBER(38),
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
378 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
379 path VARCHAR(256),
2862
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
380 id NUMBER PRIMARY KEY NOT NULL
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
381 );
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
382 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
383 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
384 BEGIN
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
385 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
386 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
387 /
5291
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
388
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
389
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
390 CREATE TABLE jetty_kinds(
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
391 id NUMBER PRIMARY KEY NOT NULL,
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
392 name VARCHAR(64)
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
393 );
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
394 INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf');
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
395 INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß');
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
396 INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel');
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
397
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
398 CREATE SEQUENCE JETTIES_ID_SEQ;
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
399 CREATE TABLE jetties (
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
400 OGR_FID NUMBER(38),
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
401 GEOM MDSYS.SDO_GEOMETRY,
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
402 id NUMBER PRIMARY KEY NOT NULL,
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
403 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
404 path VARCHAR(256),
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
405 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
406 km NUMBER(7,3),
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
407 z NUMBER(38,12)
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
408 );
5354
555a99dff849 Jetties are two dimensional
Andre Heinecke <aheinecke@intevation.de>
parents: 5315
diff changeset
409 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
410 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
411 BEGIN
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
412 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
413 END;
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
414 /
5409
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
415
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
416 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
417 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
418 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
419 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
420 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
421 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
422 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
423 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
424 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
425 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
426 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
427 );
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
428 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
429 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
430 BEGIN
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
431 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
432 END;
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5408
diff changeset
433 /
http://dive4elements.wald.intevation.org