Mercurial > dive4elements > river
annotate flys-backend/doc/schema/oracle-spatial.sql @ 5325:53aee7e9ce53
Be more aggressive when replacing the wiki url
author | Andre Heinecke <aheinecke@intevation.de> |
---|---|
date | Fri, 15 Mar 2013 19:28:35 +0100 |
parents | aa407dfb9949 |
children | 555a99dff849 |
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 ); |
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
|
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),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
|
43 CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
44 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
45 SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
46 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
47 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
48 --CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point'); |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
49 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
50 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
51 --Geodaesie/Querprofile/QP-Spuren/qps.shp |
5214
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
52 CREATE TABLE cross_section_track_kinds( |
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
53 id NUMBER PRIMARY KEY NOT NULL, |
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
54 name VARCHAR(64) |
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
55 ); |
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
56 INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige'); |
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
57 INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'Aktuell'); |
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
58 |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
59 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
60 CREATE TABLE cross_section_tracks ( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
61 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
62 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
63 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, |
5214
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
64 kind_id NUMBER(38) REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0, |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
65 km NUMBER(38,12) NOT NULL, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
66 z NUMBER(38,12) DEFAULT 0 NOT NULL, |
2861
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
67 name VARCHAR(64), |
3677
c37effda3655
Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2862
diff
changeset
|
68 path VARCHAR(256), |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
69 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
70 ); |
2370
7d378970d764
doc/schema/oracle-spatial.sql: Adjust extent of germany to EPSG:31467 (GK3) coordinates.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2369
diff
changeset
|
71 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('cross_section_tracks', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
72 CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
73 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
74 SELECT CROSS_SECTION_TRACKS_ID_SEQ.nextval INTO :new.id FROM dual; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
75 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
76 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
77 --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
78 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
79 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
80 -- Geodaesie/Bauwerke/Wehre.shp |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
81 CREATE SEQUENCE BUILDINGS_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
82 CREATE TABLE buildings( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
83 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
84 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
85 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
86 name VARCHAR2(255), |
3677
c37effda3655
Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2862
diff
changeset
|
87 path VARCHAR(256), |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
88 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
89 ); |
2370
7d378970d764
doc/schema/oracle-spatial.sql: Adjust extent of germany to EPSG:31467 (GK3) coordinates.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2369
diff
changeset
|
90 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('buildings', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
91 CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
92 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
93 SELECT BUILDINGS_ID_SEQ.nextval INTO :new.id FROM dual; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
94 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
95 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
96 --CREATE INDEX buildings_spatial_idx ON buildings(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
97 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
98 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
99 -- Geodaesie/Festpunkte/Festpunkte.shp |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
100 CREATE SEQUENCE FIXPOINTS_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
101 CREATE TABLE fixpoints ( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
102 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
103 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
104 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
105 x NUMBER(38,11), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
106 y NUMBER(38,11), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
107 km NUMBER(38,11) NOT NULL, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
108 HPGP VARCHAR2(255), |
2861
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
109 name VARCHAR(64), |
3677
c37effda3655
Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2862
diff
changeset
|
110 path VARCHAR(256), |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
111 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
112 ); |
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
|
113 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('fixpoints', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),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
|
114 CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
115 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
116 SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
117 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
118 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
119 --CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT'); |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
120 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
121 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
122 -- Hydrologie/Hydr. Grenzen/talaue.shp |
5214
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
123 CREATE TABLE floodplain_kinds( |
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
124 id NUMBER PRIMARY KEY NOT NULL, |
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
125 name VARCHAR(64) |
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
126 ); |
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
127 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige'); |
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
128 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'Aktuell'); |
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
129 |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
130 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
131 CREATE TABLE floodplain( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
132 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
133 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
134 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, |
5214
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
135 kind_id NUMBER(38) REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0, |
2861
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
136 name VARCHAR(64), |
3677
c37effda3655
Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2862
diff
changeset
|
137 path VARCHAR(256), |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
138 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
139 ); |
2370
7d378970d764
doc/schema/oracle-spatial.sql: Adjust extent of germany to EPSG:31467 (GK3) coordinates.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2369
diff
changeset
|
140 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodplain', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
141 CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
142 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
143 SELECT FLOODPLAIN_ID_SEQ.nextval INTO :new.id FROM dual; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
144 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
145 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
146 --CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON'); |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
147 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
148 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
149 -- Geodaesie/Hoehenmodelle/* |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
150 CREATE SEQUENCE DEM_ID_SEQ; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
151 CREATE TABLE dem ( |
5210
908848b74d7e
SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents:
5205
diff
changeset
|
152 ID NUMBER PRIMARY KEY NOT NULL, |
908848b74d7e
SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents:
5205
diff
changeset
|
153 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, |
908848b74d7e
SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents:
5205
diff
changeset
|
154 name VARCHAR(64), |
5260
8f71fe38977c
fixed DEM-importer to work with Oracle (made by A. Heinecke)
Tom Gottfried <tom@intevation.de>
parents:
5214
diff
changeset
|
155 range_id NUMBER(38) REFERENCES ranges(id), |
5210
908848b74d7e
SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents:
5205
diff
changeset
|
156 time_interval_id NUMBER(38) REFERENCES time_intervals(id), |
908848b74d7e
SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents:
5205
diff
changeset
|
157 projection VARCHAR(32), |
908848b74d7e
SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents:
5205
diff
changeset
|
158 elevation_state VARCHAR(32), |
908848b74d7e
SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents:
5205
diff
changeset
|
159 srid NUMBER NOT NULL, |
908848b74d7e
SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents:
5205
diff
changeset
|
160 format VARCHAR(32), |
908848b74d7e
SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents:
5205
diff
changeset
|
161 border_break NUMBER(1) DEFAULT 0 NOT NULL, |
908848b74d7e
SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents:
5205
diff
changeset
|
162 resolution VARCHAR(16), |
908848b74d7e
SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents:
5205
diff
changeset
|
163 description VARCHAR(256), |
5315
aa407dfb9949
removed unique constraint to allow one dem to be used by several rivers
Tom Gottfried <tom@intevation.de>
parents:
5291
diff
changeset
|
164 path VARCHAR(256) NOT NULL |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
165 ); |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
166 CREATE OR REPLACE TRIGGER dem_trigger BEFORE INSERT ON dem FOR each ROW |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
167 BEGIN |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
168 SELECT DEM_ID_SEQ.nextval INTO :new.id FROM dual; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
169 END; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
170 / |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
171 |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
172 --Static lookup tables for Hochwasserschutzanlagen |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
173 CREATE TABLE hws_kinds ( |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
174 id NUMBER PRIMARY KEY NOT NULL, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
175 kind VARCHAR(64) NOT NULL |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
176 ); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
177 INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
178 INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
179 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
180 |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
181 CREATE TABLE fed_states ( |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
182 id NUMBER PRIMARY KEY NOT NULL, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
183 name VARCHAR(23) NOT NULL |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
184 ); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
185 INSERT INTO fed_states (id, name) VALUES (1, 'Bayern'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
186 INSERT INTO fed_states (id, name) VALUES (2, 'Hessen'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
187 INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
188 INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
189 INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
190 INSERT INTO fed_states (id, name) VALUES (6, 'Saarland'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
191 INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
192 INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
193 INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
194 INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
195 INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
196 INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
197 INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
198 INSERT INTO fed_states (id, name) VALUES (14, 'Berlin'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
199 INSERT INTO fed_states (id, name) VALUES (15, 'Bremen'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
200 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
201 |
1250 | 202 --Hydrologie/HW-Schutzanlagen/hws.shp |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
203 -- HWS-Lines |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
204 CREATE SEQUENCE HWS_LINES_ID_SEQ; |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
205 CREATE TABLE hws_lines ( |
5122
5ba502e78e05
Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents:
5121
diff
changeset
|
206 OGR_FID NUMBER(38), |
1250 | 207 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
208 kind_id NUMBER(2) DEFAULT 2 REFERENCES hws_kinds(id), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
209 fed_state_id NUMBER(2) REFERENCES fed_states(id), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
210 river_id NUMBER(38) REFERENCES rivers(id), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
211 name VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
212 path VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
213 official NUMBER DEFAULT 0, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
214 agency VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
215 range VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
216 shore_side NUMBER DEFAULT 0, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
217 source VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
218 status_date TIMESTAMP, |
5122
5ba502e78e05
Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents:
5121
diff
changeset
|
219 description VARCHAR(256), |
5ba502e78e05
Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents:
5121
diff
changeset
|
220 id NUMBER PRIMARY KEY NOT NULL |
1250 | 221 ); |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
222 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws_lines', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
223 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW |
1250 | 224 BEGIN |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
225 SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual; |
1250 | 226 END; |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
227 / |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
228 -- HWS Points lookup tables |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
229 CREATE TABLE sectie_kinds ( |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
230 id NUMBER PRIMARY KEY NOT NULL, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
231 name VARCHAR(64) NOT NULL |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
232 ); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
233 INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
234 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
235 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
236 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
237 |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
238 CREATE TABLE sobek_kinds ( |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
239 id NUMBER PRIMARY KEY NOT NULL, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
240 name VARCHAR(64) NOT NULL |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
241 ); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
242 INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
243 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
244 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
245 |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
246 CREATE TABLE boundary_kinds ( |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
247 id NUMBER PRIMARY KEY NOT NULL, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
248 name VARCHAR(64) NOT NULL |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
249 ); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
250 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
251 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
252 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
253 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige'); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
254 |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
255 -- HWS Points |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
256 CREATE SEQUENCE HWS_POINTS_ID_SEQ; |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
257 CREATE TABLE hws_points ( |
5122
5ba502e78e05
Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents:
5121
diff
changeset
|
258 OGR_FID NUMBER(38), |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
259 GEOM MDSYS.SDO_GEOMETRY, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
260 kind_id NUMBER DEFAULT 2 REFERENCES hws_kinds(id), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
261 fed_state_id NUMBER REFERENCES fed_states(id), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
262 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
263 name VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
264 path VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
265 official NUMBER DEFAULT 0, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
266 agency VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
267 range VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
268 shore_side NUMBER DEFAULT 0, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
269 source VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
270 status_date VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
271 description VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
272 freeboard NUMBER(19,5), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
273 dike_km NUMBER(19,5), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
274 z NUMBER(19,5), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
275 z_target NUMBER(19,5), |
5122
5ba502e78e05
Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents:
5121
diff
changeset
|
276 rated_level NUMBER(19,5), |
5ba502e78e05
Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents:
5121
diff
changeset
|
277 id NUMBER PRIMARY KEY NOT NULL |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
278 ); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
279 |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
280 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws_points', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
281 |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
282 CREATE OR REPLACE TRIGGER hws_points_trigger BEFORE INSERT ON hws_points FOR each ROW |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
283 BEGIN |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
284 SELECT HWS_POINTS_ID_SEQ.nextval INTO :new.id FROM dual; |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
285 END; |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
286 / |
2796
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
287 |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
288 --Hydrologie/UeSG |
5276
170f514a4f29
SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents:
5260
diff
changeset
|
289 CREATE TABLE floodmap_kinds ( |
170f514a4f29
SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents:
5260
diff
changeset
|
290 id NUMBER PRIMARY KEY NOT NULL, |
170f514a4f29
SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents:
5260
diff
changeset
|
291 name varchar(64) NOT NULL |
170f514a4f29
SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents:
5260
diff
changeset
|
292 ); |
170f514a4f29
SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents:
5260
diff
changeset
|
293 INSERT INTO floodmap_kinds VALUES (200, 'Messung'); |
170f514a4f29
SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents:
5260
diff
changeset
|
294 INSERT INTO floodmap_kinds VALUES (111, 'Berechnung-Aktuell-BfG'); |
170f514a4f29
SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents:
5260
diff
changeset
|
295 INSERT INTO floodmap_kinds VALUES (112, 'Berechnung-Aktuell-Bundesländer'); |
170f514a4f29
SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents:
5260
diff
changeset
|
296 INSERT INTO floodmap_kinds VALUES (121, 'Berechnung-Potenziell-BfG'); |
170f514a4f29
SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents:
5260
diff
changeset
|
297 INSERT INTO floodmap_kinds VALUES (122, 'Berechnung-Potenziell-Bundesländer'); |
170f514a4f29
SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents:
5260
diff
changeset
|
298 |
2796
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
299 CREATE SEQUENCE FLOODMAPS_ID_SEQ; |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
300 CREATE TABLE floodmaps ( |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
301 OGR_FID NUMBER(38), |
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
302 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
303 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
304 name VARCHAR(255), |
5276
170f514a4f29
SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents:
5260
diff
changeset
|
305 kind NUMBER NOT NULL REFERENCES floodmap_kinds(id), |
2796
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
306 diff NUMBER(19,5), |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
307 count NUMBER(38), |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
308 area NUMBER(19,5), |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
309 perimeter NUMBER(19,5), |
3677
c37effda3655
Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2862
diff
changeset
|
310 path VARCHAR(256), |
5276
170f514a4f29
SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents:
5260
diff
changeset
|
311 source varchar(64), |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
312 id NUMBER PRIMARY KEY NOT NULL |
2796
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
313 ); |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
314 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodmaps', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); |
2796
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
315 CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
316 BEGIN |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
317 SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual; |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
318 END; |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
319 / |
2861
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
320 |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
321 --Hydrologie/Hydr.Grenzen/Linien |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
322 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
323 CREATE TABLE hydr_boundaries ( |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
324 OGR_FID NUMBER(38), |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
325 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
326 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, |
2861
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
327 name VARCHAR(255), |
5078
0fd7720e5c7f
Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents:
5038
diff
changeset
|
328 kind NUMBER(38) REFERENCES boundary_kinds(id), |
0fd7720e5c7f
Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents:
5038
diff
changeset
|
329 sectie NUMBER(38) REFERENCES sectie_kinds(id), |
0fd7720e5c7f
Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents:
5038
diff
changeset
|
330 sobek NUMBER(38) REFERENCES sobek_kinds(id), |
3677
c37effda3655
Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2862
diff
changeset
|
331 path VARCHAR(256), |
2861
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
332 id NUMBER PRIMARY KEY NOT NULL |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
333 ); |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
334 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hydr_boundaries', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
335 CREATE OR REPLACE TRIGGER hydr_boundaries_trigger BEFORE INSERT ON hydr_boundaries FOR each ROW |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
336 BEGIN |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
337 SELECT HYDR_BOUNDARIES_ID_SEQ.nextval INTO :new.id FROM dual; |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
338 END; |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
339 / |
2861
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
340 |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
341 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
342 CREATE TABLE hydr_boundaries_poly ( |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
343 OGR_FID NUMBER(38), |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
344 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
345 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, |
2861
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
346 name VARCHAR(255), |
5078
0fd7720e5c7f
Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents:
5038
diff
changeset
|
347 kind NUMBER(38) REFERENCES boundary_kinds(id), |
0fd7720e5c7f
Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents:
5038
diff
changeset
|
348 sectie NUMBER(38) REFERENCES sectie_kinds(id), |
0fd7720e5c7f
Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents:
5038
diff
changeset
|
349 sobek NUMBER(38) REFERENCES sobek_kinds(id), |
3677
c37effda3655
Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2862
diff
changeset
|
350 path VARCHAR(256), |
2861
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
351 id NUMBER PRIMARY KEY NOT NULL |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
352 ); |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
353 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hydr_boundaries_poly', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
354 CREATE OR REPLACE TRIGGER hydr_boundaries_poly_trigger BEFORE INSERT ON hydr_boundaries_poly FOR each ROW |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
355 BEGIN |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
356 SELECT HYDR_BOUNDARIES_POLY_ID_SEQ.nextval INTO :new.id FROM dual; |
b0132e1b9719
Added further shape importers and added the option to reproject shapes during the import process.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2852
diff
changeset
|
357 END; |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
358 / |
2862
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
359 |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
360 -- Hydrologie/Streckendaten/ |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
361 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ; |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
362 CREATE TABLE gauge_location ( |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
363 OGR_FID NUMBER(38), |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
364 GEOM MDSYS.SDO_GEOMETRY, |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
365 river_id NUMBER(38), |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
366 name VARCHAR(64), |
3677
c37effda3655
Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2862
diff
changeset
|
367 path VARCHAR(256), |
2862
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
368 id NUMBER PRIMARY KEY NOT NULL |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
369 ); |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
370 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('gauge_location', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
371 CREATE OR REPLACE TRIGGER gauge_location_trigger BEFORE INSERT ON gauge_location FOR EACH ROW |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
372 BEGIN |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
373 SELECT GAUGE_LOCATION_ID_SEQ.nextval INTO :new.id FROM dual; |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
374 END; |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
375 / |
5291
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
376 |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
377 |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
378 CREATE TABLE jetty_kinds( |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
379 id NUMBER PRIMARY KEY NOT NULL, |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
380 name VARCHAR(64) |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
381 ); |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
382 INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf'); |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
383 INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß'); |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
384 INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel'); |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
385 |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
386 CREATE SEQUENCE JETTIES_ID_SEQ; |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
387 CREATE TABLE jetties ( |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
388 OGR_FID NUMBER(38), |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
389 GEOM MDSYS.SDO_GEOMETRY, |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
390 id NUMBER PRIMARY KEY NOT NULL, |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
391 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
392 path VARCHAR(256), |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
393 kind_id NUMBER(38) REFERENCES jetty_kinds(id), |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
394 km NUMBER(7,3), |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
395 z NUMBER(38,12) |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
396 ); |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
397 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('jetties', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
398 CREATE OR REPLACE TRIGGER jetties_trigger BEFORE INSERT ON jetties FOR EACH ROW |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
399 BEGIN |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
400 SELECT JETTIES_ID_SEQ.nextval INTO :new.id FROM dual; |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
401 END; |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
402 / |