Mercurial > dive4elements > river
annotate backend/doc/schema/postgresql-spatial.sql @ 8008:80e4aa6e9936 facet-metadata
Allow differences between years and epochs.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 03 Jul 2014 16:34:28 +0200 |
parents | f4e32ac4e55c |
children | cc53aae06303 |
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( |
d4fdd98a04f7
Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents:
5137
diff
changeset
|
4 id int PRIMARY KEY NOT NULL, |
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 ( |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
14 id int PRIMARY KEY NOT NULL, |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4953
diff
changeset
|
15 river_id int REFERENCES rivers(id) ON DELETE CASCADE, |
5146
d4fdd98a04f7
Add kind_id to river_axes table and add AxisKind object to model
Andre Heinecke <aheinecke@intevation.de>
parents:
5137
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 ( |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
28 id int PRIMARY KEY NOT NULL, |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4953
diff
changeset
|
29 river_id int REFERENCES rivers(id) ON DELETE CASCADE, |
4931
f5c55d7ea07e
SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents:
4930
diff
changeset
|
30 km FLOAT8 NOT NULL, |
5886
6a63456b99d9
Schema change, importer, i18n: additional field for federal state stations
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5854
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( |
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
41 id int PRIMARY KEY NOT NULL, |
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 ( |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
49 id int PRIMARY KEY NOT NULL, |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4953
diff
changeset
|
50 river_id int REFERENCES rivers(id) ON DELETE CASCADE, |
5214
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
51 kind_id int REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0, |
4931
f5c55d7ea07e
SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents:
4930
diff
changeset
|
52 km FLOAT8 NOT NULL, |
f5c55d7ea07e
SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents:
4930
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( |
2da74705c29d
Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents:
5363
diff
changeset
|
61 id int PRIMARY KEY NOT NULL, |
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 ( |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
72 id int PRIMARY KEY NOT NULL, |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4953
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 |
5391
e65fee5ea98a
Rename station to km to be in line with the rest
Andre Heinecke <aheinecke@intevation.de>
parents:
5385
diff
changeset
|
76 km FLOAT8, |
5385
2da74705c29d
Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents:
5363
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 ( |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
87 id int PRIMARY KEY NOT NULL, |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4953
diff
changeset
|
88 river_id int REFERENCES rivers(id) ON DELETE CASCADE, |
4931
f5c55d7ea07e
SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents:
4930
diff
changeset
|
89 x FLOAT8, |
f5c55d7ea07e
SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents:
4930
diff
changeset
|
90 y FLOAT8, |
f5c55d7ea07e
SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents:
4930
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( |
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
diff
changeset
|
102 id int PRIMARY KEY NOT NULL, |
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 ( |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
110 id int PRIMARY KEY NOT NULL, |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4953
diff
changeset
|
111 river_id int REFERENCES rivers(id) ON DELETE CASCADE, |
5214
b46df0609276
add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents:
5210
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 ( |
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
123 id int PRIMARY KEY NOT NULL, |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4953
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), |
7001
dcc7a6c8b92c
Minor schema change: allow to delete ranges with associated DEMs.
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5886
diff
changeset
|
126 range_id INT REFERENCES ranges(id) ON DELETE CASCADE, |
5291
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
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), |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5748
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), |
908848b74d7e
SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents:
5182
diff
changeset
|
132 border_break BOOLEAN NOT NULL DEFAULT FALSE, |
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 ( |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
142 id int PRIMARY KEY NOT NULL, |
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 ( |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1243
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 ( |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
173 id int PRIMARY KEY NOT NULL, |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
174 ogr_fid int, |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
175 kind_id int REFERENCES hws_kinds(id) DEFAULT 2, |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
176 fed_state_id int REFERENCES fed_states(id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4953
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), |
5010
ee52faa6b7ac
Corrected typo in field name
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4999
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), |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
183 shore_side INT DEFAULT 0, |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
184 source VARCHAR(256), |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
185 status_date TIMESTAMP, |
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 ( |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
196 id int PRIMARY KEY NOT NULL, |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
197 ogr_fid int, |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
198 kind_id int REFERENCES hws_kinds(id) DEFAULT 2, |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
199 fed_state_id int REFERENCES fed_states(id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4953
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, |
5010
ee52faa6b7ac
Corrected typo in field name
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4999
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, |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
206 shore_side INT DEFAULT 0, |
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, |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
210 freeboard FLOAT8, |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
211 dike_km FLOAT8, |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
212 z FLOAT8, |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
213 z_target FLOAT8, |
28f992c0f937
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents:
3961
diff
changeset
|
214 rated_level FLOAT8 |
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 ( |
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
|
223 id int PRIMARY KEY 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
|
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 ( |
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2376
diff
changeset
|
234 id int PRIMARY KEY NOT NULL, |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4953
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, |
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
|
237 kind int NOT NULL REFERENCES floodmap_kinds(id), |
4931
f5c55d7ea07e
SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents:
4930
diff
changeset
|
238 diff FLOAT8, |
2796
f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2376
diff
changeset
|
239 count int, |
4931
f5c55d7ea07e
SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents:
4930
diff
changeset
|
240 area FLOAT8, |
f5c55d7ea07e
SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents:
4930
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 ( |
929ec3ed7dc2
SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents:
4992
diff
changeset
|
250 id int PRIMARY KEY NOT NULL, |
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 ( |
929ec3ed7dc2
SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents:
4992
diff
changeset
|
259 id int PRIMARY KEY NOT NULL, |
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 ( |
929ec3ed7dc2
SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents:
4992
diff
changeset
|
267 id int PRIMARY KEY NOT NULL, |
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 ( |
883dd49d5e74
Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2797
diff
changeset
|
277 id int PRIMARY KEY NOT NULL, |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4953
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), |
5513
ef3517905e6e
Add not null and default for hydr_boundary classifiers
Andre Heinecke <aheinecke@intevation.de>
parents:
5495
diff
changeset
|
280 kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, |
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
|
281 sectie int REFERENCES sectie_kinds(id), |
b5553164fabf
Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5623
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 ( |
883dd49d5e74
Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2797
diff
changeset
|
291 id int PRIMARY KEY NOT NULL, |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4953
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), |
5513
ef3517905e6e
Add not null and default for hydr_boundary classifiers
Andre Heinecke <aheinecke@intevation.de>
parents:
5495
diff
changeset
|
294 kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, |
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
|
295 sectie int REFERENCES sectie_kinds(id), |
b5553164fabf
Schema change: allow SECTIE and STROVOER (alias SOBEK) to be NULL, rename kinds
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5623
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 |
883dd49d5e74
Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2797
diff
changeset
|
303 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ; |
883dd49d5e74
Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2797
diff
changeset
|
304 CREATE TABLE gauge_location ( |
883dd49d5e74
Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2797
diff
changeset
|
305 id int PRIMARY KEY NOT NULL, |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4953
diff
changeset
|
306 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
|
307 name VARCHAR(255), |
883dd49d5e74
Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2797
diff
changeset
|
308 path VARCHAR(256) |
883dd49d5e74
Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2797
diff
changeset
|
309 ); |
3950
82e931f88137
Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
3948
diff
changeset
|
310 SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2); |
3948
883dd49d5e74
Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2797
diff
changeset
|
311 ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ'); |
883dd49d5e74
Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2797
diff
changeset
|
312 |
5291
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
313 |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
314 CREATE TABLE jetty_kinds( |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
315 id int PRIMARY KEY NOT NULL, |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
316 name VARCHAR(64) |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
317 ); |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
318 INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf'); |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
319 INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß'); |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
320 INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel'); |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
321 |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
322 CREATE SEQUENCE JETTIES_ID_SEQ; |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
323 CREATE TABLE jetties ( |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
324 id int PRIMARY KEY NOT NULL, |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
325 river_id int REFERENCES rivers(id) ON DELETE CASCADE, |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
326 path VARCHAR(256), |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
327 kind_id int REFERENCES jetty_kinds(id), |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
328 km FLOAT8, |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
329 z FLOAT8 |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
330 ); |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
331 SELECT AddGeometryColumn('jetties','geom',31467,'POINT',2); |
66d9389c80cb
added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents:
5276
diff
changeset
|
332 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
|
333 |
5409
8b29a94ebdd8
SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents:
5391
diff
changeset
|
334 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
|
335 CREATE TABLE flood_marks ( |
8b29a94ebdd8
SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents:
5391
diff
changeset
|
336 id int PRIMARY KEY NOT NULL, |
8b29a94ebdd8
SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents:
5391
diff
changeset
|
337 river_id int REFERENCES rivers(id) ON DELETE CASCADE, |
8b29a94ebdd8
SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents:
5391
diff
changeset
|
338 path VARCHAR(256), |
8b29a94ebdd8
SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents:
5391
diff
changeset
|
339 km FLOAT8, |
8b29a94ebdd8
SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents:
5391
diff
changeset
|
340 z FLOAT8, |
8b29a94ebdd8
SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents:
5391
diff
changeset
|
341 location VARCHAR(64), |
8b29a94ebdd8
SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents:
5391
diff
changeset
|
342 year int |
8b29a94ebdd8
SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents:
5391
diff
changeset
|
343 ); |
8b29a94ebdd8
SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
Tom Gottfried <tom@intevation.de>
parents:
5391
diff
changeset
|
344 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
|
345 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
|
346 |
3948
883dd49d5e74
Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2797
diff
changeset
|
347 COMMIT; |