Mercurial > dive4elements > river

comparison backend/doc/schema/postgresql-migrate-dami.sql @ 5838:5aa05a7a34b7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Rename modules to more fitting names.
author Sascha L. Teichmann <teichmann@intevation.de>
date Thu, 25 Apr 2013 15:23:37 +0200
parents flys-backend/doc/schema/postgresql-migrate-dami.sql@e4cc9aebfcf1
children
comparison
equal deleted inserted replaced
5837:d9901a08d0a6 5838:5aa05a7a34b7
1 DROP table hws;
2 DROP sequence HWS_ID_SEQ;
3 DROP table lines;
4 DROP sequence LINES_ID_SEQ;
5 DROP table catchment;
6 DROP sequence CATCHMENT_ID_SEQ;
7
8 -- Static lookup tables for Hochwasserschutzanlagen
9 CREATE TABLE hws_kinds (
10 id int PRIMARY KEY NOT NULL,
11 kind VARCHAR(64) NOT NULL
12 );
13 INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass');
14 INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm');
15 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
16
17 CREATE TABLE fed_states (
18 id int PRIMARY KEY NOT NULL,
19 name VARCHAR(23) NOT NULL
20 );
21 INSERT INTO fed_states (id, name) VALUES (1, 'Bayern');
22 INSERT INTO fed_states (id, name) VALUES (2, 'Hessen');
23 INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen');
24 INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen');
25 INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz');
26 INSERT INTO fed_states (id, name) VALUES (6, 'Saarland');
27 INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein');
28 INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg');
29 INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern');
30 INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen');
31 INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg');
32 INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt');
33 INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen');
34 INSERT INTO fed_states (id, name) VALUES (14, 'Berlin');
35 INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
36 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
37
38 CREATE TABLE sectie_kinds (
39 id int PRIMARY KEY NOT NULL,
40 name VARCHAR(64) NOT NULL
41 );
42 INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt');
43 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch');
44 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank');
45 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich');
46
47 CREATE TABLE sobek_kinds (
48 id int PRIMARY KEY NOT NULL,
49 name VARCHAR(64) NOT NULL
50 );
51 INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt');
52 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend');
53 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd');
54
55 CREATE TABLE boundary_kinds (
56 id int PRIMARY KEY NOT NULL,
57 name VARCHAR(64) NOT NULL
58 );
59 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
60 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
61 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
62 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
63
64 --Hydrologie/HW-Schutzanlagen/*Linien.shp
65 CREATE SEQUENCE HWS_LINES_ID_SEQ;
66 CREATE TABLE hws_lines (
67 id int PRIMARY KEY NOT NULL,
68 ogr_fid int,
69 kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
70 fed_state_id int REFERENCES fed_states(id),
71 river_id int REFERENCES rivers(id),
72 name VARCHAR(256),
73 path VARCHAR(256),
74 offical INT DEFAULT 0,
75 agency VARCHAR(256),
76 range VARCHAR(256),
77 shore_side INT DEFAULT 0,
78 source VARCHAR(256),
79 status_date TIMESTAMP,
80 description VARCHAR(256)
81 );
82 SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'LINESTRING', 3);
83 -- TODO: dike_km_from dike_km_to, are they geometries?
84
85 ALTER TABLE hws_lines ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_LINES_ID_SEQ');
86
87 --Hydrologie/HW-Schutzanlagen/*Punkte.shp
88 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
89 CREATE TABLE hws_points (
90 id int PRIMARY KEY NOT NULL,
91 ogr_fid int,
92 kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
93 fed_state_id int REFERENCES fed_states(id),
94 river_id int REFERENCES rivers(id),
95 name VARCHAR,
96 path VARCHAR,
97 offical INT DEFAULT 0,
98 agency VARCHAR,
99 range VARCHAR,
100 shore_side INT DEFAULT 0,
101 source VARCHAR,
102 status_date VARCHAR,
103 description VARCHAR,
104 freeboard FLOAT8,
105 dike_km FLOAT8,
106 z FLOAT8,
107 z_target FLOAT8,
108 rated_level FLOAT8
109 );
110 SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2);
111
112 ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ');
113
114 ALTER TABLE hydr_boundaries_poly ADD COLUMN sectie INT REFERENCES sectie_kinds(id);
115 ALTER TABLE hydr_boundaries_poly ADD COLUMN sobek INT REFERENCES sobek_kinds(id);
116 ALTER TABLE hydr_boundaries_poly ADD FOREIGN KEY (kind) REFERENCES boundary_kinds(id);
117 ALTER TABLE hydr_boundaries ADD COLUMN sectie INT REFERENCES sectie_kinds(id);
118 ALTER TABLE hydr_boundaries ADD COLUMN sobek INT REFERENCES sobek_kinds(id);
119 ALTER TABLE hydr_boundaries ADD FOREIGN KEY (kind) REFERENCES boundary_kinds(id);
120 ALTER TABLE dem ADD COLUMN srid INT NOT NULL;
121 ALTER TABLE dem ALTER COLUMN year_from DROP NOT NULL;
122 ALTER TABLE dem ALTER COLUMN year_to DROP NOT NULL;
123 ALTER TABLE dem ALTER COLUMN projection DROP NOT NULL;
124 ALTER TABLE dem ALTER COLUMN path SET NOT NULL;
125
126 COMMIT;
127
http://dive4elements.wald.intevation.org