Mercurial > dive4elements > river
changeset 3948:883dd49d5e74
Adapted PostgreSQL spatial schema.
flys-backend/trunk@5585 c6561f87-3c4e-4783-a992-168aeb5c3f6f
| author | Ingo Weinzierl <ingo.weinzierl@intevation.de> |
|---|---|
| date | Mon, 24 Sep 2012 09:13:29 +0000 |
| parents | 371e38af6f79 |
| children | 145b87bcb10a |
| files | flys-backend/ChangeLog flys-backend/doc/schema/postgresql-drop-spatial.sql flys-backend/doc/schema/postgresql-spatial.sql |
| diffstat | 3 files changed, 118 insertions(+), 22 deletions(-) [+] |
line wrap: on
line diff
--- a/flys-backend/ChangeLog Mon Sep 24 07:58:34 2012 +0000 +++ b/flys-backend/ChangeLog Mon Sep 24 09:13:29 2012 +0000 @@ -1,3 +1,11 @@ +2012-09-24 Ingo Weinzierl <ingo@intevation.de> + + * doc/schema/postgresql-spatial.sql: Adapted schema; added missing + relations. + + * doc/schema/postgresql-drop-spatial.sql: SQL statements to drop an + existing FLYS postgresql schema. + 2012-09-21 Björn Ricks <bjoern.ricks@intevation.de> * src/main/java/de/intevation/flys/model/River.java,
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-backend/doc/schema/postgresql-drop-spatial.sql Mon Sep 24 09:13:29 2012 +0000 @@ -0,0 +1,45 @@ +BEGIN; + +DROP TABLE river_axes; +DROP SEQUENCE RIVER_AXES_ID_SEQ; + +DROP TABLE river_axes_km; +DROP SEQUENCE RIVER_AXES_KM_ID_SEQ; + +DROP TABLE cross_section_tracks; +DROP SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; + +DROP TABLE lines; +DROP SEQUENCE LINES_ID_SEQ; + +DROP TABLE buildings; +DROP SEQUENCE BUILDINGS_ID_SEQ; + +DROP TABLE fixpoints; +DROP SEQUENCE FIXPOINTS_ID_SEQ; + +DROP TABLE floodplain; +DROP SEQUENCE FLOODPLAIN_ID_SEQ; + +DROP TABLE dem; +DROP SEQUENCE DEM_ID_SEQ; + +DROP TABLE catchment; +DROP SEQUENCE CATCHMENT_ID_SEQ; + +DROP TABLE hws; +DROP SEQUENCE HWS_ID_SEQ; + +DROP TABLE floodmaps; +DROP SEQUENCE FLOODMAPS_ID_SEQ; + +DROP TABLE hydr_boundaries; +DROP SEQUENCE HYDR_BOUNDARIES_ID_SEQ; + +DROP TABLE hydr_boundaries_poly; +DROP SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; + +DROP TABLE gauge_location; +DROP SEQUENCE GAUGE_LOCATION_ID_SEQ; + +COMMIT;
--- a/flys-backend/doc/schema/postgresql-spatial.sql Mon Sep 24 07:58:34 2012 +0000 +++ b/flys-backend/doc/schema/postgresql-spatial.sql Mon Sep 24 09:13:29 2012 +0000 @@ -5,7 +5,9 @@ CREATE TABLE river_axes ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), - kind int NOT NULL DEFAULT 0 + kind int NOT NULL DEFAULT 0, + name VARCHAR(64), + path VARCHAR(256) ); SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2); ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ'); @@ -17,7 +19,9 @@ CREATE TABLE river_axes_km ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), - km NUMERIC NOT NULL + km NUMERIC NOT NULL, + name VARCHAR(64), + path VARCHAR(256) ); SELECT AddGeometryColumn('river_axes_km', 'geom', 31466, 'POINT', 2); ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ'); @@ -29,7 +33,9 @@ id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), km NUMERIC NOT NULL, - z NUMERIC NOT NULL DEFAULT 0 + z NUMERIC NOT NULL DEFAULT 0, + name VARCHAR(64), + path VARCHAR(256) ); SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2); ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ'); @@ -41,7 +47,9 @@ id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), kind VARCHAR(16) NOT NULL, - z NUMERIC DEFAULT 0 + z NUMERIC DEFAULT 0, + name VARCHAR(64), + path VARCHAR(256) ); SELECT AddGeometryColumn('lines', 'geom', 31466, 'LINESTRING', 4); ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ'); @@ -55,7 +63,8 @@ CREATE TABLE buildings ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), - name VARCHAR(256) + name VARCHAR(256), + path VARCHAR(256) ); SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2); ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ'); @@ -69,7 +78,9 @@ x int, y int, km NUMERIC NOT NULL, - HPGP VARCHAR(2) + HPGP VARCHAR(2), + name VARCHAR(64), + path VARCHAR(256) ); SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2); ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ'); @@ -79,7 +90,9 @@ CREATE SEQUENCE FLOODPLAIN_ID_SEQ; CREATE TABLE floodplain ( id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) + river_id int REFERENCES rivers(id), + name VARCHAR(64), + path VARCHAR(256) ); SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'POLYGON', 2); ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ'); @@ -105,7 +118,8 @@ id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), area NUMERIC, - name VARCHAR(256) + name VARCHAR(256), + path VARCHAR(256) ); SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2); ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ'); @@ -117,7 +131,9 @@ id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), hws_facility VARCHAR(256), - type VARCHAR(256) + type VARCHAR(256), + name VARCHAR(64), + path VARCHAR(256) ); SELECT AddGeometryColumn('hws','geom',31466,'LINESTRING',2); ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ'); @@ -133,14 +149,7 @@ -- 121 = Berechnung->Potenziell->BfG -- 122 = Berechnung->Potenziell->Land -- -CREATE SEQUENCE FLOODMAPS_SEQ; -CREATE FUNCTION floodmaps_id_func() RETURNS trigger AS $floodmaps_id_func$ - BEGIN - NEW.id := nextval('floodmaps_seq'); - RETURN NEW; - END; -$floodmaps_id_func$ LANGUAGE plpgsql; - +CREATE SEQUENCE FLOODMAPS_ID_SEQ; CREATE TABLE floodmaps ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), @@ -149,13 +158,47 @@ diff real, count int, area real, - perimeter real + perimeter real, + path VARCHAR(256) ); SELECT AddGeometryColumn('floodmaps', 'geom', 31466, 'MULTIPOLYGON', 2); ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom; ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text); -ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_SEQ'); +ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ'); -CREATE TRIGGER floodmaps_id_trigger BEFORE INSERT OR UPDATE ON floodmaps - FOR EACH ROW EXECUTE PROCEDURE floodmaps_id_func(); -END; + +CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; +CREATE TABLE hydr_boundaries ( + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id), + name VARCHAR(255), + kind int, + path VARCHAR(256) +); +SELECT AddGeometryColumn('hydr_boundaries','geom',31466,'LINESTRING',4); +ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ'); + + +CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; +CREATE TABLE hydr_boundaries_poly ( + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id), + name VARCHAR(255), + kind int, + path VARCHAR(256) +); +SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31466,'POLYGON',4); +ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ'); + + +CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ; +CREATE TABLE gauge_location ( + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id), + name VARCHAR(255), + path VARCHAR(256) +); +SELECT AddGeometryColumn('gauge_location','geom',31466,'POINT',2); +ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ'); + +COMMIT;