Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 4991:b79eb203032d dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
author Tom Gottfried <tom.gottfried@intevation.de>
date Thu, 14 Feb 2013 17:36:10 +0100
parents a72e90fc45ed
children ecc6fd57b630
comparison
equal deleted inserted replaced
4989:f4d61ac84b76 4991:b79eb203032d
2 2
3 -- Geodaesie/Flussachse+km/achse 3 -- Geodaesie/Flussachse+km/achse
4 CREATE SEQUENCE RIVER_AXES_ID_SEQ; 4 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
5 CREATE TABLE river_axes ( 5 CREATE TABLE river_axes (
6 id int PRIMARY KEY NOT NULL, 6 id int PRIMARY KEY NOT NULL,
7 river_id int REFERENCES rivers(id), 7 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
8 kind int NOT NULL DEFAULT 0, 8 kind int NOT NULL DEFAULT 0,
9 name VARCHAR(64), 9 name VARCHAR(64),
10 path VARCHAR(256) 10 path VARCHAR(256)
11 ); 11 );
12 SELECT AddGeometryColumn('river_axes', 'geom', 31467, 'LINESTRING', 2); 12 SELECT AddGeometryColumn('river_axes', 'geom', 31467, 'LINESTRING', 2);
16 -- TODO: TestMe. 16 -- TODO: TestMe.
17 -- Geodaesie/Flussachse+km/km.shp 17 -- Geodaesie/Flussachse+km/km.shp
18 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; 18 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
19 CREATE TABLE river_axes_km ( 19 CREATE TABLE river_axes_km (
20 id int PRIMARY KEY NOT NULL, 20 id int PRIMARY KEY NOT NULL,
21 river_id int REFERENCES rivers(id), 21 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
22 km FLOAT8 NOT NULL, 22 km FLOAT8 NOT NULL,
23 name VARCHAR(64), 23 name VARCHAR(64),
24 path VARCHAR(256) 24 path VARCHAR(256)
25 ); 25 );
26 SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2); 26 SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2);
29 29
30 --Geodaesie/Querprofile/QP-Spuren/qps.shp 30 --Geodaesie/Querprofile/QP-Spuren/qps.shp
31 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; 31 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
32 CREATE TABLE cross_section_tracks ( 32 CREATE TABLE cross_section_tracks (
33 id int PRIMARY KEY NOT NULL, 33 id int PRIMARY KEY NOT NULL,
34 river_id int REFERENCES rivers(id), 34 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
35 km FLOAT8 NOT NULL, 35 km FLOAT8 NOT NULL,
36 z FLOAT8 NOT NULL DEFAULT 0, 36 z FLOAT8 NOT NULL DEFAULT 0,
37 name VARCHAR(64), 37 name VARCHAR(64),
38 path VARCHAR(256) 38 path VARCHAR(256)
39 ); 39 );
43 43
44 -- Geodaesie/Linien/rohre-und-spreen 44 -- Geodaesie/Linien/rohre-und-spreen
45 CREATE SEQUENCE LINES_ID_SEQ; 45 CREATE SEQUENCE LINES_ID_SEQ;
46 CREATE TABLE lines ( 46 CREATE TABLE lines (
47 id int PRIMARY KEY NOT NULL, 47 id int PRIMARY KEY NOT NULL,
48 river_id int REFERENCES rivers(id), 48 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
49 kind VARCHAR(16) NOT NULL, 49 kind VARCHAR(16) NOT NULL,
50 z FLOAT8 DEFAULT 0, 50 z FLOAT8 DEFAULT 0,
51 name VARCHAR(64), 51 name VARCHAR(64),
52 path VARCHAR(256) 52 path VARCHAR(256)
53 ); 53 );
60 60
61 -- Geodaesie/Bauwerke/Wehre.shp 61 -- Geodaesie/Bauwerke/Wehre.shp
62 CREATE SEQUENCE BUILDINGS_ID_SEQ; 62 CREATE SEQUENCE BUILDINGS_ID_SEQ;
63 CREATE TABLE buildings ( 63 CREATE TABLE buildings (
64 id int PRIMARY KEY NOT NULL, 64 id int PRIMARY KEY NOT NULL,
65 river_id int REFERENCES rivers(id), 65 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
66 name VARCHAR(256), 66 name VARCHAR(256),
67 path VARCHAR(256) 67 path VARCHAR(256)
68 ); 68 );
69 SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2); 69 SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2);
70 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ'); 70 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ');
72 72
73 -- Geodaesie/Festpunkte/Festpunkte.shp 73 -- Geodaesie/Festpunkte/Festpunkte.shp
74 CREATE SEQUENCE FIXPOINTS_ID_SEQ; 74 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
75 CREATE TABLE fixpoints ( 75 CREATE TABLE fixpoints (
76 id int PRIMARY KEY NOT NULL, 76 id int PRIMARY KEY NOT NULL,
77 river_id int REFERENCES rivers(id), 77 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
78 x FLOAT8, 78 x FLOAT8,
79 y FLOAT8, 79 y FLOAT8,
80 km FLOAT8 NOT NULL, 80 km FLOAT8 NOT NULL,
81 HPGP VARCHAR(2), 81 HPGP VARCHAR(2),
82 name VARCHAR(64), 82 name VARCHAR(64),
88 88
89 -- Hydrologie/Hydr. Grenzen/talaue.shp 89 -- Hydrologie/Hydr. Grenzen/talaue.shp
90 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; 90 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
91 CREATE TABLE floodplain ( 91 CREATE TABLE floodplain (
92 id int PRIMARY KEY NOT NULL, 92 id int PRIMARY KEY NOT NULL,
93 river_id int REFERENCES rivers(id), 93 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
94 name VARCHAR(64), 94 name VARCHAR(64),
95 path VARCHAR(256) 95 path VARCHAR(256)
96 ); 96 );
97 SELECT AddGeometryColumn('floodplain', 'geom', 31467, 'POLYGON', 2); 97 SELECT AddGeometryColumn('floodplain', 'geom', 31467, 'POLYGON', 2);
98 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ'); 98 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');
100 100
101 -- Geodaesie/Hoehenmodelle/* 101 -- Geodaesie/Hoehenmodelle/*
102 CREATE SEQUENCE DEM_ID_SEQ; 102 CREATE SEQUENCE DEM_ID_SEQ;
103 CREATE TABLE dem ( 103 CREATE TABLE dem (
104 id int PRIMARY KEY NOT NULL, 104 id int PRIMARY KEY NOT NULL,
105 river_id int REFERENCES rivers(id), 105 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
106 -- XXX Should we use the ranges table instead? 106 -- XXX Should we use the ranges table instead?
107 name VARCHAR(64), 107 name VARCHAR(64),
108 lower FLOAT8, 108 lower FLOAT8,
109 upper FLOAT8, 109 upper FLOAT8,
110 year_from VARCHAR(32) NOT NULL, 110 year_from VARCHAR(32) NOT NULL,
122 122
123 -- Hydrologie/Einzugsgebiete/EZG.shp 123 -- Hydrologie/Einzugsgebiete/EZG.shp
124 CREATE SEQUENCE CATCHMENT_ID_SEQ; 124 CREATE SEQUENCE CATCHMENT_ID_SEQ;
125 CREATE TABLE catchment ( 125 CREATE TABLE catchment (
126 id int PRIMARY KEY NOT NULL, 126 id int PRIMARY KEY NOT NULL,
127 river_id int REFERENCES rivers(id), 127 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
128 area FLOAT8, 128 area FLOAT8,
129 name VARCHAR(256), 129 name VARCHAR(256),
130 path VARCHAR(256) 130 path VARCHAR(256)
131 ); 131 );
132 SELECT AddGeometryColumn('catchment','geom',31467,'POLYGON',2); 132 SELECT AddGeometryColumn('catchment','geom',31467,'POLYGON',2);
167 CREATE TABLE hws_lines ( 167 CREATE TABLE hws_lines (
168 id int PRIMARY KEY NOT NULL, 168 id int PRIMARY KEY NOT NULL,
169 ogr_fid int, 169 ogr_fid int,
170 kind_id int REFERENCES hws_kinds(id) DEFAULT 2, 170 kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
171 fed_state_id int REFERENCES fed_states(id), 171 fed_state_id int REFERENCES fed_states(id),
172 river_id int REFERENCES rivers(id), 172 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
173 name VARCHAR(256), 173 name VARCHAR(256),
174 path VARCHAR(256), 174 path VARCHAR(256),
175 offical INT DEFAULT 0, 175 offical INT DEFAULT 0,
176 agency VARCHAR(256), 176 agency VARCHAR(256),
177 range VARCHAR(256), 177 range VARCHAR(256),
192 CREATE TABLE hws_points ( 192 CREATE TABLE hws_points (
193 id int PRIMARY KEY NOT NULL, 193 id int PRIMARY KEY NOT NULL,
194 ogr_fid int, 194 ogr_fid int,
195 kind_id int REFERENCES hws_kinds(id) DEFAULT 2, 195 kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
196 fed_state_id int REFERENCES fed_states(id), 196 fed_state_id int REFERENCES fed_states(id),
197 river_id int REFERENCES rivers(id), 197 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
198 name VARCHAR, 198 name VARCHAR,
199 path VARCHAR, 199 path VARCHAR,
200 offical INT DEFAULT 0, 200 offical INT DEFAULT 0,
201 agency VARCHAR, 201 agency VARCHAR,
202 range VARCHAR, 202 range VARCHAR,
225 -- 122 = Berechnung->Potenziell->Land 225 -- 122 = Berechnung->Potenziell->Land
226 -- 226 --
227 CREATE SEQUENCE FLOODMAPS_ID_SEQ; 227 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
228 CREATE TABLE floodmaps ( 228 CREATE TABLE floodmaps (
229 id int PRIMARY KEY NOT NULL, 229 id int PRIMARY KEY NOT NULL,
230 river_id int REFERENCES rivers(id), 230 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
231 name varchar(64) NOT NULL, 231 name varchar(64) NOT NULL,
232 kind int NOT NULL, 232 kind int NOT NULL,
233 diff FLOAT8, 233 diff FLOAT8,
234 count int, 234 count int,
235 area FLOAT8, 235 area FLOAT8,
242 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ'); 242 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ');
243 243
244 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; 244 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
245 CREATE TABLE hydr_boundaries ( 245 CREATE TABLE hydr_boundaries (
246 id int PRIMARY KEY NOT NULL, 246 id int PRIMARY KEY NOT NULL,
247 river_id int REFERENCES rivers(id), 247 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
248 name VARCHAR(255), 248 name VARCHAR(255),
249 kind int, -- 1 BfG / 2 Land / 3 Sonstige 249 kind int, -- 1 BfG / 2 Land / 3 Sonstige
250 sectie int, -- 1 stromführend / 2 stromspeichernd 250 sectie int, -- 1 stromführend / 2 stromspeichernd
251 sobek int, -- 1 Flussschlauch / 2 Uferbank / 3 Überflutungsbereich 251 sobek int, -- 1 Flussschlauch / 2 Uferbank / 3 Überflutungsbereich
252 path VARCHAR(256) 252 path VARCHAR(256)
256 256
257 257
258 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; 258 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
259 CREATE TABLE hydr_boundaries_poly ( 259 CREATE TABLE hydr_boundaries_poly (
260 id int PRIMARY KEY NOT NULL, 260 id int PRIMARY KEY NOT NULL,
261 river_id int REFERENCES rivers(id), 261 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
262 name VARCHAR(255), 262 name VARCHAR(255),
263 kind int, -- 1 BfG / 2 Land / 3 Sonstige 263 kind int, -- 1 BfG / 2 Land / 3 Sonstige
264 sectie int, -- 1 Flussschlauch / 2 Uferbank / 3 Überflutungsbereich 264 sectie int, -- 1 Flussschlauch / 2 Uferbank / 3 Überflutungsbereich
265 sobek int, -- 1 stromführend / 2 stromspeichernd 265 sobek int, -- 1 stromführend / 2 stromspeichernd
266 path VARCHAR(256) 266 path VARCHAR(256)
270 270
271 271
272 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ; 272 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
273 CREATE TABLE gauge_location ( 273 CREATE TABLE gauge_location (
274 id int PRIMARY KEY NOT NULL, 274 id int PRIMARY KEY NOT NULL,
275 river_id int REFERENCES rivers(id), 275 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
276 name VARCHAR(255), 276 name VARCHAR(255),
277 path VARCHAR(256) 277 path VARCHAR(256)
278 ); 278 );
279 SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2); 279 SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2);
280 ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ'); 280 ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ');
http://dive4elements.wald.intevation.org