Mercurial > dive4elements > river

comparison backend/doc/schema/postgresql-spatial.sql @ 5838:5aa05a7a34b7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Rename modules to more fitting names.
author Sascha L. Teichmann <teichmann@intevation.de>
date Thu, 25 Apr 2013 15:23:37 +0200
parents flys-backend/doc/schema/postgresql-spatial.sql@153456f84602
children 249b8766a178
comparison
equal deleted inserted replaced
5837:d9901a08d0a6 5838:5aa05a7a34b7
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, 'aktuelle Achse');
9 INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige');
10
11 -- Geodaesie/Flussachse+km/achse
12 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
13 CREATE TABLE river_axes (
14 id int PRIMARY KEY NOT NULL,
15 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
16 kind_id int REFERENCES axis_kinds(id) NOT NULL DEFAULT 0,
17 name VARCHAR(64),
18 path VARCHAR(256)
19 );
20 SELECT AddGeometryColumn('river_axes', 'geom', 31467, 'MULTILINESTRING', 2);
21 ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ');
22
23
24 -- TODO: TestMe.
25 -- Geodaesie/Flussachse+km/km.shp
26 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
27 CREATE TABLE river_axes_km (
28 id int PRIMARY KEY NOT NULL,
29 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
30 km FLOAT8 NOT NULL,
31 name VARCHAR(64),
32 path VARCHAR(256)
33 );
34 SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2);
35 ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ');
36
37
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, 'aktuelle Querprofilspuren');
45
46 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
47 CREATE TABLE cross_section_tracks (
48 id int PRIMARY KEY NOT NULL,
49 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
50 kind_id int REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0,
51 km FLOAT8 NOT NULL,
52 z FLOAT8 NOT NULL DEFAULT 0,
53 name VARCHAR(64),
54 path VARCHAR(256)
55 );
56 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, 'LINESTRING', 2);
57 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');
58
59 CREATE TABLE building_kinds(
60 id int PRIMARY KEY NOT NULL,
61 name VARCHAR(64)
62 );
63 INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige');
64 INSERT INTO building_kinds(id, name) VALUES (1, 'Brücken');
65 INSERT INTO building_kinds(id, name) VALUES (2, 'Wehre');
66 INSERT INTO building_kinds(id, name) VALUES (3, 'Pegel');
67
68 -- Geodaesie/Bauwerke
69 CREATE SEQUENCE BUILDINGS_ID_SEQ;
70 CREATE TABLE buildings (
71 id int PRIMARY KEY NOT NULL,
72 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
73 description VARCHAR(256), -- Name taken from attributes,
74 name VARCHAR(256), -- The layername
75 km FLOAT8,
76 kind_id int REFERENCES building_kinds(id) NOT NULL DEFAULT 0,
77 path VARCHAR(256)
78 );
79 SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2);
80 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ');
81
82
83 -- Geodaesie/Festpunkte/Festpunkte.shp
84 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
85 CREATE TABLE fixpoints (
86 id int PRIMARY KEY NOT NULL,
87 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
88 x FLOAT8,
89 y FLOAT8,
90 km FLOAT8 NOT NULL,
91 HPGP VARCHAR(64),
92 name VARCHAR(64),
93 path VARCHAR(256)
94 );
95 SELECT AddGeometryColumn('fixpoints', 'geom', 31467, 'POINT', 2);
96 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ');
97
98
99 -- Hydrologie/Hydr. Grenzen/talaue.shp
100 CREATE TABLE floodplain_kinds(
101 id int PRIMARY KEY NOT NULL,
102 name VARCHAR(64)
103 );
104 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige');
105 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'aktuelle Talaue');
106
107 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
108 CREATE TABLE floodplain (
109 id int PRIMARY KEY NOT NULL,
110 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
111 kind_id int REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0,
112 name VARCHAR(64),
113 path VARCHAR(256)
114 );
115 SELECT AddGeometryColumn('floodplain', 'geom', 31467, 'POLYGON', 2);
116 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');
117
118
119 -- Geodaesie/Hoehenmodelle/*
120 CREATE SEQUENCE DEM_ID_SEQ;
121 CREATE TABLE dem (
122 id int PRIMARY KEY NOT NULL,
123 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
124 name VARCHAR(64),
125 range_id INT REFERENCES ranges(id),
126 time_interval_id INT REFERENCES time_intervals(id),
127 projection VARCHAR(32),
128 srid int NOT NULL,
129 elevation_state VARCHAR(32),
130 format VARCHAR(32),
131 border_break BOOLEAN NOT NULL DEFAULT FALSE,
132 resolution VARCHAR(16),
133 description VARCHAR(256),
134 path VARCHAR(256) NOT NULL
135 );
136 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');
137
138
139 -- Static lookup tables for Hochwasserschutzanlagen
140 CREATE TABLE hws_kinds (
141 id int PRIMARY KEY NOT NULL,
142 kind VARCHAR(64) NOT NULL
143 );
144 INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass');
145 INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm');
146 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
147
148 CREATE TABLE fed_states (
149 id int PRIMARY KEY NOT NULL,
150 name VARCHAR(23) NOT NULL
151 );
152 INSERT INTO fed_states (id, name) VALUES (1, 'Bayern');
153 INSERT INTO fed_states (id, name) VALUES (2, 'Hessen');
154 INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen');
155 INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen');
156 INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz');
157 INSERT INTO fed_states (id, name) VALUES (6, 'Saarland');
158 INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein');
159 INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg');
160 INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern');
161 INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen');
162 INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg');
163 INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt');
164 INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen');
165 INSERT INTO fed_states (id, name) VALUES (14, 'Berlin');
166 INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
167 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
168
169 --Hydrologie/HW-Schutzanlagen/*Linien.shp
170 CREATE SEQUENCE HWS_LINES_ID_SEQ;
171 CREATE TABLE hws_lines (
172 id int PRIMARY KEY NOT NULL,
173 ogr_fid int,
174 kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
175 fed_state_id int REFERENCES fed_states(id),
176 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
177 name VARCHAR(256),
178 path VARCHAR(256),
179 official INT DEFAULT 0,
180 agency VARCHAR(256),
181 range VARCHAR(256),
182 shore_side INT DEFAULT 0,
183 source VARCHAR(256),
184 status_date TIMESTAMP,
185 description VARCHAR(256)
186 );
187 SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'MULTILINESTRING', 3);
188 -- TODO: dike_km_from dike_km_to, are they geometries?
189
190 ALTER TABLE hws_lines ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_LINES_ID_SEQ');
191
192 --Hydrologie/HW-Schutzanlagen/*Punkte.shp
193 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
194 CREATE TABLE hws_points (
195 id int PRIMARY KEY NOT NULL,
196 ogr_fid int,
197 kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
198 fed_state_id int REFERENCES fed_states(id),
199 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
200 name VARCHAR,
201 path VARCHAR,
202 official INT DEFAULT 0,
203 agency VARCHAR,
204 range VARCHAR,
205 shore_side INT DEFAULT 0,
206 source VARCHAR,
207 status_date VARCHAR,
208 description VARCHAR,
209 freeboard FLOAT8,
210 dike_km FLOAT8,
211 z FLOAT8,
212 z_target FLOAT8,
213 rated_level FLOAT8
214 );
215 SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2);
216
217 ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ');
218
219 --
220 --Hydrologie/UeSG
221 CREATE TABLE floodmap_kinds (
222 id int PRIMARY KEY NOT NULL,
223 name varchar(64) NOT NULL
224 );
225 INSERT INTO floodmap_kinds VALUES (200, 'Messung');
226 INSERT INTO floodmap_kinds VALUES (111, 'Berechnung-Aktuell-BfG');
227 INSERT INTO floodmap_kinds VALUES (112, 'Berechnung-Aktuell-Bundesländer');
228 INSERT INTO floodmap_kinds VALUES (121, 'Berechnung-Potenziell-BfG');
229 INSERT INTO floodmap_kinds VALUES (122, 'Berechnung-Potenziell-Bundesländer');
230
231 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
232 CREATE TABLE floodmaps (
233 id int PRIMARY KEY NOT NULL,
234 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
235 name varchar(64) NOT NULL,
236 kind int NOT NULL REFERENCES floodmap_kinds(id),
237 diff FLOAT8,
238 count int,
239 area FLOAT8,
240 perimeter FLOAT8,
241 path VARCHAR(256),
242 source varchar(64)
243 );
244 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2);
245 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom;
246 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text);
247 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ');
248
249 CREATE TABLE sectie_kinds (
250 id int PRIMARY KEY NOT NULL,
251 name VARCHAR(64) NOT NULL
252 );
253 INSERT INTO sectie_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
254 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Hauptgerinne');
255 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbereich');
256 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Vorland');
257
258 CREATE TABLE sobek_kinds (
259 id int PRIMARY KEY NOT NULL,
260 name VARCHAR(64) NOT NULL
261 );
262 INSERT INTO sobek_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
263 INSERT INTO sobek_kinds (id, name) VALUES (1, 'durchströmt');
264 INSERT INTO sobek_kinds (id, name) VALUES (2, 'nicht durchströmt');
265
266 CREATE TABLE boundary_kinds (
267 id int PRIMARY KEY NOT NULL,
268 name VARCHAR(64) NOT NULL
269 );
270 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
271 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
272 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
273 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
274
275 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
276 CREATE TABLE hydr_boundaries (
277 id int PRIMARY KEY NOT NULL,
278 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
279 name VARCHAR(255),
280 kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0,
281 sectie int REFERENCES sectie_kinds(id),
282 sobek int REFERENCES sobek_kinds(id),
283 path VARCHAR(256)
284 );
285 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'MULTILINESTRING',3);
286 ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ');
287
288
289 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
290 CREATE TABLE hydr_boundaries_poly (
291 id int PRIMARY KEY NOT NULL,
292 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
293 name VARCHAR(255),
294 kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0,
295 sectie int REFERENCES sectie_kinds(id),
296 sobek int REFERENCES sobek_kinds(id),
297 path VARCHAR(256)
298 );
299 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'MULTIPOLYGON',3);
300 ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ');
301
302
303 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
304 CREATE TABLE gauge_location (
305 id int PRIMARY KEY NOT NULL,
306 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
307 name VARCHAR(255),
308 path VARCHAR(256)
309 );
310 SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2);
311 ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ');
312
313
314 CREATE TABLE jetty_kinds(
315 id int PRIMARY KEY NOT NULL,
316 name VARCHAR(64)
317 );
318 INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf');
319 INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß');
320 INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel');
321
322 CREATE SEQUENCE JETTIES_ID_SEQ;
323 CREATE TABLE jetties (
324 id int PRIMARY KEY NOT NULL,
325 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
326 path VARCHAR(256),
327 kind_id int REFERENCES jetty_kinds(id),
328 km FLOAT8,
329 z FLOAT8
330 );
331 SELECT AddGeometryColumn('jetties','geom',31467,'POINT',2);
332 ALTER TABLE jetties ALTER COLUMN id SET DEFAULT NEXTVAL('JETTIES_ID_SEQ');
333
334 CREATE SEQUENCE FLOOD_MARKS_ID_SEQ;
335 CREATE TABLE flood_marks (
336 id int PRIMARY KEY NOT NULL,
337 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
338 path VARCHAR(256),
339 km FLOAT8,
340 z FLOAT8,
341 location VARCHAR(64),
342 year int
343 );
344 SELECT AddGeometryColumn('flood_marks','geom',31467,'POINT',2);
345 ALTER TABLE flood_marks ALTER COLUMN id SET DEFAULT NEXTVAL('FLOOD_MARKS_ID_SEQ');
346
347 COMMIT;
http://dive4elements.wald.intevation.org