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