Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial.sql @ 2339:b28ab244a77d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Harmonized oracle and postgresql spatial schema. flys-backend/trunk@2817 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Bjoern Schilberg <bjoern@intevation.de>
date Fri, 23 Sep 2011 10:42:25 +0000
parents e69ef36290ae
children c4f090a00178
comparison
equal deleted inserted replaced
2338:e69ef36290ae 2339:b28ab244a77d
1 -- Geodaesie/Flussachse+km/achse
2 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
3 CREATE TABLE river_axes(
4 OGR_FID NUMBER(38),
5 GEOM MDSYS.SDO_GEOMETRY,
6 river_id NUMBER(38),
7 kind NUMBER(38) DEFAULT 0 NOT NULL,
8 ID NUMBER PRIMARY KEY NOT NULL
9 );
10 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
11 CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW
12 BEGIN
13 SELECT RIVER_AXES_ID_SEQ.nextval INTO :new.id FROM dual;
14 END;
15 /
16 --CREATE INDEX river_axes_spatial_idx ON river_axes(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
17
18
19 -- Geodaesie/Flussachse+km/km.shp
20 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
21 CREATE TABLE river_axes_km(
22 OGR_FID NUMBER(38),
23 GEOM MDSYS.SDO_GEOMETRY,
24 river_id NUMBER(38),
25 km NUMBER(6,3),
26 ID NUMBER PRIMARY KEY NOT NULL
27 );
28 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes_km', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
29 CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW
30 BEGIN
31 SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual;
32 END;
33 /
34 --CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point');
35
36
37 --Geodaesie/Querprofile/QP-Spuren/qps.shp
38 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
39 CREATE TABLE cross_section_tracks (
40 OGR_FID NUMBER(38),
41 GEOM MDSYS.SDO_GEOMETRY,
42 river_id NUMBER(38),
43 km NUMBER(38,12) NOT NULL,
44 z NUMBER(38,12) DEFAULT 0 NOT NULL,
45 ID NUMBER PRIMARY KEY NOT NULL
46 );
47 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('cross_section_tracks', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
48 CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW
49 BEGIN
50 SELECT CROSS_SECTION_TRACKS_ID_SEQ.nextval INTO :new.id FROM dual;
51 END;
52 /
53 --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
54
55
56 -- TODO
57 -- Geodaesie/Linien/rohre-und-spreen
58
59
60 -- Geodaesie/Bauwerke/Wehre.shp
61 CREATE SEQUENCE BUILDINGS_ID_SEQ;
62 CREATE TABLE buildings(
63 OGR_FID NUMBER(38),
64 GEOM MDSYS.SDO_GEOMETRY,
65 river_id NUMBER(38),
66 name VARCHAR2(255),
67 ID NUMBER PRIMARY KEY NOT NULL
68 );
69 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('buildings', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
70 CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW
71 BEGIN
72 SELECT BUILDINGS_ID_SEQ.nextval INTO :new.id FROM dual;
73 END;
74 /
75 --CREATE INDEX buildings_spatial_idx ON buildings(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
76
77
78 -- Geodaesie/Festpunkte/Festpunkte.shp
79 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
80 CREATE TABLE fixpoints (
81 OGR_FID NUMBER(38),
82 GEOM MDSYS.SDO_GEOMETRY,
83 river_id NUMBER(38),
84 x NUMBER(38,11),
85 y NUMBER(38,11),
86 km NUMBER(38,11) NOT NULL,
87 HPGP VARCHAR2(255),
88 ID NUMBER PRIMARY KEY NOT NULL
89 );
90 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('fixpoints', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
91 CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW
92 BEGIN
93 SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual;
94 END;
95 /
96 --CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
97
98
99 -- Hydrologie/Hydr. Grenzen/talaue.shp
100 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
101 CREATE TABLE floodplain(
102 OGR_FID NUMBER(38),
103 GEOM MDSYS.SDO_GEOMETRY,
104 river_id NUMBER(38),
105 ID NUMBER PRIMARY KEY NOT NULL
106 );
107 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodplain', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
108 CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW
109 BEGIN
110 SELECT FLOODPLAIN_ID_SEQ.nextval INTO :new.id FROM dual;
111 END;
112 /
113 --CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON');
114
115
116 -- TODO
117 -- Geodaesie/Hoehenmodelle/*
118
119
1 -- Hydrologie/Einzugsgebiete/EZG.shp 120 -- Hydrologie/Einzugsgebiete/EZG.shp
2 CREATE SEQUENCE CATCHMENT_ID_SEQ; 121 CREATE SEQUENCE CATCHMENT_ID_SEQ;
3 CREATE TABLE catchment( 122 CREATE TABLE catchment(
4 OGR_FID NUMBER(38), 123 OGR_FID NUMBER(38),
5 GEOM MDSYS.SDO_GEOMETRY, 124 GEOM MDSYS.SDO_GEOMETRY,
15 SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual; 134 SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual;
16 END; 135 END;
17 / 136 /
18 --CREATE INDEX catchment_spatial_idx ON catchment(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=polygon'); 137 --CREATE INDEX catchment_spatial_idx ON catchment(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=polygon');
19 138
20 -- Geodaesie/Flussachse+km/km.shp
21 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
22 CREATE TABLE river_axes_km(
23 OGR_FID NUMBER(38),
24 GEOM MDSYS.SDO_GEOMETRY,
25 river_id NUMBER(38),
26 km NUMBER(6,3),
27 ID NUMBER PRIMARY KEY NOT NULL
28 );
29 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes_km', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
30 CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW
31 BEGIN
32 SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual;
33 END;
34 /
35 --CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point');
36
37 -- Geodaesie/Bauwerke/Wehre.shp
38 CREATE SEQUENCE BUILDINGS_ID_SEQ;
39 CREATE TABLE buildings(
40 OGR_FID NUMBER(38),
41 GEOM MDSYS.SDO_GEOMETRY,
42 river_id NUMBER(38),
43 name VARCHAR2(255),
44 ID NUMBER PRIMARY KEY NOT NULL
45 );
46 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('buildings', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
47 CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW
48 BEGIN
49 SELECT BUILDINGS_ID_SEQ.nextval INTO :new.id FROM dual;
50 END;
51 /
52 --CREATE INDEX buildings_spatial_idx ON buildings(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
53
54 -- Geodaesie/Festpunkte/Festpunkte.shp
55 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
56 CREATE TABLE fixpoints (
57 OGR_FID NUMBER(38),
58 GEOM MDSYS.SDO_GEOMETRY,
59 river_id NUMBER(38),
60 x NUMBER(38,11),
61 y NUMBER(38,11),
62 km NUMBER(38,11) NOT NULL,
63 HPGP VARCHAR2(255),
64 ID NUMBER PRIMARY KEY NOT NULL
65 );
66 --Extent: (2539388.036000, 5450896.688000) - (2575586.296000, 5507370.606000)
67 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('fixpoints', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
68 CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW
69 BEGIN
70 SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual;
71 END;
72 /
73 --CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
74
75 -- Geodaesie/Flussachse+km/achse
76 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
77 CREATE TABLE river_axes(
78 OGR_FID NUMBER(38),
79 GEOM MDSYS.SDO_GEOMETRY,
80 river_id NUMBER(38),
81 kind NUMBER(38) DEFAULT 0 NOT NULL,
82 ID NUMBER PRIMARY KEY NOT NULL
83 );
84 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
85 CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW
86 BEGIN
87 SELECT RIVER_AXES_ID_SEQ.nextval INTO :new.id FROM dual;
88 END;
89 /
90 --CREATE INDEX river_axes_spatial_idx ON river_axes(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
91
92 --Geodaesie/Querprofile/QP-Spuren/qps.shp
93 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
94 CREATE TABLE cross_section_tracks (
95 OGR_FID NUMBER(38),
96 GEOM MDSYS.SDO_GEOMETRY,
97 river_id NUMBER(38),
98 km NUMBER(38,12) NOT NULL,
99 z NUMBER(38,12) DEFAULT 0 NOT NULL,
100 ID NUMBER PRIMARY KEY NOT NULL
101 );
102 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('cross_section_tracks', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
103 CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW
104 BEGIN
105 SELECT CROSS_SECTION_TRACKS_ID_SEQ.nextval INTO :new.id FROM dual;
106 END;
107 /
108 --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
109
110 --Hydrologie/HW-Schutzanlagen/hws.shp 139 --Hydrologie/HW-Schutzanlagen/hws.shp
111 CREATE SEQUENCE HWS_ID_SEQ; 140 CREATE SEQUENCE HWS_ID_SEQ;
112 CREATE TABLE hws( 141 CREATE TABLE hws(
113 OGR_FID NUMBER(38), 142 OGR_FID NUMBER(38),
114 GEOM MDSYS.SDO_GEOMETRY, 143 GEOM MDSYS.SDO_GEOMETRY,
122 BEGIN 151 BEGIN
123 SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual; 152 SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual;
124 END; 153 END;
125 / 154 /
126 --CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); 155 --CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
127
128 -- Hydrologie/Hydr. Grenzen/talaue.shp
129 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
130 CREATE TABLE floodplain(
131 OGR_FID NUMBER(38),
132 GEOM MDSYS.SDO_GEOMETRY,
133 river_id NUMBER(38),
134 ID NUMBER PRIMARY KEY NOT NULL
135 );
136 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodplain', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466);
137 CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW
138 BEGIN
139 SELECT FLOODPLAIN_ID_SEQ.nextval INTO :new.id FROM dual;
140 END;
141 /
142 --CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON');
http://dive4elements.wald.intevation.org