Mercurial > dive4elements > river

annotate flys-backend/doc/schema/postgresql-migrate-dami.sql @ 4975:9eea3cd22ee7 dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add dgm file reader although an awk script should have been more appropiate
author Andre Heinecke <aheinecke@intevation.de>
date Wed, 13 Feb 2013 17:46:34 +0100
parents 1a218a0bcfcf
children bdef4c74d675
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;
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
2 -- 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
3 CREATE TABLE hws_kinds (
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
4 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
5 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
6 );
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
7 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
8 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
9 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
10
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
11 CREATE TABLE fed_states (
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
12 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
13 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
14 );
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
15 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
16 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
17 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
18 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
19 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
20 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
21 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
22 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
23 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
24 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
25 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
26 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
27 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
28 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
29 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
30 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
31
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
32 --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
33 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
34 CREATE TABLE hws_lines (
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
35 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
36 ogr_fid int,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
37 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
38 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
39 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
40 name VARCHAR(256),
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
41 path VARCHAR(256),
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
42 offical INT DEFAULT 0,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
43 agency VARCHAR(256),
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
44 range VARCHAR(256),
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
45 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
46 source VARCHAR(256),
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
47 status_date TIMESTAMP,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
48 description VARCHAR(256)
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
49 );
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
50 SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'LINESTRING', 2);
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
51 SELECT AddGeometryColumn('hws_lines', 'geom_target', 31467, 'LINESTRING', 2); -- ?
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
52 SELECT AddGeometryColumn('hws_lines', 'geom_rated_level', 31467, 'LINESTRING', 2); -- ?
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
53 -- 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
54
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
55 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
56
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
57 --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
58 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
59 CREATE TABLE hws_points (
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
60 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
61 ogr_fid int,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
62 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
63 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
64 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
65 name VARCHAR,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
66 path VARCHAR,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
67 offical INT DEFAULT 0,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
68 agency VARCHAR,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
69 range VARCHAR,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
70 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
71 source VARCHAR,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
72 status_date VARCHAR,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
73 description VARCHAR,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
74 freeboard FLOAT8,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
75 dike_km FLOAT8,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
76 z FLOAT8,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
77 z_target FLOAT8,
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
78 rated_level FLOAT8
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 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
81
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
82 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
83
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
84 ALTER TABLE hydr_boundaries_poly ADD column sectie int;
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
85 ALTER TABLE hydr_boundaries_poly ADD column sobek int;
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
86 ALTER TABLE hydr_boundaries ADD column sectie int;
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
87 ALTER TABLE hydr_boundaries ADD column sobek int;
1a218a0bcfcf Importer: Add db migration script for postgres to track schema changes
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
88
http://dive4elements.wald.intevation.org