Mercurial > dive4elements > river
annotate backend/doc/schema/oracle-spatial.sql @ 5871:88d6bddbf6cd
River GWT client: Removed obsolete same package imports.
author | Sascha L. Teichmann <teichmann@intevation.de> |
---|---|
date | Sun, 28 Apr 2013 15:55:52 +0200 |
parents | 249b8766a178 |
children | 6a63456b99d9 |
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'); |
5495
1488227b97b7
Minor scheme change. Update kind names to their UI representation.
Andre Heinecke <aheinecke@intevation.de>
parents:
5409
diff
changeset
|
8 INSERT INTO axis_kinds(id, name) VALUES (1, 'aktuelle Achse'); |
5162
94c3eb995bf6
Fixed broken insert statements for axis_kinds.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5158
diff
changeset
|
9 INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige'); |
5146
d4fdd98a04f7
Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents:
5127
diff
changeset
|
10 |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
11 -- Geodaesie/Flussachse+km/achse |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
12 CREATE SEQUENCE RIVER_AXES_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
13 CREATE TABLE river_axes( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
14 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
15 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
16 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, |
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, |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5748
diff
changeset
|
37 km NUMBER(7,3) NOT NULL, |
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'); |
5495
1488227b97b7
Minor scheme change. Update kind names to their UI representation.
Andre Heinecke <aheinecke@intevation.de>
parents:
5409
diff
changeset
|
57 INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'aktuelle Querprofilspuren'); |
5214
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
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, |
5623
0aaed46a88be
Schema change: Allow longer strings in fixpoints.hpgp
Tom Gottfried <tom@intevation.de>
parents:
5577
diff
changeset
|
120 HPGP VARCHAR(64), |
2861
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
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'); |
5495
1488227b97b7
Minor scheme change. Update kind names to their UI representation.
Andre Heinecke <aheinecke@intevation.de>
parents:
5409
diff
changeset
|
140 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'aktuelle Talaue'); |
5214
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
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 | 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 | 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), |
5577
5f91881124ba
added missing ON DELETE CASCADE to Oracle schema
Tom Gottfried <tom@intevation.de>
parents:
5535
diff
changeset
|
222 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
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 | 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 | 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 | 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 / |
5748
b5553164fabf
Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5623
diff
changeset
|
240 |
5025
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 ); |
5748
b5553164fabf
Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5623
diff
changeset
|
245 INSERT INTO sectie_kinds (id, name) VALUES (0, 'nicht berücksichtigt'); |
b5553164fabf
Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5623
diff
changeset
|
246 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Hauptgerinne'); |
b5553164fabf
Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5623
diff
changeset
|
247 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbereich'); |
b5553164fabf
Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5623
diff
changeset
|
248 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Vorland'); |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
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 ); |
5748
b5553164fabf
Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5623
diff
changeset
|
254 INSERT INTO sobek_kinds (id, name) VALUES (0, 'nicht berücksichtigt'); |
b5553164fabf
Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5623
diff
changeset
|
255 INSERT INTO sobek_kinds (id, name) VALUES (1, 'durchströmt'); |
b5553164fabf
Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5623
diff
changeset
|
256 INSERT INTO sobek_kinds (id, name) VALUES (2, 'nicht durchströmt'); |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
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, |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5748
diff
changeset
|
316 name VARCHAR(255) NOT NULL, |
5276
170f514a4f29
SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents:
5260
diff
changeset
|
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), |
5854
249b8766a178
Schema change: new column 'waterbody' for floodmaps and importer
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5838
diff
changeset
|
322 waterbody VARCHAR(64), |
3677
c37effda3655
Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2862
diff
changeset
|
323 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
|
324 source varchar(64), |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
325 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
|
326 ); |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
327 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
|
328 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
|
329 BEGIN |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
330 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
|
331 END; |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
332 / |
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
|
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 --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
|
335 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
|
336 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
|
337 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
|
338 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
339 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
|
340 name VARCHAR(255), |
5535
16afd4613f9d
Fix syntax errors in oracle scheme
Andre Heinecke <aheinecke@intevation.de>
parents:
5514
diff
changeset
|
341 kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id), |
5748
b5553164fabf
Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5623
diff
changeset
|
342 sectie NUMBER(38) REFERENCES sectie_kinds(id), |
b5553164fabf
Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5623
diff
changeset
|
343 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
|
344 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
|
345 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
|
346 ); |
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 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
|
348 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
|
349 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
|
350 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
|
351 END; |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
352 / |
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
|
353 |
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 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
|
355 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
|
356 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
|
357 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
358 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
|
359 name VARCHAR(255), |
5535
16afd4613f9d
Fix syntax errors in oracle scheme
Andre Heinecke <aheinecke@intevation.de>
parents:
5514
diff
changeset
|
360 kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id), |
5748
b5553164fabf
Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5623
diff
changeset
|
361 sectie NUMBER(38) REFERENCES sectie_kinds(id), |
b5553164fabf
Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5623
diff
changeset
|
362 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
|
363 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
|
364 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
|
365 ); |
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 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
|
367 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
|
368 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
|
369 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
|
370 END; |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
371 / |
2862
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
372 |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
373 -- Hydrologie/Streckendaten/ |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
374 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ; |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
375 CREATE TABLE gauge_location ( |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
376 OGR_FID NUMBER(38), |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
377 GEOM MDSYS.SDO_GEOMETRY, |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
378 river_id NUMBER(38), |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
379 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
|
380 path VARCHAR(256), |
2862
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
381 id NUMBER PRIMARY KEY NOT NULL |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
382 ); |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
383 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
|
384 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
|
385 BEGIN |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
386 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
|
387 END; |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
388 / |
5291
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 |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
391 CREATE TABLE jetty_kinds( |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
392 id NUMBER PRIMARY KEY NOT NULL, |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
393 name VARCHAR(64) |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
394 ); |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
395 INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf'); |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
396 INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß'); |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
397 INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel'); |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
398 |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
399 CREATE SEQUENCE JETTIES_ID_SEQ; |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
400 CREATE TABLE jetties ( |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
401 OGR_FID NUMBER(38), |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
402 GEOM MDSYS.SDO_GEOMETRY, |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
403 id NUMBER PRIMARY KEY NOT NULL, |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
404 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
|
405 path VARCHAR(256), |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
406 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
|
407 km NUMBER(7,3), |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
408 z NUMBER(38,12) |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
409 ); |
5354
555a99dff849
Jetties are two dimensional
Andre Heinecke <aheinecke@intevation.de>
parents:
5315
diff
changeset
|
410 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
|
411 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
|
412 BEGIN |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
413 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
|
414 END; |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
415 / |
5409
8b29a94ebdd8
SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents:
5408
diff
changeset
|
416 |
8b29a94ebdd8
SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents:
5408
diff
changeset
|
417 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
|
418 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
|
419 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
|
420 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
|
421 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
|
422 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
|
423 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
|
424 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
|
425 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
|
426 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
|
427 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
|
428 ); |
8b29a94ebdd8
SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents:
5408
diff
changeset
|
429 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
|
430 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
|
431 BEGIN |
8b29a94ebdd8
SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents:
5408
diff
changeset
|
432 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
|
433 END; |
8b29a94ebdd8
SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents:
5408
diff
changeset
|
434 / |