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

annotate flys-backend/doc/schema/oracle-spatial.sql @ 4221:480de0dbca8e

Extended location input helper. The locationpicker has now an attribute whether the input is distance or location to display one or two clickable columns. Replaced the record click handler with cell click handler.

author	Raimund Renkert <rrenkert@intevation.de>
date	Tue, 23 Oct 2012 13:17:20 +0200
parents	f5912365619c
children	eecd1ebba128

rev	line source
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	1 -- Geodaesie/Flussachse+km/achse
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	2 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	3 CREATE TABLE river_axes(
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	4 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	5 GEOM MDSYS.SDO_GEOMETRY,
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	6 river_id NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	7 kind NUMBER(38) DEFAULT 0 NOT NULL,
2861 b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	8 name VARCHAR(64),
3677 c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2862 diff changeset	9 path VARCHAR(256),
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	10 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	11 );
2370 7d378970d764 doc/schema/oracle-spatial.sql: Adjust extent of germany to EPSG:31467 (GK3) coordinates. Bjoern Schilberg <bjoern@intevation.de> parents: 2369 diff changeset	12 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	13 CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	14 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	15 SELECT RIVER_AXES_ID_SEQ.nextval INTO :new.id FROM dual;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	16 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	17 /
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	18 --CREATE INDEX river_axes_spatial_idx ON river_axes(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	19
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	20
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	21 -- Geodaesie/Flussachse+km/km.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	22 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	23 CREATE TABLE river_axes_km(
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	24 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	25 GEOM MDSYS.SDO_GEOMETRY,
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	26 river_id NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	27 km NUMBER(6,3),
2861 b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	28 name VARCHAR(64),
3677 c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2862 diff changeset	29 path VARCHAR(256),
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	30 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	31 );
2370 7d378970d764 doc/schema/oracle-spatial.sql: Adjust extent of germany to EPSG:31467 (GK3) coordinates. Bjoern Schilberg <bjoern@intevation.de> parents: 2369 diff changeset	32 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes_km', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	33 CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	34 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	35 SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	36 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	37 /
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	38 --CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point');
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	39
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	40
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	41 --Geodaesie/Querprofile/QP-Spuren/qps.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	42 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	43 CREATE TABLE cross_section_tracks (
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	44 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	45 GEOM MDSYS.SDO_GEOMETRY,
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	46 river_id NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	47 km NUMBER(38,12) NOT NULL,
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	48 z NUMBER(38,12) DEFAULT 0 NOT NULL,
2861 b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	49 name VARCHAR(64),
3677 c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2862 diff changeset	50 path VARCHAR(256),
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	51 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	52 );
2370 7d378970d764 doc/schema/oracle-spatial.sql: Adjust extent of germany to EPSG:31467 (GK3) coordinates. Bjoern Schilberg <bjoern@intevation.de> parents: 2369 diff changeset	53 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('cross_section_tracks', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	54 CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	55 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	56 SELECT CROSS_SECTION_TRACKS_ID_SEQ.nextval INTO :new.id FROM dual;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	57 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	58 /
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	59 --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	60
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	61
2340 c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	62 -- TODO: TestMe. Fix Importer-Script. Fix oracle_spatial_idx.sql script.
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	63 -- Geodaesie/Linien/rohre-und-speeren
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	64 CREATE SEQUENCE LINES_ID_SEQ;
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	65 CREATE TABLE lines (
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	66 OGR_FID NUMBER(38),
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	67 GEOM MDSYS.SDO_GEOMETRY,
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	68 river_id NUMBER(38),
2365 60532f75396b Adapted 'lines' relation and made lines queryable for a given river. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2340 diff changeset	69 kind VARCHAR2(16) NOT NULL,
2340 c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	70 z NUMBER(38,12) DEFAULT 0,
2861 b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	71 name VARCHAR(64),
3677 c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2862 diff changeset	72 path VARCHAR(256),
2340 c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	73 ID NUMBER PRIMARY KEY NOT NULL
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	74 );
2370 7d378970d764 doc/schema/oracle-spatial.sql: Adjust extent of germany to EPSG:31467 (GK3) coordinates. Bjoern Schilberg <bjoern@intevation.de> parents: 2369 diff changeset	75 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('lines', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
2340 c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	76 CREATE OR REPLACE TRIGGER lines_trigger BEFORE INSERT ON lines FOR each ROW
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	77 BEGIN
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	78 SELECT LINES_ID_SEQ.nextval INTO :new.id FROM dual;
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	79 END;
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	80 /
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	81 -- NOTE: Should lines should be 3D.
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	82 -- TODO: Test index.
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	83 --CREATE INDEX lines_idx ON lines(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	84 -- 'kind':
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	85 -- 0: ROHR1
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	86 -- 1: DAMM
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	87
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	88
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	89 -- Geodaesie/Bauwerke/Wehre.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	90 CREATE SEQUENCE BUILDINGS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	91 CREATE TABLE buildings(
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	92 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	93 GEOM MDSYS.SDO_GEOMETRY,
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	94 river_id NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	95 name VARCHAR2(255),
3677 c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2862 diff changeset	96 path VARCHAR(256),
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	97 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	98 );
2370 7d378970d764 doc/schema/oracle-spatial.sql: Adjust extent of germany to EPSG:31467 (GK3) coordinates. Bjoern Schilberg <bjoern@intevation.de> parents: 2369 diff changeset	99 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('buildings', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	100 CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	101 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	102 SELECT BUILDINGS_ID_SEQ.nextval INTO :new.id FROM dual;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	103 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	104 /
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	105 --CREATE INDEX buildings_spatial_idx ON buildings(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	106
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	107
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	108 -- Geodaesie/Festpunkte/Festpunkte.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	109 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	110 CREATE TABLE fixpoints (
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	111 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	112 GEOM MDSYS.SDO_GEOMETRY,
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	113 river_id NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	114 x NUMBER(38,11),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	115 y NUMBER(38,11),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	116 km NUMBER(38,11) NOT NULL,
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	117 HPGP VARCHAR2(255),
2861 b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	118 name VARCHAR(64),
3677 c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2862 diff changeset	119 path VARCHAR(256),
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	120 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	121 );
2370 7d378970d764 doc/schema/oracle-spatial.sql: Adjust extent of germany to EPSG:31467 (GK3) coordinates. Bjoern Schilberg <bjoern@intevation.de> parents: 2369 diff changeset	122 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('fixpoints', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	123 CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	124 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	125 SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	126 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	127 /
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	128 --CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	129
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	130
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	131 -- Hydrologie/Hydr. Grenzen/talaue.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	132 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	133 CREATE TABLE floodplain(
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	134 OGR_FID NUMBER(38),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	135 GEOM MDSYS.SDO_GEOMETRY,
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	136 river_id NUMBER(38),
2861 b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	137 name VARCHAR(64),
3677 c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2862 diff changeset	138 path VARCHAR(256),
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	139 ID NUMBER PRIMARY KEY NOT NULL
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	140 );
2370 7d378970d764 doc/schema/oracle-spatial.sql: Adjust extent of germany to EPSG:31467 (GK3) coordinates. Bjoern Schilberg <bjoern@intevation.de> parents: 2369 diff changeset	141 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodplain', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	142 CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	143 BEGIN
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	144 SELECT FLOODPLAIN_ID_SEQ.nextval INTO :new.id FROM dual;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	145 END;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	146 /
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	147 --CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON');
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	148
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	149
2340 c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	150 -- TODO: Test-Me. Fix Importer-Skript.
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	151 -- NOTE: It's not a spatial schema!
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	152 -- Geodaesie/Hoehenmodelle/*
2340 c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	153 CREATE SEQUENCE DEM_ID_SEQ;
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	154 CREATE TABLE dem (
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	155 ID NUMBER PRIMARY KEY NOT NULL,
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	156 river_id NUMBER(38),
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	157 -- XXX Should we use the ranges table instead?
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	158 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	159 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	160 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	161 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	162 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	163 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	164 elevation_state VARCHAR(32),
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	165 format VARCHAR(32),
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	166 border_break BOOLEAN NOT NULL DEFAULT FALSE,
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	167 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	168 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	169 path VARCHAR(256)
2340 c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	170 );
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	171 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	172 BEGIN
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	173 SELECT DEM_ID_SEQ.nextval INTO :new.id FROM dual;
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	174 END;
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	175 /
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	176
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 2338 diff changeset	177
1244 281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	178 -- Hydrologie/Einzugsgebiete/EZG.shp
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	179 CREATE SEQUENCE CATCHMENT_ID_SEQ;
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	180 CREATE TABLE catchment(
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	181 OGR_FID NUMBER(38),
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	182 GEOM MDSYS.SDO_GEOMETRY,
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	183 river_id NUMBER(38),
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	184 area NUMBER(19,5),
2335 e1fb4a1e0b4a Generalized oracle spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1253 diff changeset	185 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	186 path VARCHAR(256),
1244 281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	187 ID NUMBER PRIMARY KEY NOT NULL
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	188 );
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	189 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('CATCHMENT', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
1244 281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	190
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	191 CREATE TRIGGER catchment_trigger BEFORE INSERT ON catchment FOR each ROW
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	192 BEGIN
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	193 SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual;
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	194 END;
281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	195 /
1253 a75e7d922942 Deactivated spatial indexes. Bjoern Schilberg <bjoern@intevation.de> parents: 1251 diff changeset	196 --CREATE INDEX catchment_spatial_idx ON catchment(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=polygon');
1244 281f38fc6c3e Initial oracle schema. Bjoern Schilberg <bjoern@intevation.de> parents: diff changeset	197
1250 660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	198 --Hydrologie/HW-Schutzanlagen/hws.shp
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	199 CREATE SEQUENCE HWS_ID_SEQ;
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	200 CREATE TABLE hws(
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	201 OGR_FID NUMBER(38),
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	202 GEOM MDSYS.SDO_GEOMETRY,
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	203 river_id NUMBER(38),
2335 e1fb4a1e0b4a Generalized oracle spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1253 diff changeset	204 hws_facility VARCHAR2(255),
e1fb4a1e0b4a Generalized oracle spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1253 diff changeset	205 type VARCHAR2(255),
2861 b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	206 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	207 path VARCHAR(256),
1250 660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	208 ID NUMBER PRIMARY KEY NOT NULL
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	209 );
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	210 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
1250 660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	211 CREATE OR REPLACE TRIGGER hws_trigger BEFORE INSERT ON hws FOR each ROW
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	212 BEGIN
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	213 SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual;
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	214 END;
660116539429 Added schema hws. Bjoern Schilberg <bjoern@intevation.de> parents: 1249 diff changeset	215 /
1253 a75e7d922942 Deactivated spatial indexes. Bjoern Schilberg <bjoern@intevation.de> parents: 1251 diff changeset	216 --CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
2796 f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	217
f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	218
f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	219 --Hydrologie/UeSG
f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	220 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	221 CREATE TABLE floodmaps (
2800 efb10ed2af96 Modified the oracle db schema (relation 'floodmaps'). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	222 OGR_FID NUMBER(38),
efb10ed2af96 Modified the oracle db schema (relation 'floodmaps'). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	223 GEOM MDSYS.SDO_GEOMETRY,
2796 f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	224 river_id NUMBER(38),
2800 efb10ed2af96 Modified the oracle db schema (relation 'floodmaps'). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	225 name VARCHAR(255),
2796 f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	226 kind NUMBER(38),
f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	227 diff NUMBER(19,5),
f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	228 count NUMBER(38),
f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	229 area NUMBER(19,5),
2800 efb10ed2af96 Modified the oracle db schema (relation 'floodmaps'). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	230 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	231 path VARCHAR(256),
2800 efb10ed2af96 Modified the oracle db schema (relation 'floodmaps'). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	232 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	233 );
2800 efb10ed2af96 Modified the oracle db schema (relation 'floodmaps'). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	234 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	235 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	236 BEGIN
f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2370 diff changeset	237 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	238 END;
2852 875a87b8489f Repaired broken oracle spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2803 diff changeset	239 /
2800 efb10ed2af96 Modified the oracle db schema (relation 'floodmaps'). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	240 CREATE INDEX floodmaps_spatial_idx ON floodmaps(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON');
2861 b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	241
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	242
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	243 --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	244 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	245 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	246 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	247 GEOM MDSYS.SDO_GEOMETRY,
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	248 river_id NUMBER(38),
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	249 name VARCHAR(255),
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	250 kind NUMBER(38),
3677 c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2862 diff changeset	251 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	252 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	253 );
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	254 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	255 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	256 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	257 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	258 END;
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	259 /
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	260 CREATE INDEX hydr_boundaries_idx ON hydr_boundaries(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	261
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	262 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	263 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	264 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	265 GEOM MDSYS.SDO_GEOMETRY,
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	266 river_id NUMBER(38),
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	267 name VARCHAR(255),
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	268 kind NUMBER(38),
3677 c37effda3655 Added 'path' attribute to each geo relation; adapted the shape importer to set the path for each feature. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2862 diff changeset	269 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	270 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	271 );
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	272 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	273 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	274 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	275 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	276 END;
b0132e1b9719 Added further shape importers and added the option to reproject shapes during the import process. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2852 diff changeset	277 /
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	278 CREATE INDEX hydr_boundaries_poly_idx ON hydr_boundaries_poly(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON');
2862 998f1a7dcfde New shape importers and schema modifications. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2861 diff changeset	279
998f1a7dcfde New shape importers and schema modifications. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2861 diff changeset	280
998f1a7dcfde New shape importers and schema modifications. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2861 diff changeset	281 -- Hydrologie/Streckendaten/
998f1a7dcfde New shape importers and schema modifications. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2861 diff changeset	282 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
998f1a7dcfde New shape importers and schema modifications. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2861 diff changeset	283 CREATE TABLE gauge_location (
998f1a7dcfde New shape importers and schema modifications. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2861 diff changeset	284 OGR_FID NUMBER(38),
998f1a7dcfde New shape importers and schema modifications. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2861 diff changeset	285 GEOM MDSYS.SDO_GEOMETRY,
998f1a7dcfde New shape importers and schema modifications. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2861 diff changeset	286 river_id NUMBER(38),
998f1a7dcfde New shape importers and schema modifications. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2861 diff changeset	287 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	288 path VARCHAR(256),
2862 998f1a7dcfde New shape importers and schema modifications. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2861 diff changeset	289 id NUMBER PRIMARY KEY NOT NULL
998f1a7dcfde New shape importers and schema modifications. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2861 diff changeset	290 );
998f1a7dcfde New shape importers and schema modifications. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2861 diff changeset	291 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	292 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	293 BEGIN
998f1a7dcfde New shape importers and schema modifications. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2861 diff changeset	294 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	295 END;
998f1a7dcfde New shape importers and schema modifications. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2861 diff changeset	296 /
998f1a7dcfde New shape importers and schema modifications. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2861 diff changeset	297 CREATE INDEX gauge_location_idx ON gauge_location(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');

Mercurial > dive4elements > river

annotate flys-backend/doc/schema/oracle-spatial.sql @ 4221:480de0dbca8e