Mercurial > dive4elements > river
annotate flys-backend/doc/schema/oracle-spatial.sql @ 2807:5ac1db5156be
Some oracle db schema adaptions specific to bed heights in MINFO.
flys-backend/trunk@4215 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Ingo Weinzierl <ingo.weinzierl@intevation.de> |
---|---|
date | Wed, 11 Apr 2012 13:09:46 +0000 |
parents | cdc084071c14 |
children | 875a87b8489f |
rev | line source |
---|---|
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
1 -- Geodaesie/Flussachse+km/achse |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
2 CREATE SEQUENCE RIVER_AXES_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
3 CREATE TABLE river_axes( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
4 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
5 GEOM MDSYS.SDO_GEOMETRY, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
6 river_id NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
7 kind NUMBER(38) DEFAULT 0 NOT NULL, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
8 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
9 ); |
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
|
10 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
|
11 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
|
12 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
13 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
|
14 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
15 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
16 --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
|
17 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
18 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
19 -- Geodaesie/Flussachse+km/km.shp |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
20 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
21 CREATE TABLE river_axes_km( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
22 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
23 GEOM MDSYS.SDO_GEOMETRY, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
24 river_id NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
25 km NUMBER(6,3), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
26 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
27 ); |
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
|
28 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
|
29 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
|
30 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
31 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
|
32 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
33 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
34 --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
|
35 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
36 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
37 --Geodaesie/Querprofile/QP-Spuren/qps.shp |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
38 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
39 CREATE TABLE cross_section_tracks ( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
40 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
41 GEOM MDSYS.SDO_GEOMETRY, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
42 river_id NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
43 km NUMBER(38,12) NOT NULL, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
44 z NUMBER(38,12) DEFAULT 0 NOT NULL, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
45 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
46 ); |
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
|
47 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
|
48 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
|
49 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
50 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
|
51 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
52 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
53 --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
|
54 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
55 |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
56 -- TODO: TestMe. Fix Importer-Script. Fix oracle_spatial_idx.sql script. |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
57 -- Geodaesie/Linien/rohre-und-speeren |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
58 CREATE SEQUENCE LINES_ID_SEQ; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
59 CREATE TABLE lines ( |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
60 OGR_FID NUMBER(38), |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
61 GEOM MDSYS.SDO_GEOMETRY, |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
62 river_id NUMBER(38), |
2365
60532f75396b
Adapted 'lines' relation and made lines queryable for a given river.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2340
diff
changeset
|
63 kind VARCHAR2(16) NOT NULL, |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
64 z NUMBER(38,12) DEFAULT 0, |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
65 ID NUMBER PRIMARY KEY NOT NULL |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
66 ); |
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
|
67 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('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); |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
68 CREATE OR REPLACE TRIGGER lines_trigger BEFORE INSERT ON lines FOR each ROW |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
69 BEGIN |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
70 SELECT LINES_ID_SEQ.nextval INTO :new.id FROM dual; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
71 END; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
72 / |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
73 -- NOTE: Should lines should be 3D. |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
74 -- TODO: Test index. |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
75 --CREATE INDEX lines_idx ON lines(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
76 -- 'kind': |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
77 -- 0: ROHR1 |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
78 -- 1: DAMM |
2339
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 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
81 -- Geodaesie/Bauwerke/Wehre.shp |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
82 CREATE SEQUENCE BUILDINGS_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
83 CREATE TABLE buildings( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
84 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
85 GEOM MDSYS.SDO_GEOMETRY, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
86 river_id NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
87 name VARCHAR2(255), |
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, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
104 river_id NUMBER(38), |
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), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
109 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
110 ); |
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
|
111 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
|
112 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
|
113 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
114 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
|
115 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
116 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
117 --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
|
118 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
119 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
120 -- Hydrologie/Hydr. Grenzen/talaue.shp |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
121 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
122 CREATE TABLE floodplain( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
123 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
124 GEOM MDSYS.SDO_GEOMETRY, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
125 river_id NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
126 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
127 ); |
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
|
128 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
|
129 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
|
130 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
131 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
|
132 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
133 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
134 --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
|
135 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
136 |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
137 -- TODO: Test-Me. Fix Importer-Skript. |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
138 -- NOTE: It's not a spatial schema! |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
139 -- Geodaesie/Hoehenmodelle/* |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
140 CREATE SEQUENCE DEM_ID_SEQ; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
141 CREATE TABLE dem ( |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
142 ID NUMBER PRIMARY KEY NOT NULL, |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
143 river_id NUMBER(38), |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
144 -- XXX Should we use the ranges table instead? |
2803
cdc084071c14
Adapted the column type of relation 'dem' in oracle-spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2800
diff
changeset
|
145 lower NUMBER(19,5), |
cdc084071c14
Adapted the column type of relation 'dem' in oracle-spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2800
diff
changeset
|
146 upper NUMBER(19,5), |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
147 path VARCHAR(256), |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
148 UNIQUE (river_id, lower, upper) |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
149 ); |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
150 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
|
151 BEGIN |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
152 SELECT DEM_ID_SEQ.nextval INTO :new.id FROM dual; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
153 END; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
154 / |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
155 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
156 |
1244 | 157 -- Hydrologie/Einzugsgebiete/EZG.shp |
158 CREATE SEQUENCE CATCHMENT_ID_SEQ; | |
159 CREATE TABLE catchment( | |
160 OGR_FID NUMBER(38), | |
161 GEOM MDSYS.SDO_GEOMETRY, | |
162 river_id NUMBER(38), | |
163 area NUMBER(19,5), | |
2335
e1fb4a1e0b4a
Generalized oracle spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1253
diff
changeset
|
164 name VARCHAR2(255), |
1244 | 165 ID NUMBER PRIMARY KEY NOT NULL |
166 ); | |
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
|
167 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('CATCHMENT', '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); |
1244 | 168 |
169 CREATE TRIGGER catchment_trigger BEFORE INSERT ON catchment FOR each ROW | |
170 BEGIN | |
171 SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual; | |
172 END; | |
173 / | |
1253
a75e7d922942
Deactivated spatial indexes.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1251
diff
changeset
|
174 --CREATE INDEX catchment_spatial_idx ON catchment(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=polygon'); |
1244 | 175 |
1250 | 176 --Hydrologie/HW-Schutzanlagen/hws.shp |
177 CREATE SEQUENCE HWS_ID_SEQ; | |
178 CREATE TABLE hws( | |
179 OGR_FID NUMBER(38), | |
180 GEOM MDSYS.SDO_GEOMETRY, | |
181 river_id NUMBER(38), | |
2335
e1fb4a1e0b4a
Generalized oracle spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1253
diff
changeset
|
182 hws_facility VARCHAR2(255), |
e1fb4a1e0b4a
Generalized oracle spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1253
diff
changeset
|
183 type VARCHAR2(255), |
1250 | 184 ID NUMBER PRIMARY KEY NOT NULL |
185 ); | |
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
|
186 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws', '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); |
1250 | 187 CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW |
188 BEGIN | |
189 SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual; | |
190 END; | |
191 / | |
1253
a75e7d922942
Deactivated spatial indexes.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1251
diff
changeset
|
192 --CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); |
2796
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
193 |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
194 |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
195 --Hydrologie/UeSG |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
196 CREATE SEQUENCE FLOODMAPS_ID_SEQ; |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
197 CREATE TABLE floodmaps ( |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
198 OGR_FID NUMBER(38), |
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
199 GEOM MDSYS.SDO_GEOMETRY, |
2796
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
200 river_id NUMBER(38), |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
201 name VARCHAR(255), |
2796
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
202 kind NUMBER(38), |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
203 diff NUMBER(19,5), |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
204 count NUMBER(38), |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
205 area NUMBER(19,5), |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
206 perimeter NUMBER(19,5), |
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
207 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
|
208 ); |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
209 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
|
210 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
|
211 BEGIN |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
212 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
|
213 END; |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
214 CREATE INDEX floodmaps_spatial_idx ON floodmaps(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON'); |
2796
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
215 / |