annotate flys-backend/doc/schema/postgresql-migrate-dami.sql @ 5037:2837b11cdb13 dami

SCHEMA Change: Make hws_lines a 3d geom
author Andre Heinecke <aheinecke@intevation.de>
date Wed, 20 Feb 2013 10:43:00 +0100
parents bdef4c74d675
children df090e461c30
rev   line source
4954
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
1 DROP table hws;
5026
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
2 DROP sequence HWS_ID_SEQ;
4954
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
3 -- Static lookup tables for Hochwasserschutzanlagen
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
4 CREATE TABLE hws_kinds (
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
5 id int PRIMARY KEY NOT NULL,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
6 kind VARCHAR(64) NOT NULL
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
7 );
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
8 INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
9 INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
10 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
11
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
12 CREATE TABLE fed_states (
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
13 id int PRIMARY KEY NOT NULL,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
14 name VARCHAR(23) NOT NULL
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
15 );
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
16 INSERT INTO fed_states (id, name) VALUES (1, 'Bayern');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
17 INSERT INTO fed_states (id, name) VALUES (2, 'Hessen');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
18 INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
19 INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
20 INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
21 INSERT INTO fed_states (id, name) VALUES (6, 'Saarland');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
22 INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
23 INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
24 INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
25 INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
26 INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
27 INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
28 INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
29 INSERT INTO fed_states (id, name) VALUES (14, 'Berlin');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
30 INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
31 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
32
5026
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
33 CREATE TABLE sectie_kinds (
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
34 id int PRIMARY KEY NOT NULL,
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
35 name VARCHAR(64) NOT NULL
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
36 );
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
37 INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt');
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
38 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch');
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
39 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank');
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
40 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich');
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
41
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
42 CREATE TABLE sobek_kinds (
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
43 id int PRIMARY KEY NOT NULL,
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
44 name VARCHAR(64) NOT NULL
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
45 );
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
46 INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt');
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
47 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend');
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
48 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd');
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
49
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
50 CREATE TABLE boundary_kinds (
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
51 id int PRIMARY KEY NOT NULL,
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
52 name VARCHAR(64) NOT NULL
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
53 );
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
54 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
55 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
56 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
57 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
58
4954
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
59 --Hydrologie/HW-Schutzanlagen/*Linien.shp
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
60 CREATE SEQUENCE HWS_LINES_ID_SEQ;
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
61 CREATE TABLE hws_lines (
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
62 id int PRIMARY KEY NOT NULL,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
63 ogr_fid int,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
64 kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
65 fed_state_id int REFERENCES fed_states(id),
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
66 river_id int REFERENCES rivers(id),
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
67 name VARCHAR(256),
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
68 path VARCHAR(256),
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
69 offical INT DEFAULT 0,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
70 agency VARCHAR(256),
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
71 range VARCHAR(256),
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
72 shore_side INT DEFAULT 0,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
73 source VARCHAR(256),
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
74 status_date TIMESTAMP,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
75 description VARCHAR(256)
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
76 );
5037
2837b11cdb13 SCHEMA Change: Make hws_lines a 3d geom
Andre Heinecke <aheinecke@intevation.de>
parents: 5026
diff changeset
77 SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'LINESTRING', 3);
4954
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
78 -- TODO: dike_km_from dike_km_to, are they geometries?
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
79
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
80 ALTER TABLE hws_lines ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_LINES_ID_SEQ');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
81
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
82 --Hydrologie/HW-Schutzanlagen/*Punkte.shp
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
83 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
84 CREATE TABLE hws_points (
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
85 id int PRIMARY KEY NOT NULL,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
86 ogr_fid int,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
87 kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
88 fed_state_id int REFERENCES fed_states(id),
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
89 river_id int REFERENCES rivers(id),
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
90 name VARCHAR,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
91 path VARCHAR,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
92 offical INT DEFAULT 0,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
93 agency VARCHAR,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
94 range VARCHAR,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
95 shore_side INT DEFAULT 0,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
96 source VARCHAR,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
97 status_date VARCHAR,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
98 description VARCHAR,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
99 freeboard FLOAT8,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
100 dike_km FLOAT8,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
101 z FLOAT8,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
102 z_target FLOAT8,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
103 rated_level FLOAT8
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
104 );
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
105 SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2);
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
106
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
107 ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ');
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
108
5026
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
109 ALTER TABLE hydr_boundaries_poly ADD COLUMN sectie INT REFERENCES sectie_kinds(id);
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
110 ALTER TABLE hydr_boundaries_poly ADD COLUMN sobek INT REFERENCES sobek_kinds(id);
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
111 ALTER TABLE hydr_boundaries ADD COLUMN sectie INT REFERENCES sectie_kinds(id);
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
112 ALTER TABLE hydr_boundaries ADD COLUMN sobek INT REFERENCES sobek_kinds(id);
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
113 ALTER TABLE hydr_boundaries ADD FOREIGN KEY (kind) REFERENCES boundary_kinds(id);
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
114 ALTER TABLE dem ADD COLUMN srid INT NOT NULL;
4954
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
115
5026
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
116 COMMIT;
bdef4c74d675 Update migrators for changes made in the dami branch and add
Andre Heinecke <aheinecke@intevation.de>
parents: 4954
diff changeset
117

http://dive4elements.wald.intevation.org