Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 5276:170f514a4f29

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
author Tom Gottfried <tom@intevation.de>
date Wed, 13 Mar 2013 18:39:28 +0100
parents b46df0609276
children f6478773a5bb 66d9389c80cb
comparison
equal deleted inserted replaced
5275:911f62917753 5276:170f514a4f29
206 206
207 ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ'); 207 ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ');
208 208
209 -- 209 --
210 --Hydrologie/UeSG 210 --Hydrologie/UeSG
211 -- 211 CREATE TABLE floodmap_kinds (
212 -- 'kind' can be one of: 212 id int PRIMARY KEY NOT NULL,
213 -- 200 = Messung 213 name varchar(64) NOT NULL
214 -- 111 = Berechnung->Aktuell->BfG 214 );
215 -- 112 = Berechnung->Aktuell->Land 215 INSERT INTO floodmap_kinds VALUES (200, 'Messung');
216 -- 121 = Berechnung->Potenziell->BfG 216 INSERT INTO floodmap_kinds VALUES (111, 'Berechnung-Aktuell-BfG');
217 -- 122 = Berechnung->Potenziell->Land 217 INSERT INTO floodmap_kinds VALUES (112, 'Berechnung-Aktuell-Bundesländer');
218 -- 218 INSERT INTO floodmap_kinds VALUES (121, 'Berechnung-Potenziell-BfG');
219 INSERT INTO floodmap_kinds VALUES (122, 'Berechnung-Potenziell-Bundesländer');
220
219 CREATE SEQUENCE FLOODMAPS_ID_SEQ; 221 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
220 CREATE TABLE floodmaps ( 222 CREATE TABLE floodmaps (
221 id int PRIMARY KEY NOT NULL, 223 id int PRIMARY KEY NOT NULL,
222 river_id int REFERENCES rivers(id) ON DELETE CASCADE, 224 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
223 name varchar(64) NOT NULL, 225 name varchar(64) NOT NULL,
224 kind int NOT NULL, 226 kind int NOT NULL REFERENCES floodmap_kinds(id),
225 diff FLOAT8, 227 diff FLOAT8,
226 count int, 228 count int,
227 area FLOAT8, 229 area FLOAT8,
228 perimeter FLOAT8, 230 perimeter FLOAT8,
229 path VARCHAR(256) 231 path VARCHAR(256),
232 source varchar(64)
230 ); 233 );
231 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2); 234 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2);
232 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom; 235 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom;
233 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text); 236 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text);
234 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ'); 237 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ');
http://dive4elements.wald.intevation.org