Mercurial > dive4elements > river
changeset 2797:e41d03bf9807
Adapted the PostgreSQL schema for floodmaps.
flys-backend/trunk@4126 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Ingo Weinzierl <ingo.weinzierl@intevation.de> |
---|---|
date | Wed, 07 Mar 2012 20:27:20 +0000 (2012-03-07) |
parents | f3b4e32b5db6 |
children | 5a654f2e35bc |
files | flys-backend/ChangeLog flys-backend/doc/schema/postgresql-spatial.sql |
diffstat | 2 files changed, 32 insertions(+), 22 deletions(-) [+] |
line wrap: on
line diff
--- a/flys-backend/ChangeLog Tue Mar 06 11:54:41 2012 +0000 +++ b/flys-backend/ChangeLog Wed Mar 07 20:27:20 2012 +0000 @@ -1,3 +1,8 @@ +2012-03-06 Ingo Weinzierl <ingo@intevation.de> + + * doc/schema/postgresql-spatial.sql: Adapted the PostgreSQL schema for + floodmaps. + 2012-03-06 Ingo Weinzierl <ingo@intevation.de> * doc/schema/postgresql-spatial.sql,
--- a/flys-backend/doc/schema/postgresql-spatial.sql Tue Mar 06 11:54:41 2012 +0000 +++ b/flys-backend/doc/schema/postgresql-spatial.sql Wed Mar 07 20:27:20 2012 +0000 @@ -123,34 +123,39 @@ ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ'); +-- --Hydrologie/UeSG -CREATE SEQUENCE FLOODMAPS_ID_SEQ; +-- +-- 'kind' can be one of: +-- 200 = Messung +-- 111 = Berechnung->Aktuell->BfG +-- 112 = Berechnung->Aktuell->Land +-- 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 TABLE floodmaps ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), + name varchar(64) NOT NULL, kind int NOT NULL, - diff NUMERIC, + diff real, 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 + area real, + perimeter real ); -SELECT AddGeometryColumn('polygons', 'geom', 31466, 'POLYGON', 2); -ALTER TABLE polygons ALTER COLUMN id SET DEFAULT NEXTVAL('POLYGONS_SEQ'); - +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'); -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'); - +CREATE TRIGGER floodmaps_id_trigger BEFORE INSERT OR UPDATE ON floodmaps + FOR EACH ROW EXECUTE PROCEDURE floodmaps_id_func(); END;