Mercurial > dive4elements > river
annotate flys-backend/doc/schema/oracle-spatial.sql @ 5149:3b58bb0c55e3
Removed trailing white space
author | Sascha L. Teichmann <teichmann@intevation.de> |
---|---|
date | Fri, 01 Mar 2013 09:28:43 +0100 |
parents | d4fdd98a04f7 |
children | a9658d43b621 |
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 ); |
d4fdd98a04f7
Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents:
5127
diff
changeset
|
7 INSERT INTO axis_kinds(id, name, use_for_caclulation) VALUES (0, 'Unbekannt'); |
d4fdd98a04f7
Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents:
5127
diff
changeset
|
8 INSERT INTO axis_kinds(id, name, use_for_caclulation) VALUES (1, 'Aktuell'); |
d4fdd98a04f7
Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents:
5127
diff
changeset
|
9 INSERT INTO axis_kinds(id, name, use_for_caclulation) VALUES (2, 'Sonstige'); |
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, |
5146
d4fdd98a04f7
Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents:
5127
diff
changeset
|
17 kind_id NUMBER(38) REFERENCES axis_kinds(id) 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
|
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, |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
37 km NUMBER(6,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 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
52 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
53 CREATE TABLE cross_section_tracks ( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
54 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
55 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
56 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
|
57 km NUMBER(38,12) NOT NULL, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
58 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
|
59 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
|
60 path VARCHAR(256), |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
61 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
62 ); |
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
|
63 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
|
64 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
|
65 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
66 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
|
67 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
68 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
69 --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
|
70 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
71 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
72 -- Geodaesie/Bauwerke/Wehre.shp |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
73 CREATE SEQUENCE BUILDINGS_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
74 CREATE TABLE buildings( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
75 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
76 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
77 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
|
78 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
|
79 path VARCHAR(256), |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
80 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
81 ); |
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
|
82 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
|
83 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
|
84 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
85 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
|
86 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
87 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
88 --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
|
89 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
90 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
91 -- Geodaesie/Festpunkte/Festpunkte.shp |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
92 CREATE SEQUENCE FIXPOINTS_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
93 CREATE TABLE fixpoints ( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
94 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
95 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
96 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
|
97 x NUMBER(38,11), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
98 y NUMBER(38,11), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
99 km NUMBER(38,11) NOT NULL, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
100 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
|
101 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
|
102 path VARCHAR(256), |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
103 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
104 ); |
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
|
105 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
|
106 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
|
107 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
108 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
|
109 END; |
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 --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
|
112 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
113 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
114 -- Hydrologie/Hydr. Grenzen/talaue.shp |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
115 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
116 CREATE TABLE floodplain( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
117 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
118 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
119 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
|
120 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
|
121 path VARCHAR(256), |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
122 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
123 ); |
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
|
124 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
|
125 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
|
126 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
127 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
|
128 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
129 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
130 --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
|
131 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
132 |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
133 -- TODO: Test-Me. Fix Importer-Skript. |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
134 -- NOTE: It's not a spatial schema! |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
135 -- Geodaesie/Hoehenmodelle/* |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
136 CREATE SEQUENCE DEM_ID_SEQ; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
137 CREATE TABLE dem ( |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
138 ID NUMBER PRIMARY KEY NOT NULL, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
139 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
140 -- XXX Should we use the ranges table instead? |
3951
89ada0b9083f
Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
3677
diff
changeset
|
141 name VARCHAR(64), |
89ada0b9083f
Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
3677
diff
changeset
|
142 lower NUMBER(19,5), |
89ada0b9083f
Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
3677
diff
changeset
|
143 upper NUMBER(19,5), |
5115
c4b28de753de
removed unnecessary NOT NULL constraints from dem-table
Tom Gottfried <tom@intevation.de>
parents:
5078
diff
changeset
|
144 year_from VARCHAR(32), |
c4b28de753de
removed unnecessary NOT NULL constraints from dem-table
Tom Gottfried <tom@intevation.de>
parents:
5078
diff
changeset
|
145 year_to VARCHAR(32), |
c4b28de753de
removed unnecessary NOT NULL constraints from dem-table
Tom Gottfried <tom@intevation.de>
parents:
5078
diff
changeset
|
146 projection VARCHAR(32), |
3951
89ada0b9083f
Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
3677
diff
changeset
|
147 elevation_state VARCHAR(32), |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
148 srid NUMBER NOT NULL, |
3951
89ada0b9083f
Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
3677
diff
changeset
|
149 format VARCHAR(32), |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
150 border_break NUMBER(1) DEFAULT 0 NOT NULL, |
3951
89ada0b9083f
Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
3677
diff
changeset
|
151 resolution VARCHAR(16), |
89ada0b9083f
Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
3677
diff
changeset
|
152 description VARCHAR(256), |
5121
e4cc9aebfcf1
sensible constraints for dem-table
Tom Gottfried <tom@intevation.de>
parents:
5115
diff
changeset
|
153 path VARCHAR(256) NOT NULL |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
154 ); |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
155 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
|
156 BEGIN |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
157 SELECT DEM_ID_SEQ.nextval INTO :new.id FROM dual; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
158 END; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
159 / |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
160 |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
161 --Static lookup tables for Hochwasserschutzanlagen |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
162 CREATE TABLE hws_kinds ( |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
163 id NUMBER PRIMARY KEY NOT NULL, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
164 kind VARCHAR(64) NOT NULL |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
165 ); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
166 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
|
167 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
|
168 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
|
169 |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
170 CREATE TABLE fed_states ( |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
171 id NUMBER PRIMARY KEY NOT NULL, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
172 name VARCHAR(23) NOT NULL |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
173 ); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
174 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
|
175 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
|
176 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
|
177 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
|
178 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
|
179 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
|
180 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
|
181 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
|
182 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
|
183 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
|
184 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
|
185 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
|
186 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
|
187 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
|
188 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
|
189 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
|
190 |
1250 | 191 --Hydrologie/HW-Schutzanlagen/hws.shp |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
192 -- HWS-Lines |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
193 CREATE SEQUENCE HWS_LINES_ID_SEQ; |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
194 CREATE TABLE hws_lines ( |
5122
5ba502e78e05
Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents:
5121
diff
changeset
|
195 OGR_FID NUMBER(38), |
1250 | 196 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
197 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
|
198 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
|
199 river_id NUMBER(38) REFERENCES rivers(id), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
200 name VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
201 path VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
202 official NUMBER DEFAULT 0, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
203 agency VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
204 range VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
205 shore_side NUMBER DEFAULT 0, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
206 source VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
207 status_date TIMESTAMP, |
5122
5ba502e78e05
Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents:
5121
diff
changeset
|
208 description VARCHAR(256), |
5ba502e78e05
Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents:
5121
diff
changeset
|
209 id NUMBER PRIMARY KEY NOT NULL |
1250 | 210 ); |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
211 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
|
212 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW |
1250 | 213 BEGIN |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
214 SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual; |
1250 | 215 END; |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
216 / |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
217 -- HWS Points lookup tables |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
218 CREATE TABLE sectie_kinds ( |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
219 id NUMBER PRIMARY KEY NOT NULL, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
220 name VARCHAR(64) NOT NULL |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
221 ); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
222 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
|
223 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
|
224 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
|
225 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
|
226 |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
227 CREATE TABLE sobek_kinds ( |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
228 id NUMBER PRIMARY KEY NOT NULL, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
229 name VARCHAR(64) NOT NULL |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
230 ); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
231 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
|
232 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
|
233 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
|
234 |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
235 CREATE TABLE boundary_kinds ( |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
236 id NUMBER PRIMARY KEY NOT NULL, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
237 name VARCHAR(64) NOT NULL |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
238 ); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
239 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
|
240 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
|
241 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
|
242 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
|
243 |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
244 -- HWS Points |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
245 CREATE SEQUENCE HWS_POINTS_ID_SEQ; |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
246 CREATE TABLE hws_points ( |
5122
5ba502e78e05
Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents:
5121
diff
changeset
|
247 OGR_FID NUMBER(38), |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
248 GEOM MDSYS.SDO_GEOMETRY, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
249 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
|
250 fed_state_id NUMBER REFERENCES fed_states(id), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
251 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
|
252 name VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
253 path VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
254 official NUMBER DEFAULT 0, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
255 agency VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
256 range VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
257 shore_side NUMBER DEFAULT 0, |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
258 source VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
259 status_date VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
260 description VARCHAR(256), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
261 freeboard NUMBER(19,5), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
262 dike_km NUMBER(19,5), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
263 z NUMBER(19,5), |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
264 z_target NUMBER(19,5), |
5122
5ba502e78e05
Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents:
5121
diff
changeset
|
265 rated_level NUMBER(19,5), |
5ba502e78e05
Fix import of hws_* on Oracle
Andre Heinecke <aheinecke@intevation.de>
parents:
5121
diff
changeset
|
266 id NUMBER PRIMARY KEY NOT NULL |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
267 ); |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
268 |
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
269 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
|
270 |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
271 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
|
272 BEGIN |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
273 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
|
274 END; |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
275 / |
2796
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
276 |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
277 --Hydrologie/UeSG |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
278 CREATE SEQUENCE FLOODMAPS_ID_SEQ; |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
279 CREATE TABLE floodmaps ( |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
280 OGR_FID NUMBER(38), |
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
281 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
282 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
|
283 name VARCHAR(255), |
2796
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
284 kind NUMBER(38), |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
285 diff NUMBER(19,5), |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
286 count NUMBER(38), |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
287 area NUMBER(19,5), |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
288 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
|
289 path VARCHAR(256), |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
290 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
|
291 ); |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
292 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
|
293 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
|
294 BEGIN |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
295 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
|
296 END; |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
297 / |
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
|
298 |
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
|
299 --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
|
300 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
|
301 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
|
302 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
|
303 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
304 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
|
305 name VARCHAR(255), |
5078
0fd7720e5c7f
Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents:
5038
diff
changeset
|
306 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
|
307 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
|
308 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
|
309 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
|
310 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
|
311 ); |
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
|
312 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
|
313 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
|
314 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
|
315 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
|
316 END; |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
317 / |
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
|
318 |
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
|
319 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
|
320 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
|
321 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
|
322 GEOM MDSYS.SDO_GEOMETRY, |
5025
9ef49f3b0304
Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents:
5013
diff
changeset
|
323 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
|
324 name VARCHAR(255), |
5078
0fd7720e5c7f
Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents:
5038
diff
changeset
|
325 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
|
326 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
|
327 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
|
328 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
|
329 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
|
330 ); |
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
|
331 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
|
332 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
|
333 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
|
334 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
|
335 END; |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
336 / |
2862
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
337 |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
338 -- Hydrologie/Streckendaten/ |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
339 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ; |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
340 CREATE TABLE gauge_location ( |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
341 OGR_FID NUMBER(38), |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
342 GEOM MDSYS.SDO_GEOMETRY, |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
343 river_id NUMBER(38), |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
344 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
|
345 path VARCHAR(256), |
2862
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
346 id NUMBER PRIMARY KEY NOT NULL |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
347 ); |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
348 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
|
349 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
|
350 BEGIN |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
351 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
|
352 END; |
5038
57a845da72bd
Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents:
5028
diff
changeset
|
353 / |