Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 2797:e41d03bf9807

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
parents f3b4e32b5db6
children 883dd49d5e74
comparison
equal deleted inserted replaced
2796:f3b4e32b5db6 2797:e41d03bf9807
121 ); 121 );
122 SELECT AddGeometryColumn('hws','geom',31466,'LINESTRING',2); 122 SELECT AddGeometryColumn('hws','geom',31466,'LINESTRING',2);
123 ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ'); 123 ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
124 124
125 125
126 --
126 --Hydrologie/UeSG 127 --Hydrologie/UeSG
127 CREATE SEQUENCE FLOODMAPS_ID_SEQ; 128 --
129 -- 'kind' can be one of:
130 -- 200 = Messung
131 -- 111 = Berechnung->Aktuell->BfG
132 -- 112 = Berechnung->Aktuell->Land
133 -- 121 = Berechnung->Potenziell->BfG
134 -- 122 = Berechnung->Potenziell->Land
135 --
136 CREATE SEQUENCE FLOODMAPS_SEQ;
137 CREATE FUNCTION floodmaps_id_func() RETURNS trigger AS $floodmaps_id_func$
138 BEGIN
139 NEW.id := nextval('floodmaps_seq');
140 RETURN NEW;
141 END;
142 $floodmaps_id_func$ LANGUAGE plpgsql;
143
128 CREATE TABLE floodmaps ( 144 CREATE TABLE floodmaps (
129 id int PRIMARY KEY NOT NULL, 145 id int PRIMARY KEY NOT NULL,
130 river_id int REFERENCES rivers(id), 146 river_id int REFERENCES rivers(id),
147 name varchar(64) NOT NULL,
131 kind int NOT NULL, 148 kind int NOT NULL,
132 diff NUMERIC, 149 diff real,
133 count int, 150 count int,
134 area NUMERIC, 151 area real,
135 perimeter NUMERIC 152 perimeter real
136 ); 153 );
137 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ'); 154 SELECT AddGeometryColumn('floodmaps', 'geom', 31466, 'MULTIPOLYGON', 2);
155 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom;
156 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text);
157 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_SEQ');
138 158
139 159 CREATE TRIGGER floodmaps_id_trigger BEFORE INSERT OR UPDATE ON floodmaps
140 CREATE SEQUENCE POLYGONS_SEQ; 160 FOR EACH ROW EXECUTE PROCEDURE floodmaps_id_func();
141 CREATE TABLE polygons (
142 id int PRIMARY KEY NOT NULL
143 );
144 SELECT AddGeometryColumn('polygons', 'geom', 31466, 'POLYGON', 2);
145 ALTER TABLE polygons ALTER COLUMN id SET DEFAULT NEXTVAL('POLYGONS_SEQ');
146
147
148 CREATE SEQUENCE FLOODMAP_GEOMS_SEQ;
149 CREATE TABLE floodmap_geoms (
150 id int PRIMARY KEY NOT NULL,
151 floodmaps_id int NOT NULL REFERENCES floodmaps(id),
152 polygons_id int NOT NULL REFERENCES polygons(id)
153 );
154 ALTER TABLE floodmap_geoms ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAP_GEOMS_SEQ');
155
156 END; 161 END;
http://dive4elements.wald.intevation.org