Mercurial > dive4elements > river
changeset 2796:f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
flys-backend/trunk@4125 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Ingo Weinzierl <ingo.weinzierl@intevation.de> |
---|---|
date | Tue, 06 Mar 2012 11:54:41 +0000 (2012-03-06) |
parents | c41bb1293acb |
children | e41d03bf9807 |
files | flys-backend/ChangeLog flys-backend/doc/schema/oracle-spatial.sql flys-backend/doc/schema/postgresql-spatial.sql |
diffstat | 3 files changed, 81 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/flys-backend/ChangeLog Tue Mar 06 07:45:30 2012 +0000 +++ b/flys-backend/ChangeLog Tue Mar 06 11:54:41 2012 +0000 @@ -1,3 +1,9 @@ +2012-03-06 Ingo Weinzierl <ingo@intevation.de> + + * doc/schema/postgresql-spatial.sql, + doc/schema/oracle-spatial.sql: Added new relations for existing + floodmaps (currently tested for PostgreSQL only!). + 2012-03-06 Ingo Weinzierl <ingo@intevation.de> * src/main/java/de/intevation/flys/model/Wst.java: Adapted method call of
--- a/flys-backend/doc/schema/oracle-spatial.sql Tue Mar 06 07:45:30 2012 +0000 +++ b/flys-backend/doc/schema/oracle-spatial.sql Tue Mar 06 11:54:41 2012 +0000 @@ -190,3 +190,48 @@ END; / --CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); + + +--Hydrologie/UeSG +CREATE SEQUENCE FLOODMAPS_ID_SEQ; +CREATE TABLE floodmaps ( + id NUMBER PRIMARY KEY NOT NULL, + river_id NUMBER(38), + kind NUMBER(38), + diff NUMBER(19,5), + count NUMBER(38), + area NUMBER(19,5), + perimeter NUMBER(19,5) +); +CREATE OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW + BEGIN + SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual; + END; +/ + + +CREATE SEQUENCE FLOODMAP_GEOMS_SEQ; +CREATE TABLE floodmap_geoms ( + id NUMBER(38) PRIMARY KEY NOT NULL, + floodmaps_id NUMBER(38) NOT NULL, + polygons_id NUMBER(38) NOT NULL +); +CREATE OR REPLACE TRIGGER floodmap_geoms_trigger BEFORE INSERT ON floodmap_geoms FOR each ROW + BEGIN + SELECT FLOODMAP_GEOMS_SEQ.nextval INTO :new.id FROM dual; + END; +/ + + +CREATE SEQUENCE POLYGONS_SEQ; +CREATE TABLE polygons ( + OGR_FID NUMBER(38), + GEOM MDSYS.SDO_GEOMETRY, + id NUMBER(38) PRIMARY KEY NOT NULL +); +INSERT INTO USER_SDO_GEOM_METADATA VALUES ('polygons', '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 polygons_trigger BEFORE INSERT ON polygons FOR each ROW + BEGIN + SELECT POLYGONS_SEQ.nextval INTO :new.id FROM dual; + END; +/
--- a/flys-backend/doc/schema/postgresql-spatial.sql Tue Mar 06 07:45:30 2012 +0000 +++ b/flys-backend/doc/schema/postgresql-spatial.sql Tue Mar 06 11:54:41 2012 +0000 @@ -123,4 +123,34 @@ ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ'); +--Hydrologie/UeSG +CREATE SEQUENCE FLOODMAPS_ID_SEQ; +CREATE TABLE floodmaps ( + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id), + kind int NOT NULL, + diff NUMERIC, + count int, + area NUMERIC, + perimeter NUMERIC +); +ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ'); + + +CREATE SEQUENCE POLYGONS_SEQ; +CREATE TABLE polygons ( + id int PRIMARY KEY NOT NULL +); +SELECT AddGeometryColumn('polygons', 'geom', 31466, 'POLYGON', 2); +ALTER TABLE polygons ALTER COLUMN id SET DEFAULT NEXTVAL('POLYGONS_SEQ'); + + +CREATE SEQUENCE FLOODMAP_GEOMS_SEQ; +CREATE TABLE floodmap_geoms ( + id int PRIMARY KEY NOT NULL, + floodmaps_id int NOT NULL REFERENCES floodmaps(id), + polygons_id int NOT NULL REFERENCES polygons(id) +); +ALTER TABLE floodmap_geoms ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAP_GEOMS_SEQ'); + END;