Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-migrate-dami.sql @ 4954:1a218a0bcfcf dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Importer: Add db migration script for postgres to track schema changes
author Andre Heinecke <aheinecke@intevation.de>
date Fri, 01 Feb 2013 10:43:19 +0100
parents
children bdef4c74d675
comparison
equal deleted inserted replaced
4953:a72e90fc45ed 4954:1a218a0bcfcf
1 DROP table hws;
2 -- Static lookup tables for Hochwasserschutzanlagen
3 CREATE TABLE hws_kinds (
4 id int PRIMARY KEY NOT NULL,
5 kind VARCHAR(64) NOT NULL
6 );
7 INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass');
8 INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm');
9 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
10
11 CREATE TABLE fed_states (
12 id int PRIMARY KEY NOT NULL,
13 name VARCHAR(23) NOT NULL
14 );
15 INSERT INTO fed_states (id, name) VALUES (1, 'Bayern');
16 INSERT INTO fed_states (id, name) VALUES (2, 'Hessen');
17 INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen');
18 INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen');
19 INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz');
20 INSERT INTO fed_states (id, name) VALUES (6, 'Saarland');
21 INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein');
22 INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg');
23 INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern');
24 INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen');
25 INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg');
26 INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt');
27 INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen');
28 INSERT INTO fed_states (id, name) VALUES (14, 'Berlin');
29 INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
30 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
31
32 --Hydrologie/HW-Schutzanlagen/*Linien.shp
33 CREATE SEQUENCE HWS_LINES_ID_SEQ;
34 CREATE TABLE hws_lines (
35 id int PRIMARY KEY NOT NULL,
36 ogr_fid int,
37 kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
38 fed_state_id int REFERENCES fed_states(id),
39 river_id int REFERENCES rivers(id),
40 name VARCHAR(256),
41 path VARCHAR(256),
42 offical INT DEFAULT 0,
43 agency VARCHAR(256),
44 range VARCHAR(256),
45 shore_side INT DEFAULT 0,
46 source VARCHAR(256),
47 status_date TIMESTAMP,
48 description VARCHAR(256)
49 );
50 SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'LINESTRING', 2);
51 SELECT AddGeometryColumn('hws_lines', 'geom_target', 31467, 'LINESTRING', 2); -- ?
52 SELECT AddGeometryColumn('hws_lines', 'geom_rated_level', 31467, 'LINESTRING', 2); -- ?
53 -- TODO: dike_km_from dike_km_to, are they geometries?
54
55 ALTER TABLE hws_lines ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_LINES_ID_SEQ');
56
57 --Hydrologie/HW-Schutzanlagen/*Punkte.shp
58 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
59 CREATE TABLE hws_points (
60 id int PRIMARY KEY NOT NULL,
61 ogr_fid int,
62 kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
63 fed_state_id int REFERENCES fed_states(id),
64 river_id int REFERENCES rivers(id),
65 name VARCHAR,
66 path VARCHAR,
67 offical INT DEFAULT 0,
68 agency VARCHAR,
69 range VARCHAR,
70 shore_side INT DEFAULT 0,
71 source VARCHAR,
72 status_date VARCHAR,
73 description VARCHAR,
74 freeboard FLOAT8,
75 dike_km FLOAT8,
76 z FLOAT8,
77 z_target FLOAT8,
78 rated_level FLOAT8
79 );
80 SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2);
81
82 ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ');
83
84 ALTER TABLE hydr_boundaries_poly ADD column sectie int;
85 ALTER TABLE hydr_boundaries_poly ADD column sobek int;
86 ALTER TABLE hydr_boundaries ADD column sectie int;
87 ALTER TABLE hydr_boundaries ADD column sobek int;
88
http://dive4elements.wald.intevation.org