Mercurial > dive4elements > river

annotate flys-backend/doc/schema/oracle-spatial.sql @ 2812:a36a5407acbf

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. flys-backend/trunk@4228 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Fri, 13 Apr 2012 08:16:55 +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
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
157 -- Hydrologie/Einzugsgebiete/EZG.shp
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
158 CREATE SEQUENCE CATCHMENT_ID_SEQ;
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
159 CREATE TABLE catchment(
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
160 OGR_FID NUMBER(38),
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
161 GEOM MDSYS.SDO_GEOMETRY,
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
162 river_id NUMBER(38),
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
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
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
165 ID NUMBER PRIMARY KEY NOT NULL
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
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
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
168
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
169 CREATE TRIGGER catchment_trigger BEFORE INSERT ON catchment FOR each ROW
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
170 BEGIN
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
171 SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual;
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
172 END;
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
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
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
175
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
176 --Hydrologie/HW-Schutzanlagen/hws.shp
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
177 CREATE SEQUENCE HWS_ID_SEQ;
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
178 CREATE TABLE hws(
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
179 OGR_FID NUMBER(38),
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
180 GEOM MDSYS.SDO_GEOMETRY,
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
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
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
184 ID NUMBER PRIMARY KEY NOT NULL
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
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
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
187 CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
188 BEGIN
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
189 SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual;
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
190 END;
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
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 /
http://dive4elements.wald.intevation.org