Mercurial > dive4elements > river

annotate flys-backend/doc/schema/oracle-spatial.sql @ 5025:9ef49f3b0304 dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Sync Posgres and Oracle spatial schema
author Andre Heinecke <aheinecke@intevation.de>
date Tue, 19 Feb 2013 14:58:33 +0100
parents eecd1ebba128
children a5f438895a38
rev   line source
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
1 WHENEVER SQLERROR EXIT SQL.ERRORCODE ROLLBACK;
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
2 -- Geodaesie/Flussachse+km/achse
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
3 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
4 CREATE TABLE river_axes(
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
5 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
6 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
7 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
8 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
9 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
10 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
11 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
12 );
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
13 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
14 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
15 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
16 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
17 END;
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 --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
20
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
21
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
22 -- Geodaesie/Flussachse+km/km.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
23 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
24 CREATE TABLE river_axes_km(
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
25 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
26 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
27 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
28 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
29 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
30 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
31 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
32 );
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
33 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
34 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
35 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
36 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
37 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
38 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
39 --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
40
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
41
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
42 --Geodaesie/Querprofile/QP-Spuren/qps.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
43 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
44 CREATE TABLE cross_section_tracks (
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
45 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
46 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
47 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
48 km NUMBER(38,12) NOT NULL,
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
49 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
50 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
51 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
52 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
53 );
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
54 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
55 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
56 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
57 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
58 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
59 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
60 --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
61
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
62
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
63 -- Geodaesie/Bauwerke/Wehre.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
64 CREATE SEQUENCE BUILDINGS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
65 CREATE TABLE buildings(
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
66 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
67 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
68 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
69 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
70 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
71 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
72 );
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
73 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
74 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
75 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
76 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
77 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
78 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
79 --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
80
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
81
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
82 -- Geodaesie/Festpunkte/Festpunkte.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
83 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
84 CREATE TABLE fixpoints (
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
85 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
86 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
87 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
88 x NUMBER(38,11),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
89 y NUMBER(38,11),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
90 km NUMBER(38,11) NOT NULL,
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
91 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
92 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
93 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
94 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
95 );
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
96 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
97 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
98 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
99 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
100 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
101 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
102 --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
103
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 -- Hydrologie/Hydr. Grenzen/talaue.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
106 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
107 CREATE TABLE floodplain(
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
108 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
109 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
110 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
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
111 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
112 path VARCHAR(256),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
113 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
114 );
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
115 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
116 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
117 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
118 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
119 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
120 /
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
121 --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
122
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
123
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
124 -- TODO: Test-Me. Fix Importer-Skript.
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
125 -- NOTE: It's not a spatial schema!
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
126 -- Geodaesie/Hoehenmodelle/*
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
127 CREATE SEQUENCE DEM_ID_SEQ;
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
128 CREATE TABLE dem (
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
129 ID NUMBER PRIMARY KEY NOT NULL,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
130 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
131 -- XXX Should we use the ranges table instead?
3951
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3677
diff changeset
132 name VARCHAR(64),
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3677
diff changeset
133 lower NUMBER(19,5),
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3677
diff changeset
134 upper NUMBER(19,5),
3961
f5912365619c Adapted the spatial schema for DEMs and the DgmSqlConverter.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3951
diff changeset
135 year_from VARCHAR(32) NOT NULL,
f5912365619c Adapted the spatial schema for DEMs and the DgmSqlConverter.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3951
diff changeset
136 year_to VARCHAR(32) NOT NULL,
f5912365619c Adapted the spatial schema for DEMs and the DgmSqlConverter.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3951
diff changeset
137 projection VARCHAR(32) NOT NULL,
3951
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3677
diff changeset
138 elevation_state VARCHAR(32),
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
139 srid NUMBER NOT NULL,
3951
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3677
diff changeset
140 format VARCHAR(32),
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
141 border_break NUMBER(1) DEFAULT 0 NOT NULL,
3951
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3677
diff changeset
142 resolution VARCHAR(16),
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3677
diff changeset
143 description VARCHAR(256),
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3677
diff changeset
144 path VARCHAR(256)
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
145 );
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
146 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
147 BEGIN
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
148 SELECT DEM_ID_SEQ.nextval INTO :new.id FROM dual;
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
149 END;
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
150 /
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
151
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2338
diff changeset
152
1244
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
153 -- Hydrologie/Einzugsgebiete/EZG.shp
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
154 CREATE SEQUENCE CATCHMENT_ID_SEQ;
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
155 CREATE TABLE catchment(
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
156 OGR_FID NUMBER(38),
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
157 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
158 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
1244
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
159 area NUMBER(19,5),
2335
e1fb4a1e0b4a Generalized oracle spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1253
diff changeset
160 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
161 path VARCHAR(256),
1244
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
162 ID NUMBER PRIMARY KEY NOT NULL
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
163 );
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
164 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
165
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
166 CREATE TRIGGER catchment_trigger BEFORE INSERT ON catchment FOR each ROW
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
167 BEGIN
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
168 SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual;
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
169 END;
281f38fc6c3e Initial oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
170 /
1253
a75e7d922942 Deactivated spatial indexes.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1251
diff changeset
171 --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
172
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
173 --Static lookup tables for Hochwasserschutzanlagen
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
174 CREATE TABLE hws_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
175 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
176 kind VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
177 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
178 INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
179 INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
180 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
181
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
182 CREATE TABLE fed_states (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
183 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
184 name VARCHAR(23) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
185 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
186 INSERT INTO fed_states (id, name) VALUES (1, 'Bayern');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
187 INSERT INTO fed_states (id, name) VALUES (2, 'Hessen');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
188 INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
189 INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
190 INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
191 INSERT INTO fed_states (id, name) VALUES (6, 'Saarland');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
192 INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
193 INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
194 INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
195 INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
196 INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
197 INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
198 INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
199 INSERT INTO fed_states (id, name) VALUES (14, 'Berlin');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
200 INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
201 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
202
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
203 --Hydrologie/HW-Schutzanlagen/hws.shp
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
204 -- HWS-Lines
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
205 CREATE SEQUENCE HWS_LINES_ID_SEQ;
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
206 CREATE TABLE hws_lines (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
207 id NUMBER PRIMARY KEY NOT NULL,
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
208 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
209 ogr_fid NUMBER,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
210 kind_id NUMBER(2) DEFAULT 2 REFERENCES hws_kinds(id),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
211 fed_state_id NUMBER(2) REFERENCES fed_states(id),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
212 river_id NUMBER(38) REFERENCES rivers(id),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
213 name VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
214 path VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
215 official NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
216 agency VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
217 range VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
218 shore_side NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
219 source VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
220 status_date TIMESTAMP,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
221 description VARCHAR(256)
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
222 );
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
223 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);
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
224 CREATE INDEX hws_lines_spatial_idx ON hws_lines(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
225
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
226 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
227 BEGIN
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
228 SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual;
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
229 END;
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
230
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
231 -- HWS Points lookup tables
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
232 CREATE TABLE sectie_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
233 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
234 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
235 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
236 INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
237 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
238 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
239 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
240
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
241 CREATE TABLE sobek_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
242 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
243 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
244 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
245 INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
246 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
247 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
248
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
249 CREATE TABLE boundary_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
250 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
251 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
252 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
253 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
254 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
255 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
256 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
257
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
258
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
259 -- HWS Points
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
260 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
261 CREATE TABLE hws_points (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
262 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
263 GEOM MDSYS.SDO_GEOMETRY,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
264 ogr_fid NUMBER,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
265 kind_id NUMBER DEFAULT 2 REFERENCES hws_kinds(id),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
266 fed_state_id NUMBER REFERENCES fed_states(id),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
267 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
268 name VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
269 path VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
270 official NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
271 agency VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
272 range VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
273 shore_side NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
274 source VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
275 status_date VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
276 description VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
277 freeboard NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
278 dike_km NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
279 z NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
280 z_target NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
281 rated_level NUMBER(19,5)
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
282 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
283
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
284 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws_points', '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);
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
285 CREATE INDEX hws_lines_spatial_idx ON hws_pints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
286
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
287 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
288 BEGIN
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
289 SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual;
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
290 END;
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
291
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
292
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
293 --Hydrologie/UeSG
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
294 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
295 CREATE TABLE floodmaps (
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
296 OGR_FID NUMBER(38),
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
297 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
298 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
299 name VARCHAR(255),
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
300 kind NUMBER(38),
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
301 diff NUMBER(19,5),
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
302 count NUMBER(38),
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
303 area NUMBER(19,5),
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
304 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
305 path VARCHAR(256),
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
306 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
307 );
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
308 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
309 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
310 BEGIN
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
311 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
312 END;
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
313
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
314 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
315
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
316
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
317 --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
318 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
319 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
320 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
321 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
322 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
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
323 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
324 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
325 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
326 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
327 );
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
328 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
329 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
330 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
331 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
332 END;
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
333
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
334 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
335
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
336 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
337 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
338 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
339 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
340 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
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
341 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
342 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
343 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
344 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
345 );
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
346 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
347 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
348 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
349 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
350 END;
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
351
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
352 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
353
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
354
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
355 -- Hydrologie/Streckendaten/
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
356 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
357 CREATE TABLE gauge_location (
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
358 OGR_FID NUMBER(38),
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
359 GEOM MDSYS.SDO_GEOMETRY,
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
360 river_id NUMBER(38),
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
361 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
362 path VARCHAR(256),
2862
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
363 id NUMBER PRIMARY KEY NOT NULL
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
364 );
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
365 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
366 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
367 BEGIN
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
368 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
369 END;
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
370
2862
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
371 CREATE INDEX gauge_location_idx ON gauge_location(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
http://dive4elements.wald.intevation.org