Mercurial > dive4elements > river
diff flys-backend/doc/schema/oracle-spatial.sql @ 5540:25c2505df28f
Merged
author | Christian Lins <christian.lins@intevation.de> |
---|---|
date | Wed, 03 Apr 2013 16:00:21 +0200 |
parents | 16afd4613f9d |
children | 5f91881124ba |
line wrap: on
line diff
--- a/flys-backend/doc/schema/oracle-spatial.sql Wed Apr 03 15:59:01 2013 +0200 +++ b/flys-backend/doc/schema/oracle-spatial.sql Wed Apr 03 16:00:21 2013 +0200 @@ -5,7 +5,7 @@ name VARCHAR(64) ); INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt'); -INSERT INTO axis_kinds(id, name) VALUES (1, 'Aktuell'); +INSERT INTO axis_kinds(id, name) VALUES (1, 'aktuelle Achse'); INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige'); -- Geodaesie/Flussachse+km/achse @@ -54,7 +54,7 @@ name VARCHAR(64) ); INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige'); -INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'Aktuell'); +INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'aktuelle Querprofilspuren'); CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; CREATE TABLE cross_section_tracks ( @@ -92,8 +92,8 @@ OGR_FID NUMBER(38), GEOM MDSYS.SDO_GEOMETRY, river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, - kind_id NUMBER(38) REFERENCES building_kinds(id) NOT NULL DEFAULT 0, - km NUMBER(38,11) NOT NULL, + kind_id NUMBER(38) DEFAULT 0 NOT NULL REFERENCES building_kinds(id), + km NUMBER(38,11), name VARCHAR2(255), -- The layername description VARCHAR(256), -- Name taken from attributes path VARCHAR(256), @@ -137,7 +137,7 @@ name VARCHAR(64) ); INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige'); -INSERT INTO floodplain_kinds(id, name) VALUES (1, 'Aktuell'); +INSERT INTO floodplain_kinds(id, name) VALUES (1, 'aktuelle Talaue'); CREATE SEQUENCE FLOODPLAIN_ID_SEQ; CREATE TABLE floodplain( @@ -242,7 +242,7 @@ id NUMBER PRIMARY KEY NOT NULL, name VARCHAR(64) NOT NULL ); -INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt'); +INSERT INTO sectie_kinds (id, name) VALUES (0, 'SECTIE Unbekannt'); INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch'); INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank'); INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich'); @@ -251,7 +251,7 @@ id NUMBER PRIMARY KEY NOT NULL, name VARCHAR(64) NOT NULL ); -INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt'); +INSERT INTO sobek_kinds (id, name) VALUES (0, 'SOBEK Unbekannt'); INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend'); INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd'); @@ -337,9 +337,9 @@ GEOM MDSYS.SDO_GEOMETRY, river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(255), - kind NUMBER(38) REFERENCES boundary_kinds(id), - sectie NUMBER(38) REFERENCES sectie_kinds(id), - sobek NUMBER(38) REFERENCES sobek_kinds(id), + kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id), + sectie NUMBER(38) DEFAULT 0 NOT NULL REFERENCES sectie_kinds(id), + sobek NUMBER(38) DEFAULT 0 NOT NULL REFERENCES sobek_kinds(id), path VARCHAR(256), id NUMBER PRIMARY KEY NOT NULL ); @@ -356,9 +356,9 @@ GEOM MDSYS.SDO_GEOMETRY, river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(255), - kind NUMBER(38) REFERENCES boundary_kinds(id), - sectie NUMBER(38) REFERENCES sectie_kinds(id), - sobek NUMBER(38) REFERENCES sobek_kinds(id), + kind NUMBER(38) DEFAULT 0 NOT NULL REFERENCES boundary_kinds(id), + sectie NUMBER(38) DEFAULT 0 NOT NULL REFERENCES sectie_kinds(id), + sobek NUMBER(38) DEFAULT 0 NOT NULL REFERENCES sobek_kinds(id), path VARCHAR(256), id NUMBER PRIMARY KEY NOT NULL ); @@ -412,3 +412,22 @@ SELECT JETTIES_ID_SEQ.nextval INTO :new.id FROM dual; END; / + +CREATE SEQUENCE FLOOD_MARKS_ID_SEQ; +CREATE TABLE flood_marks ( + OGR_FID NUMBER(38), + GEOM MDSYS.SDO_GEOMETRY, + id NUMBER PRIMARY KEY NOT NULL, + river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, + path VARCHAR(256), + km NUMBER(7,3), + z NUMBER(38,12), + location VARCHAR(64), + year NUMBER(38,0) +); +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); +CREATE OR REPLACE TRIGGER flood_marks_trigger BEFORE INSERT ON flood_marks FOR EACH ROW + BEGIN + SELECT FLOOD_MARKS_ID_SEQ.nextval INTO :new.id FROM dual; + END; +/