Mercurial > dive4elements > river

annotate backend/doc/schema/postgresql-spatial.sql @ 9199:d177121fc95d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Ignore contents of folder shapefiles, because it contains runtime data
author gernotbelger
date Fri, 29 Jun 2018 16:15:43 +0200
parents f89fb9e9abad
children
rev   line source
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
1 BEGIN;
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
2
5146
d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents: 5137
diff changeset
3 CREATE TABLE axis_kinds(
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
4 id int PRIMARY KEY NOT NULL,
5146
d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents: 5137
diff changeset
5 name VARCHAR(64)
d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents: 5137
diff changeset
6 );
d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents: 5137
diff changeset
7 INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt');
5495
1488227b97b7 Minor scheme change. Update kind names to their UI representation.
Andre Heinecke <aheinecke@intevation.de>
parents: 5409
diff changeset
8 INSERT INTO axis_kinds(id, name) VALUES (1, 'aktuelle Achse');
5146
d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents: 5137
diff changeset
9 INSERT INTO axis_kinds(id, name) VALUES (2, 'Sonstige');
d4fdd98a04f7 Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents: 5137
diff changeset
10
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
11 -- Geodaesie/Flussachse+km/achse
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
12 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
13 CREATE TABLE river_axes (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
14 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
15 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
16 kind_id int REFERENCES axis_kinds(id) NOT NULL DEFAULT 0,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
17 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
18 path VARCHAR(256)
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
19 );
5137
04eb62eae722 River Axes and HWS_lines are now multiline
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
20 SELECT AddGeometryColumn('river_axes', 'geom', 31467, 'MULTILINESTRING', 2);
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
21 ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ');
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
22
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
23
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
24 -- TODO: TestMe.
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
25 -- Geodaesie/Flussachse+km/km.shp
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
26 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
27 CREATE TABLE river_axes_km (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
28 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
29 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
30 km FLOAT8 NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
31 fedstate_km FLOAT8,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
32 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
33 path VARCHAR(256)
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
34 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
35 SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2);
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
36 ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ');
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
37
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
38
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
39 --Geodaesie/Querprofile/QP-Spuren/qps.shp
5214
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
40 CREATE TABLE cross_section_track_kinds(
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
41 id int PRIMARY KEY NOT NULL,
5214
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
42 name VARCHAR(64)
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
43 );
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
44 INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige');
5495
1488227b97b7 Minor scheme change. Update kind names to their UI representation.
Andre Heinecke <aheinecke@intevation.de>
parents: 5409
diff changeset
45 INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'aktuelle Querprofilspuren');
5214
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
46
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
47 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
48 CREATE TABLE cross_section_tracks (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
49 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
50 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
51 kind_id int REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
52 km FLOAT8 NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
53 z FLOAT8 NOT NULL DEFAULT 0,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
54 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
55 path VARCHAR(256)
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
56 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
57 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, 'LINESTRING', 2);
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
58 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
59
5385
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5363
diff changeset
60 CREATE TABLE building_kinds(
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
61 id int PRIMARY KEY NOT NULL,
5385
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5363
diff changeset
62 name VARCHAR(64)
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5363
diff changeset
63 );
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5363
diff changeset
64 INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige');
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5363
diff changeset
65 INSERT INTO building_kinds(id, name) VALUES (1, 'Brücken');
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5363
diff changeset
66 INSERT INTO building_kinds(id, name) VALUES (2, 'Wehre');
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5363
diff changeset
67 INSERT INTO building_kinds(id, name) VALUES (3, 'Pegel');
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
68
5385
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5363
diff changeset
69 -- Geodaesie/Bauwerke
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
70 CREATE SEQUENCE BUILDINGS_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
71 CREATE TABLE buildings (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
72 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
73 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
5391
e65fee5ea98a Rename station to km to be in line with the rest
Andre Heinecke <aheinecke@intevation.de>
parents: 5385
diff changeset
74 description VARCHAR(256), -- Name taken from attributes,
5385
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5363
diff changeset
75 name VARCHAR(256), -- The layername
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
76 km FLOAT8,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
77 kind_id int REFERENCES building_kinds(id) NOT NULL DEFAULT 0,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
78 path VARCHAR(256)
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
79 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
80 SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2);
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
81 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ');
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
82
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
83
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
84 -- Geodaesie/Festpunkte/Festpunkte.shp
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
85 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
86 CREATE TABLE fixpoints (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
87 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
88 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
89 x FLOAT8,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
90 y FLOAT8,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
91 km FLOAT8 NOT NULL,
5623
0aaed46a88be Schema change: Allow longer strings in fixpoints.hpgp
Tom Gottfried <tom@intevation.de>
parents: 5514
diff changeset
92 HPGP VARCHAR(64),
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
93 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
94 path VARCHAR(256)
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
95 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
96 SELECT AddGeometryColumn('fixpoints', 'geom', 31467, 'POINT', 2);
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
97 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ');
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
98
1236
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
99
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
100 -- Hydrologie/Hydr. Grenzen/talaue.shp
5214
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
101 CREATE TABLE floodplain_kinds(
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
102 id int PRIMARY KEY NOT NULL,
5214
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
103 name VARCHAR(64)
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
104 );
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
105 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige');
5495
1488227b97b7 Minor scheme change. Update kind names to their UI representation.
Andre Heinecke <aheinecke@intevation.de>
parents: 5409
diff changeset
106 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'aktuelle Talaue');
5214
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
107
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
108 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
1237
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
109 CREATE TABLE floodplain (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
110 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
111 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
112 kind_id int REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
113 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
114 path VARCHAR(256)
1237
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
115 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
116 SELECT AddGeometryColumn('floodplain', 'geom', 31467, 'POLYGON', 2);
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
117 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');
1237
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
118
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
119
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
120 -- Geodaesie/Hoehenmodelle/*
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
121 CREATE SEQUENCE DEM_ID_SEQ;
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
122 CREATE TABLE dem (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
123 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
124 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5182
diff changeset
125 name VARCHAR(64),
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
126 range_id INT REFERENCES ranges(id) ON DELETE CASCADE,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
127 time_interval_id INT REFERENCES time_intervals(id),
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5182
diff changeset
128 projection VARCHAR(32),
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
129 srid int NOT NULL,
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5182
diff changeset
130 elevation_state VARCHAR(32),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5182
diff changeset
131 format VARCHAR(32),
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
132 border_break BOOLEAN NOT NULL DEFAULT FALSE,
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5182
diff changeset
133 resolution VARCHAR(16),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5182
diff changeset
134 description VARCHAR(256),
5315
aa407dfb9949 removed unique constraint to allow one dem to be used by several rivers
Tom Gottfried <tom@intevation.de>
parents: 5303
diff changeset
135 path VARCHAR(256) NOT NULL
1236
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
136 );
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
137 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');
1236
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
138
1238
35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings
Hans Plum <hans.plum@intevation.de>
parents: 1237
diff changeset
139
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
140 -- Static lookup tables for Hochwasserschutzanlagen
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
141 CREATE TABLE hws_kinds (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
142 id int PRIMARY KEY NOT NULL,
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
143 kind VARCHAR(64) NOT NULL
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
144 );
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
145 INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
146 INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
147 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
148
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
149 CREATE TABLE fed_states (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
150 id int PRIMARY KEY NOT NULL,
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
151 name VARCHAR(23) NOT NULL
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
152 );
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
153 INSERT INTO fed_states (id, name) VALUES (1, 'Bayern');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
154 INSERT INTO fed_states (id, name) VALUES (2, 'Hessen');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
155 INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
156 INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
157 INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
158 INSERT INTO fed_states (id, name) VALUES (6, 'Saarland');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
159 INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
160 INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
161 INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
162 INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
163 INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
164 INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
165 INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
166 INSERT INTO fed_states (id, name) VALUES (14, 'Berlin');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
167 INSERT INTO fed_states (id, name) VALUES (15, 'Bremen');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
168 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
169
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
170 --Hydrologie/HW-Schutzanlagen/*Linien.shp
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
171 CREATE SEQUENCE HWS_LINES_ID_SEQ;
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
172 CREATE TABLE hws_lines (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
173 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
174 ogr_fid int,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
175 kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
176 fed_state_id int REFERENCES fed_states(id),
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
177 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
178 name VARCHAR(256),
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
179 path VARCHAR(256),
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
180 official INT DEFAULT 0,
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
181 agency VARCHAR(256),
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
182 range VARCHAR(256),
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
183 shore_side INT DEFAULT 0,
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
184 source VARCHAR(256),
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
185 status_date TIMESTAMP,
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
186 description VARCHAR(256)
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
187 );
5137
04eb62eae722 River Axes and HWS_lines are now multiline
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
188 SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'MULTILINESTRING', 3);
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
189 -- TODO: dike_km_from dike_km_to, are they geometries?
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
190
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
191 ALTER TABLE hws_lines ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_LINES_ID_SEQ');
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
192
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
193 --Hydrologie/HW-Schutzanlagen/*Punkte.shp
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
194 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
195 CREATE TABLE hws_points (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
196 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
197 ogr_fid int,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
198 kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
199 fed_state_id int REFERENCES fed_states(id),
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
200 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
201 name VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
202 path VARCHAR,
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
203 official INT DEFAULT 0,
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
204 agency VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
205 range VARCHAR,
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
206 shore_side INT DEFAULT 0,
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
207 source VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
208 status_date VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
209 description VARCHAR,
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
210 freeboard FLOAT8,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
211 dike_km FLOAT8,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
212 z FLOAT8,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
213 z_target FLOAT8,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
214 rated_level FLOAT8
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
215 );
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
216 SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2);
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
217
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
218 ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ');
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
219
2797
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
220 --
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
221 --Hydrologie/UeSG
5276
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5214
diff changeset
222 CREATE TABLE floodmap_kinds (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
223 id int PRIMARY KEY NOT NULL,
5276
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5214
diff changeset
224 name varchar(64) NOT NULL
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5214
diff changeset
225 );
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5214
diff changeset
226 INSERT INTO floodmap_kinds VALUES (200, 'Messung');
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5214
diff changeset
227 INSERT INTO floodmap_kinds VALUES (111, 'Berechnung-Aktuell-BfG');
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5214
diff changeset
228 INSERT INTO floodmap_kinds VALUES (112, 'Berechnung-Aktuell-Bundesländer');
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5214
diff changeset
229 INSERT INTO floodmap_kinds VALUES (121, 'Berechnung-Potenziell-BfG');
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5214
diff changeset
230 INSERT INTO floodmap_kinds VALUES (122, 'Berechnung-Potenziell-Bundesländer');
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5214
diff changeset
231
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
232 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
233 CREATE TABLE floodmaps (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
234 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
235 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
2797
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
236 name varchar(64) NOT NULL,
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
237 kind int NOT NULL REFERENCES floodmap_kinds(id),
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
238 diff FLOAT8,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
239 count int,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
240 area FLOAT8,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
241 perimeter FLOAT8,
5854
249b8766a178 Schema change: new column 'waterbody' for floodmaps and importer
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5838
diff changeset
242 waterbody varchar(64),
5276
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5214
diff changeset
243 path VARCHAR(256),
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5214
diff changeset
244 source varchar(64)
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
245 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
246 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2);
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
247 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ');
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
248
4999
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
249 CREATE TABLE sectie_kinds (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
250 id int PRIMARY KEY NOT NULL,
4999
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
251 name VARCHAR(64) NOT NULL
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
252 );
5748
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
253 INSERT INTO sectie_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
254 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Hauptgerinne');
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
255 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbereich');
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
256 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Vorland');
4999
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
257
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
258 CREATE TABLE sobek_kinds (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
259 id int PRIMARY KEY NOT NULL,
4999
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
260 name VARCHAR(64) NOT NULL
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
261 );
5748
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
262 INSERT INTO sobek_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
263 INSERT INTO sobek_kinds (id, name) VALUES (1, 'durchströmt');
b5553164fabf Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5623
diff changeset
264 INSERT INTO sobek_kinds (id, name) VALUES (2, 'nicht durchströmt');
4999
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
265
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
266 CREATE TABLE boundary_kinds (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
267 id int PRIMARY KEY NOT NULL,
4999
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
268 name VARCHAR(64) NOT NULL
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
269 );
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
270 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
271 INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG');
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
272 INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land');
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
273 INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige');
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
274
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
275 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
276 CREATE TABLE hydr_boundaries (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
277 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
278 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
279 name VARCHAR(255),
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
280 kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
281 sectie int REFERENCES sectie_kinds(id),
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
282 sobek int REFERENCES sobek_kinds(id),
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
283 path VARCHAR(256)
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
284 );
5182
8aac391871f9 SCHEME CHANGE: Hydr.Boundaries are now three dimensional multilinestrings
Andre Heinecke <aheinecke@intevation.de>
parents: 5158
diff changeset
285 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'MULTILINESTRING',3);
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
286 ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ');
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
287
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
288
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
289 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
290 CREATE TABLE hydr_boundaries_poly (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
291 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
292 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
293 name VARCHAR(255),
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
294 kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
295 sectie int REFERENCES sectie_kinds(id),
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
296 sobek int REFERENCES sobek_kinds(id),
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
297 path VARCHAR(256)
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
298 );
5303
08b3109c637b fixed spatial schema for hydraulic boundaries
Tom Gottfried <tom@intevation.de>
parents: 5292
diff changeset
299 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'MULTIPOLYGON',3);
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
300 ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ');
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
301
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
302
5291
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
303 CREATE TABLE jetty_kinds(
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
304 id int PRIMARY KEY NOT NULL,
5291
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
305 name VARCHAR(64)
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
306 );
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
307 INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf');
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
308 INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß');
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
309 INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel');
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
310
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
311 CREATE SEQUENCE JETTIES_ID_SEQ;
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
312 CREATE TABLE jetties (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
313 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
314 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
5291
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
315 path VARCHAR(256),
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
316 kind_id int REFERENCES jetty_kinds(id),
5291
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
317 km FLOAT8,
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
318 z FLOAT8
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
319 );
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
320 SELECT AddGeometryColumn('jetties','geom',31467,'POINT',2);
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
321 ALTER TABLE jetties ALTER COLUMN id SET DEFAULT NEXTVAL('JETTIES_ID_SEQ');
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
322
5409
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5391
diff changeset
323 CREATE SEQUENCE FLOOD_MARKS_ID_SEQ;
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5391
diff changeset
324 CREATE TABLE flood_marks (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
325 id int PRIMARY KEY NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
326 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
5409
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5391
diff changeset
327 path VARCHAR(256),
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
328 km FLOAT8,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
329 z FLOAT8,
5409
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5391
diff changeset
330 location VARCHAR(64),
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
331 year int
5409
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5391
diff changeset
332 );
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5391
diff changeset
333 SELECT AddGeometryColumn('flood_marks','geom',31467,'POINT',2);
8b29a94ebdd8 SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents: 5391
diff changeset
334 ALTER TABLE flood_marks ALTER COLUMN id SET DEFAULT NEXTVAL('FLOOD_MARKS_ID_SEQ');
5291
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
335
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
336 COMMIT;
http://dive4elements.wald.intevation.org