Mercurial > dive4elements > river
annotate flys-backend/doc/schema/oracle-spatial.sql @ 3945:26084ee6d352
Change methods names until the url is really be fetched from the db
flys-backend/trunk@5551 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Bjoern Ricks <bjoern.ricks@intevation.de> |
---|---|
date | Fri, 21 Sep 2012 10:47:32 +0000 |
parents | c37effda3655 |
children | 89ada0b9083f |
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, |
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
|
8 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
|
9 path VARCHAR(256), |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
10 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
11 ); |
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
|
12 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
|
13 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
|
14 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
15 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
|
16 END; |
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 --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
|
19 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
20 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
21 -- Geodaesie/Flussachse+km/km.shp |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
22 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
23 CREATE TABLE river_axes_km( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
24 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
25 GEOM MDSYS.SDO_GEOMETRY, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
26 river_id NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
27 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
|
28 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
|
29 path VARCHAR(256), |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
30 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
31 ); |
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
|
32 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
|
33 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
|
34 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
35 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
|
36 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
37 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
38 --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
|
39 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
40 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
41 --Geodaesie/Querprofile/QP-Spuren/qps.shp |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
42 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
43 CREATE TABLE cross_section_tracks ( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
44 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
45 GEOM MDSYS.SDO_GEOMETRY, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
46 river_id NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
47 km NUMBER(38,12) NOT NULL, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
48 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
|
49 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
|
50 path VARCHAR(256), |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
51 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
52 ); |
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
|
53 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
|
54 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
|
55 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
56 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
|
57 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
58 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
59 --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
|
60 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
61 |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
62 -- TODO: TestMe. Fix Importer-Script. Fix oracle_spatial_idx.sql script. |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
63 -- Geodaesie/Linien/rohre-und-speeren |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
64 CREATE SEQUENCE LINES_ID_SEQ; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
65 CREATE TABLE lines ( |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
66 OGR_FID NUMBER(38), |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
67 GEOM MDSYS.SDO_GEOMETRY, |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
68 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
|
69 kind VARCHAR2(16) NOT NULL, |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
70 z NUMBER(38,12) DEFAULT 0, |
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
|
71 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
|
72 path VARCHAR(256), |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
73 ID NUMBER PRIMARY KEY NOT NULL |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
74 ); |
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
|
75 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
|
76 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
|
77 BEGIN |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
78 SELECT LINES_ID_SEQ.nextval INTO :new.id FROM dual; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
79 END; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
80 / |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
81 -- NOTE: Should lines should be 3D. |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
82 -- TODO: Test index. |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
83 --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
|
84 -- 'kind': |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
85 -- 0: ROHR1 |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
86 -- 1: DAMM |
2339
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 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
89 -- Geodaesie/Bauwerke/Wehre.shp |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
90 CREATE SEQUENCE BUILDINGS_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
91 CREATE TABLE buildings( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
92 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
93 GEOM MDSYS.SDO_GEOMETRY, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
94 river_id NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
95 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
|
96 path VARCHAR(256), |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
97 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
98 ); |
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
|
99 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
|
100 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
|
101 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
102 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
|
103 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
104 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
105 --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
|
106 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
107 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
108 -- Geodaesie/Festpunkte/Festpunkte.shp |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
109 CREATE SEQUENCE FIXPOINTS_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
110 CREATE TABLE fixpoints ( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
111 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
112 GEOM MDSYS.SDO_GEOMETRY, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
113 river_id NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
114 x NUMBER(38,11), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
115 y NUMBER(38,11), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
116 km NUMBER(38,11) NOT NULL, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
117 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
|
118 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
|
119 path VARCHAR(256), |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
120 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
121 ); |
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
|
122 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
|
123 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
|
124 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
125 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
|
126 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
127 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
128 --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
|
129 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
130 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
131 -- Hydrologie/Hydr. Grenzen/talaue.shp |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
132 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
133 CREATE TABLE floodplain( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
134 OGR_FID NUMBER(38), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
135 GEOM MDSYS.SDO_GEOMETRY, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
136 river_id NUMBER(38), |
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
|
137 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
|
138 path VARCHAR(256), |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
139 ID NUMBER PRIMARY KEY NOT NULL |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
140 ); |
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
|
141 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
|
142 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
|
143 BEGIN |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
144 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
|
145 END; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
146 / |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
147 --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
|
148 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
149 |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
150 -- TODO: Test-Me. Fix Importer-Skript. |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
151 -- NOTE: It's not a spatial schema! |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
152 -- Geodaesie/Hoehenmodelle/* |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
153 CREATE SEQUENCE DEM_ID_SEQ; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
154 CREATE TABLE dem ( |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
155 ID NUMBER PRIMARY KEY NOT NULL, |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
156 river_id NUMBER(38), |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
157 -- 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
|
158 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
|
159 upper NUMBER(19,5), |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
160 path VARCHAR(256), |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
161 UNIQUE (river_id, lower, upper) |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
162 ); |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
163 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
|
164 BEGIN |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
165 SELECT DEM_ID_SEQ.nextval INTO :new.id FROM dual; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
166 END; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
167 / |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
168 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2338
diff
changeset
|
169 |
1244 | 170 -- Hydrologie/Einzugsgebiete/EZG.shp |
171 CREATE SEQUENCE CATCHMENT_ID_SEQ; | |
172 CREATE TABLE catchment( | |
173 OGR_FID NUMBER(38), | |
174 GEOM MDSYS.SDO_GEOMETRY, | |
175 river_id NUMBER(38), | |
176 area NUMBER(19,5), | |
2335
e1fb4a1e0b4a
Generalized oracle spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1253
diff
changeset
|
177 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
|
178 path VARCHAR(256), |
1244 | 179 ID NUMBER PRIMARY KEY NOT NULL |
180 ); | |
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
|
181 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 | 182 |
183 CREATE TRIGGER catchment_trigger BEFORE INSERT ON catchment FOR each ROW | |
184 BEGIN | |
185 SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual; | |
186 END; | |
187 / | |
1253
a75e7d922942
Deactivated spatial indexes.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1251
diff
changeset
|
188 --CREATE INDEX catchment_spatial_idx ON catchment(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=polygon'); |
1244 | 189 |
1250 | 190 --Hydrologie/HW-Schutzanlagen/hws.shp |
191 CREATE SEQUENCE HWS_ID_SEQ; | |
192 CREATE TABLE hws( | |
193 OGR_FID NUMBER(38), | |
194 GEOM MDSYS.SDO_GEOMETRY, | |
195 river_id NUMBER(38), | |
2335
e1fb4a1e0b4a
Generalized oracle spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1253
diff
changeset
|
196 hws_facility VARCHAR2(255), |
e1fb4a1e0b4a
Generalized oracle spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1253
diff
changeset
|
197 type 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
|
198 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
|
199 path VARCHAR(256), |
1250 | 200 ID NUMBER PRIMARY KEY NOT NULL |
201 ); | |
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
|
202 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 | 203 CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW |
204 BEGIN | |
205 SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual; | |
206 END; | |
207 / | |
1253
a75e7d922942
Deactivated spatial indexes.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1251
diff
changeset
|
208 --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
|
209 |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
210 |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
211 --Hydrologie/UeSG |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
212 CREATE SEQUENCE FLOODMAPS_ID_SEQ; |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
213 CREATE TABLE floodmaps ( |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
214 OGR_FID NUMBER(38), |
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
215 GEOM MDSYS.SDO_GEOMETRY, |
2796
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
216 river_id NUMBER(38), |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
217 name VARCHAR(255), |
2796
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
218 kind NUMBER(38), |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
219 diff NUMBER(19,5), |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
220 count NUMBER(38), |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
221 area NUMBER(19,5), |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
222 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
|
223 path VARCHAR(256), |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
224 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
|
225 ); |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
226 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
|
227 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
|
228 BEGIN |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2370
diff
changeset
|
229 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
|
230 END; |
2852
875a87b8489f
Repaired broken oracle spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2803
diff
changeset
|
231 / |
2800
efb10ed2af96
Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2796
diff
changeset
|
232 CREATE INDEX floodmaps_spatial_idx ON floodmaps(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON'); |
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
|
233 |
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
|
234 |
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
|
235 --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
|
236 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
|
237 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
|
238 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
|
239 GEOM MDSYS.SDO_GEOMETRY, |
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
|
240 river_id 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
|
241 name VARCHAR(255), |
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
|
242 kind NUMBER(38), |
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
|
243 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
|
244 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
|
245 ); |
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
|
246 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
|
247 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
|
248 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
|
249 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
|
250 END; |
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
|
251 / |
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
|
252 CREATE INDEX hydr_boundaries_idx ON hydr_boundaries(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); |
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
|
253 |
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
|
254 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
|
255 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
|
256 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
|
257 GEOM MDSYS.SDO_GEOMETRY, |
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
|
258 river_id 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
|
259 name VARCHAR(255), |
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
|
260 kind NUMBER(38), |
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
|
261 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
|
262 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
|
263 ); |
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
|
264 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
|
265 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
|
266 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
|
267 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
|
268 END; |
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
|
269 / |
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
|
270 CREATE INDEX hydr_boundaries_poly_idx ON hydr_boundaries_poly(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON'); |
2862
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
271 |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
272 |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
273 -- Hydrologie/Streckendaten/ |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
274 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ; |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
275 CREATE TABLE gauge_location ( |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
276 OGR_FID NUMBER(38), |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
277 GEOM MDSYS.SDO_GEOMETRY, |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
278 river_id NUMBER(38), |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
279 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
|
280 path VARCHAR(256), |
2862
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
281 id NUMBER PRIMARY KEY NOT NULL |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
282 ); |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
283 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
|
284 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
|
285 BEGIN |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
286 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
|
287 END; |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
288 / |
998f1a7dcfde
New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2861
diff
changeset
|
289 CREATE INDEX gauge_location_idx ON gauge_location(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT'); |