Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 5379:61bf64b102bc mapgenfix

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merge with default branch
author Christian Lins <christian.lins@intevation.de>
date Fri, 22 Mar 2013 11:25:54 +0100
parents aa407dfb9949
children 7b35f2c070c3
comparison
equal deleted inserted replaced
5175:cfc5540a4eec 5379:61bf64b102bc
1 BEGIN; 1 BEGIN;
2
3 CREATE TABLE axis_kinds(
4 id int PRIMARY KEY NOT NULL,
5 name VARCHAR(64)
6 );
7 INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt');
8 INSERT INTO axis_kinds(id, name) VALUES (1, 'Aktuell');
9 INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige');
2 10
3 -- Geodaesie/Flussachse+km/achse 11 -- Geodaesie/Flussachse+km/achse
4 CREATE SEQUENCE RIVER_AXES_ID_SEQ; 12 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
5 CREATE TABLE river_axes ( 13 CREATE TABLE river_axes (
6 id int PRIMARY KEY NOT NULL, 14 id int PRIMARY KEY NOT NULL,
7 river_id int REFERENCES rivers(id), 15 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
8 kind int NOT NULL DEFAULT 0, 16 kind_id int REFERENCES axis_kinds(id) NOT NULL DEFAULT 0,
9 name VARCHAR(64), 17 name VARCHAR(64),
10 path VARCHAR(256) 18 path VARCHAR(256)
11 ); 19 );
12 SELECT AddGeometryColumn('river_axes', 'geom', 31467, 'LINESTRING', 2); 20 SELECT AddGeometryColumn('river_axes', 'geom', 31467, 'MULTILINESTRING', 2);
13 ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ'); 21 ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ');
14 22
15 23
16 -- TODO: TestMe. 24 -- TODO: TestMe.
17 -- Geodaesie/Flussachse+km/km.shp 25 -- Geodaesie/Flussachse+km/km.shp
18 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; 26 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
19 CREATE TABLE river_axes_km ( 27 CREATE TABLE river_axes_km (
20 id int PRIMARY KEY NOT NULL, 28 id int PRIMARY KEY NOT NULL,
21 river_id int REFERENCES rivers(id), 29 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
22 km NUMERIC NOT NULL, 30 km FLOAT8 NOT NULL,
23 name VARCHAR(64), 31 name VARCHAR(64),
24 path VARCHAR(256) 32 path VARCHAR(256)
25 ); 33 );
26 SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2); 34 SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2);
27 ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ'); 35 ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ');
28 36
29 37
30 --Geodaesie/Querprofile/QP-Spuren/qps.shp 38 --Geodaesie/Querprofile/QP-Spuren/qps.shp
39 CREATE TABLE cross_section_track_kinds(
40 id int PRIMARY KEY NOT NULL,
41 name VARCHAR(64)
42 );
43 INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige');
44 INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'Aktuell');
45
31 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; 46 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
32 CREATE TABLE cross_section_tracks ( 47 CREATE TABLE cross_section_tracks (
33 id int PRIMARY KEY NOT NULL, 48 id int PRIMARY KEY NOT NULL,
34 river_id int REFERENCES rivers(id), 49 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
35 km NUMERIC NOT NULL, 50 kind_id int REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0,
36 z NUMERIC NOT NULL DEFAULT 0, 51 km FLOAT8 NOT NULL,
52 z FLOAT8 NOT NULL DEFAULT 0,
37 name VARCHAR(64), 53 name VARCHAR(64),
38 path VARCHAR(256) 54 path VARCHAR(256)
39 ); 55 );
40 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, 'LINESTRING', 2); 56 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, 'LINESTRING', 2);
41 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ'); 57 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');
42
43
44 -- Geodaesie/Linien/rohre-und-spreen
45 CREATE SEQUENCE LINES_ID_SEQ;
46 CREATE TABLE lines (
47 id int PRIMARY KEY NOT NULL,
48 river_id int REFERENCES rivers(id),
49 kind VARCHAR(16) NOT NULL,
50 z NUMERIC DEFAULT 0,
51 name VARCHAR(64),
52 path VARCHAR(256)
53 );
54 SELECT AddGeometryColumn('lines', 'geom', 31467, 'LINESTRING', 3);
55 ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ');
56 -- 'kind':
57 -- 0: ROHR1
58 -- 1: DAMM
59 58
60 59
61 -- Geodaesie/Bauwerke/Wehre.shp 60 -- Geodaesie/Bauwerke/Wehre.shp
62 CREATE SEQUENCE BUILDINGS_ID_SEQ; 61 CREATE SEQUENCE BUILDINGS_ID_SEQ;
63 CREATE TABLE buildings ( 62 CREATE TABLE buildings (
64 id int PRIMARY KEY NOT NULL, 63 id int PRIMARY KEY NOT NULL,
65 river_id int REFERENCES rivers(id), 64 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
66 name VARCHAR(256), 65 name VARCHAR(256),
67 path VARCHAR(256) 66 path VARCHAR(256)
68 ); 67 );
69 SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2); 68 SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2);
70 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ'); 69 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ');
72 71
73 -- Geodaesie/Festpunkte/Festpunkte.shp 72 -- Geodaesie/Festpunkte/Festpunkte.shp
74 CREATE SEQUENCE FIXPOINTS_ID_SEQ; 73 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
75 CREATE TABLE fixpoints ( 74 CREATE TABLE fixpoints (
76 id int PRIMARY KEY NOT NULL, 75 id int PRIMARY KEY NOT NULL,
77 river_id int REFERENCES rivers(id), 76 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
78 x int, 77 x FLOAT8,
79 y int, 78 y FLOAT8,
80 km NUMERIC NOT NULL, 79 km FLOAT8 NOT NULL,
81 HPGP VARCHAR(2), 80 HPGP VARCHAR(2),
82 name VARCHAR(64), 81 name VARCHAR(64),
83 path VARCHAR(256) 82 path VARCHAR(256)
84 ); 83 );
85 SELECT AddGeometryColumn('fixpoints', 'geom', 31467, 'POINT', 2); 84 SELECT AddGeometryColumn('fixpoints', 'geom', 31467, 'POINT', 2);
86 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ'); 85 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ');
87 86
88 87
89 -- Hydrologie/Hydr. Grenzen/talaue.shp 88 -- Hydrologie/Hydr. Grenzen/talaue.shp
89 CREATE TABLE floodplain_kinds(
90 id int PRIMARY KEY NOT NULL,
91 name VARCHAR(64)
92 );
93 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige');
94 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'Aktuell');
95
90 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; 96 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
91 CREATE TABLE floodplain ( 97 CREATE TABLE floodplain (
92 id int PRIMARY KEY NOT NULL, 98 id int PRIMARY KEY NOT NULL,
93 river_id int REFERENCES rivers(id), 99 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
100 kind_id int REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0,
94 name VARCHAR(64), 101 name VARCHAR(64),
95 path VARCHAR(256) 102 path VARCHAR(256)
96 ); 103 );
97 SELECT AddGeometryColumn('floodplain', 'geom', 31467, 'POLYGON', 2); 104 SELECT AddGeometryColumn('floodplain', 'geom', 31467, 'POLYGON', 2);
98 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ'); 105 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');
100 107
101 -- Geodaesie/Hoehenmodelle/* 108 -- Geodaesie/Hoehenmodelle/*
102 CREATE SEQUENCE DEM_ID_SEQ; 109 CREATE SEQUENCE DEM_ID_SEQ;
103 CREATE TABLE dem ( 110 CREATE TABLE dem (
104 id int PRIMARY KEY NOT NULL, 111 id int PRIMARY KEY NOT NULL,
105 river_id int REFERENCES rivers(id), 112 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
106 -- XXX Should we use the ranges table instead? 113 -- XXX Should we use the ranges table instead?
107 name VARCHAR(64), 114 name VARCHAR(64),
108 lower NUMERIC, 115 range_id INT REFERENCES ranges(id),
109 upper NUMERIC, 116 time_interval_id INT REFERENCES time_intervals(id),
110 year_from VARCHAR(32) NOT NULL, 117 projection VARCHAR(32),
111 year_to VARCHAR(32) NOT NULL, 118 srid int NOT NULL,
112 projection VARCHAR(32) NOT NULL, 119 elevation_state VARCHAR(32),
113 elevation_state VARCHAR(32), 120 format VARCHAR(32),
114 format VARCHAR(32), 121 border_break BOOLEAN NOT NULL DEFAULT FALSE,
115 border_break BOOLEAN NOT NULL DEFAULT FALSE, 122 resolution VARCHAR(16),
116 resolution VARCHAR(16), 123 description VARCHAR(256),
117 description VARCHAR(256), 124 path VARCHAR(256) NOT NULL
118 path VARCHAR(256)
119 ); 125 );
120 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ'); 126 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');
121 127
122 128
123 -- Hydrologie/Einzugsgebiete/EZG.shp 129 -- Static lookup tables for Hochwasserschutzanlagen
124 CREATE SEQUENCE CATCHMENT_ID_SEQ; 130 CREATE TABLE hws_kinds (
125 CREATE TABLE catchment ( 131 id int PRIMARY KEY NOT NULL,
126 id int PRIMARY KEY NOT NULL, 132 kind VARCHAR(64) NOT NULL
127 river_id int REFERENCES rivers(id), 133 );
128 area NUMERIC, 134 INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass');
135 INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm');
136 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
137
138 CREATE TABLE fed_states (
139 id int PRIMARY KEY NOT NULL,
140 name VARCHAR(23) NOT NULL
141 );
142 INSERT INTO fed_states (id, name) VALUES (1, 'Bayern');
143 INSERT INTO fed_states (id, name) VALUES (2, 'Hessen');
144 INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen');
145 INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen');
146 INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz');
147 INSERT INTO fed_states (id, name) VALUES (6, 'Saarland');
148 INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein');
149 INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg');
150 INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern');
151 INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen');
152 INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg');
153 INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt');
154 INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen');
155 INSERT INTO fed_states (id, name) VALUES (14, 'Berlin');
156 INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
157 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
158
159 --Hydrologie/HW-Schutzanlagen/*Linien.shp
160 CREATE SEQUENCE HWS_LINES_ID_SEQ;
161 CREATE TABLE hws_lines (
162 id int PRIMARY KEY NOT NULL,
163 ogr_fid int,
164 kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
165 fed_state_id int REFERENCES fed_states(id),
166 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
129 name VARCHAR(256), 167 name VARCHAR(256),
130 path VARCHAR(256) 168 path VARCHAR(256),
131 ); 169 official INT DEFAULT 0,
132 SELECT AddGeometryColumn('catchment','geom',31467,'POLYGON',2); 170 agency VARCHAR(256),
133 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ'); 171 range VARCHAR(256),
134 172 shore_side INT DEFAULT 0,
135 173 source VARCHAR(256),
136 --Hydrologie/HW-Schutzanlagen/hws.shp 174 status_date TIMESTAMP,
137 CREATE SEQUENCE HWS_ID_SEQ; 175 description VARCHAR(256)
138 CREATE TABLE hws ( 176 );
139 id int PRIMARY KEY NOT NULL, 177 SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'MULTILINESTRING', 3);
140 river_id int REFERENCES rivers(id), 178 -- TODO: dike_km_from dike_km_to, are they geometries?
141 hws_facility VARCHAR(256), 179
142 type VARCHAR(256), 180 ALTER TABLE hws_lines ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_LINES_ID_SEQ');
143 name VARCHAR(64), 181
144 path VARCHAR(256) 182 --Hydrologie/HW-Schutzanlagen/*Punkte.shp
145 ); 183 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
146 SELECT AddGeometryColumn('hws','geom',31467,'LINESTRING',2); 184 CREATE TABLE hws_points (
147 ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ'); 185 id int PRIMARY KEY NOT NULL,
148 186 ogr_fid int,
187 kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
188 fed_state_id int REFERENCES fed_states(id),
189 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
190 name VARCHAR,
191 path VARCHAR,
192 official INT DEFAULT 0,
193 agency VARCHAR,
194 range VARCHAR,
195 shore_side INT DEFAULT 0,
196 source VARCHAR,
197 status_date VARCHAR,
198 description VARCHAR,
199 freeboard FLOAT8,
200 dike_km FLOAT8,
201 z FLOAT8,
202 z_target FLOAT8,
203 rated_level FLOAT8
204 );
205 SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2);
206
207 ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ');
149 208
150 -- 209 --
151 --Hydrologie/UeSG 210 --Hydrologie/UeSG
152 -- 211 CREATE TABLE floodmap_kinds (
153 -- 'kind' can be one of: 212 id int PRIMARY KEY NOT NULL,
154 -- 200 = Messung 213 name varchar(64) NOT NULL
155 -- 111 = Berechnung->Aktuell->BfG 214 );
156 -- 112 = Berechnung->Aktuell->Land 215 INSERT INTO floodmap_kinds VALUES (200, 'Messung');
157 -- 121 = Berechnung->Potenziell->BfG 216 INSERT INTO floodmap_kinds VALUES (111, 'Berechnung-Aktuell-BfG');
158 -- 122 = Berechnung->Potenziell->Land 217 INSERT INTO floodmap_kinds VALUES (112, 'Berechnung-Aktuell-Bundesländer');
159 -- 218 INSERT INTO floodmap_kinds VALUES (121, 'Berechnung-Potenziell-BfG');
219 INSERT INTO floodmap_kinds VALUES (122, 'Berechnung-Potenziell-Bundesländer');
220
160 CREATE SEQUENCE FLOODMAPS_ID_SEQ; 221 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
161 CREATE TABLE floodmaps ( 222 CREATE TABLE floodmaps (
162 id int PRIMARY KEY NOT NULL, 223 id int PRIMARY KEY NOT NULL,
163 river_id int REFERENCES rivers(id), 224 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
164 name varchar(64) NOT NULL, 225 name varchar(64) NOT NULL,
165 kind int NOT NULL, 226 kind int NOT NULL REFERENCES floodmap_kinds(id),
166 diff real, 227 diff FLOAT8,
167 count int, 228 count int,
168 area real, 229 area FLOAT8,
169 perimeter real, 230 perimeter FLOAT8,
170 path VARCHAR(256) 231 path VARCHAR(256),
232 source varchar(64)
171 ); 233 );
172 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2); 234 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2);
173 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom; 235 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom;
174 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text); 236 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text);
175 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ'); 237 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ');
176 238
239 CREATE TABLE sectie_kinds (
240 id int PRIMARY KEY NOT NULL,
241 name VARCHAR(64) NOT NULL
242 );
243 INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt');
244 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch');
245 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank');
246 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich');
247
248 CREATE TABLE sobek_kinds (
249 id int PRIMARY KEY NOT NULL,
250 name VARCHAR(64) NOT NULL
251 );
252 INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt');
253 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend');
254 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd');
255
256 CREATE TABLE boundary_kinds (
257 id int PRIMARY KEY NOT NULL,
258 name VARCHAR(64) NOT NULL
259 );
260 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
261 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
262 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
263 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
177 264
178 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; 265 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
179 CREATE TABLE hydr_boundaries ( 266 CREATE TABLE hydr_boundaries (
180 id int PRIMARY KEY NOT NULL, 267 id int PRIMARY KEY NOT NULL,
181 river_id int REFERENCES rivers(id), 268 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
182 name VARCHAR(255), 269 name VARCHAR(255),
183 kind int, 270 kind int REFERENCES boundary_kinds(id),
271 sectie int REFERENCES sectie_kinds(id),
272 sobek int REFERENCES sobek_kinds(id),
184 path VARCHAR(256) 273 path VARCHAR(256)
185 ); 274 );
186 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'LINESTRING',3); 275 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'MULTILINESTRING',3);
187 ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ'); 276 ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ');
188 277
189 278
190 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; 279 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
191 CREATE TABLE hydr_boundaries_poly ( 280 CREATE TABLE hydr_boundaries_poly (
192 id int PRIMARY KEY NOT NULL, 281 id int PRIMARY KEY NOT NULL,
193 river_id int REFERENCES rivers(id), 282 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
194 name VARCHAR(255), 283 name VARCHAR(255),
195 kind int, 284 kind int REFERENCES boundary_kinds(id),
285 sectie int REFERENCES sectie_kinds(id),
286 sobek int REFERENCES sobek_kinds(id),
196 path VARCHAR(256) 287 path VARCHAR(256)
197 ); 288 );
198 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'POLYGON',3); 289 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'MULTIPOLYGON',3);
199 ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ'); 290 ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ');
200 291
201 292
202 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ; 293 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
203 CREATE TABLE gauge_location ( 294 CREATE TABLE gauge_location (
204 id int PRIMARY KEY NOT NULL, 295 id int PRIMARY KEY NOT NULL,
205 river_id int REFERENCES rivers(id), 296 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
206 name VARCHAR(255), 297 name VARCHAR(255),
207 path VARCHAR(256) 298 path VARCHAR(256)
208 ); 299 );
209 SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2); 300 SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2);
210 ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ'); 301 ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ');
211 302
303
304 CREATE TABLE jetty_kinds(
305 id int PRIMARY KEY NOT NULL,
306 name VARCHAR(64)
307 );
308 INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf');
309 INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß');
310 INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel');
311
312 CREATE SEQUENCE JETTIES_ID_SEQ;
313 CREATE TABLE jetties (
314 id int PRIMARY KEY NOT NULL,
315 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
316 path VARCHAR(256),
317 kind_id int REFERENCES jetty_kinds(id),
318 km FLOAT8,
319 z FLOAT8
320 );
321 SELECT AddGeometryColumn('jetties','geom',31467,'POINT',2);
322 ALTER TABLE jetties ALTER COLUMN id SET DEFAULT NEXTVAL('JETTIES_ID_SEQ');
323
324
212 COMMIT; 325 COMMIT;
http://dive4elements.wald.intevation.org