dive4elements/river: backend/doc/schema/oracle-spatial.sql annotate

annotate backend/doc/schema/oracle-spatial.sql @ 8644:c15cebcf60da

Remove JavaScript greeting exception. This fixes the Error "$wnd.unHighlight" is not a function exception at startup. This apperantly did not do anything (as the function does not exist) and was not necessary (as everything works as expected) So just throw it away.

author	Andre Heinecke <andre.heinecke@intevation.de>
date	Fri, 27 Mar 2015 19:10:02 +0100
parents	cc53aae06303
children	6824c447f5b0

rev	line source
6632 97145ec4e33c Schema changes: fixed typos, syntax errors and missing or obsolete statements in Oracle schema Tom Gottfried <tom@intevation.de> parents: 5886 diff changeset	1 --WHENEVER SQLERROR EXIT;
5146 d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model Andre Heinecke <aheinecke@intevation.de> parents: 5127 diff changeset	2
d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model Andre Heinecke <aheinecke@intevation.de> parents: 5127 diff changeset	3 CREATE TABLE axis_kinds(
d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model Andre Heinecke <aheinecke@intevation.de> parents: 5127 diff changeset	4 id NUMBER PRIMARY KEY NOT NULL,
d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model Andre Heinecke <aheinecke@intevation.de> parents: 5127 diff changeset	5 name VARCHAR(64)
d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model Andre Heinecke <aheinecke@intevation.de> parents: 5127 diff changeset	6 );
5162 94c3eb995bf6 Fixed broken insert statements for axis_kinds. Sascha L. Teichmann <teichmann@intevation.de> parents: 5158 diff changeset	7 INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt');
5495 1488227b97b7 Minor scheme change. Update kind names to their UI representation. Andre Heinecke <aheinecke@intevation.de> parents: 5409 diff changeset	8 INSERT INTO axis_kinds(id, name) VALUES (1, 'aktuelle Achse');
5162 94c3eb995bf6 Fixed broken insert statements for axis_kinds. Sascha L. Teichmann <teichmann@intevation.de> parents: 5158 diff changeset	9 INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige');
5146 d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model Andre Heinecke <aheinecke@intevation.de> parents: 5127 diff changeset	10
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	11 -- Geodaesie/Flussachse+km/achse
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	12 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	13 CREATE TABLE river_axes(
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	14 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	15 GEOM MDSYS.SDO_GEOMETRY,
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	16 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
6632 97145ec4e33c Schema changes: fixed typos, syntax errors and missing or obsolete statements in Oracle schema Tom Gottfried <tom@intevation.de> parents: 5886 diff changeset	17 kind_id NUMBER(38) DEFAULT 0 NOT NULL REFERENCES axis_kinds(id),
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	18 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	19 path VARCHAR(256),
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	20 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	21 );
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	22 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	23 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	24 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	25 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	26 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	27 /
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	28 --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	29
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	30
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	31 -- Geodaesie/Flussachse+km/km.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	32 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	33 CREATE TABLE river_axes_km(
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	34 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	35 GEOM MDSYS.SDO_GEOMETRY,
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	36 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5748 diff changeset	37 km NUMBER(7,3) NOT NULL,
5886 6a63456b99d9 Schema change, importer, i18n: additional field for federal state stations Tom Gottfried <tom.gottfried@intevation.de> parents: 5854 diff changeset	38 fedstate_km NUMBER(7,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	39 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	40 path VARCHAR(256),
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	41 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	42 );
5358 59f9760e8b7f Switch to 2D for fixpoints and river_axes Andre Heinecke <aheinecke@intevation.de> parents: 5354 diff changeset	43 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)), 31467);
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	44 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	45 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	46 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	47 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	48 /
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	49 --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	50
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	51
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	52 --Geodaesie/Querprofile/QP-Spuren/qps.shp
5214 b46df0609276 add lookup tables for floodplain and cross section tracks Tom Gottfried <tom@intevation.de> parents: 5210 diff changeset	53 CREATE TABLE cross_section_track_kinds(
b46df0609276 add lookup tables for floodplain and cross section tracks Tom Gottfried <tom@intevation.de> parents: 5210 diff changeset	54 id NUMBER PRIMARY KEY NOT NULL,
b46df0609276 add lookup tables for floodplain and cross section tracks Tom Gottfried <tom@intevation.de> parents: 5210 diff changeset	55 name VARCHAR(64)
b46df0609276 add lookup tables for floodplain and cross section tracks Tom Gottfried <tom@intevation.de> parents: 5210 diff changeset	56 );
b46df0609276 add lookup tables for floodplain and cross section tracks Tom Gottfried <tom@intevation.de> parents: 5210 diff changeset	57 INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige');
5495 1488227b97b7 Minor scheme change. Update kind names to their UI representation. Andre Heinecke <aheinecke@intevation.de> parents: 5409 diff changeset	58 INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'aktuelle Querprofilspuren');
5214 b46df0609276 add lookup tables for floodplain and cross section tracks Tom Gottfried <tom@intevation.de> parents: 5210 diff changeset	59
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	60 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	61 CREATE TABLE cross_section_tracks (
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	62 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	63 GEOM MDSYS.SDO_GEOMETRY,
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	64 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
6632 97145ec4e33c Schema changes: fixed typos, syntax errors and missing or obsolete statements in Oracle schema Tom Gottfried <tom@intevation.de> parents: 5886 diff changeset	65 kind_id NUMBER(38) DEFAULT 0 NOT NULL REFERENCES cross_section_track_kinds(id),
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	66 km NUMBER(38,12) NOT NULL,
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	67 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	68 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	69 path VARCHAR(256),
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	70 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	71 );
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	72 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	73 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	74 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	75 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	76 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	77 /
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	78 --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	79
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	80
5385 2da74705c29d Scheme change: Building table is extended and got a lookup table for kind Andre Heinecke <aheinecke@intevation.de> parents: 5358 diff changeset	81 CREATE TABLE building_kinds(
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind Andre Heinecke <aheinecke@intevation.de> parents: 5358 diff changeset	82 id NUMBER PRIMARY KEY NOT NULL,
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind Andre Heinecke <aheinecke@intevation.de> parents: 5358 diff changeset	83 name VARCHAR(64)
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind Andre Heinecke <aheinecke@intevation.de> parents: 5358 diff changeset	84 );
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind Andre Heinecke <aheinecke@intevation.de> parents: 5358 diff changeset	85 INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige');
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind Andre Heinecke <aheinecke@intevation.de> parents: 5358 diff changeset	86 INSERT INTO building_kinds(id, name) VALUES (1, 'Brücken');
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind Andre Heinecke <aheinecke@intevation.de> parents: 5358 diff changeset	87 INSERT INTO building_kinds(id, name) VALUES (2, 'Wehre');
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind Andre Heinecke <aheinecke@intevation.de> parents: 5358 diff changeset	88 INSERT INTO building_kinds(id, name) VALUES (3, 'Pegel');
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind Andre Heinecke <aheinecke@intevation.de> parents: 5358 diff changeset	89
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind Andre Heinecke <aheinecke@intevation.de> parents: 5358 diff changeset	90 -- Geodaesie/Bauwerke
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	91 CREATE SEQUENCE BUILDINGS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	92 CREATE TABLE buildings(
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	93 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	94 GEOM MDSYS.SDO_GEOMETRY,
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	95 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
5408 91f5908c472a Add building_kinds to drop script and fix syntax of buildings table Andre Heinecke <aheinecke@intevation.de> parents: 5391 diff changeset	96 kind_id NUMBER(38) DEFAULT 0 NOT NULL REFERENCES building_kinds(id),
91f5908c472a Add building_kinds to drop script and fix syntax of buildings table Andre Heinecke <aheinecke@intevation.de> parents: 5391 diff changeset	97 km NUMBER(38,11),
5385 2da74705c29d Scheme change: Building table is extended and got a lookup table for kind Andre Heinecke <aheinecke@intevation.de> parents: 5358 diff changeset	98 name VARCHAR2(255), -- The layername
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind Andre Heinecke <aheinecke@intevation.de> parents: 5358 diff changeset	99 description VARCHAR(256), -- Name taken from attributes
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	100 path VARCHAR(256),
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	101 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	102 );
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	103 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	104 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	105 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	106 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	107 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	108 /
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	109 --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	110
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	111
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	112 -- Geodaesie/Festpunkte/Festpunkte.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	113 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	114 CREATE TABLE fixpoints (
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	115 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	116 GEOM MDSYS.SDO_GEOMETRY,
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	117 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	118 x NUMBER(38,11),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	119 y NUMBER(38,11),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	120 km NUMBER(38,11) NOT NULL,
5623 0aaed46a88be Schema change: Allow longer strings in fixpoints.hpgp Tom Gottfried <tom@intevation.de> parents: 5577 diff changeset	121 HPGP VARCHAR(64),
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	122 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	123 path VARCHAR(256),
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	124 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	125 );
5358 59f9760e8b7f Switch to 2D for fixpoints and river_axes Andre Heinecke <aheinecke@intevation.de> parents: 5354 diff changeset	126 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)), 31467);
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	127 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	128 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	129 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	130 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	131 /
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	132 --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	133
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	134
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	135 -- Hydrologie/Hydr. Grenzen/talaue.shp
5214 b46df0609276 add lookup tables for floodplain and cross section tracks Tom Gottfried <tom@intevation.de> parents: 5210 diff changeset	136 CREATE TABLE floodplain_kinds(
b46df0609276 add lookup tables for floodplain and cross section tracks Tom Gottfried <tom@intevation.de> parents: 5210 diff changeset	137 id NUMBER PRIMARY KEY NOT NULL,
b46df0609276 add lookup tables for floodplain and cross section tracks Tom Gottfried <tom@intevation.de> parents: 5210 diff changeset	138 name VARCHAR(64)
b46df0609276 add lookup tables for floodplain and cross section tracks Tom Gottfried <tom@intevation.de> parents: 5210 diff changeset	139 );
b46df0609276 add lookup tables for floodplain and cross section tracks Tom Gottfried <tom@intevation.de> parents: 5210 diff changeset	140 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige');
5495 1488227b97b7 Minor scheme change. Update kind names to their UI representation. Andre Heinecke <aheinecke@intevation.de> parents: 5409 diff changeset	141 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'aktuelle Talaue');
5214 b46df0609276 add lookup tables for floodplain and cross section tracks Tom Gottfried <tom@intevation.de> parents: 5210 diff changeset	142
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	143 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	144 CREATE TABLE floodplain(
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	145 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	146 GEOM MDSYS.SDO_GEOMETRY,
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	147 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
6632 97145ec4e33c Schema changes: fixed typos, syntax errors and missing or obsolete statements in Oracle schema Tom Gottfried <tom@intevation.de> parents: 5886 diff changeset	148 kind_id NUMBER(38) DEFAULT 0 NOT NULL REFERENCES floodplain_kinds(id),
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	149 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	150 path VARCHAR(256),
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	151 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	152 );
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	153 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	154 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	155 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	156 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	157 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	158 /
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	159 --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	160
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	161
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	162 -- Geodaesie/Hoehenmodelle/*
2340 c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	163 CREATE SEQUENCE DEM_ID_SEQ;
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	164 CREATE TABLE dem (
5210 908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges Andre Heinecke <aheinecke@intevation.de> parents: 5205 diff changeset	165 ID NUMBER PRIMARY KEY NOT NULL,
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges Andre Heinecke <aheinecke@intevation.de> parents: 5205 diff changeset	166 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges Andre Heinecke <aheinecke@intevation.de> parents: 5205 diff changeset	167 name VARCHAR(64),
7001 dcc7a6c8b92c Minor schema change: allow to delete ranges with associated DEMs. Tom Gottfried <tom.gottfried@intevation.de> parents: 6632 diff changeset	168 range_id NUMBER(38) REFERENCES ranges(id) ON DELETE CASCADE,
5210 908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges Andre Heinecke <aheinecke@intevation.de> parents: 5205 diff changeset	169 time_interval_id NUMBER(38) REFERENCES time_intervals(id),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges Andre Heinecke <aheinecke@intevation.de> parents: 5205 diff changeset	170 projection VARCHAR(32),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges Andre Heinecke <aheinecke@intevation.de> parents: 5205 diff changeset	171 elevation_state VARCHAR(32),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges Andre Heinecke <aheinecke@intevation.de> parents: 5205 diff changeset	172 srid NUMBER NOT NULL,
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges Andre Heinecke <aheinecke@intevation.de> parents: 5205 diff changeset	173 format VARCHAR(32),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges Andre Heinecke <aheinecke@intevation.de> parents: 5205 diff changeset	174 border_break NUMBER(1) DEFAULT 0 NOT NULL,
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges Andre Heinecke <aheinecke@intevation.de> parents: 5205 diff changeset	175 resolution VARCHAR(16),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges Andre Heinecke <aheinecke@intevation.de> parents: 5205 diff changeset	176 description VARCHAR(256),
5315 aa407dfb9949 removed unique constraint to allow one dem to be used by several rivers Tom Gottfried <tom@intevation.de> parents: 5291 diff changeset	177 path VARCHAR(256) NOT NULL
2340 c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	178 );
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	179 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	180 BEGIN
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	181 SELECT DEM_ID_SEQ.nextval INTO :new.id FROM dual;
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	182 END;
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	183 /
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	184
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	185 --Static lookup tables for Hochwasserschutzanlagen
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	186 CREATE TABLE hws_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	187 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	188 kind VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	189 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	190 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	191 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	192 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	193
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	194 CREATE TABLE fed_states (
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	195 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	196 name VARCHAR(23) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	197 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	198 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	199 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	200 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	201 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	202 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	203 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	204 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	205 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	206 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	207 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	208 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	209 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	210 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	211 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	212 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	213 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	214
1250 660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	215 --Hydrologie/HW-Schutzanlagen/hws.shp
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	216 -- HWS-Lines
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	217 CREATE SEQUENCE HWS_LINES_ID_SEQ;
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	218 CREATE TABLE hws_lines (
5122 5ba502e78e05 Fix import of hws_* on Oracle Andre Heinecke <aheinecke@intevation.de> parents: 5121 diff changeset	219 OGR_FID NUMBER(38),
1250 660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	220 GEOM MDSYS.SDO_GEOMETRY,
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	221 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	222 fed_state_id NUMBER(2) REFERENCES fed_states(id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5535 diff changeset	223 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	224 name VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	225 path VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	226 official NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	227 agency VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	228 range VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	229 shore_side NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	230 source VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	231 status_date TIMESTAMP,
5122 5ba502e78e05 Fix import of hws_* on Oracle Andre Heinecke <aheinecke@intevation.de> parents: 5121 diff changeset	232 description VARCHAR(256),
5ba502e78e05 Fix import of hws_* on Oracle Andre Heinecke <aheinecke@intevation.de> parents: 5121 diff changeset	233 id NUMBER PRIMARY KEY NOT NULL
1250 660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	234 );
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	235 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	236 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	237 BEGIN
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	238 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	239 END;
5038 57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql Andre Heinecke <aheinecke@intevation.de> parents: 5028 diff changeset	240 /
5748 b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds Tom Gottfried <tom.gottfried@intevation.de> parents: 5623 diff changeset	241
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	242 CREATE TABLE sectie_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	243 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	244 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	245 );
5748 b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds Tom Gottfried <tom.gottfried@intevation.de> parents: 5623 diff changeset	246 INSERT INTO sectie_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds Tom Gottfried <tom.gottfried@intevation.de> parents: 5623 diff changeset	247 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Hauptgerinne');
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds Tom Gottfried <tom.gottfried@intevation.de> parents: 5623 diff changeset	248 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbereich');
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds Tom Gottfried <tom.gottfried@intevation.de> parents: 5623 diff changeset	249 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Vorland');
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	250
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	251 CREATE TABLE sobek_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	252 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	253 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	254 );
5748 b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds Tom Gottfried <tom.gottfried@intevation.de> parents: 5623 diff changeset	255 INSERT INTO sobek_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds Tom Gottfried <tom.gottfried@intevation.de> parents: 5623 diff changeset	256 INSERT INTO sobek_kinds (id, name) VALUES (1, 'durchströmt');
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds Tom Gottfried <tom.gottfried@intevation.de> parents: 5623 diff changeset	257 INSERT INTO sobek_kinds (id, name) VALUES (2, 'nicht durchströmt');
5025 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 CREATE TABLE boundary_kinds (
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	260 id NUMBER PRIMARY KEY NOT NULL,
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	261 name VARCHAR(64) NOT NULL
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	262 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	263 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	264 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	265 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	266 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	267
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	268 -- HWS Points
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	269 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	270 CREATE TABLE hws_points (
5122 5ba502e78e05 Fix import of hws_* on Oracle Andre Heinecke <aheinecke@intevation.de> parents: 5121 diff changeset	271 OGR_FID NUMBER(38),
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	272 GEOM MDSYS.SDO_GEOMETRY,
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	273 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	274 fed_state_id NUMBER REFERENCES fed_states(id),
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	275 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	276 name VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	277 path VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	278 official NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	279 agency VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	280 range VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	281 shore_side NUMBER DEFAULT 0,
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	282 source VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	283 status_date VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	284 description VARCHAR(256),
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	285 freeboard NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	286 dike_km NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	287 z NUMBER(19,5),
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	288 z_target NUMBER(19,5),
5122 5ba502e78e05 Fix import of hws_* on Oracle Andre Heinecke <aheinecke@intevation.de> parents: 5121 diff changeset	289 rated_level NUMBER(19,5),
5ba502e78e05 Fix import of hws_* on Oracle Andre Heinecke <aheinecke@intevation.de> parents: 5121 diff changeset	290 id NUMBER PRIMARY KEY NOT NULL
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	291 );
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	292
9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	293 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	294
5038 57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql Andre Heinecke <aheinecke@intevation.de> parents: 5028 diff changeset	295 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	296 BEGIN
5038 57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql Andre Heinecke <aheinecke@intevation.de> parents: 5028 diff changeset	297 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	298 END;
5038 57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql Andre Heinecke <aheinecke@intevation.de> parents: 5028 diff changeset	299 /
2796 f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	300
f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	301 --Hydrologie/UeSG
5276 170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names Tom Gottfried <tom@intevation.de> parents: 5260 diff changeset	302 CREATE TABLE floodmap_kinds (
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names Tom Gottfried <tom@intevation.de> parents: 5260 diff changeset	303 id NUMBER PRIMARY KEY NOT NULL,
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names Tom Gottfried <tom@intevation.de> parents: 5260 diff changeset	304 name varchar(64) NOT NULL
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names Tom Gottfried <tom@intevation.de> parents: 5260 diff changeset	305 );
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names Tom Gottfried <tom@intevation.de> parents: 5260 diff changeset	306 INSERT INTO floodmap_kinds VALUES (200, 'Messung');
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names Tom Gottfried <tom@intevation.de> parents: 5260 diff changeset	307 INSERT INTO floodmap_kinds VALUES (111, 'Berechnung-Aktuell-BfG');
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names Tom Gottfried <tom@intevation.de> parents: 5260 diff changeset	308 INSERT INTO floodmap_kinds VALUES (112, 'Berechnung-Aktuell-Bundesländer');
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names Tom Gottfried <tom@intevation.de> parents: 5260 diff changeset	309 INSERT INTO floodmap_kinds VALUES (121, 'Berechnung-Potenziell-BfG');
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names Tom Gottfried <tom@intevation.de> parents: 5260 diff changeset	310 INSERT INTO floodmap_kinds VALUES (122, 'Berechnung-Potenziell-Bundesländer');
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names Tom Gottfried <tom@intevation.de> parents: 5260 diff changeset	311
2796 f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	312 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	313 CREATE TABLE floodmaps (
2800 efb10ed2af96 Modified the oracle db schema (relation 'floodmaps'). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	314 OGR_FID NUMBER(38),
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps'). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	315 GEOM MDSYS.SDO_GEOMETRY,
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	316 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5748 diff changeset	317 name VARCHAR(255) NOT NULL,
5276 170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names Tom Gottfried <tom@intevation.de> parents: 5260 diff changeset	318 kind NUMBER NOT NULL REFERENCES floodmap_kinds(id),
2796 f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	319 diff NUMBER(19,5),
f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	320 count NUMBER(38),
f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	321 area NUMBER(19,5),
2800 efb10ed2af96 Modified the oracle db schema (relation 'floodmaps'). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	322 perimeter NUMBER(19,5),
5854 249b8766a178 Schema change: new column 'waterbody' for floodmaps and importer Tom Gottfried <tom.gottfried@intevation.de> parents: 5838 diff changeset	323 waterbody 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	324 path VARCHAR(256),
5276 170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names Tom Gottfried <tom@intevation.de> parents: 5260 diff changeset	325 source varchar(64),
2800 efb10ed2af96 Modified the oracle db schema (relation 'floodmaps'). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	326 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	327 );
2800 efb10ed2af96 Modified the oracle db schema (relation 'floodmaps'). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	328 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	329 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	330 BEGIN
f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	331 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	332 END;
5038 57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql Andre Heinecke <aheinecke@intevation.de> parents: 5028 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
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 --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	336 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	337 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	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),
5535 16afd4613f9d Fix syntax errors in oracle scheme Andre Heinecke <aheinecke@intevation.de> parents: 5514 diff changeset	342 kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id),
5748 b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds Tom Gottfried <tom.gottfried@intevation.de> parents: 5623 diff changeset	343 sectie NUMBER(38) REFERENCES sectie_kinds(id),
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds Tom Gottfried <tom.gottfried@intevation.de> parents: 5623 diff changeset	344 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	345 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	346 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	347 );
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 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	349 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	350 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	351 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	352 END;
5038 57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql Andre Heinecke <aheinecke@intevation.de> parents: 5028 diff changeset	353 /
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	354
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	355 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	356 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	357 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	358 GEOM MDSYS.SDO_GEOMETRY,
5025 9ef49f3b0304 Sync Posgres and Oracle spatial schema Andre Heinecke <aheinecke@intevation.de> parents: 5013 diff changeset	359 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	360 name VARCHAR(255),
5535 16afd4613f9d Fix syntax errors in oracle scheme Andre Heinecke <aheinecke@intevation.de> parents: 5514 diff changeset	361 kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id),
5748 b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds Tom Gottfried <tom.gottfried@intevation.de> parents: 5623 diff changeset	362 sectie NUMBER(38) REFERENCES sectie_kinds(id),
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds Tom Gottfried <tom.gottfried@intevation.de> parents: 5623 diff changeset	363 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	364 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	365 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	366 );
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	367 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	368 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	369 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	370 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	371 END;
5038 57a845da72bd Fix some oracle syntax problems and move indicies to _idx.sql Andre Heinecke <aheinecke@intevation.de> parents: 5028 diff changeset	372 /
2862 998f1a7dcfde New shape importers and schema modifications. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2861 diff changeset	373
998f1a7dcfde New shape importers and schema modifications. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2861 diff changeset	374 -- Hydrologie/Streckendaten/
5291 66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	375 CREATE TABLE jetty_kinds(
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	376 id NUMBER PRIMARY KEY NOT NULL,
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	377 name VARCHAR(64)
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	378 );
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	379 INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf');
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	380 INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß');
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	381 INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel');
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	382
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	383 CREATE SEQUENCE JETTIES_ID_SEQ;
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	384 CREATE TABLE jetties (
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	385 OGR_FID NUMBER(38),
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	386 GEOM MDSYS.SDO_GEOMETRY,
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	387 id NUMBER PRIMARY KEY NOT NULL,
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	388 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	389 path VARCHAR(256),
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	390 kind_id NUMBER(38) REFERENCES jetty_kinds(id),
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	391 km NUMBER(7,3),
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	392 z NUMBER(38,12)
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	393 );
5354 555a99dff849 Jetties are two dimensional Andre Heinecke <aheinecke@intevation.de> parents: 5315 diff changeset	394 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('jetties', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001)), 31467);
5291 66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	395 CREATE OR REPLACE TRIGGER jetties_trigger BEFORE INSERT ON jetties FOR EACH ROW
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	396 BEGIN
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	397 SELECT JETTIES_ID_SEQ.nextval INTO :new.id FROM dual;
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	398 END;
66d9389c80cb added tables for jetties in spatial schema Tom Gottfried <tom@intevation.de> parents: 5276 diff changeset	399 /
5409 8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	400
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	401 CREATE SEQUENCE FLOOD_MARKS_ID_SEQ;
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	402 CREATE TABLE flood_marks (
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	403 OGR_FID NUMBER(38),
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	404 GEOM MDSYS.SDO_GEOMETRY,
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	405 id NUMBER PRIMARY KEY NOT NULL,
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	406 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	407 path VARCHAR(256),
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	408 km NUMBER(7,3),
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	409 z NUMBER(38,12),
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	410 location VARCHAR(64),
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	411 year NUMBER(38,0)
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	412 );
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	413 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('flood_marks', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001)), 31467);
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	414 CREATE OR REPLACE TRIGGER flood_marks_trigger BEFORE INSERT ON flood_marks FOR EACH ROW
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	415 BEGIN
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	416 SELECT FLOOD_MARKS_ID_SEQ.nextval INTO :new.id FROM dual;
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	417 END;
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties Tom Gottfried <tom@intevation.de> parents: 5408 diff changeset	418 /

Mercurial > dive4elements > river

annotate backend/doc/schema/oracle-spatial.sql @ 8644:c15cebcf60da