Mercurial > dive4elements > river
changeset 5291:66d9389c80cb
added tables for jetties in spatial schema
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Thu, 14 Mar 2013 12:16:58 +0100 |
| parents | 05eaecd2be42 |
| children | 3dc4c2798212 |
| files | flys-backend/doc/schema/oracle-spatial.sql flys-backend/doc/schema/oracle-spatial_idx.sql flys-backend/doc/schema/postgresql-spatial.sql |
| diffstat | 3 files changed, 56 insertions(+), 2 deletions(-) [+] |
line wrap: on
line diff
--- a/flys-backend/doc/schema/oracle-spatial.sql Thu Mar 14 11:56:13 2013 +0100 +++ b/flys-backend/doc/schema/oracle-spatial.sql Thu Mar 14 12:16:58 2013 +0100 @@ -373,3 +373,30 @@ SELECT GAUGE_LOCATION_ID_SEQ.nextval INTO :new.id FROM dual; END; / + + +CREATE TABLE jetty_kinds( + id NUMBER PRIMARY KEY NOT NULL, + name VARCHAR(64) +); +INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf'); +INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß'); +INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel'); + +CREATE SEQUENCE JETTIES_ID_SEQ; +CREATE TABLE jetties ( + 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), + kind_id NUMBER(38) REFERENCES jetty_kinds(id), + km NUMBER(7,3), + z NUMBER(38,12) +); +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),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); +CREATE OR REPLACE TRIGGER jetties_trigger BEFORE INSERT ON jetties FOR EACH ROW + BEGIN + SELECT JETTIES_ID_SEQ.nextval INTO :new.id FROM dual; + END; +/
--- a/flys-backend/doc/schema/oracle-spatial_idx.sql Thu Mar 14 11:56:13 2013 +0100 +++ b/flys-backend/doc/schema/oracle-spatial_idx.sql Thu Mar 14 12:16:58 2013 +0100 @@ -12,7 +12,7 @@ -- TODO: index prevents importing on 11g. -- Error: "Ebenendimensionalitat stimmt nicht mit Geometrie-Dimensionen uberein" --- CREATE INDEX river_axes_spatial_idx ON river_axes(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); +-- CREATE INDEX river_axes_spatial_idx ON river_axes(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTILINE'); -- TODO: index prevents importing on 11g. -- Error: "Ebenendimensionalitat stimmt nicht mit Geometrie-Dimensionen uberein" @@ -26,7 +26,11 @@ CREATE INDEX hws_points_spatial_idx ON hws_points(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT'); CREATE INDEX hws_lines_spatial_idx ON hws_lines(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTILINE'); + CREATE INDEX floodmaps_spatial_idx ON floodmaps(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON'); + CREATE INDEX gauge_location_idx ON gauge_location(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT'); CREATE INDEX hydr_boundaries_poly_idx ON hydr_boundaries_poly(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON'); +CREATE INDEX jetties_idx ON jetties(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT'); +
--- a/flys-backend/doc/schema/postgresql-spatial.sql Thu Mar 14 11:56:13 2013 +0100 +++ b/flys-backend/doc/schema/postgresql-spatial.sql Thu Mar 14 12:16:58 2013 +0100 @@ -112,8 +112,8 @@ river_id int REFERENCES rivers(id) ON DELETE CASCADE, -- XXX Should we use the ranges table instead? name VARCHAR(64), - range_id INT REFERENCES ranges(id); - time_interval_id INT REFERENCES time_intervals(id); + range_id INT REFERENCES ranges(id), + time_interval_id INT REFERENCES time_intervals(id), projection VARCHAR(32), srid int NOT NULL, elevation_state VARCHAR(32), @@ -300,4 +300,26 @@ SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2); ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ'); + +CREATE TABLE jetty_kinds( + id int PRIMARY KEY NOT NULL, + name VARCHAR(64) +); +INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf'); +INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß'); +INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel'); + +CREATE SEQUENCE JETTIES_ID_SEQ; +CREATE TABLE jetties ( + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id) ON DELETE CASCADE, + path VARCHAR(256), + kind_id int REFERENCES jetty_kinds(id), + km FLOAT8, + z FLOAT8 +); +SELECT AddGeometryColumn('jetties','geom',31467,'POINT',2); +ALTER TABLE jetties ALTER COLUMN id SET DEFAULT NEXTVAL('JETTIES_ID_SEQ'); + + COMMIT;