Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial-migrate-dami.sql @ 5026:bdef4c74d675 dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Update migrators for changes made in the dami branch and add oracle migrator. Both are currently untested!
author Andre Heinecke <aheinecke@intevation.de>
date Tue, 19 Feb 2013 14:59:44 +0100
parents
children 0fd7720e5c7f
comparison
equal deleted inserted replaced
5025:9ef49f3b0304 5026:bdef4c74d675
1 DROP TRIGGER hws_trigger;
2 DROP TABLE hws;
3 DROP SEQUENCE HWS_ID_SEQ;
4
5 --Static lookup tables for Hochwasserschutzanlagen
6 CREATE TABLE hws_kinds (
7 id NUMBER PRIMARY KEY NOT NULL,
8 kind VARCHAR(64) NOT NULL
9 );
10 INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass');
11 INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm');
12 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
13
14 CREATE TABLE fed_states (
15 id NUMBER PRIMARY KEY NOT NULL,
16 name VARCHAR(23) NOT NULL
17 );
18 INSERT INTO fed_states (id, name) VALUES (1, 'Bayern');
19 INSERT INTO fed_states (id, name) VALUES (2, 'Hessen');
20 INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen');
21 INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen');
22 INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz');
23 INSERT INTO fed_states (id, name) VALUES (6, 'Saarland');
24 INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein');
25 INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg');
26 INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern');
27 INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen');
28 INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg');
29 INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt');
30 INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen');
31 INSERT INTO fed_states (id, name) VALUES (14, 'Berlin');
32 INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
33 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
34
35 -- HWS-Lines
36 CREATE SEQUENCE HWS_LINES_ID_SEQ;
37 CREATE TABLE hws_lines (
38 id NUMBER PRIMARY KEY NOT NULL,
39 GEOM MDSYS.SDO_GEOMETRY,
40 ogr_fid NUMBER,
41 kind_id NUMBER REFERENCES hws_kinds(id) DEFAULT 2,
42 fed_state_id NUMBER REFERENCES fed_states(id),
43 river_id NUMBER REFERENCES rivers(id),
44 name VARCHAR(256),
45 path VARCHAR(256),
46 official NUMBER DEFAULT 0,
47 agency VARCHAR(256),
48 range VARCHAR(256),
49 shore_side NUMBER DEFAULT 0,
50 source VARCHAR(256),
51 status_date TIMESTAMP,
52 description VARCHAR(256)
53 );
54 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);
55 CREATE INDEX hws_lines_spatial_idx ON hws_lines(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
56
57 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW
58 BEGIN
59 SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual;
60 END;
61
62 -- HWS Points lookup tables
63 CREATE TABLE sectie_kinds (
64 id NUMBER PRIMARY KEY NOT NULL,
65 name VARCHAR(64) NOT NULL
66 );
67 INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt');
68 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch');
69 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank');
70 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich');
71
72 CREATE TABLE sobek_kinds (
73 id NUMBER PRIMARY KEY NOT NULL,
74 name VARCHAR(64) NOT NULL
75 );
76 INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt');
77 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend');
78 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd');
79
80 CREATE TABLE boundary_kinds (
81 id NUMBER PRIMARY KEY NOT NULL,
82 name VARCHAR(64) NOT NULL
83 );
84 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
85 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
86 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
87 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
88
89 -- HWS Points
90 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
91 CREATE TABLE hws_points (
92 id NUMBER PRIMARY KEY NOT NULL,
93 GEOM MDSYS.SDO_GEOMETRY,
94 ogr_fid NUMBER(2),
95 kind_id NUMBER(2) DEFAULT 2 REFERENCES hws_kinds(id),
96 fed_state_id NUMBER(2) REFERENCES fed_states(id),
97 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
98 name VARCHAR(256),
99 path VARCHAR(256),
100 official NUMBER DEFAULT 0,
101 agency VARCHAR(256),
102 range VARCHAR(256),
103 shore_side NUMBER DEFAULT 0,
104 source VARCHAR(256),
105 status_date VARCHAR(256),
106 description VARCHAR(256),
107 freeboard NUMBER(19,5),
108 dike_km NUMBER(19,5),
109 z NUMBER(19,5),
110 z_target NUMBER(19,5),
111 rated_level NUMBER(19,5)
112 );
113
114 -- Altrications
115 ALTER TABLE dem ADD COLUMN srid NUMBER NOT NULL;
116 ALTER TABLE hydr_boundaries_poly ADD COLUMN sectie INT REFERENCES sectie_kinds(id);
117 ALTER TABLE hydr_boundaries_poly ADD COLUMN sobek INT REFERENCES sobek_kinds(id);
118 ALTER TABLE hydr_boundaries ADD COLUMN sectie INT REFERENCES sectie_kinds(id);
119 ALTER TABLE hydr_boundaries ADD COLUMN sobek INT REFERENCES sobek_kinds(id);
120 ALTER TABLE hydr_boundaries ADD COLUMN kind INT REFERENCES boundary_kinds(id);
http://dive4elements.wald.intevation.org