Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial-migrate-dami.sql @ 5122:5ba502e78e05 dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix import of hws_* on Oracle The OCI driver needs a field named OGR_FID as the first element of a table, otherwise it gets confused and tries to add the fid twice in the insert statement leading to the "too many values" error.
author Andre Heinecke <aheinecke@intevation.de>
date Thu, 28 Feb 2013 10:09:31 +0100
parents 0fd7720e5c7f
children
comparison
equal deleted inserted replaced
5121:e4cc9aebfcf1 5122:5ba502e78e05
33 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg'); 33 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
34 34
35 -- HWS-Lines 35 -- HWS-Lines
36 CREATE SEQUENCE HWS_LINES_ID_SEQ; 36 CREATE SEQUENCE HWS_LINES_ID_SEQ;
37 CREATE TABLE hws_lines ( 37 CREATE TABLE hws_lines (
38 id NUMBER PRIMARY KEY NOT NULL, 38 OGR_FID NUMBER(38),
39 GEOM MDSYS.SDO_GEOMETRY, 39 GEOM MDSYS.SDO_GEOMETRY,
40 ogr_fid NUMBER, 40 kind_id NUMBER(2) DEFAULT 2 REFERENCES hws_kinds(id),
41 kind_id NUMBER REFERENCES hws_kinds(id) DEFAULT 2, 41 fed_state_id NUMBER(2) REFERENCES fed_states(id),
42 fed_state_id NUMBER REFERENCES fed_states(id), 42 river_id NUMBER(38) REFERENCES rivers(id),
43 river_id NUMBER REFERENCES rivers(id),
44 name VARCHAR(256), 43 name VARCHAR(256),
45 path VARCHAR(256), 44 path VARCHAR(256),
46 official NUMBER DEFAULT 0, 45 official NUMBER DEFAULT 0,
47 agency VARCHAR(256), 46 agency VARCHAR(256),
48 range VARCHAR(256), 47 range VARCHAR(256),
49 shore_side NUMBER DEFAULT 0, 48 shore_side NUMBER DEFAULT 0,
50 source VARCHAR(256), 49 source VARCHAR(256),
51 status_date TIMESTAMP, 50 status_date TIMESTAMP,
52 description VARCHAR(256) 51 description VARCHAR(256),
52 id NUMBER PRIMARY KEY NOT NULL
53 ); 53 );
54 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); 54 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);
55 CREATE INDEX hws_lines_spatial_idx ON hws_lines(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); 55 CREATE INDEX hws_lines_spatial_idx ON hws_lines(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
56 56
57 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW 57 CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW
87 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige'); 87 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
88 88
89 -- HWS Points 89 -- HWS Points
90 CREATE SEQUENCE HWS_POINTS_ID_SEQ; 90 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
91 CREATE TABLE hws_points ( 91 CREATE TABLE hws_points (
92 id NUMBER PRIMARY KEY NOT NULL, 92 OGR_FID NUMBER(38),
93 GEOM MDSYS.SDO_GEOMETRY, 93 GEOM MDSYS.SDO_GEOMETRY,
94 ogr_fid NUMBER(2), 94 ogr_fid NUMBER,
95 kind_id NUMBER(2) DEFAULT 2 REFERENCES hws_kinds(id), 95 kind_id NUMBER DEFAULT 2 REFERENCES hws_kinds(id),
96 fed_state_id NUMBER(2) REFERENCES fed_states(id), 96 fed_state_id NUMBER REFERENCES fed_states(id),
97 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, 97 river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
98 name VARCHAR(256), 98 name VARCHAR(256),
99 path VARCHAR(256), 99 path VARCHAR(256),
100 official NUMBER DEFAULT 0, 100 official NUMBER DEFAULT 0,
101 agency VARCHAR(256), 101 agency VARCHAR(256),
106 description VARCHAR(256), 106 description VARCHAR(256),
107 freeboard NUMBER(19,5), 107 freeboard NUMBER(19,5),
108 dike_km NUMBER(19,5), 108 dike_km NUMBER(19,5),
109 z NUMBER(19,5), 109 z NUMBER(19,5),
110 z_target NUMBER(19,5), 110 z_target NUMBER(19,5),
111 rated_level NUMBER(19,5) 111 rated_level NUMBER(19,5),
112 id NUMBER PRIMARY KEY NOT NULL
112 ); 113 );
113 114
114 -- Altrications 115 -- Altrications
115 ALTER TABLE dem ADD srid NUMBER NOT NULL; 116 ALTER TABLE dem ADD srid NUMBER NOT NULL;
116 ALTER TABLE hydr_boundaries_poly ADD sectie NUMBER REFERENCES sectie_kinds(id); 117 ALTER TABLE hydr_boundaries_poly ADD sectie NUMBER REFERENCES sectie_kinds(id);
http://dive4elements.wald.intevation.org