Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-migrate-dami.sql @ 5026:bdef4c74d675 dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Update migrators for changes made in the dami branch and add oracle migrator. Both are currently untested!
author Andre Heinecke <aheinecke@intevation.de>
date Tue, 19 Feb 2013 14:59:44 +0100
parents 1a218a0bcfcf
children 2837b11cdb13
comparison
equal deleted inserted replaced
5025:9ef49f3b0304 5026:bdef4c74d675
1 DROP table hws; 1 DROP table hws;
2 DROP sequence HWS_ID_SEQ;
2 -- Static lookup tables for Hochwasserschutzanlagen 3 -- Static lookup tables for Hochwasserschutzanlagen
3 CREATE TABLE hws_kinds ( 4 CREATE TABLE hws_kinds (
4 id int PRIMARY KEY NOT NULL, 5 id int PRIMARY KEY NOT NULL,
5 kind VARCHAR(64) NOT NULL 6 kind VARCHAR(64) NOT NULL
6 ); 7 );
27 INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen'); 28 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 (14, 'Berlin');
29 INSERT INTO fed_states (id, name) VALUES (15, 'Bremen'); 30 INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
30 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg'); 31 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
31 32
33 CREATE TABLE sectie_kinds (
34 id int PRIMARY KEY NOT NULL,
35 name VARCHAR(64) NOT NULL
36 );
37 INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt');
38 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch');
39 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank');
40 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich');
41
42 CREATE TABLE sobek_kinds (
43 id int PRIMARY KEY NOT NULL,
44 name VARCHAR(64) NOT NULL
45 );
46 INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt');
47 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend');
48 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd');
49
50 CREATE TABLE boundary_kinds (
51 id int PRIMARY KEY NOT NULL,
52 name VARCHAR(64) NOT NULL
53 );
54 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
55 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
56 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
57 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
58
32 --Hydrologie/HW-Schutzanlagen/*Linien.shp 59 --Hydrologie/HW-Schutzanlagen/*Linien.shp
33 CREATE SEQUENCE HWS_LINES_ID_SEQ; 60 CREATE SEQUENCE HWS_LINES_ID_SEQ;
34 CREATE TABLE hws_lines ( 61 CREATE TABLE hws_lines (
35 id int PRIMARY KEY NOT NULL, 62 id int PRIMARY KEY NOT NULL,
36 ogr_fid int, 63 ogr_fid int,
46 source VARCHAR(256), 73 source VARCHAR(256),
47 status_date TIMESTAMP, 74 status_date TIMESTAMP,
48 description VARCHAR(256) 75 description VARCHAR(256)
49 ); 76 );
50 SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'LINESTRING', 2); 77 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? 78 -- TODO: dike_km_from dike_km_to, are they geometries?
54 79
55 ALTER TABLE hws_lines ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_LINES_ID_SEQ'); 80 ALTER TABLE hws_lines ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_LINES_ID_SEQ');
56 81
57 --Hydrologie/HW-Schutzanlagen/*Punkte.shp 82 --Hydrologie/HW-Schutzanlagen/*Punkte.shp
79 ); 104 );
80 SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2); 105 SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2);
81 106
82 ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ'); 107 ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ');
83 108
84 ALTER TABLE hydr_boundaries_poly ADD column sectie int; 109 ALTER TABLE hydr_boundaries_poly ADD COLUMN sectie INT REFERENCES sectie_kinds(id);
85 ALTER TABLE hydr_boundaries_poly ADD column sobek int; 110 ALTER TABLE hydr_boundaries_poly ADD COLUMN sobek INT REFERENCES sobek_kinds(id);
86 ALTER TABLE hydr_boundaries ADD column sectie int; 111 ALTER TABLE hydr_boundaries ADD COLUMN sectie INT REFERENCES sectie_kinds(id);
87 ALTER TABLE hydr_boundaries ADD column sobek int; 112 ALTER TABLE hydr_boundaries ADD COLUMN sobek INT REFERENCES sobek_kinds(id);
113 ALTER TABLE hydr_boundaries ADD FOREIGN KEY (kind) REFERENCES boundary_kinds(id);
114 ALTER TABLE dem ADD COLUMN srid INT NOT NULL;
88 115
116 COMMIT;
117
http://dive4elements.wald.intevation.org