Mercurial > dive4elements > river

annotate flys-backend/doc/schema/oracle-spatial.sql @ 5084:ca45dd039b54 slt-simplify-cross-sections

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Log the number of points of cross sections to see the effect of the simplification.
author Sascha L. Teichmann <teichmann@intevation.de>
date Sun, 24 Feb 2013 21:38:09 +0100
parents 0fd7720e5c7f
children c4b28de753de
rev   line source
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
1 WHENEVER SQLERROR EXIT;
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
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
152 --Static lookup tables for Hochwasserschutzanlagen
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
153 CREATE TABLE hws_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
154 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
155 kind VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
156 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
157 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
158 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
159 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
160
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
161 CREATE TABLE fed_states (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
162 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
163 name VARCHAR(23) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
164 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
165 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
166 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
167 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
168 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
169 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
170 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
171 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
172 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
173 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
174 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
175 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
176 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
177 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
178 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
179 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
180 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
181
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
182 --Hydrologie/HW-Schutzanlagen/hws.shp
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
183 -- HWS-Lines
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
184 CREATE SEQUENCE HWS_LINES_ID_SEQ;
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
185 CREATE TABLE hws_lines (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
186 id NUMBER PRIMARY KEY NOT NULL,
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
187 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
188 ogr_fid NUMBER,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
189 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
190 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
191 river_id NUMBER(38) REFERENCES rivers(id),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
192 name VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
193 path VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
194 official NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
195 agency VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
196 range VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
197 shore_side NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
198 source VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
199 status_date TIMESTAMP,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
200 description VARCHAR(256)
1250
660116539429 Added schema hws.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1249
diff changeset
201 );
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
202 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
203 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
204 BEGIN
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
205 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
206 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
207 /
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
208 -- HWS Points lookup tables
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
209 CREATE TABLE sectie_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
210 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
211 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
212 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
213 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
214 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
215 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
216 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
217
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
218 CREATE TABLE sobek_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
219 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
220 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
221 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
222 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
223 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
224 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
225
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
226 CREATE TABLE boundary_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
227 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
228 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
229 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
230 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
231 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
232 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
233 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
234
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
235 -- HWS Points
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
236 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
237 CREATE TABLE hws_points (
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
238 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
239 GEOM MDSYS.SDO_GEOMETRY,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
240 ogr_fid NUMBER,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
241 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
242 fed_state_id NUMBER REFERENCES fed_states(id),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
243 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
244 name VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
245 path VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
246 official NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
247 agency VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
248 range VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
249 shore_side NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
250 source VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
251 status_date VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
252 description VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
253 freeboard NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
254 dike_km NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
255 z NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
256 z_target NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
257 rated_level NUMBER(19,5)
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
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
260 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
261
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
262 CREATE OR REPLACE TRIGGER hws_points_trigger BEFORE INSERT ON hws_points FOR each ROW
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
263 BEGIN
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
264 SELECT HWS_POINTS_ID_SEQ.nextval INTO :new.id FROM dual;
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
265 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
266 /
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
267
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
268 --Hydrologie/UeSG
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
269 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
270 CREATE TABLE floodmaps (
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
271 OGR_FID NUMBER(38),
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
272 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
273 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
274 name VARCHAR(255),
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
275 kind NUMBER(38),
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
276 diff NUMBER(19,5),
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
277 count NUMBER(38),
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
278 area NUMBER(19,5),
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
279 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
280 path VARCHAR(256),
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
281 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
282 );
2800
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
283 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
284 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
285 BEGIN
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2370
diff changeset
286 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
287 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
288 /
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
289
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
290 --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
291 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
292 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
293 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
294 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
295 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
296 name VARCHAR(255),
5078
0fd7720e5c7f Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents: 5038
diff changeset
297 kind NUMBER(38) REFERENCES boundary_kinds(id),
0fd7720e5c7f Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents: 5038
diff changeset
298 sectie NUMBER(38) REFERENCES sectie_kinds(id),
0fd7720e5c7f Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents: 5038
diff changeset
299 sobek NUMBER(38) REFERENCES sobek_kinds(id),
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
300 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
301 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
302 );
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
303 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
304 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
305 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
306 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
307 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
308 /
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
309
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
310 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
311 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
312 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
313 GEOM MDSYS.SDO_GEOMETRY,
5025
9ef49f3b0304 Sync Posgres and Oracle spatial schema
Andre Heinecke <aheinecke@intevation.de>
parents: 5013
diff changeset
314 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
315 name VARCHAR(255),
5078
0fd7720e5c7f Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents: 5038
diff changeset
316 kind NUMBER(38) REFERENCES boundary_kinds(id),
0fd7720e5c7f Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents: 5038
diff changeset
317 sectie NUMBER(38) REFERENCES sectie_kinds(id),
0fd7720e5c7f Oracle Schema, add overlooked sectie and sobek fields
Andre Heinecke <aheinecke@intevation.de>
parents: 5038
diff changeset
318 sobek NUMBER(38) REFERENCES sobek_kinds(id),
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
319 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
320 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
321 );
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
322 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
323 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
324 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
325 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
326 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
327 /
2862
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
328
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
329 -- Hydrologie/Streckendaten/
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
330 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
331 CREATE TABLE gauge_location (
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
332 OGR_FID NUMBER(38),
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
333 GEOM MDSYS.SDO_GEOMETRY,
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
334 river_id NUMBER(38),
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
335 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
336 path VARCHAR(256),
2862
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
337 id NUMBER PRIMARY KEY NOT NULL
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
338 );
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
339 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
340 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
341 BEGIN
998f1a7dcfde New shape importers and schema modifications.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2861
diff changeset
342 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
343 END;
5038
57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
344 /
http://dive4elements.wald.intevation.org