Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 5748:b5553164fabf

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
author Tom Gottfried <tom.gottfried@intevation.de>
date Wed, 17 Apr 2013 19:30:10 +0200
parents 0aaed46a88be
children 153456f84602
comparison
equal deleted inserted replaced
5747:09465f534c86 5748:b5553164fabf
248 248
249 CREATE TABLE sectie_kinds ( 249 CREATE TABLE sectie_kinds (
250 id int PRIMARY KEY NOT NULL, 250 id int PRIMARY KEY NOT NULL,
251 name VARCHAR(64) NOT NULL 251 name VARCHAR(64) NOT NULL
252 ); 252 );
253 INSERT INTO sectie_kinds (id, name) VALUES (0, 'SECTIE Unbekannt'); 253 INSERT INTO sectie_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
254 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch'); 254 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Hauptgerinne');
255 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank'); 255 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbereich');
256 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich'); 256 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Vorland');
257 257
258 CREATE TABLE sobek_kinds ( 258 CREATE TABLE sobek_kinds (
259 id int PRIMARY KEY NOT NULL, 259 id int PRIMARY KEY NOT NULL,
260 name VARCHAR(64) NOT NULL 260 name VARCHAR(64) NOT NULL
261 ); 261 );
262 INSERT INTO sobek_kinds (id, name) VALUES (0, 'SOBEK Unbekannt'); 262 INSERT INTO sobek_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
263 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend'); 263 INSERT INTO sobek_kinds (id, name) VALUES (1, 'durchströmt');
264 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd'); 264 INSERT INTO sobek_kinds (id, name) VALUES (2, 'nicht durchströmt');
265 265
266 CREATE TABLE boundary_kinds ( 266 CREATE TABLE boundary_kinds (
267 id int PRIMARY KEY NOT NULL, 267 id int PRIMARY KEY NOT NULL,
268 name VARCHAR(64) NOT NULL 268 name VARCHAR(64) NOT NULL
269 ); 269 );
276 CREATE TABLE hydr_boundaries ( 276 CREATE TABLE hydr_boundaries (
277 id int PRIMARY KEY NOT NULL, 277 id int PRIMARY KEY NOT NULL,
278 river_id int REFERENCES rivers(id) ON DELETE CASCADE, 278 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
279 name VARCHAR(255), 279 name VARCHAR(255),
280 kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, 280 kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0,
281 sectie int REFERENCES sectie_kinds(id) NOT NULL DEFAULT 0, 281 sectie int REFERENCES sectie_kinds(id),
282 sobek int REFERENCES sobek_kinds(id) NOT NULL DEFAULT 0, 282 sobek int REFERENCES sobek_kinds(id),
283 path VARCHAR(256) 283 path VARCHAR(256)
284 ); 284 );
285 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'MULTILINESTRING',3); 285 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'MULTILINESTRING',3);
286 ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ'); 286 ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ');
287 287
290 CREATE TABLE hydr_boundaries_poly ( 290 CREATE TABLE hydr_boundaries_poly (
291 id int PRIMARY KEY NOT NULL, 291 id int PRIMARY KEY NOT NULL,
292 river_id int REFERENCES rivers(id) ON DELETE CASCADE, 292 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
293 name VARCHAR(255), 293 name VARCHAR(255),
294 kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, 294 kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0,
295 sectie int REFERENCES sectie_kinds(id) NOT NULL DEFAULT 0, 295 sectie int REFERENCES sectie_kinds(id),
296 sobek int REFERENCES sobek_kinds(id) NOT NULL DEFAULT 0, 296 sobek int REFERENCES sobek_kinds(id),
297 path VARCHAR(256) 297 path VARCHAR(256)
298 ); 298 );
299 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'MULTIPOLYGON',3); 299 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'MULTIPOLYGON',3);
300 ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ'); 300 ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ');
301 301
http://dive4elements.wald.intevation.org