Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 4999:929ec3ed7dc2 dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
author Andre Heinecke <aheinecke@intevation.de>
date Fri, 15 Feb 2013 12:38:25 +0100
parents ecc6fd57b630
children ee52faa6b7ac
comparison
equal deleted inserted replaced
4998:ab5d8f73a982 4999:929ec3ed7dc2
240 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2); 240 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2);
241 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom; 241 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom;
242 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text); 242 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text);
243 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ'); 243 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ');
244 244
245 CREATE TABLE sectie_kinds (
246 id int PRIMARY KEY NOT NULL,
247 name VARCHAR(64) NOT NULL
248 );
249 INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt');
250 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch');
251 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank');
252 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich');
253
254 CREATE TABLE sobek_kinds (
255 id int PRIMARY KEY NOT NULL,
256 name VARCHAR(64) NOT NULL
257 );
258 INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt');
259 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend');
260 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd');
261
262 CREATE TABLE boundary_kinds (
263 id int PRIMARY KEY NOT NULL,
264 name VARCHAR(64) NOT NULL
265 );
266 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
267 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
268 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
269 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
270
245 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; 271 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
246 CREATE TABLE hydr_boundaries ( 272 CREATE TABLE hydr_boundaries (
247 id int PRIMARY KEY NOT NULL, 273 id int PRIMARY KEY NOT NULL,
248 river_id int REFERENCES rivers(id) ON DELETE CASCADE, 274 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
249 name VARCHAR(255), 275 name VARCHAR(255),
250 kind int, -- 1 BfG / 2 Land / 3 Sonstige 276 kind int REFERENCES boundary_kinds(id),
251 sectie int, -- 1 stromführend / 2 stromspeichernd 277 sectie int REFERENCES sectie_kinds(id),
252 sobek int, -- 1 Flussschlauch / 2 Uferbank / 3 Überflutungsbereich 278 sobek int REFERENCES sobek_kinds(id),
253 path VARCHAR(256) 279 path VARCHAR(256)
254 ); 280 );
255 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'LINESTRING',3); 281 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'LINESTRING',3);
256 ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ'); 282 ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ');
257 283
259 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; 285 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
260 CREATE TABLE hydr_boundaries_poly ( 286 CREATE TABLE hydr_boundaries_poly (
261 id int PRIMARY KEY NOT NULL, 287 id int PRIMARY KEY NOT NULL,
262 river_id int REFERENCES rivers(id) ON DELETE CASCADE, 288 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
263 name VARCHAR(255), 289 name VARCHAR(255),
264 kind int, -- 1 BfG / 2 Land / 3 Sonstige 290 kind int REFERENCES boundary_kinds(id),
265 sectie int, -- 1 Flussschlauch / 2 Uferbank / 3 Überflutungsbereich 291 sectie int REFERENCES sectie_kinds(id),
266 sobek int, -- 1 stromführend / 2 stromspeichernd 292 sobek int REFERENCES sobek_kinds(id),
267 path VARCHAR(256) 293 path VARCHAR(256)
268 ); 294 );
269 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'POLYGON',3); 295 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'POLYGON',3);
270 ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ'); 296 ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ');
271 297
http://dive4elements.wald.intevation.org